MySQL Data export and import

Tools

Mysql
Mysqldump

application Example Export

Export a full library backup to a local directory

mysqldump-u$user-p$passwd-h127.0.0.1-p3306--routines--default-character-set=utf8--lock-all-tables-- Add-drop-database-a > Db.all.sql   

Export the specified library to a local directory (e.g. MySQL library)

mysqldump-u$user-p$passwd-h127.0.0.1-p3306--routines--default-character-set=utf8--databases mysql > Db.sql  

Export a library's table to a local directory (for example, the user table of the MySQL library)

mysqldump-u$user-p$passwd-h127.0.0.1-p3306--routines--default-character-set=utf8--tables MySQL user> Db.table.sql  

Export a table of the specified library (data only) to a local directory (for example, the user table of the MySQL library with filters)

mysqldump-u$user-p$passwd-h127.0.0.1-p3306--routines--default-character-set=utf8--no-create-db--no-create-info --tables mysql user--where= "host= ' localhost '" > Db.table.sql

Export all table structures of a library

mysqldump-u$user-p$passwd-h127.0.0.1-p3306--routines--default-character-set=utf8--no-data--databases mysql > D B.nodata.sql     

Export data from a query SQL to a local directory (separated by "tabs" between data values)

For example, SQL is ' select User,host,password from Mysql.user; ' mysql-u$user-p$passwd-h127.0.0.1-p3306--default-character-set=utf8--skip-column-names-b-E ' select User,host, Password from Mysql.user; ' > Mysql_user.txt   

MySQL query results exported to file:

SELECT * from tables to  outfile '/tmp/tj_cps_20150906 '; MySQL  -h127.0.0.1  -uroot-pxxxx-p3306-e "SELECT * From table ">/tmp/tj_cps

Import

Restore the full library data to MySQL because it contains the permissions table for the MySQL library, and the import completion requires the flush privileges;

The first method: mysql-u$user-p$passwd-h127.0.0.1-p3306--default-character-set=utf8 < Db.all.sql The second method: Log in to MySQL, execute the source command , followed by an absolute path to the file name. ... mysql> source/tmp/db.all.sql;

Recover data from a library (user table for MySQL library)

Mysql> Source/tmp/db.table.sql;

Restore the TXT format file above the MySQL server (requires file permission, separated by "tabs" between the data values)

mysql> LOAD DATA INFILE '/tmp/mysql_user.txt ' into TABLE user;

Restore the CSV format file above the MySQL server (requires file permission, separated by a "comma" between the data values)

mysql-u$user-p$passwd-h127.0.0.1-p3306--default-character-set=utf8 ... mysql> use MySQL; mysql> LOAD DATA INFILE '/tmp/mysql_user.csv ' into TABLE user fields TERMINATED by ', ';

Restore local TXT or CSV files to MySQL

mysql-u$user-p$passwd-h127.0.0.1-p3306--default-character-set=utf8 ... mysql> use MySQL; # txt mysql> LOAD DATA LOCAL INFILE '/tmp/mysql_user.csv ' into TABLE user; # csv mysql> LOAD DATA LOCAL INFILE '/tmp/mysql_user.csv ' into TABLE user fields TERMINATED by ', ';

Precautions

About MySQL Connections
-u$user User Name
-P$PASSWD Password
-h127.0.0.1 If the remote service is connected
-p3306 Port
--default-character-set=ut
About MySQL Parameters
--skip-column-names not displayed
-B run MySQL in batch mode
-E after executing the command, exit
About the mysqldump parameter
-A full library backup
--routines Backup stored procedures and?
--default-character-set=ut
--lock-all-tables Global Consistency
--add-drop-database in every
--no-create-db not output creat
--no-create-info does not output cre
--databases the following parameters are
--tables The first parameter is a library name
About the Load data syntax
If the load data statement does not have the local keyword, the file is read directly on the MySQL server, and you want to have files permission.
If the local keyword is taken, the data file is read locally on the client and is uploaded to MySQL via the network.
The LOAD data statement, which is also logged to Binlog, is nothing more than an internal mechanism.

　

　　

　　

　　

　　

　　

　　

　　

　　

　　

　　

　　

MySQL Data export and import