MySQL data backup and restore learning notes

Data backup and restore

1.mysqldump

1.1 file Address :

E:\xampp\mysql\bin

File name: Mysqldump.exe

CMD down into Mysqldump.exe

CD E:\xampp_new\mysql\bin

1.2 Call Mysqldump The Parameters

Mysqldump–u user Name [-H host] [-P password] database name [ table name table name] > path and file name to backup to

Back up all tables for multiple databases:

Mysqldump–u user Name [-H host] [-P password]–databases Database 1 name Database 2 name > path and file name to backup to

backup MySQL of all databases:

Mysqldump–u user Name [-H host] [-P password]–all-databases > path and file name to backup to

Description

If you add a table name, you back up the specified table, and if you do not add a table name, it represents the entire table of the backup database.

1.3 Backup operation:

The number of backups specified by the data base:

Mysqldump-uroot-hlocalhost-p123 Homework>g:/homeworks20141124.sql

To back up the specified data table for the specified database:

Mysqldump-uroot-hlocalhost-p123 Homework Province>g:/homeworks20141124.sql

To back up a specified number of data tables for a specified database:

mysqldump-uroot-hlocalhost-p123 Homework Province users2>g:\ Learn \mysql\ log \1.sql

Back up all tables for multiple databases:

Mysqldump-uroot-hlocalhost-p123--databases Homework mysqlpart2>g:\ Learn \mysql\ log \2.sql

backup MySQL all databases under:

mysqldump-uroot-hlocalhost-p123--all-databases>g:\ Learning \mysql\ Log \3.sql

Description: mysqladmin password in: E:\xampp\phpMyAdmin\ config.inc.php file

1.4 mysqldump Other common parameters:

Add-drop-database If this parameter is added to the backup, a drop database is generated

Add-drop-tables If this parameter is added to the backup, a drop TABLES statement is generated

COMMENT=[0|1] When the backup file is generated, the system comments are added

Complete-insert If this parameter is added, the INSERT statement for all column names owned by the table name is generated when the backup file is generated

2. contents of the SQL file:

(1 ) MYSQLdump version number of the tool:

--MySQL dump 10.13 distrib 5.6.16, for Win32 (x86)

--Host:localhost Database:homework

-- ------------------------------------------------------

--Server version 5.6.16 (MySQL release number)

(2 ) variable assignment and comment content:

/*!40101 SET @[email protected] @CHARACTER_SET_CLIENT */;

Description

①/*! Note content */; Is the MySQL executable comment, MySQL executes the comment content, the other database does not execute these comments content.

②40101 is the minimum version of MySQL that supports executable statements

③ uses set to define a user-level variable, which is assigned by a system-level variable, and the system parameters of the database are stored in the form of a variable.

(3 ) Table Backup of the database

--

--Table structure for table ' province '

--

DROP TABLE IF EXISTS ' province ';

...

(4 ) to insert statement to save data to restore data when restoring a database

--

--Dumping data for table ' province '

--

LOCK TABLES ' province ' WRITE;

/*!40000 ALTER TABLE ' province ' DISABLE KEYS * *;

INSERT into ' Province ' VALUES (1, ' Beijing '), (2, ' Shanghai '), (3, ' Liaoning '), (4, ' Tianjin '), (5, ' Guangdong '), (6, ' Fujian '), (100, ' Jilin ');

/*!40000 ALTER TABLE ' province ' ENABLE KEYS */;

UNLOCK TABLES;

3. ways to back up data files

Back up all files in the Data folder:

E:\xampp\mysql\data\

4. Client Tool backup method:

Database can be recovered from SQL file

CREATE table to save the structure of tables

INSERT into saves data

5. Database Restore technology

5.1 MySQL command to restore the database

Mysql-u user name-P Password databases to restore < backup file path and file name

Mysql-u root-p 123 Homework <g: learn \mysql\ log \hw1.sql

5.2 restore using the Paste database file (this method is prohibited)

Through the operation found that the use of direct copy, paste database file method, the database is not feasible, will cause the database can not start, even if the subsequent deletion of log files, the database may be started, but also cannot browse the database table file.

5.3 Using Client Tools for restore

6. Import and export of tables

6.1 SELECT into OUTFILE Exporting data Tables

Exporting a data table simply exports the contents of a data table with no SQL statements:

SELECT statement into OUTFILE ' export path and filename '

[LINES TERMINATED by ' end of each line character ']

;

Parameter description:

Fields TERMINATED by ', ' column end character

What is the data for fields enclosed by ' \ ' is amplified (caused)

Fields escaoed by ' \ ' Define a new escape character symbol

LINES starting by ' > ' to what as the beginning of the line

LINES TERMINATED by ' \ r \ n ' with what as the end of the line

Example 1 : SELECT * from users2 into OUTFILE ' G:\users2.txt ';

Description

The path cannot contain Chinese

Example 2 : line-wrap export with escape character:

SELECT * from province to OUTFILE ' G:\1.txt ' LINES TERMINATED by ' \ r \ n ';

Show:

1 Beijing

2 Shanghai

3 Liaoning

4 Tianjin

5 Guangdong

6 Fujian

100 Jilin

6.2 using mysqldump tool to export table data:

The Mysqldump tool exports the data and data tables of the datasheet with the SQL table structure statement (without the INSERT statement, which does not contain the data contents of the data table)

Grammar:

mysqldump-t path to save the file Database name data table name-u User name –p Password

[--lines-terminated-by= line break]

Description

-T means to output

Parameter description:

--fields-terminated-by=, end of each column

--fields-optionally-enclosed-by=\ "What to refer to in each column

--fields-escaped-by=? Defining escape characters

--lines-terminated-by=\r\n what each line ends with

Example 1:

Mysqldump-t g:/Homework Province-u root-p

Example 2 to format a newline output table data:

Mysqldump-t g:/Homework Province-u root-p--lines-terminated-by=\r\n

6.3 using MYSQ To Export table data:

In a system environment (not in MySQL environment):

mysql-u root-p--execute= "Select statement " Database name > path and file name of the output file

Cases:

Mysql-u root-p--execute= "select * from Province" homework >g:/p.txt

Show:

ID Pro_name

1 Beijing

2 Shanghai

3 Liaoning

4 Tianjin

5 Guangdong

6 Fujian

100 Jilin

6.4 through the load DATA method to import data into the data table

LOAD DATA INFILE ' input file path and filename ' into TABLE ' the table name of the table to import;

LOAD DATA INFILE ' g:/p.txt ' into TABLE province;

LOAD DATA INFILE ' g:/p.txt ' into TABLE province CHARACTER SET UTF8;

Attention:

(1) Before importing TXT, the header of the field should be deleted, otherwise the header content will also be imported into the field as the value of the field.

(2) before importing TXT, you should save txt as utf-8 format, otherwise Chinese cannot import.

(3) before importing TXT, be sure to utf-8 the document to BOM header, or because there is a leading string, the error will be imported.

6.5 by Mysqlimport tools to import data into a data table

The command needs to be entered using the System's command window:

Mysqlimport-u user name-P Database name path and file name of the import table

Mysqlimport-u Root-p Homework G:/province.txt

  

Description

(1) If the name of the table is not specified, then the system will import the corresponding table according to the TXT file name, so the TXT file name should be the same as the data table.

(2) The name and value of the parameter for the import operation, to match the parameter name and value of the export operation, in order to successfully import the data.

MySQL data backup and restore learning notes