利用mycat实现基于mysql5.5主从复制的读写分离

整体步骤：

1.准备好两台服务器，一台作为主数据库服务器，一台作为从服务器，并安装好mysql数据库，此处略
2.配置好主从同步
3.下载JDK配置mycat依赖的JAVA环境，mycat采用java语言开发运行依赖jre
4.配置mycat的相关文件
5.测试

一、配置mysql主从环境

MYSQL主从同步的作用
(1) 数据分布
(2) 负载平衡(load balancing)
(3) 备份
(4) 高可用性(high availability)和容错
MYSQL主从同步的原理
关于MYSQL的主从同步，最主要的是要了解MYSQL的主从同步是如何工作的也即主从同步的原理，通过下图能很明白的指导其工作的过程：

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

MYSQL主从同步的搭建实战
主从同步的搭建是一项比较细的技术活，前期做好了一些事情会让你在以后的工作中减少很多工作，搭建的时候需要注意一些问题，一会搭建的时候会一边搭建一边介绍需要注意的问题，让初学者能在刚开始的时候就有效的规避掉一些潜在的问题(MYSQL安装这里不做介绍)：
主从同步环境介绍
操作系统环境：Centos 6.5 64 bit
MYSQL版本：MYSQL 5.5.48
主服务器的IP：192.168.8.60
从服务器的IP：192.168.11.145

环境拓扑

1.将服务器都安装ntp服务，保持主从数据库时间一致

8.102为时间服务器，手动同步一次
# yum install -y ntp
# ntpdate -u 192.168.8.102

2.备份8.60主数据库

# service mysqld stop
# cp -r /data/mydata/TD_OA /home
将数据库打包下载到本地传送到辅助服务器上
# chown -R mysql.mysql /data/

3.在主服务器上建立同步帐号

mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.11.145' IDENTIFIED BY 'pass';
mysql> FLUSH PRIVILEGES;
注意：大家在设置权限的时候不要将密码设置过于简单。

主服务器my.cnf配置参考：

# cat /etc/my.cnf | grep "^[^#]"
[client]
port		= 3306
socket		= /tmp/mysql.sock
[mysqld]
port		= 3306
default-storage-engine=MyISAM
max_connections=1500
socket		= /tmp/mysql.sock
skip-external-locking
skip-innodb
key_buffer_size = 4096M
max_allowed_packet = 64M
table_open_cache = 2400
open_files_limit=65535
tmp_table_size=256M
sort_buffer_size = 16M
read_buffer_size = 16M
read_rnd_buffer_size = 16M
myisam_sort_buffer_size = 128M
thread_cache_size = 256
query_cache_type=0
query_cache_size= 512M
innodb_buffer_pool_size = 1024M
skip-name-resolve
wait_timeout=300
thread_concurrency = 8
innodb_buffer_pool_instances = 35
innodb_file_per_table = 1
datadir = /data/mydata
log-bin=/data/binlogs/mysql-bin
binlog-do-db=TD_OA
binlog-ignore-db = mysql
expire_logs_day=30
max_binlog_size = 200M
slow_query_log=ON
slow-query-log-file=/data/binlogs/slow_query.log
long_query_time=2
log_bin_trust_function_creators=1
binlog_format=mixed
server-id	= 60
[mysqldump]
quick
max_allowed_packet = 64M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 1024M
sort_buffer_size = 512M
read_buffer = 256M
write_buffer = 256M
[mysqlhotcopy]
interactive-timeout

4、从服务器设置：

# mkdir /data/relaylog/
# chown -R mysql.mysql /data/relaylog
# vim /etc/my.cnf

server-id = 145 #配置serverid
relay-log = /data/relaylog/relay-bin#启用中继日志
replicate_wild_do_table=TD_OA.%#需要同步的数据库表
replicate_wild_ignore_table=mysql.%#忽略的数据库表
skip-slave-start#不自动启动复制

从服务器配置参考：

# cat /etc/my.cnf | grep "^[^#]"
[client]
port		= 3306
socket		= /tmp/mysql.sock
[mysqld]
port		= 3306
socket		= /tmp/mysql.sock
default-storage-engine=MyISAM
max_connections=1000
open_files_limit=65535
innodb_file_per_table = 1
datadir = /data/mydata
skip-external-locking
skip-innodb
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
thread_concurrency = 4
log-bin=/data/binlogs/mysql-bin
relay-log = /data/relaylog/relay-bin
log_bin_trust_function_creators=1
binlog_format=mixed
server-id	= 145
replicate_wild_do_table=TD_OA.%
replicate_wild_ignore_table=mysql.%
skip-slave-start
read-only=ON
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout

注意：
1) server-id这一项需要认真检查，一定不能和主服务器冲突了，不然到时候会出现莫民其妙的问题，因为同步的时候会会根据server-id做判断，如果server-id一样就不进行同步了，不然可能会导致死循环(主主同步或者环状同步的时候)。
2) 有的人会感觉奇怪我这里为什么要使用replicate-wild-ignore-table参数，而不是用replicate-do-db或者replicate-ignore-db来过滤需要同步的数据库和不需要同步的数据库。这里有几个原因：
A. replicate-wild-ignore-table参数能同步所有跨数据库的更新，比如replicate-do-db或者replicate-ignore-db不会同步类似
use mysql;
UPDATE test.aaa SET amount=amount+10;
B. replicate-wild-ignore-table=mysql.%在以后需要添加同步数据库的时候能方便添加而不需要重新启动从服务器的数据库。因为以后很可能需要同步其他的数据库。
如设置 replicate_do_db=test
use mysql;
update test.table1 set ......第二句将不会被执行
如设置 replicate_ignore_db=mysql
use mysql;
update test.table1 set ......
第二句会被忽略执行原因是设置replicate_do_db或replicate_ignore_db后，MySQL执行sql前检查的是当前默认数据库，所以跨库更新语句被忽略。
可以使用replicate_wild_do_table和replicate_wild_ignore_table来代替
如
replicate_wild_do_table=test.%
或
replicate_wild_ignore_table=mysql.%
这样就可以避免出现上述问题了
3) auto_increment_increment和auto_increment_offset参数，这两个参数一般用在主主同步中，用来错开自增值, 防止键值冲突。
4) --slave-skip-errors参数，不要胡乱使用这些跳过错误的参数，除非你非常确定你在做什么。当你使用这些参数时候，MYSQL会忽略那些错误，这样会导致你的主从服务器数据不一致。

MySQL主从复制几个重要的启动选项
　　(1)　 log-slave-updates
　　log-slave-updates这个参数用来配置从服务器的更新是否写入二进制日志，这个选项默认是不打开的，但是，如果这个从服务器B是服务器A的从服务器，同时还作为服务器C的主服务器，那么就需要开发这个选项，这样它的从服务器C才能获得它的二进制日志进行同步操作
　　(2)　 master-connect-retry
　　master-connect-retry这个参数是用来设置在和主服务器连接丢失的时候，重试的时间间隔，默认是60秒
　　(3)　 read-only
　　read-only是用来限制普通用户对从数据库的更新操作，以确保从数据库的安全性，不过如果是超级用户依然可以对从数据库进行更新操作
　　(4)　 slave-skip-errors
　　在复制过程中，由于各种的原因，从服务器可能会遇到执行BINLOG中的SQL出错的情况，在默认情况下，服务器会停止复制进程，不再进行同步，等到用户自行来处理。
　　Slave-skip-errors的作用就是用来定义复制过程中从服务器可以自动跳过的错误号，当复制过程中遇到定义的错误号，就可以自动跳过，直接执行后面的SQL语句。
　　--slave-skip-errors=[err1,err2,…….|ALL]
　　但必须注意的是，启动这个参数，如果处理不当，很可能造成主从数据库的数据不同步，在应用中需要根据实际情况，如果对数据完整性要求不是很严格，那么这个选项确实可以减轻维护的成本

重启服务使之生效
# service mysqld restart

5.主服务器状态

在主服务器执行查看状态，记录binlog日志的文件名及位置参数
mysql> show master status;

从服务器配置

mysql> CHANGE MASTER TO MASTER_HOST='192.168.8.60',MASTER_USER='repluser',MASTER_PASSWORD='replpass2014',MASTER_LOG_FILE='mysql-bin.000006',MASTER_LOG_POS=691;
mysql> START SLAVE;


mysql> show slave statusG
*************************** 1. row ***************************
       Slave_IO_State: 
 Master_Host: 192.168.8.60
 Master_User: repluser
 Master_Port: 3306
Connect_Retry: 60
      Master_Log_File: mysql-bin.000006
  Read_Master_Log_Pos: 691
       Relay_Log_File: relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000006
     Slave_IO_Running: No
    Slave_SQL_Running: No
      Replicate_Do_DB: 
  Replicate_Ignore_DB: 
   Replicate_Do_Table: 
Replicate_Ignore_Table: 
Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
  Last_Errno: 0
  Last_Error: 
Skip_Counter: 0
  Exec_Master_Log_Pos: 691
      Relay_Log_Space: 107
      Until_Condition: None
       Until_Log_File: 
Until_Log_Pos: 0
   Master_SSL_Allowed: No
   Master_SSL_CA_File: 
   Master_SSL_CA_Path: 
      Master_SSL_Cert: 
    Master_SSL_Cipher: 
       Master_SSL_Key: 
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
       Last_SQL_Errno: 0
       Last_SQL_Error: 
Replicate_Ignore_Server_Ids: 
     Master_Server_Id: 6

遇到这样的错误如：“Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'"等或由于清数据导致主从库不同步了

解决办法如下：
先进入slave中执行:"slave stop;"来停止从库同步；
再去master中执行:"flush logs;"来清空日志；
然后在master中执行:"show master status;"查看下主库的状态，主要是日志的文件和position；
然后回到slave中，执行:"CHANGE MASTER TO MASTER_LOG_FILE='log-bin.000006',MASTER_LOG_POS=107;"，文件和位置对应master中的;
最后在slave中执行:"slave start;"来启动同步。

状态OK：
mysql> show slave statusG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.8.60
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 6022
Relay_Log_File: relay-bin.000005
Relay_Log_Pos: 3733
Relay_Master_Log_File: mysql-bin.000007
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table: TD_OA.%
Replicate_Wild_Ignore_Table: mysql.%
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 6022
Relay_Log_Space: 3883
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 60

如果备份的时候停止了从服务器，需要重新启动从服务器的复制
mysql> slave start;

二、mycat的安装及部署

1.下载mycat1.4版本

https://github.com/MyCATApache/Mycat-download
# tar xf Mycat-server-1.4-release-20151019230038-linux.tar.gz
# mv mycat /usr/local/

2.部署jdk环境

检查现有版本JDK
# java -version

查找java相关文件并卸载
# rpm -qa | grep java
tzdata-java-2013g-1.el6.noarch
java-1.7.0-openjdk-1.7.0.45-2.4.3.3.el6.x86_64
libvirt-java-0.4.9-1.el6.noarch
libvirt-java-devel-0.4.9-1.el6.noarch
java-1.6.0-openjdk-1.6.0.0-1.66.1.13.0.el6.x86_64


卸载openjdk1.7
# rpm -e --nodeps java-1.7.0-openjdk-1.7.0.45-2.4.3.3.el6.x86_64
 rpm -qa | grep java
tzdata-java-2013g-1.el6.noarch
libvirt-java-0.4.9-1.el6.noarch
libvirt-java-devel-0.4.9-1.el6.noarch
java-1.6.0-openjdk-1.6.0.0-1.66.1.13.0.el6.x86_64
# java -version
java version "1.6.0_28"
OpenJDK Runtime Environment (IcedTea6 1.13.0pre) (rhel-1.66.1.13.0.el6-x86_64)
OpenJDK 64-Bit Server VM (build 23.25-b01, mixed mode)
卸载openjdk1.6
# rpm -e --nodeps java-1.6.0-openjdk-1.6.0.0-1.66.1.13.0.el6.x86_64
# java -version
-bash: /usr/bin/java: No such file or directory


安装jdk7
# rpm -ivh jdk-7u79-linux-x64.rpm

将java命令加入到环境变量中
vim /etc/profile
//添加内容
JAVA_HOME=/usr/java/jdk1.7.0_79
JRE_HOME=/usr/java/jdk1.7.0_79/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
//查看PATH
# echo $PATH
/usr/local/mysql/bin:/usr/lib64/qt-3.3/bin:/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin:/usr/java/jdk1.7.0_79/bin:/usr/java/jdk1.7.0_79/jre/bin

3、配置mycat中的jdk文件路径

# cd /usr/local/mycat/conf/
# vim wrapper.conf
# Java Application
wrapper.java.command=/usr/java/jdk1.7.0_79/bin/java

4、读写分离配置(重点配置)：

# cp schema.xml schema.xml.bak
# vim schema.xml

<schema name="TD_OA" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
<dataNode name="dn1" dataHost="localhost1" database="TD_OA" />
<dataNode name="dn2" dataHost="localhost1" database="TD_OA" />
<dataNode name="dn3" dataHost="localhost1" database="TD_OA" />

<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>

<writeHost host="hostM1" url="192.168.8.60:3306" user="mycat"
password="pass">

<readHost host="hostS1" url="192.168.11.145:3306" user="mycat_r" password="pass" />
</writeHost>
</dataHost>

(1)<schema name="TD_OA" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
这里的TD_OA就是我们所宣称的数据库名称，必须和server.xml中的用户指定的数据库名称一致。添加一个dataNode="dn1"，是指定了我们这个库只有在dn1上，没有分库。

(2)<dataNode name="dn1" dataHost="localhost1" database="TD_OA" />
这里只需要改database的名字，TD_OA就是你真是的数据库上的数据库名，可根据自己的数据库名称修改。

(3) <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

需要配置的位置：
balance="1" writeType="0" switchType="1"

balance
1、balance=0 不开启读写分离机制，所有读操作都发送到当前可用的writehostle .

2、balance=1 全部的readhost与stand by writeHost 参与select语句的负载均衡。简单的说，双主双从模式(M1àS1,M2àS2，并且M1和M2互为主备)，正常情况下，M1，S1，S2都参与select语句的复杂均衡。

3、balance=2 所有读操作都随机的在readhost和writehost上分发

writeType
负载均衡类型，目前的取值有3种：
1、writeType="0", 所有写操作发送到配置的第一个writeHost。
2、writeType="1"，所有写操作都随机的发送到配置的writeHost。
3、writeType="2"，不执行写操作。

switchType
1、switchType=-1 表示不自动切换
2、switchType=1 默认值，自动切换
3、switchType=2 基于MySQL 主从同步的状态决定是否切换
(4)<writeHost host="hostM1" url="192.168.8.60:3306" user="mycat" password="pass">
<!– can have multi read hosts –>
<readHost host="hostS1" url="192.168.11.145:3306" user="mycat_r" password="pass" />

注意：
这里配置的是读写服务器的IP地址和端口访问，访问的用户名和密码；而且一定要先在客户端测试OK了以后在进行配置。

5、将mycat命令加入到配置文件中

# vim /etc/profile
export PATH=/usr/local/mycat/bin:$PATH
# source /etc/profile

6、创建管理用户

主库上对mycat用户授权如下：

用户：mycat 密码：pass 端口：3306

权限：insert,delete,update,select

命令：grant insert,delete,update,select on TD_OA.* to mycat@'192.168.8.%' identified by 'pass';
flush privileges;

从库上mycat_r用户授权如下：
用户：mycat_r 密码：pass 端口：3306
权限： select

grant select on TD_OA.* to mycat_r@'192.168.8.%' identified by 'pass';
flush privileges;

提示：由于主库和从库是同步mysql数据库复制的，所以从库上的mycat用户会自动和主库的一致，即无法实现只读select的授权
revoke insert,update,delete on TD_OA.* from mycat@'192.168.8.%'; #<=回收insert update delete 权限

修改mycat配置文件
vim /usr/local/mycat/conf/server.xml
<user name="mycat">
<property name="password">pass</property>
<property name="schemas">TD_OA</property>
</user>

<user name="mycat_r">
<property name="password">pass</property>
<property name="schemas">TD_OA</property>
<property name="readOnly">true</property>
</user>

注意：

①这里配置的是可以连接主库的两个用户

用户：mycat 密码：pass 给予此用户TD_OA数据库增删改查的权限。

用户：mycat_r 密码：pass 给予此用户TD_OA数据库读的权限。

②这里的TD_OA，不一定是你数据库上的真实库名，可以任意指定，只要接下来和schema.xml的配置文件的库名统一即可。

7、启动mycat

# mycat console #<=通过console命令启动mycat，这样方便提取信息
Running Mycat-server...
wrapper | --> Wrapper Started as Console
wrapper | Launching a JVM...
wrapper | JVM exited while loading the application.
jvm 1 | Error: Exception thrown by the agent : java.net.MalformedURLException: Local host name unknown: java.net.UnknownHostException: oawebserver002: oawebserver002: Temporary failure in name resolution
解决：编辑Hosts文件将主机名和IP绑定
# vim /etc/hosts
192.168.8.60 oawebserver002
# mycat restart使配置生效

在客户端连接mysql主库服务器：

# yum install -y mysql
# mysql -umycat -ppass -h192.168.8.60 -P8066

8、管理命令与监控

相关阅读:
2022 flag 150篇文章 63 ES elastic search
2022 flag 150编文章 005 java 网络编程
2022 flag 150篇文章 91 分布式系统协调服务分布式基础
2022 flag 150篇文章 50 PMP
2022 flag 150篇文章 61 消息队列 Kafka
2022年1月最新最全的微前端框架调研
sqlconnection,sqlcommand,sqldataadapter,sqldatareader,dataset的关系详解
[日常] 解决docker拉取镜像速度慢的问题
jupyter notebook下python2和python3共存（kali）
Jboss弱密码及反序列化漏洞

原文地址：https://www.cnblogs.com/reblue520/p/6239811.html