MySQL Data backup

In order to prevent unscrupulous site crawler crawling articles, hereby identified, reproduced please indicate the source of the article. LAPLACEDEMON/SJQ.

Http://www.cnblogs.com/shijiaqi1066/p/3830007.html

1 mysqldump Command Backup

The mysqldump command backup backs up the data in the database into a text file. The file suffix of the mysqldump command backup is typically ". sql", and other suffix names are also available.

The mysqldump command generates a CREATE TABLE statement from the table structure, and the data in the table generates an INSERT statement.

Note:the mysqldump command is actually used in the shell environment. Commands in a non-database environment.

1.1 Backing up data sheets

Backup scripts that are generated by the backup table do not contain the CREATE TABLE statement .

Basic syntax:

Mysqldump-u user name-p [Password] database table 1 Table 2 ... > Script name

Example: Root user backs up the student table under the test database.

Mysqldump-u root-p Test Student > D:\student.sql

1.2 Backing up the database

Use the--databases option to back up the database.

Basic syntax:

Mysqldump-u user name-p [Password]--databases library 1 Library 2 ... > script name

Example: Use the root user to back up the test database and MySQL database.

Mysqldump-u root-p--databases test MySQL  >d:\backup.sql

1.3 Backing up all databases

Use the--all-databases option to back up all databases.

Basic syntax:

Mysqldump-u user name-p [password]--all-databases ... > Script name

Example: Use the root user to back up all the databases currently connected.

Mysqldump-u root-p--all-databases  > D:\all.sql

1.4 Data Restore

Use the MySQL command to restore the backed up data.

Basic syntax:

- - [ database ] < backup scripts

1.5 backup via MySQL storage file

For the MyISAM engine database, you can copy the MySQL storage file to implement the backup database. To back up the database in this way, ensure that the database major version number is the same .

In Linux using this method to back up data requires attention to file permissions.

2 Database Migration

Database migration moves the database from one system to another.

Database migrations are broadly categorized into three categories:

• MySQL migrates between databases of the same version.
• Migrated to other MySQL versions of the database.
• Migrated to other types of databases.

For the same version of MySQL database, you can use MySQL backup and restore technology to complete the database migration.

Example: Use the mysqldump command to back up and use the MySQL command to restore in another database.

Mysqldump-h host1-u user1-p Password1-all-database |  -H host2-u user2-p Password2

For different databases, the use of a similar approach may result in errors due to differences in SQL standard definitions. You can choose to use tools to do these things.

3 Import and export of Tables 3.1 with Select ... into outfile exporting text files

Grammar:

SELECT [ column name ]  from [WHERE statement ]  into ' target file ' [ options ];

Description

The SELECT clause is used to query the data.

Into OUTFILE is used to export data.

The destination file is the backup file for the exported data.

Options :

Field options:

Fields terminated by ' string ': Sets the field delimiter, which defaults to ' \ t '.

Fields enclosed by ' character ': sets the character that encloses the field. No characters are used by default.

Fields optionally enclosed by ' character ': sets the string to enclose the character type field in the Char,varchar,text. No characters are used by default.

Fields escaped by ' character ': Set escape character, default value '/'.

Line Options:

Lines starting by ' string ': Sets the character at the beginning of each line and does not use any characters by default.

Lines terminated by ' string ': sets the character to end each line, without using any characters by default.

Example: Export a record of the student table under the test library. Where the fields are separated by ",", and the character data is enclosed in double quotation marks. Each record starts with ">".

SELECT *  from. Student intoOUTFILE'D:\student1.txt'Fields TERMINATED by '\,'optionally enclosed by '\'LINES Starting by '\>'TERMINATED by '\ r \ n';

3.2 Exporting a text file with the mysqldump command

Basic syntax:

- - - [ options ]

Description

The parameter of the- T option is the path to the exported file.

Options :

--fields-terminated-by= string: Sets the field delimiter.

--fields-enclosed-by= Character: Sets the value of the field in the string.

--fields-optionally-enclosed-by= Character: Sets the value of the field in the string. Fields that can be used only for Char,verchar and text types.

--fields-escaped-by= character: Sets the escape character.

--lines-terminated-by= string: Row record delimiter.

Example: Export a record of the student table under the test library. Where the fields are separated by ",", the character data is enclosed in double quotation marks.

Mysqldump-u root-p111-T d:\test student        --fields-terminated-by =,        -- fields-optionally-enclosed-by =\"

Export XML-formatted files with the mysqldump command

Basic syntax:

Mysqldump-u User name-p password--xml  database table > target file

Description:--xml represents a file exported as an XML format, or you can use-X instead of--xml.

Example: Export the record of the student table under the test library as an XML file.

Mysqldump-u root-p111--xml Test student > D:\name.xml;

3.3 Exporting a text file with the MySQL command

MySQL commands can be used to log in to the MySQL server, back up data, or export text files.

Basic syntax:

" SELECT statement the database > destination file

Description: The parameter of the-e option is a SELECT statement that is used to find data.

Example: Export the student table under the test database

" SELECT * FROM student " Test > D:/student2.txt

Export XML and HTML files with the MySQL command

Export XML Basic Syntax:

Mysql-u User name-p password--xml-e "SELECT statement" database table > target file

Description:--xml represents a file exported as an XML format, or you can use-X instead of--xml.

To export the basic HTML syntax:

" SELECT statement the database table > destination file

Description:--html represents a file exported as an HTML format, or you can use-H instead of--html.

For more mysqldump options please refer to the official documentation or use the search engine query.

In order to prevent unscrupulous site crawler crawling articles, hereby identified, reproduced please indicate the source of the article. LAPLACEDEMON/SJQ.

http://www.cnblogs.com/shijiaqi1066/p/3830007.html