MySQL data import Export Instance Tutorial manual

Mysqldump is a mysql-brought data import export tool, its official note is:

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

To export an entire database using the mysqldump command

The code is as follows	Copy Code
Mysqldump-h 127.0.0.1-p 3306-u root-p123456 crazyant–skip-lock-tables > Crazyant.sql

Where-H is followed by the host name followed by the port number,------the user name, followed by the password, and Crazyant is the name of the database to export;

This error occurred during the export process:

Mysqldump:got error:1044:access denied for user ' root ' @ ' 127.0.0.1 ' to database ' crazyant ' when using LOCK TABLE

So in the end, I add –skip-lock-tables this option, there is no error.

Set a table that does not need to be exported when the mysqldump command is exported

Sometimes there are tables in a database that we don't want to export, and you can use the mysqldump –ignore-table command to set the filtered table, which uses the following:

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

You can see the usage:

–ignore-table=database.table
• You can specify only one table at a time, and if you want to specify multiple tables, this option needs to be written multiple times
Use instance:

The code is as follows	Copy Code
Mysqldump-h 127.0.1.1-p 3306-u root-p123456 CRAZYANT–SKIP-LOCK-TABLES–IGNORE-TABLE=CRAZYANT.TABLE1–IGNORE-TABLE=CR Azyant.table2–ignore-table=crazyant.table3–ignore-table=crazyant.table4 > Crazyant.sql

To export only 1 tables or specify a table by using the mysqldump command

You can also follow the mysqldump database with the 1 tables you want to export to export only a single table.

The code is as follows	Copy Code
Mysqldump-h 127.0.0.1-p 3306-u root-p123456–skip-lock-tables crazyant table_a

You can also write multiple tables to export, separating them with spaces:

The code is as follows	Copy Code
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, just use the "database_name tablename1 tablename2 tablename3" Way to write out the table of the exported database.

To export a table structure only by using the mysqldump command

Mysqldump also has an option to export only the structure of the table, not the contents of the table:

-d,–no-data No row information

Use instance:

The code is as follows	Copy Code
Mysqldump–opt-d-H 127.0.0.1-p 3306-u root-p123456 crazyant–skip-lock-tables

Importing data using the source command

Using the source command of MySQL, you can load the mysqldump output directly into the datasheet;

Use the following methods:

The code is as follows	Copy Code
> 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, if it is a full volume import, sometimes it is necessary to use TRUNCATE table to clean up the data first;

Summarize

Using the mysqldump and source commands from MySQL, you can easily export the data from the database and import the exported data into the library.