mysql主从复制

453423

一、主从同步复制

MySQL 5.6引入的GTID(Global Transaction IDs)使得其复制功能的配置、监控及管理变得更加易于实现，且更加健壮。

要在MySQL 5.6中使用复制功能，其服务配置段[mysqld]中应该定义如下选项：

binlog-format：二进制日志的格式，有row、statement和mixed几种类型；

需要注意的是：当设置隔离级别为READ-COMMITED必须设置二进制日志格式为ROW，现在MySQL官方认为STATEMENT这个已经不再适合继续使用；但mixed类型在默认的事务隔离级别下，可能会导致主从数据不一致；

log-slave-updates、gtid-mode、enforce-gtid-consistency、report-port和report-host：用于启动GTID及满足附属的其它需求；

master-info-repository和relay-log-info-repository：启用此两项，可用于实现在崩溃时保证二进制及从服务器安全的功能；

sync-master-info：启用之可确保无信息丢失；

slave-paralles-workers：设定从服务器的SQL线程数；0表示关闭多线程复制功能；

binlog-checksum、master-verify-checksum和slave-sql-verify-checksum：启用复制有关的所有校验功能；

binlog-rows-query-log-events：启用之可用于在二进制日志记录事件相关的信息，可降低故障排除的复杂度；

log-bin：启用二进制日志，这是保证复制功能的基本前提；

server-id：同一个复制拓扑中的所有服务器的id号必须惟一；

实验环境：

系统：centos6.6

数据库：mariadb-5.5.43

masterIP：192.168.43.113

slaveIP:192.168.43.110

1.修改master节点配置文件/etc/mysql/my.cnf

[mysqld]

binlog-format=ROW

log-bin=master-bin

log-slave-updates=true

gtid-mode=on

enforce-gtid-consistency=true

master-info-repository=TABLE

relay-log-info-repository=TABLE

sync-master-info=1

slave-parallel-workers=2

binlog-checksum=CRC32

master-verify-checksum=1

slave-sql-verify-checksum=1

binlog-rows-query-log_events=1

server-id=1

report-port=3306

port=3306

datadir=/mydata/data

socket=/tmp/mysql.sock

report-host=master.magedu.com

2.修改slave节点配置文件

[mysqld]

binlog-format=ROW

log-slave-updates=true

gtid-mode=on

enforce-gtid-consistency=true

master-info-repository=TABLE

relay-log-info-repository=TABLE

sync-master-info=1

slave-parallel-workers=2

binlog-checksum=CRC32

master-verify-checksum=1

slave-sql-verify-checksum=1

binlog-rows-query-log_events=1

server-id=11

report-port=3306

port=3306

log-bin=mysql-bin.log

datadir=/mydata/data

socket=/tmp/mysql.sock

report-host=slave.magedu.com

read-only = on

3.在主节点创建用于复制的用户（最小权限原则，给予尽量少的权限）

MariaDB [(none)]> grant replication client,replication slave on *.* to 'copyuser'@'192.168.%.%' identified by 'copypass';

MariaDB [(none)]> flush privileges;

4.启动从节点的复制线程

（1）查看主节点二进制日志文件名称和事件位置

MariaDB [(none)]> show master status;

+-------------------+----------+--------------+------------------+

| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+-------------------+----------+--------------+------------------+

| master-bin.000001 |      245 |              |                  |

+-------------------+----------+--------------+------------------+

（2）在从节点启动复制线程

MariaDB [(none)]> change master to master_host='192.168.43.113', master_user='copyuser', master_password='copypass', master_log_file='master-bin.000001', master_log_pos=245;

MariaDB [(none)]> start slave;

MariaDB [(none)]> show slave status\G;

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.43.113

                  Master_User: copyuser

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: master-bin.000001

          Read_Master_Log_Pos: 326

               Relay_Log_File: relay-bin.000002

                Relay_Log_Pos: 611

        Relay_Master_Log_File: master-bin.000001

             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: 326

              Relay_Log_Space: 899

              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         主从节点数据是否一致，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: 1

可以看到：

        Slave_IO_Running: Yes

        Slave_SQL_Running: Yes

证明已经启动成功

5.验证主从同步结果

（1）在主节点修改数据库数据

MariaDB [(none)]> create database xxx;

MariaDB [(none)]> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| test               |

| xxx                |

+--------------------+

（2）在从节点查看数据是否同步

MariaDB [(none)]> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| test               |

| xxx                |

+--------------------+

可以看到新建的数据库“xxx”已经同步到从节点，试验完成。

复制时应该注意的问题：

1、如何限制从服务器只读？

在从服务器启动read_only；但仅对非具有SUPER权限的用户有效；

阻止所有用户 ：MariaDB> FLUSH TABLES WITH READ LOCK;

2、如何保证主从复制时的事务安全？

在master节点启用参数：

sync_binlog = on

如果用到的为InnoDB存储引擎：

innodb_flush_logs_at_trx_commit

innodb_support_xa=on

在slave节点：

skip_slave_start  不自动启动slave_start，应该手动启动。

主节点：

sync_master_info = 1

从节点

sync_relay_log = 1

sync_relay_log_info = 1

二、半同步复制

基本原理：

客户端发起数据库修改操作之后，master服务器执行操作之后，不立即返回给客户端结果，而是等待slave服务器复制master的二进制日志文件执行同步操作，等slave执行完成同步操作返回给master同步完成信号，master才将执行结果返回给客户端。如果由于某种原因导致master在超时时间内没有收到slave节点的同步完成信号，则master转换为异步模式，不在管slave是否同步，客户端发起操作后，由master执行完成直接返回结果给客户端

需要安装两个由谷歌开源的插件（插件目录：/usr/local/mysql/lib/plugin）

（1）master服务器安装插件semisync_master.so

MariaDB [(none)]> install plugin rpl_semi_sync_master soname 'semisync_master.so';

Query OK, 0 rows affected (0.12 sec)

MariaDB [(none)]> show global variables like '%semi%';

+------------------------------------+-------+

| Variable_name                      | Value |

+------------------------------------+-------+

| rpl_semi_sync_master_enabled       | OFF   | 是否开启半同步复制

| rpl_semi_sync_master_timeout       | 10000 | 超时时长，超时后转为异步模式复制，默认10秒

| rpl_semi_sync_master_trace_level   | 32    |

| rpl_semi_sync_master_wait_no_slave | ON    |

+------------------------------------+-------+

4 rows in set (0.00 sec)

（2）设置全局变量，开启半同步复制，缩短超时时长

MariaDB [(none)]> set global rpl_semi_sync_master_enabled=1;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> set global rpl_semi_sync_master_timeout=2000;

Query OK, 0 rows affected (0.00 sec)

（3）slave服务器安装插件semisync_slave.so

MariaDB [(none)]> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';

Query OK, 0 rows affected (0.03 sec)

MariaDB [(none)]> show global variables like '%semi%';

+---------------------------------+-------+

| Variable_name                   | Value |

+---------------------------------+-------+

| rpl_semi_sync_slave_enabled     | OFF   |

| rpl_semi_sync_slave_trace_level | 32    |

+---------------------------------+-------+

2 rows in set (0.00 sec)

MariaDB [(none)]> set global rpl_semi_sync_slave_enabled=1;

Query OK, 0 rows affected (0.00 sec)

（4）重启IO_THREAD

MariaDB [(none)]> stop slave io_thread;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> start slave io_thread;

Query OK, 0 rows affected (0.00 sec)

（5）查看状态

MariaDB [(none)]> show global status like '%semi%';

+--------------------------------------------+-------+

| Variable_name                              | Value |

+--------------------------------------------+-------+

| Rpl_semi_sync_master_clients               | 1     |  客户端1个

| Rpl_semi_sync_master_net_avg_wait_time     | 0     |

| Rpl_semi_sync_master_net_wait_time         | 0     |

| Rpl_semi_sync_master_net_waits             | 0     |

| Rpl_semi_sync_master_no_times              | 0     |

| Rpl_semi_sync_master_no_tx                 | 0     |

| Rpl_semi_sync_master_status                | ON    |  半同步状态ON

| Rpl_semi_sync_master_timefunc_failures     | 0     |

| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |

| Rpl_semi_sync_master_tx_wait_time          | 0     |

| Rpl_semi_sync_master_tx_waits              | 0     |

| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |

| Rpl_semi_sync_master_wait_sessions         | 0     |

| Rpl_semi_sync_master_yes_tx                | 0     |

+--------------------------------------------+-------+

三、双主模式（互为主从）

双主模型简单介绍：

互为主从需注意：

1、可能产生数据不一致；

2、自动增长id需要隔离开，否则害怕自动增长字段冲突

定义一个节点使用奇数id

auto_increment_offset=1 自动增长ID由1开始

auto_increment_increment=2  每次增长步长为2

定义另一个节点使用偶数id

auto_increment_offset=2  自动增长ID由2开始

auto_increment_increment=2   每次增长步长为2

简单配置过程：

(1) 各自使用不同的server id

(2) 都启用binlog和relay log

(3) 定义自动增长的id字段的增长方式

(4) 都授权有复制权限的用户账号

(5) 各自把对方指定为主服务器

四、复制过滤器

让slave仅复制有限的几个数据库，而非所有；

有两种实现思路：

(1) 主服务器仅向二进制日志中记录有特定数据库相关的写操作；

问题：即时点还原将无法全面实现；

binlog_do_db=                  # 数据库白名单

binlog_ignore_db=           # 数据库黑名单

(2)从服务器的SQL_THREAD仅在中继日志中读取特定数据相关的语句并应用在本地；

问题：会造成网络带宽和磁盘IO的浪费；

Replicate_Do_DB=   执行中继日志的数据库白名单

Replicate_Ignore_DB=

Replicate_Do_Table=

Replicate_Ignore_Table=

Replicate_Wild_Do_Table=

Replicate_Wild_Ignore_Table=

五、基于SSL的复制

前提：支持SSL

(1) 主服务器端配置证书和私钥，并创建一个要求必须使用SSL连接的复制账号(REQUIRE SSL);

(2) SLAV端连接master时，使用MASTER_SSL相关的选项来配置证书等信息；

六、跟复制功能相关的文件

master.info：用于保存slave连接至master时的相关信息；

relay-log.info：保存了当前slave节点上已经复制的当前二进制日志和本地relay log日志对应关系；

7、复制的监控和维护

(1) 清理日志：PURGE

(2) 复制监控

SHOW MASTER STATUS

SHOW BINLOG EVENTS

SHOW BINARY LOGS

SHOW SLAVE STATUS

(3) 如何判断slave是否落后于master（show slave status;）               

Seconds_Behind_Master: 0

(4) 如何确定主从节点数据是否一致？

通过表自身的CHECKSUM检查SHOW TABLE STATUS\G;

使用percona-tools中pt-table-checksum

(5) 数据不一致的修复方法：

重新复制