双主（主主）架构方案思路是
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)