MySQL is one of the most widely used Database system, that comes with tons of effective command line utilities. For instance, taking backup of your MySQL database and restoring it is made simple using mysqldump command. Today we are going to see ten effective mysqldump command examples for your reference.
1. Taking backup of your MySQL database
Mysqldump allows you to input password when requested or supply it along with the command. Below is the example command for both the methods.
$mysqldump -u root -p[root_password] [database_name] > save_mydb.sql or $mysqldump -u root --password=[root_password] [database_name] > save_mydb.sql
2. Backup single database
The below example will allow to take backup of a single database. Lets assume, the database name as ‘RequestTracker’.
$mysqldump -u root --password=mydbpassword RequestTracker > RT_dump.sql
3. Backup multiple databases at once
If you wish to take backup of multiple databases at the same time, then identify the databases and pass them to mysqldump command as shown below. In our example, the databases are RequestTracker, RequestTracker_2, RequestTracker_3.
$mysqldump -u root –-password=mydbpassword RequestTracker RequestTracker_2 RequestTracker_3 > db_dump.sql
4. Backup all databases at once
Interestingly, you can backup all databases in a single command.
$mysqldump -u root --password=mydbpassword --all-databases > all_database.sql
5. Backup only specific table of a database
Don’t want to backup the entire database and want only specific table? say user_table from RequestTracker database. Here’s the command,
$mysqldump -u root --password=mydbpassword RequestTracker user_table > user_table_rt.sql
6. Restore Database
Login to MySQL
$mysql -u root -p
Create database
mysql> create database RequestTracker; Query OK, 1 row affected (0.02 sec)
Restore the database,
source RequestTracker.sql;
Note: mysql will search RequestTracker.sql file inside the current directory. If not, then you will have to mention absolute path of the file.
source /tmp/RequestTracker.sql;
7. Another way to Restore Database
mysql -u root --password=mydbpassword RequestTracker < /tmp/rt_dump.sql
8. Backup and Restore database on a remote server at once
If you to wish to backup database on one server and send it to restore on remote server, then run the below command. The below example will backup database named ‘RequestTracker’ on the current server and restores it on a remote server as “RequestTracker1”.
mysqldump -u root --password=mydbpassword RequestTracker | mysql -u root --password=myremotepass --host=remote_hostname -C RequestTracker1
Remember: The above command assumes that the database ‘RequestTracker1″ already exist on the remote server.
9. Use Cron to execute regular backups
Let’s write a simple shell script to take backup regularly. Copy the below code and paste it in a file called “backup.sh“.
#!/bin/sh today_date=$(date +"%Y%m%d") mysqldump -u root --password=mydbpassword RequestTracker > /backup/RT_dump_$today_date.sql
Remember to change the username, password and database accordingly.
Create cron as below,
0 0 * * * /bin/sh /backup/backup.sh
The above cron will execute daily at 12 mignight.
10. More mysqldump options
Get more mysqldump options using the below command,
$mysqldump --help
