Mysql backup and restore library command method analysis (long article)

Regarding the method of backing up and restoring the mysql database, here we first talk about the backup tool: mysqlhotcopy. Use the mysqlhotcopy tool to perform a quick backup. Then restore the data and use the MySQL command to restore; finally, you need to export the database table. You have to read this article for a detailed introduction.

1. Data backup

Back up the database regularly to minimize losses when unexpected situations occur.

1. Use the mysqldump command to back up

mysqldump is a database backup tool provided by MySQL. When the mysqldump command is executed, the database is backed up into a text file, which contains multiple CREATEs. and INSERT statements. Use these statements to re-create tables and insert data;

[Use mysqldump to back up a single database]

mysqldump -u user -h host -p password dbname>filename.sql

[Use mysqldump to back up specified tables in the database]

mysqldump -u user -h host -p password dbname[tbname,[tbname…]]>filename.sql

[Use mysqldump to back up multiple databases]

mysqldump -u user -h host -p password --databases[dbname,[dbname…]]>filename.sql

After using the --databases parameter, you must specify the name of at least one database, and use spaces to separate multiple databases;

[ Back up all databases in the system】

mysqldump -u user -h host -p password --all-databases>filename.sql

Tips: If you are backing up on the server and the tables are all MyISAM, you should consider using mysqlhotcopy because it can be backed up and restored faster;

2. Directly copy the entire database directory

Because the MySQL table is saved as a file, you can directly copy the storage directory and files of the MySQL database for backup.

This is a simple, fast, and effective backup method. To maintain the consistency of the backup, you need to perform the LOCK TABLES operation on the relevant tables before the backup, and then perform FLUSH TABLES on the table (make sure to All active index pages are written to disk). This allows other users to continue querying the table while the files in the database directory are copied.

This method is not applicable to tables of InnoDB storage engine. It is best to restore the data backed up to the same version of the server using this method. Different versions may not be compatible;

3. Use the mysqlhotcopy tool to quickly back up

mysqlhotcopy is a Perl script.

Can only be run on the machine where the database directory is located, and can only back up MyISAM and ARCHIVE type tables;

2. Data restoration

1. Use MySQL commands to restore

mysql -u username -p [dbname] < filename.sql

Note: If the filename.sql file is a file created by the mysqldump tool and contains a database creation statement, you do not need to specify the database name when executing;

If you have logged in to the MySQL server, you can still Use the source command to import the SQL file.

source filename

Tip: Before executing the source command, you must use the use statement to select the database. Otherwise, errors will occur during the recovery process;

2. Copy directly to the database directory

If the database is backed up by copying the database file, you can directly copy the backed up file to the MySQL data directory for restoration.

When restoring through this method, the major version numbers of the backup database and the database server to be restored must be kept the same. And this method is only valid for the tables of the MyISAM engine, but not for the tables of the InnoDB engine;

Close the MySQL service before performing the restore, overwrite the backup file or directory with the MySQL data directory, and start the MySQL service.

For Linux/Unix operating systems, after copying the file, you need to change the user or user group of the file to the user and group where mysql is running. Usually the user is mysql and the group is also mysql;

3.mysqlhotcopy quick recovery

Mysqlhotcopy backed up files can also be used to restore the database. When the MySQL server stops running, copy the backed up database files to the location where MySQL is stored (MySQL data folder). Just restart the MySQL service.

If you perform this operation as the root user, you must specify the owner of the database file

chown -R mysql.mysql /var/lib/mysql/dbname
cp -R /usr/backup/test usr/local/mysql/data

After executing this statement, restart the server, MySQL will restore to the backup state

Tips: If the database that needs to be restored already exists, the recovery can only be successful after using the DROP statement to delete the existing database. In addition, different versions of MySQL must be compatible;

3. Database migration

Database migration is to move data from one system to another system. Data migration occurs for the following reasons:

1.相同版本的MySQL数据库之间的迁移

相同版本的MySQL数据库之间的迁移就是指在主版本号相同的MySQL数据库之间进行数据库移动。

举例：

将www.abc.com主机上的MySQL数据库全部迁移到www.bcd.com主机上：

mysqldump -h www.abc.com -u root -ppassword dbname | mysql -h www.bcd.com -uroot -ppassword

说明：

mysqldump导入的数据直接通过管道符|，传给mysql命令导入到主机www.bcd.com数据库中，dbname为需要迁移的数据库名称，如果需要迁移全部的数据库，可以使用参数 --all-databases

2.不同版本的MySQL数据库之间的迁移

MySQL服务器升级的时候，需要先停止服务，然后卸载旧版本，并安装新版本MySQL，这种更新方法很简单，如果想保留旧版本中的用户访问控制信息，需要备份MySQL中的mysql数据库，在新版本MySQL安装完成之后，重新读入mysql备份文件中的信息；

旧版本与新版本的字符集不同时，迁移过程需要对默认字符集进行修改，不然可能无法正常显示结果；

对于InnoDB引擎的表，一般只能使用mysqldump工具将数据导出，然后使用mysql命令导入到目标服务器上。

从新版本向旧版本迁移数据的时候，需要特别的小心，最好使用mysqldump命令导出，然后导入目标数据库中；

3.不同数据库之间的迁移

数据库迁移可以使用一些工具，例如在Windows系统下，可以使用MyODBC实现MySQL和SQL Server之间的迁移。

MySQL官方提供的工具MySQL Migration Toolkit也可以实现在不同数据库间进行数据迁移；

4.表的导出和导入

MySQL数据库中的数据可以导出成SQL文本文件、xml文件或者HTML文件。

1.使用SELECT…INTO OUTFILE导出文本文件

MySQL数据库导出数据的时候，允许使用包含导出定义的SELECT语句进行数据导出操作。该文件被创建到服务器主机上，因此必须拥有文件写入权限（FILE权限），才能使用此语法。

语法格式：

SELECT columnlist FORM table WHERE condition INTO OUTFILE ‘filename’ [OPTIONS]

[OPTIONS]选项：

●　FIELDS TERMINATED BY ‘value’

●　FIELDS [OPTIONALLY] ENCLOSED BY ‘value’

●　FIELDS ESCAPED BY ‘value’

●　LINES STARTING BY ‘value’

●　LINES TERMINATED BY ‘value’

说明：filename不能是一个已经存在的文件；

OPTIONS部分语法包括FIELDS部分的语法和LINES子句，其可能的取值有：

FIELDS TERMINATED BY ‘value’：

设置字段之间的分隔字符，可以为单个或者多个字符，默认情况下为制表符‘\t’

FIELDS [OPTIONALLY] ENCLOSED BY ‘value’：

设置字段的包围字符，只能为单个字符，如果使用了OPTIONALLY，则只有CHAR和VERCHAR等字符数据字段被包括；

FIELDS ESCAPED BY ‘value’：

设置如何写入或者读取特殊字符，只能为单个字符，即设置转义字符，默认值为“\”

LINES STARTING BY ‘value’：

设置每行数据开始字符，可以为单个或者多个，默认不使用任何字符

LINES TERMINATED BY ‘value’：

设置每行数据结尾的字符 可以为单个或者多个字符，默认值为‘\n’;

注意：FIELDS和LINES两个子句是可选的，如果同时指定，FIELDS必须位于LINES的前面；

2.使用mysqldump命令导出文本文件

mysqldump工具不仅可以将数据导出为包含CREATE、INSERT的SQL文件，也可以导出为纯文本文件；

mysqldump -T path-u root -p dbname [tables] [OPTIONS]

--OPTIONS选项：

●　--fields-terminated-by=value

●　--fields-enclosed-by=value

●　--fields-optionally-enclosed-by=value

●　--fields-escaped-by=value

●　--lines-terminated-end-by=value

说明：只有指定了T参数才可以导出为纯文本文件；path表示导出数据的目录；tables为指定要导出表的名称；如果不指定，将导出数据库dbname中的所有的表；

[options]取值：

●　--fields-terminated-by=value：

设置字段之间的分隔字符，可以为单个或者多个字符，默认情况下为制表符‘\t’

●　--fields-enclosed-by=value：

设置字段的包围字符；

●　--fields-optionally-enclosed-by=value：

设置字段的包围字符，只能为单个字符，如果使用了OPTIONALLY，则只有CHAR和VERCHAR等字符数据字段被包括；

●　--fields-escaped-by=value：

控制如何写入或者读取特殊字符，只能为单个字符，及设置转义字符，默认为反斜线“\”;

●　--lines-terminated-end-by=value：

设置每行数据结尾的字符，可以为单个或者多个字符，默认值为‘\n’

3.使用MySQL命令导出文本文件

mysql是一个功能丰富的工具命令，使用MySQL还可以在命令行模式下执行SQL指令将查询结果导入到文本文件中。相比mysqldump，MySQL工具导出的结果可读性更强。

如果MySQL服务器是一个单独的机器，用户是在一个client上进行操作，用户要把数据导入到client机器上，可以使用mysql -e语句；

使用MySQL导出数据文本文件语句的基本格式如下：

mysql -u root -p --execute=”SELECT语句” dbname > filename.txt

使用MySQL命令还可以指定查询结果的显示格式：

如果某行记录字段很多，可能一行不能完全显示，可以使用--vartical参数，将每条记录分为多行显示；

【将查询结果导出到HTML文件中】

mysql -u root -p --html --execute=”SELECT语句” dbname > filename.html

【将查询结果导出到xml文件中】

mysql -u root -p --xml --execute=”SELECT语句” dbname > filename.xml

4.使用LOAD DATA INFILE方式导入文本文件

LOAD DATA INFILE 语句用于高速的从一个文本文件中读取行，并装入一个表中。文件名称必须为文字字符串。

LOAD DATA INFILE ‘路径+文件名.txt’ INTO TABLE tablename [OPTIONS] [IGNORE number LINES]

注意：如果导出的.txt文件中指定了一些特殊的字符，因此还原语句中也要指定这些字符，以确保还原之后数据的完整性和正确性；

--OPTIONS选项

●　FIELDS TERMINATED BY ‘value’

●　FIELDS [OPTIONALLY] ENCLOSED BY ‘value’

●　FIELDS ESCAPED BY ‘value’

●　LINES STARTING BY ‘value’

●　LINES TERMINATED BY ‘value’

可以看到LOAD DATA 语句中，关键字INFILE后面的filename文件为导入数据的来源；

tablename表示待导入的数据表名称；

OPTIONS部分语法包括FIELDS部分的语法和LINES子句，其可能的取值有：

FIELDS TERMINATED BY ‘value’：

设置字段之间的分隔字符，可以为单个或者多个字符，默认情况下为制表符‘\t’

FIELDS [OPTIONALLY] ENCLOSED BY ‘value’：

设置字段的包围字符，只能为单个字符，如果使用了OPTIONALLY，则只有CHAR和VERCHAR等字符数据字段被包括；

FIELDS ESCAPED BY ‘value’：

设置如何写入或者读取特殊字符，只能为单个字符，即设置转义字符，默认值为“\”

LINES STARTING BY ‘value’：

设置每行数据开始字符，可以为单个或者多个，默认不使用任何字符

LINES TERMINATED BY ‘value’：

设置每行数据结尾的字符 可以为单个或者多个字符，默认值为‘\n’;

[IGNORE number LINES]

选项表示忽略文件开始处的行数，number表示忽略的行数。执行LOAD DATA语句需要FILE权限；

5.使用mysqlimport命令导入文本文件

使用mysqlimport命令可以导入文本文件，并且不需要登录MySQL客户端。

使用mysqlimport语句需要指定所需的选项、导入的数据库名称以及导入的数据文件的路径和名称。

mysqlimport命令的基本语法如下：

mysqlimport -u root -p dbname filename.txt [OPTIONS]

[options]取值：

●　--fields-terminated-by=value：

         设置字段之间的分隔字符，可以为单个或者多个字符，默认情况下为制表符‘\t’

●　--fields-enclosed-by=value：

Set the surrounding characters of the fields;

●　--fields-optionally-enclosed-by=value：

Set the surrounding characters of the field, which can only be a single character. If OPTIONALLY is used, only character data fields such as CHAR and VERCHAR are included;

●　--fields-escaped-by=value：

Control how to write or read special characters, which can only be a single character, and set the escape character. The default is backslash "\";

●　--lines-terminated-end-by =value：

Set the character at the end of each line of data, which can be single or multiple characters. The default value is '\n'

●　--ignore-lines=n

Ignore the first n lines of the data file;

Note: the mysqlimport command cannot specify the table name of the imported database. The name of the data table is determined by the name of the imported file, that is, the file name is used as the table name, and the table is imported before the data is imported. must exist.

Related recommendations:

mysql database backup and restore command

Mysql database backup and restore command memo

The above is the detailed content of Mysql backup and restore library command method analysis (long article). For more information, please follow other related articles on the PHP Chinese website!