企业级运维——Mysql读写分离

读写分离原理

如何理解读写分离: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 masterreset 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)

至此,我们的读写分离就实现了。

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值