部署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;

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

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

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

42 rows in set (0.00 sec)

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

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

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW PLUGINS;

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

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

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

43 rows in set (0.00 sec)

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;

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

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

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

43 rows in set (0.00 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=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;

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

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

| 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.000004',

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)

发表于 2019-10-21 19:18 繁梦阅读(159) 评论(0) 编辑收藏举报