Mysqldump: database backup program

Mysqldump: There are three ways for the database backup program to call mysqldump: mysqldump [options] db_name [tables] mysqldump [options] --- databaseDB1 [DB2DB3...] mysqldump [options] -- all -- database if no table is specified or --- database or -- all -- d is used

Mysqldump: There are three ways for the database backup program to call 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 -- d is used

Mysqldump: database backup program

There are three methods to callMysqldump:

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

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

Back up a database.

mysqldump -uroot -p123456 mysql > mysql_backup.sql

The structure and data of database mysql are backed up here, And the generated SQL file does not contain statements for creating database mysql.

You can use one command to back up mysql and test multiple databases:

mysqldump -u root -p123456 --database mysql test > my_databases.sql

The generated SQL file contains the statement for creating database mysql and test.

Back up all databases:

mysqldump -u root -p123456  --all-databases > all_databases.sql

Export the structure of the mysql database

 mysqldump -u root -p123456 -d --add-drop-table mysql > mysql_define.sql

Export all data of one data and compress it with gz

mysqldump -u root -p123456 mysql | gzip > mysql.sql.gz

The following code reads the dump file back to the server:

mysql db_name < backup-file.sqlmysql -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

More references http://dev.mysql.com/doc/refman/5.1/zh/client-side-scripts.html#mysqldump

SELECT... INTO OUTFILE

SELECT... into outfile'File_name'Select can write the selected row into a file, which is created on the server host.

SELECT... into outfile is the complement of load data infile. The syntax used for the exort_options part of the statement includes some FIELDS and LINES clauses, which are used together with the load data infile statement.

In the following example, a file is generated, and each value is separated by a comma. This format can be used by many programs.

SELECT * INTO OUTFILE '/tmp/result.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\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, but cannot export the structure. It is generally used together with load data.

More references http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#select

LOAD DATA INFILE

The load data infile statement is used to quickly read rows from a text file and LOAD a table. The file name must be a text string.

The character set indicated by character_set_database system variables is used to interpret information in the file.

LOAD DATA LOCAL INFILE '/tmp/result.txt' INTO TABLE test.userFIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'

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

More references http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#load-data