当前位置： 代码迷 >> MySQL >> Mysql1主多从和读写分离配置简记
  详细解决方案

Mysql1主多从和读写分离配置简记

热度：124   发布时间：2016-05-05 17:06:26.0
Mysql一主多从和读写分离配置简记
近期开发的系统中使用MySql作为数据库，由于数据涉及到Money，所以不得不慎重。同时，用户对最大访问量也提出了要求。为了避免Mysql成为性能瓶颈并具备很好的容错能力，特此实现主从热备和读写分离。在此简做纪要,以备日后所用！

一、配置主从

条件：两台PC，IP分别为192.168.168.253,192.168.168.251。两台PC上的Mysql版本为5.0。253上的Mysql为Master，251上的Mysql为Slave。

1、主数据库服务器配置

进入主数据库服务器安装目录,打开my.ini,在文件末尾增加如下配置：

?

[java]?view plaincopy
?
  1. #数据库ID号，?为1时表示为Master,其中master_id必须为1到232–1之间的一个正整数值;???
  2. server-id?=?1??
  3. #启用二进制日志；??
  4. log-bin=mysql-bin??
  5. #需要同步的二进制数据库名；??
  6. binlog-do-db=minishop??
  7. #不同步的二进制数据库名,如果不设置可以将其注释掉;??
  8. binlog-ignore-db=information_schema??
  9. binlog-ignore-db=mysql??
  10. binlog-ignore-db=personalsite??
  11. binlog-ignore-db=test??
  12. #设定生成的log文件名；??
  13. log-bin="D:/Database/materlog"??
  14. #把更新的记录写到二进制文件中；??
  15. log-slave-updates??
保存文件。重启Mysql服务。

?

进入从数据库服务器安装目录,打开my.ini,在文件末尾增加如下配置：

?

[java]?view plaincopy
?
  1. #如果需要增加Slave库则，此id往后顺延；??
  2. server-id?=?2????
  3. log-bin=mysql-bin??
  4. #主库host??
  5. master-host?=?192.168.168.253??
  6. #在主数据库服务器中建立的用于该从服务器备份使用的用户??
  7. master-user?=?forslave??
  8. master-password?=?******??
  9. master-port?=?3306??
  10. #如果发现主服务器断线，重新连接的时间差；??
  11. master-connect-retry=60??
  12. #不需要备份的数据库；???
  13. replicate-ignore-db=mysql??
  14. #需要备份的数据库??
  15. replicate-do-db=minishop??
  16. log-slave-update??
保存文件。重启Mysql服务。

?

进入主数据库服务器，创建上面备份使用的用户名和密码，同时授权replication slave,super和reload

?

[java]?view plaincopy
?
  1. mysql>grant?replication?slave,super,reload?on?minishop.*?to?forslave@192.168.168.251?identified?by?'******';??

进入从数据库服务器，启动Slave。

[java]?view plaincopy
?
  1. mysql>slave?start;??
  2. ??
  3. mysql>show?slave?status\G;??

测试：进入主数据库服务器，在Minishop中某张表中插入一条数据，然后到从数据库服务器中查看是否含有刚刚插入的数据。完毕！

备注：1)运行配置后的主数据库服务器先于从数据库服务器，这样运行从数据库服务器时,主库的 File 和 Position 与 从库的上设置Master_Log_File、Read_Master_Log_Pos 就会一致。否则，可能出现不一致的情况。这也可以通过命令调整。

2)如果发现主从复制失败时，可以先关闭从数据库服务器，然后删除从数据库服务器中data目录下relay-log.info,hosname-relay-bin*,master.info等文件，重启从服务器。

?

二、读写分离配置

本想采用Mysql Proxy来实现读写分离，奈何其使用的lua脚本着实让人头痛，最后决定采用国人开发的开源数据库代理中间件Amoeba。使用Amoeba，只需要简单的xml配置，就可以很容易地实现读写分离。

Amoeba处于应用程序和数据库服务器之间，充当一个中间代理层。其支持负载均衡、高可用性、Query过滤、读写分离、可路由相关的query到目标数据库、可并发请求多台数据库合并结果。功能很强大。

Amoeba默认的端口为8066，实现了Mysql协议。应用程序中只需要修改一个数据库连接就可以实现采用Amoeba来代理数据库访问。比如：java应用中，假如你原来的jdbc连接字符串为：jdbc:mysql://192.168.168.42:3306/minishop，那么现在，你想使用Amoeba作为数据库访问代理，则只需要将上面连接字符串改为如下(假如Amoeba所在机子IP为192.168.168.88)：jdbc:mysql://192.168.168.88:8066/minishop。Amoeba透明性做的很赞。

主要还是配置Amoeda，但是配置也是相当的简单。基本只需要配置两个文件：conf\dbServers.xml和conf\amoeba.xml。配置中各项的含义，可以参考amoeda中文指南，这里不做过多解释。仅记录下配置。

dbServers.xml主要配置

?

[html]?view plaincopy
?
  1. <amoeba:dbServers?xmlns:amoeba="http://amoeba.meidusa.com/">??
  2. ??
  3. ????????<!--???
  4. ????????????Each?dbServer?needs?to?be?configured?into?a?Pool,??
  5. ????????????If?you?need?to?configure?multiple?dbServer?with?load?balancing?that?can?be?simplified?by?the?following?configuration:??
  6. ?????????????add?attribute?with?name?virtual?=?"true"?in?dbServer,?but?the?configuration?does?not?allow?the?element?with?name?factoryConfig??
  7. ?????????????such?as?'multiPool'?dbServer?????
  8. ????????-->??
  9. ??????????
  10. ????<dbServer?name="abstractServer"?abstractive="true">??
  11. ????????<factoryConfig?class="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory">??
  12. ????????????<property?name="manager">${defaultManager}</property>??
  13. ????????????<property?name="sendBufferSize">64</property>??
  14. ????????????<property?name="receiveBufferSize">128</property>??
  15. ??????????????????
  16. ????????????<!--?mysql?port?-->??
  17. ????????????<property?name="port">3306</property>??
  18. ??????????????
  19. ????????????<!--?mysql?schema?-->??
  20. ????????????<property?name="schema">minishop</property>??
  21. ??????????????
  22. ????????????<!--?mysql?user?-->??
  23. ????????????<property?name="user">chenjie</property>??
  24. ??????????????
  25. ????????????<!--??mysql?password?-->??
  26. ????????????<property?name="password">chenjie</property>??
  27. ??
  28. ????????</factoryConfig>??
  29. ??
  30. ????????<poolConfig?class="com.meidusa.amoeba.net.poolable.PoolableObjectPool">??
  31. ????????????<property?name="maxActive">500</property>??
  32. ????????????<property?name="maxIdle">500</property>??
  33. ????????????<property?name="minIdle">10</property>??
  34. ????????????<property?name="minEvictableIdleTimeMillis">600000</property>??
  35. ????????????<property?name="timeBetweenEvictionRunsMillis">600000</property>??
  36. ????????????<property?name="testOnBorrow">true</property>??
  37. ????????????<property?name="testWhileIdle">true</property>??
  38. ????????</poolConfig>??
  39. ????</dbServer>??
  40. ??
  41. ????<dbServer?name="master"??parent="abstractServer">??
  42. ????????<factoryConfig>??
  43. ????????????<!--?mysql?ip?-->??
  44. ????????????<property?name="ipAddress">192.168.168.253</property>??
  45. ????????</factoryConfig>??
  46. ????</dbServer>??
  47. ??
  48. ????<dbServer?name="slave1"??parent="abstractServer">??
  49. ????????<factoryConfig>??
  50. ????????????<!--?mysql?ip?-->??
  51. ????????????<property?name="ipAddress">192.168.168.119</property>??
  52. ????????</factoryConfig>??
  53. ????</dbServer>??
  54. ??
  55. ????<dbServer?name="slave2"??parent="abstractServer">??
  56. ????????<factoryConfig>??
  57. ????????????<!--?mysql?ip?-->??
  58. ????????????<property?name="ipAddress">192.168.168.251</property>??
  59. ??
  60. ????????</factoryConfig>??
  61. ????</dbServer>??
  62. ??????
  63. ????<dbServer?name="multiPool"?virtual="true">??
  64. ????????<poolConfig?class="com.meidusa.amoeba.server.MultipleServerPool">??
  65. ????????????<!--?Load?balancing?strategy:?1=ROUNDROBIN?,?2=WEIGHTBASED?,?3=HA-->??
  66. ????????????<property?name="loadbalance">1</property>??
  67. ??????????????
  68. ????????????<!--?Separated?by?commas,such?as:?server1,server2,server1?-->??
  69. ????????????<property?name="poolNames">slave1,slave2</property>??
  70. ????????</poolConfig>??
  71. ????</dbServer>??
  72. ??????????
  73. </amoeba:dbServers>??

amoeba.xml配置：

?

?

[html]?view plaincopy
?
  1. <amoeba:configuration?xmlns:amoeba="http://amoeba.meidusa.com/">??
  2. ??
  3. ????<proxy>??
  4. ??????
  5. ????????<!--?service?class?must?implements?com.meidusa.amoeba.service.Service?-->??
  6. ????????<service?name="Amoeba?for?Mysql"?class="com.meidusa.amoeba.net.ServerableConnectionManager">??
  7. ????????????<!--?port?-->??
  8. ????????????<property?name="port">8066</property>??
  9. ??????????????
  10. ????????????<!--?bind?ipAddress?-->??
  11. ??
  12. ????????????<property?name="ipAddress">192.168.168.253</property>??
  13. ??
  14. ??????????????
  15. ????????????<property?name="manager">${clientConnectioneManager}</property>??
  16. ??????????????
  17. ????????????<property?name="connectionFactory">??
  18. ????????????????<bean?class="com.meidusa.amoeba.mysql.net.MysqlClientConnectionFactory">??
  19. ????????????????????<property?name="sendBufferSize">128</property>??
  20. ????????????????????<property?name="receiveBufferSize">64</property>??
  21. ????????????????</bean>??
  22. ????????????</property>??
  23. ??????????????
  24. ????????????<property?name="authenticator">??
  25. ????????????????<bean?class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator">??
  26. ??????????????????????
  27. ????????????????????<property?name="user">chenjie</property>??
  28. ??????????????????????
  29. ????????????????????<property?name="password">chenjie</property>??
  30. ??????????????????????
  31. ????????????????????<property?name="filter">??
  32. ????????????????????????<bean?class="com.meidusa.amoeba.server.IPAccessController">??
  33. ????????????????????????????<property?name="ipFile">${amoeba.home}/conf/access_list.conf</property>??
  34. ????????????????????????</bean>??
  35. ????????????????????</property>??
  36. ????????????????</bean>??
  37. ????????????</property>??
  38. ??????????????
  39. ????????</service>??
  40. ??????????
  41. ????????<!--?server?class?must?implements?com.meidusa.amoeba.service.Service?-->??
  42. ????????<service?name="Amoeba?Monitor?Server"?class="com.meidusa.amoeba.monitor.MonitorServer">??
  43. ????????????<!--?port?-->??
  44. ????????????<!--??default?value:?random?number??
  45. ????????????<property?name="port">9066</property>??
  46. ????????????-->??
  47. ????????????<!--?bind?ipAddress?-->??
  48. ????????????<property?name="ipAddress">127.0.0.1</property>??
  49. ????????????<property?name="daemon">true</property>??
  50. ????????????<property?name="manager">${clientConnectioneManager}</property>??
  51. ????????????<property?name="connectionFactory">??
  52. ????????????????<bean?class="com.meidusa.amoeba.monitor.net.MonitorClientConnectionFactory"></bean>??
  53. ????????????</property>??
  54. ??????????????
  55. ????????</service>??
  56. ??????????
  57. ????????<runtime?class="com.meidusa.amoeba.mysql.context.MysqlRuntimeContext">??
  58. ????????????<!--?proxy?server?net?IO?Read?thread?size?-->??
  59. ????????????<property?name="readThreadPoolSize">20</property>??
  60. ??????????????
  61. ????????????<!--?proxy?server?client?process?thread?size?-->??
  62. ????????????<property?name="clientSideThreadPoolSize">30</property>??
  63. ??????????????
  64. ????????????<!--?mysql?server?data?packet?process?thread?size?-->??
  65. ????????????<property?name="serverSideThreadPoolSize">30</property>??
  66. ??????????????
  67. ????????????<!--?per?connection?cache?prepared?statement?size??-->??
  68. ????????????<property?name="statementCacheSize">500</property>??
  69. ??????????????
  70. ????????????<!--?query?timeout(?default:?60?second?,?TimeUnit:second)?-->??
  71. ????????????<property?name="queryTimeout">60</property>??
  72. ????????</runtime>??
  73. ??????????
  74. ????</proxy>??
  75. ??????
  76. ????<!--???
  77. ????????Each?ConnectionManager?will?start?as?thread??
  78. ????????manager?responsible?for?the?Connection?IO?read?,?Death?Detection??
  79. ????-->??
  80. ????<connectionManagerList>??
  81. ????????<connectionManager?name="clientConnectioneManager"?class="com.meidusa.amoeba.net.MultiConnectionManagerWrapper">??
  82. ????????????<property?name="subManagerClassName">com.meidusa.amoeba.net.ConnectionManager</property>??
  83. ????????????<!--???
  84. ??????????????default?value?is?avaliable?Processors???
  85. ????????????<property?name="processors">5</property>??
  86. ?????????????-->??
  87. ????????</connectionManager>??
  88. ????????<connectionManager?name="defaultManager"?class="com.meidusa.amoeba.net.MultiConnectionManagerWrapper">??
  89. ????????????<property?name="subManagerClassName">com.meidusa.amoeba.net.AuthingableConnectionManager</property>??
  90. ??????????????
  91. ????????????<!--???
  92. ??????????????default?value?is?avaliable?Processors???
  93. ????????????<property?name="processors">5</property>??
  94. ?????????????-->??
  95. ????????</connectionManager>??
  96. ????</connectionManagerList>??
  97. ??????
  98. ????????<!--?default?using?file?loader?-->??
  99. ????<dbServerLoader?class="com.meidusa.amoeba.context.DBServerConfigFileLoader">??
  100. ????????<property?name="configFile">${amoeba.home}/conf/dbServers.xml</property>??
  101. ????</dbServerLoader>??
  102. ??????
  103. ????<queryRouter?class="com.meidusa.amoeba.mysql.parser.MysqlQueryRouter">??
  104. ????????<property?name="ruleLoader">??
  105. ????????????<bean?class="com.meidusa.amoeba.route.TableRuleFileLoader">??
  106. ????????????????<property?name="ruleFile">${amoeba.home}/conf/rule.xml</property>??
  107. ????????????????<property?name="functionFile">${amoeba.home}/conf/ruleFunctionMap.xml</property>??
  108. ????????????</bean>??
  109. ????????</property>??
  110. ????????<property?name="sqlFunctionFile">${amoeba.home}/conf/functionMap.xml</property>??
  111. ????????<property?name="LRUMapSize">1500</property>??
  112. ??
  113. ????????<property?name="defaultPool">master</property>??
  114. ??????????
  115. ??
  116. ????????<property?name="writePool">master</property>??
  117. ????????<property?name="readPool">multiPool</property>??
  118. ??
  119. ????????<property?name="needParse">true</property>??
  120. ????</queryRouter>??
  121. </amoeba:configuration>??

至此，Mysql主从热备和读写分离配置完毕。但是，具体应用到生产环境究竟如何，还有待测试和考察。后来测试一主多从，又加入了一台Mysql从数据库服务器，这就是为什么上面amoeba配置中多了一个IP为119的原因。
  相关解决方案

代码迷 - 您身边的软件异常，代码异常，编程异常助手（发现，解决，分享）
Copyright © 2009-2013 DAIMAMI.COM. All rights reserved.