mysql 主从服务器配置

当遇到磁盘读写的事务锁导致的系统读写能力下降的问题，可以使用主从读写分离的模式进行优化。不过读写分离会导致数据读取的延时性。

mysql的主从复制主要依赖于数据库的二级制文件进行数据库的同步功能。

参考链接：

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

http://blog.itpub.net/29870867/viewspace-1304862/

1.安装mysql 我的master 用的是win10系统 mysql版本是5.6，虚拟机 slave 系统为centos mysql 版本 5.5 （查看版本命令 mysql -V）

主从同步

2.配置master数据库

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

create user master_user;

//master_user用户必须具有REPLICATION SLAVE权限，除此之外没有必要添加不必要的权限，密码为mysql。说明一下192.168.2.%，这个配置是指明repl用户所在服务器，这里%是通配符，表示192.168.0.0-192.168.0.255的Server都可以以repl用户登陆主服务器。当然你也可以指定固定Ip。

GRANT REPLICATION SLAVE ON *.* TO 'master_user'@'192.168.2.%' IDENTIFIED BY 'mysql';

// 赋予权限

grant all privileges on *.* to 'master_user' @"192.168.2.%" identified by "mysql";

flush privileges;

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

在mysqld节点添加

server-id=1
log-bin=master-bin
binlog-do-db=master_test// 是我们要同步的数据库

重启mysql

show master status;// 用来查看配置是否起效

由于开启了binlog会导致磁盘的日志写满，

清除10天的日志

PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY);

另外可以配置

expire_logs_days = 10

在运行时修改：
show binary logs;
show variables like '%log%';
set global expire_logs_days = 10;
清除之前可以采用相应的备份策略。

3.配置slave 数据库

同样找到mysqld节点添加配置

server-id=2

重启mysql服务

systemctl restart mysql

进入mysql命名模式

mysql -uroot -p123456

创建链接

CHANGE MASTER TO MASTER_HOST='192.168.2.220',MASTER_USER='master_user',MASTER_PASSWORD='mysql',MASTER_LOG_FILE='master-bin.000006',MASTER_LOG_POS=120;

开启从服务器

start slave;

检查从服务器状态

show slave statusG;

发现报错

Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).

show variables like 'server_id';

发现从服务器的seerver_id 也是1 则确认 my.conf 无效，手动修改 server_id

set global server_id=2;

show variables like 'server_id';

并开启 slave;

再次show slave statusG;

发现报错

Got fatal error 1236 from master when reading data from binary log: 'Slave can not handle replication events with the checksum that master is configured to log; the first event 'master-bin.000006' at 120, the last event read from '.master-bin.000006' at 120, the last byte read from '.master-bin.000006' at 120.'

这个错误一般出现在master5.6,slave在低版本的情况下。这是由于5.6使用了crc32做binlog的checksum。把master的设置从crc32改到none

修改master mysqld配置

binlog-checksum = none

重启master主机

slave中重新启动slave;

show slave statusG; 发现 Slave_IO_Running: Yes 标识配置主从正确；

在master数据库的 master_test 中创建一个表发现同步到了slave数据库中~~~

通过以上，便可实现mysql的主从同步，接下来，开始读写分离的配置；

读写分离

架设数据库代理

database proxy采用amoeba ，它的相关信息可以查阅官方文档

1.安装amoeba

下载amoeba（1.2.0-GA）后解压到本地（D:/amoeba），即完成安装

2.配置amoeba

先配置proxy连接和与各后端mysql服务器连接信息，修改amoeba目录中的conf/amoeba.xml

<property name="readThreadPoolSize">20</property>

<property name="clientSideThreadPoolSize">30</property>

<property name="serverSideThreadPoolSize">30</property>

<property name="netBufferSize">128</property>

<property name="tcpNoDelay">true</property>

<property name="user">root</property>

以上是proxy提供给client的连接配置

<factoryConfig class="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory">
<property name="manager">defaultManager</property>

<property name="port">3306</property>

<property name="ipAddress">192.168.2.220</property>
<property name="schema">master_test</property>

<property name="user">root</property>

</factoryConfig>

<poolConfig class="com.meidusa.amoeba.net.poolable.PoolableObjectPool">
<property name="maxActive">200</property>
<property name="maxIdle">200</property>
<property name="minIdle">10</property>
<property name="minEvictableIdleTimeMillis">600000</property>
<property name="timeBetweenEvictionRunsMillis">600000</property>
<property name="testOnBorrow">true</property>
<property name="testWhileIdle">true</property>
</poolConfig>
</dbServer>

<factoryConfig class="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory">
<property name="manager">defaultManager</property>

<property name="port">3306</property>

<property name="ipAddress">192.168.2.55</property>
<property name="schema">master_test</property>

<property name="user">master_user</property>

<property name="password">mysql</property>

</factoryConfig>

配置两个server

以上是proxy与后端各mysql数据库服务器配置信息

最后是读写分离配置

<queryRouter class="com.meidusa.amoeba.mysql.parser.MysqlQueryRouter">
<property name="ruleConfig">${amoeba.home}/conf/rule.xml</property>
<property name="functionConfig">${amoeba.home}/conf/functionMap.xml</property>
<property name="ruleFunctionConfig">${amoeba.home}/conf/ruleFunctionMap.xml</property>
<property name="LRUMapSize">1500</property>
<property name="defaultPool">server1</property>

<property name="writePool">server1</property>
<property name="readPool">server2</property>

<property name="needParse">true</property>
</queryRouter>

读slave库，写master库

到此配置完毕

3.启动amoeba

命令行进入 /bin/amoeba.bat 执行

发生错误错误: 必须设置环境变量“JAVA_HOME”，指向JDK的安装路径

由于 amoeba是基于java的，需要安装java的运行库

故而需要安装 java jdk

http://www.oracle.com/technetwork/java/javase/downloads/jdk8-downloads-2133151.html

http://jingyan.baidu.com/article/870c6fc3257891b03fe4bef6.html

在上面的链接中下载，安装

安装并配置环境变量

完成后，再次命令行进入 /bin/amoeba.bat 执行

log4j:WARN log4j config load completed from file:D:amoebain..conflog4j.xml
log4j:WARN ip access config load completed from file:D:amoebain../conf/access_list.conf
2017-03-23 15:39:15,381 INFO net.ServerableConnectionManager - Server listening on 0.0.0.0/0.0.0.0:8066.

amoeba 数据库代理成功搭建

开始测试读写分离

实例：

$mysql_server_name='192.168.2.220:8066'; //改成自己的mysql数据库服务器

$mysql_username='root'; //改成自己的mysql数据库用户名

$mysql_password='root'; //改成自己的mysql数据库密码

$mysql_database='master_test'; //改成自己的mysql数据库名

$conn=mysql_connect($mysql_server_name,$mysql_username,$mysql_password) or die("error connecting") ; //连接数据库

mysql_query("set names 'utf8'");

mysql_select_db($mysql_database); //打开数据库

$sql ="select * from master1 "; //SQL语句

$result = mysql_query($sql,$conn); //查询
print_r($result);

查看mysql日志可以发现读操作在slave主机的mysql日志中有记录，而在master主机中没有日志记录，说明读操作只通过了slave数据库

而写操作，在master 中先有，而后在slave中亦发生了写库操作

说明读写分离配置成功~~