起因：
主mysql的my.cnf注释了binlog(猜测是因为数据目录为默认的/var/lib/mysql导致根满而关闭了binlog)导致从mysql同步失败
(数据库名称,用户，IP，主机名均已隐去)
修改主从mysql的my.cnf将数据目录转移到data目录
1.新增分区sda4并格式化,挂载为/data
2.停止mysql
# service mysqld stop
3.修改my.cnf打开binlog(从mysql是打开relay-bin.),拷贝目录
# cp -r /var/lib/mysql /data/
# chown -R mysql.mysql /data/mysql
4.启动mysql
# service mysqld start
从mysql:
mysql> stop slave;
mysql> reset slave;
主mysql:
mysql> flush tables with read lock;
mysql> show master status;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000003 | 662 | | |
+——————+———-+————–+——————+
1 row in set (0.00 sec)
再打开一个窗口：
# mysqldump –single-transaction –master-data=2 -B database1 database2 -uroot -p |gzip > /data/20170518dump_data.gz
拷贝至从mysql
从mysql：
# gunzip -c 20170518dump_data.gz > 20170518dump_data.sql
# mysql -uroot -p < 20170518dump_data.sql
mysql> stop slave;
mysql> change master to master_host=’主mysql-ip’,master_user=’repl’,master_log_file=’mysql-bin.000003′,master_log_pos=662;
mysql> show slave status\G;
可以看到目前状态同步正常
主mysql解锁：
mysql> unlock tables;
从mysql:
mysql> show slave status\G;
可以看到目前状态同步正常
至此mysql主从同步完成
#######################################
校验数据库同步情况：
主mysql：
mysql> CREATE database pt CHARACTER SET utf8;
mysql> GRANT SELECT,PROCESS,SUPER,REPLICATION SLAVE ON *.* TO ‘checksums’@’主mysql-ip’ IDENTIFIED BY ‘passwd’;
mysql> GRANT ALL PRIVILEGES ON pt.* TO ‘checksums’@’主mysql-ip’ IDENTIFIED BY ‘passwd’;
mysql> CREATE TABLE IF NOT EXISTS pt.checksums (
-> db CHAR(64) NOT NULL,
-> tbl CHAR(64) NOT NULL,
-> chunk INT NOT NULL,
-> chunk_time FLOAT NULL,
-> chunk_index VARCHAR(200) NULL,
-> lower_boundary TEXT NULL,
-> upper_boundary TEXT NULL,
-> this_crc CHAR(40) NOT NULL,
-> this_cnt INT NOT NULL,
-> master_crc CHAR(40) NULL,
-> master_cnt INT NULL,
-> ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> PRIMARY KEY (db,tbl,chunk),
-> INDEX ts_db_tbl(ts,db,tbl)
-> ) ENGINE=InnoDB;
mysql> flush privileges;
# pt-table-checksum h=’主mysql-ip’,u=’checksums’,p=’passwd’,P=3306 -d database1 –nocheck-replication-filters –replicate=pt.checksums –no-check-binlog-format
# pt-table-checksum h=’主mysql-ip’,u=’checksums’,p=’passwd’,P=3306 -d database2 –nocheck-replication-filters –replicate=pt.checksums –no-check-binlog-format
校验正常
# pt-table-checksum h=’主mysql-ip’,u=’checksums’,p=’passwd’,P=3306 -d mysql –nocheck-replication-filters –replicate=pt.checksums –no-check-binlog-format
执行后发现mysql有库不一致的表：
mysql.db
mysql.user
从mysql上执行： mysql> GRANT ALL PRIVILEGES ON `database1`.* TO ‘user1’@’北京公司出口ip’; 再次校验pt-table-checksum 后发现mysql.user正常，mysql.db依旧不正常
打开主mysql.db表后发现存在一个授权(很可能是delete方式删除的用户,更新user表但不会更新db表),执行 mysql> drop user user1@’192.168.145.xx’;
再次检验时报错
Error setting innodb_lock_wait_timeout: DBD::mysql::db do failed: Variable ‘innodb_lock_wait_timeout’ is a read only variable [for Statement “SET SESSION innodb_lock_wait_timeout=1”]. The current value for innodb_lock_wait_timeout is 50. If the variable is read only (not dynamic), specify –set-vars innodb_lock_wait_timeout=50 to avoid this warning, else manually set the variable and restart MySQL.
Replica oth-bj-xxx-xxx-xxxx-xxx is stopped. Waiting.
Replica oth-bj-xxx-xxx-xxxx-xxx is stopped. Waiting.
Replica oth-bj-xxx-xxx-xxxx-xxx is stopped. Waiting.
Replica oth-bj-xxx-xxx-xxxx-xxx is stopped. Waiting.
经查从mysql同步状态已经异常
从mysql: mysql> show slave status\G; … …
Error ‘Operation DROP USER failed for ‘user1’@’192.168.145.xx” on query. Default database: ”. Query: ‘drop user user1@’192.168.145.xx”
mysql> GRANT SELECT ON database1.* TO ‘user1’@’192.168.145.xx’;
mysql> stop slave;
mysql> start slave;
mysql> show slave status\G;
同步状态正常
主mysql：
# pt-table-checksum h=’主mysql-ip’,u=’checksums’,p=’passwd’,P=3306 -d mysql –nocheck-replication-filters –replicate=pt.checksums –no-check-binlog-format –set-vars innodb_lock_wait_timeout=50
检验正常
