MySQL很容易成为整个应用的瓶颈, 经常使用的数据库往往面临数据量太大、数据访问太频繁、数据读写慢等;因此我们往往加入Redis、MQ削峰等手段来保护数据库
但也应提高MySQL自身的性能,如对MySQL进行优化配置、换成ClickHouse等针对大数据的产品、从单体->集群
1 MySQL主从同步(读写分离)
1.1 MySQL主从同步原理
Binlog日志:需要手动开启,对MySQL操作进行记录(建库、建表、数据等);slave节点开启线程监听Binlog日志的变化,写入到Relaylog中,再通过另一线程恢复日志数据
1.2 MySQL主从配置
MySQL主从配置均针对my.cnf文件
1.2.1 主库配置
[mysqld] //对MySQL配置
server-id=47 //局域网内id唯一
# 开启binglog
log_bin=master-bin
log_bin-index=master-bin.index
skip-name-resolve
# 设置连接端口
port=3306
主库执行如下sql:
# 查看主库状态
show master status;
1.2.2 从库配置
[mysqld]
server-id=48
# mysql中继日志
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
# 设置只读
read-only=1
# 打开从binlog日志
log-bin=mysql-bin
# 使得更新的数据写进binlog日志中
log-slave-updates=1
# 端口号
port=3306
从库执行如下sql:
# 设置同步主节点
CHANGE MASTER TO
MASTER_HOST ='192.168.31.31',
MASTER_PORT =3306,
MASTER_USER ='root',
MASTER_PASSWORD ='12345',
MASTER_LOG_FILE ='master-bin.000007',
MASTER_LOG_POS =157, #填入的是主库 show master status 列出的数据
GET_MASTER_PUBLIC_KEY =1;
# 开启slave
start slave;
# 查看主从同步状态
show slave status;
1.2.3 验证
主库新建database:create database syncliuxin;
可以看到,两个库的操作进行了同步
1.2.4 指定库/表
show master status;
主库配置:/etc/my.cnf
# 记录哪些DB(没有则全部) Binlog_Do_DB
binlog-do-db=syncdemo
binlog-do-db=lx
# 忽略哪些DB Binlog_Ignore_DB
binlog-ignore-db=mysql
# Executed_Gtid_Set Gtid同步复制
重启服务后:
从库配置:/etc/my.cnf
# 从库与主库库名相同时
replicate-do-db=syncdemo
replicate-do-db=lx
# 从库与主库库名不同
replicate-rewrite-db=syncdemo->syncdemo1
# 如果不是要全部同步,则需要指定表
replicate-wild-do-table=syncdemo1.t_dict
进行如上配置后的结果:只同步了lx库,而没有同步lzh库
1.2.5 互主集群
当主节点挂了后,从节点也失效,为了保证主从的高可用性,可以考虑搭建互主集群;理解上述搭建过程,很容易搭建出互主集群,/etc/my.cnf文件也需配置
192.168.31.31 my.cnf:
[mysqld]
server-id=47
log_bin=master-bin
log_bin-index=master-bin.index
skip-name-resolve
# 设置连接端口
port=3306
# mysql中继日志
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
# 使得更新的数据写进binlog日志中
log-slave-updates=1
192.168.31.32 my.cnf:
[mysqld]
server-id=48
# mysql中继日志
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
# read-only=1
log-bin=mysql-bin
# 使得更新的数据写进binlog日志中
log-slave-updates=1
port=3306
log_bin-index=master-bin.index
2 MySQL半同步复制
异步复制并不能保证数据的可靠性,MySQL引入了半同步复制基础,master等待接收slave写库成功之后返回ack,再返回给客户端(master超过10s没收到ack就降级为异步复制)
性能下降,数据安全性提高
2.1 半同步复制搭建
主库:
install plugin rpl_semi_sync_master soname 'semisync_master.so';
set global rpl_semi_sync_master_enabled = ON;
show global variables like 'rpl_semi%';
从库:
install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
set global rpl_semi_sync_slave_enabled = ON;
show global variables like 'rpl_semi%';
重启slave服务:
stop slave;
start slave;
3 其它高可用方案
3.1 MMM
3.2 MHA
3.3 MGR
4 分库分表
分库分表包含分库和分表,统一称为数据分片;分库分表是对SQL的转发,主从架构决定数据存储
4.1 垂直分片
原本属于一个库里的表,拆到两个库中;并不能解决单一表过大问题
4.2 水平分片
原本属于一个库中的表,拆成多张表
常见分配策略:
策略 | 优点 | 缺点 |
取余/取模 | 数据分布均匀 | 扩容麻烦 |
时间范围 | 扩容简单 | 数据分布不均匀 |
思考:如何设计一个不需要迁移的取模分片方案?
先按时间范围分片到库,再按取余/取模分片到表
4.3 分库分表需要解决的问题
事务一致性
关联查询、分页查询、Group函数
主键避重
4.4 什么时候分库分表
单表记录>=500w或容量达到2GB
分库分表需要考虑对数据的再平衡,所以项目设计之初就尽可能设计好
分库分表之后进行(排序、分页、聚合等)操作会很麻烦,且消耗内存,需要考虑降级方案,如备份一份到ES
4.5 常见分库分表组件
ShardingSphere
Mycat