部署MySQL主从半同步复制

半同步复制支持多种插件：/usr/lib64/mysql/plugin/*

[root@localhost ~]# rpm -ql mariadb-server | grep semisync

/usr/lib64/mysql/plugin/semisync_master.so

/usr/lib64/mysql/plugin/semisync_slave.so

 

半同步复制插件：

 semisync_master.so

 semisync_slave.so

 

查看插件是否安装：

MariaDB [(none)]> SHOW PLUGINS;

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

| Name                           | Status   | Type               | Library | License |

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

 

在主节点安装并启用插件：

MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

Query OK, 0 rows affected (0.00 sec)

 

MariaDB [(none)]> SHOW PLUGINS;

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

| Name                           | Status   | Type               | Library            | License |

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

| binlog                         | ACTIVE   | STORAGE ENGINE     | NULL    

 

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%semi%';

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

| Variable_name                      | Value |

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

| rpl_semi_sync_master_enabled       | OFF   |

| rpl_semi_sync_master_timeout       | 10000 |

| rpl_semi_sync_master_trace_level   | 32    |

| rpl_semi_sync_master_wait_no_slave | ON    |

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

4 rows in set (0.00 sec)

 

MariaDB [(none)]> SET @@global.rpl_semi_sync_master_enabled=ON;

Query OK, 0 rows affected (0.00 sec)

 

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    |

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

4 rows in set (0.00 sec)

 

在从节点安装并启用插件：

MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

Query OK, 0 rows affected (0.00 sec)

 

MariaDB [(none)]> SHOW PLUGINS;

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

| Name                           | Status   | Type               | Library           | License |

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

| binlog                         | ACTIVE   | STORAGE ENGINE     | NULL       

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=ON;

Query OK, 0 rows affected (0.00 sec)

 

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%semi%';

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

| Variable_name                   | Value |

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

| rpl_semi_sync_slave_enabled     | ON    |

| rpl_semi_sync_slave_trace_level | 32    |

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

2 rows in set (0.00 sec)

 

主节点：

MariaDB [(none)]> SHOW GLOBAL STATUS LIKE '%semi%';

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

| Variable_name                              | Value |

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

| Rpl_semi_sync_master_clients               | 0     |

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

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

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

14 rows in set (0.00 sec)

 

从节点启动IO线程：

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)

 

主节点：

MariaDB [(none)]> SHOW GLOBAL STATUS LIKE '%semi%';

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

| Variable_name                              | Value |

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

| Rpl_semi_sync_master_clients               | 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    |

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

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

14 rows in set (0.00 sec)

 

在主节点为事先创建的用户进行主从复制授权：

MariaDB [(none)]> grant replication slave on *.* to 'myslave'@'192.168.200.%' identified by '123456';

Query OK, 0 rows affected (0.00 sec)

 

MariaDB [(none)]> flush privileges;

Query OK, 0 rows affected (0.00 sec)

 

MariaDB [(none)]> show master status;

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

| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-binlog.000003 |      487 |              |                  |

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

1 row in set (0.00 sec)

 

从节点：

MariaDB [(none)]> CHANGE MASTER TO

    -> MASTER_HOST='192.168.200.113',

    -> MASTER_USER='myslave',

    -> MASTER_PASSWORD='123456',

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

    -> MASTER_LOG_POS=487;

Query OK, 0 rows affected (0.02 sec)

 

MariaDB [(none)]> start slave;

Query OK, 0 rows affected (0.00 sec)

 

在主节点创建数据库mydb：

MariaDB [(none)]> CREATE DATABASE mydb CHARACTER SET 'utf8';

Query OK, 1 row affected (0.00 sec)

 

MariaDB [(none)]> show databases;

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

| Database           |

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

| information_schema |

| mydb               |

| mysql              |

| performance_schema |

| test               |

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

5 rows in set (0.00 sec)

 

从节点查看数据库是否复制成功：

MariaDB [(none)]> show databases;

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

| Database           |

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

| information_schema |

| mydb               |

| mysql              |

| performance_schema |

| test               |

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

5 rows in set (0.00 sec)

 

发现数据库mydb成功复制

使用数据库mydb在主节点进行数据操作：

MariaDB [(none)]> use mydb;

Database changed

MariaDB [mydb]> CREATE TABLE tbl1 (id INT,name VARCHAR(100));

Query OK, 0 rows affected (0.01 sec)

 

在主节点上观察：

MariaDB [mydb]> SHOW GLOBAL STATUS LIKE '%semi%';

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

| Variable_name                              | Value |

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

| Rpl_semi_sync_master_clients               | 1     |

| Rpl_semi_sync_master_net_avg_wait_time     | 734   |

| Rpl_semi_sync_master_net_wait_time         | 1469  |

| Rpl_semi_sync_master_net_waits             | 2     |

| Rpl_semi_sync_master_no_times              | 0     |

| Rpl_semi_sync_master_no_tx                 | 0     |

| Rpl_semi_sync_master_status                | ON    |

| Rpl_semi_sync_master_timefunc_failures     | 0     |

| Rpl_semi_sync_master_tx_avg_wait_time      | 902   |

| Rpl_semi_sync_master_tx_wait_time          | 1804  |

| Rpl_semi_sync_master_tx_waits              | 2     |

| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |

| Rpl_semi_sync_master_wait_sessions         | 0     |

| Rpl_semi_sync_master_yes_tx                | 2     |

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

14 rows in set (0.00 sec)

 

主节点继续操作：

MariaDB [mydb]> INSERT INTO tbl1 VALUES (1,'tom');

Query OK, 1 row affected (0.01 sec)

 

我们发现从节点的数据都在变化：

MariaDB [mydb]> SHOW GLOBAL STATUS LIKE '%semi%';

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

| Variable_name                              | Value |

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

| Rpl_semi_sync_master_clients               | 1     |

| Rpl_semi_sync_master_net_avg_wait_time     | 1055  |

| Rpl_semi_sync_master_net_wait_time         | 3166  |

| Rpl_semi_sync_master_net_waits             | 3     |

| Rpl_semi_sync_master_no_times              | 0     |

| Rpl_semi_sync_master_no_tx                 | 0     |

| Rpl_semi_sync_master_status                | ON    |

| Rpl_semi_sync_master_timefunc_failures     | 0     |

| Rpl_semi_sync_master_tx_avg_wait_time      | 1112  |

| Rpl_semi_sync_master_tx_wait_time          | 3338  |

| Rpl_semi_sync_master_tx_waits              | 3     |

| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |

| Rpl_semi_sync_master_wait_sessions         | 0     |

| Rpl_semi_sync_master_yes_tx                | 3     |

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

14 rows in set (0.00 sec)