mysql的读写分离（mysql-proxy）

mysql-proxy简介
MySQL读写分离是指让master处理写操作，让slave处理读操作，非常适用于读操作量比较大的场景，可减轻master的压力。

使用mysql-proxy实现mysql的读写分离，mysql-proxy实际上是作为后端mysql主从服务器的代理，它直接接受客户端的请求，对SQL语句进行分析，判断出是读操作还是写操作，然后分发至对应的mysql服务器上。
　mysql-proxy是官方提供的mysql中间件产品可以实现负载平衡，读写分离，failover等

MySQL Proxy就是这么一个中间层代理，简单的说，MySQL Proxy就是一个连接池，负责将前台应用的连接请求转发给后台的数据库，并且通过使用lua脚本，可以实现复杂的连接控制和过滤，

从而实现读写分离和负载平衡。对于应用来说，MySQL Proxy是完全透明的，应用则只需要连接到MySQL Proxy的监听端口即可。

当然，这样proxy机器可能成为单点失效，但完全可以使用多个proxy机器做为冗余，在应用服务器的连接池配置中配置到多个proxy的连接参数即可。
mysql的读写分离（mysql-proxy）
从图中可以看到，SQL语句并不直接进入到master数据库或者slave数据库，而是进入到
proxy，然后proxy判断这条语句是有关写的语句（包括insert、update、delete）还
是读语句（select），当是写语句的时候，那么proxy将向master所在的服务器发出请
求，同理，如果是读语句的时候，proxy将向slave所在的服务器发出请求。
环境设定：
server1 172.25.78.11 master
server2 172.25.78.12 slave
server3 172.25.78.13 mysql-proxy
前提：必须实现主从复制
mysql的读写分离（mysql-proxy）
server1上建立用户

server2同步成功

通过编写lua脚本实现读写分离
在slave上
[[email protected] ~]# cd /usr/local/mysql-proxy/
[[email protected] mysql-proxy]# mkdir conf
[[email protected] mysql-proxy]# cd conf/
[[email protected] conf]# pwd
/usr/local/mysql-proxy/conf
[[email protected] conf]# vim ~./bash_profile
PATH= $PATH:$ HOME/bin:/usr/local/mysql-proxy/bin
[[email protected] conf]# source ~/.bash_profile
[[email protected] conf]# cd …
[[email protected] mysql-proxy]# mkdir logs
[[email protected] mysql-proxy]# cd conf/
[[email protected] conf]# vim mysql-proxy.conf
[mysql-proxy]
user=root 运行mysql-proxy用户
proxy-address=0.0.0.0:3306
proxy-backend-addresses=172.25.44.1:3306 #指定后端主master写入数据
proxy-read-only-backend-addresses=172.25.44.2:3306 #指定后端从slave读取数据
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 #定义log日志级别，由高到低分别有(error|warning|info|message|debug)
daemon=true #以守护进程方式运行
keepalive=true #mysql-proxy崩溃时，尝试重启
[[email protected] logs]# cd /usr/local/mysql-proxy/share/doc/mysql-proxy/
[[email protected] conf]# mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf
[[email protected] logs]# mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf
2018-08-14 10:23:06: (critical) mysql-proxy-cli.c:326: loading config from ‘/usr/local/mysql-proxy/conf/mysql-proxy.conf’ failed: permissions of /usr/local/mysql-proxy/conf/mysql-proxy.conf aren’t secure (0660 or stricter required)
2018-08-14 10:23:06: (message) Initiating shutdown, requested from mysql-proxy-cli.c:328
2018-08-14 10:23:06: (message) shutting down normally, exit code is: 1
[[email protected] logs]# chmod 660 /usr/local/mysql-proxy/conf/mysql-proxy.conf
[[email protected] logs]# mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf
[[email protected] conf]# ps ax
1555 ? Ss 0:00 sshd: [email protected]/1
1557 pts/1 Ss+ 0:00 -bash
1617 pts/0 R+ 0:00 ps ax
[[email protected] conf]# /etc/init.d/mysqld stop
Stopping mysqld: [ OK ]

[[email protected] conf]# ps ax
1662 ? S 0:00 /usr/local/mysql-proxy/libexec/mysql-proxy --defaults
1663 ? S 0:00 /usr/local/mysql-proxy/libexec/mysql-proxy --defaults
1664 pts/0 R+ 0:00 ps ax
[[email protected] conf]# netstat -antlp
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 1663/mysql-proxy

客户端进行远程测试
[[email protected] ~]# mysql -h 172.25.44.3 -u root -p # 远程登陆测试
MySQL [(none)]> use westos;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

如果登陆不上，就在master端进行授权

在master端插入数据
[[email protected] ~]# mysql [email protected]
mysql> insert into userlist values(‘user6’,‘666’);
Query OK, 1 row affected (0.09 sec)
看客户端是否可以成功同步
[[email protected] ~]# mysql -h 172.25.44.3 -u root -p
MySQL [westos]> select * from userlist; #数据同步成功
±---------±---------+
| username | password |
±---------±---------+
| userq | 111 |
| user2 | 222 |
| user3 | 333 |
| user5 | 555 |
| user6 | 666 |
±---------±---------+
5 rows in set (0.00 sec)
在master上进行监控
[[email protected] ~]# yum install -y lsof
[[email protected] ~]# lsof -i :3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 1248 mysql 18u IPv6 8560 0t0 TCP *:mysql (LISTEN)
mysqld 1248 mysql 48u IPv6 9242 0t0 TCP server1:mysql->server3:44918 (ESTABLISHED)
mysqld 1248 mysql 50u IPv6 9089 0t0 TCP server1:mysql->server2:37402 (ESTABLISHED)

在master上插入数据，多次远程连接，就会抓到
mysql> use westos;
mysql> insert into userlist values(‘user7’,‘777’);
Query OK, 1 row affected (0.08 sec)
mysql> insert into userlist values(‘user8’,‘888’);
Query OK, 1 row affected (0.17 sec)

[[email protected] ~]# lsof -i :3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 1248 mysql 18u IPv6 8560 0t0 TCP *:mysql (LISTEN)
mysqld 1248 mysql 48u IPv6 9242 0t0 TCP server1:mysql->server3:44918 (ESTABLISHED)
mysqld 1248 mysql 49u IPv6 9909 0t0 TCP server1:mysql->server3:44919 (ESTABLISHED)
mysqld 1248 mysql 50u IPv6 9089 0t0 TCP server1:mysql->server2:37402 (ESTABLISHED)
mysqld 1248 mysql 52u IPv6 10300 0t0 TCP server1:mysql->server3:44922 (ESTABLISHED)

#自动抓包
[[email protected] mysql-proxy]# server default db: westos
client default db:
syncronizing
server default db: westos
client default db:
syncronizing
server default db: westos
client default db:
syncronizing

[[email protected] conf]# pwd
/usr/local/mysql-proxy/conf
[[email protected] conf]# vim mysql-proxy.conf
mysql的读写分离（mysql-proxy）
[[email protected] mysql-proxy]# mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf --plugins=admin --plugins=proxy
[[email protected] mysql-proxy]# netstat -antlp #会出现4041端口
tcp 0 0 0.0.0.0:4041 0.0.0.0:* LISTEN 1753/mysql-proxy
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 1753/mysql-proxy

#如果端口没有出现，执行以下操作：
[[email protected] mysql-proxy]# killall mysql-proxy
#重新刷新
[[email protected] mysql-proxy]# mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf --plugins=admin --plugins=proxy

#远程登陆4041,，4041为管理员端口，3306为mysql-proxy与进行读写分离的两台mysql的主机进行直接通信的端口，如果使用mysql-proxy的默认端口，则必须在登陆时在后面加上 -P 4040
mysql的读写分离（mysql-proxy）
#远程登陆master和slave,必须成功登陆

#再次远程查看就会变成up

检测读写分离
[[email protected] mysql-proxy]# vim rw-splitting.lua #改变连接数，方便查看实验效果

因为在proxy读写脚本设置的最大连接数是2，而现在已经达到最大连接数
mysql的读写分离（mysql-proxy）
再次刷新连接，查看监控就会发现连接到slave端

读的数据也是slave端的数据

但是写入数据时就会写在master端

在server1上可以查看到添加的数据

帮助
mysql-proxy --help

–help-all ：获取全部帮助信息；
–admin-address=host:port ：管理模块监听的地址和端口；
–proxy-backend-addresses=host:port ：后端mysql服务器的地址和端口；
–proxy-read-only-backend-addresses=host:port ：后端只读mysql服务器的地址和端口；
–proxy-lua-script=file_name ：完成mysql代理功能的Lua脚本；
–daemon ：以守护进程模式启动mysql-proxy；
–keepalive ：在mysql-proxy崩溃时尝试重启之；
–log-file=/path/to/log_file_name ：日志文件名称；
–log-level=level ：日志级别；
–log-use-syslog ：基于syslog记录日志；
–plugins=plugin：在mysql-proxy启动时加载的插件；
–user=user_name ：运行mysql-proxy进程的用户；
–defaults-file=/path/to/conf_file_name ：默认使用的配置文件路径；其配置段使用[mysql-proxy]标识；
–proxy-skip-profiling ：禁用profile；
–pid-file=/path/to/pid_file_name ：进程文件名；

二进制日志 Binary Log
什么是 binary log
二进制日志包含 “events”。这些“events” 描述了数据库的变更，例如创建数据表的操作，或者表数据的变革。如果使用的不是基于行的日志，二进制日志同时也包含那些 “有可能”改变数据表的“events”，例如没有匹配任何的 DELETE 语句。它也包含每条语句更新数据所花费的时间。

binary log 的用途
A 复制。 master server 的 bin-log 记载了 master server 的数据变更记录，这个变更记录随后被发送给 slave servers, slave servers 执行这些变更，使得自身数据与 master server 数据一致

B 数据恢复。当一个备份被恢复后，在备份恢复后的 “events”需要重新执行
bin-log 不会记录 SELECT 和 SHOW 之类对数据没有修改的的语句
bin-log 使得服务器的性能有轻微下降，但是相对于通过它进行复制和恢复带来的好处，开启bin-log是完全值得的

开启 binary log
step1 查看是否开启
mysql> show variables like ‘%bin%’;
±----------------------------------------±---------------------+
| Variable_name | Value |
±----------------------------------------±---------------------+
| binlog_cache_size | 32768 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_format | STATEMENT |
| binlog_stmt_cache_size | 32768 |
| innodb_locks_unsafe_for_binlog | OFF |
| log_bin | OFF |
| log_bin_trust_function_creators | OFF |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| sql_log_bin | ON |
| sync_binlog | 0 |
±----------------------------------------±---------------------+
12 rows in set (0.00 sec)
step2 开启
修改 mysql 配置文件并重启服务器
log_bin = mysql_bin
或者附加 –log-bin[=base_name] 选项重启服务器
mysqld --log-bin=mysql_bin restart
step3
mysql> show variables like ‘%bin%’;
±----------------------------------------±---------------------+
| Variable_name | Value |
±----------------------------------------±---------------------+
| binlog_cache_size | 32768 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_format | STATEMENT |
| binlog_stmt_cache_size | 32768 |
| innodb_locks_unsafe_for_binlog | OFF |
| log_bin | ON |
| log_bin_trust_function_creators | OFF |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| sql_log_bin | ON |
| sync_binlog | 0 |
±----------------------------------------±---------------------+
12 rows in set (0.00 sec)
解释：
如果 base_name 缺省，默认值是 pid-file 的value 后面加上 “-bin”,例如iZ23aazo3fzZ-bin （pid-file 默认value 是机器名）。bin-log 默认目录是 data-directory,可以在 base_name 前面加上绝对路径指定其他目录
如果指定后缀名，例如 –log-bin=base_name.extension，后缀名将被忽略。

mysqld 在 base_name 后面附件一个数字后缀。服务器新建一个log file,数字后缀就会增加，形成一系列有序的log files。
服务器在以下三种情况下会创建新的log file：
a 服务器重启
b flush log
c 当前 bin-log 文件大小超出 max_binlog_size
由于事务只能记录于同一个文件，当使用一个大的事务时，bin-log 可能超出 max_binlog_size 所规定的大小
mysqld 会创建一个 bin-log 索引文件，它包含所有被使用的bin-log 名称。默认的，它的文件名是base_name加上 “.index”后缀。
可以通过 –log-bin-index[=file_name] 选项修改索引文件名称。
不要手工编辑索引文件，这会使得 mysqld 无法识别
查看bin-log
mysql> show binlog events in ‘mysql_bin.000001’\G
*************************** 1. row ***************************
Log_name: mysql_bin.000001
Pos: 4
Event_type: Format_desc
Server_id: 1
End_log_pos: 107
Info: Server ver: 5.5.40-log, Binlog ver: 4
1 row in set (0.00 sec)
step2
create table test.test_bin(
id int(10) primary key auto_increment,
name varchar(255)
);
step3
mysql> show binlog events in ‘mysql_bin.000001’\G
*************************** 1. row ***************************
Log_name: mysql_bin.000001
Pos: 4
Event_type: Format_desc
Server_id: 1
End_log_pos: 107
Info: Server ver: 5.5.40-log, Binlog ver: 4
*************************** 2. row ***************************
Log_name: mysql_bin.000001
Pos: 107
Event_type: Query
Server_id: 1
End_log_pos: 266
Info: use test; create table test.test_bin(
id int(10) primary key auto_increment,
name varchar(255)
)
2 rows in set (0.00 sec)
step4
insert into test.test_bin(name) values (‘orange’);
step5
mysql> show binlog events in ‘mysql_bin.000001’\G
*************************** 1. row ***************************
Log_name: mysql_bin.000001
Pos: 4
Event_type: Format_desc
Server_id: 1
End_log_pos: 107
Info: Server ver: 5.5.40-log, Binlog ver: 4
*************************** 2. row ***************************
Log_name: mysql_bin.000001
Pos: 107
Event_type: Query
Server_id: 1
End_log_pos: 266
Info: use test; create table test.test_bin(
id int(10) primary key auto_increment,
name varchar(255)
)
*************************** 3. row ***************************
Log_name: mysql_bin.000001
Pos: 266
Event_type: Query
Server_id: 1
End_log_pos: 334
Info: BEGIN
*************************** 4. row ***************************
Log_name: mysql_bin.000001
Pos: 334
Event_type: Intvar
Server_id: 1
End_log_pos: 362
Info: INSERT_ID=1
*************************** 5. row ***************************
Log_name: mysql_bin.000001
Pos: 362
Event_type: Query
Server_id: 1
End_log_pos: 474
Info: use test; insert into test.test_bin(name) values (‘orange’)
*************************** 6. row ***************************
Log_name: mysql_bin.000001
Pos: 474
Event_type: Query
Server_id: 1
End_log_pos: 543
Info: COMMIT
6 rows in set (0.00 sec)
解释：
Log_name: 此条log存在那个文件中
Pos: log在bin-log中的开始位置
Event_type: log的类型信息
Server_id: 可以查看配置中的server_id,表示log是那个服务器产生
End_log_pos：log在bin-log中的结束位置
Info: log的一些备注信息，可以直观的看出进行了什么操作
相关参数
mysql> show variables like ‘%bin%’;
±----------------------------------------±---------------------+
| Variable_name | Value |
±----------------------------------------±---------------------+
| binlog_cache_size | 32768 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_format | STATEMENT |
| binlog_stmt_cache_size | 32768 |
| innodb_locks_unsafe_for_binlog | OFF |
| log_bin | ON |
| log_bin_trust_function_creators | OFF |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| sql_log_bin | ON |
| sync_binlog | 0 |
±----------------------------------------±---------------------+
12 rows in set (0.00 sec)
log_bin_index
设置此参数是指定二进制索引文件的路径与名称

binlog_do_db
此参数表示只记录指定数据库的二进制日志

binlog_ignore_db
此参数表示不记录指定的数据库的二进制日志

max_binlog_cache_size
此参数表示binlog使用的内存最大的尺寸

binlog_cache_size
此参数表示binlog使用的内存大小，可以通过状态变量binlog_cache_use和binlog_cache_disk_use来帮助测试。

binlog_cache_use
使用二进制日志缓存的事务数量

binlog_cache_disk_use
使用二进制日志缓存但超过binlog_cache_size值并使用临时文件来保存事务中的语句的事务数量
max_binlog_size
Binlog最大值，最大和默认值是1GB，该设置并不能严格控制Binlog的大小，尤其是Binlog比较靠近最大值而又遇到一个比较大事务时，为了保证事务的完整性，不可能做切换日志的动作，只能将该事务的所有SQL都记录进当前日志，直到事务结束

sync_binlog
这个参数直接影响mysql的性能和完整性
sync_binlog=0：
当事务提交后，Mysql仅仅是将binlog_cache中的数据写入Binlog文件，但不执行fsync之类的磁盘同步指令通知文件系统将缓存刷新到磁盘，而让Filesystem自行决定什么时候来做同步，这个是性能最好的。

sync_binlog=n，在进行n次事务提交以后，Mysql将执行一次fsync之类的磁盘同步指令，通知文件系统将Binlog文件缓存刷新到磁盘。

Mysql中默认的设置是sync_binlog=0，即不作任何强制性的磁盘刷新指令，这时性能是最好的，但风险也是最大的。一旦系统绷Crash，在文件系统缓存中的所有Binlog信息都会丢失
bin_log 操作

a1 删除bin-log
方法一：设置日志保留时长expire_logs_days自动删除
step1 查看当前日志保存天数 (默认是0，也就是logs不过期，可通过设置全局的参数，使他临时生效)
mysql> show variables like ‘expire_logs_days’;
±-----------------±------+
| Variable_name | Value |
±-----------------±------+
| expire_logs_days | 0 |
±-----------------±------+
1 row in set (0.00 sec)
step2 设置了只保留7天BINLOG, 下次重启mysql这个参数默认会失败，所以需在my.cnf中设置
set global expire_logs_days=7;
方法二：手动删除BINLOG (purge binary logs)
reset {MASTER | SLAVE}
PURGE {MASTER | BINARY} LOGS TO ‘log_name’
PURGE {MASTER | BINARY} LOGS BEFORE ‘date’
例如
mysql> reset master; //删除master的binlog
mysql> reset slave; //删除slave的中继日志
mysql> purge master logs before ‘2012-03-30 17:20:00’; //删除指定日期以前的日志索引中binlog日志文件
mysql> purge master logs to ‘binlog.000002’; //删除指定日志文件的日志索引中binlog日志文件
方法三：用操作系统命令直接删除
a2 查看最后一个bin日志文件是那个
mysql> show master status;
±-----------------±---------±-------------±-----------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
±-----------------±---------±-------------±-----------------+
| mysql_bin.000002 | 107 | | |
±-----------------±---------±-------------±-----------------+
1 row in set (0.00 sec)
a3 启用新的日志文件,一般备份完数据库后执行
mysql> flush logs;
Query OK, 0 rows affected (0.05 sec)

mysql> show master status;
±-----------------±---------±-------------±-----------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
±-----------------±---------±-------------±-----------------+
| mysql_bin.000003 | 107 | | |
±-----------------±---------±-------------±-----------------+
1 row in set (0.00 sec)
a4 如果用户有super权限，可以启用或禁用当前会话的binlog记录
mysql> set sql_log_bin=1/0;
a5 查看master的binlog日志
mysql> show master logs;
或者
mysql> show binary logs;
a6 显示当前注册的slave的列表。不以–report-host=slave_name选项为开头的slave不会显示在本列表中
mysql> show slave hosts;
bin-log 三种格式

Mysql binlog日志有三种格式，分别为Statement,MiXED,以及ROW

a Statement：每一条会修改数据的sql都会记录在binlog中。

优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。(相比row能节约多少性能与日志量，这个取决于应用的SQL情况，正常同一条记录修改或者插入row格式所产生的日志量还小于Statement产生的日志量，但是考虑到如果带条件的update操作，以及整表删除，alter表等操作，ROW格式会产生大量日志，因此在考虑是否使用ROW格式日志时应该跟据应用的实际情况，其所产生的日志量会增加多少，以及带来的IO性能问题。)

缺点：由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行时候相同的结果。另外mysql 的复制,像一些特定函数功能，slave可与master上要保持一致会有很多相关问题(如sleep()函数， last_insert_id()，以及user-defined functions(udf)会出现问题).
使用以下函数的语句也无法被复制：

LOAD_FILE()

UUID()

USER()

FOUND_ROWS()

SYSDATE() (除非启动时启用了 –sysdate-is-now 选项)

同时在INSERT …SELECT 会产生比 RBR 更多的行级锁

b Row:不记录sql语句上下文相关信息，仅保存哪条记录被修改。

优点： binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题

缺点:所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容,比如一条update语句，修改多条记录，则binlog中每一条修改都会有记录，这样造成binlog日志量会很大，特别是当执行alter table之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中。
c Mixedlevel: 是以上两种level的混合使用，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种.新版本的MySQL中队row level模式也被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录。至于update或者delete等修改数据的语句，还是会记录所有行的变更。

Mysql默认是使用Statement日志格式，推荐使用MIXED.
由于一些特殊使用，可以考虑使用ROWED，如自己通过binlog日志来同步数据的修改，这样会节省很多相关操作。对于binlog数据处理会变得非常轻松,相对mixed，解析也会很轻松(当然前提是增加的日志量所带来的IO开销在容忍的范围内即可)。
【参考】http://dev.mysql.com/doc/refman/5.7/en/binary-log.html

mysql的读写分离（mysql-proxy）

相关推荐