Mysql Import Export Command parsing _mysql

Mysqldump is a utility that MySQL uses to store databases. It primarily produces a SQL script that contains the command create TABLE inserts that are required to re-create the database from scratch. Now let us study together!

First, Mysqldump: Database Backup Program
There are 3 different ways to invoke mysqldump:

mysqldump [Options] db_name [tables]
mysqldump [options]---database DB1 [DB2 DB3 ...]
mysqldump [Options]--all--database

If no table is specified or---database or--all--database option is used, the entire database is dumped.

1, backup a database.

mysqldump-uroot-p123456 mysql > Mysql_backup.sql

This backup database MySQL structure and data, the resulting SQL file will not create a database MySQL statement.

2, you can use a command backup mysql,test multiple databases:

Mysqldump-u root-p123456--database mysql Test > My_databases.sql

The generated SQL file has statements to create database MySQL and test

3. Back up all databases:

Mysqldump-u root-p123456--all-databases > All_databases.sql

4, export the MySQL database structure

 Mysqldump-u root-p123456-d--add-drop-table mysql > Mysql_define.sql

5, the export of a data and all data and GZ compression

Mysqldump-u root-p123456 MySQL | gzip > mysql.sql.gz

You can read the dump file back to the server like this:

MySQL db_name < backup-file.sql
mysql-e "Source/path-to--backup/backup-file.sql" db_name

Or restore from the GZ file

Gunzip-f < mysql.sql.gz | Mysql-u root-p123456 Test

Second, SELECT ... into outfile
SELECT ... A SELECT INTO outfile ' file_name ' can write the selected row to a file that is created on the server host.

SELECT ... Into outfile is the complement of the load data infile; The syntax for the exort_options part of the statement includes partial fields and lines clauses that are used concurrently with the load data infile statement.

In the following example, a file is generated with the values separated by commas. This format can be used by many programs

SELECT * into outfile '/tmp/result.txt ' 
FIELDS terminated by ', ' optionally enclosed by 
' ' 
LINES terminated B Y ' n ' from
Mysql.user;

Export data from the user table of the MySQL database to/tmp/result.txt

SELECT ... into outfile can only export data, can not export the structure, general and load data joint use.

Third, LOAD DATA INFILE
The load DATA infile statement is used to read rows from a text file at high speed and to mount a table. The file name must be a literal string.

The character set indicated by the CHARACTER_SET_DATABASE system variable is used to interpret the information in the file.

LOAD DATA local INFILE '/tmp/result.txt ' to TABLE test.user
FIELDS terminated by ', ' 
optionally enclosed by ' ' C3/>lines terminated by ' \ n '

Import the/tmp/result.txt data into the user table of the test database.

Iv. Import Export formats

FIELDS terminated by ', '   between field delimiters, number
optionally enclosed by ' "with" number to enclose the field, invalid numeric type LINES terminated by
' \ n ' The record spacer takes the \ n newline character.

The above is about the MySQL import and Export command of all introduction, I hope to be proficient in mastering the MySQL command to help