mysqldump的基本用法-白红宇

mysqldump的基本用法

阅读量：2448 次

发布时间：2019-05-10

本文共 3507 字，大约阅读时间需要 11 分钟。

Mysqldump为逻辑备份工具，通过sql或文本方式导出数据；

运行该命令的用户对要导出的对象应该有select/show view/trigger权限以导出相应表/视图/触发器，如果不指定—single-transaction还应具有lock tables权限；

限制

不适合大规模数据备份：如果以InnoDB表为主，采用mysqlbackup进行物理备份；如果MyISAM为主，则采用mysqlhotcopy；

工作机制

可逐行抽取，也可缓存整个表内容然后一次dump，但对于大表则比较消耗内存；

--quick采用row-by-row模式，也可使用—opt(包含--quick)；--skip-quick则使用缓存机制；

Mysqldump可生成两种文件：文本文件和sql文件；

--tab=dirname选项为每个表生成两个文件sql和txt(select … into outfile，需要file权限)

选项

复制相关

--dump-slave：生成change master语句

--apply-slave-statements：指定—dump-slave时，在change master to前后添加stop slave/start slave

--include-master-host-port：对生成的change master添加master_host/master_port选项

--master-data(1|2)：可用于创建slave，该选项会输出change master to 并包含binlog的具体位置，在导入slave后就不必再手工配置change master to信息；如果值为2则以注释的方式生成；

此选项会自动禁止—lock-tables同时激活—lock-all-tables(除非指定了—single-transaction)；

需要reload权限，并且启用了二进制日志；

Sql相关

--add-drop-table：每个create table前添加drop table

--complete-insert：生成完整的insert，包含列名

--no-data：不dump表内容

网络相关

--compress/-C：压缩服务器/客户端之间的通信信息

--max_allowed_packet：

--net_buffer_length：TCP/IP的buffer大小，当使用—extended-insert生成insert … values时，每个insert受限于net_buffer_length，同时注意mysql服务器中该变量的设置；

性能相关

--delayed-insert：生成insert delayed语句，5.6.6起不再支持delayed insert

--extended-insert：生成insert … values，导入时可批量执行，减少sql parse

--disable-keys：仅用于MyISAM表的非唯一索引，在导入所有行后再创建索引；每个表的insert前后分别有

/*!40000 ALTER TABLE tbl_name DISABLE KEYS */

……

/*!40000 ALTER TABLE tbl_name ENABLE KEYS */

--opt：为多个选项集合--add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset

--quick：row-by-row导出数据

事务相关

--flush-logs：在dump前刷新日志文件，需要reload权限

--add-locks：每个表前后生成lock tables/unlock tables

--lock-all-tables/-x：获取global read lock锁定所有表，与--single-transaction/--lock-tables不兼容

--lock-tables/-l：对于每个即将dump的db，提前锁定其下所有表；对MyISAM采用read local，InnoDB最好采用--single-transaction

--no-autocommit：将每个表的所有insert添加set autocommit=0/commit

--single-transaction：只用于事务表，不必锁定要导出的表；将事务隔离级别设置为repeatable read，在dump数据前执行start transaction；

注：5.5.3之前，使用该选项时相应表不能进行alter/create/drop/rename/truncate table操作，一致性读不能隔离这些操作；而5.5.3引入metadata lock解决了这一问题；

此选项在dump之前需要获取global read lock

Assume that we make a full backup of all our InnoDB tables in all databases using the following command on Sunday at 1 p.m., when load is low:

shell> mysqldump --single-transaction --all-databases > backup_sunday_1_PM.sql

The resulting .sql file produced by mysqldump contains a set of SQL INSERT statements that can be used to reload the dumped tables at a later time.

This backup operation acquires a global read lock on all tables at the beginning of the dump (using FLUSH TABLES WITH READ LOCK). As soon as this lock has been acquired, the binary log coordinates are read and the lock is released. If long updating statements are running when the FLUSH statement is issued, the backup operation may stall until those statements finish. After that, the dump becomes lock-free and does not disturb reads and writes on the tables.

用途

复制数据库

Mysqldump db1 > dump.sql –不能指定—databases，否则文件开头会有use db1

Mysqladmin create db2

Mysql db2 < dump.sql

导出存储程序

--events：导出scheduler event

--routines：存储过程和函数

--triggers：触发器，默认导出

这些选项分别由对应的skip-***，

分别导出表定义和内容

--no-data：只导出表定义