Implementation of MySQL5.7 mysqldump backup and recovery

MySQL backup

Cold backup:
停止服務進行備份，即停止數據庫的寫入

Hot backup:
不停止服務進行備份（在線）

MySQL's MyIsam engine only supports cold backup, while InnoDB supports hot backup. The reasons are:

The InnoDB engine is a transactional storage engine. Each statement will be written into a log, and each statement has a time point in the log. So when backing up, MySQL can perform redo and undo based on this log, rolling back transactions that were not committed during the backup and redoing those that have been committed. But MyIsam cannot do this. MyIsam has no logs. In order to ensure consistency, you can only shut down or lock the table for backup.

InnoDB does not support directly copying the entire database directory and using the mysqlhotcopy tool for physical backup:

1. 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. The location of the MYSQL database directory is not necessarily the same. On Windows platforms, the directory where MYSQL5.6 stores the database usually defaults to ~\MySQL\MYSQL Server 5.6\data, or other user-defined directories. This method does not apply to tables using the INNODB storage engine. Data backed up using this method is best restored to a server of the same version; different versions may not be compatible. When restoring, you can directly copy the backup file to the MYSQL data directory to restore it. When restoring in this way, you must ensure that the major version number of the database of the backup data and the database server to be restored are the same. Moreover, this method is only valid for the MYISAM engine and is not available for tables using the InnoDB engine. Before performing the restore, shut down the MySQL service, overwrite the MySQL data directory with the backed-up files or directories, and start the MySQL service.

2. Use mysqlhotcopy tool for quick backup
mysqlhotcopy is a perl script originally written and contributed by Tim Bunce. He uses LOCK TABLES , FLUSH TABLES , and cp or scp to quickly back up the database. It is the fastest way to back up a database or a single table, but it can only run on the machine where the database directory is located and can only back up MyISAM type tables.

A brief introduction to mysqldump backup

mysqldump can produce two types of output files, depending on whether the -tab=dir_name option is used.

Without using the -tab=dir_name option, the data file generated by mysqldump is a plain text SQL file consisting of CREATE (database, table, storage path, etc.) statements and INSERT (record) statements. The output results are saved in a file, and the backup file can be restored using the mysql command.

Using the -tab=dir_name option, mysqldump generates two output files for each data table to be backed up: one is a delimited text file, in which each row in the backed up data table is stored as a line of text, saved as "table name.txt"; the other output file is the CREATE TABLE statement of the data table, saved as "table name.sql".

mysqldump Syntax and Options

【Command】 shell> mysqldump -help

- -all-databases means backing up all databases in the system. After using the - -databases parameter, you must specify the name of at least one database. Multiple database names are separated by spaces.

Commonly used options

- -add-drop-table
This option will add a DROP TABLE IF EXISTS statement in front of each table, which can ensure that there will be no errors when importing back to the MySQL database, because each time it is imported, it will first check whether the table exists, and delete it if it exists.

--add-locks
This option bundles a LOCK TABLE and UNLOCK TABLE statement with the INSERT statement. This prevents other users from performing operations on the table while the records are being re-imported into the database.

- -tab
This option will create two files, one is a delimited text file, each row in the backed up data table is stored as a line of text, saved as "table name.txt"; the other output file is the CREATE TABLE statement of the data table, saved as "table name.sql".

-quick or -opt
If you do not use the --quick or --opt option, mysqldump loads the entire contents into memory before dumping the results. This can be a problem when you are dumping large databases. This option is on by default, but can be turned off using --skip-opt.

-skip-comments
Use --skip-comments to remove comments in the exported file.

- -compact
Use the -compact option to output only the most important statements without outputting comments, table deletion statements, etc.

Backing up data in SQL format

If the backup file name .sql does not specify the location, it is placed in the ~\MySQL\MySQL Server 5.6\bin directory by default.

Specify the data backup path:
mysqldump –h 主機名–u 用戶名–p - -all-databases > C:\備份文件名.sql

Call mysqldump with the --all-databases option to back up all databases.
mysqldump –h 主機名–u 用戶名–p - -all-databases > C:\備份文件名.sql

Call mysqldump with the --databases option to back up the specified databases.
mysqldump –u 用戶名–p - -databases db1 db2 db3 … > C:\備份文件名.sql

Call mysqldump to back up a specified database:
mysqldump –u 用戶名–p - -databases db > 備份文件名.sql

Use mysqldump without the --databases option to back up a specific database. The generated backup file does not contain CREATE DATABASE and USE statements:
mysqldump –u 用戶名–p db > 備份文件名.sql

Note: When backing up a database, -databases can be omitted, but this will result in the backup file name .sql not having CREATE DATABASE and USE statements. When restoring the backup file, you must specify a default database name so that the server knows which database to restore the backup file to; this can result in you using a database name that is different from the original database name.

Call mysqldump to back up several tables in a database:
mysqldump –u用戶名–p 數據庫名表名1 表名2 表名3… > 備份文件名.sql

Restore SQL format backup files

If the --all-databases or --databases option is used for the file backed up by mysqldump, the backup file contains CREATE DATABASE and USE statements, so you do not need to specify a database name to restore the backup file.

In Shell command:

shell> mysql –u username –p < backup file.sql

Under the mysql command, use the source command to import the backup file:

mysql> source backup file.sql; // Already logged in to mysql, use the source command

If a single database is backed up by mysqldump and the --databases option is not used, the backup file does not contain CREATE DATABASE and USE statements, so the database must be created first when restoring.

In shell command:

shell> mysqladmin –u username –p create database name //Create database shell> mysql –u username –p database name < backup file.sql

Under the mysql command:

mysql> CREATE DATABASE IF NOT EXIST database_name;
mysql> USE database name;
mysql> source backup file.sql;

Note: The source command can only be executed in the cmd interface, and cannot be executed in the mysql tool. An error will be reported because cmd directly calls mysql.exe to execute commands.

Back up data in delimited text file format

Call mysqldump with the - -tab=dir_name option to back up the database, where dir_name represents the directory of the output files. In this directory, two files will be generated for each table to be backed up. For example, a table named t1 contains two files: t1.sql and t1.txt. The .sql file contains the CREATE TABLE statement. A line in the .txt file is a record in the data table, and column values ​​are separated by 'tab'.

Note: Using mysqldump with the --tab=dir_name option is best used only on a local server. Because if used on a remote server, the directory generated by - -tab will exist both on the local host and on the remote host, the .txt file will be written by the server in the remote host's directory, and the .sql file will be written in the local host's directory.

Call mysqldump with the -tab=dir_name option to back up the database
mysqldump –u 用戶名–p - -tab=dir_name 數據庫名

[Example] Use mysqldump with the - -tab=dir_name option to back up the database test and put it on drive D:

Tables in the test database:

Execute the backup command:

The output results:

Restore a backup file in delimited text file format. Use the mysql command to process the .sql file to restore the table structure, and then process the .txt file to load the records.

shell> mysql –u username –p database name < table name.sql //Restore table structure shell> mysqlimport –u username –p database name table name.txt //Restore records

Alternatively, you can use LOAD DATA INFILE instead of the mysqlimport command, but this time you must use the mysql command:

mysql> use database name; //Select databasemysql> LOAD DATA INFILE 'table name.txt' INTO TABLE table name; //Restore records

[Example] Restore the stucou table in the test database: Check the tables in the test database, and there is no stucou table:

Restore the stucou table structure using the stucou.sql file:

The stucou data table was restored successfully:

There are no records in the stucou data table:

Restore the stucou table records using the stucou.txt file:

Use mysql command to export the intermediate results of the query

Import query results into a text file

mysql is a feature-rich tool command. You can also use mysql to execute SQL instructions in command line mode and import query results into text files. Compared with mysqldump, the results exported by the mysql tool are more readable. If the MySQL server is a separate machine and the user is operating on a client, the user can use the mysql -e statement to import the data results into the client machine.

【Order】:
shell> mysql -u root -p --execute="SELECT 語句" dbname > filename.txt

This command uses the --execute option, which means executing the statement following this option and exiting. The following statement must be enclosed in double quotes.

dbname is the name of the database to be exported. Different columns in the exported file are separated by tabs. The first line contains the field names.

[Example] Use the mysql command to export the person table records of the test database to a text file:
shell> mysql -u root -p --execute="SELECT * FROM person;" test > C:\person3.txt

The content of person3.txt is as follows

ID Name Age job

1 green 29 lawer

2 suse 26 dancer

3 evans 27 sports man

4 mary 26 singer

As you can see, the person3.txt file contains the name of each field and each record. If a row of records has many fields, it may not be fully displayed in one row. You can use the -vertical parameter to split each record into multiple lines.

[Example] Use the mysql command to export the person table of the test database and use the –vertical parameter to display:
shell> mysql -u root -p --vertical --execute="SELECT * FROM person;" test > C:\person4.txt

*************************** 1. row ***************************

ID: 1

Name: green

Age: 29

job: lawer

*************************** 2. row ***************************

ID: 2

Name: suse

Age: 26

job: dancer

*************************** 3. row ***************************

ID: 3

Name: evans

Age: 27

job: sports man

*************************** 4. row ***************************

ID: 4

Name: mary

Age: 26

job: singer

If the records in the person table are too long, this display will be easier to read

Import the query results into an HTML file

Use the mysql command to export the person table records of the test library to an html file. The input statement is as follows

shell> mysql -u root -p --html --execute="SELECT * FROM PERSON;" test > C:\person5.html 

Import the query results into an XML file

If you want to export to an XML file, use the –xml option to use the mysql command to export the records in the person table of the test library to an XML file.

shell> mysql -u root -p --xml --execute="SELECT * FROM PERSON;" test > C:\person6.xml

<?xml version="1.0"?>

<resultset statement="SELECT * FROM PERSON" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

 <row>

  <field name="ID">1</field>

  <field name="Name">green</field>

  <field name="Age">29</field>

  <field name="job">lawyer</field>

 </row>

 <row>

  <field name="ID">2</field>

  <field name="Name">suse</field>

  <field name="Age">26</field>

  <field name="job">dancer</field>

 </row>

 <row>

  <field name="ID">3</field>

  <field name="Name">evans</field>

  <field name="Age">27</field>

  <field name="job">sports man</field>

 </row>

 <row>

  <field name="ID">4</field>

  <field name="Name">mary</field>

  <field name="Age">26</field>

  <field name="job">singer</field>

 </row>

</resultset>

This is the end of this article about the implementation of MySQL 5.7 mysqldump backup and recovery. For more relevant MySQL mysqldump backup and recovery content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!