主从复制（一主二从并且实现读写分离）
实验步骤
1，安装好六台linux（centos7.9）虚拟机
2，在master，slave1，slave2上用脚本安装好二进制mysql(msyql5.7.33)
3，在以上三台虚拟机上做一主二从基于GTID的主从复制
1，关闭防火墙
[root@localhost ~]# systemctl disable firewalld
2，在master，slave1，slave2上开启二进制日志并且设置server-id(不能相同)，vim 打开/etc/my.cnf修改配置文件
[root@localhost ~]# vim /etc/my.cnf
在mysqld下添加以下内容后刷新服务[root@localhost ~]# service mysql restart
server-id = 1
log_bin
3，创建用来复制二进制日志的用户，给予权限
root@(none) 09:57 mysql>create user 'liao'@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
root@(none) 09:59 mysql>grant replication slave on *.* to 'liao'@'%';
Query OK, 0 rows affected (0.00 sec)
4，创建全备
[root@localhost ~]# mysqldump -uroot -p123456 --all-databases >/all.sql
5，将全备传到slave1和slave2上
[root@localhost ~]# scp /all.sql root@192.168.136.131:/
root@192.168.136.131's password:
all.sql 100% 852KB 11.6MB/s 00:00
[root@localhost ~]# scp /all.sql root@192.168.136.130:/
root@192.168.136.130's password:
all.sql 100% 852KB 31.8MB/s 00:00
[root@localhost ~]#
6，在slave1和slave2上做全备
[root@localhost ~]# mysql -uroot -p123456 <all.sql
7，刷新日志并且记录下位置号：
root@(none) 10:18 mysql>flush logs;
Query OK, 0 rows affected (0.00 sec)
root@(none) 10:19 mysql>show master status;
+----------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+------------------------------------------+
| localhost-bin.000002 | 154 | | | 0f03b367-a18a-11eb-9d07-000c2912d2f8:1-4 |
+----------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
8，在slave1，slave2上启用master2的信息
root@(none) 10:26 mysql>stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
root@(none) 10:26 mysql>RESET MASTER;
Query OK, 0 rows affected (0.00 sec)
root@(none) 10:26 mysql>CHANGE MASTER TO MASTER_HOST = '192.168.136.129',
-> MASTER_USER = 'liao',
-> MASTER_PASSWORD = '123456',
-> MASTER_PORT = 3306
-> MASTER_LOG_FILE = 'localhost-bin.000002',
-> MASTER_LOG_POS = 154;
9，查看是否成功
root@(none) 10:29 mysql>START SLAVE;
Query OK, 0 rows affected (0.01 sec)
root@(none) 10:31 mysql>show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.136.129
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 194
Relay_Log_File: localhost-relay-bin.000009
Relay_Log_Pos: 407
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Slave_IO_Running和Slave_SQL_Running是yes就是成功了
10，部署基于GTID，先关闭slave，清除master
root@(none) 10:31 mysql>stop slave;
Query OK, 0 rows affected (0.00 sec)
root@(none) 10:35 mysql>RESET MASTER;
Query OK, 0 rows affected (0.00 sec)
修改master的配置文件my.cnf在mysqld下添加以下内容
gtid_mode=ON
enforce-gtid-consistency=on
enforce_gtid_consistency 保证GTID安全的参数强制gtid一致性，开启后对于特定create table不被支持
slave1和slave2配置信息
刷新服务：
[root@localhost ~]# service mysql restart
4，读写分离
1，在router1和router2上安装mysqlrouter（安装包来自官方网站）
[root@localhost ~]# ls
anaconda-ks.cfg mysql-router-community-8.0.23-1.el7.x86_64.rpm
[root@localhost ~]# rpm -ivh mysql-router-community-8.0.23-1.el7.x86_64.rpm
2,关闭防火墙
3，在router1和router2上修改配置在这里插入代码片文件[root@localhost ~]# vi /etc/mysqlrouter/mysqlrouter.conf
#名字可随意
[routing:read_write]
#mysql-router服务器的ip
bind_address = 192.168.136.132
# 端口
bind_port = 7001
# 支持可读写
mode = read-write
# 因为是可读写所有使用master的ip
destinations = 192.168.136.129:3306
max_connections = 65535
max_connect_errors = 100
client_connect_timeout = 9
[routing:read_only]
bind_address = 192.168.136.132
bind_port = 7002
mode = read-only
# 因为是只读所有用slave的ip
destinations = 192.168.136.130:3306,192.168.136.131:3306
max_connections = 65535
max_connect_errors = 100
clent_connect_timeout = 9
要读写的通过7001端口到master上
只读的通过7002端口到slave上
从而实现读写分离
4，刷新服务
[root@localhost ~]# service mysqlrouter restart
Redirecting to /bin/systemctl restart mysqlrouter.service
创建用户进行验证（不做详细演示）
5，双vip高可用
1，在router1和router2上安装keepalived
[root@localhost ~]# yum install keepalived -y
2，修改配置文件[root@localhost ~]# vi /etc/keepalived/keepalived.conf36行以下全部删除
vrrp_instance VI_1 {
#为master状态
state MASTER
#指定监听网络接口
interface ens33
#虚拟路由id
virtual_router_id 51
#优先级范围
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
#虚拟ip
192.168.136.20
}
}
vrrp_instance VI_2 {
state BACKUP
interface ens33
virtual_router_id 52
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.136.21
}
}
router1和router2互为主从
由于是双VIP，此处我们需要配置两个vip，让master和 backup互为主主，最大的利用资源
做master - - - VI_1
删除从virtual server开始的所有(从这里开始主要是 keepalived的负载均衡作用，此次我们只使用keepalived的高可用)
注释 vrrp_strict # vrrp_strict
修改状态为backup state MASTER
修改网络接口与本机一致 - - - 一致则不用修改 interface eth33
修改virtual_router_id — 一致
修改优先级 - - -优先级高于backup
修改虚拟ip地址vip - - - 一致
做backup - - - VI_2
修改vrrp_instance 为 VI_2
修改状态为backup state BACKUP
修改网络接口与本机一致 - - - 一致则不用修改 interface eth33
修改virtual_router_id — 一致
修改优先级 - - - 优先级低于master
修改虚拟ip地址vip - - - 一致
3，刷新服务，查看是否成功
[root@localhost ~]# ip add
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 00:0c:29:90:4d:61 brd ff:ff:ff:ff:ff:ff
inet 192.168.136.132/24 brd 192.168.136.255 scope global noprefixroute dynamic ens33
valid_lft 1703sec preferred_lft 1703sec
inet 192.168.136.20/32 scope global ens33
valid_lft forever preferred_lft forever
inet 192.168.136.21/32 scope global ens33
valid_lft forever preferred_lft forever
inet6 fe80::b4b3:ad71:36dc:2e84/64 scope link noprefixroute
valid_lft forever preferred_lft forever
出现两个虚拟ip地址即为成功
1129
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
