mariadb主主架构

双主（主主）架构方案思路是

 

1.两台mysql都可读写，互为主备，默认只使用一台（masterA）负责数据的写入，另一台（masterB）备用；

2.masterA是masterB的主库，masterB又是masterA的主库，它们互为主从；

3.两台主库之间做高可用,可以采用keepalived等方案（使用VIP对外提供服务）；

4.所有提供服务的从服务器与masterB进行主从同步（双主多从）;

5.建议采用高可用策略的时候，masterA或masterB均不因宕机恢复后而抢占VIP（非抢占模式）；

 

 

环境前部署

192.168.126.16 A

192.168.126.17 B

#!/bin/bash
mkdir /etc/yum.repos.d/centos
mv /etc/yum.repos.d/* /etc/yum.repos.d/centos

echo "[mariadb]
name = MariaDB
baseurl = http://mirrors.ustc.edu.cn/mariadb/yum/10.3/centos7-amd64/
gpgkey = http://mirrors.ustc.edu.cn/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck=1" > /etc/yum.repos.d/mysql.repo

mount /dev/cdrom /mnt
echo "[bendi]
name=bendi
gpgcheck=0
enabled=1
baseurl=file:///mnt" > /etc/yum.repos.d/bendi.repo

echo "[epel1]
name = epel
enabled = 1
gpgcheck = 0
baseurl = https://mirrors.aliyun.com/epel/7/x86_64/
cost = 1
[centos]
name = centos base
enabled = 1
gpgcheck = 0
baseurl = http://mirrors.163.com/centos/7/os/x86_64/
" > /etc/yum.repos.d/wanglou.repo

yum clean all 
yum repolist

第一步：下载数据库

[root@zxw16 ~]# yum install mariadb  mariadb-server    -y

 第二步：修改配置文件16

[client-server]
[mysqld]
log-bin=master-bin　　　　　　　　　　　　　　　　　　　　　　　　　　　　#二进制日志
relay_log=relay-mysql　　　　　　　　　　　　　　　　　　　　　　　　　　#中继日志
server-id = 1　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　#server-id 唯一
auto-increment-offset = 1　　　　　　　　　　　　　　　　　　　　　　　 #设置起始值从1开始
auto-increment-increment = 2　　　　　　　　　　　　　　　　　　　　　　#步长为2　　　
binlog-format=mixed　　　　　　　　　　　　　　　　　　　　　　　　　　　#二进制日志模式
datadir=/var/lib/mysql　　　　　　　　　　　　　　　　　　　　　　　　　#数据目录
socket=/var/lib/mysql/mysql.sock
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
!includedir /etc/my.cnf.d

 第二步：修改配置文件17

[client-server]
[mysqld]
log-bin=master-bin
relay_log=relay-mysql
server-id = 2
auto-increment-offset = 2
auto-increment-increment = 2
binlog-format=mixed
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

!includedir /etc/my.cnf.d

初始化数据库A  B

mysql_secure_installation 

登录数据库A B

mysql -uroot -p123

MariaDB [(none)]>  grant replication slave on *.* to slave@'%' identified by 'slave';
Query OK, 0 rows affected (0.000 sec)

A是主B是从连接

flush logs;

 

 

MariaDB [(none)]> show master status;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000003 |      945 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.000 sec)



MariaDB [(none)]> change master to  master_host='192.168.126.16',
    -> master_user='slave',
    -> master_password='slave', 
    -> master_port=3306, 
    -> master_log_file='master-bin.000003',
    -> master_log_pos=945;
Query OK, 0 rows affected (0.010 sec)

B是主A是从连接

 

 

MariaDB [(none)]> show master status;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000013 |     1618 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.000 sec)
MariaDB [(none)]> show master status;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000003 |      945 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.000 sec)

MariaDB [(none)]>  change master to  master_host='192.168.126.17',
    -> master_user='slave',
    -> master_password='slave', 
    -> master_port=3306, 
    -> master_log_file='master-bin.000013',
    -> master_log_pos=1618;
Query OK, 0 rows affected (0.013 sec)

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

MariaDB [(none)]> show slave statusG
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.126.17
                   Master_User: slave
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: master-bin.000003
           Read_Master_Log_Pos: 945
                Relay_Log_File: relay-mysql.000002
                 Relay_Log_Pos: 556
         Relay_Master_Log_File: master-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: 945
               Relay_Log_Space: 861
               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: 0
 Master_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: 2
                Master_SSL_Crl: 
            Master_SSL_Crlpath: 
                    Using_Gtid: No
                   Gtid_IO_Pos: 
       Replicate_Do_Domain_Ids: 
   Replicate_Ignore_Domain_Ids: 
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
              Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0
1 row in set (0.000 sec)