Mysql5.6主从同步实践过程

2017 年 1 月 4 日 jslfl linux, mysql

环境：centos6.8 + mysql5.6
主库IP：192.168.153.128
从库IP：192.168.153.129

有些配置可能mysql5.6+版本才支持

主库配置：
#vi /etc/my.cnf

# 启用二进制日志
log_bin = mysql-bin
#一般可用IP末段
server-id = 128
log-bin-index=mysql-bin.index

#几个事务提交后刷新日志到磁盘，默认0，性能最好风险最大，1风险最小性能最低
sync_binlog=1
binlog_format=mixed

#要同步数据库,可以逗号分隔多个
binlog-do-db = testdb

#忽略DB
binlog-ignore-db = mysql
binlog-ignore-db = performance_schema
binlog-ignore-db = information_schema
binlog-ignore-db = test

#binlog过期清理时间
expire_logs_days=10
#binlog每个日志文件大小,默认为1G
max_binlog_size=1G
#binlog缓存大小
binlog_cache_size=4m
#最大binlog缓存大小
max_binlog_cache_size=512m

主服务器创建同步用户，必须授予REPLICAITON SLAVE权限，在主服务器登陆mysql上执行
mysql -uroot -proot
mysql>grant replication slave on *.* to ‘replication’@’192.168.153.%’ identified by ‘root’;

replication@192.168.153.%这里是客户端的ip,可以使用 % 代替，表示允许任意的客户端，也可指定IP。

mysql> flush privileges;

重启mysql后查看主库状态
mysql> exit;
# service mysql restart
# mysql -uroot -proot
mysql> show master status;
mysql> show master status\G;

*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 120
     Binlog_Do_DB: rwdb
 Binlog_Ignore_DB: mysql,performance_schema,information_schema,test
Executed_Gtid_Set:
1 row in set (0.00 sec)

File 和 Position属性值在slave配置的时候会使用

从库配置
/etc/my.cnf

server_id=129
log-bin=mysql-bin
replicate-do-db=rwdb

mysql> change master to master_host=’192.168.153.128′, master_user=’replication’, master_password=’root’, master_log_file=’mysql-bin.000001′, master_log_pos=120;

master_log_file，master_log_pos由上面master查出的状态值确定。

启用slave;
start slave;

关闭slave;
stop slave;

查看状态：
mysql> show slave status\G;
正常状态
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.153.128
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 120
               Relay_Log_File: localhost-relay-bin.000003
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: rwdb
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 120
              Relay_Log_Space: 460
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 128
                  Master_UUID: 6d7cd1db-c8e6-11e6-b0e4-000c29c14ba9
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the sla                                                                                        ve I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)

在主库rwdb中建表和修改数据，测试同步效果

出错处理：
Slave_IO_Running: No
Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

mysql 5.6的复制引入了uuid的概念，各个复制结构中的server_uuid得保证不一样，因我是通过安装好环境后的虚拟机克隆方式，所以data文件夹后server_uuid是相同的
show variables like ‘%server_uuid%’;
修改data文件夹下auto.cnf中的uuid值，保证各个db的uuid不一样，重启

解决不同步
重新做主从，完全同步
适用于主从库数据相差较大，或者要求数据完全统一的情况

解决步骤如下：

先主库锁表，防止数据写入
mysql> flush tables with read lock;

进行数据备份

#把数据备份到mysql.bak.sql文件
#mysqldump -uroot -p -hlocalhost > mysql.bak.sql

注意：数据库备份一定要定期进行，确保数据万无一失

查看master 状态

mysql> show master status;

+-------------------+----------+--------------+-------------------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB              |
+-------------------+----------+--------------+-------------------------------+
| mysql-bin.000001 |     3423 |              | mysql,test,information_schema |
+-------------------+----------+--------------+-------------------------------+
1 row in set (0.00 sec)

把mysql备份文件传到从库机器，进行数据恢复
# scp mysql.bak.sql root@192.168.153.129:/tmp/

停止从库的状态
mysql> stop slave;

导入数据备份
mysql> source /tmp/mysql.bak.sql

设置从库同步，注意该处的同步点，就是主库show master status信息里的| File| Position两项
change master to master_host = ‘192.168.153.128’, master_user = ‘replication’, master_port=3306, master_password=’root’, master_log_file = ‘mysql-bin.000001’, master_log_pos=3423;

重新开启从同步
mysql> start slave;

查看同步状态
mysql> show slave status\G;

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

centos, linux, mysql