MySQL篇之二：MySQL搭建集群_mysql集群搭建

开篇介绍：

上一章介绍了如何安装MySQL数据库，本章将介绍MySQL的主从复制。

一、环境介绍

操作系统：CentOS 7
MySQL：5.7.40

二、环境准备

准备3台CentOS 7服务器，并且已安装MySQL5.7.40数据库：

三、主从复制

1、主从复制原理

2、binlog和relay日志

bin log：

• 记录所有数据的更改，可用于本机数据恢复和主从同步

relay log：

• Mysql 主节点将binlog写入本地，从节点请求增量binlog，主节点将binlog同步到从节点。
• 从节点单独进程会将binlog 拷贝至本地 relaylog（中继日志）中。
• 从节点定时重放relay log。

3、调整binlog日志模式

查看binlog日志模式

show variables like 'binlog_format';
1

调整binlog日志模式

set binlog_format=STATEMENT;
1

binlog的三种格式： STATEMENT 、 ROW 、 MIXED 。

4、查看binlog日志列表

show master logs;
or
show binary logs;
1
2
3

查看所有二进制日志列表

5、切换binlog文件

 flush logs;
1

会生成一个新的日志文件。

 reset master;
1

清空日志

6、查看binlog二进制文件

因为binlog和relaylog日志文件是二进制文件，需要mysql的自带的mysqlbinlog工具进行解码转为可阅读的sql语句。

mysqlbinlog --base64-output=decode-rows -v -v mysql-bin.000001 > binlog
1

7、基于binlog主从复制

7.1、master配置

1、查看binlog是否开启可以使用命令：

show variables like 'log_bin%';
1

log_bin如果是 OFF 代表是未开启状态。

2、开启binlog
修改my.cnf文件

vim /ect/my.cnf
1

[mysqld] 添加内容

#binlog刷盘策略
sync_binlog=1
#需要备份的数据库
binlog-do-db=hello
#不需要备份的数据库
binlog-ignore-db=mysql
#启动二进制文件
log-bin=mysql-bin
#服务器ID
server-id=132
1
2
3
4
5
6
7
8
9
10

sync_binlog参数：

• 0 ：存储引擎不进行binlog的刷新到磁盘，而由操作系统的文件系统控制缓存刷新。
• 1：每提交一次事务，存储引擎调用文件系统的sync操作进行一次缓存的刷新，这种方式最安全，但性能较低。
• n：当提交的日志组=n时，存储引擎调用文件系统的sync操作进行一次缓存的刷新。

注：sync_binlog=0或sync_binlog大于1，事务被提交，而尚未同步到磁盘。因此，在电源故障或操作系统崩溃时有可能服务器已承诺尚未同步一些事务到二进制日志。因此它是不可能执行例行程序恢复这些事务，他们将会丢失二进制日志。

3、重启MySQL服务

systemctl restart mysqld
1

4、主机给从机授备份权限

GRANT REPLICATION SLAVE ON *.* TO '从机MySQL用户名'@'从机IP' identified
by '从机MySQL密码';
1
2

注：一般不用root帐号，“%”表示所有客户端都可能连，只要帐号，密码正确，此处可用具体客户端IP代替，如192.168.145.226，加强安全。

5、刷新权限

 FLUSH PRIVILEGES;
1

6、查询master的状态

7.2、从服务器配置

1、修改my.conf文件

[mysqld]
server-id=133
1
2

2、删除uuid

uuid 是mysql唯一标识，克隆的系统uuid是一样的，所以需要修改。

find / -iname "auto.cnf"
rm -rf /usr/local/mysql/data/auto.cnf
1
2

3、重启

systemctl restart mysqld
1

4、配置从服务器

change master to
master_host='192.168.51.131',
master_port=3306,
master_user='root',
master_password='123456',
master_log_file='mysql-bin.000003',
master_log_pos=1189,
MASTER_AUTO_POSITION=0;
1
2
3
4
5
6
7
8

5、启动从服务器复制功能

start slave;
1

6、检查从服务器复制功能状态

show slave status \G;
1

注：Slave_IO及Slave_SQL进程必须正常运行，即YES状态，否则都是错误的状态(如：其中一个NO均属错误)。
7、测试
搭建成功之后，往主机中插入数据，看看从机中是否有数据；
slave:

master:

slave:

8、基于GTID的主从复制

GTID就是全局事务标识符（Global Transaction Identifiers），基于事务的复制。使用 GTID 时，可以识别和跟踪每个事务，因为它在原始服务器上提交并由任何从属服务器应用;这意味着在启动新从站或故障到新主服务器时，使用 GTID 引用日志文件或这些文件中的位置时，不需要引用日志文件或位置，这大大简化了这些任务。由于基于 GTID 的复制完全基于事务，因此只需确定主复制和从级复制是否一致;只要在主主机上提交的所有事务也都提交到从站上，就保证两者之间的一致性。

GTID = server_uuid:transaction_id

server_uuid 来源于 auto.cnf

1、配置GTID主从复制
1）修改master、slave服务器的my.cnf文件

#开启GTID模式（必选）
gtid_mode=ON
#强制gtid一致性（必选）
enforce-gtid-consistency=true
1
2
3
4

2）重启数据库

systemctl restart mysqld
1

3）从服务器中执行change master

change master to
master_host='192.168.51.131',
master_port=3306,
master_user='root',
master_password='123456',
master_auto_position = 1;
1
2
3
4
5
6

4）开启同步

START SLAVE;
1

测试同上。

四、高可用方案

1、配置双主集群

开始配置一个基于GTID的双主集群

1.1 master1配置

修改 /etc/my.cnf 文件

# 服务器id，一般是ip的最后一段
server-id=131
# 开启binlog
log-bin=mysql-bin
# 表示自增长字段每次递增的量，其默认值是1，取值范围是1 .. 65535
auto_increment_increment=2
# 表示自增长字段从那个数开始，他的取值范围是1 .. 65535，另外一台服务器的offset为2，防止
生成的主键冲突
auto_increment_offset=1
# 开启基于GTID的复制
gtid_mode = on
# 只记录对基于gtid的复制安全的语句
enforce-gtid-consistency=true
1
2
3
4
5
6
7
8
9
10
11
12
13

1.2 master2配置

# 服务器id，一般是ip的最后一段
server-id=132
# 开启binlog
log-bin=mysql-bin
auto_increment_increment=2
# 生成主键从2开始
auto_increment_offset=2
gtid_mode = on
enforce-gtid-consistency=true
1
2
3
4
5
6
7
8
9

1.3 建立主从关系

如果之前已经开启的主从复制，建议使用 stop slave 关闭。在每个节点上切换binlog，执行如下语句：

# 使用新的binlog
mysql> flush logs;
# 清空binlog
mysql> reset master;
1
2
3
4

然后使用 change master 语句建立主从关系：

#master1执行
change master to
master_host='192.168.51.132',
master_port=3306,
master_user='root',
master_password='123456',
master_auto_position = 1;

#master2执行
change master to
master_host='192.168.51.131',
master_port=3306,
master_user='root',
master_password='123456',
master_auto_position = 1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

master1和master2节点上开启同步

START SLAVE;
1

查看状态

show slave status \G
1

master1

master2

2、安装keepalived

Keepalived高可用服务对之间的故障切换转移，是通过 VRRP (Virtual Router Redundancy Protocol ,虚拟路由器冗余协议）来实现的。VRRP ,全 称 Virtual Router Redundancy Protocol ,中文名为虚拟路由冗余协议 ，VRRP的出现就是为了解决静态踣甶的单点故障问题，VRRP是通过一种竞选机制来将路由的任务交给某台VRRP路由器的。

2.1 安装keepalived

安装keepalived简单方式，使用yum方式在线安装：

yum install keepalived -y
1

2.2 配置keepalived

备份keepalived.conf

cp /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.bak
1

重新配置

# 配置通知的email
global_defs {
	notification_email {
		acassen@firewall.loc
		failover@firewall.loc
		sysadmin@firewall.loc
	}
	notification_email_from Alexandre.Cassen@firewall.loc
	smtp_server 192.168.51.1
	smtp_connect_timeout 30
	router_id LVS_DEVEL
	vrrp_skip_check_adv_addr
	# 注释掉vrrp_strict,开启会导致其他机器无法访问虚拟IP
	# vrrp_strict
	vrrp_garp_interval 0
	vrrp_gna_interval 0
}
# 检查mysql脚本，定时执行
vrrp_script check_run {
	script "/usr/check_run.sh"
	interval 3
}
# 设置虚拟ip
vrrp_instance VI_1 {
	# 当前节点的状态MASTER、BACKUP
	state MASTER
	# 当前服务器使用的网卡名称，使用ifconfig查看
	interface ens33
	#VRRP组名，两个节点的设置必须一样
	virtual_router_id 51
	#主节点的优先级（1-254之间）
	priority 100
	#组播信息发送间隔，两个节点设置必须一样
	advert_int 1
	#设置验证信息，两个节点必须一致
	authentication {
		auth_type PASS
		auth_pass 1111
	}
	#虚拟IP,对外提供MySQL服务的IP地址
	virtual_ipaddress {
		192.168.51.130
	}
}

# master2
# 配置通知的email
global_defs {
	notification_email {
		acassen@firewall.loc
		failover@firewall.loc
		sysadmin@firewall.loc
	}
	notification_email_from Alexandre.Cassen@firewall.loc
	smtp_server 192.168.51.1
	smtp_connect_timeout 30
	router_id LVS_DEVEL
	vrrp_skip_check_adv_addr
	# 注释掉vrrp_strict,开启会导致其他机器无法访问虚拟IP
	# vrrp_strict
	vrrp_garp_interval 0
	vrrp_gna_interval 0
}
# 检查mysql脚本，定时执行
vrrp_script check_run {
	script "/usr/check_run.sh"
	interval 3
}
# 设置虚拟ip
vrrp_instance VI_1 {
	# 当前节点的状态MASTER、BACKUP
	state BACKUP
	# 当前服务器使用的网卡名称，使用ifconfig查看
	interface ens33
	#VRRP组名，两个节点的设置必须一样
	virtual_router_id 51
	#主节点的优先级（1-254之间）
	priority 100
	#组播信息发送间隔，两个节点设置必须一样
	advert_int 1
	#设置验证信息，两个节点必须一致
	authentication {
		auth_type PASS
		auth_pass 1111
	}
	#虚拟IP,对外提供MySQL服务的IP地址
	virtual_ipaddress {
		192.168.51.130
	}
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91

2.3 检查脚本check_run.sh

master1和mster2分别创建脚本

vim /usr/check_run.sh
1

添加一下内容

#!/bin/bash
./root/.bashrc
count=1
while true
do
	mysql -uroot -proot -S /tmp/mysql.sock -e "select now();" > /dev/null 2>&1
	i=$?
	ps aux | grep mysqld | grep -v grep > /dev/null 2>&1
	j=$?
	if [ $i = 0 ] && [ $j = 0 ]
	then
		exit 0
	else
		if [ $i = 1 ] && [ $j = 0 ]
		then
			exit 0
		else
			if [ $count -gt 5 ]
			then
				break
			fi
			let count++
			continue
		fi
	fi
done
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

授与执行权限

chmod 755 /usr/check_run.sh
1

2.4 启动keepalived

systemctl start keepalived.service #启动
systemctl stop keepalived.service #停止
systemctl status keepalived.service #状态
systemctl restart keepalived.service #重启
1
2
3
4

查看状态

2.5 查看虚拟IP

master1查看vip

2.6 访问测试

1）远程连接数据库
2）插入数据测试：
用vip远程连接数据库(IP：192.168.51.130):

master1故障前，插入数据id是奇数（131服务器IP：192.168.51.131）

master1故障后：
1）vip漂移到132服务器

2）插入数据id是偶数（132服务器IP：192.168.51.132）

3、配置多源复制从节点

MySQL 5.7前只能实现一主一从、一主多从或者多主多从的复制，如果想实现多主一从的复制，只好使用MariaDB，但是MariaDB又与官方的MySQL版本不兼容的，在MySQL 5.7版本可以实现多主一从的复制了
配置步骤：

1、将主节点的数据同步到从节点

可以用navicat手动将表从任一master主机同步过来

2、配置my.cnf

server-id=133
gtid_mode=ON
enforce-gtid-consistency=ON
master_info_repository=table
relay_log_info_repository=table
1
2
3
4
5

3、配置多源复制

# 复制master1
mysql> change master to
master_host='192.168.51.131',
master_port=3306,
master_user='root',
master_password='123456',
master_auto_position=1
FOR CHANNEL 'm-131';

# 复制master2
mysql> change master to
master_host='192.168.51.132',
master_port=3306,
master_user='root',
master_password='123456',
master_auto_position=1
FOR CHANNEL 'm-132';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

和普通复制不同的是需要增加 FOR CHANNEL ‘xxx’ 语句指定不同的频道复制。由于是多源复制必须指定参数 master_info_repository=table

4、配置跳过的GTID集合

#master节点 :
mysql> flush logs;
mysql> show global variables like 'gtid_executed' \G
#slave节点:
mysql> reset master;
Query OK, 0 rows affected (0.00 sec)
mysql> set global gtid_purged='2b095e9d-5d7a-11ed-9a6a-000c296534b2:1-13,e1186686-5e58-11ed-855c-000c29920730:1-4';
Query OK, 0 rows affected (0.00 sec)
#启动节点
mysql> start slave for channel 'm-131';
#查看某一频道的复制状态
mysql> show slave status for channel 'm-131' \G
#启动节点
mysql> start slave for channel 'm-132';
mysql> show slave status for channel 'm-132' \G
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

配置完成，数据成功同步过来：