mysql-双向主从复制

515 阅读3分钟

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刷下去, 01 相差5倍行读写性能或者更高
 8.binlog-do-db 需要同步的数据库,如果存在多个需要写多个,不可以写在一行
 9.auto-increment-increment id生成策略, 防止id冲突 这里设置为2 这样就会生成1,3,5  或者 2,4,6id
 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; 查看是否正常, slave_IO_Running  slave_SQL_Running 都为yes
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't execute the given command because you have active locked tables or an active transaction
    
解决方法:
1.查看进程
shwo processlist;
unlock tables; 解锁

2.可以正常执行stop salve