MySql数据库企业级应用实战（mysql主从复制）

一、MySql主从复制介绍

mysql的主从复制并不是数据库磁盘上的文件直接拷贝,而是通过逻辑的binlog日志复制到要同步的服务器本地，然后由本地的线程读取日志里面的SQL语句，重新应用到MySql数据库中。

【mysql数据库支持单向、多向、链式级联、环状等不同业务场景的复制。】

复制过程，一台服务器充当主服务器（master）,接受来自用户的内容更新，而一个或多个其它服务器充当从服务器（Slave）,接受来自主服务器binlog文件日志内容，解析出SQL，重新更新到从服务器，使得主从服务器数据达到一致。

如果设置了链式级联复制，那么从服务器（Slave）本身除了充当从服务器外，也会同时充当其下面从服务器的主服务器。

链式级联复制模式类似：A--->B--->C的复制形式。

二、MySql主从复制原理介绍

mysql 的主从复制是一个异步的复制过程，数据将从一个mysql数据库（我们称为Master）复制到另一个mysql数据库（我们称为Slave）,在Master与Slave之间实现整个主从复制过程是由三个线程参与完成的，其中有两个线程（SQL线程和I/O线程）在Slave端，另一个线程（I/O线程）在Master端。

实现mysql的主从复制，首先必须打开master端的binlog记录功能，否则无法实现。

整个过程实际上就是Slave从Master端获取binlog日志,然后在往Slave上以相同的顺序执行获取的binlog日志中所记录的各种sql操作。

打开mysql的binlog记录功能，可通过在mysql的配置文件my.cnf中的mysqld模块（[mysqld]标示后的参数部分）增加“log-bin”参数选项来实现，具体信息如下：

[mysqld]

log-bin = /data/3306/mysql-bin

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

mysql主从复制原理的重点进行小结：

1、主从复制是异步逻辑的SQL语句级的复制

2、复制时，主库有一个I/O线程，从库有两个线程，即I/O和SQL线程。

3、实现主从复制的必要条件是主库要开启记录binlog功能。

4、作为复制的所有MYSQL节点的server-id都不能相同。

5、binlog文件只记录对数据库有更改的sql语句（来自主数据库内容的变更），不记录任何查询（如select、show）语句。

三、mysql主从复制集群架构的数据备份策略

有了主从复制了，还需要做定时全量增加备份么？答案是肯定的！

因为，如果主库有语句级错误操作（如：drop databases zhaosjdb;）从库也会执行drop databases zhaosjdb;

这样mysql主从库就都删除了该数据。

高并发业务场景备份时，可以选择在一台从库上备份（Slave*）,把从库作为数据库备份服务时需要从库开启binlog功能。

步骤如下：

1、选择一台不对外提供服务的从库，这样可以确保和主库更新最接近，专门用于做数据备份。

2、开启一台从库的binlog功能。

备份时可以选择只停止SQL线程，停止应用SQL语句到数据库，I/O线程保留工作状态。执行命令为 stop slave sql_thread;

备份方式可以采取mysqldump逻辑备份或直接物理备份，列如：使用cp、tar（针对/data/目录）。

四、mysql主从复制延迟问题的原因及解决方案

问题一：主库的从库太多，导致复制延迟。

从库的数量以3~5个为宜，要复制的从节点数量过多，会导致复制延迟。

问题二：从库的硬件比主库差，导致复制延迟。

查看Master和Slave的系统配置，可能会因为机器配置不当，包括磁盘I/O、CPU内存等各方面因素造成复制的延迟。这一般发生在高并发大数据量写入场景中。

问题三：慢SQL语句过多。

假如一条sql语句执行时间是20秒，那么从执行完毕到从库上能查到数据至少需要20秒，这样就延迟了20秒。

一般要把sql语句的优化工作作为常规工作，不断的进行监控和优化，如果单个sql的写入时间长，可以修改后分多次写入。通过查看慢查询日志或show full processlist 命令，找出执行时间长的查询语句或大的事物。

问题四：主从复制的设计问题。

主从复制单线程，如果主库的写并发太大，来不及传送到从库，就会导致延迟。

更高版本的mysql可以支持多线程复制，门户网站则会自己开发多线程功能。

问题五：主从库之间的网络延迟。

主从库的网卡、网线、连接的交换机等网络设备都可能成为复制的瓶颈，导致复制延迟，另外，跨公网主从复制很容易导致主从复制延迟。

问题六：主库读写压力大，导致复制延迟。

主库硬件要搞好一点，架构的前端要加buffer及缓存层。

五、通过read-only参数让从库只读访问

read-only参数选项可以让服务器只允许来自从服务器的线程或具有SUPER权限的数据库用户进行更新，确保从服务器不接受来自用户端的非法用户更新。

1>、具有SUPER权限的用户可以更新，不受read-only参数影响，如：管理员root。

2>、来自从服务器线程可以更新，不受read-only参数影响，如：rep用户。

再生产环境中，可以在从库Slave中使用read-only参数，确保从库数据不被非法更新。

read-only参数配置方法：

方法一：直接带--read-only参数启动或重启数据库

使用：kill all mysqld

或

mysqladmin -uroot -poldboy123 -S /data/3306/mysql.sock shutdown

mysqld_safe --defaults-file=/data/3306/my.cnf --read-only &

方法二：在my.cnf里[mysqld]模块下加入read-only参数重启数据库

配置：

[mysqld]

read-only

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

六、Mysql主从复制读写分离集群

主库和从库使用不同的用户，授予不同的权限

例如：主库对于web_w用户的授权

用户：web_w 密码：oldboy123  端口：3306  主库VIP：10.0.0.7

权限：select、insert、update、delete

命名：grant select、insert、update、delete on 'web' .* to 'web_w'@'10.0.0.%' identified by 'oldboy123'

从库上对web_r用户授权

用户：web_r 密码：oldboy123  端口：3306  主库VIP：10.0.0.8

权限：select

命名：grant select on 'web' .* to 'web_r'@'10.0.0.%' identified by 'oldboy123'

主库和从库使用相同的用户，但授予不同的权限

在主库上创建用户和权限后，从库上revoke收回对应更新权限（insert、update、delete）

命令为：

revoke insert、update、delete on web.* from 'web'@'10.0.0.%';

二是忽略授权库mysql同步，主库的配置参数如下：

binlog-lgnore = mysql

replicate-ignore-db = mysql