MySQL数据库逻辑备份工具mysqldump介绍

下文给大家带来有关MySQL数据库逻辑备份工具mysqldump介绍内容，相信大家一定看过类似的文章。我们给大家带来的有何不同呢？一起来看看正文部分吧，相信看完MySQL数据库逻辑备份工具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=name Create tab-separated textfile for each table to given path. (Create .sql and .txt files.)       --fields-terminated-by=name Fields in the output file are terminated by the given string.  --fields-enclosed-by=name   Fields in the output file are enclosed by the given character.  --lines-terminated-by=name  Lines 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 tpcc100 > tpcc100.sql 备份指定的表[mysql@wallet01 ~]$ mysqldump -uroot -p tpcc100 customer >customer.sql 仅备份表结构[mysql@wallet01 ~]$ mysqldump -uroot -p --no-data tpcc100 customer >customer.sql 仅备份表记录[mysql@wallet01 ~]$ mysqldump -uroot -p --no-create-info tpcc100 customer >customer.sql 仅备份表中匹配条件的记录[mysql@wallet01 ~]$ mysqldump -uroot -p --where="c_state='z3'" tpcc100 customer >customer.sql 还原指定的库[mysql@wallet01 ~]$ mysql -uroot -p tpcc100 < tpcc100.sql 还原指定的表[mysql@wallet01 ~]$ mysql -uroot -p tpcc100 < customer.sql 表结构备份为sql文件，表记录备份为文本文件[mysql@wallet01 ~]$ mysqldump -uroot -p --tab=/var/lib/mysql-files \--fields-terminated-by=',' --fields-enclosed-by='"' --lines-terminated-by='\n' tpcc100 customer [mysql@wallet01 ~]$ cd /var/lib/mysql-files[mysql@wallet01 mysql-files]$ ls -lhtotal 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 tpcc100 < customer.sql 还原表记录[mysql@wallet01 ~]$ mysqlimport -uroot -p tpcc100 \--fields-terminated-by=',' \--fields-enclosed-by='"' \--lines-terminated-by='\n' /var/lib/mysql-files/customer.txtEnter password:  tpcc100.customer: Records: 300000  Deleted: 0  Skipped: 0  Warnings: 0

对于上文关于MySQL数据库逻辑备份工具mysqldump介绍，大家觉得是自己想要的吗？如果想要了解更多相关，可以继续关注我们的行业资讯板块。