Backup and Restore MySQL Database with command (Command)

Backing up data is an integral part of a Database administrator. Hence the work backup (backup) MySQL database regularly is necessary. Most of you often use phpmyadmin to backup or restore.

You can simplify by use command in terminal To backup and restore, follow the instructions below:

General notes in commands:

[uname] : user database
[dbname] : Tên database
[bk.sql] : Tên file backup sẽ được tạo ra
[--opt] : Các tùy chọn thêm của lệnh

1. Backup by command in Terminal (backup)

Use the following structure:

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

Eg : To backup database Duyquangnet

$ mysqldump -u root -p duyquangnet > bk_duyquangnet.sql

You can also customize the backup separately from the table by selecting the table to backup with the command below:

$ mysqldump -u root -p duyquangnet table_a table_b > bk_duyquangnet.sql

Back up MySQL Database automatically compresses

If your database is too large, you can let it automatically compress to reduce the size, we can use the compression command structure with gzip

$ mysqldump -u [uname] -p [dbname] | gzip -9 > [bk_duyquangnet.sql.gz]

2. Restore MySQL Database

The command below is used to Restore, used in case your Database has a problem or error, and you want to restore the previous version.

Requirements: Must have a file backup database (in item 1)

$ mysql -u [uname] -p [dbname] < [backupfile.sql]

Example: Restore database Duyquangnet

$ mysql -u root -p duyquangnet < bk_duyquangnet.sql

Recover a pre-compressed database

gunzip < [backupfile.sql.gz] | mysql -u [uname] -p [dbname]

If you have an already created database, then use the mysqlimport command to restore. The command structure is as follows:

mysqlimport -u [uname] -p [dbname] [bk.sql]