About MySQL Data export import

Tools

Mysql
Mysqldump

application Example Export

2. 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

4. 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 

6. 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

8. 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 

10. 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 & Gt Db.nodata.sql

12. 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

14. Export the data for a query SQL to the MySQL server as a txt format file.
    Log in to MySQL and replace the default tab with a comma. (adapted to CSV format files).
    Specifies the path to MySQL to have write permissions. It is best to use the TMP directory, after the file is exhausted, then delete!

    SELECT User,host,password from Mysql.user to OUTFILE '/tmp/mysql_user.csv ' fields TERMINATED by ', ';

Import

2. 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, Executes the source command, followed by the absolute path to the file name .......mysql> source/tmp/db.all.sql;

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

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

6. Restore the TXT format file above the MySQL server (requires file permission, separated by "tabs" 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.txt ' into TABLE user;

8. 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 ', ';

10. 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;# txtmysql> Load data local INFILE '/tmp/mysql_user.csv ' into TABLE user;# csvmysql> LOAD data local INFILE '/tmp/mysql_user.csv ' I NTO TABLE user Fields TERMINATED by ', ';

Precautions

1. About MySQL Connections
   -u$user User Name
   -P$PASSWD Password
   -h127.0.0.1 If you are connecting to a remote server, replace it with the corresponding hostname or IP address
   -p3306 Port
   --default-character-set=utf8 specifying a character set
2. About MySQL Parameters
   --skip-column-names does not display the name of the data column
   -B runs the MySQL program in batch mode. The query results are displayed in the tab spacing format.
   -E after executing the command, exit
3. About the mysqldump parameter
   -A full library backup
   --routines Backup stored procedures and functions
   --default-character-set=utf8 Setting the character set
   --lock-all-tables Global Consistency Lock
   --add-drop-database execution of the drop table IF exist statement before each execution of the build statement
   --NO-CREATE-DB does not output the CREATE DATABASE statement
   --no-create-info do not output the CREATE TABLE statement
   --databases resolves the following arguments to the library name
   --tables The first parameter is the name of the table followed by the library name
4. 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.

About MySQL Data export import