mysql读写分离的完整配置

参考文章：

文章一【仅供参考】：

构建高性能web之路------mysql读写分离实战【按照里面配置主从mysql同步失败，并且按照他的my.cnf配置，给我的虚拟机搞坏了，重新弄了一个】

http://blog.csdn.net/cutesource/article/details/5710645

文章二【仅供参考，主要参考这个】：

mysql主从配置，实现读写分离【他这个是把主mysql中的所有数据库都要进行同步】

http://www.cnblogs.com/alvin_xp/p/4162249.html

---------------------------------------------------------------------一一、搭建mysql的master-slave环境---------------------------------------------------------------------

大型网站为了软解大量的并发访问，除了在网站实现分布式负载均衡，远远不够。到了数据业务层、数据访问层，如果还是传统的数据结构，或者只是单单靠一台服务器扛，如此多的数据库连接操作，数据库必然会崩溃，数据丢失的话，后果更是 不堪设想。这时候，我们会考虑如何减少数据库的联接，一方面采用优秀的代码框架，进行代码的优化，采用优秀的数据缓存技术如：memcached,如果资金丰厚的话，必然会想到假设服务器群，来分担主数据库的压力。Ok切入今天微博主题，利用MySQL主从配置，实现读写分离，减轻数据库压力。这种方式，在如今很多网站里都有使用，也不是什么新鲜事情，今天总结一下，方便大家学习参考一下。

【以下内容参考此文章：http://blog.csdn.net/cutesource/article/details/5710645】

一个完整的mysql读写分离环境包括以下几个部分：

• 应用程序client
• database proxy
• database集群

在本次实战中，应用程序client基于c3p0连接后端的database proxy。database proxy负责管理client实际访问database的路由策略，采用开源框架amoeba。database集群采用mysql的master-slave的replication方案。整个环境的结构图如下所示：

【以下内容参考文章：http://bbs.linuxtone.org/thread-24935-1-1.html】

概述：搭设一台Master服务器(虚拟机------一台centos6.5-mini   ip:192.168.1.145)，搭设两台Slave服务器（虚拟机——两台centos6.5-mini ip:192.168.1.152和ip:192.168.1.160）

原理：主服务器（Master）负责网站NonQuery操作，从服务器负责Query操作，用户可以根据网站功能模特性块固定访问Slave服务器，或者自己写个池或队列，自由为请求分配从服务器连接。主从服务器利用MySQL的二进制日志文件，实现数据同步。二进制日志由主服务器产生，从服务器响应获取同步数据库。

具体实现：

1、在主从服务器上都装上MySQL数据库，windows系统鄙人安装的是mysql_5.5.25.msi版本，Ubuntu安装的是mysql-5.6.22-linux-glibc2.5-i686.tar

windows安装mysql就不谈了，一般地球人都应该会。鄙人稍微说一下Ubuntu的MySQL安装，我建议不要在线下载安装，还是离线安装的好。大家可以参考 http://www.linuxidc.com/Linux/2013-01/78716.htm 这位不知道大哥还是姐妹，写的挺好按照这个就能装上。在安装的时候可能会出现几种现象，大家可以参考解决一下：

（1）如果您不是使用root用户登录，建议 su - root 切换到Root用户安装，那就不用老是 sudo 了。

（2）存放解压的mysql 文件夹，文件夹名字最好改成mysql

（3）在./support-files/mysql.server start 启动MySQL的时候，可能会出现一个警告，中文意思是启动服务运行读文件时，忽略了my.cnf文件，那是因为my.cnf的文件权限有问题，mysql会认为该文件有危险不会执行。但是mysql还会启动成功，但如果下面配置从服务器参数修改my.cnf文件的时候，你会发现文件改过了，但是重启服务时，修改过后的配置没有执行，而且您 list一下mysql的文件夹下会发现很多.my.cnf.swp等中间文件。这都是因为MySQL启动时没有读取my.cnf的原因。这时只要将my.cnf的文件权限改成my_new.cnf的权限一样就Ok，命令：chmod 644 my.cnf就Ok

（4）Ubuntu中修改文档内容没有Vim，最好把Vim 装上，apt-get install vim,不然估计会抓狂。

这时候我相信MySQL应该安装上去了。

【以上不是重点，mysql本机访问和远程访问都不可以的，自己查资料一定要弄好！！！！！否则后面无法进行】

2、配置Master主服务器

（1）在Master MySQL上创建一个用户‘repl’，并允许其他Slave服务器可以通过远程访问Master，通过该用户读取二进制日志，实现数据同步。  

[html] view plain copy print?

1. 创建用于读取日志的数据库用户:        
2. 1 mysql>create user repl; //创建新用户        
3. 2 //repl用户必须具有REPLICATION SLAVE权限，除此之外没有必要添加不必要的权限，密码为mysql。  
4.   //说明一下192.168.0.%，这个配置是指明repl用户所在服务器，这 里%是通配符，表示192.168.0.0-192.168.0.255的Server都可以以repl用户登陆主服务器。当然你也可以指定固定Ip。        
5. 3 mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.0.%' IDENTIFIED BY 'mysql';  

（2）找到MySQL安装文件夹修改my.Ini文件。mysql中有好几种日志方式，这不是今天的重点。我们只要启动二进制日志log-bin就ok。

 在[mysqld]下面增加下面几行代码

[html] view plain copy print?

1. my.cnf配置：  
2. 1 server-id=1   //给数据库服务的唯一标识，一般为大家设置服务器Ip的末尾号  
3. 2 log-bin=master-bin  
4. 3 log-bin-index=master-bin.index  
5.   
6. 缺东西：  
7. 3 binlog-do-db=amoeba_study #用于master-slave的具体数据库#设置二进制日志记录的库           
8. 4 binlog-ignore-db=mysql        ##设置二进制日志不记录的库  
9. 5 sync_binlog=1      作用：  
10.   
11. 【备注】：1和2的顺序颠倒后，centos6.5 bash下的某些命令不能用了！  

（3）查看日志

mysql> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000001 | 1285 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

重启MySQL服务

3、配置Slave从服务器（windows和centos6.5-mini配置一样）

（1）找到MySQL安装文件夹修改my.ini【windows】，centos6.5-mini修改/etc/my.cnf文件，在[mysqld]下面增加下面几行代码

[html] view plain copy print?

1. 1 [mysqld]  
2. 2 server-id=2 #slave的标示  
3. 3 relay-log=slave-relay-bin   
4. 4 relay-log-index=slave-relay-bin.index  
5.   
6. 缺东西：  
7. 5 replicate-do-db=amoeba_study #用于master-slave的具体数据库#设置二进制日志记录的库           
8. 6 replicate-ignore-db=mysql        ##设置二进制日志不记录的库  
9. 7 sync_binlog=1      作用：  

重启MySQL服务

（2）连接Master

[html] view plain copy print?

1. 执行下面的命令连接到master  
2. mysql> change master to  
3.     -> master_host='192.168.1.145',  
4.     -> master_user='repl',  
5.     -> master_password='mysql',  
6.     -> master_log_file='master-bin.000001',//Master服务器产生的日志  
7.     -> master_log_pos=0;  

（3）启动Slave

[html] view plain copy print?

1. mysql>start slave;  

（4）验证master-slave搭建生效
可以在当前窗口中输入
mysql>show slave status;
查看slave的状态信息。

或者
【
通过查看slave机的log（/var/log/mysqld.log）：

100703 10:51:42 [Note] Slave I/O thread: connected to master 'repl@10.20.147.110:3306',  replication started in log 'mysql-bin.000003' at position 161261

如看到以上信息则证明搭建成功，如果有问题也可通过此log找原因
】

OK所有配置都完成了，这时候大家可以在Master Mysql 中进行测试了，因为我们监视的时Master mysql  所有操作日志，所以，你的任何改变主服务器数据库的操作，都会同步到从服务器上。创建个数据库，表试试吧。。。

【备注】本实例只对database数据库amoeba_study进行同步。好啦，到此master-slave主从mysql数据库同步已经可以了！赶紧试试吧~

==============================================二.搭建database proxy=================================================

参考文章：

文章一【仅供参考】：

构建高性能web之路------mysql读写分离实战

http://blog.csdn.net/cutesource/article/details/5710645

文章二【仅供参考，主要参考这个】：

amoeba for mysql的读写分离配置

http://bbs.linuxtone.org/thread-24935-1-1.html

-----------------------------------------------------------------------------------

此次实战中database proxy采用amoeba，它的相关信息可以查阅官方文档，不在此详述

1）安装amoeba

下载amoeba-mysql-binary-2.1.0-RC5 后解压到本地（D:/openSource/amoeba-mysql-binary-2.1.0-RC5），即完成安装

2） 配置Amoeba for MySQL：
Amoeba for MySQL的使用是很简单的，主要是通过xml文件来实现的。
1. 配置文件介绍：
(1.) dbServers.xml   想象Amoeba作为数据库代理层，它一定会和很多数据库保持通信，因此它必须知道由它代理的数据库如何连接，比如最基础的：主机IP、端口、Amoeba使用的用户名和密码等等。这些信息存储在$AMOEBA_HOME/conf/dbServers.xml中。
(2.) rule.xml  Amoeba为了完成数据切分提供了完善的切分规则配置，为了了解如何分片数据、如何将数据库返回的数据整合，它必须知道切分规则。与切分规则相关的信息存储在$AMOEBA_HOME/conf/rule.xml中。
(3.) functionMap.xml  当我们书写SQL来操作数据库的时候，常常会用到很多不同的数据库函数，比如：UNIX_TIMESTAMP()、SYSDATE()等等。这些函数如何被Amoeba解析呢？$AMOEBA_HOME/conf/functionMap.xml描述了函数名和函数处理的关系。
(4.) ruleFunctionMap.xml  对$AMOEBA_HOME/conf/rule.xml进行配置时，会用到一些我们自己定义的函数，比如我们需要对用户ID求HASH值来切分数据，这些函数在$AMOEBA_HOME/conf/ruleFunctionMap.xml中定义。
(5.) access_list.conf  Amoeba可以制定一些可访问以及拒绝访问的主机IP地址，这部分配置在$AMOEBA_HOME/conf/access_list.conf中。
(6.) log4j.xml  Amoeba允许用户配置输出日志级别以及方式，配置方法使用log4j的文件格式，文件是$AMOEBA_HOME/conf/log4j.xml。

先配置proxy连接和与各后端mysql服务器连接信息其中，

我们主要用到dbServers.xml 和 amoeba.xml 。

【dbServers.xml 的配置：】
 

[html] view plain copy print?

1.    <dbServer name=”abstractServer” abstractive=”true”>  
2.     3306<!—代理连接数据库使用的端口号-->  
3.     amoeba_study<!—代理连接数据库所使用的数据库-->  
4.     root<!—代理连接数据库使用的用户名-->  
5.     000000asd<!—代理连接数据库使用的密码-->  
6.     </dbServer>  
7. <!--- 配置真实的数据库的地址-->  
8. <!--- 配置主从服务器及服务器连接池-->  
9. <dbServer name="<span style="color:#FF0000;">server1</span>"  parent="<span style="color:#FF0000;">abstractServer</span>">  
10.     <factoryConfig>  
11.     <!-- mysql ip -->  
12.     <property name="<span style="color:#FF0000;">ipAddress</span>">192.168.0.162</property>  
13.     <!--必须有以下配置，否则会查询或者插入数据的时候，失败！！！！！！！！！！！！！！-->  
14.     <property name="<span style="color:#FF0000;">user</span>">root</property>  
15.     <property name="<span style="color:#FF0000;">password</span>">000000asd</property>  
16.     </factoryConfig>  
17. </dbServer>  
18.   
19. <dbServer name=”<span style="color:#FF0000;">server2</span>”  parent=”<span style="color:#FF0000;">abstractServer</span>”>  
20.     <factoryConfig>  
21.     <!—mysql ip -->  
22.     <property name=”<span style="color:#FF0000;">ipAddress</span>”>192.168.0.171</property>  
23.     <!--必须有以下配置，否则会查询或者插入数据的时候，失败！！！！！！！！！！！！！！-->  
24.     <property name="<span style="color:#FF0000;">user</span>">root</property>  
25.     <property name="<span style="color:#FF0000;">password</span>">000000asd</property>  
26.     </factoryConfig>  
27. </dbServer>  
28.   
29. <dbServer name=”<span style="color:#FF0000;">master</span>” virtual=”<span style="color:#FF0000;">true</span>”>  
30.     <poolConfig class=”com.meidusa.amoeba.server.MultipleServerPool”>  
31.         <!—Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->  
32.         <property name=”<span style="color:#FF0000;">loadbalance</span>”>1</property>  
33.         <!—Separated by commas,such as: server1,server2,server1 -->  
34.         <property name=”<span style="color:#FF0000;">poolNames</span>”>server1</property>  
35.     </poolConfig>  
36. </dbServer>  
37. <dbServer name=”<span style="color:#FF0000;">slave</span>” virtual=”<span style="color:#FF0000;">true</span>”>  
38.     <poolConfig class=”com.meidusa.amoeba.server.MultipleServerPool”>  
39.         <!—Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->  
40.         <property name=”<span style="color:#FF0000;">loadbalance</span>”>1</property>  
41.         <!—Separated by commas,such as: server1,server2,server1 -->  
42.         <property name=”<span style="color:#FF0000;">poolNames</span>”>server2</property>  
43.     </poolConfig>  
44. </dbServer>  

【amoeba.xml 配置：】

[html] view plain copy print?

1. 客户端连接Amoeba时所绑定的IP地址、端口、用户名和密码。及IP访问限制。  
2. <service name="Amoeba for Mysql" >  
3.     <!-- port -->  
4.     <property name="port">8066</property>  
5.     <!-- bind ipAddress -->  
6.     <!-- 
7.     <property name="ipAddress">127.0.0.1</property> 
8.     -->  
9.     <property name="ipAddress"><span style="color:#FF0000;">192.168.1.145</span></property>  
10.     <property name="connectionFactory">  
11.         <bean >  
12.             <property name="sendBufferSize">128</property>  
13.             <property name="receiveBufferSize">64</property>  
14.         </bean>  
15.     </property>  
16.     <property name="authenticateProvider">  
17.         <bean >  
18.             <property name="<span style="color:#FF0000;">user</span>">root</property>  
19.             <property name="<span style="color:#FF0000;">password</span>">000000asd</property>  
20.             <property name="filter">  
21.                 <bean>  
22.                     <property name="ipFile">${amoeba.home}/conf/access_list.conf</property>  
23.                 </bean>  
24.             </property>  
25.         </bean>  
26.     </property>  
27. </service>  
28.   
29. 以下内容是定义读写分离：  
30.   
31. <queryRouter >  
32.     <property name="ruleLoader">  
33.         <bean >  
34.             <property name="ruleFile">${amoeba.home}/conf/rule.xml</property>  
35.             <property name="functionFile">${amoeba.home}/conf/ruleFunctionMap.xml</property>  
36.         </bean>  
37.     </property>  
38.     <property name="sqlFunctionFile">${amoeba.home}/conf/functionMap.xml</property>  
39.     <property name="LRUMapSize">1500</property>  
40.     <property name="<span style="color:#FF0000;">defaultPool</span>">master</property>  
41.     <property name="<span style="color:#FF0000;">writePool</span>">master</property>  
42.     <property name="<span style="color:#FF0000;">readPool</span>">slave</property>  
43.     <property name="<span style="color:#FF0000;">needParse</span>">true</property>  
44. </queryRouter>  

===============================
amoeba配置完成后：

3）Amoeba启动：sh amoeba start

启动成功后信息如下：

[html] view plain copy print?

1. [root@mmyr3 bin]# sh amoeba start  
2. log4j:WARN log4j config load completed from file:/usr/amoeba-mysql-binary-2.1.0-RC5/conf/log4j.xml  
3. 2015-07-30 00:34:46,888 INFO  context.MysqlRuntimeContext - Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-2.1.0-RC5  
4. log4j:WARN ip access config load completed from file:/usr/amoeba-mysql-binary-2.1.0-RC5/conf/access_list.conf  
5. 2015-07-30 00:34:47,357 INFO  net.ServerableConnectionManager - Amoeba for Mysql listening on /192.168.1.145:8066.  

【备注：由于我把主从数据库代理amoeba绑定到ip192.168.1.145上所有此处是这样子！！！！】

启动成功后，在客户端连接Amoeba测试：：

mysql -u root -p000000asd -h 192.168.1.145 --port 8066
【备注：上面的一条命令修改对应的用户名和密码，是没有问题的，如果连接不上，检查一下要连接的master机上的mysqld服务是否已经启动】

mysql> CREATE DATABASE  minunix;  \\  创建数据库，之后在主从库分别查看

通过Amoeba登录，进行数据的查询及插入更新等操作，并查看mysql-log日志，可发现所执行的INSERT 、UPDATE、DELETE等操作在主库server1上操作，SELECT查询语句在从库server2上执行。

mysql数据库的操作日志在哪里【】

这个你可以看配置文件 启用了才有这样的记录默认是没有的/etc/my.conflog-bin = mysqlbin一般放在/var/lib/mysql比如上面的设置重启数据库会生成mysqlbin.000001文件

/var/log/mysqld.log

/var/lib/mysql/mysql_log.log

/var/lib/mysql/master-bin.000003

======================================搭建amoeba主从数据库代理过程中遇到的错误=============================================

一：这个估计是验证失败，网上有的说是需要设置主主同步【参考网址：http://blog.sina.com.cn/s/blog_73164f5f0101958x.html】
    java.lang.Exception: poolName=master, no valid pools
    at com.meidusa.amoeba.net.poolable.MultipleLoadBalanceObjectPool.borrowObject(MultipleLoadBalanceObjectPool.java:183)
    at com.meidusa.amoeba.mysql.handler.CommandMessageHandler.startSession(CommandMessageHandler.java:629)
    at com.meidusa.amoeba.mysql.handler.MySqlCommandDispatcher.handleMessage(MySqlCommandDispatcher.java:123)
    at com.meidusa.amoeba.mysql.net.MysqlClientConnection$2.run(MysqlClientConnection.java:291)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    at java.lang.Thread.run(Thread.java:745)
二：这个是第二个pool没有配置用户名和密码导致的貌似
java.util.NoSuchElementException: Could not create a validated object, cause: ValidateObject failed
    at org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:1191)
    at com.meidusa.amoeba.net.poolable.GenericObjectPool.borrowObject(GenericObjectPool.java:381)
    at com.meidusa.amoeba.mysql.handler.CommandMessageHandler.startSession(CommandMessageHandler.java:629)
    at com.meidusa.amoeba.mysql.handler.MySqlCommandDispatcher.handleMessage(MySqlCommandDispatcher.java:123)
    at com.meidusa.amoeba.mysql.net.MysqlClientConnection$2.run(MysqlClientConnection.java:291)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    at java.lang.Thread.run(Thread.java:745)
==============================================三.client端调用与测试=================================================

参考文章：

http://blog.csdn.net/cutesource/article/details/5710645

1）编写client调用程序

具体程序细节就不详述了，只是一个最普通的基于mysql driver的jdbc的数据库操作程序

2）配置数据库连接

本client基于c3p0，具体数据源配置如下：

[xhtml] view plaincopy

1. <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"  
2.     destroy-method="close">  
3.     <property name="driverClass" value="com.mysql.jdbc.Driver" />  
4.     <property name="jdbcUrl" value="jdbc:mysql://localhost:8066/amoeba_study" />  
5.     <property name="user" value="root" />  
6.     <property name="password" value="root" />  
7.     <property name="minPoolSize" value="1" />  
8.     <property name="maxPoolSize" value="1" />  
9.     <property name="maxIdleTime" value="1800" />  
10.     <property name="acquireIncrement" value="1" />  
11.     <property name="maxStatements" value="0" />  
12.     <property name="initialPoolSize" value="1" />  
13.     <property name="idleConnectionTestPeriod" value="1800" />  
14.     <property name="acquireRetryAttempts" value="6" />  
15.     <property name="acquireRetryDelay" value="1000" />  
16.     <property name="breakAfterAcquireFailure" value="false" />  
17.     <property name="testConnectionOnCheckout" value="true" />  
18.     <property name="testConnectionOnCheckin" value="false" />  
19. </bean>  

值得注意是，client端只需连到proxy，与实际的数据库没有任何关系，因此jdbcUrl、user、password配置都对应于amoeba暴露出来的配置信息

3）调用与测试

首先插入一条数据：insert into zone_by_id(id,name) values(20003,'name_20003')

通过查看master机上的日志/var/lib/mysql/mysql_log.log：

100703 11:58:42       1 Query       set names latin1
                      1 Query       SET NAMES latin1
                      1 Query       SET character_set_results = NULL
                      1 Query       SHOW VARIABLES
                      1 Query       SHOW COLLATION
                      1 Query       SET autocommit=1
                      1 Query       SET sql_mode='STRICT_TRANS_TABLES'
                      1 Query       SHOW VARIABLES LIKE 'tx_isolation'
                      1 Query       SHOW FULL TABLES FROM `amoeba_study` LIKE 'PROBABLYNOT'
                      1 Prepare     [1] insert into zone_by_id(id,name) values(?,?)
                      1 Prepare     [2] insert into zone_by_id(id,name) values(?,?)          
                      1 Execute     [2] insert into zone_by_id(id,name) values(20003,'name_20003')

得知写操作发生在master机上

通过查看slave机上的日志/var/lib/mysql/mysql_log.log：

100703 11:58:42       2 Query       insert into zone_by_id(id,name) values(20003,'name_20003')

得知slave同步执行了这条语句

然后查一条数据：select t.name from zone_by_id t where t.id = 20003

通过查看slave机上的日志/var/lib/mysql/mysql_log.log：

100703 12:02:00      33 Query       set names latin1
                     33 Prepare     [1] select t.name from zone_by_id t where t.id = ?
                     33 Prepare     [2] select t.name from zone_by_id t where t.id = ?   
                     33 Execute     [2] select t.name from zone_by_id t where t.id = 20003 

得知读操作发生在slave机上

并且通过查看slave机上的日志/var/lib/mysql/mysql_log.log发现这条语句没在master上执行

 

通过以上验证得知简单的master-slave搭建和实战得以生效

----------------自己：简单测试一下，看DAO是否能连接到主从数据库代理amoeba，代码如下：------------

[html] view plain copy print?

1. package amoeba_test;  
2.   
3. import java.sql.Connection;  
4. import java.sql.DriverManager;  
5. import java.sql.SQLException;  
6.   
7. public class SQLHelper {  
8.     public static void main(String[] args) {  
9.         try {  
10.             // 加载MySql的驱动类  
11.             Class.forName("com.mysql.jdbc.Driver");  
12.         } catch (ClassNotFoundException e) {  
13.             System.out.println("找不到驱动程序类 ，加载驱动失败！");  
14.             e.printStackTrace();  
15.         }  
16.           
17.         //连接152上的mysql数据库：  
18. //      jdbc:mysql://192.168.1.152:3306/test  
19.         String url = "jdbc:mysql://192.168.1.145:8066/amoeba_study";  
20.         String username = "root";  
21.         String password = "000000asd";  
22.           try{     
23.                 Connection con =DriverManager.getConnection(url , username , password ) ;     
24.                 if(con!=null){  
25.                     System.out.println("successfully connected");  
26.                 }  
27.                  }catch(SQLException se){     
28.                 System.out.println("数据库连接失败！");     
29.                 se.printStackTrace() ;     
30.                  }      
31.   
32.     }  
33. }  

参考文章：

文章一【仅供参考】：

构建高性能web之路------mysql读写分离实战【按照里面配置主从mysql同步失败，并且按照他的my.cnf配置，给我的虚拟机搞坏了，重新弄了一个】

http://blog.csdn.net/cutesource/article/details/5710645

文章二【仅供参考，主要参考这个】：

mysql主从配置，实现读写分离【他这个是把主mysql中的所有数据库都要进行同步】

http://www.cnblogs.com/alvin_xp/p/4162249.html

---------------------------------------------------------------------一一、搭建mysql的master-slave环境---------------------------------------------------------------------

大型网站为了软解大量的并发访问，除了在网站实现分布式负载均衡，远远不够。到了数据业务层、数据访问层，如果还是传统的数据结构，或者只是单单靠一台服务器扛，如此多的数据库连接操作，数据库必然会崩溃，数据丢失的话，后果更是 不堪设想。这时候，我们会考虑如何减少数据库的联接，一方面采用优秀的代码框架，进行代码的优化，采用优秀的数据缓存技术如：memcached,如果资金丰厚的话，必然会想到假设服务器群，来分担主数据库的压力。Ok切入今天微博主题，利用MySQL主从配置，实现读写分离，减轻数据库压力。这种方式，在如今很多网站里都有使用，也不是什么新鲜事情，今天总结一下，方便大家学习参考一下。

【以下内容参考此文章：http://blog.csdn.net/cutesource/article/details/5710645】

一个完整的mysql读写分离环境包括以下几个部分：

• 应用程序client
• database proxy
• database集群

在本次实战中，应用程序client基于c3p0连接后端的database proxy。database proxy负责管理client实际访问database的路由策略，采用开源框架amoeba。database集群采用mysql的master-slave的replication方案。整个环境的结构图如下所示：

【以下内容参考文章：http://bbs.linuxtone.org/thread-24935-1-1.html】

概述：搭设一台Master服务器(虚拟机------一台centos6.5-mini   ip:192.168.1.145)，搭设两台Slave服务器（虚拟机——两台centos6.5-mini ip:192.168.1.152和ip:192.168.1.160）

原理：主服务器（Master）负责网站NonQuery操作，从服务器负责Query操作，用户可以根据网站功能模特性块固定访问Slave服务器，或者自己写个池或队列，自由为请求分配从服务器连接。主从服务器利用MySQL的二进制日志文件，实现数据同步。二进制日志由主服务器产生，从服务器响应获取同步数据库。

具体实现：

1、在主从服务器上都装上MySQL数据库，windows系统鄙人安装的是mysql_5.5.25.msi版本，Ubuntu安装的是mysql-5.6.22-linux-glibc2.5-i686.tar

windows安装mysql就不谈了，一般地球人都应该会。鄙人稍微说一下Ubuntu的MySQL安装，我建议不要在线下载安装，还是离线安装的好。大家可以参考 http://www.linuxidc.com/Linux/2013-01/78716.htm 这位不知道大哥还是姐妹，写的挺好按照这个就能装上。在安装的时候可能会出现几种现象，大家可以参考解决一下：

（1）如果您不是使用root用户登录，建议 su - root 切换到Root用户安装，那就不用老是 sudo 了。

（2）存放解压的mysql 文件夹，文件夹名字最好改成mysql

（3）在./support-files/mysql.server start 启动MySQL的时候，可能会出现一个警告，中文意思是启动服务运行读文件时，忽略了my.cnf文件，那是因为my.cnf的文件权限有问题，mysql会认为该文件有危险不会执行。但是mysql还会启动成功，但如果下面配置从服务器参数修改my.cnf文件的时候，你会发现文件改过了，但是重启服务时，修改过后的配置没有执行，而且您 list一下mysql的文件夹下会发现很多.my.cnf.swp等中间文件。这都是因为MySQL启动时没有读取my.cnf的原因。这时只要将my.cnf的文件权限改成my_new.cnf的权限一样就Ok，命令：chmod 644 my.cnf就Ok

（4）Ubuntu中修改文档内容没有Vim，最好把Vim 装上，apt-get install vim,不然估计会抓狂。

这时候我相信MySQL应该安装上去了。

【以上不是重点，mysql本机访问和远程访问都不可以的，自己查资料一定要弄好！！！！！否则后面无法进行】

2、配置Master主服务器

（1）在Master MySQL上创建一个用户‘repl’，并允许其他Slave服务器可以通过远程访问Master，通过该用户读取二进制日志，实现数据同步。  

[html] view plain copy print?

1. 创建用于读取日志的数据库用户:        
2. 1 mysql>create user repl; //创建新用户        
3. 2 //repl用户必须具有REPLICATION SLAVE权限，除此之外没有必要添加不必要的权限，密码为mysql。  
4.   //说明一下192.168.0.%，这个配置是指明repl用户所在服务器，这 里%是通配符，表示192.168.0.0-192.168.0.255的Server都可以以repl用户登陆主服务器。当然你也可以指定固定Ip。        
5. 3 mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.0.%' IDENTIFIED BY 'mysql';  

（2）找到MySQL安装文件夹修改my.Ini文件。mysql中有好几种日志方式，这不是今天的重点。我们只要启动二进制日志log-bin就ok。

 在[mysqld]下面增加下面几行代码

[html] view plain copy print?

1. my.cnf配置：  
2. 1 server-id=1   //给数据库服务的唯一标识，一般为大家设置服务器Ip的末尾号  
3. 2 log-bin=master-bin  
4. 3 log-bin-index=master-bin.index  
5.   
6. 缺东西：  
7. 3 binlog-do-db=amoeba_study #用于master-slave的具体数据库#设置二进制日志记录的库           
8. 4 binlog-ignore-db=mysql        ##设置二进制日志不记录的库  
9. 5 sync_binlog=1      作用：  
10.   
11. 【备注】：1和2的顺序颠倒后，centos6.5 bash下的某些命令不能用了！  

（3）查看日志

mysql> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000001 | 1285 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

重启MySQL服务

3、配置Slave从服务器（windows和centos6.5-mini配置一样）

（1）找到MySQL安装文件夹修改my.ini【windows】，centos6.5-mini修改/etc/my.cnf文件，在[mysqld]下面增加下面几行代码

[html] view plain copy print?

1. 1 [mysqld]  
2. 2 server-id=2 #slave的标示  
3. 3 relay-log=slave-relay-bin   
4. 4 relay-log-index=slave-relay-bin.index  
5.   
6. 缺东西：  
7. 5 replicate-do-db=amoeba_study #用于master-slave的具体数据库#设置二进制日志记录的库           
8. 6 replicate-ignore-db=mysql        ##设置二进制日志不记录的库  
9. 7 sync_binlog=1      作用：  

重启MySQL服务

（2）连接Master

[html] view plain copy print?

1. 执行下面的命令连接到master  
2. mysql> change master to  
3.     -> master_host='192.168.1.145',  
4.     -> master_user='repl',  
5.     -> master_password='mysql',  
6.     -> master_log_file='master-bin.000001',//Master服务器产生的日志  
7.     -> master_log_pos=0;  

（3）启动Slave

[html] view plain copy print?

1. mysql>start slave;  

（4）验证master-slave搭建生效
可以在当前窗口中输入
mysql>show slave status;
查看slave的状态信息。

或者
【
通过查看slave机的log（/var/log/mysqld.log）：

100703 10:51:42 [Note] Slave I/O thread: connected to master 'repl@10.20.147.110:3306',  replication started in log 'mysql-bin.000003' at position 161261

如看到以上信息则证明搭建成功，如果有问题也可通过此log找原因
】

OK所有配置都完成了，这时候大家可以在Master Mysql 中进行测试了，因为我们监视的时Master mysql  所有操作日志，所以，你的任何改变主服务器数据库的操作，都会同步到从服务器上。创建个数据库，表试试吧。。。

【备注】本实例只对database数据库amoeba_study进行同步。好啦，到此master-slave主从mysql数据库同步已经可以了！赶紧试试吧~

==============================================二.搭建database proxy=================================================

参考文章：

文章一【仅供参考】：

构建高性能web之路------mysql读写分离实战

http://blog.csdn.net/cutesource/article/details/5710645

文章二【仅供参考，主要参考这个】：

amoeba for mysql的读写分离配置

http://bbs.linuxtone.org/thread-24935-1-1.html

-----------------------------------------------------------------------------------

此次实战中database proxy采用amoeba，它的相关信息可以查阅官方文档，不在此详述

1）安装amoeba

下载amoeba-mysql-binary-2.1.0-RC5 后解压到本地（D:/openSource/amoeba-mysql-binary-2.1.0-RC5），即完成安装

2） 配置Amoeba for MySQL：
Amoeba for MySQL的使用是很简单的，主要是通过xml文件来实现的。
1. 配置文件介绍：
(1.) dbServers.xml   想象Amoeba作为数据库代理层，它一定会和很多数据库保持通信，因此它必须知道由它代理的数据库如何连接，比如最基础的：主机IP、端口、Amoeba使用的用户名和密码等等。这些信息存储在$AMOEBA_HOME/conf/dbServers.xml中。
(2.) rule.xml  Amoeba为了完成数据切分提供了完善的切分规则配置，为了了解如何分片数据、如何将数据库返回的数据整合，它必须知道切分规则。与切分规则相关的信息存储在$AMOEBA_HOME/conf/rule.xml中。
(3.) functionMap.xml  当我们书写SQL来操作数据库的时候，常常会用到很多不同的数据库函数，比如：UNIX_TIMESTAMP()、SYSDATE()等等。这些函数如何被Amoeba解析呢？$AMOEBA_HOME/conf/functionMap.xml描述了函数名和函数处理的关系。
(4.) ruleFunctionMap.xml  对$AMOEBA_HOME/conf/rule.xml进行配置时，会用到一些我们自己定义的函数，比如我们需要对用户ID求HASH值来切分数据，这些函数在$AMOEBA_HOME/conf/ruleFunctionMap.xml中定义。
(5.) access_list.conf  Amoeba可以制定一些可访问以及拒绝访问的主机IP地址，这部分配置在$AMOEBA_HOME/conf/access_list.conf中。
(6.) log4j.xml  Amoeba允许用户配置输出日志级别以及方式，配置方法使用log4j的文件格式，文件是$AMOEBA_HOME/conf/log4j.xml。

先配置proxy连接和与各后端mysql服务器连接信息其中，

我们主要用到dbServers.xml 和 amoeba.xml 。

【dbServers.xml 的配置：】
 

[html] view plain copy print?

1.    <dbServer name=”abstractServer” abstractive=”true”>  
2.     3306<!—代理连接数据库使用的端口号-->  
3.     amoeba_study<!—代理连接数据库所使用的数据库-->  
4.     root<!—代理连接数据库使用的用户名-->  
5.     000000asd<!—代理连接数据库使用的密码-->  
6.     </dbServer>  
7. <!--- 配置真实的数据库的地址-->  
8. <!--- 配置主从服务器及服务器连接池-->  
9. <dbServer name="<span style="color:#FF0000;">server1</span>"  parent="<span style="color:#FF0000;">abstractServer</span>">  
10.     <factoryConfig>  
11.     <!-- mysql ip -->  
12.     <property name="<span style="color:#FF0000;">ipAddress</span>">192.168.0.162</property>  
13.     <!--必须有以下配置，否则会查询或者插入数据的时候，失败！！！！！！！！！！！！！！-->  
14.     <property name="<span style="color:#FF0000;">user</span>">root</property>  
15.     <property name="<span style="color:#FF0000;">password</span>">000000asd</property>  
16.     </factoryConfig>  
17. </dbServer>  
18.   
19. <dbServer name=”<span style="color:#FF0000;">server2</span>”  parent=”<span style="color:#FF0000;">abstractServer</span>”>  
20.     <factoryConfig>  
21.     <!—mysql ip -->  
22.     <property name=”<span style="color:#FF0000;">ipAddress</span>”>192.168.0.171</property>  
23.     <!--必须有以下配置，否则会查询或者插入数据的时候，失败！！！！！！！！！！！！！！-->  
24.     <property name="<span style="color:#FF0000;">user</span>">root</property>  
25.     <property name="<span style="color:#FF0000;">password</span>">000000asd</property>  
26.     </factoryConfig>  
27. </dbServer>  
28.   
29. <dbServer name=”<span style="color:#FF0000;">master</span>” virtual=”<span style="color:#FF0000;">true</span>”>  
30.     <poolConfig class=”com.meidusa.amoeba.server.MultipleServerPool”>  
31.         <!—Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->  
32.         <property name=”<span style="color:#FF0000;">loadbalance</span>”>1</property>  
33.         <!—Separated by commas,such as: server1,server2,server1 -->  
34.         <property name=”<span style="color:#FF0000;">poolNames</span>”>server1</property>  
35.     </poolConfig>  
36. </dbServer>  
37. <dbServer name=”<span style="color:#FF0000;">slave</span>” virtual=”<span style="color:#FF0000;">true</span>”>  
38.     <poolConfig class=”com.meidusa.amoeba.server.MultipleServerPool”>  
39.         <!—Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->  
40.         <property name=”<span style="color:#FF0000;">loadbalance</span>”>1</property>  
41.         <!—Separated by commas,such as: server1,server2,server1 -->  
42.         <property name=”<span style="color:#FF0000;">poolNames</span>”>server2</property>  
43.     </poolConfig>  
44. </dbServer>  

【amoeba.xml 配置：】

[html] view plain copy print?

1. 客户端连接Amoeba时所绑定的IP地址、端口、用户名和密码。及IP访问限制。  
2. <service name="Amoeba for Mysql" >  
3.     <!-- port -->  
4.     <property name="port">8066</property>  
5.     <!-- bind ipAddress -->  
6.     <!-- 
7.     <property name="ipAddress">127.0.0.1</property> 
8.     -->  
9.     <property name="ipAddress"><span style="color:#FF0000;">192.168.1.145</span></property>  
10.     <property name="connectionFactory">  
11.         <bean >  
12.             <property name="sendBufferSize">128</property>  
13.             <property name="receiveBufferSize">64</property>  
14.         </bean>  
15.     </property>  
16.     <property name="authenticateProvider">  
17.         <bean >  
18.             <property name="<span style="color:#FF0000;">user</span>">root</property>  
19.             <property name="<span style="color:#FF0000;">password</span>">000000asd</property>  
20.             <property name="filter">  
21.                 <bean>  
22.                     <property name="ipFile">${amoeba.home}/conf/access_list.conf</property>  
23.                 </bean>  
24.             </property>  
25.         </bean>  
26.     </property>  
27. </service>  
28.   
29. 以下内容是定义读写分离：  
30.   
31. <queryRouter >  
32.     <property name="ruleLoader">  
33.         <bean >  
34.             <property name="ruleFile">${amoeba.home}/conf/rule.xml</property>  
35.             <property name="functionFile">${amoeba.home}/conf/ruleFunctionMap.xml</property>  
36.         </bean>  
37.     </property>  
38.     <property name="sqlFunctionFile">${amoeba.home}/conf/functionMap.xml</property>  
39.     <property name="LRUMapSize">1500</property>  
40.     <property name="<span style="color:#FF0000;">defaultPool</span>">master</property>  
41.     <property name="<span style="color:#FF0000;">writePool</span>">master</property>  
42.     <property name="<span style="color:#FF0000;">readPool</span>">slave</property>  
43.     <property name="<span style="color:#FF0000;">needParse</span>">true</property>  
44. </queryRouter>  

===============================
amoeba配置完成后：

3）Amoeba启动：sh amoeba start

启动成功后信息如下：

[html] view plain copy print?

1. [root@mmyr3 bin]# sh amoeba start  
2. log4j:WARN log4j config load completed from file:/usr/amoeba-mysql-binary-2.1.0-RC5/conf/log4j.xml  
3. 2015-07-30 00:34:46,888 INFO  context.MysqlRuntimeContext - Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-2.1.0-RC5  
4. log4j:WARN ip access config load completed from file:/usr/amoeba-mysql-binary-2.1.0-RC5/conf/access_list.conf  
5. 2015-07-30 00:34:47,357 INFO  net.ServerableConnectionManager - Amoeba for Mysql listening on /192.168.1.145:8066.  

【备注：由于我把主从数据库代理amoeba绑定到ip192.168.1.145上所有此处是这样子！！！！】

启动成功后，在客户端连接Amoeba测试：：

mysql -u root -p000000asd -h 192.168.1.145 --port 8066
【备注：上面的一条命令修改对应的用户名和密码，是没有问题的，如果连接不上，检查一下要连接的master机上的mysqld服务是否已经启动】

mysql> CREATE DATABASE  minunix;  \\  创建数据库，之后在主从库分别查看

通过Amoeba登录，进行数据的查询及插入更新等操作，并查看mysql-log日志，可发现所执行的INSERT 、UPDATE、DELETE等操作在主库server1上操作，SELECT查询语句在从库server2上执行。

mysql数据库的操作日志在哪里【】

这个你可以看配置文件 启用了才有这样的记录默认是没有的/etc/my.conflog-bin = mysqlbin一般放在/var/lib/mysql比如上面的设置重启数据库会生成mysqlbin.000001文件

/var/log/mysqld.log

/var/lib/mysql/mysql_log.log

/var/lib/mysql/master-bin.000003

======================================搭建amoeba主从数据库代理过程中遇到的错误=============================================

一：这个估计是验证失败，网上有的说是需要设置主主同步【参考网址：http://blog.sina.com.cn/s/blog_73164f5f0101958x.html】
    java.lang.Exception: poolName=master, no valid pools
    at com.meidusa.amoeba.net.poolable.MultipleLoadBalanceObjectPool.borrowObject(MultipleLoadBalanceObjectPool.java:183)
    at com.meidusa.amoeba.mysql.handler.CommandMessageHandler.startSession(CommandMessageHandler.java:629)
    at com.meidusa.amoeba.mysql.handler.MySqlCommandDispatcher.handleMessage(MySqlCommandDispatcher.java:123)
    at com.meidusa.amoeba.mysql.net.MysqlClientConnection$2.run(MysqlClientConnection.java:291)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    at java.lang.Thread.run(Thread.java:745)
二：这个是第二个pool没有配置用户名和密码导致的貌似
java.util.NoSuchElementException: Could not create a validated object, cause: ValidateObject failed
    at org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:1191)
    at com.meidusa.amoeba.net.poolable.GenericObjectPool.borrowObject(GenericObjectPool.java:381)
    at com.meidusa.amoeba.mysql.handler.CommandMessageHandler.startSession(CommandMessageHandler.java:629)
    at com.meidusa.amoeba.mysql.handler.MySqlCommandDispatcher.handleMessage(MySqlCommandDispatcher.java:123)
    at com.meidusa.amoeba.mysql.net.MysqlClientConnection$2.run(MysqlClientConnection.java:291)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    at java.lang.Thread.run(Thread.java:745)
==============================================三.client端调用与测试=================================================

参考文章：

http://blog.csdn.net/cutesource/article/details/5710645

1）编写client调用程序

具体程序细节就不详述了，只是一个最普通的基于mysql driver的jdbc的数据库操作程序

2）配置数据库连接

本client基于c3p0，具体数据源配置如下：

[xhtml] view plaincopy

1. <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"  
2.     destroy-method="close">  
3.     <property name="driverClass" value="com.mysql.jdbc.Driver" />  
4.     <property name="jdbcUrl" value="jdbc:mysql://localhost:8066/amoeba_study" />  
5.     <property name="user" value="root" />  
6.     <property name="password" value="root" />  
7.     <property name="minPoolSize" value="1" />  
8.     <property name="maxPoolSize" value="1" />  
9.     <property name="maxIdleTime" value="1800" />  
10.     <property name="acquireIncrement" value="1" />  
11.     <property name="maxStatements" value="0" />  
12.     <property name="initialPoolSize" value="1" />  
13.     <property name="idleConnectionTestPeriod" value="1800" />  
14.     <property name="acquireRetryAttempts" value="6" />  
15.     <property name="acquireRetryDelay" value="1000" />  
16.     <property name="breakAfterAcquireFailure" value="false" />  
17.     <property name="testConnectionOnCheckout" value="true" />  
18.     <property name="testConnectionOnCheckin" value="false" />  
19. </bean>  

值得注意是，client端只需连到proxy，与实际的数据库没有任何关系，因此jdbcUrl、user、password配置都对应于amoeba暴露出来的配置信息

3）调用与测试

首先插入一条数据：insert into zone_by_id(id,name) values(20003,'name_20003')

通过查看master机上的日志/var/lib/mysql/mysql_log.log：

100703 11:58:42       1 Query       set names latin1
                      1 Query       SET NAMES latin1
                      1 Query       SET character_set_results = NULL
                      1 Query       SHOW VARIABLES
                      1 Query       SHOW COLLATION
                      1 Query       SET autocommit=1
                      1 Query       SET sql_mode='STRICT_TRANS_TABLES'
                      1 Query       SHOW VARIABLES LIKE 'tx_isolation'
                      1 Query       SHOW FULL TABLES FROM `amoeba_study` LIKE 'PROBABLYNOT'
                      1 Prepare     [1] insert into zone_by_id(id,name) values(?,?)
                      1 Prepare     [2] insert into zone_by_id(id,name) values(?,?)          
                      1 Execute     [2] insert into zone_by_id(id,name) values(20003,'name_20003')

得知写操作发生在master机上

通过查看slave机上的日志/var/lib/mysql/mysql_log.log：

100703 11:58:42       2 Query       insert into zone_by_id(id,name) values(20003,'name_20003')

得知slave同步执行了这条语句

然后查一条数据：select t.name from zone_by_id t where t.id = 20003

通过查看slave机上的日志/var/lib/mysql/mysql_log.log：

100703 12:02:00      33 Query       set names latin1
                     33 Prepare     [1] select t.name from zone_by_id t where t.id = ?
                     33 Prepare     [2] select t.name from zone_by_id t where t.id = ?   
                     33 Execute     [2] select t.name from zone_by_id t where t.id = 20003 

得知读操作发生在slave机上

并且通过查看slave机上的日志/var/lib/mysql/mysql_log.log发现这条语句没在master上执行

 

通过以上验证得知简单的master-slave搭建和实战得以生效

----------------自己：简单测试一下，看DAO是否能连接到主从数据库代理amoeba，代码如下：------------

[html] view plain copy print?

1. package amoeba_test;  
2.   
3. import java.sql.Connection;  
4. import java.sql.DriverManager;  
5. import java.sql.SQLException;  
6.   
7. public class SQLHelper {  
8.     public static void main(String[] args) {  
9.         try {  
10.             // 加载MySql的驱动类  
11.             Class.forName("com.mysql.jdbc.Driver");  
12.         } catch (ClassNotFoundException e) {  
13.             System.out.println("找不到驱动程序类 ，加载驱动失败！");  
14.             e.printStackTrace();  
15.         }  
16.           
17.         //连接152上的mysql数据库：  
18. //      jdbc:mysql://192.168.1.152:3306/test  
19.         String url = "jdbc:mysql://192.168.1.145:8066/amoeba_study";  
20.         String username = "root";  
21.         String password = "000000asd";  
22.           try{     
23.                 Connection con =DriverManager.getConnection(url , username , password ) ;     
24.                 if(con!=null){  
25.                     System.out.println("successfully connected");  
26.                 }  
27.                  }catch(SQLException se){     
28.                 System.out.println("数据库连接失败！");     
29.                 se.printStackTrace() ;     
30.                  }      
31.   
32.     }  
33. }