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