MySQL offers an easy way for both backup and restore either a single database or the entire databases via “mysqldump” command. Learn how to use this command is essential to clone a database from one host to another or backup your databases regularly.

Where to locate “mysqldump” command

To find the location of ‘mysqldump’, open a terminal and run

which mysqldump

It should return something similar to

/usr/bin/mysqldump

For standard MySQL installation, it should be inside “mysql/bin” folder. If you are using MAMP (MacOS), the path to mysqldump is “MAMP/Library/bin/mysqldump”.

Dump a single database with mysqldump

Here’s the syntax of a typical mysqldump command.

mysqldump -u[username] –p[password] [database_name] > /path/to/[database_name].sql

Assuming the Mysql username is root and password is also root. We are attempting to dump a database called sampledb:

mysqldump -uroot -proot sampledb > /my-dump-folder/sampledb.sql

Dump multiple tables from a single database

To backup one or multiple tables of a specific database, simply enter the table name after the database name.

The syntax to backup database table indivudally:

mysqldump -u[root] -p[password] [database_name] [table1] [table2] > /path/to/[database_tables].sql

For example:

mysqldump sampledb table1 table2 tabl5 > sample_tbl_dump.sql

Dump ALL databases with mysqldump

To backup entire databases, use “–all-databases” flag:

mysqldump -u[root] -p[root] --all-databases > /my-dump-folder/all-databases.sql

For example:

mysqldump -uroot -proot --all-databases > /my-dump-folder/all-databases.sql

Restore database from the backup generated by mysqldump

To restore from the dump file, here’s the syntax for restore using mysql command:

mysql -u[username] –p[password] [database_name] < /path/to/[database_name].sql

For example:

mysql -uroot -proot < my_database_dump.sql

Conclusion

Using MySql command line tool seems intimidating when one have only have experienced with MYSQL desktop-based clients such as MySQL WorkBench on Windows, or Sequel Pro on Mac, both which also offer backup and restore functions without any typing. However, to my experience, the mysqldump is still unequivocally the most reliable way to dump databases.

 

Further reading:
https://dev.mysql.com/doc/refman/5.7/en/mysqldump-sql-format.html