mysqldump使用教程

最新推荐文章于 2024-01-09 16:27:53 发布

weixin_30731287

最新推荐文章于 2024-01-09 16:27:53 发布

阅读量221

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/reaperhero/articles/10567691.html

版权

mysqldump 默认选项

add-drop-table                    TRUE 表示在生成表结构语句之前，生成对应的 DROP TABLE IF EXISTS `table_name`; 语句
add-locks                         TRUE 表示在生成表中数据的 insert into `table_name` values(...) 之前生成 LOCK TABLES `tab` WRITE;语句
comments                          TRUE 表示生成备注，就是所有 -- 开头的说明，比如：-- Dumping data for  for table `tab`. 最好还是启用；
create-options                    TRUE 表示在生成表结构时会生成：ENGINE=InnoDB AUTO_INCREMENT=827 DEFAULT CHARSET=utf8; 附加建表选项
default-character-set             utf8 指定语句：/*!40101 SET NAMES utf8 */;中的字符集；可能你需要改成 --default-character-set=utf8mb4
disable-keys                      TRUE 表示生产 insert 语句之前，生成：/*!40000 ALTER TABLE `tbl` DISABLE KEYS */; 可以加快insert速度；
extended-insert                   TRUE 表示生产的insert是insert into `tbl` values(...),(...),数据行按照net-buffer-length分割合并成多个batch insert
lock-tables                       TRUE 表示在导出的过程中会锁定所有表；
max-allowed-packet                25165824 最大支持 24M 的数据包；
net-buffer-length                 1046528  1M大小的socket buffer
quick                             TRUE 表示在导出语句时，不缓存，直接输出到控制台或者文件中；
quote-names                       TRUE 表示对表名和列名使用 `` 符号包裹；防止它们是关键字时会出错;
set-charset                       TRUE default-character-set=utf8指定字符集，而--set-charset=1/0 表示是否生成/*!40101 SET NAMES utf8 */; 
dump-date                         TRUE 表示是否在导出文件的末尾生成导出时间：-- Dump completed on 2015-09-15 11:15:10 
secure-auth                       TRUE 表示登录判断密码时使用新的加密算法，拒绝就的加密算法
triggers                          TRUE 表示生成触发器脚本；
tz-utc                            TRUE 表示是否生成：/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */;

mysqldump锁实现机制

在 my.cnf 中的[mysqld] 参数段加入
general_log=on
general_log_file=general.log

1）--lock-tables 会在整个导出过程 lock read local 所有的表。该锁不会阻止其它session读和插入，但是显然阻塞了update
2）--lock-all-tables 它请求发起一个全局的读锁，会阻止对所有表的写入操作(insert,update,delete)，以此来确保数据的一致性。备份完成后，该会话断开，会自动解锁
3）--single-transaction 和 --master-data 结合使用时，也是在开始时，会短暂的请求一个全局的读锁，会阻止对所有表的写入操作
4）--single-transaction 单独使用，不会有任何锁。但是测试表明: 它也需要对备份的表持有 metadata lock 的共享锁

--lock-tables

mysqldump -uroot -p --databases gs --lock-tables > gs_l.sql， 然后查看 general.log


Init DB   gs
Query     SHOW CREATE DATABASE IF NOT EXISTS `gs`
Query     show tables
Query     LOCK TABLES `tb1` READ /*!32311 LOCAL */,`user` READ /*!32311 LOCAL */
Query     show table status like 'tb1'
Query     SET SQL_QUOTE_SHOW_CREATE=1
Query     SET SESSION character_set_results = 'binary'
Query     show create table `tb1`
Query     SET SESSION character_set_results = 'utf8'
Query     show fields from `tb1`
Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `tb1`
Query     SET SESSION character_set_results = 'binary'
Query     use `gs`
Query     select @@collation_database
Query     SHOW TRIGGERS LIKE 'tb1'
Query     SET SESSION character_set_results = 'utf8'
Query     show table status like 'user'
Query     SET SQL_QUOTE_SHOW_CREATE=1
Query     SET SESSION character_set_results = 'binary'
Query     show create table `user`
Query     SET SESSION character_set_results = 'utf8'
Query     show fields from `user`
Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `user`
Query     SET SESSION character_set_results = 'binary'
Query     use `gs`
Query     select @@collation_database
Query     SHOW TRIGGERS LIKE 'user'
Query     SET SESSION character_set_results = 'utf8'
Query     UNLOCK TABLES
Quit

第一步： SHOW CREATE DATABASE IF NOT EXISTS `gs`; 导出了建库语句；
第二步：show tables; 获得数据库中所有表名，然后锁住：LOCK TABLES `tb1` READ /*!32311 LOCAL */,`user` READ /*!32311 LOCAL */              
        使用的是 lock table tb1 read local, tb2 read local, tb3 read local; 语句
第三步：show create table 'tb1'; 导出了 tb1 的建表语句；
第四步：show fields from `tb1`; SELECT /*!40001 SQL_NO_CACHE */ * FROM `tb1`; 导出了表中的数据；
最后导出了 trigger, 最后的最后 unlock tables; 结束。
可以看到 --lock-tables 在导出一个数据库时，会在整个导出过程 lock read local 所有的表。该锁不会阻止其它session读和插入。

--lock-all-tables

mysqldump -uroot -p --databases gs --lock-all-tables > gs_l.sql， 在查看 general.log

14:34:13     4 Connect   root@localhost on
Query     /*!40100 SET @@SQL_MODE='' */
Query     /*!40103 SET TIME_ZONE='+00:00' */
Query     FLUSH TABLES
Query     FLUSH TABLES WITH READ LOCK
Query     SHOW VARIABLES LIKE 'gtid\_mode'
                    ... ...
Init DB   gs
Query     SHOW CREATE DATABASE IF NOT EXISTS `gs`
Query     show tables
                    ... ...4 Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `tb1`
Query     SET SESSION character_set_results = 'binary'
Query     use `gs`
Query     select @@collation_database
                    ... ...
Query     show fields from `user`
Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `user`
Query     SET SESSION character_set_results = 'binary'
Query     use `gs`
Query     select @@collation_database
Query     SHOW TRIGGERS LIKE 'user'
Query     SET SESSION character_set_results = 'utf8'
Quit

它的实现使用了 FLUSH TABLES; FLUSH TABLES WITH READ LOCK; 语句。在最后没有看到解锁语句。它请求发起一个全局的读锁，会阻止对所有表的写入操作，以此来确保数据的一致性。备份完成后，该会话断开，会自动解锁。

--single-transaction

mysqldump -uroot -p --databases gs --single-transaction > gs_l.sql，在查看 general.log
14:41:34     5 Connect   root@localhost on
Query     /*!40100 SET @@SQL_MODE='' */
Query     /*!40103 SET TIME_ZONE='+00:00' */
Query     SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
Query     START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
Query     SHOW VARIABLES LIKE 'gtid\_mode'
Query     UNLOCK TABLES
                    ... ...
Init DB   gs
Query     SHOW CREATE DATABASE IF NOT EXISTS `gs`
Query     SAVEPOINT sp
                    ......
Query     show create table `tb1`
                    ......5 Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `tb1`
                   ......
Query     ROLLBACK TO SAVEPOINT sp
                   ......
Query     show create table `user`
                    .....5 Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `user`
                    ......
Query     ROLLBACK TO SAVEPOINT sp
Query     RELEASE SAVEPOINT sp
Quit

基本过程是：
先改变事务隔离级别：SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
开始事务：START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
unlock tables;
导出建库语句； SHOW CREATE DATABASE IF NOT EXISTS `gs`
打开一个 savepoint: SAVEPOINT sp;
导出 表 tb1 的结构和数据；
ROLLBACK TO SAVEPOINT sp; 回滚到savepoint;
对其它表重复该过程；
最后 realease savepoint p; 释放savepoint;
整个过程，没有任何锁。RR隔离级别保证在事务中只读取本事务之前的一致性的数据。 rollback to savepoint sp; 保证了对数据库中的数据没有影响。

--master-data

mysqldump -uroot -p --databases gs --master-data=1 > gs_l.sql，在查看general.log

14:57:45     6 Connect   root@localhost on
Query     /*!40100 SET @@SQL_MODE='' */
Query     /*!40103 SET TIME_ZONE='+00:00' */
Query     FLUSH /*!40101 LOCAL */ TABLES
Query     FLUSH TABLES WITH READ LOCK
Query     SHOW VARIABLES LIKE 'gtid\_mode'
Query     SHOW MASTER STATUS

发现了 --lock-all-tables一样，就多了一句：show master status;

--single-transaction 和 --master-data 结合

mysqldump -uroot -p --databases gs --master-data=1 > gs_l.sql，在查看general.log

15:00:56     7 Connect   root@localhost on
Query     /*!40100 SET @@SQL_MODE='' */
Query     /*!40103 SET TIME_ZONE='+00:00' */
Query     FLUSH /*!40101 LOCAL */ TABLES
Query     FLUSH TABLES WITH READ LOCK
Query     SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
Query     START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
Query     SHOW VARIABLES LIKE 'gtid\_mode'
Query     SHOW MASTER STATUS
Query     UNLOCK TABLES
                    ......
Init DB   gs
Query     SHOW CREATE DATABASE IF NOT EXISTS `gs`
Query     SAVEPOINT sp
Query     show create table `tb1`
Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `tb1`
Query     ROLLBACK TO SAVEPOINT sp
Query     show create table `user`
Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `user`
Query     ROLLBACK TO SAVEPOINT sp
Query     RELEASE SAVEPOINT sp
Quit

发现 --single-transaction 单独使用与 --single-transaction 和 --master-data 结合使用的区别如下图

--single-transaction 和 --master-data 结合使用时，在导出开始，会短暂的持有一个全局的读锁，锁定时：刷新数据到磁盘、设置隔离级别为RR、开始事务、输出 binary log 的位置和文件名，然后解锁。这样就保证了：show master status 输出的 binary log 的位置和文件名 和 后面 导出的数据是同一个时间点的(同一个lsn)，所以才能使用该备份 和 利用 change master to 构造一个 slave，成功的连接上 master
可以看到这里使用了两次 flush tables:
FLUSH /*! 40101 LOCAL */ TABLES
FLUSH TABLES WITH READ LOCK
其原因是 第一次 flush 是不需要锁表的，第一次将所有数据刷新到磁盘之后，第二次 FLUSH TABLES WITH READ LOCK 时就会只有很少量的数据需要刷新到磁盘，所以第一次 flush 是为了尽量的 减少 第二次  fush 持有锁的时间

mysqldump 事务和数据一致性(锁)

只有 --lock-tables 默认是打开的；打开 --single-transaction 或者打开 --lock-all-tables 都将关闭 --lock-tables. 而--lock-all-tables会自动关闭 --single-transaction 和 --lock-tables。所以三者是互斥的。我们应该一次只启用其中一个选项

1) --lock-tables
默认为打开，它的作用是在导出过程中锁定所有表。--single-transaction 和 --lock-all-tables 都会将该选项关闭
2) --single-transaction
只能是 innodb 引擎；导出的过程中，不能有任何人执行 alter table, drop table, rename table, truncate table等DDL语句。实际上DDL会被事务所阻塞，因为事务持有表的metadata lock 的共享锁，而DDL会申请metadata lock的互斥锁，所以阻塞了
 --single-transaction 会自动关闭--lock-tables，所以单独使用--single-transaction是不会使用锁的。与 --master-data 合用才有锁
3）--lock-all-tables
4）--flush-logs 
为了获得导出数据和刷新日志的一致性(同时发生)，必须将 --flush-logs 选项和 --lock-all-tables 或者 --master-data 一起使用
5）--flush-privileges
该选项会在导出的 mysql 数据库的后面加上 flush privileges 语句，因为在向mysql数据库inert了语句之后，必须使用 flush privileges，不然权限不生效
6）  --master-data[=#]
一般会和 --single-transaction一起使用，用于搭建master-slave环境
为了获得一致性的备份数据和在备份是同时刷新binary日志，我们应该如下结合使用这些选项(完美组合)
mysqldump -uxxx -pxxx --single-transaction --master-data=2 --flush-logs --routines --databases db1 > db1.sql;
其中被 --master-data 打开的 --lock-all-tables 选项，又被 --single-transaction 关闭掉了。--flush-logs 借助于 --master-data 可以达到即使一次导出多个数据库时，其 flush 的二进制日志也是在同一个时间点的，不是每一个数据库flush一次的。并且这个时间点 和 --master-data 记录的 binary log position 和 binary log file是同一个时间点，这些都是利用了 --single-transaction 和 --master-data 合用时短暂的使用一个全局的读锁来达到目的的

mysqldump常用语句

导出单表的结构和数据：mysqldump -uxxx -p db1 tb1 > tb1.sql; 导出数据库 db1 中的 表 tb1 的表结构 和 表中数据；
导出多表的结构和数据：mysqldump -uxxx -p db1 tb1 tb2 > tb1_tb2.sql; 导出数据库 db1 中的 表 tb1、tb2 的表结构 和 表中数据；
导出单表的结构：mysqldump -uxxx -p --no-data db1 tb1 > tb1.sql; 导出数据库 db1 中的 表 tb1 的表结构; 其实也可以使用: show create table tb1
我们无法使用 mysqldump 到达 只导出某个或某几个表的数据，而不导出建表语句的目的。但是我们可以使用 select * from table into outfile 'file.sql', 比如：select * from Users into outfile '/tmp/Users.sql'; 注意需要对目录的写权限。
导出单个库中库结构、表结构、表数据：mysqldump -uxxx -p --databases db1 > db1.sql
导出多个库中库结构、表结构、表数据：mysqldump -uxxx -p --databases db1 db2 > db1_db2.sql
导出单个库中库结构、表结构、不要表数据：mysqldump -uxxx -p --no-data --databases db1 > db1.sql
导出单个库中数据，不要库结构和表结构：mysqldump -uxxx -p --no-create-db --no-create-info --databases db1 > db1.sql
导出多个库中库结构、表结构、不要表数据：mysqldump -uxxx -p --no-data --databases db1 db2 > db1_db2.sql
导出数据库中所有 库 的库结构，表结构，数据：mysqldump -uxxx -p --all-databases > all.sql
导出数据库中所有 库 的库结构，表结构，不要数据：mysqldump -uxxx -p --all-databases --no-data > all.sql
导出单个库中库结构、表结构、表数据，排除某个表：mysqldump -uxxx -p --databases db1 --ignore-table=db1.test > db1.sql

转载于:https://www.cnblogs.com/reaperhero/articles/10567691.html

weixin_30731287

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
mysqldump使用教程

mysqldump 默认选项add-drop-table TRUE 表示在生成表结构语句之前，生成对应的 DROP TABLE IF EXISTS `table_name`; 语句add-locks TRUE 表示在生成表中数据的 insert into `table_name` values(...) 之前...
复制链接

扫一扫