Linux配置Mysql5.7.9主-从、主-主同步

前言

上一篇学着在Linux下编译安装Mysql5.7.9，刚好有时间，在此基础上搭建一下Mysql的主从、主主同步！

环境

  • Master：

    Linux发行版本： Oracle Linux Server release 6.5

    Os版本： 3.8.13-98.4.1.el6uek.x86_64

    Host： 192.168.1.253

  • Slave：

    Linux发行版本： CentOS release 6.6 (Final)

    Os版本：2.6.32-504.el6.x86_64

    Host： 192.168.1.225

Slave数据库安装

参见：Linux编译安装Mysql5.7.9 | 57°极客闷烧

Master配置

在my.cnf中添加如下配置：

1
2
3
4
5
6
7
8
9
10
[mysqld]
server-id=1                    
log-bin=mysql-bin    
binlog_format="ROW"            
max_binlog_size = 500M         
binlog_cache_size = 128K     
binlog-do-db = xwy             
binlog-ignore-db = mysql     
log-slave-updates                                         
expire_logs_day=2

参数说明：

server-id:
主从复制中标识不同的主机
log-bin:
开启二进制日志，默认存在Mysql数据文件目录下,文件名以此为前缀，如mysql-bin.000001；默认大小1G
binlog_format:
日志格式，有SATEMENT、ROW、MIXED三个选项，对应复制模式：SBR、RBR、MBR.详细说明可参考：MYSQL复制参数binlog_format | Dong‘s Note
max_binlog_size:
每个bin-log最大大小，当此大小等于500M时会自动生成一个新的日志文件。一条记录不会写在2个日志文件中，所以有时日志文件会超过此大小。
binlog_cache_size:
日志缓存大小
binlog-do-db:
需要同步的数据库名字，如果是多个，就以此格式在写一行即可。
binlog-ignore-db:
不需要同步的数据库名字，如果是多个，就以此格式在写一行即可。
log-slave-updates:
当Slave从Master数据库读取日志时更新新写入日志中，如果只启动log-bin 而没有启动log-slave-updates则Slave只记录针对自己数据库操作的更新。
expire_logs_day:
设置bin-log日志文件保存的天数，此参数mysql5.0以下版本不支持。

Slave配置

在my.cnf中添加如下配置：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
[mysqld]           
server-id=2

#binlog

log-bin=mysql-bin
binlog_format=ROW    
#binlog-row-image = minimal
#gtid_mode = ON
#enforce-gtid-consistency = true
binlog_cache_size = 4M
max_binlog_size = 500M
max_binlog_cache_size = 500M
sync_binlog = 1
replicate-do-db = xwy
replicate-ignore-db = mysql
replicate_do_table=t_xwy
replicate_ignore_iable=t_test
master-connect-retry=30
slave-skip-errors = 1062
expire_logs_days = 3
skip-slave-start=1
slave_net_timeout=20
slave_parallel_workers = 0 
read-only =0 

#relay log
relay-log=/home/tools/mysql/data/relay.log #设置中继日志文件基本名
max_relay_log_size = 500M

#以下两个是启用relaylog的自动修复功能，避免由于网络之类的外因造成日志损坏，主从停止。
relay_log_purge = 1
relay_log_recovery = 1

#以下四个参数是启用binlog/relaylog的校验，防止日志出错
binlog_checksum = CRC32
slave_allow_batching = 1
master_verify_checksum = 1
slave_sql_verify_checksum = 1
binlog_rows_query_log_events = 1

#以下两个参数会将master.info和relay.info保存在表中，默认是Myisam引擎，官方建议用
#master_info_repository = TABLE
#relay_log_info_repository = TABLE
log_slave_updates

参数说明：

binlog-row-image:
这个选项允许应用程序只能对行的镜像数据进行复制，而不在关心行是否已经进行了DML操作。这提高了主从机器的复制吞吐量，减少了二进制日志所占用的磁盘空间、网络资源和内存占用。
replicate-do-db:
复制的db
replicate-ignore-db:
忽略复制的db
replicate_do_table:
设定需要复制的Table
replicate_ignore_iable:
设定可以忽略的Table
master-connect-retry:
这个选项控制重试间隔，默认为60秒。
slave-skip-errors:
同步过程中忽略掉的错误，如果有多个用逗号隔开。这些错误不会影响数据的完整性，有事经常出现的错误，一般设置忽略。其中1062为主键重复错误。
expire_logs_days:
设置bin-log日志文件保存的天数，此参数mysql5.0以下版本不支持。
skip-slave-start:
动从库但不立即启动从库的复制进程，以便对从库进行进一步的设置。
slave_net_timeout:
设置网络超时时间，即多长时间测试一下主从是否连接，默认为3600秒。
slave_parallel_workers:
默认是0，不开启，最大并发数为1024个线程。主从复制启用4个sql线程，提高从服务器吞吐量，减少延迟,使用并发的 SQL 线程对不同数据库并行应用事件，如果只同步一个库的，指定为0，否则会阻塞。
read-only:
设置Slave为只读，但具有super权限的用户仍然可写。主主复制时必须关闭。
log_slave_updates：
该参数用来控制Slave上的更新是否写入二进制日志，默认为0；若Slave只作为从服务器，则不必启用；若Slave作为其他服务器的Master，则需启用，启用时需和log-bin、binlog-format一起使用，这样Slave从主库读取日志并重做，然后记录到自己的二进制日志中.

Mater创建复制用户

1
mysql> GRANT REPLICATION SLAVE on *.* to 'slave'@'%' IDENTIFIED BY '123456';

Mater备份数据

锁表

1
mysql> flush tables with read lock;

获取日志及偏移量

1
2
3
4
5
6
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000020 |      194 |              |                  | 44c86870-999c-11e5-b608-08002718c663:1-3 |
+------------------+----------+--------------+------------------+------------------------------------------+

备份

1
2
# mysqldump -uroot -pmyroot --all-database 
>/home/mysql/data/dbback.sql;

恢复写操作

1
mysql> unlock tables;

Slave同步

恢复数据

1
mysql -uroot -pmyroot </home/mysql/data/dbback.sql

同步设置

1
2
3
4
5
6
7
8
mysql> slave stop;
mysql> reset slave;
mysql> change master to
    -> master_host='192.168.1.253',
    -> master_user='slave',
    -> master_password='123456',
    -> master_log_file='mysql-bin.000020',
    -> master_log_pos=194;

启动同步线程

1
mysql> start slave;

查看slave状态

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
mysql> show slave status\G;
*************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.1.225
                      Master_User: slave
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000020
              Read_Master_Log_Pos: 154
                   Relay_Log_File: new-relay-bin.000006
                    Relay_Log_Pos: 367
            Relay_Master_Log_File: mysql-bin.000020
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                  Replicate_Do_DB: 
              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: 154
                  Relay_Log_Space: 785
                  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: 2
                      Master_UUID: aaf502d8-a886-11e5-a976-08002787c6af
                 Master_Info_File: /home/mysql/data/master.info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
               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: 44c86870-999c-11e5-b608-08002718c663:1-3
                    Auto_Position: 0
             Replicate_Rewrite_DB: 
                     Channel_Name:

说明：

Slave_IO_Running――此进程负责从Slave从Master上读取binlog日志，并写入Slave上的中继日志。

Slave_SQL_Running――此进程负责读取并执行中继日志中的binlog日志。
这两个进程的状态需全部为YES，只要有一个为NO，则复制就会停止。

当Slave中Relay_Master_Log_File和Master_Log_File相同且Read_Master_Log_Pos和Exec_Master_Log_Pos完全相同时，则表明Slave和Master处于完全同步的状态。

测试

1
2
3
4
5
6
7
8
9
Master上:
    create database xwy;
    create table xwy.test (id int,name varchar(200));
    insert into xwy.test values (1,'xwy');
    insert into xwy.test values (2,'xwy');
    select * from xwy.test;
Slave上:
    use xwy;
    select * from xwy.test;

观察两边结果是否一致。

Slave修改启动开启同步进程

注释掉配置文件my.cnf中的skip-slave-start=1 or skip-slave-start=true

添加新的Slave

原理跟添加第一台Slave一样，修改配置文件my.cnf中的server-id=3,就可以啦。

主主复制

只需要将Slave配置中相关参数添加到Master中即可。注意因为双方需要复制更新，因此记得关闭my.cnf中的read-only =1为read-only =0，并开启log_slave_updates（slave更新写入bin-log）.

常见错误及处理

问题1

现象

在从库上面show slave status\G;显示:
Slave_IO_Running: Yes
Slave_SQL_Running: No
Seconds_Behind_Master: NULL

原因

**a.**程序可能在slave上进行了写操作；

**b.**也可能是slave机器重起后，事务回滚造成的；

**c.**有可能是在同步过程中遇到某种错误，这个会在查看从库中状态时看到错误提示，最少见的就是主键重复1062的错误。

解决方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Master:
mysql> show master status;

+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000021 |      102 |              |                  | 44c86870-999c-11e5-b608-08002718c663:1-3 |
+------------------+----------+--------------+------------------+------------------------------------------+

Slave:
mysql> slave stop;
mysql> change master to
    -> master_host='192.168.1.253',
    -> master_user='slave',
    -> master_password='123456',
    -> master_log_file='mysql-bin.000021',
    -> master_log_pos=102;
mysql> slave start;

问题2

现象

从数据库无法同步

在从库上面show slave status\G;显示:

1
2
3
4
show slave status\G;
Slave_IO_Running: No
Slave_SQL_Running: Yes
Seconds_Behind_Master: NULL

原因

这个现象主要是master数据库存在问题，由于连接主库信息错误、主库数据库挂掉如果说常见错等原因引起的，我在实际的操作中先重启master后重启slave即可解决这问题，出现此问题，必须要要重启master数据库。

解决

首先查看数据库的err日志，查看是什么错误提示，看从库连接主库的IP、用户、密码等相关信息是否有误，如果有误，重新执行同步；如果确认无误，重启主数据库。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Master:
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000001 |      94 |              |                  | 44c86870-999c-11e5-b608-08002718c663:1-3 |
+------------------+----------+--------------+------------------+------------------------------------------+
Slave:
mysql> slave stop;
mysql> change master to
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=94;
mysql> slave start;
或：
mysql> slave stop;
mysql> set global sql_slave_skip_counter =1;
mysql> slave start;

参考

Mysql数据库主从心得整理 | Sina App Engine Blog
MySQL同步参数Seconds_Behind_Master=0 并不能确定主从是否延时 – WEB·攻城志
mysql relay log参数汇总-wzq609-ITPUB博客
mysql5.7.9主从同步配置 - tanweii163的专栏 - 博客频道 - CSDN.NET