mysql_Replicate

起因:
主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 `mping`.* 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 mping.* 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
 
检验正常
Print Friendly

发表评论

电子邮件地址不会被公开。 必填项已用*标注