分库分表解决的问题
超大容量问题
性能问题
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';
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;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
备份 :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
清空
gg
dG
<?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');