环境
系统: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