[MySQL 101] Backup & Restore

As in previous post "[MySQL 101] Get Started", we have introduced some basic perspective of MySQL. In this article, we will talk about backup & restore action in MySQL.

Backup a database

To backup a MySQL database, we can use mysqldump command that natively support by official MySQL to export database to SQL script file which store the structure of data.

$ mysqldump --opt -u [uname] -p [dbname] > [backupfile.sql]

The command above will export database named [dbname] to backupfile.sql

Parameters:

• [uname]: username of user that have privileges to backup/export the database.

• [dbname]: the database name that you need to backup, it could be replaced by --all-databases that apply backup all databases available on MySQL instance.

• --opt: options of mysqldump

• -p: prompt for password input. It could be replaced by --password='yourpassword' to explicitly before running command.

Example:

To backup the database named "test", and explicitly input password:

$ mysqldump --opt -u admin --password='Pa$$w0rd' test > /home/user/backupfile.sql

To backup all databases:

$ mysqldump --opt -u admin --password='Pa$$w0rd' --all-databases > /home/user/backupfile.sql

To backup some tables (tbl_1, tbl_2, tbl_3) in database "test":

$ mysqldump --opt -u admin --password='Pa$$w0rd' test tbl_1 tbl_2 tbl_3 > /home/user/backupfile.sql

You could also apply compress configuration:

$ mysqldump --opt -u admin --password='Pa$$w0rd' test | gzip -9 > /home/user/backupfile.sql.gz

Restore database from .sql file

Use mysql syntax:

$ mysql -u [uname] --password='yourpassword’ [dbname] < [bakupfile.sql]

Example:

Restore database named "test" from db_test.sql file:

$ mysql -u admin --password='Pa$$w0rd' test < /home/user/db_test.sql

Restore all databases from alldatabases.sql file:

$ mysql -u admin --password='Pa$$w0rd' < /home/user/alldatabases.sql