MYSQL5.6 Learning--mysqldump Backup and recovery

MySQL Backup

Cold backup : Stop the service from being backed up, that is, stop writing the database

Hot backup : Do not stop the service for backup (online)

L MySQL MyISAM engine only supports cold backup, InnoDB supports hot backup, Reason:

InnoDB engine is a transactional storage engine , each statement will write the log, and each statement in the log has a point in time, then in the backup, MySQL can be based on this log to redo and undo, the backup when the transaction is not committed to rollback, has been submitted for redo. But MyISAM not, MyISAM is no log, in order to ensure consistency, can only stop or lock the table for backup.

L INNODB does not support direct replication of the entire database directory and physical backup using the Mysqlhotcopy tool:

1. 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. MySQL's database directory location is not necessarily the same, under the Windows platform, MYSQL5.6 storage database directory usually defaults to ~\mysql\mysql Server 5.6\data, or other user-defined directory. This method is not available for INNODB storage engine tables. Data backed up using this method is best restored to the same version of the server, and different versions may not be compatible. In the recovery, you can directly copy the backup files to the MySQL data directory to achieve the restoration. When you restore this way, you must ensure that the database that backs up the data is the same as the major version number of the database server that you are restoring. And this is only valid for the MyISAM engine, which is not available for InnoDB engine tables. Perform a restore before shutting down the MySQL service, overwriting the backup file or directory with the MySQL data directory and starting the MySQL service.

2. Quick Backup using the Mysqlhotcopy tool

Mysqlhotcopy is a Perl script that was originally written and provided by Tim Bunce. He uses the lock TABLES, FLUSH TABLES, and CP or SCP to quickly back up the database. He is the quickest way to back up a database or a single table, but he can only run on the same machine as the database directory, and can only back up tables of type MyISAM.

Mysqldump Backup Brief

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

L Do not use the--tab=dir_name option, mysqldump generated data files are plain text SQL files, and create (database, table, storage path, etc.) statements and insert (record) statements. The output is saved as a file, and you can use the MySQL command to restore the backup file.

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

mysqldump syntax and options

"Command" shell> mysqldump-help

--all-databases means to back up all databases in the system, after using the--databases parameter, you must specify at least one database name, separated by spaces between multiple database names

"Common Options"

1)--add-drop-table

This option will precede each table with the drop table if exists statement, which will ensure that the MySQL database is returned without error, because each time it is returned, it will first check if the table exists, and it will be deleted.

2)--add-locks

This option binds the lock table and the Unlock table statement in the INSERT statement. This prevents other users from manipulating the table when these records are re-imported to the database

3)--tab

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

4)--quick or-opt

If you do not use the--quick or--opt option, then mysqldump will load the entire contents into memory before dumping the results. This can be problematic when you dump a database with large amounts of data. This option is turned on by default, but you can use--skip-opt to close it.

5)--skip-comments

Use--skip-comments to remove comment statements from the export file

6) –compact

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

Backing up data in SQL format

If the file name is backed up. SQL does not specify the path to be placed, it is placed by default in the ~\mysql\mysql Server 5.6\bin directory. However, you can specify the path to its backup file in the following ways:

mysqldump–h hostname –u User name –p--all-databases > C: \ backup file name. sql

L Call mysqldump with--all-databases option to back up all databases

Command mysqldump–h hostname –u user name –p--all-databases > backup file name. sql

"Example", take the ' test ' @ '% ' user as an example to view its database:

Back up all Databases (Test,test1) with the mysqldump with--all-databases option:

L Call mysqldump with--databases option to back up the specified database

"Command" Mysqldump–u user name –p--databases db1 DB2 db3 ... > backup file name. sql

"Example" backs up a specified database (such as Test,test1) with the mysqldump with--databases option

L Call Mysqldump to back up a specified database:

"Command 1" mysqldump–u user name –p--databases db > backup file name. sql

"Example 1" using the mysqldump with--databases option to back up a specified database (such as test)

or "Command 2" mysqldump–u user name –p db > backup file name. sql

"Example 2" to back up a specified database (such as test) with mysqldump without the--databases option

Note There are no CREATE DATABASE and use statements in the resulting backup file:

"Note" When backing up a database--databases allows omitting ("Command 2"), but omitting it results in a backup file name. There are no CREATE DATABASE and use statements in SQL, so you must specify a default database name when restoring the backup file. This server does not know which database the backup files are restored to, which can cause you to use a database name that differs from the original database name.

L Call Mysqldump to back up a few tables in a database:

"Command" MYSQLDUMP–U user name –p database name Table name 1 Table name 2 Table name 3 ... > backup file name. sql

Table in the "Example" Test database:

Back up the course and student tables in the database test with mysqldump:

Only create Table,insert course,student information is available in the Scdump.sql file.

Restore a backup file in SQL format

Files 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, so you do not need to specify a name to restore the backup file.

Under the shell command:

shell> mysql–u User name –p < backup file. sql

Under the MySQL command, import the backup file with the source command:

Mysql> source backup file. sql; Logged in to MySQL with the source command

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 the restore must be Create the database First .

Under the shell command:

shell> mysqladmin–u User name –p CREATE database name//creation database

shell> mysql–u User name –p database name < backup file. sql

Under the MySQL command:

mysql> CREATE database IF not EXIST name;

mysql> use database name;

Mysql> source backup file. sql;

Note: You can only execute the source command under the CMD interface, and you cannot execute the source command inside the MySQL tool, as the CMD is called directly mysql.exe to execute the command.

Backing up data in a delimited text file format

Call Mysqldump with the--tab=dir_name option to back up the database, then dir_name represents the directory of the output file, in which each table that needs to be backed up will produce two files. For a table named T1, contains two files: T1.sql and t1.txt. sql file contains the CREATE TABLE statement, a record in a behavior datasheet in a. txt file, and the column value is separated from the column value with ' tab '.

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

L Call mysqldump with--tab=dir_name option to back up the database

"Command" Mysqldump–u user name –p--tab=dir_name database name

The example uses the mysqldump with the--tab=dir_name option to back up the database test, placed under the D disk:

Table in database test:

To perform a backup command:

The result of the output:

Restore a delimited text file format backup file

Use the MySQL command to process the. sql file to restore the table structure, and then process the. txt file to load the record.

Command shell> mysql–u user name –p database name < table name. SQL//Restore table structure

shell> mysqlimport–u User name –p database name Table name. txt//restore record

Alternatively: You can use the load DATA INFILE to replace the Mysqlimport command, but this is done under the MySQL command:

mysql> use database name; Select Database

mysql> LOAD DATA INFILE ' table name '. ' Into table name; Restore Records

"Example" Recovery database test data Table Stucou table:

View the table in the test database without the Stucou table:

Restore the STUCOU table structure with the Stucou.sql file:

Stucou data Table Recovery succeeded:

There are no records in the Stucou data table:

Recover Stucou table records with Stucou.txt file:

Use the MySQL command to export the intermediate results of the query

L Import Query results into a text file

MySQL is a feature-rich tool command that uses MySQL to execute SQL instructions in command-line mode and import query results into a text file . The results are more readable than the Mysqldump,mysql tool exports. If the MySQL server is a separate machine, the user operates on a client, and the user wants to import the data results onto the client machine, which can use the MYSQL-E statement.

"Command":

shell> mysql-u root-p--execute= "SELECT statement" dbname > Filename.txt

The command uses the--execute option, which means that the statement following the option is executed and exits, followed by a statement that must be enclosed in double quotation marks.

DBName is the name of the database to be exported, tabs are used between the different columns in the exported file, and the first row contains the field names

"Example" uses the MySQL command to export the test library's person table to a text file:

shell> mysql-u root-p--execute= "select * from person;" Test > C:\person3.txt

The contents of Person3.txt are as follows

ID Name Age Job

1 Green lawer

2 suse Dancer

3 Evans Sports Man

4 Mary singer

As you can see, the Person3.txt file contains the name of each field and each record, and if a row of record fields is many, a row may not be fully displayed, you can use

--vertical parameter that divides each record into multiple lines of display

The "example" uses the MySQL command to export the test library's person table using the--vertical parameter display:

shell> mysql-u root-p--vertical--execute= "select * from person;" Test > C:\person4.txt

1. Row ***************************

ID: 1

Name:green

Age:

Job:lawer

2. Row ***************************

ID: 2

Name:suse

Age:

Job:dancer

3. Row ***************************

ID: 3

Name:evans

Age:

Job:sports Mans

4. Row ***************************

ID: 4

Name:mary

Age:

Job:singer

If the record in the person table is too long, the display will be easier to read

L Import the query results into an HTML file

Use the MySQL command to export the test library's person table to the HTML file, enter the following statement

shell> mysql-u root-p--html--execute= "select * from person;" Test > C:\person5.html

L Import the query results into an XML file

If you are exporting to an XML file, use the--xml option

Using the MySQL command to export the test library's person table 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" ></field>

<field name= "Job" >lawer</field>

</row>

<row>

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

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

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

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

</row>

<row>

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

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

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

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

</row>

<row>

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

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

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

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

</row>

</resultset>

"Reference" http://www.cnblogs.com/lyhabc/p/3842015.html

MYSQL5.6 Learning--mysqldump Backup and recovery