MySQL import and export data description

Document directory

• Export Full database to local

Export Full database to local

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 database to a local device

mysqldump -u$USER -p$PASSWD -h127.0.0.1 -P3306 --routines --default-character-set=utf8 --databases mysql > db.sql

Export the table of a database to a Local Machine

mysqldump -u$USER -p$PASSWD -h127.0.0.1 -P3306 --routines --default-character-set=utf8 --tables mysql user> db.table.sql

Export the table (data only) of the specified database to the local device.

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 Database

mysqldump -u$USER -p$PASSWD -h127.0.0.1 -P3306 --routines --default-character-set=utf8 --no-data --databases mysql > db.nodata.sql

Export the SQL result set to a local file in TXT format (data values are separated by "tabs)

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

Export the data of a query SQL to a TXT file on the MySQL server.

Log on to MySQL and replace the default tab with a comma (for CSV files). Specify the path. MySQL requires the write permission. It is best to use the tmp directory. After the file is used up, delete it!

SELECT user,host,password FROM mysql.user INTO OUTFILE '/tmp/mysql_user.csv' FIELDS TERMINATED BY ',';

Dump with where Condition

mysqldump -u$USER -p$PASSWD -h127.0.0.1 -P3306 --default-character-set=utf8 --tables --where="user=root" > mysql_user.sql

Import the full-database data to mysq. After the import is complete, execute flush.
Privileges; command

1. Method 1

   mysql -u$USER -p$PASSWD -h127.0.0.1 -P3306 --default-character-set=utf8 < db.all.sql

2. Method 2: log on to MySQL and run the source command. The file name following the command must use an absolute path.

   mysql> source /tmp/db.all.sql;

Import data of a Database

1. Method 1

   mysql -u$USER -p$PASSWD -h127.0.0.1 -P3306 --default-character-set=utf8 mysql < db.table.sql

2. Method 2: log on to MySQL and run the source command. The file name following the command must use an absolute path.

   $ mysql -u$USER -p$PASSWD -h127.0.0.1 -P3306 --default-character-set=utf8mysql> use mysql;mysql> source /tmp/db.table.sql;

Import TXT files on the MySQL server

• File Permission is required. data values are separated by "tabs ".

  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 ;

Restore CSV files on the MySQL server

• File Permission is required. data values are separated by commas (,).

  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;# txtmysql> LOAD DATA LOCAL INFILE '/tmp/mysql_user.csv' INTO TABLE user;# csvmysql> LOAD DATA LOCAL INFILE '/tmp/mysql_user.csv' INTO TABLE user FIELDS TERMINATED BY ',';

Note: MySQL connection

-U $ user User Name
-P $ passwd Password
-H127.0.0.1 Replace the host name or IP address of the remote server
-P3306 Port
-- Default-character-set = utf8 specifies the character set

MySQL Parameters

-- Skip-column-names: the name of the data column is not displayed.
-B. Run the MySQL program in batches. The query results are displayed in the tab interval format.
-E: exit after the command is executed.

Mysqldump Parameters

-A full database backup
-- Routines backup stored procedures and functions
-- Default-character-set = utf8
-- Lock-all-tables global consistency lock
-- Add-drop-Database: run the drop table if exist statement before each table creation statement.
-- No-create-DB does not output the create database statement
-- No-create-Info does not output the create table statement
-- Databases resolves all the following parameters to the Database Name
-- Tables the first parameter is the database name followed by the table name