Shulou(Shulou.com)05/31 Report--

This article mainly explains the "Mysql how to import and export backup data", the article explains the content is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "Mysql how to import and export backup data" bar!

Export

Export full library backup to local directory

Mysqldump-u $USER-p $PASSWD-h227.0.0.1-P3306-routines-- default-character-set=utf8-- lock-all-tables-- add-drop-database-A > db.all.sql

Export the specified library to a local directory (for example, mysql library)

Mysqldump-u $USER-p $PASSWD-h227.0.0.1-P3306-- routines-- default-character-set=utf8-- databases mysql > db.sql

Export the table of a library to a local directory (for example, the user table of the mysql library)

Mysqldump-u $USER-p $PASSWD-h227.0.0.1-P3306-- routines-- default-character-set=utf8-- tables mysql user > db.table.sql

Export the table of the specified library (data only) to a local directory (for example, the user table of the mysql library with filter conditions)

Mysqldump-u $USER-p $PASSWD-h227.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 library

Mysqldump-u $USER-p $PASSWD-h227.0.0.1-P3306-routines-- default-character-set=utf8-- no-data-- databases mysql > db.nodata.sql

Export the data of a query sql to a txt format file to a local directory (data values are separated by "tabs")

For example, sql is' select user,host,password from mysql.user;'

Mysql-u $USER-p $PASSWD-h227.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 format file to a MySQL server.

Log in to MySQL and change the default tab to a comma. Adapt to csv format files.

For the specified path, mysql must have write permission. It's best to use the tmp directory and delete the files after you run out of them!

SELECT user,host,password FROM mysql.user INTO OUTFILE'/ tmp/mysql_user.csv' FIELDS TERMINATED BY','

Import

Restore the full database data to MySQL, because it contains the permission table of the mysql library, and the FLUSH PRIVILEGES; command needs to be executed when the import is completed.

The first method:

Mysql-u $USER-p $PASSWD-h227.0.0.1-P3306-- default-character-set=utf8

< db.all.sql 第二种方法: 登录MySQL,执行source命令,后面的文件名要用绝对路径. ...... mysql>

Source / tmp/db.all.sql

Restore the data of a library (user table of the mysql library)

The first method:

Mysql-u $USER-p $PASSWD-h227.0.0.1-P3306-- default-character-set=utf8 mysql

< db.table.sql 第二种方法: 登录MySQL,执行source命令,后面的文件名要用绝对路径. mysql -u $USER -p $PASSWD -h227.0.0.1 -P3306 --default-character-set=utf8 ...... mysql>

Use mysql

Mysql > source / tmp/db.table.sql

Restore txt format files on the MySQL server (requires FILE permission, and data values are separated by "tabs")

Mysql-u $USER-p $PASSWD-h227.0.0.1-P3306-- default-character-set=utf8

.

Mysql > use mysql

Mysql > LOAD DATA INFILE'/ tmp/mysql_user.txt' INTO TABLE user

Restore csv format files on the MySQL server (requires FILE permission, separated by "commas")

Mysql-u $USER-p $PASSWD-h227.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-h227.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','

Matters needing attention

About MySQL connection

-u $USER user name

-p $PASSWD password

-h227.0.0.1 if you connect to a remote server, replace it with the corresponding hostname or IP address

-P3306 port

-- default-character-set=utf8 specified character set

About mysql parameter

-- skip-column-names does not display the names of data columns

-B runs the mysql program in batch mode. The query results will be displayed in tab spacing format.

-e exit after executing the command

About mysqldump parameter

-A full database backup

-- routines backup stored procedures and functions

-- default-character-set=utf8 sets character set

-- lock-all-tables global consistency lock

Add-drop-database executes the DROP TABLE IF EXIST statement before each table-building statement

-- no-create-db does not output CREATE DATABASE statements

-- no-create-info does not output CREATE TABLE statements

-- databases resolves all the following parameters to the library name

-- the first parameter of tables is the library name, followed by the table name.

About LOAD DATA Grammar

If the LOAD DATA statement does not have the LOCAL keyword, read the file directly on the MySQL server and have FILE permission.

If you take the LOCAL keyword, read the data file locally on the client side and transfer it to MySQL.

The LOAD DATA statement, which is also logged to binlog, is an internal mechanism.

Thank you for your reading, the above is the content of "how Mysql imports and exports backup data". After the study of this article, I believe you have a deeper understanding of how Mysql imports and exports backup data, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.