MySQL Data Import/Export instance tutorial Manual

Mysqldump is a data import and export tool that comes with mysql. Its official notes are: shellmysqldump [options] db_name [tbl_name] shellmysqldump [options] databasesdb_nameshellmysqldump [options] all-databases export the entire database mysqldump-h127 using the mysqldump command

Mysqldump is a data import and export tool that comes with mysql. Its official notes are: shell mysqldump [options] db_name [tbl_name] shell mysqldump [options] databases db_name shell mysqldump [options] all-databases use the mysqldump command to export the entire database to mysqldump-h 127

Mysqldump is a data import and export tool that comes with mysql. Its official notes are:

Shell> mysqldump [options] db_name [tbl_name…]
Shell> mysqldump [options]-databases db_name...
Shell> mysqldump [options]-all-databases

Use the mysqldump command to export the entire database

Mysqldump-h 127.0.0.1-P 3306-u root-p123456 crazyant-skip-lock-tables> crazyant. SQL

-H is followed by the host name,-P is followed by the port number,-u is followed by the user name,-p is followed by the password, and crazyant is the name of the database to be exported;

This error occurs during the export process:

Mysqldump: Got error: 1044: Access denied for user 'root' @ '2017. 0.0.1 'to database 'crazyant' when using LOCK TABLE

So I added the-skip-lock-tables option at the end, so there is no error.

The mysqldump command is used to export tables that do not need to be exported.

Sometimes there are some tables in a database that we don't want to export. In this case, you can use the-ignore-table command of mysqldump to set the table to be filtered. The usage of this parameter is as follows:

-Ignore-table = name Do not dump the specified table. To specify more than one
Table to ignore, use the directive multiple times, once
For each table. Each table must be specified with both
Database and table names, e.g .,
-Ignore-table = database. table.

We can see its usage:

• -Ignore-table = database. table
• Only one table can be specified at a time. If you want to specify multiple tables, this option needs to be written multiple times.

Instance used:

Mysqldump-h 127.0.1.1-P 3306-u root-p123456 crazyant-skip-lock-tables-ignore-table = crazyant. table1-ignore-table = crazyant. table2-ignore-table = crazyant. table3-ignore-table = crazyant. table4> crazyant. SQL

Use the mysqldump command to export only one table or a specified table

You can also add one table to be exported after the database of mysqldump to export only a single table.

Mysqldump-h 127.0.0.1-P 3306-u root-p123456-skip-lock-tables crazyant table_a> dump_table_a. SQL

You can also write multiple tables to be exported and separate them with spaces:

Mysqldump-h 127.0.0.1-P 3306-u root-p123456-skip-lock-tables crazyant table_a table_ B> dump_two_tables. SQL

As you can see, you only need to use "database_name tablename1 tablename2 tablename3" to write the exported database table.

Use the mysqldump command to export only the table structure

Mysqldump also has an option to export only the table structure without the table content:

-D,-no-data No row information

Instance used:

Mysqldump-opt-d-h 127.0.0.1-P 3306-u root-p123456 crazyant-skip-lock-tables

Use the source command to import data

Use the mysql source command to Load the export result of mysqldump to the data table;

The usage is as follows:

> Mysql-h 127.0.1.1-P 3306-u root-p123456

Mysql> use crazyant;

Mysql> source/home/crazyant.net/mysqldump_crazyant. SQL

This completes the import. For full import, it is sometimes necessary to use the truncate table method to clear the data first;

Summary

Using MySQL's mysqldump and source commands, you can easily export database data and import exported data to the database;

Mysqldump official documentation address: http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

Reprinted please indicate Source: http://www.crazyant.net/mysql/1355.html

Original article address: MySQL Data Import/Export instance tutorial manual. Thank you for sharing it.