mysql 主主复制_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=3307datadir=/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= 1auto-increment-increment= 2

主1

[mysqld]#默认 端口自定义

port=3307datadir=/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= 2auto-increment-increment= 2

主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.00sec)#进入主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 status\G*************************** 1. row ***************************Slave_IO_State: Waitingformaster to send event

Master_Host:192.168.1.109Master_User: RepUser

Master_Port:3307Connect_Retry:60Master_Log_File: mysql-bin.000005Read_Master_Log_Pos:335Relay_Log_File: mariadb-relay-bin.000015Relay_Log_Pos:619Relay_Master_Log_File: mysql-bin.000005Slave_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:335Relay_Log_Space:1199Until_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)

主1

MariaDB [(none)]>show slave status\G*************************** 1. row ***************************Slave_IO_State: Waitingformaster to send event

Master_Host:192.168.1.108Master_User: RepUser

Master_Port:3307Connect_Retry:60Master_Log_File: mysql-bin.000004Read_Master_Log_Pos:245Relay_Log_File: mariadb-relay-bin.000015Relay_Log_Pos:529Relay_Master_Log_File: mysql-bin.000004Slave_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:245Relay_Log_Space:1109Until_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)

主2