mysql 5.7 热备_mysql5.7双机主主热备

热备这个东西必然消耗资源，对mysql应用的影响暂时还没有研究，对mysql操作频繁的场景下必然会有影响。

数据需要备份的请参考我的博文

mysql数据备份

先下载mysql的rpm安装包。本文档默认是离线环境。

若无特殊说明，操作默认双机都要进行。

首先下载rpm安装包

mysql-5.7.24-1.el7.x86_64.rpm-bundle.tar

解压后安装

rpm -ivh mysql-community-common-5.7.24-1.el7.x86_64.rpm

rpm -ivh mysql-community-libs-5.7.24-1.el7.x86_64.rpm

rpm -ivh mysql-community-client-5.7.24-1.el7.x86_64.rpm

rpm -ivh mysql-community-server-5.7.24-1.el7.x86_64.rpm

rpm -ivh mysql-community-devel-5.7.24-1.el7.x86_64.rpm

启动进程

service mysqld start

查看临时密码

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

修改密码

/usr/bin/mysql_secure_installation

若想修改密码策略，请参考我的另一篇博文

Mysql修改密码策略

#关防火墙

systemctl stop firewalld

systemctl disable firewalld

我们以10.0.0.1和10.0.0.2来作为两台主机的ip地址

1.修改两台机器的配置文件

10.0.0.1的配置文件为(数据目录一致的话，改下host就能用)

[mysqld]

datadir=/var/lib/mysql

user=mysql

port = 3306

socket= /var/lib/mysql/mysql.sock

log-bin=mysql-bin

gtid-mode=on

enforce-gtid-consistency=true

master-info-repository=TABLE

relay-log-info-repository=TABLE

sync-master-info=1

slave-parallel-workers=2

binlog-checksum=CRC32

master-verify-checksum=1

slave-sql-verify-checksum=1

binlog-rows-query-log_events=1

report-port=3306

report-host=10.0.0.1

#replicate-same-server-id #主要用于同时写的情况

server_id = 1 #从写 server_id = 2

log-slave-updates

slave-skip-errors=all

auto_increment_increment=2

auto_increment_offset=1 #从写 auto_increment_offset = 2

10.0.0.2的配置文件

datadir=/var/lib/mysql

user=mysql

port = 3306

socket= /var/lib/mysql/mysql.sock

log-bin=mysql-bin

gtid-mode=on

enforce-gtid-consistency=true

master-info-repository=TABLE

relay-log-info-repository=TABLE

sync-master-info=1

slave-parallel-workers=2

binlog-checksum=CRC32

master-verify-checksum=1

slave-sql-verify-checksum=1

binlog-rows-query-log_events=1

report-port=3306

report-host=10.0.0.2

#replicate-same-server-id #主要用于同时写的情况

server_id =2

log-slave-updates

slave-skip-errors=all

auto_increment_increment=2

auto_increment_offset=2

2.配置主机

重启mysql服务

service mysqld restart

10.0.0.1上执行命令

grant replication slave on *.* to 'slave'@'10.0.0.2' identified by 'root';

show master status\G;

10.0.0.2上执行命令

grant replication slave on *.* to 'slave'@'10.0.0.1' identified by 'root';

show master status\G;

分别记录两台机器的File和position值。

这里假设10.0.0.1的显示为如下

File: mysql-bin.000001

Position: 891

Binlog_Do_DB:

Binlog_Ignore_DB:

这里假设10.0.0.2的显示为如下

File: mysql-bin.000001

Position: 892

Binlog_Do_DB:

Binlog_Ignore_DB:

10.0.0.1上执行命令

change master to master_host='10.0.0.2',master_user='slave',

master_password='root',master_log_file='mysql-bin.000001',

master_log_pos=892;

start slave;

show slave status\G;

10.0.0.2上执行命令

change master to master_host='10.0.0.1',master_user='slave',

master_password='root',master_log_file='mysql-bin.000001',

master_log_pos=891;

start slave;

show slave status\G;

查看两边的Slave_IO_Running和Slave_SQL_Running的状态值，都为yes则配置完成。

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

再试一下，两边已经是同步的了。

可以通过修改配置文件指定特定的数据库。