MySQL主从复制和读写分离

MySQL主从复制和读写分离

主从复制原理：

主数据库的数据 新增 修改 表 表里的数据 都会同步到从数据库

MySQL主从辅助的模式

1. 异步复制 MySQL默认复制方式就是异步复制 只要执行完之后 客户端提交事务 主MySQL会立即把结果返回给从服务器 主MySQL并不关心从MySQL是否已经接受处理 主MySQL一旦崩溃 主MySQL事务可能没有传到从MySQL 这时候强行把从提升为主 可能导致新的主MySQL数据不完整
2. 全同步复制 主库执行完成一个事务 所有的从库都执行该事务才会返回客户端  因为需要等待所有从库全部执行完成 性能下降（对数据一致性和要求很高的场景）
3. 半同步复制 介于异步和全同步之间 主库执行完一个事务后 至少等待一个从库接受并处理完成之后才会返回客户端 在一定程度上提高了数据的安全性 也会有一定的延迟（一般是一个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怎么解决

1. 网络问题
2. My.cnf 配置文件写错了
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;要么文件名错了 要么位置偏移量不对
4. 防火墙和安全机制问题

主服务器创建kgc 查看从服务器是否同步 主从复制完成 主从复制是单项的 只能从主复制到从

主从复制延迟问题：

1. 网络延迟
2. 硬件设备（CPU主频 内存 硬盘）
3. 同步复制而不是异步复制

解决方案：

1. 硬件方面 主库不需要动 从库硬件配置要好 提升随机写的性能 升级CPU的核数 扩展内存 尽量使用物理机不要用云服务器
2. 网络方面 主从服务器都配置在一个局域网内 尽量避免跨网段跨机房
3. 架构方面 做读写分离 把写入控制在主库 从库负责读 降低从库压力
4. 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负责将更新写入到从库中 实现主从一致

1. 只能在主库上发送变化 然后同步到从库
2. 复制过程是串行话过程 在从库上复制是串行的 主库的并行更新不能在从库上并行操作
3. 主从复制的设计目的就是为了在主库写 在从库查 读写分离 实现高可用

读写分离：

要实现读写分离 必须要实现主从复制

所有的写入操作都在主MySQL 从库只负责读 如果有更新是从主库复制到从库

1. 数据库在写入数据时比较耗时
2. 数据库读的时候很快 读写分离可以提高效率

数据库的写入和读取是分开的 哪怕是写入的数据量比较大 但是不影响查询的效率

什么场景下需要读写分离？

数据库不是一定需要读写分离的 只有在某些程序在使用数据库过程中 更新少 但是查询多 可以考虑读写分离 生产库一般都会做读写分离

数据库的读写不会在同一个库中完成 既不安全也不能满足高可用 也不能实现高并发

读写分离的原理：

1. 根据脚本实现 在代码中实现路由分类 select和insert进行路由分类 性能好 在代码中可以实现不需要额外的硬件设备
2. 基于中间代理层实现 mysql-proxy 自带的lua脚本 Atlas 360内部自己使用的代理工具 支持事务 支持存储过程
3. Amoeba 由Java开发的开源软件 不支持事务和存储过程

面试题：

1. 主从复制的原理
2. 读写分离的实现方式
3. 如何查看主从复制的状态是否成功

Show slave status\G 纵向查看 IO SQL 两个都是yes

1. IO排查思路是什么

85%是配置文件

1. show slave status\G 能看到的信息有哪些

IO和SQL的线程状态信息  master服务器的IP地址和端口事务开始的位置  最近一次错误信息和错误位置 最近一次IO和SQL的报错信息

6、主从复制的延迟如何解决