MariaDB半同步测试

# 数据库 2024-05-05 05:19 0 21 来源： 云博客

环境：

  • 192.168.205.17: as master server
  • 192.168.205.27: as slave server
  • 192.168.205.37: as slave server

版本：

  • OS: centos 7 1810 with mini install
  • mariadb-5.5.60

目地：

默认情况下，MySQL的复制功能是异步的，异步复制可以提供最佳的性能，主库把binlog日志发送给从库即结束，并不验证从库是否接收完毕。这意味着当主服务器或从服务器端发生故障时，有可能从服务器没有接收到主服务器发送过来的binlog日志，这就会造成主服务器和从服务器的数据不一致，甚至在恢复时造成数据的丢失, 半同步解决了性能和数据安全综合，在同步从服务器时只要有一个从服务器确认同步完成，即返回结果，不用等到全部复制完成，这样解决了数据同步复制时要等所有从服务器返回结果，实现了最佳性能和数据安全的结合

步骤：

  1. 安装数据库在所有服务器，初始化运行以下脚本
     [root@master ~]#cat /data/maridb_yum.sh rpm -q mariadb-server ||yum install -y mariadb-servermkdir /data/{mysql,logs}chown mysql:mysql /data/{mysql,logs}sed -i ‘/\[mysqld\]/a log-bin=/data/logs/bin‘ /etc/my.cnfsed -i ‘/\[mysqld\]/a innodb_file_per_table = on‘ /etc/my.cnfsed -i ‘/\[mysqld\]/a skip_name_resolve = on‘ /etc/my.cnfsed -i ‘s@(datadir=).*@\1/data/mysql@‘ /etc/my.cnf
  2. 修改配置文件
    [root@master ~]#vi /etc/my.cnf[mysqld]server-id=17[root@slave1 ~]#vi /etc/my.cnf[mysqld]server-id=27 [root@slave2 ~]#vi /etc/my.cnf[mysqld]server-id=37
  3. 主服务器上建立同步帐号及，记录位置或备份主服器恢复从服务器二选一
    MariaDB [(none)]> grant replication slave on *.* to repluser@‘192.168.205.%‘ identified by ‘centos‘;MariaDB [(none)]> show master logs; +------------+-----------+| Log_name | File_size |+------------+-----------+| bin.000001 | 30373 || bin.000002 | 1038814 || bin.000003 | 406 |+------------+-----------+3 rows in set (0.00 sec)

  4. 从服务器上修改change master to，并启用slave

    MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST=‘192.168.205.17‘, -> MASTER_USER=‘repluser‘, -> MASTER_PASSWORD=‘centos‘, -> MASTER_PORT=3306, -> MASTER_LOG_FILE=‘bin.000003‘, -> MASTER_LOG_POS=406; Query OK, 0 rows affected (0.01 sec)MariaDB [(none)]> start slave;Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> show slave status\G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.205.17 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: bin.000003 Read_Master_Log_Pos: 406 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 523 Relay_Master_Log_File: bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes
  5. 要启用主从的半同步复制需要安装一个插件
    [root@centos7 data]#rpm -ql mariadb-server/usr/lib64/mysql/plugin/semisync_master.so/usr/lib64/mysql/plugin/semisync_slave.so
  6. 在数据库中可以查看那些插件安装
    MariaDB [(none)]> show plugins;
  7. 在主服务器上安装插件
    MariaDB [(none)]> install plugin rpl_semi_sync_master soname ‘semisync_master.so‘;Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> show plugins;…| rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL |+--------------------------------+----------+--------------------+--------------------+---------+43 rows in set (0.00 sec)
  8. 查看半同步的状态
    MariaDB [(none)]> show global variables like ‘%semi%‘ ;+------------------------------------+-------+| Variable_name | Value |+------------------------------------+-------+| rpl_semi_sync_master_enabled | OFF | #半同步默认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)
  9. 启用半同步
    MariaDB [(none)]> set global rpl_semi_sync_master_enabled=on;Query OK, 0 rows affected (0.00 sec)
  10. 查看半同步状态信息
    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)
  11. 在所有的从节点安装slave semi插件；
    MariaDB [(none)]> install plugin rpl_semi_sync_slave soname ‘semisync_slave.so‘; Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> show plugins;…| rpl_semi_sync_slave | ACTIVE | REPLICATION | semisync_slave.so | GPL |+--------------------------------+----------+--------------------+-------------------+---------+43 rows in set (0.00 sec)

  12. 查看slave的半同步状态，并启用，并需要重新启动线程，再查看半同步状态是on才可以

    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)]> stop slave;Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> start slave;Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> show global status like ‘%semi%‘; +----------------------------+-------+| Variable_name | Value |+----------------------------+-------+| Rpl_semi_sync_slave_status | ON |+----------------------------+-------+1 row in set (0.00 sec)
  13. 此时查看主节点的状态
    MariaDB [(none)]> show global status like ‘%semi%‘;+--------------------------------------------+-------+| Variable_name | Value |+--------------------------------------------+-------+| Rpl_semi_sync_master_clients | 2 || 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)
  14. 测试，在主节点上创建一个库
    MariaDB [(none)]> create database db1;Query OK, 1 row affected (0.00 sec)
  15. 在从节点上可以看到同步
    MariaDB [(none)]> show databases; +--------------------+| Database |+--------------------+| information_schema || db1 || mysql || performance_schema || test |+--------------------+5 rows in set (0.00 sec)
  16. 此进我们把slave1停掉,在主节点创建数据库，显示可以成功, 只会复制到slave2节点上
    [root@slave1 ~]#systemctl stop mariadbMariaDB [(none)]> create database db2; Query OK, 1 row affected (0.01 sec)MariaDB [(none)]> show databases;+--------------------+| Database |+--------------------+| information_schema || db1 || db2 || mysql || performance_schema || test |+--------------------+6 rows in set (0.00 sec)
  17. 如果全部停掉从节点，再从主节点创建数据库，会等10秒才会显示成功
    [root@slave2 ~]#systemctl stop mariadb;MariaDB [(none)]> create database db3;Query OK, 1 row affected (10.00 sec)
  18. 将两个服务器再重新启动后，查看数据也会同步
    [root@slave1 ~]#systemctl start mariadb MariaDB [(none)]> show databases;+--------------------+| Database |+--------------------+| information_schema || db1 || db2 || db3 || mysql || performance_schema || test |+--------------------+7 rows in set (0.00 sec)

相关文章

Confluence 6 关于嵌入的 H2 数据库
Confluence 6 关于嵌入的 H2 数据库
PHP7操作MongoDB的增删改查和分页操作
PHP7操作MongoDB的增删改查和分页操作
C3PO数据库连接池
C3PO数据库连接池
Mysql C API 字母顺序整理
Mysql C API 字母顺序整理
MySQL数据库篇之索引原理与慢查询优化之二
MySQL数据库篇之索引原理与慢查询优化之二
MySQL-5.6.36-多实例-部署(编译版)
MySQL-5.6.36-多实例-部署(编译版)