MySQL Backup and recovery

MySQL backup can be divided into cold backup and hot backup two kinds.

Cold backup: Stop the database service for backup

Hot backup: Do not stop the database service for backup

MySQL storage engine for MyISAM, only support cold backup, you can directly copy the MySQL data directory under the database files. This approach requires attention to the MySQL version compatibility issue and, in order to ensure consistency, must be stopped or the lock table is backed up.

When recovering, first turn off the MySQL service, copy the backed up database files to the MySQL data directory, and then start the MySQL service.

When MySQL's storage engine is InnoDB, it supports hot backup because the InnoDB engine is a transactional storage engine that can perform redo and undo based on the logs, rollback the transaction that was not committed at the time of the backup, and redo the committed transaction.

MySQL provides the mysqldump command for the storage engine when the backup is InnoDB.

First, backup

mysqldump syntax and options can be viewed through the command line input mysqldump--help.

-- run the help command to see a list of syntax and complete options --  Help

This is because the environment variable is configured when MySQL is installed, so you can enter the command directly, otherwise you need to go to the bin directory of the MySQL installation directory. (Example: C:\soft\MySql\mysql-5.7.22-winx64\bin)

The mysqldump command backs up the specified database.

-- backup the specified database (demo) - - 127.0. 0.1 - >c:\Users\liufa\Desktop\2018-ten. sql

The mysqldump command backs up the specified tables in the specified database.

-- back up the specified table (student) in the specified database (demo) - - 127.0. 0.1 - >c:\Users\liufa\Desktop\Student_2018-ten. sql

The mysqldump command backs up multiple databases.

-- Backup multiple databases (demo, SYS) - - 127.0. 0.1 - -- databases Demo SYS >c:\users\liufa\desktop\2018-07-10.sql

The mysqldump command backs up all databases.

-- back up all databases - - 127.0. 0.1 - -- all-databases >c:\users\liufa\desktop\2018-07-10.sql

A few of the knowledge points in the backup SQL script file are briefly described.

--indicates that when MySQL version is 4.01.03 or more, the SQL statements in this case will be executed./*!40103 SET [email protected]_time_zone*/;--when importing in bulk, it is more efficient to rebuild the index at once by disabling the index and then opening the index after importing the data.--index of the Disabled table (student)Alter Tablestudent disable keys;--index of the enabled table (student)Alter Tablestudent enable keys;--sets a read lock on the specified table so that the thread and other threads can only read data from the table and cannot do any write operationsLock Tables Demo.studentRead;--Release Lockunlock tables;--sets the write lock for the specified table, so that the table has only the thread that owns the lock to read and write, and the other threads are blockedlock tables Demo.student write;--Release LockUnlock tables;

Second, recovery

The MySQL command reverts to the specified database.

-- revert to the specified database (Test) - - 127.0. 0.1 - <c:\Users\liufa\Desktop\Student_2018-ten. sql

If you have already logged in to MySQL, you can use this method to revert to the specified database, but only execute the source command under the CMD interface, and you cannot execute the source command inside the MySQL tool.

-- If you have already logged in to MySQL, you can use this method to revert to the specified database -- The Source command can only be executed under the CMD interface and cannot be executed in the MySQL tool  Use Test;source c:\Users\liufa\Desktop\Student_2018 -  - - ten. sql

Note: The database file backed up by Mysqldump, if the (--all-databases) or (--databases) option is used, the CREATE DATABASE and use statements are included in the backup file. Therefore, you do not need to specify a database name to restore the backup file.

If a single database is backed up by Mysqldump, and the (--databases) option is not used, the CREATE DATABASE and use statements are not included in the backup file, then you must first create the databases and specify the database when you restore.

Third, backup and recovery with--TAB=DIR_NAME option

Call mysqldump with the--tab=dir_name option to back up the database, the Dir_name represents the directory of the output file, and in this specified directory, each table that needs to be backed up will produce two files.

For example, for a table named T1, the backup generates two files, namely T1.sql and T1.txt.

The T1.sql file contains the CREATE TABLE statement, where a row in the T1.txt file corresponds to a record in the data table, and the column value is separated from the column value with ' tab '.

-- Backup with--TAB=DIR_NAME option - - 127.0. 0.1 - -- Tab=c:\users\liufa\desktop

A backup with the--tab=dir_name option restores the table structure first, and then restores the data in the table.

-- Restore Table Structure - - 127.0. 0.1 - <C:\Users\liufa\Desktop\student.sql

-- recovering data from a table - - 127.0. 0.1 -P test C:\Users\liufa\Desktop\student.txt

Iv. Export

Export using the Select...into outfile command.

--Select ... into outfile syntaxSelect *  fromTableNamewhere 1=1  intoOutFile'filename.txt' [OPTIONS][OPTIONS]: Fields terminated by 'string'                --indicates that the value of each field is split with the specified character, and can be single or multiple charactersFields[optionally]Enclosed by 'Char'    --indicates that the value of each field is wrapped with the specified character and can only be a single characterFields escaped by 'Char'                    --represents replacing the system default transfer character with the specified character, only for a single characterLines starting by 'string'                    --a character that represents the beginning of each line of data, which can be single or multiple characters, with no characters by defaultLines terminated by 'string'                 --a character that represents the end of each line of data, which can be single or multiple characters, with no characters by default--export the queried data to a specified text fileSelect *  fromDemo.student intoOutFile'C:\\users\\liufa\\desktop\\student.txt'Fields terminated by ','    --indicates that the fields are separated by commasOptionally enclosed by '\"'--Indicates that each field is enclosed in single quotation marks, plus the optionally keyword indicates that all values are placed between quotation marks, otherwise only fields of character data types such as char and varchar are quoted escaped by'\"'Lines terminated by '\ r \ n';--indicates that each row ends with a carriage return line break to ensure that each record occupies one row

Export using the MySQL command.

-- Grammar - - 127.0. 0.1 - -- execute= "SELECT statement" dbname >filename.txt

Use the MySQL command to export the data for the specified table that is queried in the specified database.

-- export the data from the table (student) in the Database (demo), the exported file row corresponds to a record in the data table, and columns are separated by tabs (' tab ') - - 127.0. 0.1 - -- execute= "select * from Student;" Demo >c:\users\liufa\desktop\student.txt

-- Export the data for the table (student) in the Database (demo), one row for each field, such as - - 127.0. 0.1 - -- Vertical--execute= "select * from Student;" Demo >c:\users\liufa\desktop\student.txt

-- export data from the table (student) in the Database (demo) to generate an HTML file - - 127.0. 0.1 - -- HTML--execute= "select * from Student;" Demo >c:\users\liufa\desktop\student.html

-- export data from the table (student) in the Database (demo) to generate an XML file - - 127.0. 0.1 - -- XML--execute= "select * from Student;" Demo >c:\users\liufa\desktop\student.xml

Five, import

Import using the Load Data infile command.

--load data infile syntax--If the keyword low_priority is specified, then MySQL will wait until no thread reads the current specified table before importing the data--If the keyword concurrent is specified, read and import can occur simultaneously--If the keyword Local is specified, the local file is importedLOADDATA[low_priority | CONCURRENT] [LOCAL]INFILE'filename.txt' --Replace and ignore controls the duplicate processing of unique key records for an existing table, and when replace is specified, the new row replaces the existing row with the same unique key value, and skips the input of the duplicate row of the existing row with the unique key when the ignore is specified. --If you do not specify any of the options, an error occurs when a duplicate key is found, and the remainder of the text file is ignored. [REPLACE | IGNORE]  into TABLETableName[Fields [TERMINATED by ' string ']     [[Optionally]Enclosed by 'Char']     [escaped by ' char '] ] [LINES [Starting by ' string ']     [TERMINATED by ' string '] ] [IGNORE number LINES] --represents the number of rows to ignore from the beginning, number indicates ignored rows[(Col_name_or_user_var,...)] [SET col_name = expr,...]--Import DataLoadData infile'C:\\users\\liufa\\desktop\\student.txt' Replace into Tabletest.student Fields Terminated by ','optionally enclosed by '\"'escaped by'\"'Lines terminated by '\ r \ n';

MySQL Backup and recovery