写在前面：为什么要用Mycat
当我们的应用只需要一台数据库服务器的时候我们并不需要Mycat，而如果你需要分库甚至分表，这时候应用要面对很多个数据库的时候，这个时候就需要对数据库层做一个抽象，来管理这些数据库，而最上面的应用只需要面对一个数据库层的抽象或者说数据库中间件就好了，这就是Mycat的核心作用。
实现读写分离的中间件Amoeba、Cobar、MyCAT这三者的渊源比较深，若Amoeba能继续下去，Cobar就不会出来；若Cobar那批人不是都走光了的话，MyCAT也不会再另起炉灶。
环境：
Master：CentOS 6.5 192.168.81.11
Slave1：CentOS 6.5 192.168.81.12
Slave2: CentOS 7.3 192.168.81.13
MySQL： 5.6.35
一、安装数据库
传送门：
CentOS 6.5 源码安装MySQL-5.6.35 http://www.jianshu.com/p/f597d1e154f6
LNMP环境的源码搭建（CentOS 7） http://www.jianshu.com/p/4699bcb04633
二、配置主从复制（一主多从）
(参考)传送门：主从复制（双主复制） http://www.jianshu.com/p/0fec2a9d8d81
修改my.cnf文件
Master：
[mysqld]
log-bin=mysql-bin
server-id=1
binlog-do-db=database1
binlog-do-db=database2
binlog-ignore-db=mysql
binlog_format=mixed
Slave：（两个从的配置相同，但需要不同的server-id）
[mysqld]
server-id=2
replicate-do-db=database1
replicate-do-db=database2
replicate-ignore-db=mysql
重新启动数据库
登录数据库，配置主从同步
Master:
mysql> grant replication slave on *.* to slave@'192.168.81.%' identified by 'ibelieveicanfly';
mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000001 | 624 | | mysql,information_schema | |
+------------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)
Slave：
[root@master1 ~]# mysql -u root -p
Enter password:
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> change master to \
master_host='192.168.81.11',
master_user='slave',
master_password='ibelieveicanfly',
master_log_file='mysql-bin.000002' ,
master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
到此，主从复制配置完成
安装JAVA（推荐使用JAVA 1.7及以上版本）
[root@localhost ~]# yum -y install jdk-8u20-linux-x64.rpm
[root@localhost ~]# sh jdk.sh
There is 1 program that provides 'java'.
Selection Command
-----------------------------------------------
*+ 1 /usr/java/jdk1.8.0_20/bin/java
Enter to keep the current selection[+], or type selection number: 1
[root@localhost ~]# java -version
java version "1.8.0_20"
Java(TM) SE Runtime Environment (build 1.8.0_20-b26)
Java HotSpot(TM) 64-Bit Server VM (build 25.20-b23, mixed mode)
附jdk.sh：
echo "export JAVA_HOME=/usr/java/jdk1.8.0_20" >> /etc/profile
echo "export JAVA_BIN=/usr/java/jdk1.8.0_20/bin" >> /etc/profile
echo "export PATH=$PATH:$JAVA_HOME/bin" >> /etc/profile
echo "export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar" >> /etc/profile
echo "export PATH=$JAVA_HOME/bin:$JRE_HOME/bin:$PATH" >> /etc/profile
update-alternatives --install /usr/bin/java java /usr/java/jdk1.8.0_20/bin/java 300
update-alternatives --install /usr/bin/javac javac /usr/java/jdk1.8.0_20/bin/javac 300
update-alternatives --config java
下载mycat
[root@localhost ~]# wget https://github.com/MyCATApache/Mycat-download/raw/master/1.4
解压mycat
[root@localhost ~]# tar zxvf Mycat-server-1.4-release-20151019230038-linux.tar.gz
[root@localhost ~]# mv mycat /usr/local/mycat
[root@localhost ~]# cd /usr/local/mycat/
配置mycat配置文件（主要修改schema.xml和server.xml）
[root@localhost mycat]# vim ./conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/">
<schema name="database1" checkSQLschema="false" sqlMaxLimit="100" dataNode="database1"></schema>
<dataNode name="database1" dataHost="localhost1" database="database1" />
<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="cluster1" url="192.168.81.11:3306" user="mycat" password="mycat">
<readHost host="cluster2" url="192.168.152.12:3306" user="mycat" password="mycat" />
<readHost host="cluster2" url="192.168.152.12:3306" user="mycat" password="mycat" />
</writeHost>
</dataHost>
</mycat:schema>
[root@localhost mycat]# vim ./conf/server.xml
<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License");
- you may not use this file except in compliance with the License. - You
may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0
- - Unless required by applicable law or agreed to in writing, software -
distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT
WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the
License for the specific language governing permissions and - limitations
under the License. -->
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://org.opencloudb/">
<system>
<!--
<property name="mutiNodeLimitType">1</property> 0：开启小数量级（默认） ；1：开启亿级数>据排序
<property name="mutiNodePatchSize">100</property> 亿级数量排序批量
<property name="processors">32</property> <property name="processorExecutor">32</property>
<property name="serverPort">8066</property> <property name="managerPort">9066</property>
<property name="idleTimeout">300000</property> <property name="bindIp">0.0.0.0</property>
<property name="frontWriteQueueSize">4096</property> <property name="processors">32</property> -->
</system>
<user name="admin">
<property name="password">admin</property>
<property name="schemas">database1</property>
</user>
</mycat:server>
吐槽：mycat的配置文件让人看着实在是有些凌乱，不过我们这里实际用到的功能并不是那么多，因此有大段的内容是可以删掉的，比如上边这段就有可以删除的、你发现了吗？
做到这里我进行了主机名的修改：###
（应该提前做的，喵喵喵.../(ㄒoㄒ)/~~就不会遇到后边的许多问题了）
CentOS 6.5的主机名修改方法：
[root@localhost ~]# vi /etc/sysconfig/network
HOSTNAME=cluster2
[root@localhost ~]# hostname cluster2
[root@localhost ~]# reboot
CentOS 7.3的修改方法：
[root@master1 ~]# hostnamectl set-hostname cluster3
[root@master1 ~]# reboot
重启后进入数据库，查看slave的状态
然后...
发现出现问题了
mysql> show slave status\G;先报错如下：
SQL_IO_Running为NO
Last_IO_Error: error connecting to master 'slave@192.168.81.11:3306' - retry-time: 60 retries: 1
尝试start slave，报错
mysql> start slave;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
mysql> reset slave;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
再次查看slave状态，换了一个报错
Last_SQL_Error: Error 'Can't create database 'database2'; database exists' on query. Default database: 'database2'. Query: 'create database database2'
SQL_IO_Running依然为NO
在master服务器查看状态
mysql> show master status;
+------------------+----------+---------------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+---------------------+------------------+-------------------+
| mysql-bin.000003 | 120 | database1,database2 | mysql | |
+------------------+----------+---------------------+------------------+-------------------+
1 row in set (0.01 sec)
在slave服务器上执行手动同步
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> change master to master_host='192.168.81.11',master_user='slave',master_password='ibelieveicanfly',master_log_file='mysql-bin.000003' ,master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
#######至此问题解决，启动mycat
[root@cluster1 mycat]# ./bin/mycat start
Starting Mycat-server...
检查mycat是否开启
[root@cluster1 mycat]# ps -ef |grep mycat
root 2379 2106 0 17:08 pts/1 00:00:00 grep mycat
好吧...并没有，查看日志发现可能是主机名没有绑定，想起来确实忘记加入hosts了
[root@cluster1 mycat]# cat /usr/local/mycat/logs/wrapper.log
STATUS | wrapper | 2017/05/08 17:03:57 | --> Wrapper Started as Daemon
STATUS | wrapper | 2017/05/08 17:03:57 | Launching a JVM...
ERROR | wrapper | 2017/05/08 17:04:00 | JVM exited while loading the application.
INFO | jvm 1 | 2017/05/08 17:04:00 | 错误: 代理抛出异常错误: java.net.MalformedURLException: Local host name unknown: java.net.UnknownHostException: cluster1: cluster1: 未知的名称或服务
STATUS | wrapper | 2017/05/08 17:04:04 | Launching a JVM...
ERROR | wrapper | 2017/05/08 17:04:04 | JVM exited while loading the application.
INFO | jvm 2 | 2017/05/08 17:04:04 | 错误: 代理抛出异常错误: java.net.MalformedURLException: Local host name unknown: java.net.UnknownHostException: cluster1: cluster1: 未知的名称或服务
STATUS | wrapper | 2017/05/08 17:04:08 | Launching a JVM...
ERROR | wrapper | 2017/05/08 17:04:09 | JVM exited while loading the application.
INFO | jvm 3 | 2017/05/08 17:04:09 | 错误: 代理抛出异常错误: java.net.MalformedURLException: Local host name unknown: java.net.UnknownHostException: cluster1: cluster1: 未知的名称或服务
STATUS | wrapper | 2017/05/08 17:04:13 | Launching a JVM...
ERROR | wrapper | 2017/05/08 17:04:13 | JVM exited while loading the application.
INFO | jvm 4 | 2017/05/08 17:04:13 | 错误: 代理抛出异常错误: java.net.MalformedURLException: Local host name unknown: java.net.UnknownHostException: cluster1: cluster1: 未知的名称或服务
STATUS | wrapper | 2017/05/08 17:04:17 | Launching a JVM...
ERROR | wrapper | 2017/05/08 17:04:17 | JVM exited while loading the application.
INFO | jvm 5 | 2017/05/08 17:04:17 | 错误: 代理抛出异常错误: java.net.MalformedURLException: Local host name unknown: java.net.UnknownHostException: cluster1: cluster1: 未知的名称或服务
FATAL | wrapper | 2017/05/08 17:04:17 | There were 5 failed launches in a row, each lasting less than 300 seconds. Giving up.
FATAL | wrapper | 2017/05/08 17:04:17 | There may be a configuration problem: please check the logs.
STATUS | wrapper | 2017/05/08 17:04:17 | <-- Wrapper Stopped
添加本地域名解析
[root@cluster1 mycat]# vim /etc/hosts
192.168.81.11 cluster1
192.168.81.12 cluster2
192.168.81.13 cluster3
重新启动Mycat
[root@cluster1 mycat]# ./bin/mycat start
Starting Mycat-server...
[root@cluster1 mycat]# ps -ef |grep mycat
root 2442 1 0 17:12 ? 00:00:00 /usr/local/mycat/bin/./wrapper-linux-x86-64 /usr/local/mycat/conf/wrapper.conf wrapper.syslog.ident=mycat wrapper.pidfile=/usr/local/mycat/logs/mycat.pid wrapper.daemonize=TRUE wrapper.lockfile=/var/lock/subsys/mycat
root 2488 2106 0 17:12 pts/1 00:00:00 grep mycat
启动成功!
连接mycat尝试：
[root@cluster1 mycat]# mysql -h127.0.0.1 -uadmin -padmin -P8066
mysql> show databases;
+-----------+
| DATABASE |
+-----------+
| database1 |
+-----------+
网友评论