MySQL Data export and import

send a basic, about MySQL data Export Import article, the purpose is two:

1. Memo

2. For developers to test

Tools

Mysql/source Import
Mysqldump Export

Application ExamplesExport

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 (such as 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.   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 > db.nodata.sql   

12. Export data from a query SQL to a local directory (separated by "tabs" between data values)
    For example, SQL as ' 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 completes requires the flush privileges; Command

      
    
     the second method: 
    Log in to MySQL, execute the source command, followed by the absolute path of the file name.  
    ... 
    Mysql> Source/tmp/db.all.sql; 
     

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

    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 the absolute path of 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;
    # 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

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

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

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

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