一 MyCAT基础架构图
箭头指向谁是主库 192.168.80.51:3307 <-----> 192.168.80.52:3307 192.168.80.51:3309 ------> 192.168.80.51:3307 192.168.80.52:3309 ------> 192.168.80.52:3307
二 环境准备
2.1 后续命令没有指定主机，则两台都要操作
两台虚拟机 db01 db02 每台创建2个mysql实例：3307 3308
2.2 删除历史环境
pkill mysqld \rm -rf /data/33* mv /etc/my.cnf /etc/my.cnf.bak
2.3 创建数据目录，并初始化数据字典
mkdir /data/330{7,9}/data -p
mysqld --initialize-insecure --user=mysql --datadir=/data/3307/data --basedir=/opt/mysql
mysqld --initialize-insecure --user=mysql --datadir=/data/3309/data --basedir=/opt/mysql
2.4 准备配置文件和启动文件
==============================db01============================== cat >/data/3307/my.cnf<<EOF [mysqld] basedir=/opt/mysql datadir=/data/3307/data socket=/data/3307/mysql.sock port=3307 log-error=/data/3307/mysql.log log_bin=/data/3307/mysql-bin binlog_format=row skip-name-resolve server-id=7 gtid-mode=on enforce-gtid-consistency=true log-slave-updates=1 EOF cat >/data/3309/my.cnf<<EOF [mysqld] basedir=/opt/mysql datadir=/data/3309/data socket=/data/3309/mysql.sock port=3309 log-error=/data/3309/mysql.log log_bin=/data/3309/mysql-bin binlog_format=row skip-name-resolve server-id=9 gtid-mode=on enforce-gtid-consistency=true log-slave-updates=1 EOF cat >/etc/systemd/system/mysqld3307.service<<EOF [Unit] Description=MySQL Server Documentation=man:mysqld(8) Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] User=mysql Group=mysql ExecStart=/opt/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf LimitNOFILE = 5000 EOF cat >/etc/systemd/system/mysqld3309.service<<EOF [Unit] Description=MySQL Server Documentation=man:mysqld(8) Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] User=mysql Group=mysql ExecStart=/opt/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf LimitNOFILE = 5000 EOF
==============================db02============================== cat >/data/3307/my.cnf<<EOF [mysqld] basedir=/opt/mysql datadir=/data/3307/data socket=/data/3307/mysql.sock port=3307 log-error=/data/3307/mysql.log log_bin=/data/3307/mysql-bin binlog_format=row skip-name-resolve server-id=17 gtid-mode=on enforce-gtid-consistency=true log-slave-updates=1 EOF cat >/data/3309/my.cnf<<EOF [mysqld] basedir=/opt/mysql datadir=/data/3309/data socket=/data/3309/mysql.sock port=3309 log-error=/data/3309/mysql.log log_bin=/data/3309/mysql-bin binlog_format=row skip-name-resolve server-id=19 gtid-mode=on enforce-gtid-consistency=true log-slave-updates=1 EOF cat >/etc/systemd/system/mysqld3307.service<<EOF [Unit] Description=MySQL Server Documentation=man:mysqld(8) Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] User=mysql Group=mysql ExecStart=/opt/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf LimitNOFILE = 5000 EOF cat >/etc/systemd/system/mysqld3309.service<<EOF [Unit] Description=MySQL Server Documentation=man:mysqld(8) Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] User=mysql Group=mysql ExecStart=/opt/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf LimitNOFILE = 5000 EOF
2.5 修改权限，启动多实例
chown -R mysql.mysql /data/* systemctl start mysqld3307 systemctl start mysqld3309
[root@db01 ~]# mysql -S /data/3307/mysql.sock -e "show variables like 'server_id'" +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 7 | +---------------+-------+ [root@db01 ~]# mysql -S /data/3309/mysql.sock -e "show variables like 'server_id'" +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 9 | +---------------+-------+
[root@db02 ~]# mysql -S /data/3307/mysql.sock -e "show variables like 'server_id'" +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 17 | +---------------+-------+ [root@db02 ~]# mysql -S /data/3309/mysql.sock -e "show variables like 'server_id'" +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 19 | +---------------+-------+
2.6 开始构建主从
192.168.80.51:3307 <-----> 192.168.80.52:3307
db02
mysql -S /data/3307/mysql.sock -e "grant replication slave on *.* to repl@'192.168.80.%' identified by '123';" mysql -S /data/3307/mysql.sock -e "grant all on *.* to root@'192.168.80.%' identified by '123' with grant option;"
db01
mysql -S /data/3307/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='192.168.80.52', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';" mysql -S /data/3307/mysql.sock -e "start slave;" mysql -S /data/3307/mysql.sock -e "show slave status\G"
db02
mysql -S /data/3307/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='192.168.80.51', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';" mysql -S /data/3307/mysql.sock -e "start slave;" mysql -S /data/3307/mysql.sock -e "show slave status\G"
192.168.80.51:3309 ------> 192.168.80.51:3307
db01
mysql -S /data/3309/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='192.168.80.51', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';" mysql -S /data/3309/mysql.sock -e "start slave;" mysql -S /data/3309/mysql.sock -e "show slave status\G"
192.168.80.52:3309 ------> 192.168.80.52:3307
db02
mysql -S /data/3309/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='192.168.80.52', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';" mysql -S /data/3309/mysql.sock -e "start slave;" mysql -S /data/3309/mysql.sock -e "show slave status\G"
2.7 检测主从状态
[root@db01 ~]# mysql -S /data/3307/mysql.sock -e "show slave status\G"|grep Yes Slave_IO_Running: Yes Slave_SQL_Running: Yes [root@db01 ~]# mysql -S /data/3309/mysql.sock -e "show slave status\G"|grep Yes Slave_IO_Running: Yes Slave_SQL_Running: Yes
[root@db02 ~]# mysql -S /data/3307/mysql.sock -e "show slave status\G"|grep Yes Slave_IO_Running: Yes Slave_SQL_Running: Yes [root@db02 ~]# mysql -S /data/3309/mysql.sock -e "show slave status\G"|grep Yes Slave_IO_Running: Yes Slave_SQL_Running: Yes
注：如果中间出现错误，在每个节点执行以下命令后重新构建主从 mysql -S /data/3307/mysql.sock -e "stop slave; reset slave all;" mysql -S /data/3309/mysql.sock -e "stop slave; reset slave all;"
三 MyCAT安装（ db01上操作）3.1 软件下载官网： http://mycat.org.cn/ GitHub地址： https://github.com/MyCATApache/Mycat-Server 本次使用版本：Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz 3.2 安装Java运行环境
yum install -y java
3.3 解压文件
tar -xf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz -C /opt/
3.4 软件目录结构
[root@db01 ~]# cd /opt/mycat/ [root@db01 mycat]# ls bin catlet conf lib logs tmlogs version.txt
3.5 文件介绍
logs目录: wrapper.log ---->mycat启动日志 mycat.log ---->mycat详细工作日志 conf目录: schema.xml 主配置文件（读写分离、高可用、分布式策略定制、节点控制） server.xml mycat软件本身相关的配置 rule.xml 分片规则配置文件,记录分片规则列表、使用方法等
3.6 添加环境变量
echo 'export PATH=$PATH:/opt/mycat/bin' >> /etc/profile source /etc/profile
3.7 启动连接
[root@db01 mycat]# mycat start Starting Mycat-server... [root@db01 mycat]# mycat status Mycat-server is running (30737). [root@db01 mycat]# mysql -uroot -p123456 -h 127.0.0.1 -P8066 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.29-mycat-1.6.7.4-release-20200105164103 MyCat Server (OpenCloudDB) Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
3.8 用户创建及库表
mysql -S /data/3307/mysql.sock mysql> grant all on *.* to root@'10.0.0.%' identified by '123'; mysql> create database if not exists test default character set = 'utf8mb4'; mysql> use test; mysql> create table t1(id int); mysql> insert into t1 values(1),(2),(3);
四 读写分离结构配置4.1 编写配置文件
cd /opt/mycat/conf/ mv schema.xml schema.xml.default
[root@db01 conf]# vim schema.xml <?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <schema name="test" checkSQLschema="false" sqlMaxLimit="100" dataNode="sz1"> </schema> <schema name="mysql" checkSQLschema="false" sqlMaxLimit="100" dataNode="sz2"> </schema> <dataNode name="sz1" dataHost="easydb" database= "test" /> <dataNode name="sz2" dataHost="easydb" database= "mysql" /> <dataHost name="easydb" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchTyp e="1"> <heartbeat>select user()</heartbeat> <writeHost host="db1" url="192.168.80.51:3307" user="root" password="123"> <readHost host="db2" url="192.168.80.51:3309" user="root" password="123" /> </writeHost> </dataHost> </mycat:schema>
4.2 添加用户可访问的逻辑库
[root@db01 conf]# vim server.xml
4.3 重启mycat
[root@db01 conf]# mycat restart Stopping Mycat-server... Stopped Mycat-server. Starting Mycat-server... [root@db01 conf]# mycat status Mycat-server is running (31059).
4.4 测试读写分离
[root@db01 conf]# mysql -uroot -p -h 127.0.0.1 -P8066 Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.29-mycat-1.6.7.4-release-20200105164103 MyCat Server (OpenCloudDB) Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +----------+ | DATABASE | +----------+ | mysql | | test | +----------+
mysql> show variables like 'server_id'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 9 | +---------------+-------+ 1 row in set (0.00 sec) mysql> begin;show variables like 'server_id';commit; Query OK, 0 rows affected (0.00 sec) +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 7 | +---------------+-------+ 1 row in set (0.01 sec) Query OK, 0 rows affected (0.00 sec)
4.5 总结以上案例实现了1主1从的读写分离功能，写操作落到主库，读操作落到从库.如果主库宕机，从库不能在继续提供服务了。 五 配置读写分离及高可用5.1 编写配置文件
[root@db01 conf]# vim schema.xml <?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <schema name="test" checkSQLschema="false" sqlMaxLimit="100" dataNode="sh1"> </schema> <schema name="mysql" checkSQLschema="false" sqlMaxLimit="100" dataNode="sh2"> </schema> <dataNode name="sh1" dataHost="easydb" database= "test" /> <dataNode name="sh2" dataHost="easydb" database= "mysql" /> <dataHost name="easydb" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchTyp e="1"> <heartbeat>select user()</heartbeat> <writeHost host="db1" url="192.168.80.51:3307" user="root" password="123"> <readHost host="db2" url="192.168.80.51:3309" user="root" password="123" /> </writeHost> <writeHost host="db3" url="192.168.80.52:3307" user="root" password="123"> <readHost host="db4" url="192.168.80.52:3309" user="root" password="123" /> </writeHost> </dataHost> </mycat:schema>
5.2 重启mycat
[root@db01 conf]# mycat restart Stopping Mycat-server... Stopped Mycat-server. Starting Mycat-server... [root@db01 conf]# mycat status Mycat-server is running (31059).
5.3 测试读写分离
[root@db01 conf]# mysql -uroot -p -h 127.0.0.1 -P8066 mysql> show variables like 'server_id'; mysql> show variables like 'server_id'; mysql> show variables like 'server_id'; mysql> begin;show variables like 'server_id';commit;
5.4 关闭db01 3307节点，测试高可用
[root@db01 conf]# systemctl stop mysqld3307.service
mysql> begin;show variables like 'server_id';commit; Query OK, 0 rows affected (0.00 sec) +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 17 | +---------------+-------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'server_id'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 19 | +---------------+-------+ 1 row in set (0.01 sec)
5.5 总结
真正的 writehost：负责写操作的writehost standby writeHost ：和readhost一样，只提供读服务 当写节点宕机后，后面跟的readhost也不提供服务，这时候standby的writehost就提供写服务， 后面跟的readhost提供读服务 当宕机节点恢复后，将加入从节点，复制读操作
六 配置文件标签解释schema属性
schema 标签用于定义 MyCat 实例中的逻辑库，MyCat 可以有多个逻辑库，每个逻辑库都有自己的相关配 置。可以使用 schema 标签来划分这些不同的逻辑库
dataNode属性
该属性用于绑定逻辑库到后端数据库的某个具体的 database 上
database 属性
对应后端真实的数据库的 database 名
dataHost 属性
该属性用于定义该逻辑库属于哪个后端数据库实例的，属性值是引用 dataHost 标签上定义的 name 属性
