[分享]mysql主从以及mycat实现读写分离

MYSQL主从同步的原理

关于MYSQL的主从同步，最主要的是要了解MYSQL的主从同步是如何工作的也即主从同步的原理，通过下图能很明白的知道其工作的过程：

大致描述一下过程：从服务器的IO线程从主服务器获取二进制日志，并在本地保存为中继日志，然后通过SQL线程来在从上执行中继日志中的内容，从而使从库和主库保持一致。主从同步的详细过程如下：
1. 主服务器验证连接。
2. 主服务器为从服务器开启一个线程。
3. 从服务器将从服务器日志的偏移位告诉主服务器。
4. 主服务器检查该值是否小于当前二进制日志偏移位。
5. 如果小于，则通知从服务器来取数据。
6. 从服务器持续从主服务器取数据，直至取完，这时，从服务器线程进入睡眠，主服务器线程同时进入睡眠。
7. 当主服务器有更新时，主服务器线程被激活，并将二进制日志推送给从服务器，并通知从服务器线程进入工作状态。
8. 从服务器SQL线程执行二进制日志，随后进入睡眠状态。

主192.168.208.189

从192.168.164.26

mycat搭建目前搭建在主上，也可以单独开一台服务器搭建。

先做主从

1、方便管理先将两个mysql的root密码统一设置

update user set password=password("root3306") where user="root";

2、将两台服务器的my.cnf文件开启日志

log-bin=mysql-bin
server-id=1                  #注意从服务器写server-id=2 保证主从id不一致即可

3、在主服务器上添加允许从服务器访问过来的slave同步账号

GRANT REPLICATION SLAVE ON *.* to 'test'@'192.168.208.189' identified by 'root3306';

4、查询主服务器的日志文件名和posid号

日志文件是mysql-bin.000023

posid是215

5、从服务器上 设置slave连接 并启动slave

change master to master_host='192.168.208.189',master_user='test',master_password='root3306',master_log_file='mysql-bin.000023',master_log_pos=215;
slave start;       #start /stop 开启关闭
show slave status\G;     #查看slave状态 其中Slave_IO_Running: Yes    Slave_SQL_Running: Yes  状态yes表示成功。

接下来进行测试，在主上面创建一个数据库、表，看是否同步到从数据库。

下面要做读写分离，所以不配置互为主从，避免权限、数据来回同步。

读写分离配置

读写分离实现软件较多，综合对比选用mycat软件实现，在主服务器安装此软件。

1、先安装jdk1.7.0_80

主服务器上

java -version      #检查现有的java版本
rpm -qa | grep java   #版本低就将其卸载掉
rpm -e --nodeps  旧版本号     #卸载旧版本

下载jdk-7u80-linux-x64.tar.gz 到主服务器，并解压拷贝到/usr/java/目录

将java命令加入到环境变量中  

vi /etc/profile

然后加到最后 

JAVA_HOME=/usr/java/jdk1.7.0_80  
JRE_HOME=/usr/java/jdk1.7.0_80/jre  
PATH=$PATH:$JAVA_HOME/bin:$JRE_HOME/bin  
CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar:$JRE_HOME/lib  
export    JAVA_HOME JRE_HOME PATH CLASSPATH  

执行

source /etc/profile  刷新配置
echo $PATH    查看path
java -version   查看新的java版本

2、安装mycat

下载Mycat-server-1.4.2-20160613220038-linux.tar.gz

解压之后拷贝到/usr/local

#vi /etc/profile
export PATH=/usr/local/mycat/bin:$PATH
# source /etc/profile    刷新生效

cd  /usr/local/mycat/conf 进入位置文件目录

vi wrapper.conf         这个文件定义了mycat调用java的bin目录
wrapper.java.command=/usr/java/jdk1.7.0_80/bin/java     修改定位配置文件中java的目录

3、配置mycat代理连接到主 从的账号,这里aaaa作为后端的真实存在数据库,这里使用内网增加安全性。

grant insert,delete,update,select on aaaa.* to mycat@'192.168.%.%' identified by 'root3306'; 主服务器上
grant select on aaaa.* to mycat_r@'192.168.%.%' identified by 'root3306'; 从服务器上
revoke insert,update,delete on aaaa.* from mycat@'192.168.%.%';  从服务器上  删除mycat的从上多余的权限

打开schema.xml 配置

<mycat:schema xmlns:mycat="http://org.opencloudb/">
    <schema name="aaaa" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
    </schema>
    <!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743" 
        /> -->
    <dataNode name="dn1" dataHost="localhost1" database="aaaa" />
    <!--<dataNode name="dn4" dataHost="sequoiadb1" database="SAMPLE" />
     <dataNode name="jdbc_dn1" dataHost="jdbchost" database="db1" /> 
    <dataNode    name="jdbc_dn2" dataHost="jdbchost" database="db2" /> 
    <dataNode name="jdbc_dn3"     dataHost="jdbchost" database="db3" /> -->
    <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
        writeType="0" dbType="mysql" dbDriver="native" switchType="-1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <!-- can have multi write hosts -->
        <writeHost host="hostM1" url="192.168.208.189:3306" user="mycat"
            password="root3306">
            <!-- can have multi read hosts -->
        <readHost host="hosts1" url="192.168.164.26:3306" user="mycat_r"
            password="root3306" />
        </writeHost>
        <writeHost host="hosts2" url="192.168.164.26:3306" user="mycat_r"
            password="root3306" />
    </dataHost>
</mycat:schema>

其中，balance指的负载均衡类型，目前的取值有4种：
1. balance="0", 不开启读写分离机制，所有读操作都发送到当前可用的writeHost上。
2. balance="1"，全部的readHost与stand by writeHost参与select语句的负载均衡，S1,S2都参与select语句的负载均衡。
3. balance="2"，所有读操作都随机的在writeHost、readhost上分发。
4. balance="3"，所有读请求随机的分发到wiriterHost对应的readhost执行，writerHost不负担读压力
switchType指的是切换的模式，目前的取值也有4种：
1. switchType='-1' 表示不自动切换
2. switchType='1' 默认值，表示自动切换
3. switchType='2' 基于MySQL主从同步的状态决定是否切换,心跳语句为 show slave status
4. switchType='3'基于MySQL galary cluster的切换机制（适合集群）（1.4.1），心跳语句为 show status like 'wsrep%'。
因此，该配置文件中的balance="1"意味着作为的read的hostS1和stand by writeHosthostS2将参与select语句的负载均衡，这就实现了主从的读写分离，switchType='-1'意味着当主挂掉的时候，不进行自动切换，即hostS1和hostS2并不会被提升为主，仍只提供读的功能。这就避免了将数据读进slave的可能性，毕竟，单纯的MySQL主从集群并不允许将数据读进slave中，除非配置的是双master。

注意mycat_r 需要 slave从数据库select user() 权限，否则主挂了之后，无法验证，主从都无法读取。

打开server.xml进行配置

<user name="mycat">
        <property name="password">root3306</property>
        <property name="schemas">aaaa</property>
    </user>
    <user name="mycat_r">
        <property name="password">root3306</property>
        <property name="schemas">aaaa</property>
        <property name="readOnly">true</property>
    </user>

注意这里的aaaa和schema.xml 中的aaaa是对应。

#mycat console  前台运行试试看看有没有报错

# vi /etc/hosts
192.168.208.189 ebs-****           这里是修改hosts避免java报错。
mysql -umycat -proot3306 -h127.0.0.1 -P9066 -Daaaa      #本地管理检测
show @@help;
show @@databases;  几个数据库连接
show @@connection; 当前连接状态
show @@datasource;  显示数据源
show @@heartbeat;  查看 主从服务器状态

rs_code状态为1 表示正常，如果为-1 要检测schema.xml中的账号是否能正常连接。

mysql -umycat -proot3306 -h192.168.208.189 -P8066    #代理连接 端口是8066  注意防火墙放行

主库做添加表测试

create table MyClass(id int(4) not null primary key auto_increment,name char(20) not null,sex int(4) not null default '0',degree double(16,2));

读写分离测试

从上单独创建一个表，然后8066登录查看，列出来的表是从库的表，说明读取的是从库

将从上的slave停止 避免同步，然后8066登录创建表发现，主上可以看到表，从上看不到表，说明写是在主上，开启从slave后同步到从上。

中断测试

主中断之后无法进行写入，可以查询从服务器

从中断之后依然可以写入，读切到主服务器。

mycat的日志是写在/usr/local/mycat/logs/

常见的一些错误及相关文档

http://www.open-open.com/lib/view/open1409208341557.html  常见的主从数据异常恢复

http://blog.csdn.net/leipeng321123/article/details/50428020  中文乱码的解决方法

http://wenku.baidu.com/link?url=s83LXjDz6VtfMLGABKjiuYiwp8yiQPt9J6Rmp2IHBOq_hTWxibIfgNVFuRsIcVkXgrPZgwBAhAkhFAgsXQkA8vNYeSSRCYNITxz0Fw53347

debug日志分析sql的完整执行逻辑状态

http://blog.csdn.net/linlinv3/article/details/51777324  schema.xml配置文件详解