Summary of database backup and restore commands in MySQL

Let's begin by telling you how to use mysqldump backup

SQL to back up the MyISAM table:

The code is as follows	Copy Code
/usr/local/mysql/bin/mysqldump-uyejr-pyejr--default-character-set=utf8--opt--extended-insert=false --triggers-r--hex-blob-x db_name > Db_name.sql

Use the following SQL to back up the Innodb table:

The code is as follows	Copy Code
/usr/local/mysql/bin/mysqldump-uyejr-pyejr--default-character-set=utf8--opt--extended-insert=false --triggers-r--hex-blob--single-transaction db_name > Db_name.sql

And then talk about mysqldump restore backed-up data

The file backed up by Mysqldump is a SQL script that can be poured directly, and there are two ways to import the data.

• Use MySQL client directly
For example:

The code is as follows	Copy Code
/usr/local/mysql/bin/mysql-uyejr-pyejr Db_name < Db_name.sql

• Use SOURCE syntax
In fact, this is not a standard SQL syntax, but the functionality provided by the MySQL client, for example:

The code is as follows	Copy Code
Source/tmp/db_name.sql;

You need to specify the absolute path of the file, and it must be a file that the Mysqld run user (for example, nobody) has permission to read.

Maybe people will know a command. Mysqlhotcopy below I would also briefly describe

Mysqlhotcopy supports the one-time copying of multiple databases while also supporting regular expressions. Here are a few examples:

The code is as follows	Copy Code
Root#/usr/local/mysql/bin/mysqlhotcopy-h=localhost-u=yejr-p=yejr db_name/tmp

(Db_name the database directory

Copy to/tmp)

The code is as follows	Copy Code
Root#/usr/local/mysql/bin/mysqlhotcopy-h=localhost-u=yejr-p=yejr db_name_1 ... db_name_n/tmp ROOT#/USR/LOCAL/MYSQL/BIN/MYSQLHOTCOPY-H=LOCALHOST-U=YEJR-P=YEJR db_name./regex//tmp

For more detailed instructions, check the manual, or call the following command to view Mysqlhotcopy's help:

The code is as follows	Copy Code
Perldoc/usr/local/mysql/bin/mysqlhotcopy

Note that to use mysqlhotcopy, you must have SELECT, RELOAD (to perform FLUSH TABLES) permissions, and you must be able to have read

Permissions for the Datadir/db_name directory.

Mysqlhotcopy Restore

Mysqlhotcopy is backed up by the entire database directory, which can be copied directly to the mysqld specified DataDir (here is

/usr/local/mysql/data/) directory, but also pay attention to the issue of permissions, the following example:

The code is as follows	Copy Code
ROOT#CP-RF db_name/usr/local/mysql/data/ Root#chown-r nobody:nobody/usr/local/mysql/data/

(Change the owner of the Db_name directory to mysqld)

Good other like the SQL statement here is not to tell, we can search Baidu.

leverage the shell commands in Linux

The code is as follows	Copy Code
shell> mysqldump--quick db_name | gzip > db_name.contents.gz

(the file created in this example is in a compressed format).

The commands for resuming/transferring to another table are as follows:

The code is as follows	Copy Code
Shell> Gunzip < db_name.contents.gz | MySQL db_name

Above command, for machines for *nix operating systems