mysql-03 mysql 一主多从+主从复制+读写分离

发表于 | 更新于 | 分类于 ， | 评论数： | 热度： ℃
mysql-03 mysql 一主多从+主从复制+读写分离

mysql-03 mysql 一主多从+主从复制+读写分离

概念引入

　　利用mysql主从复制技术，可以在保证主从数据库数据同步的前提下，实现读写分离。在master上完成写操作，在slave上完成读操作，可以分担master的压力，从而减少主数据库的连接，降低数据库崩溃、数据丢失等风险。mysql读写分离技术非常适用于读操作量比较大的场景，slave也可以设置多台，可大幅减轻master的压力。

常见mysql读写分离分类

一、基于程序代码内部实现

　　在代码中根据select 、insert进行路由分类，这类方法也是目前生产环境下应用最广泛的。优点是性能较好，因为程序在代码中实现，不需要增加额外的硬件开支，缺点是需要开发人员来实现，运维人员无从下手。

二、基于中间代理层实现

　　代理一般介于应用服务器和数据库服务器之间，代理数据库服务器接收到应用服务器的请求后根据判断后转发到，后端数据库，有以下代表性的应用程序。

1）mysql_proxy：mysql_proxy是Mysql的一个开源项目，通过其自带的lua脚本进行sql判断。（要求具有lua编程能力，严重依赖lua脚本，需要不停给mysql_proxy打补丁，修复一些缺陷，mysql官方并不建议将此程序在生产环境使用）。

2）Atlas：是由 Qihoo 360, Web平台部基础架构团队开发维护的一个基于MySQL协议的数据中间层项目。它是在mysql-proxy 0.8.2版本的基础上，对其进行了优化，增加了一些新的功能特性。360内部使用Atlas运行的mysql业务，每天承载的读写请求数达几十亿条。支持事物以及存储过程。

3）Amoeba：由阿里巴巴集团在职员工陈思儒使用序java语言进行开发，阿里巴巴集团将其用户生产环境下，但是它并不支持事物以及存数过程。（只需要针对xml配置文件配置即可，功能强大配置简单，无需依赖额外脚本）

4）Cobar：基于Amoeba上开发的，主要实现分库，这里不做详细介绍。

注意：一般来讲，当在程序代码中实现读写分离对代码的改动较大时，会选择是由中间代理层来实现读写分离。

实验准备

slave1：192.168.141.53/24

slave2：192.168.141.69/24

master：192.168.141.12/24

proxy：192.168.141.16/24

client：192.168.141.132/24

实现方法：基于中间代理层实现，使用mysql_proxy程序实现。

实验拓扑：

实验步骤

1、下载mysql_proxy

1）打开mysql官网www.mysql.com，找到download->MySQL Community (GPL) Downloads ，如下图

2）点击Download Archives

3）点击Mysql_Proxy

4）选择最新版本，对应通用的Linux-Generic操作系统，选择适合你的linux系统的包下载，例如我的虚拟机是64位的，就下载64位的。

2、配置proxy服务器

1）安装lua，新建代理用户

1
2
[root@proxy ~]# yum install lua -y
[root@proxy ~]# useradd -r mysql-proxy

2）安装lrzsz，将刚刚下载好的mysql_proxy压缩包上传到proxy服务器

1
2
3
4
5
6
7
8
9
10
11
[root@proxy ~]# rz

[root@proxy ~]# ls

[root@proxy ~]# tar -xf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz -C /usr/local
[root@proxy ~]# cd /usr/local
[root@proxy local]# ls

[root@proxy local]# ln -sv mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/ mysql-proxy

[root@proxy local]# ll

如下图

3、了解mysql_proxy

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[root@proxy local]# cd mysql-proxy
[root@proxy mysql-proxy]# ls bin/
mysql-binlog-dump  mysql-myisam-dump  mysql-proxy
[root@proxy mysql-proxy]# 

#新建目录，用以存放配置文件和文件夹
[root@proxy mysql-proxy]# mkdir conf logs  


#为了方便访问bin/目录下的mysql-proxy命令，运行以下脚本，运行环境变量
[root@proxy mysql-proxy]# vim /etc/profile.d/mysql-proxy.sh
[root@proxy mysql-proxy]# cat /etc/profile.d/mysql-proxy.sh
export PATH=$PATH:/usr/local/mysql-proxy/bin
[root@proxy mysql-proxy]# . /etc/profile.d/mysql-proxy.sh
[root@proxy mysql-proxy]#

注意：查找帮助加–help-all即可

1
[root@proxy mysql-proxy]# mysql-proxy --help-all

注意：若正在64位linux系统装了32位的mysql_proxy，运行mysql-proxy –help-all命令会出错，如下：

1
2
3
[root@proxy mysql-proxy]# mysql-proxy --help-all
/usr/local/mysql-proxy/bin/mysql-proxy: /usr/local/mysql-proxy/libexec/mysql-proxy: /lib/ld-linux.so.2: bad ELF interpreter: 没有那个文件或目录
/usr/local/mysql-proxy/bin/mysql-proxy:行52: /usr/local/mysql-proxy/libexec/mysql-proxy: 成功

参数解释

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
[root@proxy mysql-proxy]# mysql-proxy --help-all
Usage:
  mysql-proxy [OPTION...] - MySQL Proxy

#命令帮助提示
Help Options:
  -?, --help                                              Show help options
  --help-all                                              Show all help options
  --help-proxy                                            Show options for the proxy-module


#代理模块配置
proxy-module  
 
  #代理服务器proxy-server主机地址使用ip+端口
  -P, --proxy-address=<host:port>                         listening address:port of the proxy-server (default: :4040)
  
  #只读后端slave-server
  -r, --proxy-read-only-backend-addresses=<host:port>     address:port of the remote slave-server (default: not set)
  
  #可读可写的后端backend-servers
  -b, --proxy-backend-addresses=<host:port>               address:port of the remote backend-servers (default: 127.0.0.1:3306)
  --proxy-skip-profiling                                  disables profiling of queries (default: enabled)
  --proxy-fix-bug-25371                                   fix bug #25371 (mysqld > 5.1.12) for older libmysql versions
  
  #lua脚本路径
  -s, --proxy-lua-script=<file>                           filename of the lua script (default: not set)
  --no-proxy                                              don't start the proxy-module (default: enabled)
  --proxy-pool-no-change-user                             don't use CHANGE_USER to reset the connection coming from the pool (default: enabled)
  
  #连接超时时长
  --proxy-connect-timeout                                 connect timeout in seconds (default: 2.0 seconds)
  
  #读超时时长
  --proxy-read-timeout                                    read timeout in seconds (default: 8 hours)
  
   #写超时时长
  --proxy-write-timeout                                   write timeout in seconds (default: 8 hours)

Application Options:
  -V, --version                                           Show version
 
  #默认读取的配置文件
  --defaults-file=<file>                                  configuration file
  --verbose-shutdown                                      Always log the exit code when shutting down
  
  #以守护进程模式运行，否则运行在前台
  --daemon                                                Start in daemon-mode
  
  #刚刚我们建的用户mysql-proxy，就是为了以普通用户身份运行mysql-proxy而建立的。
  --user=<user>                                           Run mysql-proxy as user
  --basedir=<absolute path>                               Base directory to prepend to relative paths in the config
  --pid-file=<file>                                       PID file in case we are started as daemon
  
  #加载插件，某个插件或者某个目录下所有插件
  --plugin-dir=<path>                                     path to the plugins
  --plugins=<name>                                        plugins to load
  
  #日志级别
  --log-level=(error|warning|info|message|debug)          log all messages of level ... or higher
  
  #日志文件
  --log-file=<file>                                       log all messages in a file
  
  #使用sys-log
  --log-use-syslog                                        log all messages to syslog
  --log-backtrace-on-crash                                try to invoke debugger on crash
  
  #若proxy宕机了，自动尝试重启
  --keepalive                                             try to restart the proxy if it crashed
  --max-open-files                                        maximum number of open files (ulimit -n)
  --event-threads                                         number of event-handling threads (default: 1)
  --lua-path=<...>                                        set the LUA_PATH
  --lua-cpath=<...>                                       set the LUA_CPATH

关于插件

1
2
3
4
5
6
7
[root@proxy mysql-proxy]# ll lib/mysql-proxy/plugins/
总用量 232
-rwxr-xr-x. 1 7161 wheel 45824 8月  19 2014 libadmin.so
-rwxr-xr-x. 1 7161 wheel 38100 8月  19 2014 libdebug.so
-rwxr-xr-x. 1 7161 wheel 86104 8月  19 2014 libproxy.so
-rwxr-xr-x. 1 7161 wheel 56043 8月  19 2014 libreplicant.so
[root@proxy mysql-proxy]#

　　其中有一个libadmin.so管理接口比较常用，可以看看当前后端有几个服务器，这些服务器在何种状态等。使用这个管理接口：–plugins=admin

4、搭建主从复制后端架构

1
2
3
4
5
6
7
#节点
slave1：192.168.141.53/24
slave2：192.168.141.69/24
master：192.168.141.12/24

#可参考上一篇博客完成命令理解，url：
https://hexo.yfengzone.com/mysql-02/

初始配置

slave1、slave2、master上执行以下命令

1
2
3
4
5
6
yum -y install mariadb mariadb-server
systemctl restart mariadb
systemctl enable mariadb
firewall-cmd --add-port=3306/tcp --permanent 
firewall-cmd --reload
mysql_secure_installation

master

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
[root@master ~]# vim /etc/my.cnf

#找到[mysqld]段，在其中加入下面两行代码

server-id=1
log-bin=mysql-bin

[root@master ~]# systemctl restart mariadb
[root@master ~]#



[root@master ~]# mysql -u root -p
Enter password: 

MariaDB [(none)]> grant replication slave on *.* to 'tongbu'@'%' identified by '123456';
MariaDB [(none)]> show master status;

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      389 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> 



#记录下bin-log文件名（mysql-bin.000001）以及position（389）

slave1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[root@slave1 ~]# vim /etc/my.cnf

#找到[mysqld]段，在其中加入下面1行代码

server-id=2

[root@slave1 ~]# systemctl restart mariadb


[root@slave1 ~]# mysql -u root -p
Enter password: 
MariaDB [(none)]> change master to master_host = '192.168.141.12',master_user='tongbu',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=389;
MariaDB [(none)]> 
MariaDB [(none)]> slave start;
MariaDB [(none)]> show slave status\G

slave2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[root@slave2 ~]# vim /etc/my.cnf

#找到[mysqld]段，在其中加入下面1行代码

server-id=3

[root@slave2 ~]# systemctl restart mariadb


[root@slave2 ~]# mysql -u root -p
Enter password: 
MariaDB [(none)]> change master to master_host = '192.168.141.12',master_user='tongbu',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=389;
MariaDB [(none)]> 
MariaDB [(none)]> slave start;
MariaDB [(none)]> show slave status\G

两个slave的结果均如下图：

验证结果

master

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
MariaDB [(none)]> create database test charset=utf8;
Query OK, 1 row affected (0.01 sec)

MariaDB [(none)]> use test;
Database changed
MariaDB [test]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [test]> create table t (id varchar(20),name varchar(20));
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t              |
+----------------+
1 row in set (0.00 sec)

MariaDB [test]> insert into t values ("01","abcd");
Query OK, 1 row affected (0.01 sec)

MariaDB [test]> insert into t values ("02","efgh");
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> select * from t;
+------+------+
| id   | name |
+------+------+
| 01   | abcd |
| 02   | efgh |
+------+------+
2 rows in set (0.00 sec)

MariaDB [test]>

slave1、slave2上查看数据是否同步

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
MariaDB [(none)]> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [test]> select * from t;
+------+------+
| id   | name |
+------+------+
| 01   | abcd |
| 02   | efgh |
+------+------+
2 rows in set (0.00 sec)

MariaDB [test]>

5、在proxy上启动mysql-proxy

1）先回顾一下各个节点ip

1
2
3
4
5
slave1：192.168.141.53/24
slave2：192.168.141.69/24
master：192.168.141.12/24
proxy：192.168.141.16/24
client：192.168.141.132/24

2）编辑配置文件

1
2
3
4
5
6
7
8
9
10
11
12
13
[root@proxy mysql-proxy]# vim /usr/local/mysql-proxy/conf/mysql-proxy.conf
[root@proxy logs]# cat /usr/local/mysql-proxy/conf/mysql-proxy.conf
[mysql-proxy]
user=root
proxy-address=0.0.0.0:3306
proxy-backend-addresses=192.168.141.12:3306
proxy-read-only-backend-addresses=192.168.141.53:3306,192.168.141.69:3306
proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log
log-level=debug
daemon=true
keepalive=true
[root@proxy logs]#

3）提升配置文件的权限，修改lua脚本（:set nu查看行号，编辑第40、41行）

注意：rw-splitting.lua是指读写分离脚本

1
2
3
4
5
6
7
8
9
10
[root@proxy mysql-proxy]# chmod 660 conf/mysql-proxy.conf
[root@proxy mysql-proxy]# vim /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua

 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         }

　　上述命令修改了两个参数，最小连接数和最大连接数，修改后，mysql-proxy的调度机制就变成了，如果连接的客户端，超过两台，就会执行读写分离策略。

4）启动mysql-proxy（如果无法正常启动，根据报错修改配置文件）

1
2
3
4
5
[root@proxy mysql-proxy]# mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf


#验证0.0.0.0:4040已在listen状态。（netstat需要yum install net-tools后可以使用）
[root@proxy logs]# netstat -tnlp

可以看到mysql-proxy启动成功。

可以通过查看日志来确定mysql-proxy启动是否成功，以及后端服务器是否加入成功。

1
2
3
[root@proxy logs]# pwd
/usr/local/mysql-proxy/logs
[root@proxy logs]# cat mysql-proxy.log

6、在master上进行数据库授权

1
2
3
MariaDB [(none)]> use mysql;
MariaDB [mysql]> grant all on *.* to 'root'@'%' identified by '123456';
MariaDB [mysql]>

7、测试

1）在proxy放行3306端口

1
2
[root@proxy logs]# firewall-cmd --permanent --add-port=3306/tcp
[root@proxy logs]# firewall-cmd --reload

2）在client连接proxy的数据库

1
2
3
[root@client ~]# yum install mariadb -y
[root@client ~]# mysql -h 192.168.141.16 -u root -p123456
MariaDB [(none)]>

3）在proxy查看连接

1
2
[root@proxy ~]# yum install lsof -y
[root@proxy ~]# lsof -i:3306

　　如下图，数据指向是proxy->192.168.141.12，也就是由代理服务器指向了master。因此，在client连接proxy的数据库时，事实上连接到的是master的数据库。

4）在client验证，可以看到主数据库master上，我们刚刚建的表格t和插入的相关数据。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
MariaDB [(none)]> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [test]> select * from t;
+------+------+
| id   | name |
+------+------+
| 01   | abcd |
| 02   | efgh |
| 03   | hijk |
+------+------+
3 rows in set (0.00 sec)

MariaDB [test]>

5）再开一个窗口，在client上建立第二个连接

1
2
[root@client ~]# mysql -h 192.168.141.16 -u root -p123456
MariaDB [(none)]>

6）在proxy查看连接

1
[root@proxy ~]# lsof -i:3306

　　　如下图，此时由于在client上开了两个连接，因此除了proxy->master外，连接指向多了一个proxy->slave1。

7）再开一个窗口，在client上建立第三个连接

1
2
[root@client ~]# mysql -h 192.168.141.16 -u root -p123456
MariaDB [(none)]>

8）在proxy查看连接

1
[root@proxy ~]# lsof -i:3306

如下图，没啥不同的，暂时忽略。

9）再开一个窗口，在client上建立第四个连接

1
2
[root@client ~]# mysql -h 192.168.141.16 -u root -p123456
MariaDB [(none)]>

10）在proxy查看连接

1
[root@proxy ~]# lsof -i:3306

如下图，除了proxy->master、proxy->slave1外，连接指向多了一个proxy->slave2。

至此，master、slave1、slave2均用上了，一主多从模式配置成功。

验证读写分离

　　读写分离的验证，可以在slave1、slave2的数据库上分别插入不同的数据，由于主从同步是单向的，salve数据改变时，master仍然不变，我们可以利用这一特性来验证读写分离是否成功。

1）首先在master上清空原来的t表，salve1和slave2的t表也会同步被清空

master：

1
2
3
4
5
6
7
8
9
10
[root@master ~]# mysql -u root -p

MariaDB [(none)]> use test;

MariaDB [test]> delete from t;

MariaDB [test]> select * from t;
Empty set (0.00 sec)

MariaDB [test]>

slave1：

1
2
3
4
MariaDB [test]> select * from t;
Empty set (0.00 sec)

MariaDB [test]>

slave2：

1
2
3
4
MariaDB [test]> select * from t;
Empty set (0.00 sec)

MariaDB [test]>

2）接着在master、slave1、slave2上分别插入不同的语句。

1
2
3
4
5
6
7
8
master：
MariaDB [test]> insert into t values ("01","i am database");

slave1：
MariaDB [test]> insert into t values ("01","i am slave1");

slave2：
MariaDB [test]> insert into t values ("01","i am slave2");

3）然后分别在master、slave1、slave2上分别查询表t内容。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
master：
MariaDB [test]> select * from t;
+------+---------------+
| id   | name          |
+------+---------------+
| 01   | i am database |
+------+---------------+


slave1：
MariaDB [test]> select * from t;
+------+---------------+
| id   | name          |
+------+---------------+
| 01   | i am database |
| 01   | i am slave1   |
+------+---------------+



slave2：
MariaDB [test]> select * from t;
+------+---------------+
| id   | name          |
+------+---------------+
| 01   | i am database |
| 01   | i am slave2   |
+------+---------------+

　　如上，可以看到master、slave1、slave2上t表的内容各有不同，我们在client之前打开的四个窗口上分别查询t表，结果如下：

　　由上图可知，client查表，是从slave1上读取的数据，而非从master上读取的，否则就不会读取到“i am slave1”这一行内容了。

4）接下来在客户端插入一行数据

　　如上图，前一句是插入，后一句是读取。由“i am slave1”这一行内容我们可以判断出这次读取是从slave上读取的数据，那么新写入的“this is insert”究竟是写入到了slave1上，还是写入到master由slave1同步过来的呢？

结果如下图

master：

slave1：

slave2：

　　综合上面三图可知，“this is insert”这一行内容是插入到master之后，同步到两个slave的。因此，可以总结出，写操作是针对于master完成的，而读操作，是针对slave完成的，读写分离实现成功。

5）测试slave之间的切换功能

现在将slave1关机

然后在client上，查看表t的内容

　　如上图，可以看到之前client尚且还是读取slave1上的数据，此时已经读取到了slave2上的数据，说明slave之间可以正常切换。

附：重启mysql-proxy的方法

1
2
3
4
[root@proxy logs]# netstat -tnlp
[root@proxy logs]# kill 11659（这里填mysql-proxy对应PID）
[root@proxy logs]# mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf
[root@proxy ~]# cat /usr/local/mysql-proxy/logs/mysql-proxy.log

如下图，可以看到mysql-proxy重启成功。

重启后，暂时尚未出现连接

1
[root@proxy ~]# lsof -i:3306

欢迎打赏，谢谢
------ 本文结束------
0%