How to back up the MySQL database

website data for our webmaster is the most valuable, we should usually form a good backup data habits. This can greatly reduce the loss caused by our database crash.
There are two ways 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, which is the safest and quickest way to backup.
commands are used in the following ways:
mysqlhotcopy-u root-p db1 db2 ... dbn
If you need to back up all the databases, you can add the –regexp= ". *" parameter.
the mysqlhotcopy command automates data locking and does not have to shut down the server when backing up. It can also refresh the log so that checkpoints for backup files and log files remain in sync.

  2. Mysqldump

650) this.width=650; "id=" aimg_2358 "src=" http://bbs.dbfen.com/data/attachment/forum/201411/27/ 104808qxz42xuh2bebhonz.png "class=" Zoom "width=" 394 "alt=" 104808qxz42xuh2bebhonz.png "/>

This command is used to pour the database into a SQL file, which is a very old command.
commands are used in the following ways:
mysqldump-u root-p–hex-blob db1 > Db1.sql
after adding the –hex-blob parameter, the BLOB data will be converted to a text-like BASE64 in a SQL file, which will not cause SQL file format problems. After testing, this method backs up the BLOB data and restores it back again to be completely correct.
if you want to back up all the databases, you can add the –all-databases parameter.
with the mysqldump command, you can also transfer the database directly to another server without generating a backup file. Recurrence can update the remote database periodically.
% mysqladmin-h remote_host Create TestDB
% mysqldump–opt TestDB | mysql-h remote_host testdb
In addition, you can remotely invoke the program on the server via SSH, such as:
% ssh remote_host mysqladmin create TestDB
% mysqldump–opt testdb | ssh remote_host mysql testdb

advantages and disadvantages of 2 database backup methods:
The use of mysqlhotcopy and other programs to backup fast, because it directly to the system files, but the need for human coordination of database data backup consistency before and after.
the mysqldump program backs up the database slowly, but the text files it generates are easy to port.
for databases that do not use InnoDB, Mysqlhotcopy is the best approach; otherwise, mysqldump must be used. However, the backup method for copying files directly should be deprecated.

    • Summary ·

backup methods are not the same, but for us, in the face of thousands of instances, choose the right backup tools to achieve unified configuration, unified planning, the construction of intelligent scheduling backup cloud platform is the king. After all, there is no way to ignore the cost of operations that coexist in multiple backup ways. Choose Multi-Backup, believe is your good choice!

Go from multi-backup forum http://bbs.dbfen.com/thread-2679-1-1.html

How to back up the MySQL database