MySQL主从复制和读写分离
主从复制原理:
主数据库的数据 新增 修改 表 表里的数据 都会同步到从数据库
MySQL主从辅助的模式
- 异步复制 MySQL默认复制方式就是异步复制 只要执行完之后 客户端提交事务 主MySQL会立即把结果返回给从服务器 主MySQL并不关心从MySQL是否已经接受处理 主MySQL一旦崩溃 主MySQL事务可能没有传到从MySQL 这时候强行把从提升为主 可能导致新的主MySQL数据不完整
- 全同步复制 主库执行完成一个事务 所有的从库都执行该事务才会返回客户端 因为需要等待所有从库全部执行完成 性能下降(对数据一致性和要求很高的场景)
- 半同步复制 介于异步和全同步之间 主库执行完一个事务后 至少等待一个从库接受并处理完成之后才会返回客户端 在一定程度上提高了数据的安全性 也会有一定的延迟(一般是一个tcp/ip的往返时间 从发送到接受的时间 毫秒 RTT)半同步模式最好在低延迟的网络使用
架构:主从复制和读写分离
三台MySQL MySQL1 主(192.168.1.10) MySQL2 从 (192.168.1.11) MySQL3 从 (192.168.1.12)
Test1 读写分离 (192.168.1.20) Test2 客户端
主从服务器之间的时间也要同步 安装时间工具 ntp
Stratum 8 数字越小 时间精确度越高 设置fudge 8 时间层级是8 最高是15 从本地获取时间源 不走网络
主的完成
从:
两台服务器时间源指向主服务器 添加定时任务:
Crontab -e -u root
Date 查看当前时间
主:
Log:允许从服务器复制数据时 可以从主的二进制日志写到自己的二进制日志当中
重启MySQL
进入主数据库 给从服务器新建用户 给从库赋权
Flush privileges 刷新权限
Show master status 查看位置点
从库:/etc/my.cnf
Server – id =2 不能重复
默认是0 1表示开启中继日志的恢复 从服务器出现异常或者崩溃时 从服务器会从主服务器的二进制日志正确读取和应用中继日志
重启MySQL
从2:
重启mysql
2 3分别进入数据库 和主同步
CHANGE master to master_host='192.168.233.21',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=604;
Start slave 两台都要
Show slave status\G;
第一个是负责和主库的io通信 第二个是负责自己的slave MySQL进程
Slave io running no怎么解决
- 网络问题
- My.cnf 配置文件写错了
- CHANGE master to master_host='192.168.233.21',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=604;要么文件名错了 要么位置偏移量不对
- 防火墙和安全机制问题
主服务器创建kgc 查看从服务器是否同步 主从复制完成 主从复制是单项的 只能从主复制到从
主从复制延迟问题:
- 网络延迟
- 硬件设备(CPU主频 内存 硬盘)
- 同步复制而不是异步复制
解决方案:
- 硬件方面 主库不需要动 从库硬件配置要好 提升随机写的性能 升级CPU的核数 扩展内存 尽量使用物理机不要用云服务器
- 网络方面 主从服务器都配置在一个局域网内 尽量避免跨网段跨机房
- 架构方面 做读写分离 把写入控制在主库 从库负责读 降低从库压力
- MySQL配置方面 从配置文件角度实现性能最大化
安全性:
Innodb_flush_log_at_trx_commit=1
每次事务提交时都会刷新事务日志 确保持久性 最高级别的数据安全性 但是会影响性能 默认·是1 关闭改0 改0之后事务提交时不会立刻刷新 而是每秒刷新一次 可以提高性能 但是发送故障会导致数据丢失 设置为2时 事务提交时 事务日志不会写入硬盘 而是保存在系统缓存 不会进行刷新 有一定的安全性和性能 对内存的要求高
Sync_binlog=1
1也是默认值 每次提交事务之后直接把日志刷新到磁盘 以确保日志的持久性 占用很高性能 改成0后会把二进制日志写入到缓存 也不会刷新日志 故障发送也会丢失数据 可以自定义事务刷新次数 改成3 每三个事务执行一次刷新到磁盘 提高性能 但是一旦崩溃 数据会大量丢失
追求性能:
Innodb_flush_log_at_trx_commit=2
Sync_binlog=0
Logs-slave-updates=0 :从库的更新不会写入二进制文件
Innodb_buffer_pool_size 300M innodb存储引擎的缓冲池大小 设置数值越高 可以提高innodb的性能 更多的数据和索引都可以缓存在内存中 减少磁盘的访问次数 对系统内存要求比较高
主从复制的工作过程:
1、主节点的数据记录发送变化都会记录在二进制日志
2、slave节点会在一定时间内对主库的二进制文件进行探测 是否发送变化
3、如果有变化 从库会开启一个I/O的线程 请求主库的二进制事件
4、主库会给每一个I/O的线程启动一个dump 用于发送二进制事件给从库 从库通过I/O线程获取更新 slave_sql负责将更新写入到从库中 实现主从一致
- 只能在主库上发送变化 然后同步到从库
- 复制过程是串行话过程 在从库上复制是串行的 主库的并行更新不能在从库上并行操作
- 主从复制的设计目的就是为了在主库写 在从库查 读写分离 实现高可用
读写分离:
要实现读写分离 必须要实现主从复制
所有的写入操作都在主MySQL 从库只负责读 如果有更新是从主库复制到从库
- 数据库在写入数据时比较耗时
- 数据库读的时候很快 读写分离可以提高效率
数据库的写入和读取是分开的 哪怕是写入的数据量比较大 但是不影响查询的效率
什么场景下需要读写分离?
数据库不是一定需要读写分离的 只有在某些程序在使用数据库过程中 更新少 但是查询多 可以考虑读写分离 生产库一般都会做读写分离
数据库的读写不会在同一个库中完成 既不安全也不能满足高可用 也不能实现高并发
读写分离的原理:
- 根据脚本实现 在代码中实现路由分类 select和insert进行路由分类 性能好 在代码中可以实现不需要额外的硬件设备
- 基于中间代理层实现 mysql-proxy 自带的lua脚本 Atlas 360内部自己使用的代理工具 支持事务 支持存储过程
- Amoeba 由Java开发的开源软件 不支持事务和存储过程
面试题:
- 主从复制的原理
- 读写分离的实现方式
- 如何查看主从复制的状态是否成功
Show slave status\G 纵向查看 IO SQL 两个都是yes
- IO排查思路是什么
85%是配置文件
- show slave status\G 能看到的信息有哪些
IO和SQL的线程状态信息 master服务器的IP地址和端口事务开始的位置 最近一次错误信息和错误位置 最近一次IO和SQL的报错信息
6、主从复制的延迟如何解决