study-25: Mysql 主从复制（非常重要）

1，Mysql主从复制介绍（基于异步的复制方式）

支持单向、双向、链式级联、实时、异步复制。在复制过程中，一台服务器充当主服务器（Master），而另一台或多台其他的服务器充当从服务器（Slave）

单向：M  ===>   S

双向：M <====> M

环状：A ---> B ----> C ----->  A

链式级联复制：A ----> B ------> C -----> D 的复制形式

2，Mysql主从复制的应用场景

Mysql主从复制有利于数据库架构的健壮性、提升访问速度和易于维护管理

1）主从服务器互为备份

2）主从服务器读写分离分担网站压力

如何解决主down掉后，从不一致的方法

Mysql主从架构本身不保证主从完全一致，当发生不一致的情况时，可以通过下面方法解决:

a，由于主库只要有数据写入，就肯定会有bin-log存在，这时 从库所在机器可以远程拉主库上的bin-log进行数据补全

b，2个主库双写

c，主库程序写一分钟内的更新到另外的文件中，从库从这个原件进行数据补全

d，Google有插件可以实现半同步：要求主库写成功、并且从库中某个半同步库（这个库什么也不干，只用做半同步从库）也写成功，主库才会commit，并向前端返回写入成功（据说在 MySQL 5.5或者更高的版本中已经集成了这个功能）

互联网公司90%的数据库操作是读的请求，所以可以通过横向扩展增加从库来分担压力

中大型公司：通过程序（php、java），遇到更改往主库写，遇到查询读从库

测试环境：代理软件（mysq-proxy、amoeba），还不稳定

门户网站：分布式drproxy（读写分离、hash负载均衡、健康检查）

3）根据服务器拆分业务，独立并分担压力

可以把几个不同的从服务器，根据公司业务进行拆分。例如：有为外部用户提供查询服务的从服务器；有为DBA用来备份的从服务器；有为公司内部人员访问的后台、脚本、日志分析及开发人员服务的从服务器；这样的拆分除了减轻主服务器的压力外，使得对外用户浏览，对内处理公司内部用户业务，及DBA备份业务互不影响。具体可以用下面的简单架构来说明：（架构越复杂、维护成本越高； 架构不能脱离实际的业务需求，适合最重要）

4）如何实现Mysql主从读写分离

a，通过程序实现读写分离（性能、效率最佳，推荐）

php和java程序都可以通过设置多个连接文件轻松实现对数据库的读写分离，即当select时，就去连接读库的连接文件，当update、insert、delete时就连接写库的连接文件；

b，通过软件实现

Mysql-proxy、Amoeba等代理软件也可以实现读写分离功能，但最常用好用的还是程序实现读写分离

c，大型公司自己开发数据库中间价

5）主从复制原理

a，从库开启2个线程（IO、SQL线程）；主库开启 IO线程

b，主库开启bin-log日志功能的开关；主库建立从库用于同步的数据库帐号及权限（grant replication slave  on  *.* ）；

c，主库数据导入一份到从库，保证同步前的数据是一致的，从库执行连接主库的参数（其中：LOG_FILE和LOG_POS 为主库和从库数据不一致的开始bin-log文件和POS点信息）：

CHANGE MASTER TO 

MASTER_HOST= '' ,

MASTER_USER='' ,

MASTER_PASSWORD='' ,

MASTER_PORT='' ,

MASTER_LOG_FILE='' ,

MASTER_LOG_POS='' ;

d，从库打开开关，开启同步（start   slave ），实际上是开启从库的IO和SQL线程进行工作：

从库开启IO线程去请求主库，主库验证从库配置是否正确；

从库告诉主库从哪个文件（LOG_FILE）的哪个点（LOG_POS）发送bin-log； ===》循环执行

从库IO线程收到bin-log后，将log存到本地的relay-log（中继日志）和更新master-info（存储最后更新的bin-log文件名和POS信息）； ===》循环执行

于此同时，从库的SQL线程会监控发现relay-log有更新，并通过转换写入到从库中去； ===》循环执行

mysqldump导出数据给slave时，如果加上  --master-data=1 参数后，从库在执行 CHANGE MASTER TO 。。。。。时可以不指定  LOG_FILE 和 LOG_POS这2个参数；

（mysqldump导出数据时，当这个参数的值为1的时候，mysqldump出来的文件就会包括CHANGE MASTER TO这个语句，CHANGE MASTER TO后面紧接着就是file和position的记录，在slave上导入数据时就会执行这个语句，salve就会根据指定这个文件位置从master端复制binlog。默认情况下这个值是1

当这个值是2的时候，chang master to也是会写到dump文件里面去的，但是这个语句是被注释的状态。）

如果slave要开级联slave（其他slave从当前slave请求bin-log进行数据异步复制），则当前slave需要打开2个参数： log-bin 和 log_slave_updates

3，MySQL主从同步实战

按之前的步骤开启主库的log-bin ，server-id 等参数（单台机多实例，可以去IP最后8位加数字来区别）

主库导出一份完整数据：

flush table with read lock;  #当前CRT会话窗口不执行其他

show master status; # 显示只读开始的当前备份的log-bin和pos

show master logs; #显示所有log-bin和它对应的pos

并复制一个CRT窗口去做mysqldump的操作：mysqldump -uroot -p123456 -A -B --events | gzip > all.sql.gz

主库解锁：unlock  tables;

从库导入备份数据：mysql -uroot -p123456 < all.sql

从库执行： change master to 

master_host='192.168.1.200',

master_user='rep',

master_password='123456',

master_port=3306,  # 不要加引号

master_log_file='mysql-bin.000001',

master_log_pos=832;  #  不要加引号

start slave;

show  slave status\G;   #  查看  IO  和  SQL 线程是否 YES

测试确认是否同步，，，完成搭建主从

4，主从复制读写分离授权多种方案（因为主库有增、删、改的权限，默认同步给从库时，从库也会有此三个权限，如果保证从库只有查询的权限呢？）

1）方案1

主库：帐号user1  （select , insert , delete , update）

从库：主库的user1用户同步到从库，然后回收user1 的  insert  , delete , update 权限

扩展方案：不收回从库权限，设置read-only参数确保从库只读

[mysqld]

read-only   #只对普通用户有效，root用户无效

2）方案2

主库：user1 （select , insert  , update  , delete）

从库：user2 （ select ）

风险：user1连从库 ，但可以设置read-only参数确保从库只读

[mysqld]

read-only   #只对普通用户有效，root用户无效

3）方案3：推荐

mysql库不同步：在主从库分别进行如下授权

主库：user1 （select , insert  , update  , delete）

从库：user1 （ select ）

主库my.cnf配置：

[mysqld]

binlog-ignore-db = mysql

binlog-ignore-db = performace_schema

binlog-ignore-db = information_schema

缺陷：当需要从库接替主库角色时，从库切换主库连接用户权限问题。

解决：保留一个从库专门准备接替主，这个从库不要不同步mysql库；