MariaDB系列之三(基于日志(binlog)主主复制(Master-Master))

title: MariaDB系列之三:基于日志(binlog)主主复制(Master-Master)
categories: Linux
tags:
- MariaDB
- MySQL
timezone: Asia/Shanghai
date: 2019-02-01 环境

[root@centos181001 ~]# cat /etc/centos-release CentOS Linux release 7.6.1810 (Core)MariaDB [(none)]> status -------------- mysqlVer 15.1 Distrib 5.5.60-MariaDB, for Linux (x86_64) using readline 5.1节点1:11.11.11.61 节点2:11.11.11.62

第一步:关闭系统默认防火墙(by all)
setenforce 0 sed -i -r "/^SELINUX=/c SELINUX=disabled" /etc/selinux/config which systemctl && systemctl stop firewalld which systemctl && systemctl disable firewalld which systemctl && systemctl stop iptables || service iptables stop which systemctl && systemctl disable iptables || chkconfig iptables off

第二步:安装MariaDB并设置开机自动启动(by all)
# 1.安装 yum install mariadb mariadb-server mariadb-libs mariadb-devel -y# 2.启动MariaDB并设置开机自动启动 systemctl start mariadb systemctl status mariadb systemctl enable mariadb# 3.初始化数据库 mysql_secure_installation Enter current password for root (enter for none):# 输入密码,默认为空 Set root password? [Y/n] y# 是否设置root密码 Remove anonymous users? [Y/n] y# 是否移除anonymous用户 Disallow root login remotely? [Y/n] n# 是否禁止远程登录 Remove test database and access to it? [Y/n] y# 是否移除默认的演示数据库 Reload privilege tables now? [Y/n] y# 是否重新加载权限表?

第三步:修改配置文件(by all)
注意根据节点1和节点2分别修改auto_increment_offset
1.两个节点分别编辑配置文件并在[mysqld]添加以下内容
vi /etc/my.cnf.d/server.cnf #任意自然数n,只要保证两台MySQL主机不重复就可以了。 server-id=11#开启二进制日志 log-bin=mysql-bin# 步进值auto_imcrement。一般有n台主MySQL就填n auto_increment_increment=2#起始值。一般填第n台主MySQL。 auto_increment_offset=1 # auto_increment_offset=2#忽略mysql库 binlog-ignore=mysql#忽略information_schema库 binlog-ignore=information_schema#要同步的数据库,默认所有库 replicate-do-db=test

2.两个节点分别重启MariaDB服务
systemctl restart mariadb

3.两个节点分别创建MySQL账户并授权对方服务器可以访问
节点1: mysql -uroot -pxiaoliu grant replication slave, replication client on *.* to 'admin'@'11.11.11.62' identified by 'xiaoliu'; flush privileges; 节点2: mysql -uroot -pxiaoliu grant replication slave, replication client on *.* to 'admin'@'11.11.11.61' identified by 'xiaoliu'; flush privileges;

4.两个节点分别创建test库
mysql -uroot -pxiaoliu create database test; commit;

5.两台服务器分别查看日志文件和Position并记录
mysql -uroot -pxiaoliu show master status;

6.两台服务器分别设置
节点1:(填入对方服务器的日志文件和Position) change master to master_host='11.11.11.62', master_user='admin', master_password='xiaoliu', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos=592, master_connect_retry=30; 节点2:(填入对方服务器的日志文件和Position) change master to master_host='11.11.11.61', master_user='admin', master_password='xiaoliu', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos=592, master_connect_retry=30;

7.查看同步状态
# 1.查看同步状态 show slave status\G; # 可看到Slave_IO_State为空 # Slave_IO_Runngin和Slave_SQL_Running是No # 表示Slave还是没有开始复制过程。# 2.开启主从同步 start slave; # 3.再次查看状态 show slave status\G; # 主要查看以下3个状态 Slave_IO_State: Waiting for master to send event Slave_IO_Running: Yes Slave_SQL_Running: Yes# 4.可以分别在主从节点查看I/O线程创建的连接 show processlist\G;

第四步:测试 节点1执行以下操作,登录节点2查看是否有同步过来:
use test; create table linux(username varchar(15) not null,password varchar(15) not null); insert into linux values ('XiaoMing', 'xiaoliu'); commit;

【MariaDB系列之三(基于日志(binlog)主主复制(Master-Master))】节点2执行以下操作,登录节点1查看是否有同步过来
use test; insert into linux values ('aaabbb', '123456'); commit;

    推荐阅读