可能是有人在我们集群的一个slave节点上对备份数据库单独做了写操作，导致该slave节点数据库与master不同步：
mysql>show slave status;
显示：Slave_SQL_Running状态为No，由于一直没有记录数据库的相关日志，二进制的备份文件和位置也搞得稀里糊涂，干脆直接干掉这个节点的备份，重新配置。
过程：
1. 锁定master表为只读：
#锁定master数据库表
MYSQL>FLUSH TABLES WITH READ LOCK;
2. 导出要备份的数据库：
[user@gisnode-1 ~]$ mysqldump -u myuser -p gisdb>gisdb.sql
Enter Password:
3. 导入到slave节点
#新建一个数据库
mysql>create database gisdb;
mysql>use gisdb;
mysql>source gisdb.sql;
4. 关掉Slave，将主机日志文件和位置信息读入从机
mysql > stop slave;
mysql > change master to master_host='192.168.19.1', master_user='myuser', master_password='mypassword', master_log_file='mysql-bin.000023',
master_log_pos=107;
mysql > start slave;
mysql> show slave status\G;
5. 若Slave_IO_Running和Slave_SQL_Running均为Yes代表配置成功。
另外一种方式：
转摘之：http://www.cnblogs.com/yeahgis/archive/2012/05/15/2501112.html
上面的方式，不太好，因为
FLUSH TABLES WITH READ LOCK;会将master上的表锁住，影响用户的使用：当master/slave不同步时可以使用另外一个工具来同步：
大概过程：
说明信息如下：
###206 master,207 slave, 在test库里面创建dsns 表！
mysql> show create table dsns;
| dsns | CREATE TABLE `dsns` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`parent_id` int(11) DEFAULT NULL,
`dsn` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
1 row in set (0.00 sec)
mysql> select * from dsns;
+----+-----------+---------------------------------------------+
| id | parent_id | dsn |
+----+-----------+---------------------------------------------+
| 3 | 1 | h=192.168.2.207,u=root,p=kuutown1601,P=3306 |
+----+-----------+---------------------------------------------+
1 row in set (0.00 sec)
mysql> iNSERT INTO dsns (parent_id,dsn) values(1,'h=192.168.2.207,u=root,p=kuutown1601,P=3306');
[root@web2 ~]# pt-table-checksum --nocheck-replication-filters --databases=loldbrtmp1 --host=192.168.2.206 --port 3306 -uslaveuser -pslaveuser
--no-check-binlog-format --recursion-method=dsn=h=192.168.2.207,D=test,t=dsns
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
08-08T10:00:48 0 0 0 1 0 1.025 loldbrtmp1.banrate
08-08T10:00:48 0 0 0 1 0 0.010 loldbrtmp1.champion_analyze
08-08T10:00:48 0 0 0 1 0 0.010 loldbrtmp1.championcombatcapacity
08-08T10:00:48 0 0 0 1 0 0.008 loldbrtmp1.rtmpaggregatedstat
08-08T10:00:48 0 0 0 1 0 0.009 loldbrtmp1.rtmpleagueitemdto
08-08T10:00:48 0 0 0 1 0 0.009 loldbrtmp1.rtmpmasterybookpagedto
08-08T10:00:48 0 0 0 1 0 0.008 loldbrtmp1.rtmpplayerstatsummaries
08-08T10:00:48 0 0 0 1 0 0.010 loldbrtmp1.rtmppublicsummoner
Checksumming loldbrtmp1.rtmprecentgamesnew: 1% 34:14 remain
Checksumming loldbrtmp1.rtmprecentgamesnew: 3% 40:57 remain
Checksumming loldbrtmp1.rtmprecentgamesnew: 5% 42:11 remain
Checksumming loldbrtmp1.rtmprecentgamesnew: 6% 42:19 remain
Checksumming loldbrtmp1.rtmprecentgamesnew: 8% 41:55 remain
Checksumming loldbrtmp1.rtmprecentgamesnew: 10% 41:06 remain
Checksumming loldbrtmp1.rtmprecentgamesnew: 12% 40:15 remain
Checksumming loldbrtmp1.rtmprecentgamesnew: 13% 39:26 remain
Checksumming loldbrtmp1.rtmprecentgamesnew: 15% 38:30 remain
Checksumming loldbrtmp1.rtmprecentgamesnew: 17% 37:31 remain
Checksumming loldbrtmp1.rtmprecentgamesnew: 19% 36:30 remain
Checksumming loldbrtmp1.rtmprecentgamesnew: 20% 35:39 remain
Checksumming loldbrtmp1.rtmprecentgamesnew: 22% 34:59 remain
Checksumming loldbrtmp1.rtmprecentgamesnew: 24% 34:16 remain
Checksumming loldbrtmp1.rtmprecentgamesnew: 26% 33:30 remain
Checksumming loldbrtmp1.rtmprecentgamesnew: 27% 32:46 remain
Checksumming loldbrtmp1.rtmprecentgamesnew: 29% 32:01 remain
Checksumming loldbrtmp1.rtmprecentgamesnew: 31% 31:15 remain
Checksumming loldbrtmp1.rtmprecentgamesnew: 33% 30:30 remain
Checksumming loldbrtmp1.rtmprecentgamesnew: 34% 29:48 remain
Checksumming loldbrtmp1.rtmprecentgamesnew: 36% 29:03 remain
Checksumming loldbrtmp1.rtmprecentgamesnew: 38% 28:20 remain
Checksumming loldbrtmp1.rtmprecentgamesnew: 40% 27:37 remain
Checksumming loldbrtmp1.rtmprecentgamesnew: 41% 26:54 remain
Checksumming loldbrtmp1.rtmprecentgamesnew: 43% 26:10 remain
Checksumming loldbrtmp1.rtmprecentgamesnew: 45% 25:26 remain
Checksumming loldbrtmp1.rtmprecentgamesnew: 47% 24:42 remain
Checksumming loldbrtmp1.rtmprecentgamesnew: 48% 23:59 remain
Checksumming loldbrtmp1.rtmprecentgamesnew: 50% 23:14 remain
Checksumming loldbrtmp1.rtmprecentgamesnew: 52% 22:29 remain
Checksumming loldbrtmp1.rtmprecentgamesnew: 54% 21:45 remain
Checksumming loldbrtmp1.rtmprecentgamesnew: 55% 21:00 remain
Checksumming loldbrtmp1.rtmprecentgamesnew: 57% 20:16 remain
Checksumming loldbrtmp1.rtmprecentgamesnew: 59% 19:30 remain
Checksumming loldbrtmp1.rtmprecentgamesnew: 60% 18:43 remain
Checksumming loldbrtmp1.rtmprecentgamesnew: 62% 17:56 remain
Checksumming loldbrtmp1.rtmprecentgamesnew: 64% 17:08 remain
Checksumming loldbrtmp1.rtmprecentgamesnew: 66% 16:21 remain
Checksumming loldbrtmp1.rtmprecentgamesnew: 67% 15:33 remain
Checksumming loldbrtmp1.rtmprecentgamesnew: 69% 14:44 remain
Checksumming loldbrtmp1.rtmprecentgamesnew: 71% 13:56 remain
Checksumming loldbrtmp1.rtmprecentgamesnew: 72% 13:07 remain
Checksumming loldbrtmp1.rtmprecentgamesnew: 74% 12:17 remain
Checksumming loldbrtmp1.rtmprecentgamesnew: 76% 11:28 remain
Checksumming loldbrtmp1.rtmprecentgamesnew: 78% 10:38 remain
Checksumming loldbrtmp1.rtmprecentgamesnew: 79% 09:48 remain
Checksumming loldbrtmp1.rtmprecentgamesnew: 81% 08:57 remain
Checksumming loldbrtmp1.rtmprecentgamesnew: 83% 08:07 remain
Checksumming loldbrtmp1.rtmprecentgamesnew: 85% 07:15 remain
Checksumming loldbrtmp1.rtmprecentgamesnew: 86% 06:24 remain
Checksumming loldbrtmp1.rtmprecentgamesnew: 88% 05:33 remain
Checksumming loldbrtmp1.rtmprecentgamesnew: 90% 04:40 remain
08-08T10:44:49 0 1 4216701 108 0 2639.845 loldbrtmp1.rtmprecentgamesnew
08-08T10:44:50 0 0 0 1 0 1.015 loldbrtmp1.rtmprunebookpagedto
08-08T10:44:50 0 0 0 1 0 0.009 loldbrtmp1.rtmpspellbookpagedto
08-08T10:44:50 0 0 0 1 0 0.009 loldbrtmp1.sid
####上面的命令用于检查两个db是否同步！
此处dsn指的是dsns表在192.168.2.207机器上，库是test和上面的--databases=test,test1 无关
no-check-binlog-format参数一定要启用，因为pt-table-checksum在主机其实执行的是 REPLACE INTO `test`.`checksums` 和update这类更新语句，然后从机同步执行此语句，
如果binlog-format采用ROW模式，从机的checksums表数据就会和主机一样，所以必须采用statment模式，加了no-check-binlog-format这个参数后，保证
pt-table-checksum在session中用statment模式来执行。
[root@web2 percona]# pt-table-checksum --nocheck-replication-filters --databases=loldbrtmp1 --tables=person --host=192.168.2.206 --port 3306 -uslaveuser
-pslaveuser --recursion-method=processlist --no-check-binlog-format --recursion-method=dsn=h=192.168.2.207,D=test,t=dsns
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
08-08T14:58:10 0 1 4 1 0 1.046 loldbrtmp1.person
[root@web2 percona]#
在master上执行：
在206上执行的，指定 --sync-to-master 就不需要指定master相关信息了
[root@web1 ~]# pt-table-sync --execute --sync-to-master --database=loldbrtmp1 --tables=person --charset=utf8 h=192.168.2.207,u=root,p=kuutown1601 --print
REPLACE INTO `loldbrtmp1`.`person`(`id`, `name`, `age`) VALUES ('3', 'xiao', '26') /*percona-toolkit src_db:loldbrtmp1 src_tbl:person
src_dsn:A=utf8,P=3306,h=192.168.2.206,p=...,u=root dst_db:loldbrtmp1 dst_tbl:person dst_dsn:A=utf8,h=192.168.2.207,p=...,u=root lock:1 transaction:1
changing_src:1 replicate:0 bidirectional:0 pid:5562 user:root host:web1*/;
REPLACE INTO `loldbrtmp1`.`person`(`id`, `name`, `age`) VALUES ('4', 'jie', '27') /*percona-toolkit src_db:loldbrtmp1 src_tbl:person
src_dsn:A=utf8,P=3306,h=192.168.2.206,p=...,u=root dst_db:loldbrtmp1 dst_tbl:person dst_dsn:A=utf8,h=192.168.2.207,p=...,u=root lock:1 transaction:1
changing_src:1 replicate:0 bidirectional:0 pid:5562 user:root host:web1*/;
[root@web1 ~]#
到两台机器上查看发现记录已经一致！
同样这个slave上的用户，在master机器上也要能访问，否则会报错如下：
我在207上删除：
mysql> drop user 'root'@'192.168.2.206';
Query OK, 0 rows affected (0.00 sec)
mysql>
然后在206上执行：
[root@web1 ~]# pt-table-sync --execute --sync-to-master --database=loldbrtmp1 --tables=person --charset=utf8 h=192.168.2.207,u=root,p=kuutown1601 --print
\DBI connect(';host=192.168.2.207;charset=utf8;mysql_read_default_group=client','root',...) failed: Access denied for user 'root'@'192.168.2.206' (using
password: YES) at /usr/local/bin/pt-table-sync line 2208
显然有错！
重新添加该用户即可！
mysql> grant all on *.* to 'root'@'192.168.2.206' identified by 'kuutown1601';
Query OK, 0 rows affected (0.00 sec)
####PS：注意用户的访问权限，生产环境下一般不使用root，现在仅仅是测试
####PS：如果表没有唯一索引或者主键就无法使用了。
安装和使用过程中遇到两个问题：
error: Failed dependencies:
perl(IO::Socket::SSL) is needed by percona-toolkit-2.2.18-1.noarch
perl(Term::ReadKey) is needed by percona-toolkit-2.2.18-1.noarch
即可搞定
yum install perl-TermReadKey.x86_64
yum install perl-IO-Socket-SSL
[root@MYSQL02 ~]# pt-table-checksum --nocheck-replication-filters --databases=abc_user --host=10.10.11.127 --port 3306 -uroot -p'abc.com' --no-check-binlog-format --recursion-method=dsn=h=10.10.11.131,D=syncdb,t=dsns
01-09T13:58:37 DBI connect(';host=10.10.11.127;port=3306;mysql_read_default_group=client','root',...) failed: Access denied for user 'root'@'10.10.11.131' (using password: YES) at /usr/bin/pt-table-checksum line 1581
在127 机器上创建一个用户，
grant all on *.* to 'root'@'10.10.11.131' identified by 'abc.com';
要添加两个用户：
'root'@'10.10.11.112'
和
'root'@'10.10.11.131'
密码为abc.com
这样才可以！
使用的例子：pt-table-checksum --nocheck-replication-filters --databases=d**t --host=192.168.10.50 --port 3306 -uroot -p'abc.com' --no-check-binlog-format --recursion-method=dsn=h=192.168.10.53,D=syncdb,t=dsns
阅读(3125) | 评论(0) | 转发(0) |
