MySQL Replication常用SQL、应用、文件、流程、模式_MySQL

bitsCN.com

MySQL Replication常用SQL、应用、文件、流程、模式

 

无聊时写的，算科普吧，毕竟内置的Replication是MySQL的骄傲

 

㈠ SQL语句篇

 

   管理主库部分

 

    show master logs

    列出主库二进制日志

    

    show master status

    列出当前主库二进制日志状态

    

    show slave hosts

    列出连接到主库的备库信息

    

    show binlog events in 'log_name'

    列出二进制日志中的事件

    

    reset master

    置空二进制日志索引文件，并创建一个新的二进制日志

    

    purge master logs to 'log_name'

    purge master logs before 'date'

    删除主库的二进制日志

    建议删除流程：

    ① 目标日志确认如下：

       在主库：show master logs 

       在备库：show slave status[每个备库都执行，抓取延迟最大的备库]

    ② 备份

    ③ purge 

 

 

   管理备库部分

 

 

    change master to master_**

    告诉备库如何连接到主库并重放其二进制日志

    参数较多较杂，请自行参阅手册

    

    reset slave

    删除master.info、relay-log.info以及所有中继日志，并新建一个中继日志

    

    show slave status

    查看当前备库状态

    输出较多，下面捡几个重要的谈谈

    Slave_IO_State、Slave_IO_Running、Slave_SQL_Running：表示IO线程和SQL线程健康状况

    Master_Log_File：IO线程当前正在读取的主库的二进制日志的名称

    Read_Master_Log_Pos：当前主库的二进制日志中，IO线程已经读取的位置

    Relay_Log_File：SQL线程当前正在读取和执行的中继日志的名称

    Relay_Log_Pos：当前中继日志中，SQL线程已经读取和执行的位置

    Exec_Master_Log_Pos：同步到备库的二进制日志的位置

    

    能借助该输出来计算复制延迟：

    

    Read_Master_Log_Pos-Exec_Master_Log_Pos：表示SQL线程延迟，进而表示了主备是否同步

    顺道提一点，二进制日志坐标：Position，减去Read_Master_Log_Pos：表示IO线程延迟

    

    start slave

    启动备库SQL线程/IO线程

    

    stop slave 

    停止备库SQL线程/IO线程 

 

 

㈡ 应用篇

 

 

   数据分布

     →给地理上互相隔离的IDC分发数据

   热备份

     →复制是备份的技术补充，但不能代替备份

   读扩展

     →负载均衡

   报表分析

     →在不影响主库业务的情况下，月底的审计和报表分析可放到备库上做

   升级测试

     →用最新版本的mysql做备库

   故障转移

     →提升备库为主库，最小化宕机时间

     

 

 

㈢ 文件篇

 

 

   master.info

   记录备库连接主库所需要的信息，如：主机、用户名、密码、当前二进制日志坐标等

   同时，他也能告诉主库："我需要某个日志的某个位置之后的内容，请发给我"

   

   relay-log.info

   记录当前备库正在复制的二进制日志和中继日志的坐标

   

   binlog index 

   记录主库磁盘上二进制日志文件

   

   relay log 

   存储IO线程从主库复制的二进制日志事件

   

   relay log index 

   作用同binlog index 

 

㈣ 流程篇

 

 

   ⑴ 主库记录二进制日志：按事务提交的顺序记录事件

   ⑵ 备库将主库的二进制日志复制到本地中继日志

   

      启动IO线程

      发起TCP/IP连接

      在主库启动binlog dump线程

      读取主库二进制日志

      IO线程记录到relay log和master.info

     

   ⑶ 备库读取并重放二进制日志事件

 

㈤ 模式篇

 

 

   复制模式可分：STATEMENT和ROW，通过binlog_format控制

   没有哪种模式能胜任任何场景，谓之：存在即是合理

   MySQL能在这两种模式动态却换(binlog_format='MIXED')

   缺省以STATEMENT运行，当无法正确复制时则以ROW运行

   

   下面列出他们各自的优缺点

   

   ROW模式

   

   优点

   

   几乎没有基于行的复制无法处理的场景

   更少的锁竞争，因为对强串行化的需求降低

   更低的CPU花费，因为没有必要在构造SQL上下文信息

   更好的保证了复制到备库的数据的品质

   更快的定位和解决数据不一致，如当找不到修改的行时，ROW模式会使整个复制过程停止而STATEMENT不会

   

   缺点

   

   无法确定执行了什么SQL

   黑盒子，很难定位出故障的地方

   占用更多的磁盘空间

   更多的网络带宽开销

   

   

   STATEMENT模式

   

   优点

   

   基于行的复制整个过程基本上就是执行SQL

   这很容易定位问题

   

   缺点

   

   无法正确复制，特别是当涉及到存储过程、触发器、函数等

   这会失去复制的意义

 

bitsCN.com