mysql5.7主从复制、mycat2配置读写分离

mysql5.7主从同步、mycat2配置读写分离（windows）

本文参考与转载自文章MySQL5.7主从同步配置、mycat2快速搭建、mycat2读写分离配置，仅用于记录自己的学习过程

一、mysql5.7配置主从同步

主从同步，将主服务器（master）上的数据复制到从服务器（slave）。

应用场景
读写分离，提高查询访问性能，有效减少主数据库访问压力。
实时灾备，主数据库出现故障时，可快速切换到从数据库。
数据汇总，可将多个主数据库同步汇总到一个从数据库中，方便数据统计分析。
部署环境
​ 注：我使用的是本机启动两个mysql在不同的端口。

数据库：MySQL 5.7.x （相比5.5，5.6而言，5.7同步性能更好，支持多源复制，可实现多主一从，主从库版本应保证一致）
操作系统：windows10
主库：IP=localhost; PORT=3307; server-id=1; database=test; table=user
从库：IP=localhost; PORT=3308; server-id=2; database=test; table=user
配置约束
主从库必须保证网络畅通可访问
主库必须开启binlog日志
主从库的server-id必须不同
主从库UUID必须不同（适用于复制的mysql）

【主库】操作及配置
配置my.ini

[mysql]
default-character-set=utf8

[mysqld]
port=3307
#其它配置忽略，以下是主要配置项
server_id=1
log-bin=mysql-bin  #[必须]启用二进制日志
expire-logs-days  = 7 #只保留7天的二进制日志，以防磁盘被日志占满
binlog-ignore-db  = mysql       #不备份的数据库
binlog-ignore-db  = information_schema
binlog-ignore-db  = performation_schema
binlog-ignore-db  = sys
binlog-do-db      = test #需要做复制的数据库名，如果有多个，复制binlog-do-db即可

创建授权用户
连接mysql主数据库，键入命令mysql -u root -p，输入密码后登录数据库。创建用户用于从库同步复制，授予复制、同步访问的权限

mysql> CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
Query OK, 0 rows affected (0.00 sec)

log_bin是否开启

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set

查看master状态

mysql> show master status;
+------------------+----------+--------------+--------------------------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                                 | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------------------------------+-------------------+
| mysql-bin.000008 |  2581064 | test         | mysql,information_schema,performation_schema,sys |                   |
+------------------+----------+--------------+--------------------------------------------------+-------------------+
1 row in set

注意：mysql-bin.000001 跟154这俩参数从库会使用到，根据实际情况修改

【从库】配置及操作

配置my.ini

[mysql]
default-character-set=utf8

[mysqld]
port=3308
#其它配置忽略，以下是主要配置项
server_id=2

设置主库信息
登录【从数据库】，进入mysql命令行。

mysql> stop slave;
Query OK, 0 rows affected

mysql> CHANGE MASTER TO MASTER_HOST='localhost',
MASTER_PORT=3307,
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000008', # 主库信息
MASTER_LOG_POS=2581064;   # 主库信息
Query OK, 0 rows affected

mysql> start slave;
Query OK, 0 rows affected

查看同步状态

mysql> show slave status;
+----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+----------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+-------------------------------------------------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+
| Slave_IO_State                   | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File  | Read_Master_Log_Pos | Relay_Log_File                   | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID                          | Master_Info_File                                                  | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State                                | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | Auto_Position | Replicate_Rewrite_DB | Channel_Name | Master_TLS_Version |
+----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+----------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+-------------------------------------------------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+
| Waiting for master to send event | localhost   | slave       |        3307 |            60 | mysql-bin.000008 |             2581064 | DESKTOP-RA9R2VD-relay-bin.000002 |       2580327 | mysql-bin.000008      | Yes              | Yes               |                 |                     |                    |                        |                         |                             |          0 |            |            0 |             2581064 |         2580544 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                     0 | No                            |             0 |               |              0 |                |                             |                1 | ae0a8ec4-6fc1-11e9-821a-4ccc6a4d7345 | E:\work\phpstudy_pro\Extensions\mysql-other\3308\data\master.info |         0 | NULL                | Slave has read all relay log; waiting for more updates |              86400 |             |                         |                          |                |                    |                    |                   |             0 |                      |              |                    |
+----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+----------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+-------------------------------------------------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+
1 row in set

状态是Slave has read all relay log; waiting for more updates就说明没问题

验证数据同步
建库
使用root账号登录【主服务器】，创建test数据库

mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.00 sec)

mysql> USE test;
Database changed

建表
在【主库】中创建user表

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `age` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

新增
在【主库】中向user表插入一条数据：

mysql> use test;
Database changed
mysql> INSERT INTO user (id, name, age) VALUES (1, '张三', 18);
Database changed
mysql> SELECT * FROM user;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | 张三 |  18 |
+----+------+-----+
1 row in set

在【从库】中查询user表数据：

mysql> use test;
Database changed
mysql> SELECT * FROM user;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | 张三 |  18 |
+----+------+-----+
1 row in set

新增记录同步成功。

更新
在【主库】中修改刚才插入的数据：

mysql> UPDATE user SET name='李四' where id=1;
Database changed
Rows matched: 1  Changed: 1  Warnings: 0
mysql> SELECT * FROM user;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | 李四 |  18 |
+----+------+-----+
1 row in set

在【从库】中查询user表数据：

mysql> SELECT * FROM user;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | 李四 |  18 |
+----+------+-----+
1 row in set

更新记录同步成功。

删除
在【主库】中删除刚才更新的数据：

mysql> DELETE FROM user WHERE id=1;
Database changed
mysql> SELECT * FROM user;
Empty set

在【从库】中查询user表数据：

mysql> SELECT * FROM user;
Empty set

删除记录同步成功。

补充：

如果【主服务器】重启mysql服务，【从服务器】会等待与【主服务器】重连。当主服务器恢复正常后，从服务器会自动重新连接上主服务器，并正常同步数据。
如果某段时间内，【从数据库】服务器异常导致同步中断（可能是同步点位置不匹配），可以尝试以下恢复方法：进入【主数据库】服务器（正常），在bin-log中找到【从数据库】出错前的position，然后在【从数据库】重新执行change master开始同步。

---

二、mycat2环境配置

1.下载对应的tar安装包,以及对应的jar包
zip包 http://dl.mycat.org.cn/2.0/install-template/mycat2-install-template-1.20.zip
jar包 http://dl.mycat.org.cn/2.0/1.21-release/ (下载最新的jar包)
由于官网可能受到DNS污染，需要修改hosts文件才能访问

210.51.26.184 mycat.org.cn www.mycat.org.cn dl.mycat.org.cn

下载所需的mycat2的fat jar 一般大小为100mb的一个jar文件
把这个jar放进解压的mycat\lib文件夹下

2. mysql创建用户,权限配置
创建用户 ,用户名为mycat,密码为123456,赋权限

CREATE USER 'mycat'@'%' IDENTIFIED BY '123456';
-- 视情况赋值的权限
GRANT ALL PRIVILEGES ON *.* TO 'mycat'@'%' ;
-- *【mysql8】需要要赋值的权限 
GRANT XA_RECOVER_ADMIN ON *.* TO 'root'@'%';
flush privileges;

3.修改mycat的prototype的配置
启动mycat之前需要确认prototype数据源所对应的mysql数据库配置，修改mycat\conf\datasources\prototypeDs.datasource.json文件如下

{
	"dbType":"mysql",
	"idleTimeout":60000,
	"initSqls":[],
	"initSqlsGetConnection":true,
	"instanceType":"WRITE",
	"maxCon":1000,
	"maxConnectTimeout":3000,
	"maxRetryCount":5,
	"minCon":1,
	"name":"prototypeDs",
	"password":"123456",
	"type":"JDBC",
	"url":"jdbc:mysql://localhost:3307/mysql?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",
	"user":"root",
	"weight":0
}

4.启动mycat

以下三种均为启动mycat服务的方式,根据环境选择以下即可

linux启动命令

cd mycat/bin
./mycat start
./mycat status
./mycat start 启动
./mycat stop 停止
./mycat console 前台运行
./mycat install 添加到系统自动启动（暂未实现）
./mycat remove 取消随系统自动启动（暂未实现）
./mycat restart 重启服务
./mycat pause 暂停
./mycat status 查看启动状态

windows启动命令

cd mycat/bin
 
 
#PowerShell
 
./mycat install
./mycat start
./mycat status
 
 
#CMD
 
mycat install
mycat start
mycat status

jar执行

java -DMYCAT_HOME=mycat2\src\main\resources -jar mycat2-0.5-SNAPSHOP.jar Mycat2\mycat2\src\main\resources 

#是配置文件所在文件夹

​

测试

利用mysql command进行连接测试,默认的mycat的用户: root,密码: 123456, 端口8066

mysql -uroot -p123456 -h192.168.28.130 -P8066

#创建一个数据库

CREATE DATABASE db1;

#创建单表
CREATE TABLE db1.`travelrecord` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `user_id` varchar(100) DEFAULT NULL,
  `traveldate` date DEFAULT NULL,
  `fee` decimal(10,0) DEFAULT NULL,
  `days` int DEFAULT NULL,
  `blob` longblob,
  PRIMARY KEY (`id`),
  KEY `id` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
#查询
select * from db1.travelrecord;

---

三、mycat2配置读写分离

注意：mycat不存在创建库，创建表操作，需要主节点创建库(物理库)后在mycat配置文件中配置逻辑库，表同理，分库分表需额外配置。
1. 准备工作
已经安装好的mycat,以及做好主从的mysql,mysql中有test这个库,且已经存在表和数据.

2. 直接通过命令进行配置
连接mycat端口8066 账号root 密码123456

mysql -uroot -p123456 -P8066 -h127.0.0.1
#创建db2逻辑库
create database test;

3.查看创建的schema的配置

customTables 放置的是mycat默认的表的配置
globalTables 放置的是全局表的配置
shardingTables 放置分片表的配置
normalTables 放置普通表的配置

mycat\conf\schemas\test.schema.json

{
    "customTables":{},
    "globalTables":{},
    "normalTables":{},
    "schemaName":"db2",
    "shardingTables":{},
    "views":{}
}

4.配置数据源
在mycat\conf\datasources下增加从数据库数据源配置文件：dr0.datasource.json

{
	"dbType":"mysql",
	"idleTimeout":60000,
	"initSqls":[],
	"initSqlsGetConnection":true,
	"instanceType":"READ",
	"maxCon":1000,
	"maxConnectTimeout":3000,
	"maxRetryCount":5,
	"minCon":1,
	"name":"dr0",
	"password":"123456",
	"type":"JDBC",
	"url":"jdbc:mysql://localhost:3308/?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",
	"user":"root",
	"weight":0
}

5.集群配置
修改mycat\conf\clusters\prototype.cluster.json文件如下
masters是主数据库数据源
replicas是从数据库数据源

{
	"clusterType":"MASTER_SLAVE",
	"heartbeat":{
		"heartbeatTimeout":1000,
		"maxRetry":3,
		"minSwitchTimeInterval":300,
		"slaveThreshold":0
	},
	"masters":[
		"prototypeDs"
	],
	"maxCon":200,
	"name":"prototype",
	"readBalanceType":"BALANCE_ALL",
	"switchType":"SWITCH",
	"replicas": [
		"dr0"
	]
}

6.修改test的schema配置

{
  "customTables":{},
  "globalTables":{},
  "normalTables":{},
  "schemaName":"test",
  "shardingTables":{},
  "targetName":"prototype"
}

修改之后并重启mycat

此时的架构图

配置完成

参考链接
MySQL5.7主从同步配置
mycat2快速搭建
mycat2读写分离配置
Mycat2 · 语雀