一、背景介绍

MySQL主从复制能解决一定的单点故障问题,但其异步的工作特性存在一定的隐患,比如主节点事务提交后还未写入binlog,此时主节点故障后,但主节点认为事务已提交,从节点无法从主节点获取到该事务的信息,提升为主节点后就会造成数据丢失,为了解决这一问题,谷歌为mysql在5.5之后的版本引进了半同步复制

二、原理介绍

所谓半同步复制就是一主多从,或一主一从的时候,主节点事务提交后至少等待一个从节点写入中继日志,这样就保证了当主节点故障后,所有的操作都在中继日志中有保存,当等待从节点的时间超出设置范围,会回复异步的方式

三、环境介绍

本次实验使用CentOS7.4系统,5.5.56-MariaDB,实验拓扑如下图所示:

1.png

使用一个日志服务器(也可以是从服务器)作为与主节点半同步的服务器,其他从服务器通过日志服务器同步binlog,由于日志服务器自身只需要记录binlog,所以不需要数据在其上执行一遍,存储引擎可以使用blackhole

四、操作步骤

1.主节点服务器操作

(1)启动主节点服务,配置文件如下

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

skip_name_resolve=ON

log_bin=mysql-binlog

slow_query_log=ON

server-id=10

innodb_file_per_table=ON

binlog_format=ROW

(2)创建一个主从复制的账号

MariaDB [mysql]> grant replication slave on *.* to 'bak'@'172.16.10.%' identified by 'bakpass';

MariaDB [mysql]> flush privileges;

(3)安装并启动半同步插件(可写入配置文件永久生效)

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

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

(4)确认插件和功能以正常启动

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

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

| Variable_name                      | Value |

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

| rpl_semi_sync_master_enabled       | ON    |

| rpl_semi_sync_master_timeout       | 10000 |

| rpl_semi_sync_master_trace_level   | 32    |

| rpl_semi_sync_master_wait_no_slave | ON    |

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

MariaDB [(none)]> show plugins;

2.png

2.日志服务器操作

(1)启动日志服务器,配置文件如下

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

skip_name_resolve=ON

relay_log=mysql-relaylog

relay_log_index=mysql-relaylog

read_only=ON

relay_log_purge=ON

slow_query_log=ON

server-id=20

innodb_file_per_table=ON

default_storage_engine=blackhole

binlog_format=ROW

log_bin=mysql-binlog

log_slave_updates=ON

(2)关闭read_only,创建一个主从复制的账号后再开启

MariaDB [(none)]> set global read_only=0;

MariaDB [(none)]> grant replication slave on *.* to 'bak'@'172.16.10.%' identified by 'bakpass';

MariaDB [(none)]> flush privileges;

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

(3)安装并启动半同步插件

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

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

(4)确认插件和功能以正常启动

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

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

| Variable_name                   | Value |

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

| rpl_semi_sync_slave_enabled     | ON    |

| rpl_semi_sync_slave_trace_level | 32    |

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

MariaDB [(none)]> show plugins;

3.png

(5)指定主服务器及主服务器当前的binlog日志和position

MariaDB [hellodb]> change master to

   -> master_host='172.16.10.30',

   -> master_user='bak',

   -> master_password='bakpass',

   -> master_port=3306,

   -> master_log_file='mysql-binlog.000003',

   -> master_log_pos=7805,

   -> master_connect_retry=10;

(6)启动从节点(可以指启动指定线程类型,不指定为都启动)

MariaDB [hellodb]> start slave [IO_THREAD | SQL_THREAD];

(7)查看从节点状态(Slave_IO_Running和Slave_SQL_Running显示Yes成功)

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

3.验证日志服务器blackhole存储引擎是否生效

(1)主节点创建一个数据库及表

MariaDB [(none)]> create database ark;

MariaDB [(none)]> use ark;

MariaDB [ark]> CREATE TABLE `students` (   `StuID` int(10) primary key,   `Name` varchar(50) );

(2)确认当前主节点的binlog日志及position

MariaDB [ark]> show master status;

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

| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-binlog.000003 |     8638 |              |                  |

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

(3)确认日志服务器当前的binlog日志及position

MariaDB [ark]> show master status;

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

| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-binlog.000003 |     1104 |              |                  |

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

(4)主服务器上任意插入条数据,再次观察主节点的binlog日志及position

MariaDB [ark]> insert into students (stuid,name) values (3,'fu');

MariaDB [ark]> show master status;

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

| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-binlog.000003 |     8818 |              |                  |

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

(5)在日志服务器上查看中继日志以更新

MariaDB [ark]> show slave status \G

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

Slave_IO_State: Waiting for master to send event

Master_Host: 172.16.10.30

Master_User: bak

Master_Port: 3306

Connect_Retry: 10

Master_Log_File: mysql-binlog.000003

Read_Master_Log_Pos: 8818

Relay_Log_File: mysql-relaylog.000002

Relay_Log_Pos: 1545

Relay_Master_Log_File: mysql-binlog.000003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Exec_Master_Log_Pos: 8818

(6)并且日志服务器上的binlog日志也完成了更新

MariaDB [ark]> show master status;

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

| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-binlog.000003 |     1309 |              |                  |

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

(7)但此时表中却无任何数据,说明blackhole引擎工作正常

4.png

4.从节点服务器操作

从节点就是将日志服务器当成主节点,所有的同步是指向日志服务器

(1)启动从节点的mysql服务,启动relaylog日志,更改serverid

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

skip_name_resolve=ON

relay_log=mysql-relaylog

relay_log_index=mysql-relaylog

read_only=ON

relay_log_purge=ON

slow_query_log=ON

server-id=30

innodb_file_per_table=ON

(2)指定主服务器及主服务器当前的binlog日志和position

MariaDB [(none)]> change master to

   -> master_host='172.16.10.40',

   -> master_user='bak',

   -> master_password='bakpass',

   -> master_port=3306,

   -> master_log_file='mysql-binlog.000003',

   -> master_log_pos=1309,

   -> master_connect_retry=10;

(3)启动从节点(可以指启动指定线程类型,不指定为都启动)

MariaDB [(none)]> start slave [IO_THREAD | SQL_THREAD];

(5)查看从节点状态(Slave_IO_Running和Slave_SQL_Running显示Yes成功)

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

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

Slave_IO_State: Waiting for master to send event

Master_Host: 172.16.10.40

Master_User: bak

Master_Port: 3306

Connect_Retry: 10

Master_Log_File: mysql-binlog.000003

Read_Master_Log_Pos: 1309

Relay_Log_File: mysql-relaylog.000002

Relay_Log_Pos: 532

Relay_Master_Log_File: mysql-binlog.000003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

五、结果验证

(1)在日志服务器查看上游节点

MariaDB [(none)]> show slave hosts;

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

| Server_id | Host | Port | Master_id |

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

|        20 |      | 3306 |        10 |

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

(2)在从节点查看上游节点

MariaDB [(none)]> show slave hosts;

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

| Server_id | Host | Port | Master_id |

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

|        30 |      | 3306 |        20 |

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

(3)主库上创建个数据库,进行验证,至此操作完成

MariaDB [(none)]> create database wahaha;

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

5.png

Rpl_semi_sync_master_no_tx 没有成功接收slave提交的次数

补充说明:

1.blackhole存储引擎会将所有关于数据的操作丢弃,但是关于数据库结构的操作会执行(如创建数据库,表)

2.Mariadb5.5或者MySQL5.7以后的版本支持多主一从,从服务器可以成为多台MySQL服务器的统一日志服务器,但要求是多台MySQL服务器上数据库名称必须不相同,同时所有从服务器使用不同的复制账号,因为主服务器上每个I/O dump thread都需要用不同的账号发起

MariaDB [(none)]> show processlist \G

*************************** 3. row ***************************

     Id: 7

   User: bak

   Host: 172.16.10.40:47972

     db: NULL

Command: Binlog Dump

   Time: 15921

  State: Master has sent all binlog to slave; waiting for binlog to be updated

   Info: NULL

Progress: 0.000

*************************** 5. row ***************************

     Id: 17

   User: qwe

   Host: 172.16.10.50:58968

     db: NULL

Command: Binlog Dump

   Time: 1866

  State: Master has sent all binlog to slave; waiting for binlog to be updated

   Info: NULL

Progress: 0.000

3.如果是先开启了主从复制,再切换到半同步,只需要执行

MariaDB [(none)]> stop slave io_thread;

MariaDB [(none)]> start slave io_thread;

4.如果服务器重启,启动MySQL服务器后,plugin插件和主从复制功能会自动启动,但是半同步功能未启动,此时是默认的异步同步(或者配置完成半同步后将该选项写入配置文件),需要手动启动半同步功能和io_thread,执行命令为:

主节点:

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

从节点:

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

MariaDB [(none)]> stop slave io_thread;

MariaDB [(none)]> start slave io_thread;