mysql主从复制读写分离

mysql主从复制读写分离

一、MYSQL读写分离的概述

Mysql作为目前世界上使用最广泛的免费数据库，相信所有从事系统运维的工程师都一定接触过。但在实际的生产环境中，由单台Mysql作为独立的数据库是完全不能满足实际需求的，无论是在安全性，高可用性以及高并发等各个方面。
因此，一般来说都是通过 主从复制（Master-Slave）的方式来同步数据，再通过读写分离（MySQL-Proxy/Amoeba）来提升数据库的并发负载能力 这样的方案来进行部署与实施的。
读写分离工作原理：
基本的原理是让主数据库处理事务性增、改、删操作（INSERT、UPDATE、DELETE），而从数据库处理SELECT查询操作。数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。

数据内部交换过程：

为什么要读写分离：

面对越来越大的访问压力，单台的服务器的性能成为瓶颈，需要分担负载

1、        主从只负责各自的写和读，极大程度的缓解X锁和S锁争用

2、        从库可配置myisam引擎，提升查询性能以及节约系统开销

3、        增加冗余，提高可用性

实现读写分离的方式：

一般有两种方式实现

应用程序层实现，网站的程序实现

应用程序层实现指的是在应用程序内部及连接器中实现读写分离

优点：
A：应用程序内部实现读写分离，安装既可以使用
B：减少一定部署难度
C：访问压力在一定级别以下，性能很好
缺点：
A：架构一旦调整，代码要跟着变
B：难以实现高级应用，如自动分库，分表
C：无法适用大型应用场景
中间件层实现
中间件层实现是指在外部中间件程序实现读写分离
常见的中间件程序：
Mysql-proxy    amoeba    Atlas (360)  Cobar(Alibaba)  TDDL(Taobao)
优点：
A：架构设计更灵活
B：可以在程序上实现一些高级控制，如：透明化水平拆分，failover,监控
C：可以依靠些技术手段提高mysql性能，
D：对业务代码的影响小，同时也安全
缺点：
需要一定的开发运维团队的支持

MYSQL-PROXY概述

MySQL Proxy是一个处于你的client端和MySQL server端之间的简单程序，它可以监测、分析或改变它们的通信。它使用灵活，没有限制，常见的用途包括：负载平衡，故障、查询分析，查询过滤和修改等等。
MySQL Proxy就是这么一个中间层代理，简单的说，MySQL Proxy就是一个连接池，负责将前台应用的连接请求转发给后台的数据库，并且通过使用lua脚本，可以实现复杂的连接控制和过滤，从而实现读写分离和负载平衡。对于应用来说，MySQL Proxy是完全透明的，应用则只需要连接到MySQL Proxy的监听端口即可。当然，这样proxy机器可能成为单点失效，但完全可以使用多个proxy机器做为冗余，在应用服务器的连接池配置中配置到多个proxy的连接参数即可。

MySQL Proxy更强大的一项功能是实现“读写分离”，基本原理是让主数据库处理事务性查询，让从库处理SELECT查询。数据库复制被用来把事务性查询导致的变更同步到集群中的从库


Lua概述：
Lua 是一个小巧的脚本语言。
Lua的速度是最快的。这一切都决定了Lua是作为嵌入式脚本的最佳选择。
Lua由标准C编写而成，几乎在所有操作系统和平台上都可以编译，运行。Lua并没有提供强大的库，这是由它的定位决定的。所以Lua不适合作为开发独立应用程序的语言。
lua官网：http://www.lua.org/
下载: mysql-proxy
http://dev.mysql.com/downloads/mysql-proxy/

二、安装环境

系统环境：CentOS release 6.9 64bit  
软件名称：mysql-5.7.18

软件用户：mysql

软件安装位置：/usr/bin/、/usr/share/、/usr/local/mysql-proxy

数据存放位置：/var/lib/mysql

日志存放位置：/var/log/mysqld.log

三、主从设计
首先设计主从的安装分配方式，共需要3台服务器，服务器分配如下：

    master节点：        192.168.99.35
    mysql-proxy节点：    192.168.99.36
    slave节点：           192.168.99.37



四、安装部署

1、三台服务器在线安装mysql

root@centos-6 mysql]# rpm –Uvh https://repo.mysql.com//mysql57-community-release-el6-11.noarch.rpm

root@centos-6 mysql]# yum -y install mysql-community-server mysql-community-client mysql-community-common mysql-community-libs

网速慢可以先下载rpm包本地rpm或yum安装

[root@centos-6 home]# mkdir /home/mysql/

[root@centos-6 home]# cd /home/mysql/

[root@centos-6 mysql]# ls

mysql-community-client-5.7.18-1.el6.x86_64.rpm

mysql-community-common-5.7.18-1.el6.x86_64.rpm

mysql-community-libs-5.7.18-1.el6.x86_64.rpm

mysql-community-server-5.7.18-1.el6.x86_64.rpm

root@centos-6 mysql]# yum -y install mysql-community-server mysql-community-client mysql-community-common mysql-community-libs

2、修改密码策略配置简单密码便于测试

第一次通过#grep "password" /var/log/mysqld.log 命令获取MySQL的临时密码

用该密码登录到服务端后，必须马上修改密码，不然操作查询时报错误

刚开始设置的密码必须符合长度，且必须含有数字，小写或大写字母，特殊字符。

如果想设置简单密码，如下操作：

首先，修改validate_password_policy参数的值

[root@centos-6 mysql]# grep "password" /var/log/mysqld.log 

2017-05-28T23:15:52.739913Z 1 [Note] A temporary password is generated for root@localhost: xnzb:ff-h2G_

mysql> set global validate_password_policy=0;  #定义复杂度

mysql> set global validate_password_length=1;  #定义长度 默认是8

mysql>set password for 'root'@'localhost'=password('123456');

通过my.cnf 配置文件设置密码策略的级别

validate_password_policy=2

最后一行 validate_password_policy 设置mysql启动的时候密码策略级别。 如果设置为3 ，那么需要指定字典文件。

当然你也可以通过 my.cnf 配置文件关闭 validate_password 插件。只需要添加一行 

validate_password = off

MySQL 新版本默认监听在IPv6的地址族上。

更改为监听IPv4地址族，修改 my.cnf 添加一行配置：

bind-address = 0.0.0.0

3、在99.36上安装lua

[root@Centos-6-99 ~]# yum -y install lua

4、在99.36上安装mysql-proxy

推荐采用已经编译好的二进制版本，因为采用源码包进行编译时，最新版的MySQL-Proxy对automake，glib以及libevent的版本都有很高的要求，而这些软件包都是系统的基础套件，不建议强行进行更新。并且这些已经编译好的二进制版本在解压后都在统一的目录内，因此建议选择以下版本：

1）下载安装mysql-proxy

[root@Centos-6-99 home]# wget http://dev.mysql.com/get/Downloads/MySQL-Proxy/mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz

[root@Centos-6-99 mysql-proxy]# tar -xvf 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 && cd /usr/local/mysql-proxy

2）修改系统环境变量

vim /etc/profile

export PATH=/usr/local/mysql-proxy/bin/:/usr/local/mysql/bin:$PATH    //增加这个变量

[root@xuegod62 local]# source !$    //使系统环境变量生效

source /etc/profile

3）修改mysql-proxy配置文件实现读写分离

vim /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua

将默认配置

min_idle_connections = 4,

max_idle_connections = 8,

修改为

min_idle_connections = 1,

max_idle_connections = 8,

修改默认连接，进行快速测试，默认最小4个以上的客户端连接才会实现读写分离,最大链接数为8。 
注：为了验证试验效果将他改成1 .就是当有一个链接的时候，就实现读写分离的功能。

4）分别在99.35和99.37上创建数据库和表，分别用于实现写和读操作

mysql -uroot -p123456

mysql> create database db;

mysql> use db;

mysql> create table test(id int);

mysql> insert into test values(35);

mysql> grant all on db.* to user1@'%' identified by '123456';

mysql>flush privileges;

mysql -uroot -p123456

mysql> create database db;

mysql> use db;

mysql> create table test(id int);

mysql> insert into test values(37);

mysql> grant all on db.* to user1@'%' identified by '123456';

mysql>flush privileges;

5）在99.36启动MYSQL-PROXY服务

[root@Centos-6-99 local]# mysql-proxy --proxy-read-only-backend-addresses=192.168.99.37:3306 --proxy-backend-addresses=192.168.99.35:3306 --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua &

[1] 2551

[root@Centos-6-99 local]# 2017-06-11 10:34:09: (critical) plugin proxy 0.8.5 started

6）参数说明

--proxy-read-only-backend-addresses=192.168.99.37:3306  # 定义后端只读服务器 
--proxy-backend-addresses=192.168.99.35:3306   #定义后端mysql主服务器地址，指定mysql写主服务器的端口

--proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua &  #指定lua脚本，在这里，使用的是rw-splitting脚本，用于读写分离

当有多个只读服务器时，可以写多个以下参数：

当有多个只读服务器时，可以写多个以下参数：
--proxy-read-only-backend-addresses=192.168.1.64:3306  # 定义后端只读服务器
--proxy-read-only-backend-addresses=192.168.1.65:3306  # 定义后端只读服务器
#--proxy-address=192.168.1.62:3307 指定mysql proxy的监听端口，默认为：4040

完整的参数可以运行以下命令查看

mysql-proxy --help-all

7）查看mysql-proxy是否启动

[root@Centos-6-99 local]# lsof -i :4040

COMMAND    PID USER   FD   TYPE DEVICE SIZE/OFF NODE NAME

mysql-pro 3144 root    9u  IPv4  19597      0t0  TCP *:yo-main (LISTEN)

8）测试读写分离

第一台服务器登录99.36查询

[root@localhost ~]# mysql -uuser1 -p123456 -P4040 -h292.168.99.36

mysql> use db;

Database changed

mysql> select * from test;

+------+

| id   |

+------+

|   35 |

+------+

1 row in set (0.00 sec)

mysql> 

插入后再进行查询

mysql> insert into test values(36);

Query OK, 1 row affected (0.04 sec)

mysql> select * from test;

+------+

| id   |

+------+

|   35 |

|   36 |

+------+

1 row in set (0.00 sec)

mysql> 

第二台服务器登录99.36查询

[root@localhost ~]# mysql -uuser1 -p123456 -P4040 -h292.168.99.36

mysql> use db;

Database changed

mysql> select * from test;

+------+

| id   |

+------+

|   37 |

+------+

1 row in set (0.00 sec)

mysql>

说明读写分离已成功；

9）查看客户端理解状态

mysql> show processlist;

+----+-------+---------------------+------+---------+------+----------+------------------+

| Id | User  | Host                | db   | Command | Time | State    | Info             |

+----+-------+---------------------+------+---------+------+----------+------------------+

|  5 | user1 | 192.168.99.36:45314 | db   | Sleep   |    2 |          | NULL             |

|  6 | user1 | 192.168.99.36:45316 | db   | Query   |    0 | starting | show processlist |

+----+-------+---------------------+------+---------+------+----------+------------------+

2 rows in set (0.00 sec)

mysql> 

10）状态参数说明；

每列参数说明：
第一列， id ，一个标识 
user列， 显示当前用户，如果不是 root ，这个命令就只显示你权限范围内的 sql 语 句。 
host 列，显示这个语句是从哪个 ip 的哪 个端口上发出的。可以用来追踪出问题语句的用户。 
db 列，显示这个进程目前连接的是哪个数据库 。 
command 列，显示当前连接的执行的命令，一般就是休眠（ sleep ），查询（ query ），连接（ connect ）。 
time 列，此这个状态持续的时间，单位是秒。 
state 列，显示使用当前连接的 sql 语句的状态，很重要的列， state 只是语句执行中的某一个状态，一个 sql 语句，以查询为例，可能需要经过 copying to tmp table ，Sorting result ， Sending data 等状态才可以完成。 

info 列，显示这个 sql 语句，因为长度有限，所以长的 sql 语句就显示不全，但是一个判断问题语句的重要依据。

5、在99.35配置mysql master并在99.37配置mysql slave实现主从复制

1）配置主服务器配置文件，启用log-bin功能

[root@centos-6 mysql]#vim /etc/my.cnf

log-bin=mysql-bin-master  #启用二进制日志

server-id=1   #本机数据库ID 标示

binlog-do-db=db #可以被从服务器复制的库。二进制需要同步的数据库名

binlog-ignore-db=mysql  #不可以被从服务器复制的库

2）授权允许slave 37访问35

mysql> grant replication slave on *.* to slave@192.168.99.37  identified by "123456"; 

Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

查看mster状态

mysql> show master status;

+-------------------------+----------+--------------+------------------+-------------------+

| File                    | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+-------------------------+----------+--------------+------------------+-------------------+

| mysql-bin-master.000001 |      154 | db           | mysql            |                   |

+-------------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

3）配置服务器37为slave

[root@Centos-6-99 ~]# vim /etc/my.cnf

server-id=2   #本机数据库ID 标示

4）配置37根据master变化

mysql -uroot -p123456

mysql> change master to master_host='192.168.99.35',master_user='slave',master_password='123456';

mysql> flush privileges;

5）启动slave并查看状态

mysql> start slave;

Query OK, 0 rows affected (0.01 sec)

mysql> show slave status \G

6）在35上插入数据

mysql -uroot -p123456

mysql> use db;

Database changed

mysql> insert into test values(36);

Query OK, 1 row affected (0.08 sec)

mysql> select * from test;

+------+

| id   |

+------+

|   35 |

|   36 |

+------+

2 rows in set (0.00 sec)

mysql> 

7）在37上进行查看

[root@Centos-6-99 ~]# mysql -u root -p123456

mysql> use db;

Database changed

mysql> select * from test;

+------+

| id   |

+------+

|   37 |

|   36 |

+------+

2 rows in set (0.00 sec)

mysql> 

可以看到新插入的36已同步过去；

8）外部登录99.36查询也可以看到分别的主备数据已同步

[root@localhost ~]# mysql -uuser1 -p123456 -P4040 -h292.168.99.36

mysql>use db;

mysql> select * from test;

+------+

| id   |

+------+

|   35 |

|   36 |

+------+

2 rows in set (0.00 sec)

mysql> 

另外一台服务器登录99.36的查询情况

[root@localhost ~]# mysql -uuser1 -p123456 -P4040 -h292.168.99.36

mysql> use db;

Database changed

mysql> select * from test;

+------+

| id   |

+------+

|   37 |

|   36 |

+------+

2 rows in set (0.00 sec)

9）外部登录99.36插入及查询情况

[root@localhost ~]# mysql -uuser1 -p123456 -P4040 -h292.168.99.36

mysql> use db;

Database changed

mysql> insert into test values(100);

Query OK, 1 row affected (0.07 sec)

mysql> insert into test values(101);

Query OK, 1 row affected (0.08 sec)

mysql> select * from test;

+------+

| id   |

+------+

|   37 |

|   36 |

|  100 |

|  101 |

+------+

4 rows in set (0.00 sec)

mysql> 

在99.35上查询确认数据已插入并且同步；

mysql> select * from test;

+------+

| id   |

+------+

|   35 |

|   36 |

|  100 |

|  101 |

+------+

4 rows in set (0.00 sec)

mysql> 

10）解决插入的35和37不一致问题

在99.35上禁用同步功能

[root@centos-6 mysql]# vim /etc/my.cnf

#log-bin=mysql-bin-master  #不启用二进制日志

重启mysql

[root@centos-6 mysql]# /etc/init.d/mysqld restart

Stopping mysqld:                                           [  OK  ]

Starting mysqld:                                           [  OK  ]

删除35

[root@centos-6 mysql]# mysql -u root -p123456

mysql> delete from test where id=35;

Query OK, 1 row affected (0.06 sec)

mysql> select * from test;

+------+

| id   |

+------+

|   36 |

|  100 |

|  101 |

+------+

3 rows in set (0.00 sec)

在99.37上禁用salve并删除37

mysql> stop slave;

Query OK, 0 rows affected (0.02 sec)

mysql> delete from test where id=37;

Query OK, 1 row affected (0.04 sec)

mysql> select * from test;

+------+

| id   |

+------+

|   36 |

|  100 |

|  101 |

+------+

3 rows in set (0.00 sec)

重新启动同步功能即可

在99.35上启用同步功能

[root@centos-6 mysql]# vim /etc/my.cnf

log-bin=mysql-bin-master  #启用二进制日志

重启mysql

[root@centos-6 mysql]# /etc/init.d/mysqld restart

Stopping mysqld:                                           [  OK  ]

Starting mysqld:                                           [  OK  ]

在99.37上启动slave即可

mysql> stop slave;

外部插入和查询即可正常