MySQL 数据库读写分离

MySQL 是最流行的关系型数据库管理系统

♥ 文章声明 ♥ 该系列文章部分文字描述,参考于以下文献,化繁为简. 《MySQL5.7从入门到精通》 - 刘增杰

MySQL 配置主备模式

基于一台服务器的数据复制,故得名单机热备,主-备 Active-Standby 主-备方式,即指的是一台服务器处于某种业务的激活状态(即Active状态),另一台服务器处于该业务的备用状态(即Standby状态),主数据库数据更新后,备份服务器同步数据只本机

两台同步执行: 安装Mariadb,设置初始密码,启动服务,并设置开机自启动,这里的配置步骤应在两台主机上同步执行

 
yum install -y mariadb mariadb-server
systemctl restart mariadb
mysql_secure_installation

修改MariaDB主配置文件,写入log-bin参数开启二进制日志文件,然后写入server-id指定本台MariaDB服务器的ID号(注意：这里主从不要重复)

 
vim /etc/my.cnf
 
[mysqld]
log-bin=mysql-bin            #开启二进制日志文件
server-id=12                 #本台MySQL服务器ID(主从不能重复)
systemctl restart mariadb    #重启生效

主数据库执行: 进入主MariaDB数据库,授权给从数据库登陆的账号,这里显示二进制日志文件大小并记录下来

 
grant replication slave on *.* to "用户名"@'%' identified by "密码";    #创建用于从服务器登陆的账号
 
show master status;                                                   #显示日志状态
 
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 245 | | |
+------------------+----------+--------------+------------------+
row in set (0.00 sec)

从数据库执行: 进入从MariaDB数据库,使用主数据库授权的用户名,密码,以及相应的日志文件名称 master_log_file,和日志文件位置 master_log_pos,此处在主数据库上获取即可

 
change master to master_host='服务器IP',master_user='授权账号',master_password='密码',master_log_file='日志文件',master_log_pos=文件位置;
 
start slave;             #启用同步
show slave status\G;     #查看同步状态
 
注意：两个状态同时开启即可
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

MySQL 配置主主模式

基于两台服务器的复制方式较多,故得名双机热备,双主机方式即指两种不同业务分别在两台服务器上互为主备状态(即 Active-Standby和Standby-Active状态),这里需要注意的是主-主同步配置,其实就是两台MariaDB数据库互为主从,所以,要在两台主机间作两次相同的操作

两台同步执行: 安装MariaDB,设置初始密码,启动服务,并设置开机自启动,这里的配置步骤应在两台主机上同步执行

 
yum install -y mariadb mariadb-server
systemctl restart mariadb
mysql_secure_installation

将MariaDB1的数据拷贝到MariaDB2上,实现数据的一致性,(注意：这里应先锁定表结构,防止数据不一致)

 
flush tables with read lock;                      #锁定数据表为只读模式
mysqldump -u root -p --all-databases >back.sql    #备份数据库
 
scp back.sql root@192.168.1.13:/root              #将数据传递到远程主机
unlock tables;                                    #解除表锁定
mysql -uroot -p <back.sql                         #从数据库恢复数据

修改MariaDB主配置文件(注意：两台都要修改),开启二进制日志和制定服务器ID号

 
vim /etc/my.cnf
 
[mysqld]
log-bin=mysql-bin                                  #开启二进制日志
server-id=10                                       #指定服务器ID号(两台不允许重复)
replicate-do-db=test                               #要同步的数据库
binlog-ignore-db=mysql                             #不需要同步的数据库
binlog-ignore-db=information_schema                #不需要同步的数据库
auto-increment-increment=2                         #每次自增长2
auto-increment-offset=1                            #自增长从1开始
 
systemctl restart mariadb

MySQL1执行: 进入 MariaDB1 数据库,创建用于从数据库登陆的账号和密码

 
grant replication slave on *.* to '用户名'@'%' identified by '密码';    #创建用于从数据库登陆的用户
show master status;                                                   #查看二进制文件名

进入 MariaDB2 数据库,同步数据

 
change master to master_host='主服务器IP',master_user='授权账号',master_password='密码',master_log_file='二进制文名',master_log_pos=文件空间大小;
 
start slave;            #启动同步功能
show slave status\G;    #查看同步状态

MySQL2执行: 进入 MariaDB2 数据库,创建用于从数据库登陆的账号和密码

 
grant replication slave on *.* to '用户名'@'%' identified by '密码';    #创建用于从数据库登陆的用户
show master status;                                                   #查看二进制文件名

进入 MariaDB1 数据库,同步数据

 
change master to master_host='主服务器IP',master_user='授权账号',master_password='密码',master_log_file='二进制文名',master_log_pos=文件空间大小;
 
start slave;            #启动同步功能
show slave status\G;    #查看同步状态

MySQL 配置主主从模式

主-主-从,字面意思就是,两个主一个从,主要用于数据备份,一次性备份两个主数据库资源,这样一个从数据库,就可以同时备份多个主数据库了,节约了宝贵的服务器资源

两台主数据库执行: 两台主服务器都需要安装MySQL初始化并设置密码

 
yum install -y mariadb mariadb-server
systemctl restart mariadb
mysql_secure_installation

两台主服务器都应配置以下内容（两台主MySQL都需要配置）

 
vim /etc/my.cnf              #修改配置文件
 
写入以下内容↓
log-bin=mysql-bin            #开启二进制日志
server-id=147                #服务器ID号
 
systemctl restart mariadb    #重启服务

登陆主MySQL并创建用户从服务器登陆的用户（两台主MySQL都需要配置）

 
mysql -uroot –p                                                        #登陆MySQL
grant replication slave on *.* to '授权用户'@'%' identified by '密码';   #创建从服务器用户
show master status;                                                    #显示日志状态

从数据库执行: 切记从MariaDB数据库不能启动,修改从MySQL主配置文件

 
vim /etc/my.cnf
 
写入以下内容↓
 
[mysqld_multi]
mysqld=/usr/bin/mysqld_safe
mysqladmin=/usr/bin/mysqladmin
log=/tmp/multi.log
 
[mysqld147]                            #名称后面加数字,后期启动用(不应相同)
port=3306                              #端口号(不应相同)
pid-file=/var/lib/mysqla/mysqld.pid    #MySQL进程号保存位置
datadir=/var/lib/mysqla                #数据文件保存位置
socket=/var/lib/mysqla/mysql.sock      #指定登陆套接字
server-id=110                          #ID号,同一主从环境不能相同
user=mysql                             #MySQL登陆用户名
 
[mysqld148]                            #名称后面加数字,后期启动用(不应相同)
port=3307                              #端口号(不应相同)
pid-file=/var/lib/mysqlb/mysqld.pid    #MySQL进程号保存位置
datadir=/var/lib/mysqlb                #数据文件保存位置    
socket=/var/lib/mysqlb/mysql.sock      #指定登陆套接字
server-id=110                          #ID号,同一主从环境不能相同
user=mysql                             #MySQL登陆用户名

初始化数据库

 
mysql_install_db --datadir=/var/lib/mysqla --user=mysql    #初始化数据库a
mysql_install_db --datadir=/var/lib/mysqlb --user=mysql    #初始化数据库b

给相应目录赋予权限

 
cd /var/lib/
chown -R mysql:mysql /var/lib/mysqla/    #修改a文件权限
chown -R mysql:mysql /var/lib/mysqlb/    #修改b文件权限

启动相应服务器

 
mysqld_multi --defaults-file=/etc/my.cnf start 147    #开启147服务器
mysqld_multi --defaults-file=/etc/my.cnf start 148    #开启148服务器

登陆147和148数据库确定主从关系

 
mysql -P 3306 -S /var/lib/mysqla/mysql.sock    #登陆147数据库
mysql -P 3306 -S /var/lib/mysqlb/mysql.sock    #登陆148数据库
 
change master to master_host='服务器IP',master_user='授权账号',master_password='密码',master_log_file='日志文件',master_log_pos=文件位置;
start slave;									    #启用同步
show slave status\G;								#查看同步状态

MySQL 实现读写分离

MyCat是一个开源的,面向企业应用开发的大数据库集群,支持事务、ACID、可以替代MySQL的加强版数据库, 其功能有可以视为MySQL集群的企业级数据库,用来替代昂贵的Oracle集群.融合了内存缓存技术、NoSQL技术、HDFS大数据的新型SQLServer,结合传统数据库和新型分布式数据仓库的新一代企业级数据库产品.

 
192.168.1.5  MyCat   Server
 
192.168.1.11 Master  主MariaDB
192.168.1.12 Slave1  从1
192.168.1.13 Slave2  从2

1.在配置读写分离前请确保你的主机有四台,分别在每台主机安装MariaDB数据库,并初始化,需要注意的是MyCat服务端的MariaDB不需要初始化.

 
[root@localhost ~]# yum install -y mariadb mariadb-server
[root@localhost ~]# systemctl start mariadb
[root@localhost ~]# systemctl enable mariadb

2.在MyCat服务端安装JDK环境,由于MyCat是使用Java开发的,在这里我们要先配置一下JDK环境.

 
[root@localhost ~]# tar -xzvf jdk-8u171-linux-x64.tar.gz -C /usr/local/
[root@localhost ~]# mv /usr/local/jdk1.8.0_171/ /usr/local/jdk
[root@localhost ~]# vim /etc/profile
 
#------------------------------------------------------------------
#JAVA-JDK-PATH
export JAVA_HOME=/usr/local/jdk
export JAVA_BIN=/usr/local/jdk/bin
export PATH=$PATH:$JAVA_HOME/bin
export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar
export JAVA_HOME JAVA_BIN PATH CLASSPATH
#------------------------------------------------------------------
 
[root@localhost ~]# source /etc/profile
[root@localhost ~]# java -version
java version "1.8.0_171"
Java(TM) SE Runtime Environment (build 1.8.0_171-b11)
Java HotSpot(TM) 64-Bit Server VM (build 25.171-b11, mixed mode)

3.此步骤需要进入每一个数据库创建授权用户,也就是给MyCAT准备的使用数据库的授权用户.

 
[root@localhost ~]# mysql -uroot -p
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 5.5.60-MariaDB MariaDB Server
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
MariaDB [(none)]> create database MyCatDB;
Query OK, 1 row affected (0.00 sec)
 
MariaDB [(none)]> grant all on MyCatDB.* to "MyCat"@"%" identified by "123123";
Query OK, 0 rows affected (0.01 sec)
 
MariaDB [(none)]> grant all on MyCatDB.* to "MyCat"@"localhost" identified by "123123";
Query OK, 0 rows affected (0.00 sec)

4.解压MyCat工具,并修改MyCat用户授权文件,写入MyCat登陆账户,这里配置文件有很多选项,我们不需要动,只需要修改以下几个地方就行.

 
[root@localhost ~]# wget http://dl.mycat.io/1.6.5/Mycat-server-1.6.5-release-20180122220033-linux.tar.gz
[root@localhost ~]# tar -xzvf Mycat-server-1.6.5-release-20180122220033-linux.tar.gz -C /usr/local/
[root@localhost ~]# vim /usr/local/mycat/conf/server.xml
 
        <user name="root" defaultAccount="true">			#指定MyCat登陆用户名
                <property name="password">123456</property>		#指定密码
                <property name="schemas">MyCatDB</property>		#指定同步数据库
        </user>
 
        <user name="user">						#指定MyCat登陆用户名
                <property name="password">123456</property>		#指定密码
                <property name="schemas">MyCatDB</property>		#指定同步数据库
                <property name="readOnly">true</property>		#指定只读
        </user>

5.修改MyCat读写分离策略,这里我们删除源文件,并自己创建一个,写入以下内容.

 
[root@localhost ~]# vim /usr/local/mycat/conf/schema.xml
 
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
 
<schema name="MyCatDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn_test"> </schema>
<dataNode name="dn_test" dataHost="localhost" database="MyCatDB" />
 
<dataHost name="localhost" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                
                <heartbeat>select user()</heartbeat>
                <writeHost host="Master1" url="192.168.1.11:3306" user="MyCat" password="123123">			#配置写主机
                <readHost host="Slave1" url="192.168.1.12:3306" user="MyCat" password="123123" />			#配置读主机
		<readHost host="Slave2" url="192.168.1.13:3306" user="MyCat" password="123123" />			#配置读主机
                </writeHost>
        </dataHost>
</mycat:schema>

6.启动MyCat若无报错则说明启动成功,查看端口看虚拟端口是否开启成功.

 
[root@localhost ~]# /usr/local/mycat/bin/mycat start
[root@localhost ~]# netstat -an |grep "9066"                 #虚拟schema管理端口
[root@localhost ~]# netstat -an |grep "8066"                 #虚拟schema登陆端口

7.在MyCat服务主机登陆数据库,测试同步效果.

 
[root@localhost ~]# mysql -uroot -p123456 -h 127.0.0.1 -P 9066
 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.29-mycat-1.6.5-release-20180122220033 MyCat Server (monitor)
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
MySQL [(none)]> show @@heartbeat;                                                       #RS_CODE为1表示心跳正常
+---------+-------+--------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| NAME    | TYPE  | HOST         | PORT | RS_CODE | RETRY | STATUS | TIMEOUT | EXECUTE_TIME | LAST_ACTIVE_TIME    | STOP  |
+---------+-------+--------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| Master1 | mysql | 192.168.1.11 | 3306 |      -1 |     0 | idle   |       0 | 0,16,16      | 2018-12-21 01:29:43 | false |
| Slave1  | mysql | 192.168.1.12 | 3306 |      -1 |     0 | idle   |       0 | 34,31,31     | 2018-12-21 01:29:43 | false |
| Slave2  | mysql | 192.168.1.13 | 3306 |      -1 |     0 | idle   |       0 | 1,16,16      | 2018-12-21 01:29:43 | false |
+---------+-------+--------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
3 rows in set (0.00 sec)
 
MySQL [(none)]> show @@datasource;                                                      #查看读写分离的机器配置情况
+----------+---------+-------+--------------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME    | TYPE  | HOST         | PORT | W/R  | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+---------+-------+--------------+------+------+--------+------+------+---------+-----------+------------+
| dn_test  | Master1 | mysql | 192.168.1.11 | 3306 | W    |      0 |    0 | 1000 |       0 |         0 |          0 |
| dn_test  | Slave1  | mysql | 192.168.1.12 | 3306 | R    |      0 |    0 | 1000 |       0 |         0 |          0 |
| dn_test  | Slave2  | mysql | 192.168.1.13 | 3306 | R    |      0 |    0 | 1000 |       0 |         0 |          0 |
+----------+---------+-------+--------------+------+------+--------+------+------+---------+-----------+------------+
3 rows in set (0.01 sec)
 
MySQL [(none)]>

8.登录MyCat代理端,测试读写分离服务.

 
[root@localhost ~]# mysql -uroot -p123456 -h127.0.0.1 -P 8066
 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.29-mycat-1.6.5-release-20180122220033 MyCat Server (OpenCloundDB)
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
MySQL [(none)]>
MySQL [(none)]>
MySQL [(none)]>
MySQL [(none)]> show databases;
+----------+
| DATABASE |
+----------+
| MyCatDB  |
+----------+
1 row in set (0.00 sec)
 
MySQL [(none)]>

MySQL 增加监控

1.首先我们先来下载MyCat以及Zookeeper.

 
[root@localhost ~]# wget http://www-eu.apache.org/dist/zookeeper/zookeeper-3.4.12/zookeeper-3.4.12.tar.gz
[root@localhost ~]# wget http://dl.mycat.io/mycat-web-1.0/Mycat-web-1.0-SNAPSHOT-20170102153329-linux.tar.gz

2.在MyCat服务端安装JDK环境,由于MyCat是使用Java开发的,在这里我们要先配置一下JDK环境.

 
[root@localhost ~]# tar -xzvf jdk-8u171-linux-x64.tar.gz -C /usr/local/
[root@localhost ~]# mv /usr/local/jdk1.8.0_171/ /usr/local/jdk
[root@localhost ~]# vim /etc/profile
 
#------------------------------------------------------------------
#JAVA-JDK-PATH
export JAVA_HOME=/usr/local/jdk
export JAVA_BIN=/usr/local/jdk/bin
export PATH=$PATH:$JAVA_HOME/bin
export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar
export JAVA_HOME JAVA_BIN PATH CLASSPATH
#------------------------------------------------------------------
 
[root@localhost ~]# source /etc/profile
[root@localhost ~]# java -version
java version "1.8.0_171"
Java(TM) SE Runtime Environment (build 1.8.0_171-b11)
Java HotSpot(TM) 64-Bit Server VM (build 25.171-b11, mixed mode)

3.配置MariaDB开启lower_case_table_names选项,每台都要开启.

 
[root@localhost ~]# yum install -y mariadb mariadb-server
[root@localhost ~]# vim /etc/my.cnf
 
[mysqld]
lower_case_table_names = 1
 
[root@localhost ~]# systemctl restart mariadb

4.安装zookeeper,其主要用来统计数据.

 
[root@localhost ~]# tar -xzvf zookeeper-3.4.12.tar.gz -C /usr/local/
[root@localhost ~]# cd /usr/local/zookeeper-3.4.12/conf/
[root@localhost ~]# cp zoo_sample.cfg zoo.cfg
[root@localhost ~]# vim zoo.cfg
 
dataDir=/usr/local/...
dataLogDir=/usr/local/...

5.运行zookeeper

 
[root@localhost ~]# cd /usr/local/zookeeper-3.4.12/bin/
[root@localhost ~]# ./zkServer.sh start
[root@localhost ~]# netstat -ant | grep 2181
tcp  0    0 :::2181       :::*           LISTEN 
 
如果出现错误：nohup: failed to run command `java’: No such file or directory
可以在zkServer.sh中的首行添加如下代码
 
export JAVA_HOME=/usr/lib/jdk
export PATH=$JAVA_HOME/bin:$PATH

6.最后一步,安装并运行Mycat-Web

 
[root@localhost ~]# tar -xzvf Mycat-web-1.0-SNAPSHOT-20170102153329-linux.tar.gz -C /usr/local
[root@localhost ~]# cd /usr/local/mycat-web
[root@localhost ~]# ./start.sh &
[root@localhost ~]# netstat  -an | grep "8082"
tcp  0    0 :::8082       :::*           LISTEN 
 
[root@localhost ~]# curl http://localhost:8082/mycat

	yum install -y mariadb mariadb-server
	systemctl restart mariadb
	mysql_secure_installation

	vim /etc/my.cnf

	[mysqld]
	log-bin=mysql-bin #开启二进制日志文件
	server-id=12 #本台MySQL服务器ID(主从不能重复)
	systemctl restart mariadb #重启生效

	grant replication slave on . to "用户名"@'%' identified by "密码"; #创建用于从服务器登陆的账号

	show master status; #显示日志状态

	MariaDB [(none)]> show master status;
	+------------------+----------+--------------+------------------+
	\| File \| Position \| Binlog_Do_DB \| Binlog_Ignore_DB \|
	+------------------+----------+--------------+------------------+
	\| mysql-bin.000002 \| 245 \| \| \|
	+------------------+----------+--------------+------------------+
	row in set (0.00 sec)

	change master to master_host='服务器IP',master_user='授权账号',master_password='密码',master_log_file='日志文件',master_log_pos=文件位置;

	start slave; #启用同步
	show slave status\G; #查看同步状态

	注意：两个状态同时开启即可
	Slave_IO_Running: Yes
	Slave_SQL_Running: Yes

	flush tables with read lock; #锁定数据表为只读模式
	mysqldump -u root -p --all-databases >back.sql #备份数据库

	scp back.sql root@192.168.1.13:/root #将数据传递到远程主机
	unlock tables; #解除表锁定
	mysql -uroot -p <back.sql #从数据库恢复数据

	vim /etc/my.cnf

	[mysqld]
	log-bin=mysql-bin #开启二进制日志
	server-id=10 #指定服务器ID号(两台不允许重复)
	replicate-do-db=test #要同步的数据库
	binlog-ignore-db=mysql #不需要同步的数据库
	binlog-ignore-db=information_schema #不需要同步的数据库
	auto-increment-increment=2 #每次自增长2
	auto-increment-offset=1 #自增长从1开始

	systemctl restart mariadb

	grant replication slave on . to '用户名'@'%' identified by '密码'; #创建用于从数据库登陆的用户
	show master status; #查看二进制文件名

	change master to master_host='主服务器IP',master_user='授权账号',master_password='密码',master_log_file='二进制文名',master_log_pos=文件空间大小;

	start slave; #启动同步功能
	show slave status\G; #查看同步状态

	vim /etc/my.cnf #修改配置文件

	写入以下内容↓
	log-bin=mysql-bin #开启二进制日志
	server-id=147 #服务器ID号

	systemctl restart mariadb #重启服务

	mysql -uroot –p #登陆MySQL
	grant replication slave on . to '授权用户'@'%' identified by '密码'; #创建从服务器用户
	show master status; #显示日志状态

	vim /etc/my.cnf

	写入以下内容↓

	[mysqld_multi]
	mysqld=/usr/bin/mysqld_safe
	mysqladmin=/usr/bin/mysqladmin
	log=/tmp/multi.log

	[mysqld147] #名称后面加数字,后期启动用(不应相同)
	port=3306 #端口号(不应相同)
	pid-file=/var/lib/mysqla/mysqld.pid #MySQL进程号保存位置
	datadir=/var/lib/mysqla #数据文件保存位置
	socket=/var/lib/mysqla/mysql.sock #指定登陆套接字
	server-id=110 #ID号,同一主从环境不能相同
	user=mysql #MySQL登陆用户名

	[mysqld148] #名称后面加数字,后期启动用(不应相同)
	port=3307 #端口号(不应相同)
	pid-file=/var/lib/mysqlb/mysqld.pid #MySQL进程号保存位置
	datadir=/var/lib/mysqlb #数据文件保存位置
	socket=/var/lib/mysqlb/mysql.sock #指定登陆套接字
	server-id=110 #ID号,同一主从环境不能相同
	user=mysql #MySQL登陆用户名

	mysql_install_db --datadir=/var/lib/mysqla --user=mysql #初始化数据库a
	mysql_install_db --datadir=/var/lib/mysqlb --user=mysql #初始化数据库b

	cd /var/lib/
	chown -R mysql:mysql /var/lib/mysqla/ #修改a文件权限
	chown -R mysql:mysql /var/lib/mysqlb/ #修改b文件权限

	mysqld_multi --defaults-file=/etc/my.cnf start 147 #开启147服务器
	mysqld_multi --defaults-file=/etc/my.cnf start 148 #开启148服务器

	mysql -P 3306 -S /var/lib/mysqla/mysql.sock #登陆147数据库
	mysql -P 3306 -S /var/lib/mysqlb/mysql.sock #登陆148数据库

	change master to master_host='服务器IP',master_user='授权账号',master_password='密码',master_log_file='日志文件',master_log_pos=文件位置;
	start slave; #启用同步
	show slave status\G; #查看同步状态

	192.168.1.5 MyCat Server

	192.168.1.11 Master 主MariaDB
	192.168.1.12 Slave1 从1
	192.168.1.13 Slave2 从2

	[root@localhost ~]# yum install -y mariadb mariadb-server
	[root@localhost ~]# systemctl start mariadb
	[root@localhost ~]# systemctl enable mariadb

	[root@localhost ~]# tar -xzvf jdk-8u171-linux-x64.tar.gz -C /usr/local/
	[root@localhost ~]# mv /usr/local/jdk1.8.0_171/ /usr/local/jdk
	[root@localhost ~]# vim /etc/profile

	#------------------------------------------------------------------
	#JAVA-JDK-PATH
	export JAVA_HOME=/usr/local/jdk
	export JAVA_BIN=/usr/local/jdk/bin
	export PATH=$PATH:$JAVA_HOME/bin
	export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar
	export JAVA_HOME JAVA_BIN PATH CLASSPATH
	#------------------------------------------------------------------

	[root@localhost ~]# source /etc/profile
	[root@localhost ~]# java -version
	java version "1.8.0_171"
	Java(TM) SE Runtime Environment (build 1.8.0_171-b11)
	Java HotSpot(TM) 64-Bit Server VM (build 25.171-b11, mixed mode)

	[root@localhost ~]# mysql -uroot -p
	Welcome to the MariaDB monitor. Commands end with ; or \g.
	Your MariaDB connection id is 10
	Server version: 5.5.60-MariaDB MariaDB Server

	Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

	Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

	MariaDB [(none)]> create database MyCatDB;
	Query OK, 1 row affected (0.00 sec)

	MariaDB [(none)]> grant all on MyCatDB.* to "MyCat"@"%" identified by "123123";
	Query OK, 0 rows affected (0.01 sec)

	MariaDB [(none)]> grant all on MyCatDB.* to "MyCat"@"localhost" identified by "123123";
	Query OK, 0 rows affected (0.00 sec)

	[root@localhost ~]# wget http://dl.mycat.io/1.6.5/Mycat-server-1.6.5-release-20180122220033-linux.tar.gz
	[root@localhost ~]# tar -xzvf Mycat-server-1.6.5-release-20180122220033-linux.tar.gz -C /usr/local/
	[root@localhost ~]# vim /usr/local/mycat/conf/server.xml

	<user name="root" defaultAccount="true"> #指定MyCat登陆用户名
	<property name="password">123456</property> #指定密码
	<property name="schemas">MyCatDB</property> #指定同步数据库
	</user>

	<user name="user"> #指定MyCat登陆用户名
	<property name="password">123456</property> #指定密码
	<property name="schemas">MyCatDB</property> #指定同步数据库
	<property name="readOnly">true</property> #指定只读
	</user>

	[root@localhost ~]# vim /usr/local/mycat/conf/schema.xml

	<?xml version="1.0"?>
	<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
	<mycat:schema xmlns:mycat="http://io.mycat/">

	<schema name="MyCatDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn_test"> </schema>
	<dataNode name="dn_test" dataHost="localhost" database="MyCatDB" />

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

	<heartbeat>select user()</heartbeat>
	<writeHost host="Master1" url="192.168.1.11:3306" user="MyCat" password="123123"> #配置写主机
	<readHost host="Slave1" url="192.168.1.12:3306" user="MyCat" password="123123" /> #配置读主机
	<readHost host="Slave2" url="192.168.1.13:3306" user="MyCat" password="123123" /> #配置读主机
	</writeHost>
	</dataHost>
	</mycat:schema>

	[root@localhost ~]# /usr/local/mycat/bin/mycat start
	[root@localhost ~]# netstat -an \|grep "9066" #虚拟schema管理端口
	[root@localhost ~]# netstat -an \|grep "8066" #虚拟schema登陆端口

	[root@localhost ~]# mysql -uroot -p123456 -h 127.0.0.1 -P 9066

	Welcome to the MariaDB monitor. Commands end with ; or \g.
	Your MySQL connection id is 2
	Server version: 5.6.29-mycat-1.6.5-release-20180122220033 MyCat Server (monitor)

	Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

	Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

	MySQL [(none)]> show @@heartbeat; #RS_CODE为1表示心跳正常
	+---------+-------+--------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
	\| NAME \| TYPE \| HOST \| PORT \| RS_CODE \| RETRY \| STATUS \| TIMEOUT \| EXECUTE_TIME \| LAST_ACTIVE_TIME \| STOP \|
	+---------+-------+--------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
	\| Master1 \| mysql \| 192.168.1.11 \| 3306 \| -1 \| 0 \| idle \| 0 \| 0,16,16 \| 2018-12-21 01:29:43 \| false \|
	\| Slave1 \| mysql \| 192.168.1.12 \| 3306 \| -1 \| 0 \| idle \| 0 \| 34,31,31 \| 2018-12-21 01:29:43 \| false \|
	\| Slave2 \| mysql \| 192.168.1.13 \| 3306 \| -1 \| 0 \| idle \| 0 \| 1,16,16 \| 2018-12-21 01:29:43 \| false \|
	+---------+-------+--------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
	3 rows in set (0.00 sec)

	MySQL [(none)]> show @@datasource; #查看读写分离的机器配置情况
	+----------+---------+-------+--------------+------+------+--------+------+------+---------+-----------+------------+
	\| DATANODE \| NAME \| TYPE \| HOST \| PORT \| W/R \| ACTIVE \| IDLE \| SIZE \| EXECUTE \| READ_LOAD \| WRITE_LOAD \|
	+----------+---------+-------+--------------+------+------+--------+------+------+---------+-----------+------------+
	\| dn_test \| Master1 \| mysql \| 192.168.1.11 \| 3306 \| W \| 0 \| 0 \| 1000 \| 0 \| 0 \| 0 \|
	\| dn_test \| Slave1 \| mysql \| 192.168.1.12 \| 3306 \| R \| 0 \| 0 \| 1000 \| 0 \| 0 \| 0 \|
	\| dn_test \| Slave2 \| mysql \| 192.168.1.13 \| 3306 \| R \| 0 \| 0 \| 1000 \| 0 \| 0 \| 0 \|
	+----------+---------+-------+--------------+------+------+--------+------+------+---------+-----------+------------+
	3 rows in set (0.01 sec)

	MySQL [(none)]>

	[root@localhost ~]# mysql -uroot -p123456 -h127.0.0.1 -P 8066

	Welcome to the MariaDB monitor. Commands end with ; or \g.
	Your MySQL connection id is 3
	Server version: 5.6.29-mycat-1.6.5-release-20180122220033 MyCat Server (OpenCloundDB)

	Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

	Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

	MySQL [(none)]>
	MySQL [(none)]>
	MySQL [(none)]>
	MySQL [(none)]> show databases;
	+----------+
	\| DATABASE \|
	+----------+
	\| MyCatDB \|
	+----------+
	1 row in set (0.00 sec)

	MySQL [(none)]>

	[root@localhost ~]# wget http://www-eu.apache.org/dist/zookeeper/zookeeper-3.4.12/zookeeper-3.4.12.tar.gz
	[root@localhost ~]# wget http://dl.mycat.io/mycat-web-1.0/Mycat-web-1.0-SNAPSHOT-20170102153329-linux.tar.gz

	[root@localhost ~]# yum install -y mariadb mariadb-server
	[root@localhost ~]# vim /etc/my.cnf

	[mysqld]
	lower_case_table_names = 1

	[root@localhost ~]# systemctl restart mariadb

	[root@localhost ~]# tar -xzvf zookeeper-3.4.12.tar.gz -C /usr/local/
	[root@localhost ~]# cd /usr/local/zookeeper-3.4.12/conf/
	[root@localhost ~]# cp zoo_sample.cfg zoo.cfg
	[root@localhost ~]# vim zoo.cfg

	dataDir=/usr/local/...
	dataLogDir=/usr/local/...

	[root@localhost ~]# cd /usr/local/zookeeper-3.4.12/bin/
	[root@localhost ~]# ./zkServer.sh start
	[root@localhost ~]# netstat -ant \| grep 2181
	tcp 0 0 :::2181 :::* LISTEN

	如果出现错误：nohup: failed to run command `java’: No such file or directory
	可以在zkServer.sh中的首行添加如下代码

	export JAVA_HOME=/usr/lib/jdk
	export PATH=$JAVA_HOME/bin:$PATH

	[root@localhost ~]# tar -xzvf Mycat-web-1.0-SNAPSHOT-20170102153329-linux.tar.gz -C /usr/local
	[root@localhost ~]# cd /usr/local/mycat-web
	[root@localhost ~]# ./start.sh &
	[root@localhost ~]# netstat -an \| grep "8082"
	tcp 0 0 :::8082 :::* LISTEN

	[root@localhost ~]# curl http://localhost:8082/mycat