首页 / MYSQL / mysqldump主要参数分析
mysqldump主要参数分析
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysqldump主要参数分析,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含4963字,纯文字阅读大概需要8分钟。
内容图文
![mysqldump主要参数分析](/upload/InfoBanner/zyjiaocheng/469/b3734d1cfb764d439118cf715cf12d9a.jpg)
mysqldump是逻辑备份,较适合备份数据量小的库。今天我们来看看几个比较常用的重要参数,并分析它们都做了什么,需要打开general_log。
set global general_log = 1; +------------------+--------------------------------------+ | Variable_name | Value | +------------------+--------------------------------------+ | general_log | ON | | general_log_file | /data/mysql/mysql3317/data/bogon.log | +------------------+--------------------------------------+
表结构如下:
*************************** 1. row *************************** Table: zhangshuo Create Table: CREATE TABLE `zhangshuo` ( `id` int(11) DEFAULT NULL, `name` varchar(50) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
1.不加参数备份zhangshuo表:
[root@bogon ~]# /usr/local/mysql/bin/mysqldump -S /tmp/mysql3317.sock zhangshuo zhangshuo >/tmp/test.sql
4 Init DB zhangshuo 4 Query SHOW TABLES LIKE ‘zhangshuo‘ 4 Query LOCK TABLES `zhangshuo` READ /*!32311 LOCAL */ 4 Query show table status like ‘zhangshuo‘ 4 Query SET SQL_QUOTE_SHOW_CREATE=1 4 Query SET SESSION character_set_results = ‘binary‘ 4 Query show create table `zhangshuo` 4 Query SET SESSION character_set_results = ‘utf8‘ 4 Query show fields from `zhangshuo` 4 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `zhangshuo` 4 Query SET SESSION character_set_results = ‘binary‘ 4 Query use `zhangshuo` 4 Query select @@collation_database 4 Query SHOW TRIGGERS LIKE ‘zhangshuo‘ 4 Query SET SESSION character_set_results = ‘utf8‘ 4 Query UNLOCK TABLES 4 Quit
2.使用--single-transaction参数:
5 Query /*!40100 SET @@SQL_MODE=‘‘ */ 5 Query /*!40103 SET TIME_ZONE=‘+00:00‘ */ 5 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 5 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */ 5 Query SHOW VARIABLES LIKE ‘ndbinfo\_version‘ 5 Init DB zhangshuo 5 Query SHOW TABLES LIKE ‘zhangshuo‘ 5 Query SAVEPOINT sp 5 Query show table status like ‘zhangshuo‘ 5 Query SET SQL_QUOTE_SHOW_CREATE=1 5 Query SET SESSION character_set_results = ‘binary‘ 5 Query show create table `zhangshuo` 5 Query SET SESSION character_set_results = ‘utf8‘ 5 Query show fields from `zhangshuo` 5 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `zhangshuo` 5 Query SET SESSION character_set_results = ‘binary‘ 5 Query use `zhangshuo` 5 Query select @@collation_database 5 Query SHOW TRIGGERS LIKE ‘zhangshuo‘ 5 Query SET SESSION character_set_results = ‘utf8‘ 5 Query ROLLBACK TO SAVEPOINT sp 5 Query RELEASE SAVEPOINT sp 5 Quit
我们看到事物隔离级别改为RR,使用innodb的事物隔离性防止发生幻读。
3.使用--master-data=2 --single-transaction参数:
[root@bogon ~]# /usr/local/mysql/bin/mysqldump --single-transaction --master-data=2 -S /tmp/mysql3317.sock zhangshuo zhangshuo >/tmp/test2.sql
6 Connect root@localhost on 6 Query /*!40100 SET @@SQL_MODE=‘‘ */ 6 Query /*!40103 SET TIME_ZONE=‘+00:00‘ */ 6 Query FLUSH /*!40101 LOCAL */ TABLES 6 Query FLUSH TABLES WITH READ LOCK 6 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 6 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */ 6 Query SHOW VARIABLES LIKE ‘gtid\_mode‘ 6 Query SHOW MASTER STATUS 6 Query UNLOCK TABLES 6 Query SHOW VARIABLES LIKE ‘ndbinfo\_version‘ 6 Init DB zhangshuo 6 Query SHOW TABLES LIKE ‘zhangshuo‘ 6 Query SAVEPOINT sp 6 Query show table status like ‘zhangshuo‘ 6 Query SET SQL_QUOTE_SHOW_CREATE=1 6 Query SET SESSION character_set_results = ‘binary‘ 6 Query show create table `zhangshuo` 6 Query SET SESSION character_set_results = ‘utf8‘ 6 Query show fields from `zhangshuo` 6 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `zhangshuo` 6 Query SET SESSION character_set_results = ‘binary‘ 6 Query use `zhangshuo` 6 Query select @@collation_database 6 Query SHOW TRIGGERS LIKE ‘zhangshuo‘ 6 Query SET SESSION character_set_results = ‘utf8‘ 6 Query ROLLBACK TO SAVEPOINT sp 6 Query RELEASE SAVEPOINT sp
首先下发flush table with read lock(关闭已经打开的表)全局读锁后将事物隔离级别改为RR,并执行START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */创建一次性快照,接着查看是否开启GTID,获取gtid执行位置。数据备份时定义断点 SAVEPOINT sp,做完重新回到这个位置并开始下一张表,最后释放掉断点。
使用--master-data=2后我们再看看备份的SQL,可看到binlog执行位置,很方便搭建主从。
[root@bogon tmp]# grep "CHANGE MASTER TO" /tmp/test2.sql -- CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000006‘, MASTER_LOG_POS=667;
总结:
备份innodb表推荐参数如下:
[root@bogon tmp]# /usr/local/mysql/bin/mysqldump --single-transaction --master-data=2 -S /tmp/mysql3317.sock zhangshuo zhangshuo >/tmp/test2.sql
mysqldump主要参数分析
标签:
本文系统来源:http://www.cnblogs.com/xxmysql/p/5698066.html
内容总结
以上是互联网集市为您收集整理的mysqldump主要参数分析全部内容,希望文章能够帮你解决mysqldump主要参数分析所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。