Mysql常用主从复制架构以及MHA高可用的主从复制的实现

实验一：一主多从或者是一主从从架构的实现一、实验环境：三台服务器，一台master,一台slave，一台slave的slave（或者是master的slave）。二、实验准备：配置好yum源、 防火墙关闭、 各节点时钟服务同步、 各节点之间可以通过主机名互相通信。三、mysql主从复制原理：1、该过程的第一部分就是master记录二进制日志。在每个事务更新数据完成之前，master在二进制日志记录这些改变。MySQL将事务串行的写入二进制日志，在事件写入二进制日志完成后，master通知存储引擎提交事务。此后可接收slave的请求。2、下一步就是slave将master的binary log拷贝到它自己的中继日志。首先，slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接，然后开始在主节点上binlog dump process（二进制转存线程）。Binlog dump process从master的二进制日志中读取事件，如果已经跟上master，它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。3、 SQL slave thread（SQL从线程）处理该过程的最后一步。SQL线程从中继日志读取事件，并重放其中的事件而更新slave的数据，使其与master中的数据一致。只要该线程与I/O线程保持一致，中继日志通常会位于OS的缓存中，所以中继日志的开销很小。四、架构图五、实验步骤：第一步：配置maser服务器vim /etc/my.cnfserver-id=33 指定serverid，唯一辨识主服务器log-bin=mysql-bin 开启二进制日志，记录数据变化，给从通路。skip-name-resolve 跳过名称解析第二步：配置slave服务器vim /etc/my.cnfserver-id=34 配置从服务器，让其拥有唯一辨识relay-log=mysql-relay-log 打开中继日志，和主通讯。read-only=1 只读权限，读写分离，确保数据的一致性log-bin=mysql-bin 开启二进制日志，记录数据的变化，从也能再设置从服务器log-slave-updates=1 把中继日志中更新的日志写入二进制日志中第三步：创建复制帐号在Master的数据库中建立一个备份帐户：每个slave使用标准的MySQL用户名和密码连接master。进行复制操作的用户会授予REPLICATION SLAVE权限。GRANT REPLICATION SLAVE, REPLICATION CLIENT ON . TO slave@'172.17.%.%' IDENTIFIED BY 'magedu';这一步完成后可以查看一下当前的二进制日志的position。设计好从哪里开始复制，并且复制的二进制数据库是哪一个。show master status; 查看主服务器的二进制日志的状态show binlog events in 'mysql-bin.000003'\G 查看这个二进制日志的信息第四步：启动从服务器复制线程change master to master_host='172.17.254.171',master_user='slave',master_password='magedu',master_log_file='mysql-bin.000003',master_log_pos=245;start slave；开启slave的I/O线程和SQL线程第五步：查看从服务器状态可使用SHOW SLAVE STATUS\G查看从服务器状态，如下所示，也可用show processlist \G查看当前复制状态：Slave_IO_Running: Yes #IO线程正常运行Slave_SQL_Running: Yes #SQL线程正常运行第六步：配置从的从服务器由上面就可以知道，当把主的全部的内容复制到从了，包括主的授权，所以当从再配置一个从服务器的时候，不需要再次授权，直接配置就行了。配置文件上面都已经标明，不需要再次解释了。vim /etc/my.cnfserver-id=36relay-log=mysql-relay-logread-only=1log-bin =mysql-binlog-slave-updates=1第七步：启动从的从服务器复制线程。CHANGE MASTER TO MASTER_HOST='172.17.254.181',master_user='slave',master_password='magedu',master_log_file='mysql-bin.000003',master_log_pos=245;start slave；开启slave的I/O线程和SQL线程第八步：也可以把从 的从服务器改成主的从服务器，也就是一主多从的意思，把第七步的master_host换成主的ip就行了。五：易错点：1、切换master的时候要stop slave，当然关闭之后要更换完之后要记得开启start slave。2、要注意复制的日志的衔接。切换主的时候提示数据库已经存在，就要更改position了。3、把从的从服务器更改成主的从服务器的时候出现如下错误：Could not initialize master info structure; more error messages can be found in the MySQL error log。原因：配置过主从后可能数据库中存放的路径下会出现master|relay.info两个文件，这两个文件说明已经配置过主从，要想切换直接删除就行了。实验总结：实现一主多从或者是一主从从都是为了减轻数据库服务器的压力，但是也只是能够解决读的问题，不能解决写的问题，数据库主从复制最重要的问题就是数据的一致性。如果非要解决也只能实现双主，但是不推荐使用。

实验二：双主架构

在上一题的基础上实现就行了，只需要把主配置成从。第一步：每个节点都配置需要配置中继日志和二进制日志。把从的配置复制一份过来就可以了。第二步：设置自增长ID。增长的方式要不一样，不然还是会重复id,导致数据不一致。定义一个节点使用奇数idauto_increment_increment=2 #表示自增长字段每次递增的量auto_increment_offset=1 #表示自增长字段从那个数开始另一个节点使用偶数idauto_increment_increment=2auto_increment_offset=2 第三步：启动从的从服务器复制线程。MASTER_HOST='172.17.254.171',master_user='slave',master_password='magedu',master_log_file='mysql-bin.000003',master_log_pos=245;start slave；开启slave的I/O线程和SQL线程第四步：测试节点一：insert into m26 values(1),(2),(3)节点二：insert into m26 values(1),(2),(3)效果：select * from m26;+----+| id |+----+| 1 || 2 || 3 || 1 || 2 || 3 |实验总结：双主虽然可以插入数据，但是数据的排序并不是按顺序排列，只能保证奇数偶数不重复。但是当两个人同时对一个数据进行修改，就要看下手的速度了。通过分库来实现双主，效果会好一点，双主也只能在小并发的情况下使用。

实验三：MySQL高可用架构之MHA

MHA是一款开源的 MySQL 的高可用程序， 它为 MySQL主从复制架构提供 了 automating master failover 功能。 MHA 在监控到master 节点故障时， 会提升其中拥有最新数据的 slave 节点成为新的master 节点， 在此期间， MHA 会通过于其它从节点获取额外信息来避免一致性方面的问题。 MHA 还提供了 master 节点的在线切换功能， 即按需切换 master/slave 节点 。一、实验环境：1、准备mysql的主从结构，具体步骤见上面的实验；2、准备提供mha工作的基本工具3、关闭selinux和iptables，同步系统时间二、实验拓扑图：三、实验原理MHA工作原理总结为以下几条：（1） 从宕机崩溃的master保存二进制日志事件（binlog events） ;（2） 识别含有最新更新的slave;（3） 应用差异的中继日志(relay log) 到其他slave;（4） 应用从master保存的二进制日志事件(binlog events);（5） 提升一个slave为新master;（6） 使用其他的slave连接新的master进行复制。 四、实验步骤第一步：每个节点都要开启中继日志和二进制日志，各个从节点都要启用read-only,并关闭relay_log_purge功能等.本实验环境共有四个节点， 其角色分配如下：node1:MariaDB masternode2: MariaDB slavenode3: MariaDB slavenode4: MHA Manager各节点的/etc/hosts文件配置内容中添加：172.17.254.171 node1.magedu.com172.17.254.181 node2.magedu.com172.17.252.196 node3.magedu.com172.17.254.238 node4.magedu.com第二步：初始节点的配置，详情步骤参看上面主从配置：master：server-id=20 //指定serverid，唯一辨识主服务器log-bin = master-log //开启二进制日志relay-log = relay-log //开启中继日志 skip_name_resolve //关闭名称解析（非必须）systemctl restart mariadbSlave1：server-id = 21 //指定serverid，唯一辨识主服务器relay-log = relay-log //开启中继日志 log-bin = master-log //开启二进制日志relay_log_purge = 0 //是否自动清空不再需要中继日志skip_name_resolve //关闭名称解析（非必须）log_slave_updates = 1 //使得更新的数据写进二进制日志中systemctl restart mariadbSlave2:server-id = 21 //指定serverid，唯一辨识主服务器relay-log = relay-log //开启中继日志 log-bin = master-log //开启二进制日志relay_log_purge = 0 //是否自动清空不再需要中继日志skip_name_resolve //关闭名称解析（非必须）log_slave_updates = 1 //使得更新的数据写进二进制日志中systemctl restart mariadb第三步：配置一主多从复制架构master节点上：MariaDB [(none)]>GRANT REPLICATION SLAVE,REPLICATION CLIENT ON. TO 'slave'@'172.17.%.%' IDENTIFIED BY 'magedu';MariaDB [(none)]> FLUSH PRIVILEGES;MariaDB [(none)]> SHOW MASTER STATUS;各slave节点上：[root@node3 ~]# mysqlMariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='172.17.%.%',MASTER_USER='slave' ,MASTER_PASSWORD='magedu' ,MASTER_LOG_FILE='master-log.000003',MASTER_LOG_POS=245;MariaDB [(none)]> START SLAVE;MariaDB [(none)]> SHOW SLAVE STATUS\G;第四步：安装MHA并配置1、在所有MYSQL节点授权拥有管理权限的用户可在本地网络中有其他节点上远程访问。 当然， 此时仅需要且只能在master节点运行类似如下SQL语句即可。mysql> GRANT ALL ON . TO 'mhaadmin'@'172.17.%.%' IDENTIFIED BY 'mhapass';2、MHA集群中的各节点彼此之间均需要基于ssh互信通信， 以实现远程控制及数据管理功能。简单起见， 可在Manager节点生成密钥对儿， 并设置其可远程连接本地主机后， 将私钥文件及authorized_keys文件复制给余下的所有节点即可。下面操作在node4：Manager 节点上操作：[root@node4 ~]# ssh-keygen -t rsa[root@node4 ~]#ssh-copy-id -i .ssh/id_rsa.pub root@node4 四台机器都做如上操作，然后manager下就会出现一个包含所有节点的公钥文件authorized_keys。然后把这个文件上发送给其他的节点scp authorized_keys root@node1.magedu.com:~/.ssh/scp authorized_keys root@node2.magedu.com:~/.ssh/scp authorized_keys root@node3.magedu.com:~/.ssh/3、进行MHA安装包安装Manager 节点： #yum install mha4mysql-manager-0.56-0.el6.noarch.rpm所有节点， 包括Manager: #yum install mha4mysql-node-0.56-0.el6.norch.rpm4、初始化MHA， 进行配置Manager 节点需要为每个监控的master/slave集群提供一个专用的配置文件，而所有的master/slave集群也可共享全局配置。全局配置文件默认为/etc/masterha_default.cnf，其为可选配置。 如果仅监控一组master/slave集群， 也可直接通过application的配置来提供各服务器的默认配置信息。而每application的配置文件路径为自定义。5、定义MHA管理配置文件,需要注意的地方是有几台mysql服务器就需要几个server，manager管理所有的机器为MHA专门创建一个管理用户， 方便以后使用， 在mysql的主节点上， 三个节点自动同步mkdir /etc/mha_mastervim /etc/mha_master/app1.cnf配置文件内容如下：[server default]user=mhaadminpassword=mhapassmanager_workdir=/etc/mha_master/app1manager_log=/etc/mha_master/manager.logremote_workdir=/mydata/mha_master/app1ssh_user=rootrepl_user=slaverepl_password=mageduping_interval=1[server1]hostname=172.17.254.181ssh_port=22candidate_master=1[server2]hostname=172.17.252.196ssh_port=22candidate_master=1[server3]hostname=172.17.254.171ssh_port=22candidate_master=16、检测各节点间ssh互信通信配置是否Ok:[root@node4 ~]# masterha_check_ssh -conf=/etc/mha_master/app1.cnf输出信息最后一行类似如下信息， 表示其通过检测。[info]All SSH connection tests passed successfully.7、检查管理的MySQL复制集群的连接配置参数是否OK：[root@node4 ~]#masterha_check_repl -conf=/etc/mha_master/app1.cnf如果测试时会报错， 可能是从节点上没有账号，因为这个架构，任何一个从节点，将有可能成为主节点， 所以也需要创建账号。因此， 这里只要在mater节点上再次执行以下操作即可：MariaDB [(none)]>GRANT REPLICATION SLAVE,REPLICATION CLIENT ON . TO 'slave@172.17.%.%' IDENTIFIED BY 'magedu';MariaDB [(none)]> FLUSH PRIVILEGES;Manager节点上再次运行， 就显示Ok了。第五步：启动MHA[root@node4 ~]#nohup masterha_manager -conf=/etc/mha_master/app1.cnf &> /etc/mha_master/manager.log &启动成功后， 可用过如下命令来查看master节点的状态:[root@node4 ~]#masterha_check_status -conf=/etc/mha_master/app1.cnfapp1 (pid:4978)is running(0:PING_OK),master:172.16.252.18上面的信息中“app1 (pid:4978)is running(0:PING_OK)”表示MHA服务运行OK，否则， 则会显示为类似“app1 is stopped(1:NOT_RUNNINg).”如果要停止MHA， 需要使用master_stop命令。[root@node4 ~]#masterha_stop -conf=/etc/mha_master/app1.cnf五、测试MHA测试故障转移1、在master节点关闭mariadb服务,模拟主节点数据崩溃killall -9 mysqld mysqld_saferm -rf /var/lib/mysql/*2、在manager查看日志tail -200 /etc/mha_master/manager.log ……Thu Nov 23 09:17:19 2017 - [info] Master failover to 172.17.254.181(192.168.254.181:3306) completed successfully.3、查看当前的manager状态，会提示mha停止masterha_check_status –conf=/etc/mha_master/app1.cnfapp1 is stopped(2:NOT_RINNING)4、提供新的从节点以修复复制集群原有 master 节点故障后， 需要重新准备好一个新的MySQL节点。基于来自于master节点的备份恢复数据后将其配置为新的 master 的从节点即可。 注意，新加入的节点如果为新 增节点，其IP地址要配置为原来master节点的IP，否则还需要修改 app1.cnf 中相应的 ip 地址。随后再次启动manager，并再次检测其状态。也就是说把故障的master节点变成新的master的从节点，注意备份的问题，避免数据脱节。现在slave1已经是主了。所以我们对它进行完全备份。mysqldump --all-database > /backup/date +%F-%T.sql然后把文件传送给原来的masterscp 2017-11-23-22:09:25.sql root@node1.magedu.com:/root/注意的是：我也不知道为什么会提示传送解析不了，所以我就去原来的主上直接来下载了。5、在原来的主上进行数据恢复mysql -uroot <2017-11-21_09:43:22-all.sql；6、接下来配置主从，照例查看一下现在的主的二进制日志的位置，然后进行新的从节点配置：Node1：change master to master_host='172.17.254.181', master_user='slave', master_password='magedu', master_log_file='master-log.000003', master_log_pos=1044;这里需要提示的是，不需要主上再次授权，因为之前主从复制的时候复制过去的逻辑语句已经执行过了。7、新节点提供后再次执行检查操作masterha_check_repl -conf=/etc/mha_master/app1.cnf8、检查无误， 再次运行， 这次要记录日志masterha_manager -conf=/etc/mha_master/app1.cnf >/etc/mha_master/manager.log 2>&19、可用如下命令来查看master节点的状态：masterha_check_status -conf=/etc/mha_master/app1.cnfmha (pid:9561) is running(0:PING_OK), master:171.17.254.181六、新节点上线， 故障转换恢复注意事项(1)、 在生产环境中， 当你的主节点挂了后， 一定要在从节点上做一个备份， 拿着备份文件把主节点手动提升为从节点， 并指明从哪一个日志文件的位置开始复制(2)、 每一次自动完成转换后， 每一次的(replication health )检测不ok始终都是启动不了必须手动修复主节点， 除非你改配置文件(3)、 手动修复主节点提升为从节点后， 再次运行检测命令[root@node5 ~]# masterha_check_repl --conf=/etc/mha_master/app1.cnfapp1 (pid:3211) is running(0:PING_OK), master:172.17.254.181(4)、 再次运行起来就恢复成功了masterha_manager --conf=/etc/mha_master/app1.cnf七、易错点问题1、 Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'原因：二进制名称写错问题2、 Opening /var/lib/mysql/relay-log.info ...Could not open relay-log-info file /var/lib/mysql/relay-log.info.原因：master|relay.info两个文件，主从配置好过后一定有的文件。需要检查一下从服务器有没有这个文件问题3、scp 2017-11-23-22:09:25.sql root@172.17.254.171:~ssh: Could not resolve hostname 2017-11-23-22: Name or service not known原因：不知道 ，上传不行，我就下载了。问题4、ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''1044'' at line原因：,MASTER_LOG_POS='1044'; 多了一个双引号。问题5、Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'原因：复制的二进制数据库写错了。问题6、[error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln301] Got MySQL error when connecting 172.17.254.171(172.17.254.171:3306) :1130:Host '172.17.254.238' is not allowed to connect to this MariaDB server, but this is not a MySQL crash. Check MySQL server settings.原因：每一个数据库都可以是主节点，所以需要在主上再一次授权。实验总结：都是自己挖的坑，配置文件要检查好，不要写错，写掉，授权容易出错，一定要注意。