mysql-双向主从复制
mysql安装(文档自查)
安装了两台服务器上的mysql
服务器A 193.169.200.241:3306 同步账户 repl
服务器B: 193.169.200.240:3306 同步账户 repl
mysql文件配置(在mysql安装的基础配置上追加配置)
/etc/my.cnf
[mysqld]
default_authentication_plugin=mysql_native_password
bind_address=0.0.0.0
server-id=1
log_bin=mysql-bin
binlog_format=mixed
binlog_expire_logs_seconds=604800
sync-binlog=1
innodb_flush_log_at_trx_commit=1
binlog-do-db=test
auto-increment-increment=2
auto-increment-offset=2
replicate-do-db=test
relay_log=relay-bin
重启服务
service mysql restart
备注:
1.使用的默认的安装路径(数据默认位置/var/lib/mysql)
2.bind_address 外部访问地址
3.server-id 服务标识,大于0的整数
4.log_bin mysql-bin二级制日志名, 最终会在/var/lib/mysql/data中以mysql-bin.000001形式存在
5.binlog_format 日志格式类型,这里是混合日志格式
6.binlog_expire_logs_seconds 日志过期时间
7.sync-binlog 每次事务提交都会报binlog刷下去, 0 与1 相差5倍行读写性能或者更高
8.binlog-do-db 需要同步的数据库,如果存在多个需要写多个,不可以写在一行
9.auto-increment-increment id生成策略, 防止id冲突 这里设置为2 这样就会生成1,3,5 或者 2,4,6的id
10.auto-increment-offset 起始偏移量, 就是起始id数 需要设置不同, 这里是两台设备所以一个设置为1 一个设置为2
11.replicate-do-db 需要从其他服务器同步过来的数据库
12.relay_log 中继器日志
mysql同步账户
在服务器A上执行
CREATE USER 'repl'@'193.169.200.241' IDENTIFIED BY 'ZAQ!2wsx';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'193.169.200.241';
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 689
Binlog_Do_DB: test
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
以上结果创建一个主从复制的配置信息到服务器B上执行以下命令
CHANGE MASTER TO MASTER_HOST='193.169.200.240',MASTER_USER='repl',MASTER_PASSWORD='ZAQ!2wsx',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=689;
在服务器B上执行
CREATE USER 'repl'@'193.169.200.240' IDENTIFIED BY 'ZAQ!2wsx';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'193.169.200.240';
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS\G
将打印的结果创建一个主从复制信息到服务器A上执行
CHANGE MASTER TO MASTER_HOST='193.169.200.241',MASTER_USER='repl',MASTER_PASSWORD='ZAQ!2wsx',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=5336;
mysql主从复制
1.初始的时候保证test库中的表一致
2.start slave; 在服务器AB上执行开启主从
3.show slave status\G 查看主从信息
查看打印的信息
slave_IO_Running yes
slave_SQL_Running yes
代表成功!
问题总结与解决
1.在数据写入A服务器库中的实时候卡死了
show processList 查看mysql进程,查看是否有等待进程
kill id 杀死等待进程
2.同步的show slave status\G 中有错误日志打印, 说明同步过程中有异常
解决方法:忽略错误,继续同步
flush tables with read lock;
stop slave;
set global sql_slave_skip_counter =1;
start slave;
unlock tables
重新做主从同步,完全同步 (主从数据差别过大,无法通过上面忽略错误来解决)
1.在master上执行 240
flush tables with read lock;
mysqldump -uroot -p -hlocalhost --all-databases > mysql.sql (--all-databases表示所有数据库) 备份数据库
scp mysql.sql root@193.169.200.241:~/app
2.在从库上241上执行
stop slave
source ~/app/mysql.sql
3.在240上执行 SHOW MASTER STATUS\G
将结果重新编写到下面的命令中,并在241上执行
CHANGE MASTER TO MASTER_HOST='193.169.200.240',MASTER_USER='repl',MASTER_PASSWORD='ZAQ!2wsx',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=689
上面的值MASTER_LOG_POS根据实际的值更换
start slave
show slave status\G
unlock tables
ps: 在实际开发中, 发现A,B两机器不同步时,可能是双方都有数据写入,这个时候要重新再双方机器上都执行
$>stop slave
$>SHOW MASTER STATUS\G
然后在各自的机器上执行
$> CHANGE MASTER TO MASTER_HOST='A/B',MASTER_USER='repl',MASTER_PASSWORD='ZAQ!2wsx',MASTER_LOG_FILE='mysql-bin.00000??',MASTER_LOG_POS=???
1. stop salve 报错误: Can
解决方法:
1.查看进程
shwo processlist;
unlock tables; 解锁
2.可以正常执行stop salve