文章目录
搭建MySQL读写分离
1、相关概念
①、数据读写分离介绍
- 把客户查询数据的select 请求 和 写数据的请求(包括insert delete update)
- 分别发给不同的数据库服务器处理
②、为什么要使用读写分离结构存储数据呢?
-
第1点:减去数据库服务器的访问压力
-
第2点:提高硬件利用率
-
但是得保证负责处理select访问请求数据库服务器的数据要和处理insert访问请求的数据库服务器的数据一致。 所以要想实现数据的读写分离 存储数据的数据库服务器之间必须是主从结构。
③、如何实现数据的读写分离?
-
第1种方式:通过程序实现(程序在编写访问数据库服务的脚本,执行select命令时 固定连接从数据库服务器,执行insert命令时 固定连接master数据库服务器)
-
第2种方式:通过服务实现(搭建提供读写分离服务功能的服务器程序访问数据库服务时,连接的不是数据库服务器本机,而是连接读写分离服务器,由读写分离服务器提供数据库连接服务)
④、提供数据读写分离服务的软件?(软件的统称 中间件)
- mysql-proxy 、mycat 、 maxscale
⑤、maxscale 软件提供的读写分离服务的工作过程
- 接收到客户连接数据库服务的请求后,根据客户端访问数据的命令类型,把请求给不同的数据库服务器处理
- 把查询请求select,给监视到的slave角色的数据库服务器处理
- 把写请求insert等等,给监视到的master角色的数据库服务器处理
2、环境准备
主机名 | IP地址 | 配置 | 操作系统 | 软件包 |
---|---|---|---|---|
mysql01 | 192.168.1.11 | 1CPU1内存 | centos7.5 | mysql-5.7.17.tar |
mysql02 | 192.168.1.12 | 1CPU1内存 | centos7.5 | mysql-5.7.17.tar |
maxscale | 192.168.1.50 | 1CPU1内存 | centos7.5 | maxscale-2.1 |
client | 192.168.1.10 | 1CPU1内存 | centos7.5 | mariadb |
软件包整理好了
链接:https://pan.baidu.com/s/1y0rrb_CDfHHzMNal-6IrsQ
提取码:lsz5
3、搭建一主一从结构
①、安装mysql服务
# 这里以mysql01为例,mysql02操作一样
# 把mysql-5.7.17.tar软件包上传到/opt文件
[root@mysql01 ~]# cd /opt/
[root@mysql01 opt]# ls
mysql-5.7.17.tar
# 解压缩
[root@mysql01 opt]# mkdir mysql
[root@mysql01 opt]# tar -xf mysql-5.7.17.tar -C mysql
# 安装mysql包
[root@mysql01 opt]# yum -y install ./mysql/*.rpm
# 启动mysql服务
[root@mysql01 opt]# systemctl enable mysqld --now
# 查看mysql的默认初始密码
[root@mysql01 opt]# grep -i password /var/log/mysqld.log
2023-08-25T13:13:55.468673Z 1 [Note] A temporary password is generated for root@localhost: KUfV_t3K>-sQ
# 登录mysql
[root@mysql01 opt]# mysql -uroot -p'KUfV_t3K>-sQ'
# 首次登录需要先修改root密码
mysql> alter user root@"localhost" identified by "123qqq...A";
mysql> exit
# 关闭防火墙和selinux
[root@mysql01 opt]# sed -i '/^SELINUX=/s/enforcing/permissive/' /etc/selinux/config
[root@mysql01 opt]# setenforce 0
[root@mysql01 opt]# yum -y remove firewall*
②、实现主从同步
# 第1 步:把mysql01配置为master数据库服务器
# 修改配置文件在第4行下面新添加两行
[root@mysql01 opt]# vim /etc/my.cnf
4 [mysqld]
5 server_id=11 # 指定id值(1-255,一般写主机号)
6 log_bin=mysql01 # 启用binlog日志
[root@mysql01 opt]# mysql -uroot -p123qqq...A
# 创建新用户给从服务来使用,"replication slave":让用户有复制命令权限
mysql> grant replication slave on *.* to repluser@"%" identified by "123www...A";
# 查看日志名和偏移量
+----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| mysql01.000002 | 441 | | | |
+----------------+----------+--------------+------------------+-------------------+
# 第2 步:把mysql02配置为slave数据库服务器
# 在第4行下面添加一行
[root@mysql02 opt]# vim /etc/my.cnf
4 [mysqld]
5 server_id=12
[root@mysql02 opt]# systemctl restart mysqld
[root@mysql02 opt]# mysql -uroot -p123qqq...A
mysql> change master to
-> master_host='192.168.1.11', master_user='repluser',
-> master_password='123www...A',
-> master_log_file='mysql01.000002', master_log_pos=441;
mysql> start slave;
# 检查从服务器的状态
mysql> show slave status \G
... ...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
... ...
4、配置maxscale代理服务器
①、安装软件
# 上传软件包到/opt目录下
[root@maxscale ~]# cd /opt/
[root@maxscale opt]# ls
maxscale-2.1.2-1.rhel.7.x86_64.rpm
[root@maxscale opt]# yum -y install maxscale-2.1.2-1.rhel.7.x86_64.rpm
# 关闭selinux和防火墙
[root@maxscale opt]# sed -i '/^SELINUX=/s/enforcing/permissive/' /etc/selinux/config
[root@maxscale opt]# setenforce 0
[root@maxscale opt]# yum -y remove firewall*
②、修改主配置文件
- 配置读写分离服务器
# 备份主配置文件
[root@maxscale opt]# cp /etc/maxscale.cnf /root/
# 清空源配置的内容
[root@maxscale opt]# echo '' > /etc/maxscale.cnf
# 编写配置文件
[root@maxscale opt]# vim /etc/maxscale.cnf
[maxscale]
threads=auto # 服务运行后线程的数量,auto:根据cpu的配置来指定多少
[server1] # 指定第1台数据库服务器的ip地址
type=server
address=192.168.1.11 # 第一台的IP地址
port=3306
protocol=MySQLBackend
[server2] # 指定第2台数据库服务器的ip地址
type=server
address=192.168.1.12 # 第二台的IP地址
port=3306
protocol=MySQLBackend
[MySQL Monitor] # 定义监视的数据库服务器
type=monitor
module=mysqlmon
servers=server1,server2 # 监视server1和server2
user=mysqla # 监控用户(监视数据库服务的运行状态和数据库服务器的主从角色)
passwd=123eee...A # 连接密码
monitor_interval=10000
[Read-Write Service] # 启用读写分离服务
type=service
router=readwritesplit
servers=server1,server2 # 读写分离服务在server1和server2服务器之间进行
user=mysqlb # 路由用户(当客户端连接到maxscale这个节点上时,maxscale节点会使用该账号去查后端数据库,检查客户端登陆的用户是否有权限或密码是否正确等等,比如客户端用test用户访问的数据库,那maxscale会用路由用户去检查test用户是否存在等)
passwd=123eee...A # 连接密码
max_slave_connections=100%
[MaxAdmin Service] # 管理服务(通过访问管理服务可以查看监控信息)
type=service
router=cli
[Read-Write Listener] # 定义读写分离服务使用端口号
type=listener
service=Read-Write Service
protocol=MySQLClient
port=4006 # 端口号
[MaxAdmin Listener] # 定义管理服务使用端口号
type=listener
service=MaxAdmin Service
protocol=maxscaled
socket=default
port=4016 # 端口号手动添加,不指定时使用的是默认端口在启动服务以后可以知道默认端口是多少
③、添加授权用户
- 配置数据库服务器(在数据库服务器上添加监控用户和路由用户)
- 注意:因为是主从结构 ,所以只需要在主服务器添加,从服务器会自动同步
- 添加监控用户 mysqla 用户(监视数据库服务的运行状态和数据库服务器的主从角色)
- 权限说明:
- replication client:监视数据库服务的运行状态
- replication slave:数据库服务器的主从角色
[root@mysql01 opt]# mysql -uroot -p123qqq...A
# 添加路由用户 mysqla 用户 (监视数据库服务的运行状态和数据库服务器的主从角色)
mysql> grant replication slave , replication
-> client on *.* to mysqla@"%" identified by "123eee...A";
# 添加路由用户 mysqlb 用户(56主机检查数据库服务器上是否添加了客户端连接服务使用的用户)
# 对授权库下的表有查询权限
mysql> grant select on mysql.* to mysqlb@"%" identified by "123eee...A";
④、查看和测试授权用户
#在从服务器查看用户是否同步
[root@mysql02 opt]# mysql -uroot -p123qqq...A -e 'select user from mysql.user where user="mysqla"'
+--------+
| user |
+--------+
| mysqla |
+--------+
[root@mysql02 opt]# mysql -uroot -p123qqq...A -e 'select user from mysql.user where user="mysqlb"'
+--------+
| user |
+--------+
| mysqlb |
+--------+
# 验证数据库服务器的授权用户 mysqla 和 mysqlb
# 安装提供mysql命令的软件
[root@maxscale opt]# which mysql || yum -y install mariadb
[root@maxscale opt]# mysql -h192.168.1.11 -umysqla -p123eee...A
[root@maxscale opt]# mysql -h192.168.1.12 -umysqla -p123eee...A
[root@maxscale opt]# mysql -h192.168.1.11 -umysqlb -p123eee...A
[root@maxscale opt]# mysql -h192.168.1.12 -umysqlb -p123eee...A
说明:
能连接成功才是对的
如果连接失败,在主数据库服务器mysql01
把添加mysqla
用户 和mysqlb
用户的命令再执行一遍
⑤、启动读写分离服务
# 启动服务,-f:file的简写,指定服务启动时调用的配置文件,可以省略不写
[root@maxscale opt]# maxscale -f /etc/maxscale.cnf
# 查看日志文件
[root@maxscale opt]# ls /var/log/maxscale/
maxscale.log
# 查看读写分离服务端口号
[root@maxscale opt]# netstat -utnlp | grep 4006
tcp6 0 0 :::4006 :::* LISTEN 1580/maxscale
# 查看管理读写分离服务端口号
[root@maxscale opt]# netstat -utnlp | grep 4016
tcp6 0 0 :::4016 :::* LISTEN 1580/maxscale
# 把服务杀死 再启动 相当于重启服务 (修改了配置文件后要重启服务使其配置生效)
[root@maxscale opt]# killall -9 maxscale //通过杀进程的方式停止服务
[root@maxscale opt]# maxscale -f /etc/maxscale.cnf //启动服务
5、测试配置
①、查看监控信息
- 在maxscale本机访问管理服务查看数据库服务的监控信息,admin是maxscale软件自带的管理员账号
[root@maxscale opt]# maxadmin -uadmin -pmariadb -P4016
MaxScale> list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server | Address | Port | Connections | Status
-------------------+-----------------+-------+-------------+--------------------
server1 | 192.168.1.11 | 3306 | 0 | Master, Running
server2 | 192.168.1.12 | 3306 | 0 | Slave, Running
-------------------+-----------------+-------+-------------+--------------------
MaxScale> exit
# 排错方法 : 查看日志里的报错信息 vim /var/log/maxscale/maxscale.log
②、在主服务器上添加访问数据连接用户
- 在主服务器添加即可,从服务器会自动同步数据
- 客户端能够连接读写分离服务器访问数据库服务
# ------在mysql01主服务器上创建库和用户
[root@mysql01 opt]# mysql -uroot -p123qqq...A
mysql> create database bbsdb;
mysql> create table bbsdb.a(id int);
mysql> grant all on bbsdb.* to yaya@"%" identified by "123qqq...A";
# 在从服务器mysql02查看存储数据库表和添加用户
[root@mysql02 opt]# mysql -uroot -p123qqq...A
mysql> desc bbsdb.a;
mysql> select user from mysql.user where user="yaya";
# -----客户端client连接读写分离服务器maxscale访问数据库服务
[root@client ~]# yum -y install mariadb
# mysql -h读写分离服务器的ip -P读写分离服务的端口号 -u数据库授权用户名 -p密码
[root@client ~]# mysql -h192.168.1.50 -P4006 -uyaya -p123qqq...A
# 连接读写分离服务后,可以对数据做查询和存储操作
mysql> select * from bbsdb.a;
Empty set (0.00 sec)
mysql> insert into bbsdb.a values(8888);
Query OK, 1 row affected (0.06 sec)
mysql> select * from bbsdb.a;
+------+
| id |
+------+
| 8888 |
+------+
1 row in set (0.00 sec)
③、验证maxscale主机的数据读写分离功能
- 怎么验证查询
select
访问,就在mysql02
从服务器获取的数据呢?- 在从服务本机向表里添加1条记录(在从服务添加的新数据主服务器不会同步)
- 怎么验证存储写数据,就是存储在了主机服务器
mysql01
上?- 把在只有在
mysql02
数据库中有的数据删除,删除失败就说明在mysql01
上执行写操作
- 把在只有在
# ----------------读测试
# 在从服务器mysql02添加新纪录
[root@mysql02 opt]# mysql -uroot -p123qqq...A -e 'insert into bbsdb.a values(5252)'
[root@mysql02 opt]# mysql -uroot -p123qqq...A -e 'select * from bbsdb.a'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+
| id |
+------+
| 8888 |
| 5252 |
+------+
# 在主服务器上查看就只能看见一条
[root@mysql01 opt]# mysql -uroot -p123qqq...A -e 'select * from bbsdb.a'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+
| id |
+------+
| 8888 |
+------+
#客户端访问读写分离服务器查询数据,看见的是两条数据说明在mysql02机器上查数据
[root@client ~]# mysql -h192.168.1.50 -P4006 -uyaya -p123qqq...A -e 'select * from bbsdb.a'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+
| id |
+------+
| 8888 |
| 5252 |
+------+
# ----------------写入测试
# 虽然会执行成功但是并没有执行成功,※这里为什么会执行成功而不报错呢,是因为在从数据库中有这一行的数据,所以在删除的时候不会显示未知列
[root@client ~]# mysql -h192.168.1.50 -P4006 -uyaya -p123qqq...A -e 'delete from bbsdb.a where id =5252'
# 查看数据还是会存在
[root@client ~]# mysql -h192.168.1.50 -P4006 -uyaya -p123qqq...A -e 'select * from bbsdb.a'
+------+
| id |
+------+
| 8888 |
| 5252 |
+------+
# 在从服务器mysql02主机上就能执行成功
[root@mysql02 opt]# mysql -uroot -p123qqq...A -e "delete from bbsdb.a where id = 5252;"
mysql: [Warning] Using a password on the command line interface can be insecure.
# 在mysql02执行之后就会查看到少了一行
[root@client ~]# mysql -h192.168.1.50 -P4006 -uyaya -p123qqq...A -e 'select * from bbsdb.a'
+------+
| id |
+------+
| 8888 |
+------+
说明:
如果主从结构中的从服务器宕机了,就实现不了读写分离了,会把读写请求都给主服务器处理。
如果主从结构中的主服务器宕机了,读写分离服务无法访问
读写分离服务器只有1台,单点故障问题无法避免。
到这里所有配置就结束了
亲爱的IT行业的朋友们🎉🎉🎉
🚀🚀🚀作为IT行业的从业者,不断学习、掌握新知识和新技能是我们应该坚持的信条。这是因为IT行业发展迅速,每天都有新技术和新应用出现,如果停滞不前,就会被时代淘汰。
🕵️🕵️🕵️在IT行业中,抱着学习的态度,不断跟进最新技术和行业动态,才能成为行业内的佼佼者。同时,通过工作实践不断地提升自己的技能水平,还可以积累宝贵的经验,让自己更加优秀。
🍔🍔🍔除此之外,学习也是一种享受。对于IT从业者来说,学习能带给我们无限的可能性和探索的空间,在解决问题和创造价值的过程中,感受到成长和满足,这种快乐是无与伦比的。
😼😼😼所以,不管你是初学者还是资深从业者,都要始终保持好奇心和求知欲。抓住每一个学习机会,拓宽视野、磨炼技能,让自己变得更强大、更具竞争力。相信只要不放弃,努力学习,未来一定会变得更加光明和美好。
🎊🎊🎊最后,让我们一起在学习的路上前行,不断挑战自我,超越自我,成为IT行业内的佼佼者!