欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

mysql5.5主从同步配置(semi_sync)

程序员文章站 2022-05-02 13:06:54
...

好久不折腾mysql主从了,发现5.5交之前的版本,配置上有些变化,趁着夜深人静,重温一下,顺便简单记录一下配置过程。 主从同步 master端配置 修改 my.cnf 文件 12345678910 vim /etc/my.cnf[mysqld]server-id = 1log-bin = mysql-binbinlog_format = mixede

好久不折腾mysql主从了,发现5.5交之前的版本,配置上有些变化,趁着夜深人静,重温一下,顺便简单记录一下配置过程。

主从同步

master端配置

  1. 修改my.cnf文件
1
2
3
4
5
6
7
8
9
10
vim /etc/my.cnf
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog_format = mixed
expire-logs-days = 7
binlog-do-db = luxtarget
binlog-ignore-db = mysql
replicate-do-db = luxtarget
replicate-ignore-db = mysql
  1. 新建授权复制用户

    1
    2
    
    grant replication slave on *.* to 'slave'@'xxx.xxx.xxx.xxx' identified by 'slave';
    flush privileges;
    
  2. 重启mysql

    1
    
    service mysqld restart
    
  3. 锁定数据库,将数据打包并拷贝至slave

    1
    
    flush tables with read lock;
    
  4. 查看master上二进制日志情况,记录FilePosition

    1
    
    show master status;
    
  5. 当数据打包拷贝完毕,解除锁定;

    1
    
    ublock tables;
    

    当数据较大时,将数据打包备份比使用mysqldump导入时效率更高

slave端配置

  1. 修改my.cnf文件

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    
    vim /etc/my.cnf
    [mysqld]
    server-id = 2
    log-bin = mysql-bin
    binlog_format = mixed
    expire-logs-days = 7
    binlog-do-db = luxtarget
    binlog-ignore-db = mysql
    replicate-do-db = luxtarget
    replicate-ignore-db = mysql
    
  2. 重启mysql

    1
    
    service mysqld restart
    
  3. 将从master拷贝来的数据导入slave

  4. 连接master开始同步

    1
    2
    3
    4
    5
    
    change master to master_host='xxx.xxx.xxx.xxx',master_user='slave',master_password='slave',master_log_file='master-bin.xxxxxx,master_log_pos=xxx;
    start slave;
    show slave status\G;
    

    当Slave_IO_Running与Slave_SQL_Running的状态都为Running时即可。

半同步配置

master端配置

  1. 安装mysql半同步插件

    1
    2
    3
    
    INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
    SET GLOBAL rpl_semi_sync_master_enabled = 1;
    SET GLOBAL rpl_semi_sync_master_timeout = 1000;
    
  2. 修改my.cnf文件

    1
    2
    3
    4
    
    vim /etc/my.cnf
    [mysqld]
    rpl_semi_sync_master_enabled=1 #启用半同步
    rpl_semi_sync_master_timeout=1000 #超时时间为1s
    
  3. 重启mysql

    1
    
    service mysqld restart
    

slave端配置

  1. 安装mysql半同步插件

    1
    2
    3
    4
    
    INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
    SET GLOBAL rpl_semi_sync_slave_enabled = 1;
    STOP SLAVE IO_THREAD;
    START SLAVE IO_THREAD;
    
  2. 修改my.cnf文件

    1
    
    rpl_semi_sync_slave_enabled=1  #启用半同步复制
    
  3. 重启mysql

    1
    
    service mysqld restart
    

检查配置

  1. master

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    
    SHOW GLOBAL STATUS LIKE 'rpl_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)
    
  2. slave

    1
    2
    3
    4
    5
    6
    7
    
    mysql> SHOW GLOBAL STATUS LIKE 'rpl_semi%';
    +----------------------------+-------+
    | Variable_name              | Value |
    +----------------------------+-------+
    | Rpl_semi_sync_slave_status | ON    |
    +----------------------------+-------+
    1 row in set (0.01 sec)
    

好久不折腾mysql主从了,发现5.5交之前的版本,配置上有些变化,趁着夜深人静,重温一下,顺便简单记录一下配置过程。

主从同步

master端配置

  1. 修改my.cnf文件
1
2
3
4
5
6
7
8
9
10
vim /etc/my.cnf
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog_format = mixed
expire-logs-days = 7
binlog-do-db = luxtarget
binlog-ignore-db = mysql
replicate-do-db = luxtarget
replicate-ignore-db = mysql