Here we will talk about how can we back up the whole database or a single table structure and data in the database. But why are we doing all these things so I want to tell you if you accidentally drop the database then this backup will help you.
In this quick tutorial, we will learn three things first the entires database backup command, 2nd- particular data table backup, and third all available database backup on your MySQL server.
For this, we will use a simple MySQL command. So, let’s start:
BACKUP || DATABASE || TABLES || SINGLE || MULTIPLE
- Go to MySQL console and login with your Database user.
- Type SHOW DATABASES; command and press Enter then all databases are seeing you.
- Now type USE Your_Database_Name; command and press Enter key.
- Type SHOW TABLES; and now all tables of the database are showing.
- Then run the important database backup command MySQL dump.
For example:
Syntax:
mysqldump -u root -p database_name > database_name.sql
The above common will create a complete backup of your database. And note that if you are logged as the same user then -u and -p not require.
If you want to backup only a single table then you have to change the command a little bit.
Syntax:
mysqldump database_name table_name > table_name.sql
In the above command instead of selecting whole database, we have just use the name of the particular Database table, we want to backup in SQL format.
If you want to backup of multiple databases then:
Syntax:
mysqldump -u root -p –all-databases > all_databases.sql
So, as we saw above how we can make database backup and use it later when needed.
