A quick way to perform a MySQL backup and restore it whenever necessary.
In this guide, we will be utilizing two commands to perform our MySQL Backup and Restore procedure: mysql and mysqldump.
mysqldump
This command is used to dump a database or a collection of database. It saves SQL statements to a .sql file that you may execute to restore your database should the need arise. Using this tool, it is possible to backup a single database, a specific table in your database, multiple databases, or all the databases stored in your server.
mysql
mysql is an SQL shell that is used to query or to manipulate your database records. You may pass options to the mysql binary to execute SQL command straight from your shell.
1. Backup
Single Database
|
1 |
mysqldump -u root -p MYSQL_ROOT_PASSWORD DATABASE_NAME > DATABASE_NAME.sql |
Specific Table in a Database
|
1 |
mysqldump -u root -p MYSQL_ROOT_PASSWORD DATABASE_NAME TABLE_NAME > DATABASE_TABLE_NAME.sql |
Multiple Databases
|
1 |
mysqldump -u root -p MYSQL_ROOT_PASSWORD —databases DATABASE_ONE DATABASE_TWO > TWO_DATABASES.sql |
All Databases
|
1 |
mysqldump -u root -p MYSQL_ROOT_PASSWORD —all-databases > ALL_DATABASES.sql |
2. Restore
All/Full Databases
|
1 |
mysql -u root -p MYSQL_ROOT_PASSWORD DATABASE_NAME < DATABASE_NAME.sql |
Single Database
|
1 |
mysql -u root -p MYSQL_ROOT_PASSWORD —one-database DATABASE_NAME < ALL_DATABASE.sql |
Specific Table in a Database
|
1 2 3 4 |
mysql -u root -p MYSQL_ROOT_PASSWORD DATABASE_NAME < DATABASE_TABLE_NAME.sql # Table Name is an optional second argument mysql -u root -p MYSQL_ROOT_PASSWORD DATABASE_NAME TABLE_NAME < DATABASE_TABLE_NAME.sql |
