一、MySQL 主从复制的几种方案
数据库读写分离对于大型系统或者访问量很高的互联网应用来说,是必不可少的一个重要功能。从数据库的角度来说,对于大多数应用来说,从集中到分布,最基本的一个需求不是数据存储的瓶颈,而是在于计算的瓶颈,即 SQL 查询的瓶颈,我们知道,正常情况下,Insert SQL 就是几十个毫秒的时间内写入完成,而系统中的大多数 Select SQL 则要几秒到几分钟才能有结果,很多复杂的 SQL,其消耗服务器 CPU 的能力超强,不亚于死循环的威力。在没有读写分离的系统上,很可能高峰时段的一些复杂 SQL 查询就导致数据库服务器 CPU爆表,系统陷入瘫痪,严重情况下可能导致数据库崩溃。因此,从保护数据库的角度来说,我们应该尽量避免没有主从复制机制的单节点数据库。
对于 MySQL 来说,标准的读写分离是主从模式,一个写节点 Master 后面跟着多个读节点,读节点的数量取决于系统的压力,通常是 1-3 个读节点的配置,如下图所示:
二、主从复制+读写分离
大型的电子商务系统一般都会使用一个Master数据库,多个Slave数据库的组合实现读写分离技术。Master库负责数据更新和实时数据查询,Slave库负责非实时数据查询。因为在实际的应用中,数据库都是读多写少(读取数据的频率高,更新数据的频率相对较少),而读取数据通常耗时比较长,占用数据库服务器的CPU较多,从而影响用户体验。我们通常的做法就是把查询从主库中抽取出来,采用多个从库,使用负载均衡,减轻每个从库的查询压力。
采用读写分离技术的目标:有效减轻Master库的压力,又可以把用户查询数据的请求分发到不同的Slave库,从而保证系统的健壮性。我们看下采用读写分离的过程:
客户端通过master对数据库进行写操作,slave端进行读操作,并且具有主从复制,即slave会备份master的数据。这样读在slave端,写在master端,实现了读写分离,减轻了master的压力,提高并发负载。
三、mysql-proxy实现读写分离
1.环境描述:
- 操作系统:Red Hat Enterprise Linux Server release 6.5 (Santiago)
- 主服务器Master:172.25.20.2
- 从服务器Slave:172.25.20.3
- 调度服务器MySQL-Proxy:172.25.20.4
- 测试客户端 client:172.25.20.5(mysql)
- mysql主从复制
2.安装mysql-proxy
在调度服务器(server4)上安装
实现读写分离是有lua脚本实现的,现在mysql-proxy里面已经集成,无需再安装
官网下载地址:https://downloads.mysql.com/archives/get/file/mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
可找你需要的版本从:http://dev.mysql.com/downloads/mysql-proxy/
[root@server4 ~]# wget https://downloads.mysql.com/archives/get/file/mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
[root@server4 ~]# tar -zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
[root@server4 ~]# mv mysql-proxy-0.8.5-linux-el6-x86-64bit /usr/local/mysql-proxy
3、配置mysql-proxy,创建主配置文件
[root@server4 ~]# cd /usr/local/mysql-proxy/
[root@server4 mysql-proxy]# mkdir lua
[root@server4 mysql-proxy]# mkdir logs
[root@server4 mysql-proxy]# cp share/doc/mysql-proxy/rw-splitting.lua ./lua/
[root@server4 mysql-proxy]# cp share/doc/mysql-proxy/admin-sql.lua ./lua
[root@server4 mysql-proxy]# vim /etc/mysql-proxy.cnf
[mysql-proxy]
user=root
admin-username=proxy
admin-password=123.com
proxy-address=172.25.20.4:4000
proxy-read-only-backend-addresses=172.25.20.3
proxy-backend-addresses=172.25.20.2
proxy-lua-script=/usr/local/mysql-proxy/lua/rw-splitting.lua
admin-lua-script=/usr/local/mysql-proxy/lua/admin-sql.lua
log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log
log-level=info
daemon=true
keepalive=true
[root@server4 mysql-proxy]# chmod 660 /etc/mysql-proxy.cnf
4.修改读写分离配置文件
[root@server4 mysql-proxy]# vim /usr/local/mysql-proxy/lua/rw-splitting.lua
if not proxy.global.config.rwsplit then
proxy.global.config.rwsplit = {
-- min_idle_connections = 4,
-- max_idle_connections = 8,
min_idle_connections = 1,
max_idle_connections = 1,
is_debug = false
}
end
5.启动mysql-proxy
[root@server4 mysql-proxy]# /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf
[root@server4 mysql-proxy]# netstat -tupln | grep 4000
tcp 0 0 172.25.20.4:4000 0.0.0.0:* LISTEN 1091/mysql-proxy
##关闭mysql-proxy使用:killall -9 mysql-proxy
6.测试读写分离
- 在主服务器master(server2)上创建proxy用户用于mysql-proxy使用,从服务器也会同步这个操作
mysql> grant all on *.* to 'myproxy'@'172.25.20.4' identified by '1234+asDF';
- 使用客户端(server5)连接mysql-proxy
[root@server5 ~]# mysql -u myproxy -h 172.25.20.4 -P 4000 -p1234+asDF
- 创建数据库和表,这时的数据只写入主mysql,然后再同步从slave,可以先把slave的关了,看能不能写入,这里我就不测试了,下面测试下读的数据!
##先查看下客户端和master及slave数据库的内容,应该是相同的
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> SELECT * FROM test.usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 111 |
+----------+----------+
1 row in set (0.02 sec)
接下来进行写入数据测试
##客户端
mysql> CREATE DATABASE myuser;
Query OK, 1 row affected (0.00 sec)
mysql> USE myuser;
Database changed
mysql> CREATE TABLE user (number INT(10),name VARCHAR(255));
Query OK, 0 rows affected (0.08 sec)
mysql> insert into test values(01,'user1');
ERROR 1146 (42S02): Table 'myuser.test' doesn't exist
mysql> insert into user values(01,'user1');
Query OK, 1 row affected (0.00 sec)
mysql> insert into user values(02,'user2');
Query OK, 1 row affected (0.01 sec)
- 登陆主从mysq查看新写入的数据如下,
mysql> CREATE DATABASE myuser;
mysql> USE myuser;
mysql> CREATE TABLE user (number INT(10),name VARCHAR(255));
mysql> insert into user values(01,'user1');
mysql> insert into user values(02,'user2');
分别在master和slave上查看:
##master
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| myuser |
| performance_schema |
| sys |
| test |
+--------------------+
6 rows in set (0.00 sec)
mysql> SELECT * FROM myuser.user;
+--------+-------+
| number | name |
+--------+-------+
| 1 | user1 |
| 2 | user2 |
+--------+-------+
2 rows in set (0.00 sec)
##slave
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| myuser |
| performance_schema |
| sys |
| test |
+--------------------+
6 rows in set (0.00 sec)
mysql> SELECT * FROM myuser.user;
+--------+-------+
| number | name |
+--------+-------+
| 1 | user1 |
| 2 | user2 |
+--------+-------+
2 rows in set (0.00 sec)
好像数据没有同步到slave上,查看master的状态
mysql> show master status ;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000003 | 1100 | test | mysql | 42989d0d-a446-11e7-8d8d-525400140b3d:1-4 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
问题明白了,只同步test库的内容,我们修改配置似乎有点麻烦,那就在test库里作实验吧
##客户端(server5)
mysql> USE test;
mysql> CREATE TABLE user (number INT(10),name VARCHAR(255));
mysql> insert into user values(01,'user1');
mysql> insert into user values(02,'user2');
##master(server2)上
mysql> SELECT * FROM test.user;
+--------+-------+
| number | name |
+--------+-------+
| 1 | user1 |
| 2 | user2 |
+--------+-------+
2 rows in set (0.00 sec)
##slave(server3)上
mysql> SELECT * FROM test.user;
+--------+-------+
| number | name |
+--------+-------+
| 1 | user1 |
| 2 | user2 |
+--------+-------+
2 rows in set (0.00 sec)
数据同步成功,顺便在客户端上查询下我们刚才写入的数据
mysql> SELECT * FROM test.user;
+--------+-------+
| number | name |
+--------+-------+
| 1 | user1 |
| 2 | user2 |
+--------+-------+
2 rows in set (0.00 sec)
顺便看看调度服务器(server4)的状态:
刚好还可以做个测试,由于我们刚才创建的数据库myuser和库里面的内容写入到了master上,但是没有同步到slave上,那么我们在客户端查询myuser数据库里面的内容,如果可以查询到的话,就说明查询也是走的master,那么我们的读写分离就没有成功,相反,如果查询不到,那么就可以说明查询走的是slave,而写入走的是master,读写分离配置成功。接下来我们一起见证奇迹:
mysql> SELECT * FROM myuser.user;
ERROR 1146 (42S02): Table 'myuser.user' doesn't exist
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| myuser |
| performance_schema |
| sys |
| test |
+--------------------+
6 rows in set (0.00 sec)
果然如我们所料,查询不到,说明我们的读写分离配置成功了
- 换一台客户端再登陆到mysql-proxy,查询数据,结果一样,test库里的内容能正常查询,myuser库里的内容不可查询,说明我们配置成功,真正实现了读写分离的效果!
[root@foundation20 ~]# mysql -u myproxy -h 172.25.20.4 -P 4000 -p1234+asDF
mysql> SELECT * FROM myuser.user;
ERROR 1146 (42S02): Table 'myuser.user' doesn't exist
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> SELECT * FROM test.user;
+--------+-------+
| number | name |
+--------+-------+
| 1 | user1 |
| 2 | user2 |
+--------+-------+
2 rows in set (0.00 sec)