Mysql 主主复制

环境

系统：Centos 7.4 x64

服务版本：Mariadb 5.5

---

 结构

主1:192.168.1.108

主2:192.168.1.109

---

主1、主2 安装Mariadb服务（两端操作相同）

1.下载yum源

wget -P /etc/yum.repos.d http://mirrors.aliyun.com/repo/Centos-7.repo

2.安装Mariadb服务

yum -y install mariadb-server-5.5.56-2.el7.x86_64
yum -y install mariadb-5.5.56-2.el7.x86_64
yum -y install mariadb-libs-5.5.56-2.el7.x86_64

3.启动mysql 加入开机自启动

# 1.启动mysql
systemctl start mariadb

# 2.加入开机自启动
systemctl enable mariadb

4.初始化数据库

# 2.初始化mysql数据库。
mysql_secure_installation

5.创建用户

# 1.创建用户名可被任意主机连接
mysql -uroot -p123456 -e "grant all on *.* to "root"@"%" identified by "123456";"

# 2.添加复制连接用户
GRANT REPLICATION SLAVE ON *.* TO 'RepUser'@'%'identified by 'beijing';

# 3.刷新
flush privileges;

---

配置主主复制（两端操作相同）

1.修改主配置文件

[mysqld]

# 默认 端口自定义 
port=3307
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
security risks
symbolic-links=0

# 添加 开启二进制文件
log-bin=mysql-bin

# 添加 主1 = 1  主2 = 2
server-id = 1

# 添加 避免主键冲突
auto-increment-offset= 1
auto-increment-increment= 2

[mysqld]

# 默认 端口自定义 
port=3307
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
security risks
symbolic-links=0

# 添加 开启二进制文件
log-bin=mysql-bin

# 添加 主1 = 1  主2 = 2
server-id = 2

# 添加 避免主键冲突
auto-increment-offset= 2
auto-increment-increment= 2

2.重启mysql

systemctl restart mariadb.service

3.查看二进制文件

# 进入主1数据库
mysql -uroot -p123456

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |      245 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)


# 进入主2数据库
mysql -uroot -p123456

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 |      335 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

4.通过关联用户 关联两端position号 与 二进制文件 实现主主复制

# 主1
MariaDB [(none)]> CHANGE MASTER TO MASTER_USER='RepUser',MASTER_HOST='192.168.1.109',MASTER_PASSWORD='beijing',MASTER_PORT=3307,MASTER_LOG_FILE='mysql-bin.000005',MASTER_LOG_POS=335;

# 主2
MariaDB [(none)]> CHANGE MASTER TO MASTER_USER='RepUser',MASTER_HOST='192.168.1.108',MASTER_PASSWORD='beijing',MASTER_PORT=3307,MASTER_LOG_FILE='mysql-bin.000004',MASTER_LOG_POS=245;

5.两端开启复制功能

MariaDB [(none)]> start slave;

6.查看主主复制状态，双yes 为成功！！！

MariaDB [(none)]> show slave statusG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.109
                  Master_User: RepUser
                  Master_Port: 3307
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 335
               Relay_Log_File: mariadb-relay-bin.000015
                Relay_Log_Pos: 619
        Relay_Master_Log_File: mysql-bin.000005
             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: 335
              Relay_Log_Space: 1199
              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
1 row in set (0.00 sec)

MariaDB [(none)]>  show slave statusG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.108
                  Master_User: RepUser
                  Master_Port: 3307
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 245
               Relay_Log_File: mariadb-relay-bin.000015
                Relay_Log_Pos: 529
        Relay_Master_Log_File: mysql-bin.000004
             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: 245
              Relay_Log_Space: 1109
              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: 1
1 row in set (0.00 sec)