ylw6006 的BLOG
用户名：ylw6006
文章数：225
评论数：669
访问量：1193993
注册日期：
阅读量：26308
51CTO推荐博文
由于各种原因，主从架构经常会出现数据不一致的情况出现，大致归结为如下几类1:备库写数据2:执行3:回滚掺杂事务表和非事务表的事务4:binlog或者数据损坏&数据不同步给应用带来的危害是致命的，当出现主从数据不一致的情况，常见的应对方法是先把从库下线，然后找个半夜三更的时间把应用停掉，重新执行同步，如果数据库的体积十分庞大，那工作量可想而知，会让人崩溃。本文介绍使用percona-toolkit工具对主从数据库的同步状态进行检查和重新同步。&一：安装percona-toolkit#&yum&-y&&install&perl-Time-HiRes
/downloads/percona-toolkit/2.2.13/tarball/percona-toolkit-2.2.13.tar.gz
#&tar&-zxvpf&percona-toolkit-2.2.13.tar.gz&
#&cd&percona-toolkit-2.2.13
#&perl&Makefile.PL&
#&make&install二：修改的格式参数为格式&mysql&binlog日志有三种格式，分别为&Mixed,以及！1.Statement：每一条会修改数据的都会记录在中。优点：不需要记录每一行的变化，减少了日志量，节约了，提高性能。相比能节约多少性能与日志量，这个取决于应用的情况，正常同一条记录修改或者插入格式所产生的日志量还小于产生的日志量，但是考虑到如果带条件的操作，以及整表删除，表等操作，格式会产生大量日志，因此在考虑是否使用格式日志时应该跟据应用的实际情况，其所产生的日志量会增加多少，以及带来的性能问题。缺点：由于记录的只是执行语句，为了这些语句能在上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在得到和在端执行时候相同&的结果。另外的复制像一些特定函数功能，可与上要保持一致会有很多相关问题如函数，&，以及会出现问题&2.Row不记录语句上下文相关信息，仅保存哪条记录被修改。优点：中可以不记录执行的语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或，以及的调用和触发无法被正确复制的问题缺点所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容比如一条语句，修改多条记录，则中每一条修改都会有记录，这样造成日志量会很大，特别是当执行之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中。&3.Mixed是以上两种的混合使用，一般的语句修改使用格式保存，如一些函数，无法完成主从复制的操作，则采用格式保存会根据执行的每一条具体的语句来区分对待记录的日志形式，也就是在和之间选择一种新版本的中队模式也被做了优化，并不是所有的修改都会以来记录，像遇到表结构变更的时候就会以模式来记录。至于或者等修改数据的语句，还是会记录所有行的变更。&主从数据库分别修改文件相关配置项如下：binlog_format=ROW三：使用pt-table-checksum工具检查数据一致性情况用法参考：假设92.168.1.205是主库，92.168.1.207是它的从库，端口在。#&&pt-table-checksum&--user=root&--password=123456&\
&&--host=192.168.1.205&--port=3306&\
&&--databases=test&&--tables=t2&&--recursion-method=processlist&\
&&--no-check-binlog-format&&--nocheck-replication-filters&\
&&--replicate=test.checksums192.168.1.207#&pt-table-sync&&--execute&&--replicate&\
test.checksums&&--sync-to-master&h=192.168.1.207,P=3306,u=root,p=12345692.168.1.207语句返回若为空，则说明修复成功：SELECT
test.checksums
master_cnt&&&&this_cnt
OR&master_crc&&&&this_crc
OR&ISNULL(master_crc)&&&&ISNULL(this_crc)各参数含义--nocheck-replication-filters:不检查复制过滤器，建议启用。后面可以用--databases来指定需要检查的数据库。--no-check-binlog-format:不检查复制的binlog模式，要是binlog模式是ROW，则会报错。--replicate-check-only:只显示不同步的信息。--replicate=:把checksum的信息写入到指定表中，建议直接写到被检查的数据库当中。&--databases=:指定需要被检查的数据库，多个则用逗号隔开。--tables=：指定需要被检查的表，多个用逗号隔开h=127.0.0.1:Master的地址u=root:用户名p=123456:密码P=3306:端口下面我们来模拟下主从数据库不同步情况下的pt-table-checksum，为了方面，这里我们采用&1:&主库上建表，插入测试数据mysql&&create&table&t2&(id&int&primary&key,name&varchar(100)&not&null,salary&int);
mysql&&CREATE&PROCEDURE&test_insert&()
&&&&&&&BEGIN
&&&&&&&DECLARE&i&INT&DEFAULT&0;
&&&&&&&WHILE&i&10000
&&&&&&&INSERT&INTO&t2
&&&&&&VALUES
&&&&&&(i,CONCAT('员工',i),&i);
&&&&&&SET&i=i+1;
&&&&&&END&WHILE&;
&&&&&&END;;
mysql&&CALL&test_insert();从库上校验当前数据的同步情况为正常。从库上删除一半的数据mysql&&delete&from&t2&where&id&&&5000;
Query&OK,&4999&rows&affected&(0.14&sec)
mysql&&select&count(*)&from&t2;
+----------+
|&count(*)&|
+----------+
|&&&&&5001&|
+----------+
1&row&in&set&(0.01&sec)2：使用pt-table-checksum工具进行校验：#&&pt-table-checksum&--user=root&--password=123456&\
&&--host=192.168.1.205&--port=3306&\
&&--databases=test&&--tables=t2&&--recursion-method=processlist&\
&&--no-check-binlog-format&&--nocheck-replication-filters&\
&&--replicate=test.checksums3：登陆从库进行查询表mysql&&SELECT
test.checksums
master_cnt&&&&this_cnt
OR&master_crc&&&&this_crc
OR&ISNULL(master_crc)&&&&ISNULL(this_crc)4：使用pt-table-sync工具进行数据重新同步#&pt-table-sync&&--execute&&--replicate&\
&test.checksums&&--sync-to-master&h=192.168.1.207,P=3306,u=root,p=1234565：从库上验证数据，中文“员工”变成了“”检查主库，发现出现一样的情况，中文“员工”变成了“”，猜想和字符集设置相关。于是检查数据库字符集设置，发现库字符集非主从库my.cnf文件添加如下配置项后重启数据库实例character_set_client=utf8
character_set_server=utf8重新执行以上步，发现一切正常！关键第步要加参数&#&pt-table-sync&&--execute&&--replicate&\
&test.checksums&&&--charset=utf8&\
&--sync-to-master&h=192.168.1.207,P=3306,u=root,p=123456本文出自 “” 博客，谢绝转载！
了这篇文章
类别：┆阅读(0)┆评论(0)
09:21:48 16:47:30 11:39:19 13:45:2651CTO还有以下内容可能满足您的需求3068人阅读
MYSQL主从复制(replication)采用 RBR 模式后，binlog的格式为&ROW&，能解决很多原先出现的主键重复问题。
在一个繁忙的master db server上，binlog日志文件增长速度很快，如果不定时清除，硬盘空间很快就会被充满。
设置自动清理mysql binlog日志，配置my.cnf：
expire_logs_days = 10
在运行时修改：
show variables like '%log%';
set global expire_logs_days = 10;
清除之前可以采用相应的备份策略。
手动删除10天前的mysql binlog日志：
PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY);
MASTER和BINARY是同义词。
一般情况下，推荐使用MIXED binlog的复制。/doc/refman/5.1/en/open-bugs-general.html中的说明:Replication uses query-level logging: The master writes the executed queries to the binary log. This is a very fast, compact, and efficient logging method that works
perfectly in most cases.
附：关于MYSQL复制的几种模式
以下转自http://steven1981.itpub.net/post/
从 MySQL 5.1.12 开始，可以用以下三种模式来实现：
– 基于SQL语句的复制(statement-based replication, SBR)，
– 基于行的复制(row-based replication, RBR)，
– 混合模式复制(mixed-based replication, MBR)。
相应地，binlog的格式也有三种：STATEMENT，ROW，MIXED。 MBR 模式中，SBR 模式是默认的。
在运行时可以动态改动 binlog的格式，除了以下几种情况：
. 存储流程或者触发器中间
. 启用了NDB
. 当前会话试用 RBR 模式，并且已打开了临时表
如果binlog采用了 MIXED 模式，那么在以下几种情况下会自动将binlog的模式由 SBR 模式改成 RBR 模式。
. 当DML语句更新一个NDB表时
. 当函数中包含 UUID() 时
. 2个及以上包含 AUTO_INCREMENT 字段的表被更新时
. 行任何 INSERT DELAYED 语句时
. 用 UDF 时
. 视图中必须要求运用 RBR 时，例如建立视图是运用了 UUID() 函数
设定主从复制模式：
log-bin=mysql-bin
#binlog_format=&STATEMENT&
#binlog_format=&ROW&
binlog_format=&MIXED&
也可以在运行时动态修改binlog的格式。例如
mysql& SET SESSION binlog_format = 'STATEMENT';
mysql& SET SESSION binlog_format = 'ROW';
mysql& SET SESSION binlog_format = 'MIXED';
mysql& SET GLOBAL binlog_format = 'STATEMENT';
mysql& SET GLOBAL binlog_format = 'ROW';
mysql& SET GLOBAL binlog_format = 'MIXED';
两种模式各自的优缺点：
SBR 的优点：
历史悠久，技能成熟
binlog文件较小
binlog中包含了所有数据库修改信息，可以据此来审核数据库的安全等情况
binlog可以用于实时的还原，而不仅仅用于复制
主从版本可以不一样，从服务器版本可以比主服务器版本高
SBR 的缺点：
不是所有的UPDATE语句都能被复制，尤其是包含不确定操作的时候。
调用具有不确定因素的 UDF 时复制也可能出疑问
运用以下函数的语句也不能被复制：
* LOAD_FILE()
* FOUND_ROWS()
* SYSDATE() (除非启动时启用了 –sysdate-is-now 选项)
INSERT … SELECT 会产生比 RBR 更多的行级锁
复制须要执行 全表扫描(WHERE 语句中没有运用到索引)的 UPDATE 时，须要比 RBR 请求更多的行级锁
对于有 AUTO_INCREMENT 字段的 InnoDB表而言，INSERT 语句会阻塞其他 INSERT 语句
对于一些复杂的语句，在从服务器上的耗资源情况会更严重，而 RBR 模式下，只会对那个发生变化的记录产生影响
存储函数(不是存储流程 )在被调用的同时也会执行一次 NOW() 函数，这个可以说是坏事也可能是好事
确定了的 UDF 也须要在从服务器上执行
数据表必须几乎和主服务器保持一致才行，否则可能会导致复制出错
执行复杂语句如果出错的话，会消耗更多资源
RBR 的优点：
任何情况都可以被复制，这对复制来说是最安全可靠的
和其他大多数数据库系统的复制技能一样
多数情况下，从服务器上的表如果有主键的话，复制就会快了很多
复制以下几种语句时的行锁更少：
* INSERT … SELECT
* 包含 AUTO_INCREMENT 字段的 INSERT
* 没有附带条件或者并没有修改很多记录的 UPDATE 或 DELETE 语句
执行 INSERT，UPDATE，DELETE 语句时锁更少
从服务器上采用多线程来执行复制成为可能
RBR 的缺点：
binlog 大了很多
复杂的回滚时 binlog 中会包含大量的数据
主服务器上执行 UPDATE 语句时，所有发生变化的记录都会写到 binlog 中，而 SBR 只会写一次，这会导致频繁发生 binlog 的并发写疑问
UDF 产生的大 BLOB 值会导致复制变慢
不能从 binlog 中看到都复制了写什么语句(加密过的)
当在非事务表上执行一段堆积的SQL语句时，最好采用 SBR 模式，否则很容易导致主从服务器的数据不一致情况发生
另外，针对系统库 mysql 里面的表发生变化时的处理准则如下：
如果是采用 INSERT，UPDATE，DELETE 直接操作表的情况，则日志格式根据 binlog_format 的设定而记录
如果是采用 GRANT，REVOKE，SET PASSWORD 等管理语句来做的话，那么无论如何 都采用 SBR 模式记录。
注：采用 RBR 模式后，能处理很多原先出现的主键重复问题。实例:
对于insert into db_allot_ids select * from db_allot_ids 这个语句:
在BINLOG_FORMAT=STATEMENT 模式下:
BINLOG日志信息为:
—————————————–
#:05:42 server id 1 end_log_pos 288 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=/*!*/;
insert into db_allot_ids select * from db_allot_ids
—————————————–
在BINLOG_FORMAT=ROW 模式下:
BINLOG日志信息为:
—————————————–
hA0yShMBAAAAMwAAAOAAAAAAAA8AAAAAAAAAA1NOUwAMZGJfYWxsb3RfaWRzAAIBAwAA
hA0yShcBAAAANQAAABUBAAAQAA8AAAAAAAEAAv/8AQEAAAD8AQEAAAD8AQEAAAD8AQEAAAA=
—————————————–
清理日志步骤
1.查找日志档案
+----------------+-----------+
| Log_name&&&&&&
| File_size |
+----------------+-----------+
| ablelee.000001 |
| ablelee.000002 |&&&&&&
| ablelee.000003 |&&&&&&
+----------------+-----------+
2.删除bin-log(删除ablelee.000003之前的而没有包含ablelee.000003)
mysql& purge binary logs to 'ablelee.000003';
Query OK, 0 rows affected (0.16 sec)
查询结果(现在只有一条记录了.)
mysql& show binlog events\G
*************************** 1. row ***************************
Log_name: ablelee.000003
&Event_type: Format_desc
Server_id: 1
End_log_pos: 106
Info: Server ver: 5.1.26-rc-log, Binlog ver: 4
1 row in set (0.01 sec)
(ablelee.000001和ablelee.000002已被删除)
+----------------+-----------+
| Log_name&&&&&&
| File_size |
+----------------+-----------+
| ablelee.000003 |&&&&&&
+----------------+-----------+
1 row in set (0.00 sec)
(删除的其它格式运用!)
PURGE {MASTER | BINARY} LOGS TO 'log_name'
　　PURGE {MASTER | BINARY} LOGS BEFORE 'date'
　　用于删除列于在指定的日志或日期之前的日志索引中的所有二进制日志。这些日志也会从记录在日志索引文件中的清单中被删除，这样被给定的日志成为第一个。
　　例如：
　　PURGE MASTER LOGS TO 'mysql-bin.010';
　　PURGE MASTER LOGS BEFORE ' 13:00:00';
&&& 清除3天前的 binlog
&&& PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 3 DAY);
　　BEFORE变量的date自变量可以为'YYYY-MM-DD
hh:mm:ss'格式。MASTER和BINARY是同义词。
　　如果您有一个活性的从属服务器，该服务器当前正在读取您正在试图删除的日志之一，则本语句不会起作用，而是会失败，并伴随一个错误。不过，如果从属服务器是休止的，并且您碰巧清理了其想要读取的日志之一，则从属服务器启动后不能复制。当从属服务器正在复制时，本语句可以安全运行。您不需要停止它们。
　　要清理日志，需按照以下步骤：
在每个从属服务器上，使用SHOW SLAVE STATUS来检查它正在读取哪个日志。
使用SHOW MASTER LOGS获得主服务器上的一系列日志。
在所有的从属服务器中判定最早的日志。这个是目标日志。如果所有的从属服务器是更新的，这是清单上的最后一个日志。
制作您将要删除的所有日志的备份。（这个步骤是自选的，但是建议采用。）
清理所有的日志，但是不包括目标日志
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：926493次
积分：11200
积分：11200
排名：第566名
原创：143篇
转载：314篇
评论：111条
(1)(2)(2)(1)(4)(4)(2)(10)(6)(3)(2)(5)(5)(4)(7)(12)(5)(18)(3)(5)(4)(11)(7)(23)(18)(6)(15)(20)(8)(12)(6)(13)(33)(4)(9)(4)(2)(2)(3)(4)(5)(2)(3)(6)(2)(4)(3)(8)(12)(4)(5)(9)(4)(4)(8)(3)(5)(7)(10)(12)(13)(3)(3)(2)(2)(4)(2)(1)(2)(9)mysql有4种不同的日志，分别是二进制日志，查询日志，慢查询日志和错误日志，这些日记记录着数据库工作的方方面面，可以帮助我们了解数据库的不同方面的踪迹，下面先介绍二进制日志的作用和使用方法，并利用二进制日志对数据库进行各种维护和优化,其他日志也会在后面陆续会做详细的介绍。
二进制日志（bin-log日志）
在中，已经提过bin-log日志的作用和使用，bin-log日志记录了所有的DDL和DML的语句，但不包括查询的语句，语句以事件的方式保存，描述了数据的更改过程，此日志对发生灾难时数据恢复起到了极为重要的作用。
mysql默认是没有开发bin-log日志，首先我们需要开启bin-log日志，在my.cnf中修改
指定了bin-log日志的路径，开启日志后需要myssqladmin flush log才生效,重启后我们发现在刚才设定的路径新增了log文件，这就是我们需要的二进制日志
由于日志是以二进制方式存储的，不能直接读取，需要使用mysql自带的mysqlbinlog工具来进行查看
语法如下：
#mysqlbinlog&mysql-bin.000002
现在我们尝试向test1表插入数据
然后使用mysqlbinlog工具进行日志查看
#mysqlbinlog&mysql-bin.000002&-d&test
如果每天都会生成大量的二进制日志，这些日志长时间不清理的话，将会对磁盘空间带来很大的浪费，所以定期清理日志是DBA维护mysql的一个重要工作
1)RESET MASTER
在上面查看日志存放的文件夹中，二进制日志命名的格式是以mysql-bin.*，*代表日志的序号,序号是递增的,其中还有mysql-bin.index是日志的索引文件,记录了日志的最大序号
我们执行RESET MASTER命名删除全部日志
查看删除后的日志
可以看到,以前的日志全部被清空,新的日志从00001开始
2)PURGE MASTER LOGS TO & PURGE MASTER LOGS BEFORE
执行PURGE MASTER LOGS TO 'mysql-bin.******'命令，是将'******'编号之前的所有日志进行删除
执行PURGE MASTER LOGS BEFORE 'yyyy-mm-dd hh:mm:ss'命令，是将在'yyyy-mm-dd hh:mm:ss'时间之前的所有日志进行删除
3)-EXPIRE_LOGS_DAYS
此参数是设置日志的过期天数，过期的日志将会被自动删除，这有利于减少我们管理日志的工作量，需要修改my.cnf
这里我们设定保存日志为3天，3天之后过期的日志将被自动删除
bin-log是记录着mysql所有事件的操作，当mysql发生灾难性错误时，可以通过bin-log做完整恢复，基于时间点的恢复，和基于位置的恢复
完整恢复，假定我们每天凌晨2点都会使用mysqldump备份数据库，但在第二天早上9点由于数据库出现了故障，数据无法访问，需要恢复数据，先使用昨天凌晨备份的文件进行恢复到凌晨2点的状态，在使用mysqlbinlog恢复自mysqldump备份以来的binlog
mysql localhost mysql-bin.000001 | mysql -uroot -p
这样数据库就可以完全的恢复到崩溃前的完全状态
基于时间点的恢复，由于误操作，比如说删除了一张表，这时使用上面讲的完全恢复是没有用的，因为日志里面还存在误操作的语句，，我们需要的是恢复到误操作前的状态，然后跳过误操作的语句，再恢复后面操作的语句，假定我们删除了一张表的误操作发生在10:00这个时间点，我们可以使用下面的语句用备份和binlog将数据恢复到故障前
mysqlbinlog&--stop-date='&9:59:59'&/var/log/mysql-bin.000001&|&mysql&-uroot&-p
然后跳过误操作的时间点，继续执行后面的binlog
mysqlbinlog&--start-date='&10:01:00'&/var/log/mysql-bin.000001&|&mysql&-uroot&-p
其中--stop-date=' 9:59:59' 和 --start-date=' 10:01:00' 其中的时间是你误操作的时间点，当然了，这个时间点你需要你自己计算的，而且这个时间点还可以涉及到的不只是误操作，还可以有正确的操作也被跳过去了。
基于位置恢复，由于上面提到的，使用基于时间点的恢复可能出现，在一个时间点里面可能存在误操作和其他正确的操作，所以我们需要一种更为精确的恢复方式
使用mysqlbinlog查看二进制，可看到
其中drop tables test1这个误操作的end_log_pos为8879917，几下这个id,得出它前后操作的id分别为9918
我们将进行位置恢复操作
mysqlbinlog&--stop-position='8879916'&/var/log/mysql-bin.000001&|&mysql&-uroot&-p
mysqlbinlog&--start-position='8879918'&/var/log/mysql-bin.000001&|&mysql&-uroot&-p
第一行是恢复到停止位置位置的所以事务，第二性是恢复从给定的起始位置知道二进制日志结束所有事物。
mysql的复制是指将主数据库的DDL和DML操作通过二进制日志传到从服务器上，然后在从服务器上对这些日志做重新执行的操作，从而使得从服务器和主服务器保持数据的同步，通过二进制日志进行主从复制的可以看前一篇《》
----------------------------------------
Mail: chenyz@