分布式专题（十二）mysql分库分表及mycat

最新推荐文章于 2023-08-20 22:36:15 发布

一点寒芒先至

最新推荐文章于 2023-08-20 22:36:15 发布

阅读量291

点赞数

分类专栏：分布式专题 mysql+Oracle

本文链接：https://blog.csdn.net/q975583865/article/details/84984342

版权

mysql+Oracle 同时被 2 个专栏收录

19 篇文章 0 订阅

订阅专栏

分布式专题

18 篇文章 7 订阅

订阅专栏

分库分表解决的问题

超大容量问题

性能问题

1.垂直切分和水平切分

垂直切分

垂直分库：解决的是表过多的问题

垂直分表：解决单表列过多的问题

水平切分：大数据表拆成小表

拆分策略：

垂直拆分（er分片）

水平拆分

一致性hash

范围切分可以按照ID

日期拆分

拆分以后带来的问题

1.跨库join的问题，用服务远程调用解决

2.全局表

3.做字段冗余（空间换时间的做法）

4.跨分片数据排序分页

5.唯一主键问题，解决方案：

UUID 性能比较低

snowflake

mongoDB

zookeeper

数据库表

6.分布式事务问题

多个数据库之间保证原子性性能问题：用强一致性较少

如何权衡当前公司的存储需要优化

提前规划（主键问题解决、 join问题）
当前数据单表超过1000W、每天的增长量持续上升

2.mysql主从

一般为单主多从，如果要实现双主，使用mysql+keepalived

centos版本7

mysql版本5.7 注意mysql5.7和5.6的不同，会生成一个随机码用于登录root，及修改安全策略，用于设置简单的密码

准备两台虚拟机，我这里是192.168.116.101和192.168.116.102 101为maste,102为slave,配置静态ip见

安装mysql

yum install wget

wget http://dev.mysql.com/get/mysql57-community-release-el7-7.noarch.rpm

rpm -ivh mysql57-community-release-el7-7.noarch.rpm

yum install mysql-server

启动mysql

systemctl start mysqld

grep "password" /var/log/mysqld.log

2018-12-15T02:08:24.814261Z 1 [Note] A temporary password is generated for root@localhost: uDjQ(eOqV1qy
2018-12-15T02:09:48.433056Z 2 [Note] Access denied for user 'root'@'localhost' (using password: YES)

其中蓝色的部分为mysql登录的随机密码，复制

mysql -uroot -p

修改安全策略

set global validate_password_length=1;

set global validate_password_policy=0;

set password=password("root");

grant all privileges on *.* to 'root'@'%' identified by 'root' with grant option;

exit;

master机器（101的机器）操作

master创建一个用户，并且允许其他服务器可以通过该用户远程访问master，通过该用户去读取二进制数据，实现数据同步

mysql -uroot -proot

create user huang identified by 'huang';

grant replication slave on *.* to 'huang'@'%' identified by 'huang'; 给huang用户赋予允许同步的权限

quit;

mysql5.7版本

安装以后文件对应的目录

mysql的数据文件和二进制文件： /var/lib/mysql/

mysql的配置文件： /etc/my.cnf

mysql的日志文件： /var/log/mysql.log

vi /etc/my.cnf 添加

log-bin=mysql-bin

server-id=101

systemctl restart mysqld

mysql -uroot -proot

show master status;

备份：mysqldump -uroot -p*** --databases exchange_buildlaw>/opt/exchange_buildlaw.sql

还原：mysql -uroot -p*** -f exchange_buildlaw</opt/exchange_buildlaw.sql

slave机器（102的机器）操作

vi /etc/my.cnf 添加

server-id=102

relay-log=slave-relay-bin

relay-log-index=slave-relay-bin.index

read_only=1

systemctl restart mysqld

mysql -uroot -proot

change master to master_host='192.168.116.101',master_port=3306,master_user='huang',master_password='huang',master_log_file='mysql-bin.000002',master_log_pos=154;

注意master_log_file=后面写的是master的二进制文件名，即上面绿色的部分mysql-bin.000002,master_log_pos=后面写的是master的Position ,即154

---------------============--------

mysql主从复制跳过错误

vi /etc/my.cnf.d/server.cnf

slave-skip-errors=all #跳过所有错误

-------===========---------------

start slave;

show slave status\G;

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

看到这两行即可说明同步成功

3.同步原理

binlog记录数据更新

binlog格式：

statement:基于SQL语句，存在一定为题，比如执行uuid函数，数据不同步

row:基于行模式，记录每一条修改数据

mixed:混合模式，由mysql自动判断处理

查看使用的模式：

show variables like '%log%';

修改binlog模式：

方式1：set global binlog_format='mixed';

方式2： vim /etc/my.cnf

添加 binlog_format=row

4.同步的问题

binlog_cache（性能和数据安全做权衡）

sync_binlog=0 文件系统来调度吧binlog_cache刷新的到磁盘，当=1，安全性最高，性能低（=0 性能高，安全低）

sync_binlog=n

sync_binlog”：这个参数是对于MySQL系统来说是至关重要的，他不仅影响到Binlog对MySQL所带来的性能损耗，而且还影响到MySQL中数据的完整性。对于“sync_binlog”参数的各种设置的说明如下：

sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。

sync_binlog=n，当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

在MySQL中系统默认的设置是sync_binlog=0，也就是不做任何强制性的磁盘刷新指令，这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。而当设置为“1”的时候，是最安全但是性能损耗最大的设置。因为当设置为1的时候，即使系统Crash，也最多丢失binlog_cache中未完成的一个事务，对实际数据没有任何实质性影响。

从以往经验和相关测试来看，对于高并发事务的系统来说，“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。

延迟监控

Nagios 做网络监控

mk-heartbeat

应用层解决：redis

5.mycat（依赖jdk1.7以上）

mycat主要用于解决水平拆分：数据量大的表分片

我测试的电脑ip为192.168.116.100,先装mysql。之后下载mycat，解压。

主要配置文件为conf/schema.xml
各个属性介绍

1.修改schema.xml

vim conf/schema.xml

清空

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

<!-- 数据库配置，与server.xml中的数据库对应 -->
    <schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100">
		<table name="company"  dataNode="dn1,dn2,dn3" rule="mod-long" />
    </schema>

<!-- 分片配置 -->
    <dataNode name="dn1" dataHost="localhost1" database="db1" />
	<dataNode name="dn2" dataHost="localhost1" database="db2" />
	<dataNode name="dn3" dataHost="localhost1" database="db3" />



<!-- 物理数据库配置 -->
    <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"  writeType="0" dbType="mysql" dbDriver="native">
        <heartbeat>select user();</heartbeat>
        <writeHost host="hostS1" url="192.168.116.100:3306" user="root" password="root" >  
        </writeHost>
    </dataHost>


</mycat:schema>

2.查看conf/rule.xml 里的mod-long对应的function为3（可略）

3.在192.168.116.100这个mysql里新建3个数据库db1,db2,db3，并都创建company表（有id,name即可，3个表结构一致）

4.启动mycat ./bin/mycat start

开启8066 和 9066 端口

5.新建navicat连接 ip:192.168.116.100 端口：8066 用户名密码在conf/server.xml里可见（ root 123456）

6.执行sql语句

insert into company(id,name) values(1,'tom');
insert into company(id,name) values(2,'tom');
insert into company(id,name) values(3,'tom');
insert into company(id,name) values(4,'tom');
insert into company(id,name) values(5,'tom');

select * from company;
explain select * from company;

这时去看对应的3个库的数据。已经完成切分

遇到的一个小问题

切分规则

单库--连续分片之按日期（天,月）分片

<tableRule name="sharding-by-month">
        <rule>
                <columns>createtime</columns>
                <algorithm>partbymonth</algorithm>
        </rule>
</tableRule>
	<function name="partbymonth"
		class="io.mycat.route.function.PartitionByMonth">
		<property name="dateFormat">yyyy-MM-dd HH:mm:ss</property>
		<property name="sBeginDate">2019-03-01 00:00:00</property>
	</function>

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
 
<!-- 数据库配置，与server.xml中的数据库对应 -->
    <schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100">
		<table name="company"  dataNode="dn1"  subTables="company2019$3-12,company2020$1-12"  rule="sharding-by-month" />
    </schema>
 
<!-- 分片配置 -->
    <dataNode name="dn1" dataHost="localhost1" database="db1" />
 
 
 
<!-- 物理数据库配置 -->
    <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"  writeType="0" dbType="mysql" dbDriver="native">
        <heartbeat>select user();</heartbeat>
        <writeHost host="hostS1" url="192.168.116.101:3306" user="root" password="root" >  
        </writeHost>
    </dataHost>
 
 
</mycat:schema>

建表

insert into company(id,name,a_time) values(8,'tom','2020-01-11 11:11:11');

一点寒芒先至

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
分布式专题（十二）mysql分库分表及mycat

分库分表解决的问题超大容量问题性能问题1.垂直切分和水平切分垂直切分垂直分库：解决的是表过多的问题垂直分表：解决单表列过多的问题水平切分：大数据表拆成小表拆分策略：垂直拆分（er分片）水平拆分一致性hash范围切分可以按照ID日期拆分拆分以后带来的问题1.跨库join的问题，用...
复制链接

扫一扫

专栏目录