MySQL主从同步和读写分离配置及理解

前言

刚刚写这货写一半的时候觉得有必要再写一篇MySQL主从同步配置来辅助, 所有才有了现在这货.😂
关于读写分离实现的部分在这里: 传送门

MySQL主从同步主要目的是为了负载均衡, 一般主要两种使用场景, 读写分离或双主.

读写分离顾名思义, 就是把CRUD中占用资源最多的R独立到从库上, 避免因为查询引起的性能耗尽导致宕机, 从库可以有多个, 所以可以把查询分散到多个从库进一步提升高可用.
双主就是两台服务器相互同步, 不分读写, 简单粗暴实现一定程度上的高可用, 不过切记主键使用基于时间戳的UUID, 否则你可能会被各种主键冲突烦到想死.
Ps: 基于时间戳我着重标记了一下, 是因为我知道很多人以为JavaSDK里提供的UUID获取API就是基于时间戳的, 那你可就想多了, 并不是.
默认只支持V3和V4(基于伪随机数)两种版本的实现, 而V1才是包含时间戳的.
支持UUID V1的第三方库

开始正题

第一步 准备工作

官方下载传送门

必须选择5.1.4之后的版本, 最好使用5.7之后的版本, 5.7之后支持了json字段类型、虚拟字段和虚拟索引, 非常好用.

本地学习测试的话, 可在本地安装多个实例来测试, 具体方法自行百度, 基本上就时改个端口号而已.

第二步 关键配置及理解

关键性记忆, Mysql提供的同步模式分为3种.

1. SBR:

   • 当使用二进制日志时, 主服务器会把SQL语句写入到日志中, 然后从服务器会执行该日志.
     • 长处:
       • 日志文件更小
       • 记录了所有的语句，可以用来日后审计
     • 弊端:
       • 使用如下函数的语句不能被正确地复制:
         • load_file(); uuid(), uuid_short(); user(); found_rows(); sysdate();
         • get_lock(); is_free_lock(); is_used_lock(); master_pos_wait(); rand();
         • release_lock(); sleep(); version();
     • 警告:
       • mysql5.1.4之前的版本仅支持该模式
       • 在日志中出现如下警告信息的不能正确地复制 [Warning] Statement is not safe to log in statement format.
       • Insert … select语句会执行大量的行级锁表
       • Update语句会执行大量的行级锁表来扫描整个表

2. RBR:

   • 主服务器把表的行变化作为事件写入到二进制日志中, 主服务器把代表了行变化的事件复制到从服务中.
     • 长处:
       • 所有的数据变化都是被复制，这是最安全的复制方式
       • 更少的行级锁表
     • 弊端:
       • 日志会很大
       • 不能通过查看日志来审计执行过的sql语句，不过可以通过使用mysqlbinlog
       • –base64-output=decode-rows –verbose来查看数据的变动

3. MBR:

   • 既使用SBR也使用RBR，默认使用SBR

从上述说明种不难抉择, 理想选择应该是MBR模式暨混合模式.

主库my.cnf(Win为my.ini)需要添加的关键配置

#开启binlog, 因为我们使用混合模式所以binlog必须开启.
log-bin=mysql-bin

#实例唯一ID, 每个参与主从的实例ID都必须唯一, 用于相互识别.
server-id=1
#事务提交后持久化频率设置. 
# 0: 固定每秒一次, 宕机丢失一秒内的所有事务数据.
# 1: 事务提交后立即写入磁盘缓冲区并立即刷新, 为默认值. 但刷新磁盘为阻塞函数, 所以最安全, 但会限制写入并发性能.
# 2: 事务提交后数据写入磁盘缓冲区, 刷新依旧每秒一次, 该方式可保证mysql宕机但操作系统正常时数据的完整性. 
# 重要数据最好选择1, 为了应对系统宕机或停电等极端情况.
innodb_flush_log_at_trx_commit=1
#这个参数和上一个成对使用, binlog中写入多少个事务缓冲后立即触发刷新.
#这个参数根据可根据自身并发写入量、服务器负载量、机房环境及数据重要性等因素综合来定
sync_binlog= 1

#同步数据库，如果多库，就以此格式另写几行即可
binlog-do-db=order
binlog-do-db=order2
#忽略mysql的系统库
binlog-ignore-db = cluster
binlog-ignore-db = mysql
binlog-ignore-db = performance_schema
binlog-ignore-db = information_schema

#混合模式同步
binlog_format=MIXED
#binlog过期清理时间
expire_logs_days=7
#binlog每个日志文件大小
max_binlog_size=20M

从库my.cnf(Win为my.ini)需要添加的关键配置

#开启binlog, 因为我们使用混合模式所以binlog必须开启.
log-bin=mysql-bin

#实例唯一ID, 每个参与主从的实例ID都必须唯一, 用于相互识别.
server-id=2

#因为我们使用的混合模式同步, 那么即是从库进用于读, bin log也会在同步时被用到.
#所以配置考量同主库.
sync_binlog=1

#这里可选择开启从库的bin log记录, 可方便日后扩展又从变主.
#binlog-do-db=order
#binlog-do-db=order2
#无需同步的数据库，以下几行基本一样，无需改动
binlog-ignore-db = cluster
binlog-ignore-db = mysql
binlog-ignore-db = performance_schema
binlog-ignore-db = information_schema

#指定需要进行同步的数据库
replicate-do-db=order
replicate-do-db=order2
#忽略mysql的系统库
replicate-ignore-db = cluster
replicate-ignore-db = mysql
replicate-ignore-db = performance_schema
replicate-ignore-db = information_schema

#从库开启 crash-safe 的关键配置 crash-safe可解决系统宕机或断电等极端情况从库出现数据异常的问题.
#开启后会自动创建mysql.slave_relay_log_info表, 注意! 5.6.5之前版本该表默认为MyISAM引擎, 需手动改为Innodb
relay_log_info_repository = TABLE
relay_log_recovery =  1

#跳过所有错误继续, 从数据健壮性考虑, 这里不建议跳过任何错误, 任何错误都应该即刻停止.
#做读写分离时, 应心跳检测主从复置状态(心跳调用show slave status), 一旦同步进程挂掉即停止从该服务器读取数据.
slave-skip-errors=OFF
#设置超时时间, 不易过长, 因为超时后重连成功会追赶同步.
slave-net-timeout=60

#混合模式同步
binlog_format=MIXED
#binlog过期清理时间
expire_logs_days=7
#binlog每个日志文件大小
max_binlog_size=20M

每个一个关键配置我都进行了比较详细描述了有什么用|什么时候用|怎么用.
建议深刻理解以上配置, 并本地进行各项选项的实验, 有坑提前踩, 避免上线后遇到问题抓耳挠腮.

看明白之后将以上配置区分主从分别加入你数据库里的配置文件中重启.

Ps: 当我们因为数据一致性被迫使用最严格的模式而导致性能瓶颈时, 还可以使用集群来做主从, 进一步分担IO压力, 集群的主从配置大同小异.

第三步 开启同步线程

首先先从主库手动同步数据库到从库中, 结构和数据皆需要同步, 空库可跳过, 备份主库时记得先锁住所有表, 防止备份时数据库发生变化.

在主库执行

/* 给主库增加从库用于同步使用的用户 */
grant replication slave on *.* to rep@"192.168.%.%" identified by "pwd";

/* 查看数据bin log, 将File和Position两个字段内容复制下来. */
show master status

在从库执行

/* 配置同步进程需要的数据 */
CHANGE MASTER TO
MASTER_HOST='主库IP',
master_port=主库端口,
MASTER_USER='刚刚在主库创建的同步用户名',
MASTER_PASSWORD='密码',
MASTER_LOG_FILE='刚刚复制File字段内容',
master_log_pos=刚刚复制的Position字段内容;

/* 开启同步 */
start slave;

/* 如果需要修改上面进程同步需要的配置, 需要先停止同步 */
stop slave;

到这里, 主从同步配置全部完成. 可自行在主库上增删改数据进行测试.

总结

科技日新月异, 中国互联网用户总量连年创下新高, 对于我们IT从业人员对于高可用的要求也是越来越高. 可惜对于优化, 真的没有万金油. 凡事都得具体业务具体分析, 没有一劳永逸的配置或方式. 只能一轮又一轮一波又一波的不断折腾着我们.
但是, 乐趣不也就在这里吗?! 与天斗! 其乐无穷! 与地斗! 其乐无穷! 与人斗! 其乐无穷!👻

好了, 这篇写完了, 我可以滚去写这货了😂！Thanks for your reading。