About MySQL Data export import

Source: Internet
Author: User

Tools

Mysql
Mysqldump

application Example Export
  1. 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
  2. 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 
  3. 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
  4. 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 
  5. 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
  6. 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
  7. 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
  1. 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;
  2. 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; 
  3. 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;
  4. 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 ', ';
  5. 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

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.