文章目录
- 读写分离原理
- 1、什么是读写分离
- 2、为什么要读写分离呢?
- 3、什么时候要读写分离?
- 读写分离实验
- 实验准备
- server1配置
- server2配置
- 测试下gtid异步复制
- Mysql-proxy服务配置(server3)
- 测试
读写分离原理
如何理解读写分离:https://baijiahao.baidu.com/s?id=1614304400276051465&wfr=spider&for=pc
读写分离就是在主服务器上修改,数据会同步到从服务器,从服务器只能提供读取数据,不能写入,实现备份的同时也实现了数据库性能的优化,以及提升了服务器安全。
1、什么是读写分离
读写分离,基本的原理是让主数据库处理事务性增、改、删操作(INSERT、UPDATE、DELETE),而从数据库处理SELECT查询操作。数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。
2、为什么要读写分离呢?
因为数据库的“写”(写10000条数据到oracle可能要3分钟)操作是比较耗时的。
但是数据库的“读”(从oracle读10000条数据可能只要5秒钟)。
3、什么时候要读写分离?
数据库不一定要读写分离,如果程序使用数据库较多时,而更新少,查询多的情况下会考虑使用,利用数据库 主从同步 。可以减少数据库压力,提高性能。当然,数据库也有其它优化方案。memcache 或是 表折分,或是搜索引擎。都是解决方法。
当数据库读远大于写,查询多的情况,就可以考虑主数据库负责写操作,从数据库负责读操作,一主多重,从而把数据读写分离,最后还可以结合redis等缓存来配合分担数据的读操作,大大的降低后端数据库的压力。
读写分离实验
实验准备
三台rhel7.5的虚拟机
主机名(IP) | 作用 |
server1(172.25.25.1) | 写数据库 |
server2(172.25.25.2) | 读数据库 |
server3(172.25.25.3) | Mysql-proxy |
server1配置
我们的读写分离实验建立在基于gtid的异步复制,所以需要配置gtid的异步复制。
- 1.因为之前做了组复制,先关闭组复制,关闭服务,删除目录下的文件,再开启服务。
[root@server1 mysql]# cd /var/lib/mysql
[root@server1 mysql]# systemctl stop mysqld.service
[root@server1 mysql]# rm -fr *
[root@server1 mysql]# systemctl start mysqld
- 2.mysql进行安全初始化
[root@server1 mysql]# cat /var/log/mysqld.log | grep password
[root@server1 mysql]# mysql_secure_installation
- 3.创建gtid需要的复制用户
[root@server1 mysql]# mysql -uroot -p
mysql> CREATE USER 'repl'@'172.25.66.%' IDENTIFIED BY 'Wsp+123ld';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'172.25.66.%';
mysql> flush privileges;
mysql> show master status;
- 4.修改mysql配置文件
/etc/my.cnf
,写入gtid复制相关配置,并重启服务。
log-bin=mysql-bin
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
systemctl restart mysqld
server2配置
- 1.因为之前做了组复制,先关闭组复制,关闭服务,删除目录下的文件,再开启服务。
[root@server2 mysql]# systemctl stop mysqld.service
[root@server2 mysql]# rm -fr *
[root@server2 mysql]# ls
[root@server2 mysql]# systemctl restart mysqld
- 2.配置文件
/etc/my.cnf
添加如下内容,并重启服务
log-bin=mysql-bin
server_id=2
gtid_mode=ON
enforce-gtid-consistency=true
systemctl restart mysqld
- 3.mysql进行安全初始化
[root@server1 mysql]# cat /var/log/mysqld.log | grep password
[root@server1 mysql]# mysql_secure_installation
- 4.创建gtid需要的复制用户
[root@server1 mysql]# mysql -uroot -p
mysql> CREATE USER 'repl'@'172.25.66.%' IDENTIFIED BY 'Wsp+123ld';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'172.25.66.%';
mysql> flush privileges;
mysql> show master status;
- 5.先停掉slave,添加新的master模式,然后再次开启slave
mysql> stop slave;
Query OK, 0 rows affected (0.03 sec)
mysql> CHANGE MASTER TO
-> MASTER_HOST = '172.25.66.1',
-> MASTER_USER = 'repl',
-> MASTER_PASSWORD = 'Wsp+123ld',
-> MASTER_AUTO_POSITION = 1;
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
- 4.查看slave状态,注意uuid是否与master一致。
mysql> show slave status\G *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.66.1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: server2-relay-bin.000002
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 576
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 50858459-b1d2-11e9-8666-525400d85f32
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
注意,如果出现Slave_IO_Running: No
,看报错日志,一般在server1上执行reset master
,然后在server2reset master
和reset slave
,重新change master,并开启即可。
测试下gtid异步复制
server1:
mysql> create database test;
Query OK, 1 row affected (0.02 sec)
mysql> use test;
Database changed
mysql> create table t1(username varchar(10) not null,age int );
Query OK, 0 rows affected (0.15 sec)
mysql> insert into t1 values('aa',11);
Query OK, 1 row affected (0.04 sec)
server2:
mysql> select * from test.t1;
+----------+------+
| username | age |
+----------+------+
| aa | 11 |
+----------+------+
1 row in set (0.00 sec)
Mysql-proxy服务配置(server3)
server3做数据库代理端
- 1.关闭之前的mysqld服务,下载解压安装mysql-proxy到指定目录。
systemctl stop mysqld(如果之前开启过mysqld,关掉,因为占用端口3306)
tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /usr/local
cd /usr/local/
mv mysql-proxy-0.8.5-linux-el6-x86-64bit mysql-proxy
- 2.创建相关目录:日志目录、配置文件目录
cd mysql-proxy/
mkdir log conf
- 3.进入配置目录,并创建填写配置文件(主要是为了启动配置)
vim /usr/local/mysql-proxy/conf/mysql-proxy.conf
写入:
[mysql-proxy]
proxy-address=0.0.0.0:3306 # 代理的ip为本机的3306端口
proxy-backend-addresses=172.25.66.1:3306 # 指定后端读写数据库
proxy-read-only-backend-addresses=172.25.66.2:3306 #指定后端只读数据库
proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua # 指定启动读写分离脚本
pid-file=/usr/local/mysql-proxy/log/mysql.pid # pid存放位置
log-file=/usr/local/mysql-proxy/log/mysql.log # 日志存放维护iz
plugins=proxy # 插件
log-level=debug # 日志级别
keepalive=true # 保证高可用
daemon=true # 守护进程开启
- 4.修改lua脚本,设置使用读写分离连接数。
[root@server3 mysql-proxy]# cd /usr/local/mysql-proxy/share/doc/mysql-proxy/
[root@server3 mysql-proxy]# vim rw-splitting.lua
37 -- connection pool
38 if not proxy.global.config.rwsplit then
39 proxy.global.config.rwsplit = {
40 min_idle_connections = 1,
41 max_idle_connections = 2,
42
43 is_debug = false
44 }
45 end
为了实验效果明显修改为1和2
- 5.开启mysql-proxy服务,并查看其进程,日志和端口。
开启之前,因为我们把所有的配置写在文件里,所以需要给文件权限。
[root@server3 mysql-proxy]# chmod 660 /usr/local/mysql-proxy/conf/mysql-proxy.conf
[root@server3 mysql-proxy]# /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf
查看进程
[root@server3 mysql-proxy]# ps aux | grep mysql-proxy
root 1880 0.0 0.0 37908 720 ? S 16:11 0:00 /usr/local/mysql-proxy/libexec/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf
root 1881 0.0 0.1 37908 1164 ? S 16:11 0:00 /usr/local/mysql-proxy/libexec/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf
root 1884 0.0 0.0 112704 980 pts/0 R+ 16:12 0:00 grep --color=auto mysql-proxy
查看日志
[root@server3 mysql-proxy]# cat /usr/local/mysql-proxy/log/mysql.log
2019-07-29 16:11:42: (message) chassis-unix-daemon.c:136: [angel] we try to keep PID=1881 alive
2019-07-29 16:11:42: (debug) chassis-unix-daemon.c:157: waiting for 1881
2019-07-29 16:11:42: (debug) chassis-unix-daemon.c:121: we are the child: 1881
2019-07-29 16:11:42: (critical) plugin proxy 0.8.5 started
2019-07-29 16:11:42: (debug) max open file-descriptors = 1024
2019-07-29 16:11:42: (message) proxy listening on port 0.0.0.0:3306
2019-07-29 16:11:42: (message) added read/write backend: 172.25.66.1:3306
2019-07-29 16:11:42: (message) added read-only backend: 172.25.66.2:3306
查看端口:
[root@server3 mysql-proxy]# netstat -tnlp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 1881/mysql-proxy
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 840/sshd
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 1075/master
tcp6 0 0 :::22 :::* LISTEN 840/sshd
tcp6 0 0 ::1:25 :::* LISTEN 1075/master
测试
- 1.server1的mysql新建用户,给其授权相关读写权力,使其可以从真机登陆:
mysql> grant insert,update,select on *.* to rwspilt_user@'%' identified by 'Wsp+123ld';
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)
mysql> select user from mysql.user;
+---------------+
| user |
+---------------+
| rwspilt_user |
| repl |
| mysql.session |
| mysql.sys |
| root |
+---------------+
5 rows in set (0.00 sec)
- 2.真机:查看读的情况
因为我们的读写分类是在一定并发连接的基础上实现的,我们的读写分类lua脚本中说明,最大连接数超过两个会启用读写分离。
所以我们在真机可以开启三个同时连接server3的mysql代理端口,同时,在server3上使用lsof
监控mysql的连接状态。
第一个连接:
[root@localhost mysql]# mysql -h 172.25.66.3 -urwspilt_user -pWsp+123ld
第二个连接:
[root@localhost mysql]# mysql -h 172.25.66.3 -urwspilt_user -pWsp+123ld
第三个连接:
[root@localhost mysql]# mysql -h 172.25.66.3 -urwspilt_user -pWsp+123ld
此时,在server3上监控:
[root@server3 mysql-proxy]# lsof -i:3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysql-pro 1881 root 10u IPv4 24981 0t0 TCP *:mysql (LISTEN)
mysql-pro 1881 root 11u IPv4 26001 0t0 TCP server3:mysql->172.25.66.250:38008 (ESTABLISHED)
mysql-pro 1881 root 12u IPv4 25676 0t0 TCP server3:33840->server1:mysql (ESTABLISHED)
mysql-pro 1881 root 13u IPv4 26002 0t0 TCP server3:33850->server1:mysql (ESTABLISHED)
mysql-pro 1881 root 14u IPv4 26003 0t0 TCP server3:mysql->172.25.66.250:38010 (ESTABLISHED)
mysql-pro 1881 root 15u IPv4 26004 0t0 TCP server3:49244->server2:mysql (ESTABLISHED)
可以发现,当连接数超过三个时,server3将代理转到只读server2上,说明开始启动读写分离。
- 3.为了看实验效果,我们在server2 停止异步复制,因为现在是从server2读,但是写是往server1写,停止了异步复制,我们就看不到写入的数据了,只有server1自身可以看到。
在真机插入数据:
MySQL [(none)]> select * from test.t1;
+----------+------+
| username | age |
+----------+------+
| aa | 11 |
+----------+------+
MySQL [(none)]> insert into test.t1 values('mac',12);
Query OK, 1 row affected (0.04 sec)
但是查看不到:
MySQL [(none)]> select * from test.t1;
+----------+------+
| username | age |
+----------+------+
| aa | 11 |
+----------+------+
写库server1查看确实插入了数据:
mysql> select * from test.t1;
+----------+------+
| username | age |
+----------+------+
| aa | 11 |
| mac | 12 |
+----------+------+
2 rows in set (0.00 sec)
至此,我们的读写分离就实现了。