How to back up the Mysql database _mysql

There are two ways to backup data in MySQL :

1. Mysqlhotcopy

This command locks up the table before copying the file and synchronizes the data to the data file to avoid copying to the incomplete data file, which is the safest and quickest way to do so.

The commands are used in the following ways:

Mysqlhotcopy-u root-p<rootpass> db1 DB2 ... dbn <output_dir>

If you need to back up all the databases, you can add the –regexp= ". *" parameter.

the mysqlhotcopy command automatically completes data locking and does not need to shut down the server when backing up. It can also refresh the log so that the checkpoints for backup files and log files remain synchronized.

2. mysqldump

This command is used to pour the database into a SQL file, and is a very old command.

The commands are used in the following ways:

Mysqldump-u Root-p<rootpass>–hex-blob db1 > Db1.sql

After adding the –hex-blob parameter,theblob data is converted to a BASE64 -like method to a text stored in the SQL file, it does not cause SQL file format problem. After testing, the BLOB data backed up by this method can be restored back to its full accuracy.

If you want to back up all the databases, you can add the –all-databases parameter.

The mysqldump command also allows you to transfer the database directly to another server without generating a backup file. Repeat execution to update the remote database periodically.

% mysqladmin-h Remote_host Create TestDB

% mysqldump–opt TestDB | Mysql-h Remote_host TestDB

You can also remotely invoke programs 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 kinds of database backup methods:

Using a program such as Mysqlhotcopy is faster because it operates directly on system files, but it needs to be coordinated before and after backup of database data.

the mysqldump program backs up the database slowly, but it generates a text file that is easy to migrate.

For databases that do not use InnoDB , mysqlhotcopy is the best method; otherwise, mysqldump must be used . Either way, the backup method for the direct copy file should be discarded.