mysql 复制 binlog_MySQL：2.基于日志（binlog）主主复制（Master

环境[[email protected] ~]# cat /etc/centos-release

CentOS Linux release 7.6.1810 (Core)

[[email protected] ~]# mysql -V

mysql Ver 14.14 Distrib 5.7.22, for Linux (x86_64) using EditLine wrapper

第一步：关闭系统默认防火墙(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

第一步：安装并设置开机自动启动(by all)

1.安装cat </etc/yum.repos.d/mysql-community.repo

[mysql57-community]

name=MySQL 5.7 Community Server

baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/

enabled=1

gpgcheck=1

gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

EOF

yum repolist all | grep mysql

2.启动和初始化systemctl start mysqld

systemctl status mysqld

systemctl enable mysqld

grep 'temporary password' /var/log/mysqld.log

mysql_secure_installation

第三步：修改配置文件(by all)注意根据节点1和节点2分别修改auto_increment_offset

1.两个节点分别编辑配置文件并在[mysqld]添加以下内容cp /etc/my.cnf /etc/my.cnf.bak

vi /etc/my.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-db=mysql

#忽略information_schema库

binlog-ignore-db=information_schema

#要同步的数据库，默认所有库

replicate-do-db=test

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

3.两个节点分别创建MySQL账户并授权对方服务器可以访问节点1：

mysql -uroot -pXiaoliu123!

grant replication slave, replication client on *.* to 'admin'@'11.11.11.62' identified by 'Xiaoliu123!';

flush privileges;

节点2：

mysql -uroot -pXiaoliu123!

grant replication slave, replication client on *.* to 'admin'@'11.11.11.61' identified by 'Xiaoliu123!';

flush privileges;

4.两个节点分别创建test库mysql -uroot -pXiaoliu123!

create database test;

commit;

5.两台服务器分别查看日志文件和Position并记录mysql -uroot -pXiaoliu123!

show master status;

6.两台服务器分别设置节点1：(填入对方服务器的日志文件和Position)

change master to master_host='11.11.11.62',

master_user='admin',

master_password='Xiaoliu123!',

master_port=3306,

master_log_file='mysql-bin.000002',

master_log_pos=794,

master_connect_retry=30;

节点2：(填入对方服务器的日志文件和Position)

change master to master_host='11.11.11.61',

master_user='admin',

master_password='Xiaoliu123!',

master_port=3306,

master_log_file='mysql-bin.000003',

master_log_pos=951,

master_connect_retry=30;

7.查看同步状态# 1.查看同步状态

show slave statusG;

# 可看到Slave_IO_State为空

# Slave_IO_Runngin和Slave_SQL_Running是No

# 表示Slave还是没有开始复制过程。

# 2.开启主从同步

start slave;

# 3.再次查看状态

show slave statusG;

# 主要查看以下3个状态

Slave_IO_State: Waiting for master to send event

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

# 4.可以分别在主从节点查看I/O线程创建的连接

show processlistG;

第四步：测试

节点1执行以下操作，登录节点2查看是否有同步过来：use test;

create table linux(username varchar(15) not null,password varchar(15) not null);

insert into linux values ('XiaoMing', 'xiaoliu');

commit;

节点2执行以下操作，登录节点1查看是否有同步过来use test;

insert into linux values ('aaabbb', '123456');

commit;