Mysql数据库的复制，读写分离，高可用（MHA）

一.Mysql数据库

    MySQL是一种关系数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性,其所使用的 SQL 语言是用于访问数据库的最常用标准化语言。支持多线程，充分利用 CPU 资源。优化的 SQL查询算法，有效地提高查询速度，既能够作为一个单独的应用程序应用在客户端服务器网络环境中，也能够作为一个库而嵌入到其他的软件中。Linux作为操作系统，Apache或者Nginx作为web服务器，mysql作为数据库，php作为服务器脚本解释器，这四个开源软件搭建一个稳定的，免费的网站系统，“LNMP”或者“LAMP”架构。数据库的复制有多种类型：一主多从，一主一从，级联复制等

二.mysql的主从复制

    一台mysql作主服务器，一个或者多个服务器充当从服务器。注意，所有对数据库的更新都要在主库进行，以免从库和主库的数据发生冲突，数据库的复制是单向的，只能由主库指向从库，主从复制是异步的复制，不能确保数据的一致性。主库将更新都记录到二进制日志文件中，主库创建一个binlog Dump线程，将二进制日志文件发送到从库，从库创建一个IO线程，用于接收主库的二进制日志文件，并将数据复制到从库的数据库目录中，也就值中继日志。同时会创建一个SQL线程，把从库数据库目录的中继日志在从库执行。

实验环境：rhel6.5   主库：172.25.78.3(server3) 从库172.25.78.4(server4)   数据库的安装包：mysql-5.7.11-1.el6.x86_64.rpm-bundle.tar

1.传统的主从复制

(1)mysql的安装（主库和从库都安装）：

(2)mysql的初始化：

初始化之后在数据库的日志里会生成一个临时密码，使用此密码进行登陆以及更改新的密码

(3)mysq的登陆以及密码的更改（此命令mysql_secure_installation用于更改新密码）

(4)mysql的主从复制的配置(mysql的配置文件：/etc/my.cnf)

master：

设置server-id=3  必须保证全局唯一

log-bin=mysql-bin        ##主库的二进制日志文件的前缀是mysql-bin，这也是主从复制的关键

主库授权一个用于同步的从库用户以及用户权限

show master sstatus;    ##查看主库的状态包含了二进制文件名，pos号(日志的位置)，忽略的数据库，GTID等

flush privileges              ##刷新数据使其更新

slave：

设置server-id=4 必须保证全局唯一

在从库上面开启复制功能

start slave          ##打开从库

show slave status；    ##查看从库状态（关键在于SQL线程和IO线程，这两个线程OK，那就没问题，主从复制完成）

在主库上面查看主库的UUID和从库上面同步的一致

(5)测试:

在主库创建一个数据库

从库也成功同步

为了避免用户在从库上进行数据的更新，在从库的配置文件中设置只读权限：read_only=on,

/var/lib/mysql      ##数据库的目录

master的数据库的目录如下：

slave的数据库的目录如下：

master.info        ##主库的信息

2.基于GTID的主从复制（mysql5.6以后支持GTID）

       借助GTID，在发生主备切换的情况下，MySQL的其它Slave可以自动在新主上找到正确的复制位置，这大大简化了复杂复制拓扑下集群的维护，也减少了人为设置复制位置发生误操作的风险。另外，基于GTID的复制可以忽略已经执行过的事务，减少了数据发生不一致的风险。

(1)主库和从库的配置

master：

gtid_mode=on      ##打开gtid的模块

enforce-gtid-consistency=true     ##保证gtid安全的参数

slave：同样也要打开gtid的模块

分别重启mysql

master：（在传统的主从复制的基础上进行GTID的主从复制，如果出现从库的IO线程为：connecting，需要再次授权即可）

slave：在从库上开启gtid的复制功能

master_auto_position=1      ##表示日志的位置是自动去同步，不再设置主库的master_log_file以及master_log_pos

(2)测试：

主库创建表插入数据

从库同步数据成功

三.mysql的并行复制

    在MySQL 5.6之前的版本里，有三个线程参与，都是单线程：Binlog Dump（主） ----->IO Thread （从） -----> SQL Thread（从）,复制出现延迟，一个是从库的SQL线程（因为大量的数据需要同步，一时间忙不过来），另一个是因为网络的波动导致从库的IQ线程异常。

并行复制在slave上面配置（同时有16个worker线程去更新中继日志）：

slave-parallel-type=LOGICAL_CLOCK         ##基于组提交的并行复制
slave-parallel-workers=16                       ##开启16个worker
master_info_repository=TABLE              ##默认以文件存储，原来是存在磁盘里，现在以表的形式存储
relay_log_info_repository=TABLE           ##优化选项
relay_log_recovery=ON                           ##从库要求重新同步master日志 （当从库down掉，就会丢弃中继日志，重新请求relay-log）

四.mysql的半同步复制

    MySQL的复制功能默认是异步的，主库把binlog日志发送给从库，这一动作就结束了，并不会验证从库是否接收，就有可能出现当主服务器或从服务器端发生故障的时候，从库并没有接收到主服务器发送过来的binlog日志，这就会造成主库和从库的数据不一致，甚至在恢复时造成数据的丢失。

    半同步复制的工作原理：当slave从库IO_Thread线程将binlog日志接收完成，会给master主库一个应答，如果超过10秒主库未收到slave从库的接受确认信号，那么就会自动切换为异步复制模式。

1.半同步复制的配置（基于mysql的内置插件）

master：

rpl_semi_sync_master_enabled=1         ##开启半同步

rpl_semi_sync_master_timeout=10000   ##超时时间（默认10s）

slave：从库也同样配置，只不过不用配置超时时间

2.激活半同步复制

slave：停止slave的IO线程，再次开启即可

master：

show status like ‘rpl%’；   ##查看半同步的状态（一个slave在线）

3.测试

(1)关闭slave的IO线程

(2)在master插入数据

有10s的延迟之后才发送过去

再次更新数据，延迟为0.07s，显然已经切换到异步复制

有三个任务是没有收到slave的回应

(3)打开slave的线程查看数据是否同步，当开启slave的IO线程后，立即进行了同步

五.数据库的级联复制(基于GTID的级联复制，如果要半同步复制，那就在B节点同时安装半同步的master和slave插件，因为B服务器即是从库，又是C服务器的主库)

    在有些应用场景中，可能读写压力差别比较大，读压力特别的大，一个Master可能需要更多的Slave才能够支撑读的压力。此时，Master负载比较大，因为仅仅连上来的SlaveIO线程就比较多了，这样写的压力较大的，Master端因为复制就会消耗较多的资源，很容易造成复制的延时。多层级联复制的架构，很容易就解决了Master端因为附属Slave太多而成为瓶颈的风险。

A-->B-->C

A：172.25.78.3(server3)  B:172.25.78.4(server4)  C:172.25.78.1(server1)

1.级联复制的各个节点的配置

masterA：

slaveB:（在172.25.78.4上面一定要开启二进制日志文件，是slaveC实现同步的关键）

log_slave_updates=1   ##将B服务器从A服务器收到的更新记入到B服务器自己的二进制日志文件中，这样才能同步主库A的数据

slaveC:

2.添加新的从库数据一定要和主库保持一致，手动把主库数据导入server1的从库

(1)先创建数据库

(2)把主库数据备份发送到slaveC

mysqldump -p   student  > test.sql

(3)把数据导入新从库slaveC

3.在B服务器进行C服务器的复制用户的授权

4.在C服务器(server1)上开启复制功能

5.测试

(1)查看B服务器的状态（有两个GTID，一个是主库A的，一个是和从库C的）

(2)查看主库A的状态

六.mysql的读写分离

    数据的更新在主库，查询功能在从库进行，这样就能减小主库的负载，把对数据库的请求分开处理，利于数据库的性能提升，通常用于读远大于写的场景。

两种常用的读写分离的实现方式

实验环境：server2：mysql-proxy代理服务器    master: (server3)172.25.78.3  slave:172.25.78.4(server4)

server3和server4依旧保持主从复制

mycat
mysql-proxy （mysql自带）
mysql-proxy+lua（包含读写分离的脚本）来实现读写分离
1.安装mysql-proxy和lua
mysql-proxy的安装包：mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
lua安装包：luajit-2.0.4-3.el6.x86_64.rpm
mysql-proxy  --help-all    ##查看mysql-proxy的配置方式

2.mysql-proxy配置
vim /usr/local/mysql-proxy/conf/mysql-proxy.conf    ##把mysql-proxy的配置信息写在自己创建的配置文件中，以指定mysql-proxy配置文件路径的方式开启代理服务器。
pid-file=/usr/local/mysql-proxy/logs/mysql-proy.pid   ##mysql-proxy的pid的文件位置
log-file=/usr/local/mysql-proxy/logs/mysql-proy.log   ##mysql-proxy的日志位置
proxy-address=172.25.78.2:3306   ##代理服务器IP：port
proxy-read-only-backend-addresses=172.25.78.4:3306 ##只读mysql的IP：port
proxy-backend-addresses=172.25.78.3:3306           ##主mysql的IP：port
proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua  ##读写分离的lua脚本的位置

vim  /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua    
为了很快看出结果，把连接数设定为最大一个连接，最小一个连接的时候，就开始读写分离

开启mysql-proxy（报错：/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua的权限不够）

mysql-proxy  --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf   &   ##后台开启进程

3.在master上面授权远程登陆数据库的用户(由于是主从复制，所以不用在从库授权)

4.测试

(1)在代理端，主库端以及从库端安装lsof（为了查看3306端口哪个终端占用）

(2)在物理机远程连接数据库进行查询和更新数据

(3)查看三个主机的3306的端口占用情况

代理端（server2）指向了主库（server3）和从库（server4），实现了读写分离

主库（server3）指向从库（server4）和代理端（server2）

从库（server4）指向主库（server3）和代理端（server2）

七.MHA(数据库的高可用架构)

    MySQL高可用性环境下故障切换和主从提升的高可用软件。在MySQL故障切换过程中，MHA能做到秒级自动完成数据库的故障切换操作，并且在进行故障切换的过程中，MHA能在最大程度上保证数据的一致性，通过SSH传送二进制日志

server2：做管理节点 server3：主库 server4和server1：从库

1.MHA的软件安装

(1)在server2上安装管理节点MHA manager以及各种依赖包(以下安装包全部安装)

关于管理节点的文件，脚本以及模块

masterha_check_ssh              检查MHA的SSH配置状况
masterha_check_repl             检查MySQL复制状况
masterha_manger                  启动MHA
masterha_check_status           检测当前MHA运行状态
masterha_master_monitor         检测master是否宕机
masterha_master_switch          控制故障转移（自动或者手动）

(2)server3,server4,server1都安装数据节点(MHA Node)的安装包

2.配置SSH免密登陆（MHA管理数据节点以及二进制日志文件的传输通过SSH通信）

(1)ssh-keygen生成密钥

(2)SSH认证文件是 .sss/authorized_keys(把公钥复制到此文件下)

(3)把认证信息发送到每个数据节点，建立通信

(4)测试免密登陆成功

3.配置每个数据节点的数据库配置文件（除了各自的server-id不同以外，别的都一致）

4.创建MHA的工作目录，并且创建相关配置文件

mkdir   /etc/masterha/appq.cnf   ##创建目录

vim /etc/masterha/app1.cnf         ##创建相关配置文件

manager_workdir=/etc/masterha/              ##manager的工作目录
manager_log= /etc/masterha/manager.log      ##manager的日志
master_binlog_dir=/var/lib/mysql                     ##master 保存binlog的位置，以便MHA可以找到master的日志
password=Redhat=123                                   ##mysql中root用户的密码
user=root                                                         ##监控用户root
ping_interval=1                                                ##设置监控主库，发送ping包的时间间隔
remote_workdir=/tmp        ##设置远端mysql在发生切换时binlog的保存位置
repl_password=Redhat=123       ##设置复制用户的密码
repl_user=repl                 ##设置复制环境中的复制用户名
ssh_user=root                 ##设置ssh的登录用户名

[server1]
hostname=172.25.78.3
port=3306

[server2]
hostname=172.25.78.4
port=3306
candidate_master=1      ##设置为候选master，如果设置该参数以后，发生主从切换以后将会将此从库提升为主库，即使这个主库不是集群中事件最新的slave
check_repl_delay=0        ##MHA触发切换在选择一个新的master的时候将会忽略复制延时，这个参数对于设置了candidate_master=1的主机非常有用，因为这个候选主在切换的过程中一定是新的master

[server3]
hostname=172.25.78.1
port=3306

5.检查管理节点到所有数据节点的SSH连接状态（SSH配置成功）

6.查看整个集群的状态

报错：（发现是mysql的设置问题，没有授权用户）

(1)之前在MHA Manager的配置文件中设置的监控用户是root，对root用户授权（只在主库授权即可，一主两从）

(2)重新验证集群状态（可见集群状态OK，主库是172.25.78.3）

(3)masterha_check_status           检测当前MHA运行状态(正在运行，主库172.25.78.3)

7.测试集群能否成功主从切换

1.发生故障主从自动切换

(1)手动kill掉server3(主库)上面的mysql进程，模拟主库故障

(2)查看MHA的日志

cat   /etc/masterha/manager.log         ##MHA日志文件

显然原来的主库server3已经down掉，172.25.78.4成为新的主库，同时也显示对原来主库的一些应对方法，可以指定原主库为从库

(3)手动设置server3为从库

成功指向新的主库SQL和IO线程正常

2.在线主从切换

(1)masterha_master_switch --conf=/etc/masterha/app1.cnf --master_state=alive    --new_master_host=172.25.78.1--new_master_port=3306 --orig_master_is_new_slave

主库状态OK，新的主库为172.25.78.1：3306，原来的主库作为从库

(2)查看日志(检查172.25.78.1知否具备主库的能力)

(3)原主库172.25.78.4开始去复制新的主库172.25.78.1,成功切换到172.25.78.1

(4)在数据库里查看slave的状态(成功复制172.25.78.1，切换成功)

(5)masterha_check_status           检测当前MHA运行状态（主库在172.25.78.1上）

3.主库故障，手动切换到新的主库

(1)手动kill掉server1(主库)上面的mysql进程，模拟主库故障

(2)masterha_master_switch --master_state=dead --conf=/etc/masterha/app1.cnf --dead_master_host=172.25.78.1 --dead_master_port=3306 --new_master_host=172.25.78.3 --new_master_port=3306 --ignore_last_failover

主库已经down掉，切换到新的主库172.25.78.3，同时忽略上次的失效点，否则不能切换，默认是8小时不能再次切换，也可以删除掉/etc/masterha/目录里的上次切换完成的文件。

(3)查看日志整个切换过程

切换的整个过程：

检查新的主库状态是否OK，确定新的主库；确定新的从库，所有从库开始复制新的主库；切换完成