海阳之新's博客

每天进步一点点...

0%

基于Mycat中间件实现MySQL的分表分库与读写分离

发表于 分类于 ， Valine：
本文字数： 12k 阅读时长 ≈ 11 分钟

一、安装Java

官网下载jdk1.8.0_212.tar.gz，解压到如下路径即可。
本次安装路径为：/usr/local/java/jdk1.8.0_212/
安装过程略。

二、安装MyCat

官网：http://www.mycat.io/
GitHub地址：https://github.com/MyCATApache/Mycat-Server
下载地址：http://dl.mycat.io/1.6.7.1/Mycat-server-1.6.7.1-release-20190627191042-linux.tar.gz
本次安装采用最新版本Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz，解压到如下路径即可。
本次安装路径为：/usr/local/mycat

1
2
3
4
cd /usr/local/src/
#wget -c http://dl.mycat.io/1.6.7.1/Mycat-server-1.6.7.1-release-20190627191042-linux.tar.gz
tar -xzvf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz
mv mycat ../

MyCat 1.6.5-release和1.6.7.4-release都测试过了，在jdk1.8环境下都是没有问题的。

三、配置环境变量

1
2
3
4
5
6
7
vim /etc/profile
#在文件末尾加上如下代码：
export JAVA_HOME=/usr/local/java/jdk1.8.0_212/
export JRE_HOME=$JAVA_HOME/jre
export CLASSPATH=$JAVA_HOME/lib:$JRE_HOME/lib:.
export MYCAT_HOME=/usr/local/mycat
export PATH=$JAVA_HOME/bin:$JRE_HOME/bin:$MYCAT_HOME/bin:$PATH

四、创建MyCat用户

1
2
3
adduser mycat 
chown -R mycat:mycat /usr/local/mycat
chmod -R 777 /usr/local/mycat/bin

五、配置MyCat

本文以笔者之前开发的一个广告平台项目ArtarvaSSP为例。

1、schema.xml配置如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
	<schema name="db_ssp" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1">
		<table name="ssp_ads_fail_log" primaryKey="id" autoIncrement="true" subTables="ssp_ads_fail_log_2020_$1-52" rule="sharding-by-date" dataNode="dn2" />
		<table name="mycat_sequence" primaryKey="name" type="global" dataNode="dn1" />
	</schema>
	<dataNode name="dn1" dataHost="dh1" database="db_ssp" />
	<dataNode name="dn2" dataHost="dh2" database="db_ssp_log" />

	<dataHost name="dh1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<writeHost host="hostM108_1" url="localhost:3306" user="db_ssp" password="SrNp6JKKmN26xwXG"/>
	</dataHost>

	<dataHost name="dh2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<writeHost host="hostM108_2" url="localhost:3306" user="db_ssp_log" password="ARZ76eCrkni4ZmTs"/>
	</dataHost>
	
</mycat:schema>

这里主要是将ssp_ads_fail_log表分离到一个独立的库db_ssp_log中（放在dn2中），所以在schema中，单独声明了<table name="ssp_ads_fail_log" primaryKey="id" autoIncrement="true" subTables="ssp_ads_fail_log_2020_$1-52" rule="sharding-by-date" dataNode="dn2" />，分片规则为按日期划分（见rule.xml文件），52周共计52张表（$1-52）。由于db_ssp_log库设置了独立的用户名密码，于是新建dn2。
mycat_sequence为全局自增表，主要用来接管数据库的自增字段，见文末附件。

2、server.xml配置如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
	<system>
	<property name="nonePasswordLogin">0</property> <!-- 0为需要密码登陆、1为不需要密码登陆 ,默认为0，设置为1则需要指定默认账户-->
	<property name="useHandshakeV10">1</property>
	<property name="useSqlStat">0</property> 
	<property name="useGlobleTableCheck">0</property>
    <property name="sequnceHandlerType">1</property> <!-- 指定使用 Mycat 全局序列的类型。0为本地文件方式，1为数据库方式，2为时间戳序列方式，3为分布式ZK ID 生成器，4 为zk 递增 id 生成。 -->
	<property name="subqueryRelationshipCheck">false</property>
		<property name="processorBufferPoolType">0</property>
		<property name="serverPort">8066</property><!-- 设置服务端口为8066 -->
		<property name="managerPort">9066</property><!-- 设置管理端口为9066 -->
		<property name="handleDistributedTransactions">0</property>
		<property name="useOffHeapForMerge">1</property>
        <property name="memoryPageSize">64k</property>
		<property name="spillsFileBufferSize">1k</property>
		<property name="useStreamOutput">0</property>
		<property name="systemReserveMemorySize">384m</property>
		<property name="useZKSwitch">false</property>

	</system>

	<user name="db_ssp" defaultAccount="true">
		<property name="password">SrNp6JKKmN26xwXG</property>
		<property name="schemas">db_ssp</property>
	</user>
</mycat:server>

这里主要配置了连接MyCat的用户名密码，允许访问的数据库，多个时可以用逗号隔开。为了让之前的项目从MySQL连接无缝切换到MyCat，这里的用户名密码与MySQL保持一致，到时只需要在项目里修改一下连接端口为8066就可以了。MyCat的服务端口为8066，如设置<property name="serverPort">8066</property>。

3、rule.xml配置如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">
	<tableRule name="sharding-by-date">
		<rule>
			<columns>access_date</columns>
			<algorithm>sharding-by-date</algorithm>
		</rule>
	</tableRule>
	<function name="sharding-by-date" class="io.mycat.route.function.PartitionByDate">
		<property name="dateFormat">yyyyMMdd</property>
		<property name="sBeginDate">20200101</property>
		<property name="sEndDate">20201229</property>
		<property name="sPartionDay">7</property>
	</function>

</mycat:rule>

这里主要是按日期（7日）来切分数据表，最近7日的数据放在一个数据表，2020年全年52周零2天。
注意：开始日期与结束日期的天数必须能整除7，否则MyCat启动不了。

4、log4j2.xml日志配置：
conf/log4j2.xml文件，配置如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
<?xml version="1.0" encoding="UTF-8"?>
<Configuration status="WARN">
    <Appenders>
        <Console name="Console" target="SYSTEM_OUT">
            <PatternLayout pattern="%d [%-5p][%t] %m %throwable{full} (%C:%F:%L) %n"/>
        </Console>

        <RollingFile name="RollingFile" fileName="${sys:MYCAT_HOME}/logs/mycat.log"
                     filePattern="${sys:MYCAT_HOME}/logs/$${date:yyyy-MM}/mycat-%d{MM-dd}-%i.log.gz">
        <PatternLayout>
                <Pattern>%d{yyyy-MM-dd HH:mm:ss.SSS} %5p [%t] (%l) - %m%n</Pattern>
            </PatternLayout>
            <Policies>
                <OnStartupTriggeringPolicy/>
                <SizeBasedTriggeringPolicy size="250 MB"/>
                <TimeBasedTriggeringPolicy/>
            </Policies>
        </RollingFile>
    </Appenders>
    <Loggers>
        <!--<AsyncLogger name="io.mycat" level="info" includeLocation="true" additivity="false">-->
            <!--<AppenderRef ref="Console"/>-->
            <!--<AppenderRef ref="RollingFile"/>-->
        <!--</AsyncLogger>-->
        <asyncRoot level="debug" includeLocation="true"> <!--日志级别设置，取值有： info、ware、 debug-->

            <!--<AppenderRef ref="Console" />-->
            <AppenderRef ref="RollingFile"/>

        </asyncRoot>
    </Loggers>
</Configuration>

建议在上线前期将日志级别修改成debug，可以看到详细的SQL执行日志（如调度到了哪台主机），观察一段时间，方便排查问题。Mycat运行稳定后将级别再调整为 info/ware（生产环境建议设置）。

六、启动MyCat

1
2
3
4
5
6
su - mycat #切换到mycat用户
source /etc/profile
mycat install #将mycat安装成自启动服务
mycat start
mycat status
Mycat-server is running (18098). #看到此消息说明启动成功了

如果启动失败，可以查看logs目录下的日志文件。

常用命令：
mycat { console | start | stop | restart | status | dump }

附录

mycat_sequence结构如下：

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE `mycat_sequence` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL DEFAULT '' COMMENT '自增变量名称',
  `current_value` int(11) unsigned NOT NULL COMMENT '当前值',
  `increment` int(11) unsigned NOT NULL DEFAULT '100' COMMENT '增量步长',
  `remark` varchar(100) NOT NULL DEFAULT '' COMMENT '备注（选填）',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `mycat_sequence_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `mycat_sequence`(`name`, `current_value`, `increment`, `remark`) VALUES ('GLOBAL', 100000, 100, '公共自增');
INSERT INTO `mycat_sequence`(`name`, `current_value`, `increment`, `remark`) VALUES ('ADS_FAIL_LOG', 4261454, 1, '广告日志自增');

在插入记录前，通过Go查询表获取一个自增数字，如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
id, err := getMycatSequence("ADS_FAIL_LOG") //获取自增值

func getMycatSequence(name string) (int64, error) {
	var currentValue,increment,newValue int64
	db := models.ConnDB()
	defer db.Close()

	tx, err := db.Begin()
	defer func() {
		switch {
		case err != nil:
			tx.Rollback()
		default:
			err = tx.Commit()
		}
	}()

	err = db.QueryRow("SELECT `current_value`,`increment` FROM `mycat_sequence` WHERE `name` = ? ORDER BY `name` asc LIMIT 1", name).Scan(&currentValue, &increment)
	if err == sql.ErrNoRows {
		panic("没有查询到mycat_sequence表中 "+ name +" 相关自增配置~\n")
		return newValue, errors.New("")
	} else if err != nil {
		return newValue, err
	}
	
	newValue = currentValue + increment
	stmt, err := db.Prepare("UPDATE `mycat_sequence` SET `current_value` = ? where `name`= ? ")
	defer stmt.Close()
	if err != nil {
		beego.Error(err)
	}
	_, err = stmt.Exec(newValue, name)
	if err != nil {
		beego.Error(err)
	}

	return newValue, nil
}

遇到的问题

1、通过Navicat连接到Mycat时，提示too many connections，将连接数修改成1000，一会又出现前面的提示，通过show processlist;，发现有大量的线程处于Waiting for table level lock，执行的操作是往日志表ssp_ads_fail_log表（MyISAM存储引擎）中insert数据，由于日志信息是实时的，每次只插入一条。
在查询一些资料后，将问题定位到存储引擎的问题上来，主要是因为MyISAM是表级锁，频繁插入，锁的开销很大，MyISAM更适合批量插入场景，于是将存储引擎又改回InnoDB。

查看磁盘I/O繁忙程度的命令：iostat -x 2 10，查看最后几列数据。
命令可参考：https://www.cnblogs.com/cynchanpin/p/6936977.html

2、将MyISAM修改成InnoDB时，又遇到了新的问题，磁盘满了（数据库日志、项目日志、MyCat debug日志都挺多的，再加上备份数据库的包也挺多的）
于是开始清理磁盘，先找出前10个大文件du -m / | sort -n -r | head -n 10，清理多余的文件，然后将表修改成InnoDB：

1
alter table ssp_ads_fail_log engine="InnoDB";

再查看一下表的存储引擎是否已经修改成功：

1
2
show table status from db_ssp_log\G;
show engines; #查看数据库支持哪些存储引擎

参考：

https://segmentfault.com/a/1190000014767902 雪花算法

https://tech.meituan.com/2017/04/21/mt-leaf.html Leaf——美团点评分布式ID生成系统

https://www.jianshu.com/p/80069f6153d6 扩展阅读