MySQL主从同步和半同步配置

时间:2023-02-20 22:37:35
MySQL主从同步和半同步配置
mysql主从配置:
1,安装maraidb,使用国内yum镜像站下载:
[root@localhost mysql]# cat /etc/yum.repos.d/MairaDB.repo
# MariaDB 10.1 CentOS repository list - created 2018-03-13 03:39 UTC
# http://downloads.mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://mirrors.ustc.edu.cn/mariadb/yum/10.1/centos6-amd64
gpgkey=http://mirrors.ustc.edu.cn/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck=1
配置主从复制,首先要关闭selinux和iptables两个服务;
 关闭selinux:setenforce 0
 关闭防火墙:service iptables stop
主节点:192.168.125.200
从节点:192.168.125.201
2,编辑主节点my.cnf文件:
[mysqld]
innodb_file_per_table = 1   //开启独立的表空间
skip_name_resolve = 1  //禁止dns解析
log_bin = master-bin //开启二进制日志
relay_log = relay-bin //开启中继日志
server_id = 1  //server-id一定要唯一。
3,编辑从节点my.cnf文件:
[mysqld]
innodb_file_per_table = 1
skip_name_resolve = 1
log_bin = master-bin
relay_log = relay-bin
server_id = 2
read_only = 1   //仅仅只读
relay_log_purge = 0
4,在主节点创建可复制权限的账号:
MariaDB [(none)]> grant replication slave, replication client on *.* to 'glq'@'192.168.125.%' identified by 'glq';
5,主节点查看当前的日志名和当前位置:
MariaDB [(none)]> show master status;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000004 |      635 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
6,从节点添加复制数据的主服务器ip,用户和密码,以及二进制日志名称和日志位置:
CHANGE MASTER TO MASTER_HOST='192.168.125.200',MASTER_USER='glq',MASTER_PASSWORD='glq',MASTER_LOG_FILE='master-bin.000004',MASTER_LOG_POS=635;
7,查看复制状态:
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.125.200
                  Master_User: glq
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000004
          Read_Master_Log_Pos: 2620
               Relay_Log_File: relay-bin.000003
                Relay_Log_Pos: 1719
        Relay_Master_Log_File: master-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes 
半同步复制配置:
1,首先需要加载插件:
主节点:MariaDB [(none)]> install plugin rpl_semi_sync_master soname 'semisync_master.so';
从节点:MariaDB [dba]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
2,查看是否加载成功:
主节点:MariaDB [(none)]> show plugins;
| rpl_semi_sync_master          | ACTIVE   | REPLICATION        | semisync_master.so | GPL     |
从节点:MariaDB [dba]> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS  WHERE PLUGIN_NAME LIKE '%semi%';
+---------------------+---------------+
| PLUGIN_NAME         | PLUGIN_STATUS |
+---------------------+---------------+
| rpl_semi_sync_slave | ACTIVE        |
+---------------------+---------------+
1 row in set (0.00 sec)
3,启动半同步复制:
主节点:MariaDB [(none)]>  SET GLOBAL rpl_semi_sync_master_enabled = 1;
从节点:MariaDB [dba]> SET GLOBAL rpl_semi_sync_slave_enabled = 1;
4,重启从节点的IO线程:
MariaDB [dba]> stop slave IO_THREAD;
Query OK, 0 rows affected (0.01 sec)
MariaDB [dba]> start slave IO_THREAD;
Query OK, 0 rows affected (0.00 sec)
5,查看半同步是否在运行:
主节点:MariaDB [(none)]> show status like 'Rpl_semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |
| Rpl_semi_sync_master_net_avg_wait_time     | 5265  |
| Rpl_semi_sync_master_net_wait_time         | 21060 |
| Rpl_semi_sync_master_net_waits             | 4     |
| Rpl_semi_sync_master_no_times              | 2     |
| Rpl_semi_sync_master_no_tx                 | 9     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 328   |
| Rpl_semi_sync_master_tx_wait_time          | 657   |
| 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 [dba]> show status like 'Rpl_semi%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |   //已经在同步
+----------------------------+-------+
1 row in set (0.00 sec)
PS:当半同步复制发生超时时(由rpl_semi_sync_master_timeout参数控制,单位是毫秒,默认为10000,即10s),会暂时关闭半同步复制,转而使用异步复制。当master dump线程发送完一个事务的所有事件之后,如果在rpl_semi_sync_master_timeout内,收到了从库的响应,则主从又重新恢复为半同步复制。
验证是否正常:
1,先在主节点写入:
MariaDB [dba]> insert into dba.dba values(4);
Query OK, 1 row affected (0.01 sec)
MariaDB [dba]> show master status;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000004 |     2776 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
2,在从节点查看复制状态:
MariaDB [dba]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.125.200
                  Master_User: glq
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000004
          Read_Master_Log_Pos: 2776
               Relay_Log_File: relay-bin.000003
                Relay_Log_Pos: 694
        Relay_Master_Log_File: master-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
3,停止从节点slave:
MariaDB [dba]> stop slave;
Query OK, 0 rows affected (0.01 sec)
4,在主节点插入数据:
MariaDB [dba]> insert into dba.dba values(5);
Query OK, 1 row affected (10.01 sec)
//此处使用了10.01s时间。而这与rpl_semi_sync_master_timeout参数的时间相吻合。
MariaDB [dba]> show variables like '%Rpl%';
+------------------------------------+--------------+
| Variable_name                      | Value        |
+------------------------------------+--------------+
| rpl_semi_sync_master_enabled       | ON           |
| rpl_semi_sync_master_timeout       | 10000        |//此处配置的是10s
| rpl_semi_sync_master_trace_level   | 32           |
| rpl_semi_sync_master_wait_no_slave | ON           |
| rpl_semi_sync_master_wait_point    | AFTER_COMMIT |
+------------------------------------+--------------+
5 rows in set (0.00 sec)