10 Effective Mysqldump command Examples for your Reference

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

Mysqldump examples

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

Updated on September 3, 2017

Was this article helpful?

Related Articles

Leave a Comment