ubuntu14.04 Mysql5.6主从,使用percona保证数据一致

前端之家收集整理的这篇文章主要介绍了ubuntu14.04 Mysql5.6主从,使用percona保证数据一致前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

数据库配置

[MysqLd]
server-id       = 1
log_bin         = /var/log/MysqL/MysqL-bin.log
expire_logs_days    = 10
max_binlog_size         = 100M

数据库配置

[MysqLd]
server-id       = 2
log_bin         = /var/log/MysqL/MysqL-bin.log
expire_logs_days    = 10
max_binlog_size         = 100M
replicate-do-db=jitui_ad
replicate-do-db=percona
slave-skip-errors = 1062

启动SLAVE

# 查看主服务器当前二进制日志名和偏移量,这个操作的目的是为了在从数据库启动后,从这个点开始进行数据的恢复
SHOW MASTER STATUS;

# 从数据库添加主从
CHANGE MASTER TO MASTER_HOST='192.168.3.91',MASTER_USER='repl',MASTER_PASSWORD='slavepass',MASTER_LOG_FILE='MysqL-bin.000008',MASTER_LOG_POS=8043;

# 启动主从
START SLAVE;

# 停止主从
STOP STLAVE;

# 查看主从状态
SHOW SLAVE STATUS\G;

# 查看slave的状态,如果下面两项值为YES,则表示配置正确:
Slave_IO_Running: Yes
Slave_sql_Running: Yes

主从数据库创建用户

INSERT INTO MysqL.user(Host,User,Password) VALUES("localhost","repl",password("slavepass"));
GRANT SELECT,PROCESS,SUPER,REPLICATION SLAVE ON *.* TO 'repl'@localhost IDENTIFIED BY 'slavepass';
GRANT SELECT,REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'slavepass';
FLUSH PRIVILEGES;

主从数据库添加数据库给保存检查数据

CREATE DATABASE percona CHARACTER SET utf8;
GRANT ALL ON percona.* TO 'repl'@localhost IDENTIFIED BY 'slavepass';
GRANT ALL ON percona.* TO 'repl'@'%' IDENTIFIED BY 'slavepass';
FLUSH PRIVILEGES;

repl添加主从数据库添加数据库权限

GRANT ALL PRIVILEGES ON jitui_ad.* TO repl@localhost IDENTIFIED BY 'slavepass';
GRANT ALL PRIVILEGES ON jitui_ad.* TO repl@'%' IDENTIFIED BY 'slavepass';
FLUSH PRIVILEGES;

pt-table-checksum检查表是否一致

pt-table-checksum --nocheck-replication-filters --databases=jitui_ad --replicate=percona.checksums --create-replicate-table  --host=127.0.0.1 --port 3306 -urepl -pslavepass

重启从库做主从

# 跳过一个错误
pt-slave-restart --user=repl --password=slavepass --host=127.0.0.1 --skip-count=1

# 跳过某些类型错误
pt-slave-restart --user=repl --password=slavepass --host=127.0.0.1 --error-numbers=1062

pt-table-sync保存数据一致

# 将主的jitui_ad数据库同步到 192.168.3.92,使从上具有一样的
数据,使用--print将结果输出
pt-table-sync --print --sync-to-master --user=repl --password=slavepass h=192.168.3.92 --database jitui_ad

# 根据 pt-table-checksum 使从的数据和主的数据一致
pt-table-sync --execute --replicate percona.checksums --user=repl --password=slavepass --sync-to-master h=192.168.3.92

 删除从库

MysqL> STOP SLAVE;
MysqL> STOP SLAVE IO_THREAD;
MysqL> STOP SLAVE sql_THREAD;
MysqL> RESET SLAVE ALL;
cd /var/lib/MysqL
service MysqL stop
rm -f master.info relay-*`
service MysqL start

参考

猜你在找的Ubuntu相关文章