dump mysql database_【MySQL Database】数据迁移工具：mysqldump

[mysql@wallet01 ~]$ mysqldump --help

-u, --user=name 指定连接数据库服务器使用的用户

-p, --password 指定连接数据库服务器使用的密码

-P, --port=# 指定连接数据库服务器使用的端口

-h, --host=name 指定连接数据库服务器的主机名

-A, --all-databases 备份全部的数据库

-B, --databases 备份指定的数据库

-t, --no-create-info 仅备份表记录

-d, --no-data 仅备份表结构

-w, --where=name 仅备份表中匹配条件的记录

-E, --events 备份数据库的事件

-R, --routines 备份数据库的存储过程与函数

--triggers 备份表的触发器

-x, --lock-all-tables 锁定全部数据库中的全部表。

-T, --tab=nameCreate tab-separated textfile for each table to given path. (Create .sql and .txt files.)

--fields-terminated-by=nameFields in the output file are terminated by the given string.

--fields-enclosed-by=nameFields in the output file are enclosed by the given character.

--lines-terminated-by=nameLines in the output file are terminated by the given string.

--dump-slave[=#] This causes the binary log position and filename of the

master to be appended to the dumped data output. Setting

the value to 1, will printit as a CHANGE MASTER command

in the dumped data output; if equal to 2, that command

will be prefixed with a comment symbol. This option will

turn --lock-all-tables on, unless --single-transaction is

specified too (in which case a global read lock is only

taken a short time at the beginning of the dump - don't

forget to read about --single-transaction below). In all

cases any action on logs will happen at the exact moment

of the dump.Option automatically turns --lock-tables off.

--master-data[=#] This causes the binary log position and filename to be

appended to the output. If equal to 1, will print it as a

CHANGE MASTER command; if equal to 2, that command will

be prefixed with a comment symbol. This option will turn

--lock-all-tables on, unless --single-transaction is

specified too (in which case a global read lock is only

taken a short time at the beginning of the dump; don't

forget to read about --single-transaction below). In all

cases, any action on logs will happen at the exact moment

of the dump. Option automatically turns --lock-tables

off.

--single-transaction

Creates a consistent snapshot by dumping all tables in a

single transaction. Works ONLY for tables stored in

storage engines which support multiversioning (currently

only InnoDB does); the dump is NOT guaranteed to be

consistent for other storage engines. While a

--single-transaction dump is in process, to ensure a

valid dump file (correct table contents and binary log

position), no other connection should use the following

statements: ALTER TABLE, DROP TABLE, RENAME TABLE,

TRUNCATE TABLE, as consistent snapshot is not isolated

from them. Option automatically turns off --lock-tables.

备份指定的库

[mysql@wallet01 ~]$ mysqldump -uroot -p --databases soe > soe.sql

备份指定的表

[mysql@wallet01 ~]$ mysqldump -uroot -p soe customer >customer.sql

仅备份表结构

[mysql@wallet01 ~]$ mysqldump -uroot -p --no-data soe customer >customer.sql

仅备份表记录

[mysql@wallet01 ~]$ mysqldump -uroot -p --no-create-info soe customer >customer.sql

仅备份表中匹配条件的记录

[mysql@wallet01 ~]$ mysqldump -uroot -p --where="c_state='z3'" soe customer >customer.sql

还原指定的库

[mysql@wallet01 ~]$ mysql -uroot -p soe < soe.sql

还原指定的表

[mysql@wallet01 ~]$ mysql -uroot -p soe < customer.sql

表结构备份为sql文件，表记录备份为文本文件

[mysql@wallet01 ~]$ mysqldump -uroot -p --tab=/var/lib/mysql-files \

--fields-terminated-by=',' --fields-enclosed-by='"' --lines-terminated-by='\n' soe customer

[mysql@wallet01 ~]$ cd /var/lib/mysql-files

[mysql@wallet01 mysql-files]$ ls -lh

total 165M

-rw-rw-r-- 1 mysql mysql 2.5K Sep 18 10:08 customer.sql

-rw-rw-rw- 1 mysql mysql 165M Sep 18 10:08 customer.txt

还原表结构

[mysql@wallet01 ~]$ mysql -uroot -p soe < customer.sql

还原表记录

[mysql@wallet01 ~]$ mysqlimport -uroot -p soe \

--fields-terminated-by=',' \

--fields-enclosed-by='"' \

--lines-terminated-by='\n' /var/lib/mysql-files/customer.txt

Enter password:

soe.customer: Records: 300000 Deleted: 0 Skipped: 0 Warnings: 0