Summary of mysql database backup and restoration _ MySQL

Summary of mysql database backup and restoration bitsCN.com

Database operations are usually performed on data tables and tables. database backup and restoration are rarely used. this is a process of self-learning.

1. database backup

Use the mysqldump command. cause: the tool is used on multiple storage engines. In addition, the database files backed up by the tool can be used on SQL server and sybase as long as simple modifications are made.

Format: mysqldump-h localhost (enter the host name of the data)-u root (user name)-p db_name (name of the database to be backed up)> path (address to be backed up)

For example, in the dos window, enter mysqldump-u root-p db_name> E:/11. SQL;

If you only want to back up a table in the database separately, add the table name after the database name.

For example, backup Table 1 in the db_name database: mysqldump-u root-p db_nameTable1> E:/11. SQL;

If you want to back up multiple databases, add the keyword -- databases: mysqldump-u root-p-- DatabasesDb_name1Db_name2> E:/db. SQL; back up db_name1 and db_name2.

In addition, if databases is not added to back up the database, it cannot be recovered directly after the database that has been backed up is deleted directly, because "mysqldump-u root-p db_name> E:/db. SQL only backs up all the tables in the db_name DATABASE. the file does not include the CREATE DATABASE statement for creating the DATABASE. Therefore, if db_name is deleted, the DATABASE is used. the "No database selected" error message is displayed during SQL restoration. Therefore, you must create a db_name database and select it with use db_name before restoration. You can also directly use mysqldump-u root-p -- databases db_name> db. SQL during Backup. This not only backs up the table structure under the used database, but also includes statements for creating the database.

2. data restoration

If you have not logged on to the mysql database, mysql-h localhost-u root-p db_name (database name)

If you have logged on to mysql, use the source statement to restore the database: Select the database to restore in mysql, that is, use db_name; then run the source filename. SQL statement (file path name)

3. import and export tables

Mysql's key operations are data operations, while data is stored in tables. Therefore, direct operations on data are implemented through table import and export, there are multiple ways to import and export tables:

1. you have logged on to mysql and used select... into outfile to export the file. use load data infile to import the file.

Format: use db_name to enter a database

Select * from table_name into outfile "filepath.txt"; export table_name to the file in the filepath

Load data infile "filepath.txt" into table table_name; import data from filepath.txt to table table_name;

The preceding two statements have some optional [options] to specify the data format for import and export, such:

Select * from table_name into outfile "filepath.txt"

Fields

Terminated ','

Enclosed '/"'

Escaped '/''

Lines

Terminated by '/r/N'

Staring by '>'

Terminated by 'in fields,' indicates that the exported data fields are used and separated. enclosed by '/"' indicates that each field is enclosed by double quotation marks, escaped by '/' indicates defining escape characters as single quotation marks. Terminated by '/r/N' under lines indicates that each line of record ends with a carriage return newline character, and staring by'> 'indicates that each line starts with a'> 'character. Example:

The export data format without option is

1 Tale of AAA Dickes28 1995 novel 112 EmmaT Jane lura40 1993 drama 22

The export data format of the preceding option is

> "1", "Tale of AAA ",...
> "2", "EmmaT ",...

2. do not log on to mysql

Using the mysqldump command to export files is similar to backing up a database. the command is as follows:

Mysqldump-T path dbname table_name-u root-p

Path is the export path, and dbname is the database name table_name is the table name in the database.

You can also add option like select... into outfile. the options are as follows:

Mysqldump-T path dbname table_name-u root-p -- fields-terminated-by =, -- fields-enclosed-by =/"-- fields-escaped-by =? -- Lines-terminated-by =/r/n

Use mysql commands to export databases

Mysql-u root-p -- execute = "select statement" dbname> filename.txt

You can also use mysql to export html data and use the -- html option.

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

Mysql-u root-p -- xml -- execute = "select statement" dbname> filename. xml

Use mysqlimport to import text files

Mysqlimport-u root-p db_name filename.txt

You can also include optional parameters such as mysqldump.

Mysqlimport-u root-p db_name filename.txt -- fields-terminated-by =, -- fields-enclosed-by =/"-- fields-escaped-by =? -- Lines-terminated-by =/r/n

BitsCN.com