MySQL import and export data description

Source: Internet
Author: User
Tags mysql import
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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.