Shulou(Shulou.com)06/01 Report--

This article is about what the mysql backup and restore library commands are about. The editor thought it was very practical, so I shared it with you as a reference. Let's follow the editor and have a look.

With regard to the method of backup and restore of mysql database, we first talk about the backup tool: mysqlhotcopy, you can use mysqlhotcopy tool for fast backup, then data restore, use MySQL command to restore; finally, you need to export database tables. You have to read this article for a detailed introduction.

1. Data backup

Back up the database regularly to minimize losses in the event of unexpected events.

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 that contains several CREATE and INSERT statements that can be used to recreate tables and insert data.

[using mysqldump to back up a single database]

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

[use mysqldump to back up the specified table 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, separated by spaces between multiple databases

[back up all databases in the system]

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

Tip: if you are backing up on the server and the tables are all MyISAM, you should consider using mysqlhotcopy because you can backup and restore faster.

two。 Copy the entire database directory directly

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

This is a simple, fast, and efficient backup method, and to maintain the consistency of the backup, you need to LOCK TABLES the relevant tables before the backup, and then FLUSH TABLES the tables (be sure to write all active index pages to the hard disk before starting the backup). This allows other users to continue querying the table when copying files from the database directory.

This method does not apply to the tables of the InnoDB storage engine. Using this method to back up data is best restored to the same version of the server. Different versions may not be compatible.

3. Quick backup using the mysqlhotcopy tool

Mysqlhotcopy is a Perl script.

Can only run on the same machine as the database directory, and can only back up tables of MyISAM and ARCHIVE types

two。 Data restore 1. Restore mysql-u username-p [dbname] using the MySQL command

< filename.sql 注意：如果filename.sql文件为mysqldump工具创建的包含创建数据库语句的文件，执行的时候不需要指定数据库名； 如果已经登录到MySQL服务器，还可以使用source命令导入SQL文件。 source filename 提示：执行source命令之前，必须使用use语句选择数据库。不然，恢复过程中会出现错误； 2.直接复制到数据库目录 如果数据库通过复制数据库文件备份，可以直接复制备份的文件到MySQL数据目录下实现还原。 通过该方式还原的时候，必须保持备份数据库和待还原的数据库服务器的主版本号相同。而且这种方式只是对MyISAM引擎的表有效，对于InnoDB引擎的表不可用； 执行还原以前关闭MySQL服务，将备份的文件或者目录覆盖MySQL的data目录，启动MySQL服务。 对于Linux/Unix操作系统来讲，复制完文件需要将文件的用户或者用户组更改为mysql运行的用户和组，通常用户是mysql，组也是mysql； 3.mysqlhotcopy快速恢复 mysqlhotcopy备份之后的文件也可以用来恢复数据库，在MySQL服务器停止运行的时候，将备份的数据库文件复制到MySQL存放的位置（MySQL的data文件夹），重新启动MySQL服务即可。 如果以根用户执行该操作，必须指定数据库文件的所有者 chown -R mysql.mysql /var/lib/mysql/dbnamecp -R /usr/backup/test usr/local/mysql/data 执行完该语句，重启服务器，MySQL将恢复到备份状态 提示：如果需要恢复的数据库已经存在，则在使用DROP语句删除已经存在的数据库之后，恢复才可以成功，另外MySQL不同版本之间必须兼容； 3.数据库迁移 数据库迁移就是把数据从一个系统移动到另一个系统上。数据迁移有以下原因： 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

You can also use the MySQL command to specify the format in which query results are displayed:

If there are many fields in a row of records, one row may not be fully displayed. You can use the-- vartical parameter to divide each record into multiple lines.

[export query results to HTML file]

Mysql-u root-p-- html-- execute= "SELECT statement" dbname > filename.html

[export query results to xml file]

Mysql-u root-p-xml-- execute= "SELECT statement" dbname > filename.xml4. Import a text file using LOAD DATA INFILE

The LOAD DATA INFILE statement is used to read rows from a text file at high speed and load them into a table. The file name must be a text string.

LOAD DATA INFILE 'path + file name .txt' INTO TABLE tablename [OPTIONS] [IGNORE number LINES]

Note: if some special characters are specified in the exported .txt file, these characters should also be specified in the restore statement to ensure the integrity and correctness of the data after the restore.

-- OPTIONS option

● FIELDS TERMINATED BY 'value'

● FIELDS [OPTIONALLY] ENCLOSED BY 'value'

● FIELDS ESCAPED BY 'value'

● LINES STARTING BY 'value'

● LINES TERMINATED BY 'value'

You can see that in the LOAD DATA statement, the filename file after the keyword INFILE is the source of the imported data

Tablename represents the name of the data table to be imported

The syntax of the OPTIONS part includes the syntax of the FIELDS part and the LINES clause, and the possible values are:

FIELDS TERMINATED BY 'value':

Sets the delimiter between fields, which can be single or multiple characters. By default, it is the tab'\ t'.

FIELDS [OPTIONALLY] ENCLOSED BY 'value':

Set the bounding character 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':

Sets how to write or read special characters, which can only be a single character, that is, set escape characters. The default value is "\".

LINES STARTING BY 'value':

Sets the start character of each line of data, which can be single or multiple. No characters are used by default.

LINES TERMINATED BY 'value':

Sets the character at the end of each line of data to be single or multiple characters, with a default value of'\ n'

[IGNORE number LINES]

Option indicates the number of lines ignored at the beginning of the file, and number indicates the number of lines ignored. FILE permission is required to execute a LOAD DATA statement

5. Import a text file using the mysqlimport command

Use the mysqlimport command to import text files without needing to log in to the MySQL client.

Using the mysqlimport statement requires specifying the required options, the name of the imported database, and the path and name of the imported data file.

The basic syntax of the mysqlimport command is as follows:

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

Value of [options]:

●-fields-terminated-by=value:

Sets the delimiter between fields, which can be single or multiple characters. By default, it is the tab'\ t'.

●-fields-enclosed-by=value:

Set the enclosing character of the field

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

Set the bounding character 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:

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

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

Sets 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 import file, that is, the file name is used as the table name, and the table must exist before importing data.

Thank you for reading! On the mysql backup and restore library commands which are shared here, I hope the above content can be of some help to you, so that you can learn more knowledge. If you think the article is good, you can share it and let more people see it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.