• 技术文章 >数据库 >mysql教程

    MySQL复制技术之异步复制和半同步复制

    长期闲置长期闲置2022-04-25 20:59:59转载682
    本篇文章给大家带来了关于mysql的相关知识,其中主要介绍了关于MySQL复制技术的相关问题,包括了异步复制、半同步复制等等内容,下面一起来看一下,希望对大家有帮助。

    推荐学习:mysql视频教程

    异步复制

    image-20220209183951427

    在异步复制(async replication)中,Master不用关心Slave是否接收到二进制日志,所以Master与Slave没有任何的依赖关系。你可以认为Master和Slave是分别独自工作的两台服务器,数据最终会通过二进制日志达到一致。

    异步复制的性能最好,因为它对数据库本身几乎没有任何开销,除非主从延迟非常大,Dump Thread需要读取大量二进制日志文件。

    如果业务对于数据一致性要求不高,当发生故障时,能容忍数据的丢失,甚至大量的丢失,推荐用异步复制,这样性能最好(比如像微博这样的业务,虽然它对性能的要求极高,但对于数据丢失,通常可以容忍)。但往往核心业务系统最关心的就是数据安全,比如监控业务、告警系统。

    异步复制环境的规划:

    master的配置

    配置文件my.cnf

    $ sudo cat /home/mysql/docker-data/3311/conf/my.cnf
    # For advice on how to change settings please see
    # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
    [mysqld]
    #
    # Remove leading # and set to the amount of RAM for the most important data
    # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
    # innodb_buffer_pool_size = 128M
    #
    # Remove leading # to turn on a very important data integrity option: logging
    # changes to the binary log between backups.
    # log_bin
    #
    # Remove leading # to set options mainly useful for reporting servers.
    # The server defaults are faster for transactions and fast SELECTs.
    # Adjust sizes as needed, experiment to find the optimal values.
    # join_buffer_size = 128M
    # sort_buffer_size = 2M
    # read_rnd_buffer_size = 2M
    #datadir=/home/mysql/docker-data/3307/data
    #socket=/home/mysql/docker-data/3307/mysql.sock
    character_set_server=utf8
    init_connect='SET NAMES utf8'
    # Disabling symbolic-links is recommended to prevent assorted security risks
    symbolic-links=0
    #log-error=/home/mysql/docker-data/3307/logs/mysqld.log
    #pid-file=/home/mysql/docker-data/3307/mysqld.pid
    lower_case_table_names=1 # 表名是否小写
    server-id=1403311
    log-bin=mysql-bin # 开启binlog
    binlog-format=ROW # binlog的格式
    auto_increment_increment=1 # 自增的步长,适用于主主复制,为了避免id冲突,步长设置为master的个数
    auto_increment_offset=1 # 自增的偏移,主主复制每个master的偏移需要不一致
    # binlog-do-db=mstest      # 要同步的数据库
    # binlog-ignore-db=mysql  # 要忽略的数据库
    #rpl_semi_sync_master_enabled=1
    #rpl_semi_sync_master_timeout=10000

    启动mysql:

    $ docker run --name mysql3310 -p 3310:3306 --privileged=true -ti -e MYSQL_ROOT_PASSWORD=root -e MYSQL_DATABASE=order -e MYSQL_USER=user -e MYSQL_PASSWORD=pass -v /home/mysql/docker-data/3310/conf:/etc/mysql/conf.d -v /home/mysql/docker-data/3310/data/:/var/lib/mysql -v /home/mysql/docker-data/3310/logs/:/var/log/mysql -d mysql:5.7

    创建用于同步的用户:

    mysql> GRANT REPLICATION SLAVE,FILE,REPLICATION CLIENT ON *.* TO 'repluser'@'%' IDENTIFIED BY '123456';
    Query OK, 0 rows affected, 1 warning (0.01 sec)
    mysql> FLUSH PRIVILEGES;
    Query OK, 0 rows affected (0.01 sec)

    在master上查看master的二进制日志:

    mysql> show master status;
    +------------------+----------+--------------+------------------+-------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +------------------+----------+--------------+------------------+-------------------+
    | mysql-bin.000003 |      1147 |              |                  |                   |
    +------------------+----------+--------------+------------------+-------------------+
    1 row in set (0.00 sec)

    查看master上的进程列表:

    mysql> show processlist;
    +----+----------+------------------+-------+-------------+------+---------------------------------------------------------------+------------------+
    | Id | User     | Host             | db    | Command     | Time | State                                                         | Info             |
    +----+----------+------------------+-------+-------------+------+---------------------------------------------------------------+------------------+
    |  2 | root     | localhost        | order | Query       |    0 | starting                                                      | show processlist |
    |  6 | repluser | 172.17.0.1:48450 | NULL  | Binlog Dump |  448 | Master has sent all binlog to slave; waiting for more updates | NULL             |
    +----+----------+------------------+-------+-------------+------+---------------------------------------------------------------+------------------+
    2 rows in set (0.00 sec)

    slave的配置

    配置文件my.cnf与master的配置一致,除了server-id字段需唯一。

    启动mysql:

    $ docker run --name mysql3311 -p 3311:3306 --privileged=true -ti -e MYSQL_ROOT_PASSWORD=root -e MYSQL_DATABASE=order -e MYSQL_USER=user -e MYSQL_PASSWORD=pass -v /home/mysql/docker-data/3311/conf:/etc/mysql/conf.d -v /home/mysql/docker-data/3311/data/:/var/lib/mysql -v /home/mysql/docker-data/3311/logs/:/var/log/mysql -d mysql:5.7

    在slave中设置master的信息:

    # master_log_file和master_log_pos取上面show master status显示的值mysql> change master to master_host='172.23.252.98',master_port=3310,master_user='repluser',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=1147;

    开启slave,启动SQL和IO线程:

    mysql> start slave;Query OK, 0 rows affected (0.00 sec)

    查看slave的状态:

    mysql> show slave status\G;*************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.54.214
                      Master_User: repluser
                      Master_Port: 3310
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000003
              Read_Master_Log_Pos: 1147
                   Relay_Log_File: 2da789531bf3-relay-bin.000002
                    Relay_Log_Pos: 320
            Relay_Master_Log_File: mysql-bin.000003
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                  Replicate_Do_DB:
              Replicate_Ignore_DB:
               Replicate_Do_Table:
           Replicate_Ignore_Table:
          Replicate_Wild_Do_Table:
      Replicate_Wild_Ignore_Table:
                       Last_Errno: 0
                       Last_Error:
                     Skip_Counter: 0
              Exec_Master_Log_Pos: 1147
                  Relay_Log_Space: 534
                  Until_Condition: None
                   Until_Log_File:
                    Until_Log_Pos: 0
               Master_SSL_Allowed: No
               Master_SSL_CA_File:
               Master_SSL_CA_Path:
                  Master_SSL_Cert:
                Master_SSL_Cipher:
                   Master_SSL_Key:
            Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No
                    Last_IO_Errno: 0
                    Last_IO_Error:
                   Last_SQL_Errno: 0
                   Last_SQL_Error:
      Replicate_Ignore_Server_Ids:
                 Master_Server_Id: 1403311
                      Master_UUID: cd2eaa0a-7a59-11ec-b3b4-0242ac110002
                 Master_Info_File: /var/lib/mysql/master.info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
               Master_Retry_Count: 86400
                      Master_Bind:
          Last_IO_Error_Timestamp:
         Last_SQL_Error_Timestamp:
                   Master_SSL_Crl:
               Master_SSL_Crlpath:
               Retrieved_Gtid_Set:
                Executed_Gtid_Set:
                    Auto_Position: 0
             Replicate_Rewrite_DB:
                     Channel_Name:
               Master_TLS_Version:1 row in set (0.00 sec)

    只有新增的数据才会进行复制,存量的数据需要手动使用工具(如mysqldump)进行同步。

    查看slave上的进程列表:

    mysql> show processlist;
    +----+-------------+-----------+-------+---------+------+--------------------------------------------------------+------------------+
    | Id | User        | Host      | db    | Command | Time | State                                                  | Info             |
    +----+-------------+-----------+-------+---------+------+--------------------------------------------------------+------------------+
    |  4 | root        | localhost | order | Query   |    0 | starting                                               | show processlist |
    |  7 | system user |           | NULL  | Connect |  533 | Waiting for master to send event                       | NULL             |
    |  8 | system user |           | NULL  | Connect |  127 | Slave has read all relay log; waiting for more updates | NULL             |
    +----+-------------+-----------+-------+---------+------+--------------------------------------------------------+------------------+
    3 rows in set (0.00 sec)

    半同步复制

    半同步复制是MySQL 5.7版本前的半同步复制机制。

    image-20220209184132921

    半同步复制要求Master事务提交过程中,至少有N个Slave接收到二进制日志,这样就能保证当Master发生宕机,至少有N 台Slave服务器中的数据是完整的。

    半同步复制并不是MySQL内置的功能,而是要安装半同步插件,并启用半同步复制功能,设置N个Slave接受二进制日志成功。

    缺点:假设user1在主库插入了一条数据,正在等待数据返回,user2这时就能查询到这条数据了,如果此时master挂了,user2又查不到这条数据了,产生了类似幻读的现象。

    增强半同步复制

    增强半同步复制解决了半同步复制的缺点,WAIT和ACK发生在事务提交之前,这样即便Slave没有收到二进制日志,但是 Master宕机了,由于最后一个事务还没有提交,所以本身这个数据对外也不可见,不存在丢失的问题。

    image-20220209184208146

    所以,对于任何有数据一致性要求的业务,如电商的核心订单业务、银行、保险、证券等与资金密切相关的业务,务必使用增强半同步复制。这样数据才是安全的、有保障的、即使发生宕机,从机也有一份完整的数据。

    增强半同步复制环境是基于异步复制的基础上进行的。

    安装插件,建议master和slave都安装,因为会有主从切换的情景:

    # master
    mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
    # slave
    mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';

    确保插件安装成功:

    mysql> show plugins;
    ... ...
    | rpl_semi_sync_master       | ACTIVE   | REPLICATION        | semisync_master.so | GPL     |
    | rpl_semi_sync_slave        | ACTIVE   | REPLICATION        | semisync_slave.so  | GPL     |
    +----------------------------+----------+--------------------+--------------------+---------+
    46 rows in set (0.00 sec)

    先启动slave上的半同步:

    mysql> set global rpl_semi_sync_slave_enabled = {0|1};  # 1:启用,0:禁止

    再启动master上的半同步:

    mysql> set global rpl_semi_sync_master_enabled = {0|1};  # 1:启用,0:禁止
    # mysql> set global rpl_semi_sync_master_timeout = 10000;    # 单位为ms,默认为10s

    从库重启io_thread:

    mysql> stop slave io_thread;
    mysql> start slave io_thread;

    半同步的参数为什么不建议写入配置文件

    参数写入配置文件的话,会使实例启动后立即进入半同步模式,如果发生长时间断连的实例重新运行启动,有可能导致主库被拖垮。

    长时间断开的从库,重新连接后,要等待追完全部事务后,手动开启半同步模式,而不是启动后直接切换,防止冲击主库。

    查询主库状态信息

    mysql> 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     |
    | Rpl_semi_sync_slave_status                 | ON    |
    +--------------------------------------------+-------+
    15 rows in set (0.00 sec)

    重要参数说明:

    查询主库参数信息

    mysql> show global variables like '%sync%';
    +-------------------------------------------+------------+
    | Variable_name                             | Value      |
    +-------------------------------------------+------------+
    | binlog_group_commit_sync_delay            | 0          |
    | binlog_group_commit_sync_no_delay_count   | 0          |
    | innodb_flush_sync                         | ON         |
    | innodb_sync_array_size                    | 1          |
    | innodb_sync_spin_loops                    | 30         |
    | rpl_semi_sync_master_enabled              | ON         |
    | rpl_semi_sync_master_timeout              | 10000      |
    | rpl_semi_sync_master_trace_level          | 32         |
    | rpl_semi_sync_master_wait_for_slave_count | 1          |
    | rpl_semi_sync_master_wait_no_slave        | ON         |
    | rpl_semi_sync_master_wait_point           | AFTER_SYNC |
    | rpl_semi_sync_slave_enabled               | ON         |
    | rpl_semi_sync_slave_trace_level           | 32         |
    | sync_binlog                               | 1          |
    | sync_frm                                  | ON         |
    | sync_master_info                          | 10000      |
    | sync_relay_log                            | 10000      |
    | sync_relay_log_info                       | 10000      |
    +-------------------------------------------+------------+
    18 rows in set (0.01 sec)

    重要参数说明:

    可以在slave端执行stop slave,测试master端会发生什么情况?

    在master上执行下面的sql:

    mysql> insert into t_order values(3,"C");Query OK, 1 row affected (10.05 sec)

    会发现这条语句会阻塞10s(对应上面的参数rpl_semi_sync_master_timeout),然后执行成功,最后看看看master的日志描述:

    2022-01-25T02:31:57.016430Z 4 [Note] Start binlog_dump to master_thread_id(4) slave_server(1403312), pos(mysql-bin.000005, 154)
    2022-01-25T02:31:57.016515Z 4 [Note] Start asynchronous binlog_dump to slave (server_id: 1403312), pos(mysql-bin.000005, 154)
    2022-01-25T02:33:32.183819Z 2 [Note] Semi-sync replication initialized for transactions.
    2022-01-25T02:33:32.183865Z 2 [Note] Semi-sync replication enabled on the master.
    2022-01-25T02:33:32.184004Z 0 [Note] Starting ack receiver thread
    2022-01-25T02:33:59.644444Z 5 [Note] While initializing dump thread for slave with UUID <aba2eb12-7cbc-11ec-9c1d-0242ac110003>, found a zombie dump thread with the same UUID. Master is killing the zombie dump thread(4).
    2022-01-25T02:33:59.644612Z 5 [Note] Start binlog_dump to master_thread_id(5) slave_server(1403312), pos(mysql-bin.000005, 154)
    2022-01-25T02:33:59.644632Z 4 [Note] Stop asynchronous binlog_dump to slave (server_id: 1403312)
    2022-01-25T02:33:59.644727Z 5 [Note] Start semi-sync binlog_dump to slave (server_id: 1403312), pos(mysql-bin.000005, 154)
    2022-01-25T02:38:02.847879Z 0 [ERROR] mysqld: Got an error reading communication packets
    2022-01-25T02:38:27.228952Z 2 [Warning] Timeout waiting for reply of binlog (file: mysql-bin.000005, pos: 684), semi-sync up to file mysql-bin.000005, position 419.
    2022-01-25T02:38:27.229063Z 2 [Note] Semi-sync replication switched OFF.
    2022-01-25T02:39:47.230189Z 5 [Note] Stop semi-sync binlog_dump to slave (server_id: 1403312)

    可以发现半同步关闭了,变成异步模式。

    推荐学习:mysql视频教程

    以上就是MySQL复制技术之异步复制和半同步复制的详细内容,更多请关注php中文网其它相关文章!

    声明:本文转载于:CSDN,如有侵犯,请联系admin@php.cn删除
    专题推荐:mysql
    上一篇:带你把MySQL索引吃透了 下一篇:mysql需要commit吗
    千万级数据并发解决方案

    相关文章推荐

    • mysql如何修改一列的值• mysql怎么修改索引类型• MySQL复制实例详解之GTID复制• mysql怎么查询第一列数据• linux怎么用yum删除mysql
    1/1

    PHP中文网