程序博客网 > 数据同步软件 增量备份

mysql读写分离

来源：互联网 发布：数据同步软件 增量备份 编辑：程序博客网 时间：2024/04/30 13:31

MySQL读写分离基本原理是让master数据库处理写操作，slave数据库处理读操作。master将写操作的变更同步到各个slave节点。

MySQL读写分离能提高系统性能的原因在于：

  • 物理服务器增加，机器处理能力提升。拿硬件换性能。

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

  • slave可以配置myiasm引擎，提升查询性能以及节约系统开销。

  • master直接写是并发的，slave通过主库发送来的binlog恢复数据是异步。

  • slave可以单独设置一些参数来提升其读的性能。

  • 增加冗余，提高可用性。

MySQLProxy介绍

下面使用MySQL官方提供的数据库代理层产品MySQLProxy搭建读写分离。
MySQLProxy实际上是在客户端请求与MySQLServer之间建立了一个连接池。所有客户端请求都是发向MySQLProxy，然后经由MySQLProxy进行相应的分析，判断出是读操作还是写操作，分发至对应的MySQLServer上。对于多节点Slave集群，也可以起做到负载均衡的效果。


MySQL读写分离配置

MySQL环境准备

master 192.168.1.5

slave 192.168.1.6

proxy 192.168.1.2

MySQL：5.5.37

MySQL-proxy：mysql-proxy-0.8.4-linux-rhel5-x86-64bit.tar.gz

创建用户并分配权限


MySQL

1

2

mysql>create userlibaiidentified by'libai';

mysql>grantallon*.*tolibai@'192.168.1.%'identified by'lib;

在配置了MySQL复制，以上操作在master执行会同步到slave节点。

启用MySQL复制

MySQL复制配置请参考这里

先关闭并清除之前的复制。

MySQL

1

2

mysql>stop slave;

mysql>reset slaveall;

启用新的复制同步。启用之前需要清除日志

MySQL

1

mysql>changemastertomaster_host='192.168.1.5',master_user='libai',master_password='libai',master_port=3306,master_log_file='mysql-bin.000001',master_log_pos=0;

主库

MySQL

1

2

3

4

5

# mysql -h localhost -ulibai -plibai

mysql>createdatabased;

mysql>used;

mysql>createtablet(iint);

mysql>insertintotvalues(1);

从库

MySQL

1

2

3

4

5

mysql>select*fromt;

+------+

|i    |

+------+

|1    |

启用MySQLProxy代理服务器

代理服务器上创建mysql用户

MySQL

1

2

# groupadd mysql

# useradd -g mysql mysql

解压启动mysql-proxy

MySQL

1

# ./mysql-proxy --daemon --log-level=debug --user=mysql --keepalive --log-file=/var/log/mysql-proxy.log --plugins="proxy" --proxy-backend-addresses="192.168.1.5:3306" --proxy-read-only-backend-addresses="192.168.1.6:3306" --proxy-lua-script="/root/soft/mysql-proxy/rw-splitting.lua" --plugins=admin --admin-username="admin" --admin-password="admin" --admin-lua-script="/root/soft/mysql-proxy/lib/mysql-proxy/lua/admin.lua"

其中proxy-backend-addresses是master服务器，proxy-read-only-backend-addresses是slave服务器。可以通过./mysql-proxy –help 查看详细说明。

查看启动后进程

MySQL

1

2

3

# ps -ef | grep mysql

root257211011:33?00:00:00/root/soft/mysql-proxy/libexec/mysql-proxy--daemon--log-level=debug--user=mysql--keepalive--log-file=/var/log/mysql-proxy.log--plugins=proxy--proxy-backend-addresses=192.168.1.5:3306--proxy-read-only-backend-addresses=192.168.1.6:3306--proxy-lua-script=/root/soft/mysql-proxy/rw-splitting.lua--plugins=admin--admin-username=admin--admin-password=admin--admin-lua-script=/root/soft/mysql-proxy/lib/mysql-proxy/lua/admin.lua

mysql2572225721011:33?00:00:00/root/soft/mysql-proxy/libexec/mysql-proxy--daemon--log-level=debug--user=mysql--keepalive--log-file=/var/log/mysql-proxy.log--plugins=proxy--proxy-backend-addresses=192.168.1.5:3306--proxy-read-only-backend-addresses=192.168.1.6:3306--proxy-lua-script=/root/soft/mysql-proxy/rw-splitting.lua--plugins=admin--admin-username=admin--admin-password=admin--admin-lua-script=/root/soft/mysql-proxy/lib/mysql-proxy/lua/admin.lua

4040是proxy端口，4041是admin管理端口

MySQL

1

2

3

4

5

COMMANDPIDUSERFDTYPEDEVICESIZE/OFFNODENAME

mysql-pro25722mysql10uIPv47624290t0TCP*:yo-main(LISTEN)

# lsof -i:4041

COMMANDPIDUSERFDTYPEDEVICESIZE/OFFNODENAME

mysql-pro25722mysql11uIPv47624320t0TCP*:houston(LISTEN)


测试

保证mysqlproxy节点上可执行mysql 。通过复制同步帐号连接proxy

MySQL

1

2

3

4

5

6

7

8

9

10

11

# mysql -h 192.168.1.2 -ulibai -p --port=4040

mysql>showdatabases    

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

  |Database          |

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

  |information_schema|

  |      d            |

  |      mysql        |

  |performance_schema|

  |        test      |

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

登录admin查看状态

MySQL

1

2

3

4

5

6

7

8

# mysql -h 192.168.1.2 -u admin -p --port=4041

mysql>select*frombackends;

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

  |backend_ndx|address          |state|type|uuid|connected_clients|

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

  |1          |192.168.1.5:3306|up    |rw  |NULL|        0        |

  |2          |192.168.1.6:3306|up    |ro  |NULL|        0        |

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

2 rows in set (0.00 sec)
可以从以上查询中看到master和slave状态均为up。

1）登录proxy节点，创建数据库dufu，并创建一张表t

MySQL

1

2

3

4

5

mysql>createdatabasedufu;

mysql>showdatabases;

mysql>usedufu;

mysql>createtablet(idint(10),namevarchar(20));

mysql>showtables;

创建完数据库及表后，主从节点上应该都可以看到

2）关闭同步，分别在master和slave上插入数据

MySQL

1

mysql>slavestop;

master

MySQL

1

mysql>insertintotvalues(1,'this_is_master');

slave

MySQL

1

mysql>insertintotvalues(2,'this_is_slave');

3）proxy上查看结果

MySQL

1

2

3

4

5

6

7

8

mysql>usedufu;

mysql>select*fromt;

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

|id  |name          |

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

|  2  |this_is_slave|  

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

1rowinset(0.00sec)


从结果可以看到数据是从slave上读取的，并没考虑master节点上的数据。

直接从proxy上插入数据

MySQL

1

mysql>insertintotvalues(3,'this_is_proxy');


再次查询


MySQL

1

2

3

4

5

6

mysql>select*fromt;

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

  |  id  |    name      |

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

  |2    |this_is_slave|

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


结果显示查询数据没有变化，因为proxy上执行insert相当于写入到了master上，而查询的数据是从slave上读取的。

master上查询

MySQL

1

2

3

4

5

6

7

mysql>select*fromt;

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

|  id  |      name      |

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

|  1  |  this_is_master|

|  3  |  this_is_proxy|

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


启用复制，proxy查询


MySQL

1

2

3

4

5

6

7

8

mysql>select*fromt;

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

  |  id  |      name      |

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

  |  2  |  this_is_slave|

  |  1  |this_is_master|

  |  3  |  this_is_proxy|

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


MySQL读写分离基本原理是让master数据库处理写操作，slave数据库处理读操作。master将写操作的变更同步到各个slave节点。

MySQL读写分离能提高系统性能的原因在于：

  • 物理服务器增加，机器处理能力提升。拿硬件换性能。

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

  • slave可以配置myiasm引擎，提升查询性能以及节约系统开销。

  • master直接写是并发的，slave通过主库发送来的binlog恢复数据是异步。

  • slave可以单独设置一些参数来提升其读的性能。

  • 增加冗余，提高可用性。

MySQLProxy介绍

下面使用MySQL官方提供的数据库代理层产品MySQLProxy搭建读写分离。
MySQLProxy实际上是在客户端请求与MySQLServer之间建立了一个连接池。所有客户端请求都是发向MySQLProxy，然后经由MySQLProxy进行相应的分析，判断出是读操作还是写操作，分发至对应的MySQLServer上。对于多节点Slave集群，也可以起做到负载均衡的效果。


MySQL读写分离配置

MySQL环境准备

master 192.168.1.5

slave 192.168.1.6

proxy 192.168.1.2

MySQL：5.5.37

MySQL-proxy：mysql-proxy-0.8.4-linux-rhel5-x86-64bit.tar.gz

创建用户并分配权限


MySQL

1

2

mysql>create userlibaiidentified by'libai';

mysql>grantallon*.*tolibai@'192.168.1.%'identified by'lib;

在配置了MySQL复制，以上操作在master执行会同步到slave节点。

启用MySQL复制

MySQL复制配置请参考这里

先关闭并清除之前的复制。

MySQL

1

2

mysql>stop slave;

mysql>reset slaveall;

启用新的复制同步。启用之前需要清除日志

MySQL

1

mysql>changemastertomaster_host='192.168.1.5',master_user='libai',master_password='libai',master_port=3306,master_log_file='mysql-bin.000001',master_log_pos=0;

主库

MySQL

1

2

3

4

5

# mysql -h localhost -ulibai -plibai

mysql>createdatabased;

mysql>used;

mysql>createtablet(iint);

mysql>insertintotvalues(1);

从库

MySQL

1

2

3

4

5

mysql>select*fromt;

+------+

|i    |

+------+

|1    |

启用MySQLProxy代理服务器

代理服务器上创建mysql用户

MySQL

1

2

# groupadd mysql

# useradd -g mysql mysql

解压启动mysql-proxy

MySQL

1

# ./mysql-proxy --daemon --log-level=debug --user=mysql --keepalive --log-file=/var/log/mysql-proxy.log --plugins="proxy" --proxy-backend-addresses="192.168.1.5:3306" --proxy-read-only-backend-addresses="192.168.1.6:3306" --proxy-lua-script="/root/soft/mysql-proxy/rw-splitting.lua" --plugins=admin --admin-username="admin" --admin-password="admin" --admin-lua-script="/root/soft/mysql-proxy/lib/mysql-proxy/lua/admin.lua"

其中proxy-backend-addresses是master服务器，proxy-read-only-backend-addresses是slave服务器。可以通过./mysql-proxy –help 查看详细说明。

查看启动后进程

MySQL

1

2

3

# ps -ef | grep mysql

root257211011:33?00:00:00/root/soft/mysql-proxy/libexec/mysql-proxy--daemon--log-level=debug--user=mysql--keepalive--log-file=/var/log/mysql-proxy.log--plugins=proxy--proxy-backend-addresses=192.168.1.5:3306--proxy-read-only-backend-addresses=192.168.1.6:3306--proxy-lua-script=/root/soft/mysql-proxy/rw-splitting.lua--plugins=admin--admin-username=admin--admin-password=admin--admin-lua-script=/root/soft/mysql-proxy/lib/mysql-proxy/lua/admin.lua

mysql2572225721011:33?00:00:00/root/soft/mysql-proxy/libexec/mysql-proxy--daemon--log-level=debug--user=mysql--keepalive--log-file=/var/log/mysql-proxy.log--plugins=proxy--proxy-backend-addresses=192.168.1.5:3306--proxy-read-only-backend-addresses=192.168.1.6:3306--proxy-lua-script=/root/soft/mysql-proxy/rw-splitting.lua--plugins=admin--admin-username=admin--admin-password=admin--admin-lua-script=/root/soft/mysql-proxy/lib/mysql-proxy/lua/admin.lua

4040是proxy端口，4041是admin管理端口

MySQL

1

2

3

4

5

COMMANDPIDUSERFDTYPEDEVICESIZE/OFFNODENAME

mysql-pro25722mysql10uIPv47624290t0TCP*:yo-main(LISTEN)

# lsof -i:4041

COMMANDPIDUSERFDTYPEDEVICESIZE/OFFNODENAME

mysql-pro25722mysql11uIPv47624320t0TCP*:houston(LISTEN)


测试

保证mysqlproxy节点上可执行mysql 。通过复制同步帐号连接proxy

MySQL

1

2

3

4

5

6

7

8

9

10

11

# mysql -h 192.168.1.2 -ulibai -p --port=4040

mysql>showdatabases    

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

  |Database          |

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

  |information_schema|

  |      d            |

  |      mysql        |

  |performance_schema|

  |        test      |

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

登录admin查看状态

MySQL

1

2

3

4

5

6

7

8

# mysql -h 192.168.1.2 -u admin -p --port=4041

mysql>select*frombackends;

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

  |backend_ndx|address          |state|type|uuid|connected_clients|

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

  |1          |192.168.1.5:3306|up    |rw  |NULL|        0        |

  |2          |192.168.1.6:3306|up    |ro  |NULL|        0        |

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

2 rows in set (0.00 sec)
可以从以上查询中看到master和slave状态均为up。

1）登录proxy节点，创建数据库dufu，并创建一张表t

MySQL

1

2

3

4

5

mysql>createdatabasedufu;

mysql>showdatabases;

mysql>usedufu;

mysql>createtablet(idint(10),namevarchar(20));

mysql>showtables;

创建完数据库及表后，主从节点上应该都可以看到

2）关闭同步，分别在master和slave上插入数据

MySQL

1

mysql>slavestop;

master

MySQL

1

mysql>insertintotvalues(1,'this_is_master');

slave

MySQL

1

mysql>insertintotvalues(2,'this_is_slave');

3）proxy上查看结果

MySQL

1

2

3

4

5

6

7

8

mysql>usedufu;

mysql>select*fromt;

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

|id  |name          |

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

|  2  |this_is_slave|  

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

1rowinset(0.00sec)


从结果可以看到数据是从slave上读取的，并没考虑master节点上的数据。

直接从proxy上插入数据

MySQL

1

mysql>insertintotvalues(3,'this_is_proxy');


再次查询


MySQL

1

2

3

4

5

6

mysql>select*fromt;

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

  |  id  |    name      |

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

  |2    |this_is_slave|

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


结果显示查询数据没有变化，因为proxy上执行insert相当于写入到了master上，而查询的数据是从slave上读取的。

master上查询

MySQL

1

2

3

4

5

6

7

mysql>select*fromt;

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

|  id  |      name      |

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

|  1  |  this_is_master|

|  3  |  this_is_proxy|

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


启用复制，proxy查询


MySQL

1

2

3

4

5

6

7

8

mysql>select*fromt;

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

  |  id  |      name      |

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

  |  2  |  this_is_slave|

  |  1  |this_is_master|

  |  3  |  this_is_proxy|

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


0 0
数据同步软件 增量备份 数据同步软件 增量备份
原创粉丝点击
热门IT博客
吴炳亮fifa淘宝吴炳亮fifa淘宝
大数据 零售业思路大数据 零售业思路
jdk1.8 64位下载linux
如何在淘宝上卖二手货
js中window.open
linux 僵尸进程
数据库去除重复数据
杭州c 程序员工资
网络匿名举报网站
cstpcd数据库
新站前期怎么做优化
淘宝客推广方式
定格动画制作软件
扬州软件测试培训
知乎经典问答荟萃
全国最新人口普查数据
晋江文学城软件下载
mac 下的虚拟机性能差
深圳市灵沃软件
产生随机数软件
热门问题 老师的惩罚 人脸识别 我在镇武司摸鱼那些年 重生之率土为王 我在大康的咸鱼生活 盘龙之生命进化 天生仙种 凡人之先天五行 春回大明朝 姑娘不必设防，我是瞎子 她缓缓把刀刺入小腹 刀刺腹肌 梦见被人拿刀刺预示什么 刺身刀什么牌子好 闪电发型刻纹图片 刀削面的刀 刀削面的酱卤的做法 山西刀削面的做法 家常刀削面的做法 刀削面的刀图片 刀削面的配方 刀削面的刀多少钱 刀削面的面怎么和 刀削面技术 刀削面机器 刀削面做法 刀削面卤的做法 机器人刀削面 刀削面机器人价格 刀削面机器人 刀削面机 刀削面怎么削 山西刀削面图片 刀削面和面配方 刀削面培训班 学刀削面要多少钱 刀削面培训哪里好 培训刀削面 特色刀削面培训 刀削面培训费用 学习刀削面 刀削面多少钱 刀削面连锁店 哪里培训刀削面 学刀削面 刀削面卤配方 附近刀削面 一一刀削面配方 二板刀削面 鸡肉刀削面 刀削面多少钱一碗

程序博客网，程序员的互联网技术博客家园。csdn论坛精品 msdn技术资料都在这里