• MariaDB主从半同步复制详解


    半同步复制(Semisynchronous replication)

    介于异步复制和全同步复制之间,主库在执行完客户端提交的事务后不是立刻返回给客户端,而是等待至少一个从库接收到并写到relay log中才返回给客户端。相对于异步复制,半同步复制提高了数据的安全性,同时它也造成了一定程度的延迟,这个延迟最少是一个TCP/IP往返的时间。所以,半同步复制最好在低延时的网络中使用。

      
    潜在的问题

    客户端事务在存储引擎层提交后,在得到从库确认的过程中,主库宕机了,此时,可能的情况有两种:
     1.事务还没发送到从库上:此时,客户端会收到事务提交失败的信息,客户端会重新提交该事务到新的主上,当宕机的主库重新启动后,以从库的身份重新加入到该主从结构中,会发现,该事务在从库中被提交了两次,一次是之前作为主的时候,一次是被新主同步过来的。
     2.事务已经发送到从库上:此时,从库已经收到并应用了该事务,但是客户端仍然会收到事务提交失败的信息,重新提交该事务到新的主上。


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

    需要安装方可使用:

    MariaDB [mydb]> INSTALL PLUGIN plugin_name SONAME 'shared_library_name'; 
    

    半同步复制插件:
     semisync_master.so
     semisync_slave.so


    主从复制演示示例:

    分别在配置文件server.cnf的[mysqld]段添加如下配置:
    主节点:

    [root@node1 ~]# vim /etc/my.cnf.d/server.cnf
     [mysqld]
     skip_name_resolve=ON
     innodb_file_per_table=ON
     server_id=1
     log_bin=log-bin
    

    从节点:

    [root@node2 ~]# vim /etc/my.cnf.d/server.cnf
     [mysqld]
     skip_name_resolve=ON
     innodb_file_per_table=ON
     server_id=2
     relay_log=relay-log
    

    查看插件是否安装:

    MariaDB [mydb]> SHOW PLUGINS;
    +--------------------------------+----------+--------------------+---------+---------+
    | Name                           | Status   | Type               | Library | License |
    +--------------------------------+----------+--------------------+---------+---------+
    | binlog                         | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
    | mysql_native_password          | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
    | mysql_old_password             | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
    | MRG_MYISAM                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
    | MEMORY                         | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
    | MyISAM                         | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
    | CSV                            | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
    | PERFORMANCE_SCHEMA             | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
    | ARCHIVE                        | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
    | InnoDB                         | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
    | INNODB_RSEG                    | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
    | INNODB_UNDO_LOGS               | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
    | INNODB_TRX                     | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
    | INNODB_LOCKS                   | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
    | INNODB_LOCK_WAITS              | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
    | INNODB_CMP                     | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
    | INNODB_CMP_RESET               | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
    | INNODB_CMPMEM                  | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
    | INNODB_CMPMEM_RESET            | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
    | INNODB_SYS_TABLES              | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
    | INNODB_SYS_TABLESTATS          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
    | INNODB_SYS_INDEXES             | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
    | INNODB_SYS_COLUMNS             | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
    | INNODB_SYS_FIELDS              | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
    | INNODB_SYS_FOREIGN             | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
    | INNODB_SYS_FOREIGN_COLS        | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
    | INNODB_SYS_STATS               | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
    | INNODB_TABLE_STATS             | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
    | INNODB_INDEX_STATS             | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
    | INNODB_BUFFER_POOL_PAGES       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
    | INNODB_BUFFER_POOL_PAGES_INDEX | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
    | INNODB_BUFFER_POOL_PAGES_BLOB  | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
    | XTRADB_ADMIN_COMMAND           | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
    | INNODB_CHANGED_PAGES           | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
    | INNODB_BUFFER_PAGE             | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
    | INNODB_BUFFER_PAGE_LRU         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
    | INNODB_BUFFER_POOL_STATS       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
    | FEDERATED                      | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
    | BLACKHOLE                      | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
    | Aria                           | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
    | FEEDBACK                       | DISABLED | INFORMATION SCHEMA | NULL    | GPL     |
    | partition                      | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
    +--------------------------------+----------+--------------------+---------+---------+
    

    经过查看半同步插件并没有安装,我们需要安装上去:

    MariaDB [mydb]> HELP INSTALL;
     Name: 'INSTALL PLUGIN'
     Description:
     Syntax:
     INSTALL PLUGIN plugin_name SONAME 'shared_library_name'
    

    在主节点安装插件:

    MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
    MariaDB [(none)]> SHOW PLUGINS;
    +--------------------------------+----------+--------------------+--------------------+---------+
    | Name                           | Status   | Type               | Library            | License |
    +--------------------------------+----------+--------------------+--------------------+---------+
    | rpl_semi_sync_master           | ACTIVE   | REPLICATION        | semisync_master.so | GPL     |
    +--------------------------------+----------+--------------------+--------------------+---------+
    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    |
    +------------------------------------+-------+
    MariaDB [(none)]> SET @@global.rpl_semi_sync_master_enabled=ON;
    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)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
    MariaDB [(none)]> SHOW PLUGINS;
    +--------------------------------+----------+--------------------+-------------------+---------+
    | Name                           | Status   | Type               | Library           | License |
    +--------------------------------+----------+--------------------+-------------------+---------+
    | rpl_semi_sync_slave            | ACTIVE   | REPLICATION        | semisync_slave.so | GPL     |
    +--------------------------------+----------+--------------------+-------------------+---------+
    MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%semi%';
    +---------------------------------+-------+
    | Variable_name                   | Value |
    +---------------------------------+-------+
    | rpl_semi_sync_slave_enabled     | OFF   |
    | rpl_semi_sync_slave_trace_level | 32    |
    +---------------------------------+-------+
    MariaDB [(none)]> SET @@global.rpl_semi_sync_slave_enabled=ON;
    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 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     |
    +--------------------------------------------+-------+
    

    从节点启动IO线程:

    MariaDB [(none)]> STOP SLAVE IO_THREAD;
    MariaDB [(none)]> START SLAVE IO_THREAD;
    

    主节点:

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

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

    MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'172.18.67.%' IDENTIFIED BY 'replpass';
    MariaDB [(none)]> FLUSH PRIVILEGES;
    MariaDB [(none)]> SHOW MASTER STATUS;
    +----------------+----------+--------------+------------------+
    | File           | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +----------------+----------+--------------+------------------+
    | log-bin.000003 |      497 |              |                  |
    +----------------+----------+--------------+------------------+
    

    从节点:

    MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='172.18.67.11',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='log-bin.000003',MASTER_LOG_POS=497;
    MariaDB [(none)]> START SLAVE;
    

    在主节点创建数据库mydb:

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

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

    MariaDB [(none)]> SHOW DATABASES;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mydb               |
    | mysql              |
    | performance_schema |
    | test               |
    +--------------------+
    

    发现数据库mydb成功复制

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

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

    在从节点上观察:

    MariaDB [mydb]> SHOW GLOBAL STATUS LIKE '%semi%';
    +--------------------------------------------+-------+
    | Variable_name                              | Value |
    +--------------------------------------------+-------+
    | Rpl_semi_sync_master_clients               | 1     |
    | Rpl_semi_sync_master_net_avg_wait_time     | 487   |
    | Rpl_semi_sync_master_net_wait_time         | 487   |
    | Rpl_semi_sync_master_net_waits             | 1     |
    | 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      | 646   |
    | Rpl_semi_sync_master_tx_wait_time          | 646   |
    | Rpl_semi_sync_master_tx_waits              | 1     |
    | Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
    | Rpl_semi_sync_master_wait_sessions         | 0     |
    | Rpl_semi_sync_master_yes_tx                | 1     |
    +--------------------------------------------+-------+
    

    主节点继续操作:

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

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

    MariaDB [mydb]> SHOW GLOBAL STATUS LIKE '%semi%';
    +--------------------------------------------+-------+
    | Variable_name                              | Value |
    +--------------------------------------------+-------+
    | Rpl_semi_sync_master_clients               | 1     |
    | Rpl_semi_sync_master_net_avg_wait_time     | 910   |
    | Rpl_semi_sync_master_net_wait_time         | 1821  |
    | 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      | 734   |
    | Rpl_semi_sync_master_tx_wait_time          | 1469  |
    | 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     |
    +--------------------------------------------+-------+
    
  • 相关阅读:
    matrix_2015_1 138
    2014ACM/ICPC亚洲区广州站 北大命题
    无向图的联通分量
    5.1 基础题目选讲
    URAL
    Codeforces Round #274 (Div. 2)
    后缀数组
    poj 1661 help jimmy dp
    hdu 1676 Full Tank? 限制最短路 dp 蛮有技巧的~
    hdu 1023 Train Problem II 双向广搜
  • 原文地址:https://www.cnblogs.com/iuskye/p/6965439.html
Copyright © 2020-2022  润新知