MySQL Backup Restore library command method parsing (long text)

On the MySQL database backup and restore method, here is the first to refer to the Backup tool: Mysqlhotcopy, using the Mysqlhotcopy tool for quick backup, then data restore, using the MySQL command restore, and finally need to export the database table. The detailed introduction also has to read this article.

1. Data backup

Regular backup of the database, so that in the event of unexpected circumstances, as far as possible to reduce the loss.

1. Use the mysqldump command to back up

Mysqldump is a database backup tool provided by MySQL, when the mysqldump command executes, the database is backed up into a text file containing multiple create and INSERT statements that can be used to recreate the table and insert the data;

"Using mysqldump to back up a single database"

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

"Use Mysqldump to back up specified tables in the database"

Mysqldump-u user-h host-p password dbname[tbname,[tbname ...] >filename.sql

"Backing up multiple databases with mysqldump"

Mysqldump-u user-h host-p password--databases[dbname,[dbname ...] >filename.sql

After you use 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 MyISAM, you should consider using mysqlhotcopy because you can backup and restore faster;

2. 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 way to keep backups consistent, you need to perform a lock TABLES operation on the related tables before backing up, then flush TABLES the table (ensure that all active index pages are written 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 tables in the InnoDB storage engine. Using this method to back up data is best to restore 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 be run on the same machine as the database directory, and only MyISAM and archive types of tables can be backed up;

2. Data restore

1. Restore using MySQL command

Mysql-u username-p [dbname] < Filename.sql

Note: If the Filename.sql file is created for the Mysqldump tool that contains the file that created the database statement, you do not need to specify the database name when executing;

If you are already logged in to the MySQL server, you can also import the SQL file using the source command.

SOURCE filename

Tip: Before you execute the source command, you must select the database by using the USE statement. Otherwise, errors will occur during the recovery process;

2. Copy directly to the database directory

If the database is backed up by copying the database files, you can copy the backed up files directly to the MySQL data directory for restoration.

In this way, the backup database must be kept the same as the major version number of the database server you are restoring. And this method is only valid for the MyISAM engine table, the table for the InnoDB engine is not available;

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.

For the Linux/unix operating system, the file needs to be copied to the user or user group to the MySQL running users and groups, usually the user is MySQL, the group is also MySQL;

3.mysqlhotcopy Fast Recovery

Mysqlhotcopy backup files can also be used to restore the database, when the MySQL server stopped running, the backup database files copied to the location of MySQL (MySQL data folder), restart the MySQL service.

If you perform this operation as a root user, you must specify the owner of the database file

Chown-r Mysql.mysql/var/lib/mysql/dbname

Cp-r/usr/backup/test Usr/local/mysql/data

After executing the statement, restart the server, and MySQL will revert to the backup state

Tip: If the database that needs to be recovered already exists, the recovery succeeds after using the drop statement to delete the existing database, and the MySQL version must be compatible;

3. Database Migration

Database migration is the movement of data from one system to another. Data migration has the following reasons:

1. Migration between MySQL databases of the same version

Migration between the same version of MySQL database means that the database moves between MySQL databases with the same major version number.

Example:

Migrate the MySQL database on the www.abc.com host to the www.bcd.com host:

Mysqldump-h www.abc.com-u Root-ppassword dbname | Mysql-h Www.bcd.com-uroot-ppassword

Description

Mysqldump imported data is imported directly into the host Www.bcd.com database via the pipe character |, to the MySQL command, dbname the name of the database to be migrated, and if you need to migrate all the databases, you can use the parameters--all-databases

2. Migration between different versions of MySQL databases

MySQL server upgrade, you need to stop the service, and then uninstall the old version, and install a new version of MySQL, this update method is very simple, if you want to retain the old version of the user access control information, you need to back up MySQL database, after the new version of MySQL installation, Re-read the information in the MySQL backup file;

If the old version and the new version of the character set are not the same, the migration process needs to modify the default character set, otherwise it may not display the results properly;

For InnoDB engine tables, it is generally only possible to export data using the Mysqldump tool and then use the MySQL command to import to the target server.

When migrating data from a new version to an older version, special care is required, preferably exported using the mysqldump command, and then imported into the target database;

3. Migration between different databases

Database migrations can use tools such as MYODBC to enable migration between MySQL and SQL Server using the Windows system.

MySQL's official tool MySQL Migration Toolkit also enables data migration between different databases;

4. Export and import of tables

The data in the MySQL database can be exported to a SQL text file, an XML file, or an HTML file.

1. Use Select ... into outfile exporting text files

When the MySQL database exports data, the data export operation is allowed using the SELECT statement that contains the export definition. The file is created on the server host, so you must have the file Write permission (filename permission) to use this syntax.

Syntax format:

SELECT columnlist FORM table WHERE condition into OUTFILE ' filename ' [OPTIONS]

[Options] Options:

Fields TERMINATED by ' value '

fields [optionally] enclosed by ' value '

Fields escaped by ' value '

LINES starting by ' value '

LINES TERMINATED by ' value '

Description: FileName cannot be a file that already exists;

The Options section syntax includes the syntax and lines clauses of the fields section, and the possible values are:

Fields TERMINATED by ' value ':

Sets the separator character between fields, which can be single or multiple characters, tab ' \ t ' by default

fields [optionally] enclosed by ' value ':

Sets the bounding character of a field, only a single character, and 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, only a single character, that is, set the escape character, and the default value is "\"

LINES starting by ' value ':

Sets the start character for each line of data, which can be single or multiple, without using any characters by default

LINES TERMINATED by ' value ':

The character that sets the end of each line of data can be single or multiple characters, and the default value is ' \ n ';

Note: Fields and lines two clauses are optional, and if specified at the same time, fields must be in front of lines;

2. Export a text file using the mysqldump command

Mysqldump tool can not only export data to include create, insert SQL file, can also be exported as plain text files;

Mysqldump-t path-u root-p dbname [tables] [OPTIONS]

--options options:

--fields-terminated-by=value

--fields-enclosed-by=value

--fields-optionally-enclosed-by=value

--fields-escaped-by=value

--lines-terminated-end-by=value

Note: only the T parameter can be exported as a plain text file; The path represents the directory where the data is exported; tables specifies the name of the table to export, and if not specified, all tables in the database dbname are exported;

[Options] Value:

--fields-terminated-by=value:

Sets the separator character between fields, which can be single or multiple characters, tab ' \ t ' by default

--fields-enclosed-by=value:

Sets the bounding character of the field;

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

Sets the bounding character of a field, only a single character, and 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, can only be a single character, and set the escape character, the default is the backslash "\";

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

Sets the character at the end of each line of data, which can be single or multiple characters, and the default value is ' \ n '

3. Exporting a text file using the MySQL command

MySQL is a feature-rich tool command that uses MySQL to execute SQL instructions in command-line mode to 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 is on a client operation, the user to import data into the client machine, you can use the MYSQL-E statement;

The basic format for exporting data text file statements using MySQL is as follows:

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

You can also specify the display format of the query results by using the MySQL command:

If a row records a lot of fields, perhaps a row can not be fully displayed, you can use the--vartical parameter, each record is divided into multiple lines display;

"Export query results to an HTML file"

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

"Export query results to an XML file"

Mysql-u root-p--xml--execute= "SELECT statement" dbname > Filename.xml

4. Import a text file using the load DATA infile method

The LOAD DATA INFILE statement is used to read rows from a text file at high speed and load a table. The file name must be a literal 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 are also specified in the RESTORE statement to ensure the integrity and correctness of the data after the restore.

--options Options

Fields TERMINATED by ' value '

fields [optionally] enclosed by ' value '

Fields escaped by ' value '

LINES starting by ' value '

LINES TERMINATED by ' value '

You can see in the load data statement that the filename file after the keyword infile is the source of the imported data;

TableName indicates the name of the data table to be imported;

The Options section syntax includes the syntax and lines clauses of the fields section, and the possible values are:

Fields TERMINATED by ' value ':

Sets the separator character between fields, which can be single or multiple characters, tab ' \ t ' by default

fields [optionally] enclosed by ' value ':

Sets the bounding character of a field, only a single character, and 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, only a single character, that is, set the escape character, and the default value is "\"

LINES starting by ' value ':

Sets the start character for each line of data, which can be single or multiple, without using any characters by default

LINES TERMINATED by ' value ':

The character that sets the end of each line of data can be single or multiple characters, and the default value is ' \ n ';

[IGNORE number LINES]

The option indicates the number of rows to omit at the beginning of the file, and number represents the ignored row count. File permission is required to execute the load data statement;

5. Import a text file using the Mysqlimport command

You can import a text file using the Mysqlimport command, and you do not need to log in to the MySQL client.

Use the Mysqlimport statement to specify the options you want, the name of the imported database, and the path and name of the imported data file.

The basic syntax for the Mysqlimport command is as follows:

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

[Options] Value:

--fields-terminated-by=value:

Sets the separator character between fields, which can be single or multiple characters, tab ' \ t ' by default

--fields-enclosed-by=value:

Sets the bounding character of the field;

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

Sets the bounding character of a field, only a single character, and 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, can only be a single character, and set the escape character, the default is the backslash "\";

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

Sets the character at the end of each line of data, which can be single or multiple characters, and the default value is ' \ n '

--ignore-lines=n

Ignoring the first n rows 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 import file name, that is, the file name is the table name, and the table must exist before importing the data.

Related recommendations:

MySQL Database backup restore command

MySQL database backup and restore command memo