mariadb 多源复制的实现

什么是多源复制
 


在我们以前的数据库主从复制和高可用的配置中,如果一旦高可用服务器切换,从机不能复制新启动的backup主机,原因是因为 slave机器没有同步master2机器,而只是同步了master1机器。
master1 从master2 同步了数据,只是写入master自己的中继日志,而没有写入自己的binlog日志,这样slave机器就不能读取master1的binlog日志。

解决这个问题的方法,就是在版本高于10的mariadb或者mysql5.7当中,做多远复制,就是slave机器同时同步二台master机器

环境

机器IP 角色
192.168.137.4 master1
192.168.137.5 master2
192.168.137.6 slave1
服务器 centos7.9 mariadb 10.3.14  

配置步骤

  • 配置数据源,
cat <<EOF >/etc/yum.repos.d/mariadb.repo
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.3/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
EOF
  • 更新缓存,注意,10.0以后,安装的包名变成了大写的MariaDB
# yum clean all

# yum list |grep MariaDB-server

MariaDB-rocksdb-engine.x86_64              10.3.28-1.el7.centos        mariadb 
MariaDB-rocksdb-engine-debuginfo.x86_64    10.3.28-1.el7.centos        mariadb 
MariaDB-server.x86_64                      10.3.28-1.el7.centos        mariadb 
MariaDB-server-debuginfo.x86_64            10.3.28-1.el7.centos        mariadb 
MariaDB-shared.x86_64                      10.3.28-1.el7.centos        mariad
  • 安装
  • [root@localhost ~]# yum list --showduplicates MariaDB-server
    已加载插件:fastestmirror
    Loading mirror speeds from cached hostfile
     * base: mirrors.aliyun.com
     * extras: mirrors.163.com
     * updates: mirrors.aliyun.com
    已安装的软件包
    MariaDB-server.x86_64                                                         10.3.28-1.el7.centos                                                          @mariadb
    可安装的软件包
    MariaDB-server.x86_64                                                         10.3.26-1.el7.centos                                                          mariadb
    MariaDB-server.x86_64                                                         10.3.27-1.el7.centos                                                          mariadb
    MariaDB-server.x86_64                                                         10.3.28-1.el7.centos                                                          mariadb
    mariadb-server.x86_64                                                         1:5.5.68-1.el7                                                          
    
    [root@localhost ~]# yum install MariaDB-server
    已加载插件:fastestmirror
    Loading mirror speeds from cached hostfile
     * base: mirrors.aliyun.com
     * extras: mirrors.163.com
     * updates: mirrors.aliyun.com
    正在解决依赖关系
    --> 正在检查事务
    ---> 软件包 MariaDB-server.x86_64.0.10.3.28-1.el7.centos 将被 安装
    --> 正在处理依赖关系 perl(Data::Dumper),它被软件包 MariaDB-server-10.3.28-1.el                                                                                     7.centos.x86_64 需要
    ====
     perl-Time-HiRes.x86_64 4:1.9725-3.el7                 perl-Time-Local.noarch 0:1.2300-2.el7                 perl-constant.noarch 0:1.27-2.el7
      perl-libs.x86_64 4:5.16.3-299.el7_9                   perl-macros.x86_64 4:5.16.3-299.el7_9                 perl-parent.noarch 1:0.225-244.el7
      perl-podlators.noarch 0:2.5.1-3.el7                   perl-threads.x86_64 0:1.87-4.el7                      perl-threads-shared.x86_64 0:1.43-6.el7
      rsync.x86_64 0:3.1.2-10.el7
    
    替代:
      mariadb-libs.x86_64 1:5.5.68-1.el7
    
    
    
    

     

  • 启动服务
  • [root@localhost ~]# systemctl restart mariadb
    [root@localhost ~]# netstat -antp
    Active Internet connections (servers and established)
    Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
    tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      1061/sshd
    tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN      1327/master
    tcp        0      0 192.168.137.5:22        192.168.137.2:62865     ESTABLISHED 1573/sshd: root@not
    tcp        0      0 192.168.137.5:22        192.168.137.2:62863     ESTABLISHED 1569/sshd: root@pts
    tcp6       0      0 :::3306                 :::*                    LISTEN      2087/mysqld
    tcp6       0      0 :::22                   :::*                    LISTEN      1061/sshd
    tcp6       0      0 ::1:25                  :::*                    LISTEN      1327/master
    [root@localhost ~]# mysql
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MariaDB connection id is 8
    Server version: 10.3.28-MariaDB MariaDB Server
    
    Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    MariaDB [(none)]> exit
    Bye
    [root@localhost ~]#
    

    配置主主复制

  • 二台主主机器上增加一个用户
[root@localhost my.cnf.d]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 16
Server version: 10.3.28-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> grant replication slave on *.* to mm_slave@'%' identified by '123456';
MariaDB [(none)]> flush privileges;

 

  修改主主二台机器的配置,关闭防火墙,重启。

[mariadb-10.3]
server_id=1 #不能重复,137.4使用1 ,137.5使用 2
log-bin
auto_increment_offset=1  137.4使用1 ,137.5使用 2
auto_increment_increment=2

加入复制

-- 注意,二个机器都要增加,确定master_host是对方的机器
MariaDB [(none)]> change master to master_host='192.168.137.5' , master_password='123456';
Query OK, 0 rows affected (0.024 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.002 sec)

测试通过

MariaDB [(none)]> show slave status \G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.137.5
                   Master_User: mm_slave
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: localhost-bin.000001
           Read_Master_Log_Pos: 653
                Relay_Log_File: localhost-relay-bin.000002
                 Relay_Log_Pos: 956
         Relay_Master_Log_File: localhost-bin.000001
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes

从服务器加入二个主

增加server_id,重启启动

-- /etc/my.cnf
[mariadb]
server_id=3

加入主,记得所有的mater别名必须加单引号。否则会报错。

ERROR 1200 (HY000): Misconfigured slave: MASTER_HOST was not set; Fix in config file or with CHANGE MASTER TO

正确加入如下 

MariaDB [(none)]>  change master 'master1374'  to master_host='192.168.137.4', master_password='123456',master_user='mm_slave';
MariaDB [(none)]>  start slave   'master1374'

MariaDB [(none)]>  change master 'master1375'  to master_host='192.168.137.4', master_password='123456',master_user='mm_slave';
MariaDB [(none)]>  start slave   'master1375'
MariaDB [(none)]> show slave 'master1374'  status\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.137.4
                   Master_User: mm_slave
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: localhost-bin.000001
           Read_Master_Log_Pos: 653
                Relay_Log_File: localhost-relay-bin-master1374.000002
                 Relay_Log_Pos: 956
         Relay_Master_Log_File: localhost-bin.000001
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
MariaDB [(none)]> show slave 'master1375'  status\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.137.5
                   Master_User: mm_slave
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: localhost-bin.000001
           Read_Master_Log_Pos: 653
                Relay_Log_File: localhost-relay-bin-master1374.000002
                 Relay_Log_Pos: 956
         Relay_Master_Log_File: localhost-bin.000001
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
         、

 

 

上一篇:Linux相关操作-安装MySQL


下一篇:997. 找到小镇的法官(图,哈希表)