MySQL做读写分离提高性能缓解数据库压力（mysql 读写分离 分库分表）全程干货

目录一 什么是读写分离二 读写分离的好处三 读写分离提高性能之原因四 读写分离示意图五 读写分离模拟

serv08?mysql>?grant?all?on?*.*?to?’larry’@’192.168.1.%’?identified?by?’larry’;?Query?OK,?0?rows?affected?(0.00?sec)?serv09?mysql>?grant?all?on?*.*?to?’larry’@’192.168.1.%’?identified?by?’larry’;?Query?OK,?0?rows?affected?(0.00?sec)?

serv09?mysql>?change?master?to?master_host=’192.168.1.18′,?master_user=’larry’,?master_password=’larry’,?master_port=3306,?master_log_file=’mysql-bin.000001′,?master_log_pos=107;?
Query?OK,?0?rows?affected?(0.01?sec)?
mysql>?start?slave;?
Query?OK,?0?rows?affected?(0.00?sec)?
mysql>?show?slave?status?\G;?***************************?1.?row?***************************?Slave_IO_State:?Waiting?for?master?to?send?event?Master_Host:?192.168.1.18?Master_User:?larry?Master_Port:?3306?Connect_Retry:?60?Master_Log_File:?mysql-bin.000001?Read_Master_Log_Pos:?107?Relay_Log_File:?serv09-relay-bin.000002?Relay_Log_Pos:?253?Relay_Master_Log_File:?mysql-bin.000001Slave_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:?107?Relay_Log_Space:?410?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:?2?1?row?in?set?(0.00?sec)?ERROR:?No?query?specified?mysql>?select?user,password,host?from?mysql.user;?+——+——————————————-+—————–+?|?user?|?password?|?host?|?+——+——————————————-+—————–+?|?root?|?|?localhost?|?|?root?|?|?serv08.host.com?|?|?root?|?|?127.0.0.1?|?|?root?|?|?::1?|?|?|?|?localhost?|?|?|?|?serv08.host.com?|?|?rep?|?*0CDC8D34246E22649D647DB04E7CCCACAB4368B6?|?192.168.1.%?|?+——+——————————————-+—————–+?7?rows?in?set?(0.00?sec)?
mysql>?create?database?larrydb;?
Query?OK,?1?row?affected?(0.00?sec)?
mysql>?use?larrydb;?Database?changed?
mysql>?create?table?user(id?int,?name?varchar(30));?
Query?OK,?0?rows?affected?(0.01?sec)?
mysql>?insert?into?user?values(1,’larrywen’);?
Query?OK,?1?row?affected?(0.01?sec)?
mysql>?insert?into?user?values(2,’wentasy’);?
Query?OK,?1?row?affected?(0.00?sec)?
mysql>?select?*?from?user;?+——+———-+?|?id?|?name?|?+——+———-+?|?1?|?larrywen?|?|?2?|?wentasy?|?+——+———-+?2?rows?in?set?(0.00?sec)?serv09?mysql>?select?*?from?larrydb.user;?+——+———-+?|?id?|?name?|?+——+———-+?|?1?|?larrywen?|?|?2?|?wentasy?|?+——+———-+?2?rows?in?set?(0.00?sec)?

mysql>?stop?slave;
Query?OK,?0?rows?affected?(0.01?sec)

[root@serv01?mysql-proxy]#?id?mysql?uid=500(mysql)?gid=500(mysql)?groups=500(mysql)?[root@serv01?mysql-proxy]#?vim?rw-splitting.lua?
[root@serv01?mysql-proxy]#?cat?rw-splitting.lua?|?grep?-e?min_idle_connections?-e?max_idle_connections?-e?is_debug?min_idle_connections?=?1,–最小空闲连接数，为了测试，这里设置为1?max_idle_connections?=?1,–最大空闲连接数，为了测试，这里设置为1?
is_debug?=?true–是否打开Debug调试，为了查看调试信息，这里设置为true?

[root@serv01?mysql-proxy]#?/etc/init.d/mysql-proxy?start?Starting?mysql-proxy:?–先确定是否可以连接?
[root@serv01?~]#?mysql?-ularry?-plarry?-h?192.168.1.18?Welcome?to?the?MySQL?monitor.?Commands?end?with?;?or?\g.?Your?MySQL?connection?id?is?6?Server?version:?5.5.29-log?Source?distribution?Copyright?(c)?2000,?2012,?Oracle?and/or?its?affiliates.?All?rights?reserved.?Oracle?is?a?registered?trademark?of?Oracle?Corporation?and/or?its?affiliates.?Other?names?may?be?trademarks?of?their?respective?owners.?Type?’help;’?or?’\h’?for?help.?Type?’\c’?to?clear?the?current?input?statement.?
mysql>?exit?Bye?
[root@serv01?~]#?mysql?-ularry?-plarry?-h?192.168.1.19?Welcome?to?the?MySQL?monitor.?Commands?end?with?;?or?\g.?Your?MySQL?connection?id?is?8?Server?version:?5.5.29-log?Source?distribution?Copyright?(c)?2000,?2012,?Oracle?and/or?its?affiliates.?All?rights?reserved.?Oracle?is?a?registered?trademark?of?Oracle?Corporation?and/or?its?affiliates.?Other?names?may?be?trademarks?of?their?respective?owners.?Type?’help;’?or?’\h’?for?help.?Type?’\c’?to?clear?the?current?input?statement.?
mysql>?exit?Bye?

[root@serv01?~]#?/etc/init.d/mysql-proxy?start?Starting?mysql-proxy:?
[root@serv01?~]#?mysql?-ularry?-plarry?-h?192.168.1.11?[connect_server]?192.168.1.11:51054?[1].connected_clients?=?0?[1].pool.cur_idle?=?0?[1].pool.max_idle?=?1?[1].pool.min_idle?=?1?[1].type?=?1?[1].state?=?0?[1]?idle-conns?below?min-idle?Welcome?to?the?MySQL?monitor.?Commands?end?with?;?or?\g.?[read_query]?192.168.1.11:51054?current?backend?=?0?client?default?db?=?client?username?=?larry?query?=?select?@@version_comment?limit?1?sending?to?backend?:?192.168.1.19:3306?is_slave?:?false?server?default?db:?server?username?:?larry?in_trans?:?false?in_calc_found?:?false?COM_QUERY?:?true?Your?MySQL?connection?id?is?10?Server?version:?5.5.29-log?Source?distribution?Copyright?(c)?2000,?2012,?Oracle?and/or?its?affiliates.?All?rights?reserved.?Oracle?is?a?registered?trademark?of?Oracle?Corporation?and/or?its?affiliates.?Other?names?may?be?trademarks?of?their?respective?owners.?Type?’help;’?or?’\h’?for?help.?Type?’\c’?to?clear?the?current?input?statement.?
mysql>?
mysql>?use?larrydb;?[read_query]?192.168.1.11:51054?current?backend?=?0?client?default?db?=?client?username?=?larry?query?=?SELECT?DATABASE()sending?to?backend?:?192.168.1.19:3306?is_slave?:?false?server?default?db:?server?username?:?larry?in_trans?:?false?in_calc_found?:?false?COM_QUERY?:?true?[read_query]?192.168.1.11:51054?current?backend?=?0?client?default?db?=?client?username?=?larry?sending?to?backend?:?192.168.1.19:3306?is_slave?:?false?server?default?db:?server?username?:?larry?in_trans?:?false?in_calc_found?:?false?COM_QUERY?:?false?Database?changed?
mysql>?select?*?from?user;?
[read_query]?192.168.1.11:51054?current?backend?=?0?client?default?db?=?larrydb?client?username?=?larry?query?=?select?*?from?user?sending?to?backend?:?192.168.1.19:3306?is_slave?:?false?server?default?db:?larrydb?server?username?:?larry?in_trans?:?false?in_calc_found?:?false?COM_QUERY?:?true
?+——+———-+?
|?id?|?name?|?+——+———-+?
|?1?|?larrywen?|?
|?2?|?wentasy?|?
+——+———-+?
2?rows?in?set?(0.00?sec)?
mysql>?insert?into?user?values(3,’jsutdb’);?
[read_query]?192.168.1.11:51644?current?backend?=?0?client?default?db?=?larrydb?client?username?=?larry?query?=?insert?into?user?values(3,’jsutdb’)sending?to?backend?:?192.168.1.19:3306?is_slave?:?false?server?default?db:?larrydb?server?username?:?larry?in_trans?:?false?in_calc_found?:?false?COM_QUERY?:?true?
Query?OK,?1?row?affected?(0.00?sec)?serv08?
mysql>?select?*?from?user;
?+——+———-+?|?id?|?name?|?
+——+———-+?|?1?|?larrywen?|?
|?2?|?wentasy?|?+——+———-+?
2?rows?in?set?(0.00?sec)?serv09?
mysql>?select?*?from?larrydb.user;?
+——+———-+?|?id?|?name?|?
+——+———-+?|?1?|?larrywen?|?
|?2?|?wentasy?|?
|?3?|?jsutdb?|
?+——+———-+?
3?rows?in?set?(0.00?sec)?

[root@serv01?~]#?vim?/etc/init.d/mysql-proxy?
[root@serv01?~]#?cat?/etc/init.d/mysql-proxy?#!/bin/sh?#?#?mysql-proxy?This?script?starts?and?stops?the?mysql-proxy?daemon?#?#?chkconfig:?-?78?30?#?processname:?mysql-proxy?#?description:?mysql-proxy?is?a?proxy?daemon?to?mysql?#?Source?function?library.?.?/etc/rc.d/init.d/functions?#PROXY_PATH=/usr/local/bin?PROXY_PATH=/usr/local/mysql-proxy/bin?prog=”mysql-proxy”?#?Source?networking?configuration.?.?/etc/sysconfig/network?#?Check?that?networking?is?up.?[?${NETWORKING}?=?”no”?]?&&?exit?0?#?Set?default?mysql-proxy?configuration.?#PROXY_OPTIONS=”–daemon”?PROXY_OPTIONS=”–proxy-read-only-backend-addresses=192.168.1.19:3306?–proxy-backend-addresses=192.168.1.18:3306?–proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua”?#PROXY_PID=/usr/local/mysql-proxy/run/mysql-proxy.pid?PROXY_PID=/var/run/mysql-proxy.pid?#?Source?mysql-proxy?configuration.?if?[?-f?/etc/sysconfig/mysql-proxy?];?then?.?/etc/sysconfig/mysql-proxy?fi?PATH=$PATH:/usr/bin:/usr/local/bin:$PROXY_PATH?#?By?default?it’s?all?good?RETVAL=0?#?See?how?we?were?called.?case?”$1″?in?start)?#?Start?daemon.?echo?-n?$”Starting?$prog:?”$NICELEVEL?$PROXY_PATH/mysql-proxy?$PROXY_OPTIONS?–daemon?–pid-file=$PROXY_PID?–user=mysql?–log-level=debug?–log-file=/var/log/mysql-proxy.log?–proxy-address=192.168.1.11:3306?RETVAL=$echo?if?[?$RETVAL?=?0?];?then?touch?/var/lock/subsys/mysql-proxy?fi?;;?stop)?#?Stop?daemons.?echo?-n?$”Stopping?$prog:?”?killproc?$prog?RETVAL=$echo?if?[?$RETVAL?=?0?];?then?rm?-f?/var/lock/subsys/mysql-proxy?rm?-f?$PROXY_PID?fi?;;?restart)?$0?stop?sleep?3?$0?start?;;?condrestart)?[?-e?/var/lock/subsys/mysql-proxy?]?&&?$0?restart?;;?status)?status?mysql-proxy?RETVAL=$;;?*)?echo?”Usage:?$0?{start|stop|restart|status|condrestart}”?RETVAL=1?;;?esac?exit?$RETVAL?

[root@serv01?~]#?mysql?-ularry?-plarry?-h?192.168.1.11?[connect_server]?192.168.1.11:57891?[1].connected_clients?=?0?[1].pool.cur_idle?=?0?[1].pool.max_idle?=?1?[1].pool.min_idle?=?1?[1].type?=?1?[1].state?=?1?[1]?idle-conns?below?min-idle?[read_query]?192.168.1.11:57891?current?backend?=?0?client?default?db?=?client?username?=?larry?query?=?select?@@version_comment?limit?1sending?to?backend?:?192.168.1.18:3306?is_slave?:?false?server?default?db:?server?username?:?larry?in_trans?:?false?in_calc_found?:?false?COM_QUERY?:?true?
mysql>?insert?into?user?values(5,’test’);?
Query?OK,
1?row?affected?(0.01?sec)?
[read_query]?192.168.1.11:57893?current?backend?=?0?client?default?db?=?larrydb?client?username?=?larry?query?=?insert?into?user?values(5,’test’)sending?to?backend?:?192.168.1.18:3306?is_slave?:?false?server?default?db:?larrydb?server?username?:?larry?in_trans?:?false?in_calc_found?:?false?COM_QUERY?:?true?
mysql>?select?*?from?user;?
+——+———-+?|?id?|?name?|?
+——+———-+?|?1?|?larrywen?|?
|?2?|?wentasy?|?
|?5?|?test?|?+——+———-+?
3?rows?in?set?(0.00?sec)?
[read_query]?192.168.1.11:57893?current?backend?=?0?client?default?db?=?larrydb?client?username?=?larry?query?=?select?*?from?usersending?to?backend?:?192.168.1.18:3306?is_slave?:?false?server?default?db:?larrydb?server?username?:?larry?in_trans?:?false?in_calc_found?:?false?COM_QUERY?:?true?serv08主服务器查看数据，可以查询到，说明主服务器可以写?mysql>?select?*?from?larrydb.user;?
+——+———-+?|?id?|?name?|?
+——+———-+?|?1?|?larrywen?|
?|?2?|?wentasy?|
?|?5?|?test?|?+——+———-+?
3?rows?in?set?(0.00?sec)?
serv09从服务器查询数据，发现不可查询到，说明从服务器只读
?mysql>?
mysql>?select?*?from?larrydb.user;?
+——+———-+?|?id?|?name?|
?+——+———-+?|?1?|?larrywen?|?
|?2?|?wentasy?|?
|?3?|?jsutdb?|
?|?4?|?db?|?+——+———-+?
4?rows?in?set?(0.00?sec)?

mysql>?start?slave;
Query?OK,?0?rows?affected?(0.00?sec)
mysql>?select?*?from?larrydb.user;
+——+———-+
|?id?|?name?|
+——+———-+
|1?|?larrywen?|
|2?|?wentasy|
|3?|?jsutdb?|
|4?|?db?|
|5?|?test?|
+——+———-+
5?rows?in?set?(0.00?sec)

您可能感兴趣的文章:从mysql读写分离着手提升服务器性能Mysql性能调优之max_allowed_packet使用及说明5招带你轻松优化MySQL count(*)查询性能MYSQL?数据库时间字段?INT,TIMESTAMP,DATETIME?性能效率的比较介绍MySQL查询性能优化七种方式索引潜水