Common commands in MySQL Import Export Data command (MYSQLDUMP,SOURCE)

Today I want to export some of the data for half a day finally find a corresponding several methods

First look at the character set, the province of the export time is garbled

1. By order

The code is as follows	Copy Code
Mysql> show VARIABLES like "character_set%";

Determines whether to use the export character latin1 or UTF8 according to the corresponding character set value

2. Export by –where method

The code is as follows	Copy Code
mysqldump-uroot-p123456--default-character-set=latin1 i5a6 i5a6_data--where= "id < MB" > Bki5a6.sql

3. To create a new table structure and the original table the same copy of the data in the form of all the new table data can be exported

The code is as follows	Copy Code
INSERT into Table2 (field1,field2,...) SELECT value1,value2,... From Table1

Involve more MySQL backup Import Export description (binary export, etc.)

1. Export the entire database (–hex-blob for BLOB data, prevent garbled and import failures)

Mysqldump-u user name-p database name > exported file name

The code is as follows	Copy Code
Mysqldump-u root-p--default-character-set=gbk--hex-blob i5a6 > I5a6.sql

2. Export a table

Mysqldump-u user name-P database name Table name > exported file name

The code is as follows	Copy Code
Mysqldump-u root-p i5a6 users> i5a6.sql

3. Export a database structure

The code is as follows	Copy Code
Mysqldump-u Root-p-D--add-drop-table i5a6 >d:/i5a6.sql

-D No data –add-drop-table add a drop table before each CREATE statement

4. Import Database

Common source Commands

Enter the MySQL database console,

such as Mysql-u root-p

Mysql>use Database

Then use the source command, followed by the script file (such as the. SQL used here)

The code is as follows	Copy Code
Mysql>source D:/i5a6.sql

Another Appendix A backup shell script (backup.sh)

The code is as follows	Copy Code
Date_str= ' Date +%y%m%d ' Cd/data2/backup Mysqldump-h localhost-u Root--password=xxxxx-r-e\ --max_allowed_packet=1048576--net_buffer_length=16384 i5a6\ | gzip >/data2/backup/i5a6_$date_str.sql.gz

Finished, feel the use of mysqldump export time more convenient, flow after the use of a good time to read directly