How to back up a MySQL database

How to back up a MySQL database

There are two methods to back up data in MySQL:

1. mysqlhotcopy

This command locks the table before copying the file and synchronizes the data to the data file to avoid copying to incomplete data files. This is the safest and quickest backup method.

The command is used as follows:

Mysqlhotcopy-u root-p <rootpass> db1 db2... Dbn <output_dir>

To back up all databases, add the-regexp = ". *" parameter.

The Mysqlhotcopy command automatically locks data and does not need to shut down the server during Backup. It can also refresh logs so that the checkpoints of Backup files and log files can be synchronized.

2. mysqldump

This command is used to convert the database into an SQL file. It is a very old command.

The command is used as follows:

Mysqldump-u root-p <rootpass>-hex-blob db1> db1. SQL

After the-hex-blob parameter is added, blob data is converted to a file originally stored in an SQL file by a BASE64-like method, which will not cause the format of the SQL file. After testing, it is completely correct to restore the blob data backed up in this way.

To back up all databases, you can add the-all-databases parameter.

You can use the mysqldump command to directly transfer the database to another server without generating backup files. You can regularly update the remote database after repeated execution.

% Mysqladmin-h remote_host create testdb

% Mysqldump-opt testdb | mysql-h remote_host testdb

In addition, you can remotely call programs on the server through ssh, such:

% Ssh remote_host mysqladmin create testdb

% Mysqldump-opt testdb | ssh remote_host mysql testdb

Advantages and disadvantages of the two database backup methods:

Using mysqlhotcopy and other programs to back up data is fast, because it directly performs operations on system files, but manual coordination is required for database data backup consistency.

The mysqldump program backs up the database slowly, but the text files it generates are easy to transplant.

Mysqlhotcopy is the best method for databases that do not use InnoDB; otherwise, mysqldump must be used. In any case, the backup method for directly copying files should be discarded.