Backup and Restore MySQL Databases Using mysqldump

mysqldump lets you quickly and efficiently take a backup of a single database, multiple databases or even all of the databases on your server. This requires shell access to the server you wish to perform the commands on and the mysql login credentials for your user(s).


Backing up a Single Database

mysqldump -u user -p[user_pass] [database_name] > dumpfile.sql

mysqldump -u root -pABC123 exampledb > exampledbdump.sql

The filepath for your database (.sql) backup will be your current working directory. You may use an absolute path as well, such as /root/exampledbdump.sql


Backing up Multiple Databases

If you wish to backup two or more databases, you’ll need to specify –databases followed by the names of the databases you want to backup. Remember that the database user must have privileges to access the database that is being backed up.

mysqldump -u user -p[user_pass] –databases [database_name] [database_name] [database_name] > dumpfile.sql

mysqldump -u root -pABC123 –databases exampledb sampledb testdb > exampledb_sampledb_testdb.sql


Backing up all databases

mysqldump -u user -p[user_pass] –all-databases > all-dbs.sql

mysqldump -u root -pABC123 –all-databases > all-dbs.sql


Restoring Database(s)

mysql -u user -p[user_pass] [database_name] < dumpfile.sql

mysql -u root -pABC123 exampledb < exampledbdump.sql


Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.