mysql集群（主从复制）实操

mysql集群（主从复制）实操

1.环境准备

1.准备5台全新的机器

​ 192.168.31.23 master1

​ 192.168.31.24 master2
​ 192.168.31.89 slave1
​ 192.168.31.159 slave2
​ 192.168.31.241 mycat

2.在每个机器本地hosts文件加入一下内容

[root@master1_23 ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

192.168.31.23 master1
192.168.31.24 master2
192.168.31.89 slave1
192.168.31.159 slave2
192.168.31.241 mycat

3.前四台机器分别全新安装mysql57

[root@master1_23 srv]# rpm -qa | grep mysql
mysql-community-common-5.7.27-1.el7.x86_64
mysql-community-client-5.7.27-1.el7.x86_64
mysql-community-server-5.7.27-1.el7.x86_64
mysql-community-libs-5.7.27-1.el7.x86_64
mysql-community-libs-compat-5.7.27-1.el7.x86_64

4.将所有机器的防火墙和selinux关掉

systemctl stop firewalld
systemctl disable firewalld
[root@master1-23 ~]# getenforce
Disabled

vim /etc/selinux/config
SELINUX=disabled

2.一主一从（M-S）（1）

主（master1）

登陆数据库

1.首先启动数据库
systemctl start mysqld

2.通过日志找到临时密码，并且设置数据密码
[root@master1-23 ~]# grep password /var/log/mysqld.log
2022-05-26T11:26:39.973413Z 1 [Note] A temporary password is generated for root@localhost: (ZT%>jydv6pP

# 更改数据库密码
mysqladmin -uroot -p'(ZT%>jydv6pP' password 'QazWsx@123'


3.登陆数据库
mysql -uroot -p'QazWsx@123'

准备数据1（验证主从同步使用）

master1

mysql> create database master1db;   # 创建master1db库
Query OK, 1 row affected (0.00 sec)

# 创建mastertab表
mysql> create table master1db.mastertab (id int,name varchar(50));	
Query OK, 0 rows affected (0.01 sec)

# 往表中写入数据
mysql> insert into master1db.mastertab values(1,'zhangsan');
Query OK, 1 row affected (0.02 sec)

mysql> insert into master1db.mastertab values(2,'lisi');
Query OK, 1 row affected (0.00 sec)

开启二进制日志 master1

[root@master1-23 ~]# vim /etc/my.cnf
log_bin			# 开启二进制日志
server-id=1		# 数据库id 每台机器的id不能相同

# 重新启动mysql
systemctl restart mysqld

创建复制用户 master1

 msyql> grant replication slave, replication client on *.* to 'rep'@'192.168.31.%' identified by 'QazWsx@123';

# 参数解释
grant  # 授权
replication  # 授予复制权限
*.*   # 所有库和所有表
rep		# 授权用户
192.168.31.%   # 该网段所有机器能够访问 
by 'QazWsx@123'  # 指定该用户密码

将**（master1）主数据与（master2）从**数据手动一致

1.备份master1数据
[root@master1-23 ~]# mysqldump -uroot -p'QazWsx@123'\
> --all-databases --single-transaction \
> --master-data=2 --flush-logs > `date +%F`-mysql-all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

2.将数据远程发送到（master2）从机器上
[root@master1-23 ~]# scp -r 2022-05-26-mysql-all.sql  master2:~/
root@master2's password:
2022-05-26-mysql-all.sql      100%  203   284.8KB/s   00:00

3.观察二进制日志分割点，方便（master2）从机器上恢复
vim 2022-05-26-mysql-all.sql
CHANGE MASTER TO MASTER_LOG_FILE='master1-23-bin.000002', MASTER_LOG_POS=154;

master1-23-bin.000002  # 分割的二进制文件
MASTER_LOG_POS=154     # 分割的位置

准备数据2（用于验证主从同步使用）master1

mysql -uroot -p'QazWsx@123' -e "insert into master1db.mastertab values(3,'wangsu');"
mysql -uroot -p'QazWsx@123' -e "insert into master1db.mastertab values(4,'zhaoliu'');"
mysql -uroot -p'QazWsx@123' -e "insert into master1db.mastertab values(5,'qi');"

从（master2）

首先测试一下刚才授权的rep用户是否可用（master2）

1.远程登陆master1的数据库
# 此环节如果登陆报错，查看是否授权问题，还有防火墙是否关闭
mysql -h master1 -urep -p'QazWsx@123'

启动服务器序号，防止乱序

# 不用在从设备上开启二进制日志，没有人向master2请求日志。
vim /etc/my.cnf
server-id=2

# 设置完了之后重启mysql
systemctl restart mysqld

# 测试一下能否正常登陆
mysql -uroot -p'QazWsx@123'

手动同步数据

# 在数据库中执行以下命令
mysql> set sql_log_bin=0;	# 临时关闭二进制日志文件，防止恢复数据被记录下来，是无用的

mysql> source ~/2022-05-26-mysql-all.sql   # 恢复备份文件数据

设置主服务器

1.此操作在master2（从）机器上
# 设置master1为主服务器，master2为slave服务器

# 这是该数据的主人，地址为master1	也可以写IP地址
mysql> change master to master_host='master1' , 
    -> master_user='rep',		# 访问主数据库的用户为 rep
    -> master_password='QazWsx@123',		# 访问 密码
 #   注意，二进制日志的位置，应该参照主服务器备份时生成的新位置。
    -> master_log_file='master1-23-bin.000002',		
    -> master_log_pos=154;

 
2.开始运行/停止运行   stop slave；
mysql> start slave;   

# 查看启动状态（IO-YES/SQL-YES）
	
 show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: master1
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master1-23-bin.000002
          Read_Master_Log_Pos: 1295
               Relay_Log_File: master2-24-relay-bin.000002
                Relay_Log_Pos: 1466
        Relay_Master_Log_File: master1-23-bin.000002
             Slave_IO_Running: Yes			
            Slave_SQL_Running: Yes

此时返回主服务器（master1）更新数据，在（master2）观察是否同步

（master1）主服务器

mysql> insert into master1db.mastertab values(6,'liu');
Query OK, 1 row affected (0.00 sec)

mysql> insert into master1db.mastertab values(7,'yi');
Query OK, 1 row affected (0.00 sec)

mysql> select * from mastertab;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
|    2 | lisi     |
|    3 | wangsu   |
|    4 | zhaoliu  |
|    5 | qi       |
|    6 | liu      |
|    7 | yi       |
+------+----------+
7 rows in set (0.00 sec)

此时观察（master2）从服务器

# （master1）更新数据前
mysql> select * from master1db.mastertab;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
|    2 | lisi     |
|    3 | wangsu   |
|    4 | zhaoliu  |
|    5 | qi       |
+------+----------+
5 rows in set (0.00 sec)

# （master1）更新数据前
mysql> select * from mastertab;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
|    2 | lisi     |
|    3 | wangsu   |
|    4 | zhaoliu  |
|    5 | qi       |
|    6 | liu      |
|    7 | yi       |
+------+----------+

3.一主一从（M-S）（2）

简单来说：可以通过 GTID 自动找点，无需像之前那样通过 binlog 名 和 position 号找点

GTID复制

1、master 更新数据时，会在事务前产生 GTID 并一同记录到 binlog 日志中；
2、slave 端的 IO 线程将变更的 binlog写入到本地的 relay-log（中继日志）中；
3、sql 线程从 relay-log 中获取对应的 GTID，对比 slave 端的 binlog 的记录 ；
4、如果有记录，说明该 GTID 的事务已执行，slave 会忽略该 GTID；
5、如果没有记录，slave 会从 relay-log 中执行该 GTID 的事务，并记录到 binlog 中；

实验2与上一个实验需求基本相同经。master1 作为主mysql，master2 作为从mysql。

不同之处，使用了
“gtid_mode=ON
enforce_gtid_consistency=1”
该属性自动记录position位置。不需要手动指定了。

环境

在上一个实验的基础上

重置master2数据库

1. 清空master2数据库
[root@master2-24 ~]# systemctl stop mysqld
[root@master2-24 ~]# rm -rf /var/lib/mysql/*
[root@master2-24 ~]# rm -rf /var/log/mysqld.log

2.启动数据库，重新获取临时密码，并且设定新的数据库密码
[root@master2-24 ~]# systemctl start mysqld
[root@master2-24 ~]# grep password /var/log/mysqld.log
2022-05-26T14:06:22.892911Z 1 [Note] A temporary password is generated for root@localhost: aHqO-6bUeW:d
[root@master2-24 ~]# mysqladmin -uroot -p'aHqO-6bUeW:d' password 'QazWsx@123'


3.登陆数据库
[root@master2-24 ~]# mysql -uroot -p'QazWsx@123'

master1 主服务器

1.启动二进制日志，服务器ID，GTID master1

1.修改/etc/my.cnf配置文件 ,添加如下内容
vim /etc/my.cnf    
log_bin
server-id=1
gtid_mode=ON     # 启用GTID模块
enforce_gtid_consistency=1		#强制GTID的一致性

2.重启mysql
systemctl restart mysqld

2.授权复制用户rep （如果你实验一master1没有情况无需此操作）

grant replication slave,replication client on *.* to 'rep'@'192.168.122.%' identified by 'QianFeng@123';

# 参数解释
grant  # 授权
replication  # 授予复制权限
*.*   # 所有库和所有表
rep		# 授权用户
192.168.31.%   # 该网段所有机器能够访问 
by 'QazWsx@123'  # 指定该用户密码


# 刷新数据库
flush privileges;   

3.备份数据 master1

1.创建新的数据
mysql> create database master1;
Query OK, 1 row affected (0.00 sec)

mysql> use master1;
Database changed
mysql> create table t1 (id int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1 values(2);
Query OK, 1 row affected (0.01 sec)

mysql> select * from master1.t1;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

2.备份数据
# master-data=2设置为2，已经不需要这个标记了。

mysqldump -uroot -p'QazWsx@123' --all-databases --single-transaction  --master-data=2 --flush-logs > `date +%F`-mysql.sql.bak


3.将备份文件发送到master2从服务器中
scp -r 2022-05-26-mysql.sql.bak  master2:~/
root@master2's password:
2022-05-26-mysql.sql.bak      100%  827KB  65.2MB/s   00:00


4. 模拟数据变化
 insert into  master1.t1 values(2);

master2 从服务器

1.测试一下rep用户是否可用 master2

1.远程登陆master1的数据库
# 此环节如果登陆报错，查看是否授权问题，还有防火墙是否关闭
mysql -h master1 -urep -p'QazWsx@123'

2.启动二进制日志，服务器ID，GTID master2

1.修改/etc/my.cnf配置文件 ,添加如下内容
vim /etc/my.cnf    
log_bin
server-id=1
gtid_mode=ON     # 启用GTID模块
enforce_gtid_consistency=1		#强制GTID的一致性

2.重启mysql
systemctl restart mysqld

3.手动恢复同步数据

# 在数据库中执行以下命令
mysql> set sql_log_bin=0;	# 临时关闭二进制日志文件，防止恢复数据被记录下来，是无用的

mysql> source ~/2022-05-26-mysql.sql.bak   # 恢复备份文件数据

# 验证一下是否恢复
mysql> select * from master1.t1;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

4.设置主服务器（master2）

1. 设置master1为主服务器，master2为slave服务器

# 这是该数据的主人，地址为master1	也可以写IP地址
mysql> change master to master_host='master1' , 
    -> master_user='rep',		# 访问主数据库的用户为 rep
    -> master_password='QazWsx@123',		# 访问 密码
    -> master_auto_position=1;		# 与master1协商自动找到定位点
Query OK, 0 rows affected, 2 warnings (0.05 sec)

# 此操作比第一次实验少了两行，不用手动添加定位点，GTID自动协商定位点


2.开始运行/停止运行   stop slave；
mysql> start slave;   


# 检查状态
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: master1
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master1-23-bin.000007
          Read_Master_Log_Pos: 446
               Relay_Log_File: master2-24-relay-bin.000002
                Relay_Log_Pos: 629
        Relay_Master_Log_File: master1-23-bin.000007
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

在主服务器（masger1）更新数据，查看从服务器（master2）是否同步

1. 现在从服务器（master2）更改前的数据
mysql> select * from master1.t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    2 |
+------+
3 rows in set (0.00 sec)


2.切换到master1更新数据

mysql> insert into master1.t1 values(3);
Query OK, 1 row affected (0.00 sec)

mysql> insert into master1.t1 values(4);
Query OK, 1 row affected (0.00 sec)

mysql> insert into master1.t1 values(5);
Query OK, 1 row affected (0.00 sec)

3.切换到master2查看是否同步

# 这是更新后的数据
mysql> select * from master1.t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
6 rows in set (0.00 sec)