MySQL-（集群-MHA）

1.为什么要做主从复制

1.分担主库的压力
2.做读写分离

  • 主从复制原理图


    image.png

MySQL的 IO线程和SQL线程报错

IO 线程：

  • UUID报错


    image.png
#1.UUID相同，主从复制会报错
#解决方法1:
[root@mysql-db02 data]# vim auto.cnf 
[root@mysql-db02 data]# /etc/init.d/mysqld restart

#解决方法2：
[root@mysql-db02 data]# rm -f auto.cnf 
[root@mysql-db02 data]# /etc/init.d/mysqld restart

  • server_id和主库相同


    image.png
#2.server_id相同,主从复制会报错
#解决方法：修改server_id
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+

# 这些配置文件可能存在server_id，找出删除
/etc/my.cnf -> /etc/mysql/my.cnf -> /application/mysql/my.cnf -> --defaults-extra-file -> ~/.my.cnf

----------------------------------------------------------------------------------------------------------

change master to
master_host='10.0.0.51',
master_user='rep',
master_password='123',
master_log_file='mysql-bin.000001',
master_log_pos=1132;

#3.网络
[root@mysql-db02 data]# ping 10.0.0.51
[root@mysql-db02 data]# tcping 10.0.0.51 3306
10.0.0.51 port 3306 open.

#4.端口
[root@mysql-db02 data]# telnet 10.0.0.51 3306
[root@mysql-db02 data]# tcping 10.0.0.51 3306
10.0.0.51 port 3306 open.

#5.用户名和密码
[root@mysql-db02 data]# mysql -urep -p123 -h10.0.0.51

#6.反向解析
#解决方法：跳过反向解析
vim /etc/my.cnf
[mysqld]
skip_name_resolve
/etc/init.d/mysqld restart

SQL线程：

  • 主库上有从库上没有的数据


    image.png
# 主库上有xtr库，从库上没有xtr库
Slave_SQL_Running: No
Last_Error: Error executing row event: 'Table 'xtr.xtr' doesn't exist'

#方法一：（不推荐）
#临时停止同步
mysql> stop slave;
#将同步指针向下移动一个（可重复操作）
mysql> set global sql_slave_skip_counter=1;
#开启同步
mysql> start slave;

#方法二：(不推荐)
#编辑配置文件
[root@db01 ~]# vim /etc/my.cnf
#在[mysqld]标签下添加以下参数
slave-skip-errors=1032,1062,1007,1146
  • 主库上没有从库上有的数据(主从复制)
# 从库上有 zkmy，主库又创建一遍
Error 'Can't create database 'zkmy'; database exists' on query. Default database: 'zkmy'. Query: 'create database zkmy'

# 注意：主从数据不一致
#1.主库全备（打点）
[root@mysql-db01 data]# mysqldump -A -R --triggers --master-data=2 --single-transaction|gzip > /tmp/full.sql.gz

#2.从库准备一个新的环境
[root@mysql-db02 mysql] mv data data_bak
[root@mysql-db02 /application/mysql]# cd scripts/
[root@mysql-db04 /application/mysql/scripts] # ./mysql_install_db --user=mysql --basedir=/application/mysql --datadir=/application/mysql/data

#3.将数据传到从库
[root@mysql-db01 data]# scp /tmp/full.sql.gz 172.16.1.52:/tmp/

#4.将数据导入
[root@mysql-db02 tmp]# zcat /tmp/full.sql.gz |mysql

#5.查看备份的位置点
[root@mysql-db02 scripts]# zcat /tmp/full.sql.gz |head -25
CHANGE MASTER TO
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=2821,
MASTER_HOST='10.0.0.51',
MASTER_USER='rep',
MASTER_PASSWORD='123',
master_port=3309;
#6.启动主从并检查是否运行正常
start slave;
show slave status\G

#7.从库设置只读
set global read_only=1;  # 临时生效只读

vim /etc/my.cnf         # 永久生效只读
[mysqld]
read_only=1

# 用户
1.root
2.程序连接用户
3.运维或者DBA使用的用户
4.所有开发统一使用一个用户 dev slect update insetr

2.MySQL延时从库

延迟从库作用：可以在主库误操作后。只需在设置的延迟时间内，通过截取从库上的relay log达到恢复数据的目的。
企业一般设置延时：3-6个小时

  • 原理图


    image.png
  • 配置
#方法一：（未做过主从配置情况）
CHANGE MASTER TO
MASTER_LOG_FILE='mysql-bin.000002', 
MASTER_LOG_POS=338,
MASTER_HOST='10.0.0.51',
MASTER_USER='rep',
MASTER_PASSWORD='123',
MASTER_DELAY=180;          #这里的延时单位是秒。此处实验环境为180秒

#方法二：（已经做过主从配置。只需添加延时单条配置即可）
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to master_delay=250;
Query OK, 0 rows affected (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
  • 延迟从库恢复思路（误删除数据）
#1.停 SQL线程（128）
mysql> stop slave sql_thread;

#2.停 延时从库
[root@mysql-db04 ~]# /etc/init.d/mysqld stop

#3.导出延时从库中的所有数据
[root@mysql-db04 data]# mysqldump -A > /tmp/ys.sql

#4.找新增的数据（relay log）
./mysql-db04-relay-bin.000002
283
#第一个截取点（无drop情况下）  
起始位置点：283
结束位置点：16960
[root@mysql-db04 data]# mysqlbinlog --start-position=283 --stop-position=16960 mysql-db04-relay-bin.000002 > /tmp/inc1.sql

 # 第二个截取点（跳过drop点后。新增的数据）
起始位置点:17049
结束位置点：1036586
[root@mysql-db04 data]# mysqlbinlog --start-position=17049 --stop-position=1036586 mysql-db04-relay-bin.000002 > /tmp/inc2.sql

#5.将所有数据恢复到主库
mysql < /opt/ys.sql         # 延迟从库全备（差数据）
mysql < /opt/inc1.sql       # 第一个截取点（无drop情况下）  
mysql < /opt/inc2.sql       # 第二个截取点（跳过drop点后。新增的数据）

3.半同步复制

5.5 出现概念，但是不建议使用，性能太差
5.6出现group commit 组提交功能，来提升开启半同步复制的性能
5.7更加完善了，在group commit基础上出现了MGR
5.7的增强半同步复制的新特性：after commit； after sync；

  • 原理图


    image.png
  • 半同步配置
#主从库需要安装相应的插件
[root@mysql-db04 plugin]# ll /application/mysql/lib/plugin
-rwxr-xr-x 1 7161 31415 426219 Sep 27 16:19 semisync_master.so      #主库半同步插件
-rwxr-xr-x 1 7161 31415 249327 Sep 27 16:19 semisync_slave.so       #从库半同步插件

#查看是否支持半同步
mysql> show global variables like 'have_dynamic_loading';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| have_dynamic_loading | YES   |
+----------------------+-------+

#安装主库插件
mysql> install plugin rpl_semi_sync_master soname'semisync_master.so';

#启动主库插件
mysql> set  global rpl_semi_sync_master_enabled = 1;

#设置超时时间
set global rpl_semi_sync_master_timeout = 1000;

#添加配置文件，永久生效
[root@db01 ~]# vim /etc/my.cnf
#在[mysqld]标签下添加如下内容（不用重启库）
[mysqld]
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000

#检查
mysql> show variables like'rpl%';
+------------------------------------+----------+
| Variable_name                      | Value    |
+------------------------------------+----------+
| rpl_semi_sync_master_enabled       | ON       |
| rpl_semi_sync_master_timeout       | 1000     |
| rpl_semi_sync_master_trace_level   | 32       |
| rpl_semi_sync_master_wait_no_slave | ON       |
| rpl_stop_slave_timeout             | 31536000 |
+------------------------------------+----------+

mysql> show global status like 'rpl_semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 0     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
+--------------------------------------------+-------+

## 从库安装插件
mysql> install plugin rpl_semi_sync_slave soname'semisync_slave.so';

## 启动插件
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;

## 重启IO线程
mysql> stop slave io_thread;
Query OK, 0 rows affected (0.01 sec)

mysql> start slave io_thread;
Query OK, 0 rows affected (0.01 sec)

mysql> show global status like 'rpl_semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 3     |
| Rpl_semi_sync_master_net_avg_wait_time     | 567   |
| Rpl_semi_sync_master_net_wait_time         | 5107  |
| Rpl_semi_sync_master_net_waits             | 9     |
| Rpl_semi_sync_master_no_times              | 1     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | OFF   |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 639   |
| Rpl_semi_sync_master_tx_wait_time          | 1919  |
| Rpl_semi_sync_master_tx_waits              | 3     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 3     |  # 走半同步的SQL语句有3条
+--------------------------------------------+-------+

4.MySQL 过滤复制

image.png

过滤复制配置方法

  • 主库（不推荐主库配置会造成binlog丢失数据）
#黑名单
#忽略的数据库
binlog-ignore-db

注意：binlog-ignore-db是只不记录该参数指定的库的 binlog
binlog-ignore-db=wzry
binlog-ignore-db=yxlm
------------------------------------------------------------------------------------------
#白名单
#使用的数据库
binlog-do-db

vim /etc/my.cnf
[mysqld]
binlog-do-db=wzry
binlog-do-db=yxlm

注意：binlog-do-db是只记录该参数指定的库的 binlog    （wzry,yxml）
错误写法:
vim /etc/my.cnf
[mysqld]
binlog-do-db=wzry,yxlm


mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000009 |      434 | wzry,yxlm    |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000010 |      510 |              | wzry             |                   |
+------------------+----------+--------------+------------------+-------------------+
  • 从库（推荐设置）
#黑名单
replicate-ignore-db=test
replicate-ignore-table=test.t1
replicate-wild-ignore-table=test.t%

mysql> show slave status\G
              Replicate_Do_DB: wzry,zls
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: yxlm.yxml
      Replicate_Wild_Do_Table: cjzc.t%
  Replicate_Wild_Ignore_Table: 

#相当于白名单的取反。（设置后不提取指定库的数据）
-------------------------------------------------------------------------------------------------------------

#白名单
replicate-do-db=test
replicate-do-table=test.t1
replicate-wild-do-table=test.t2

注意：replicate-do-db 只去主库的binlog中提取该参数指定的库中的所有表数据
     replicate-do-table 只去主库的binlog中提取该参数指定的库中的指定表
     replicate-wild-do-table=test.t%   支持正则

4. MySQL高可用MHA

在主从复制的基础上。保证整套MySQL集群的可用性。

  • MySQL高可用方案：

MHA
NMM
双主 + keepalived
MGR

  • 环境准备：


    image.png
  • MHA的软件介绍

当Master出现故障时，它可以自动将最新数据的Slave提升为新的Master,然后将所有其他的Slave重新指向新的Master。

  • MHA的组件：

node：（注意：对比差异数据，截取binlog，恢复数据）
manager ：（主要：监控主库存活，切换主库）
注意：一个MHA的manager可用管理多套MySQL集群。由于及其的轻量化，不会影响集群性能！

  • MHA工作原理图：


    image.png

  • MHA架构


    image.png

MHA工具

# manager工具
[root@mysql-db01 ~]# tar xf mha4mysql-manager-0.56.tar.gz
cd /root/mha4mysql-manager-0.56/bin
[root@mysql-db01 bin]# ll
masterha_check_repl         ## MHA检测主从复制（自动，手动）
masterha_check_ssh          ## MHA检测ssh免密
masterha_check_status       ## 查看MHA的状态（systemctl status xxx）
masterha_conf_host          # 配置MHA的主机
masterha_manager            ## MHA启动脚本
masterha_master_monitor     # MHA检测主库的心跳
masterha_master_switch      # MHA切换脚本
masterha_secondary_check    # 建立TCP连接
masterha_stop               ## 停止MHA

# node工具
[root@mysql-db01 ~]# tar xf mha4mysql-node-0.56.tar.gz
cd /root/mha4mysql-node-0.56/bin

[root@mysql-db01 bin]# ll
apply_diff_relay_logs   # 对比从库之间的差异日志
filter_mysqlbinlog      # 截取binlog，防止事件回滚
purge_relay_logs        # 删除差异日志
save_binary_logs        # 保存binlog

MHA优点总结

1、Masterfailover and slave promotion can be done very quickly
自动故障转移快 （10-30 0-10）
2、Mastercrash does not result in data inconsistency
主库崩溃不存在数据一致性问题
3、Noneed to modify current MySQL settings (MHA works with regular MySQL)
不需要对当前mysql环境做重大修改
4、Noneed to increase lots of servers
不需要添加额外的服务器(仅一台manager就可管理上百个replication)
5、Noperformance penalty
性能优秀，可工作在半同步复制和异步复制，当监控mysql状态时，仅需要每隔N秒向master发送ping包(默认3秒)，所以对性能无影响。你可以理解为MHA的性能和简单的主从复制框架性能一样。
ping：ICMP
SQL ping： select ping
6、Works with any storage engine
只要replication支持的存储引擎，MHA都支持，不会局限于innodb

部署MHA前戏（提前准备好单机主从复制架构）

# 先做传统的主从复制
# 主库操作
#1.修改主库配置文件
vim /etc/my.cnf
[mysqld]
server_id=1
log-bin=mysql-bin
binlog_format=row
skip_name_resolve      # 反向解析

#2.创建主从复制用户
mysql> grant replication slave on *.* to rep@'%' identified by '123';

#3.查看主从复制binlog 和 pos
mysql> show master status;
+------------------+----------+
| File             | Position |
+------------------+----------+
| mysql-bin.000001 |      317 |
+------------------+----------+

# 从库操作
#1.修改从库配置文件
[mysqld]
server_id=2
log-bin=mysql-bin
binlog_format=row
skip_name_resolve    # 反向解析

#2.从库内，执行change master
change master to
master_host='10.0.0.51',
master_user='rep',
master_password='123',
master_log_file='mysql-bin.000001',
master_log_pos=317;

#开启主从
start slave 
#检查是否开启
show slave status\G

#注意：
1.主库server_id是5 从库不等于5即可，从库之间可以相同么?  #从库不能相同
2.主库要开启binlog，从库用开启binlog么？              #从库必须开启binlog
3.主库要创建主从复制用户，从库需要创建主从复制用户么?     #从库必须创建主从复制用户

# 关闭MySQL自动删除relay log的功能（主库从库都执行）
## 临时关闭
mysql> set global relay_log_purge = 0;

## 永久关闭
vim /etc/my.cnf
[mysqld]
relay_log_purge = 0

# 从库设置只读
## 只能临时设置
mysql> set global read_only=1;

部署MHA

#1.下载安装包，或上传下载好rpm包
wget http://www.mysql.gr.jp/frame/modules/bwiki/index.php?plugin=attach&pcmd=open&file=mha4mysql-manager-0.56-0.el6.noarch.rpm&refer=matsunobu

wget http://www.mysql.gr.jp/frame/modules/bwiki/index.php?plugin=attach&pcmd=open&file=mha4mysql-node-0.56-0.el6.noarch.rpm&refer=matsunobu

#2.安装epel源
wget -O /etc/yum.repos.d/epel.repo https://mirrors.aliyun.com/repo/epel-7.repo

#3.安装node节点
[root@mysql-db01 ~]# yum localinstall -y mha4mysql-node-0.56-0.el6.noarch.rpm

#4.安装manager（必须先要把node装上，node是manager包的依赖之一）,(manger只需一台机安装即可)
[root@mysql-db04 ~]# yum localinstall -y mha4mysql-manager-0.56-0.el6.noarch.rpm 

#5.在MySQL中，创建一个mha的管理用户（每一台都要创建，主库上创建即可，会自动同步）
mysql> grant all on *.* to mha@'%' identified by 'mha';

#6.做mysql和mysqlbinlog的软链接（程序会去/usr/local下找mysql和mysqlbinlog的命令）四台都要做
[root@mysql-db01 ~]# ln -s /application/mysql/bin/mysql /usr/local/mysql
[root@mysql-db01 ~]# ln -s /application/mysql/bin/mysqlbinlog /usr/local/mysqlbinlog

#7.做ssh免密登陆（4台机器都要互相免密，包括自己）
[root@mysql-db01 ~]# ssh-keygen -t dsa -P '' -f ~/.ssh/id_dsa >/dev/null 2>&1
[root@mysql-db01 ~]# ssh-copy-id -i ~/.ssh/id_dsa.pub 10.0.0.51
[root@mysql-db01 ~]# ssh-copy-id -i ~/.ssh/id_dsa.pub 10.0.0.52
[root@mysql-db01 ~]# ssh-copy-id -i ~/.ssh/id_dsa.pub 10.0.0.53
[root@mysql-db01 ~]# ssh-copy-id -i ~/.ssh/id_dsa.pub 10.0.0.54

[root@mysql-db02 ~]# ssh-keygen -t dsa -P '' -f ~/.ssh/id_dsa >/dev/null 2>&1
[root@mysql-db02 ~]# ssh-copy-id -i ~/.ssh/id_dsa.pub 10.0.0.51
[root@mysql-db02 ~]# ssh-copy-id -i ~/.ssh/id_dsa.pub 10.0.0.52
[root@mysql-db02 ~]# ssh-copy-id -i ~/.ssh/id_dsa.pub 10.0.0.53
[root@mysql-db02 ~]# ssh-copy-id -i ~/.ssh/id_dsa.pub 10.0.0.54

[root@mysql-db03 ~]# ssh-keygen -t dsa -P '' -f ~/.ssh/id_dsa >/dev/null 2>&1
[root@mysql-db03 ~]# ssh-copy-id -i ~/.ssh/id_dsa.pub 10.0.0.51
[root@mysql-db03 ~]# ssh-copy-id -i ~/.ssh/id_dsa.pub 10.0.0.52
[root@mysql-db03 ~]# ssh-copy-id -i ~/.ssh/id_dsa.pub 10.0.0.53
[root@mysql-db03 ~]# ssh-copy-id -i ~/.ssh/id_dsa.pub 10.0.0.54

[root@mysql-db04 ~]# ssh-keygen -t dsa -P '' -f ~/.ssh/id_dsa >/dev/null 2>&1
[root@mysql-db04 ~]# ssh-copy-id -i ~/.ssh/id_dsa.pub 10.0.0.51
[root@mysql-db04 ~]# ssh-copy-id -i ~/.ssh/id_dsa.pub 10.0.0.52
[root@mysql-db04 ~]# ssh-copy-id -i ~/.ssh/id_dsa.pub 10.0.0.53
[root@mysql-db04 ~]# ssh-copy-id -i ~/.ssh/id_dsa.pub 10.0.0.54

# 创建MHA配置文件存放目录
[root@mysql-db04 ~]# mkdir /etc/mha
vim /etc/mha/app1.cnf

# 手写MHA配置文件
[server default]
# MHA日志文件
manager_log=/etc/mha/app1/manager.log
# MHA的工作目录
manager_workdir=/etc/mha/app1
# MySQL的binlog存放目录
master_binlog_dir=/application/mysql/data
# MHA的管理用户
user=mha
# MHA的管理用户的密码
password=mha
# MHA每间隔N秒会向主库发送一次select ping检测心跳,默认3s,此处设置2s
ping_interval=2
# MHA主从复制用户的密码
repl_password=123
# MHA主从复制的用户
repl_user=rep
# SSH免密连接的用户
ssh_user=root
# SSH免密连接的端口
ssh_port=22

[server1]
# 数据库的ip
hostname=10.0.0.51
# 数据库的端口
port=3306

[server2]
#candidate_master=1          # 优先升为主库（提前的数据不能落后最新从库100M）
#check_repl_delay=0          # 忽略落后机制，和candidate_master=1组合为强制为优先升为主库（但是二条参数不推荐使用！！！）
hostname=10.0.0.52
port=3306

[server3]
hostname=10.0.0.53
port=3306

[server4]
hostname=10.0.0.54
port=3306

# 创建MHA工作目录
[root@mysql-db04 mha]# mkdir /etc/mha/app1

MHA启动 前戏

#1.检测SSH
[root@mysql-db04 mha]# masterha_check_ssh --conf=/etc/mha/app1.cnf

 All SSH connection tests passed successfully.

#2.检测主从复制
[root@mysql-db04 mha]# masterha_check_repl --conf=/etc/mha/app1.cnf

MySQL Replication Health is OK.

#3.优化ssh
[root@mysql-db04 mha]# vim /etc/ssh/sshd_config 
UseDNS no

[root@mysql-db04 mha]# systemctl restart sshd

MHA 启动报错及解决方案

  • 反向解析


    image.png
# MySQL的ip被反向解析了，解决方法如下：跳过反向解析
[root@mysql-db01 ~]# vim /etc/my.cnf
[mysqld]
skip_name_resolve

  • 没有从库创建rep用于主从的用户


    image.png
# 从库上没有创建主从复制用户 ，解决方法如下：从库创建主从复制用户即可
#主库执行：mysql> grant replication slave on *.* to rep@'%' identified by '123';

  • 未安装node


    image.png
#未安装node
[root@mysql-db01 ~]# yum localinstall -y mha4mysql-node-0.56-0.el6.noarch.rpm

启动MHA

#启动命令
[root@mysql-db04 ~]# nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /etc/mha/app1/manager.log 2>&1 &

[root@mysql-db04 mha]# masterha_check_status --conf=/etc/mha/app1.cnf      # (启动慢时，重新检查)
app1 (pid:21017) is running(0:PING_OK), master:10.0.0.51

#停止命令
mastercha_stop --conf=/etc/mha/app1.cnf

MHA 集群恢复

#1.查看MHA日志，找到change master语句
[root@mysql-db04 mha]# grep -i 'change master to' /etc/mha/app1/manager.log 
Wed Feb 26 12:30:26 2020 - [info]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='10.0.0.52', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=120, MASTER_USER='rep', MASTER_PASSWORD='xxx';

#2.在down机的主库中，执行change master 语句
CHANGE MASTER TO MASTER_HOST='10.0.0.52', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=120, MASTER_USER='rep', MASTER_PASSWORD='123';

#3.开启主从复制
mysql> start slave;

#4.补全MHA的配置文件
[root@mysql-db04 mha]# vim /etc/mha/app1.cnf 
[server default]    
manager_log=/etc/mha/app1/manager.log
manager_workdir=/etc/mha/app1
master_binlog_dir=/application/mysql/data
password=mha
ping_interval=2
repl_password=123
repl_user=rep
ssh_port=22
ssh_user=root
user=mha

[server1]
hostname=10.0.0.51
port=3306

[server2]
hostname=10.0.0.52
port=3306

[server3]
hostname=10.0.0.53
port=3306

[server4]
hostname=10.0.0.54

#5.启动MHA
[root@mysql-db04 tmp]# nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /etc/mha/app1/manager.log 2>&1 &

#6.检查MHA启动
[root@mysql-db04 tmp]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:24508) is running(0:PING_OK), master:10.0.0.52



nohup &  ：放在后台启动
masterha_manager： MHA启动脚本
--conf：指定配置文件
--global_conf：指定全局配置文件
--remove_dead_master_conf：在MHA配置文件中，删除down机主库的server标签
--ignore_last_failover：忽略上一次切换，不生成锁文件


MHA工作机制：切换完一次之后，会在工作目录下生成一个锁文件，如果有锁文件，MHA在8小时之内不会做第二次切换

使用脚本自动恢复MHA集群

#提前写好用于覆盖的文件
[root@mysql-db06 /etc/mha]# cp app1.cnf app1.cnf.ori    #配置文件集群server必须是完整的

vim reslave.sh
#!/bin/bash

mha_log='/etc/mha/app1/manager.log'

change=$(grep -i 'change master to' ${mha_log}|sed -nr 's#^.*: (.*);$#\1#gp'|sed 's#xxx#123#g')

down_master=$(sed -nr 's#^Master (.*)\(.*\!$#\1#gp' ${mha_log})

ssh $down_master "/etc/init.d/mysqld start"
mysql -umha -pmha -h $down_master -e "${change};start slave;"
\cp /etc/mha/app1.cnf.ori /etc/mha/app1.cnf

使用MHA自带脚本管理vip

#1.修改配置文件
[root@mysql-db04 ~]# vim /etc/mha/app1.cnf
master_ip_failover_script=/etc/mha/app1/master_ip_failover

#2.手动绑定vip
[root@mysql-db04 app1]# ifconfig eth0:0 10.0.0.55/24

#3.上传提前修改好的脚本master_ip_failover放入/etc/mha/app1/目录下（格式已转换就无需下面的格式转换）

#4.授权执行权限
[root@mysql-db04 app1]# chmod +x master_ip_failover 

#5.转格式
yum install -y dos2unix
[root@mysql-db04 app1]# dos2unix master_ip_failover 
dos2unix: converting file master_ip_failover to Unix format ...

#5.加完vip脚本的配置后，MHA起不来的原因
1.脚本语法问题
2.脚本权限问题
3.脚本格式问题

--------------------------------------------测试环境-----------------------------------
#测试宕掉主库，查看是否从库切换，切换完成后服务是否断开（切换过程中会断开）
[root@mysql-db02 ~]# vim test.sh 
#!/bin/bash
num=1
while true;
do
  mysql -umha -pmha -h10.0.0.55 -e "insert into d1.t1 values($num);commit"
  let num++
  sleep 1
done
#-h 连接VIP的ip地址 -u -p 均为mha管理用户
image.png

MHA 防止断电断网，使用binlog-server

[root@mysql-db04 ~]# vim /etc/mha/app1.cnf
[binlog1]
no_master=1
hostname=10.0.0.53
master_binlog_dir=/data/mysql/binlog/
#如果是单独用一台机做实时备份的binlog的话，新机器需要安装mysql，node，做ssh-keygen -t dsa -P '' -f ~/.ssh/id_dsa >/dev/null 2>&1 并且和集群中机器互做免秘钥登录如：ssh-copy-id -i ~/.ssh/id_dsa.pub 10.0.0.51

[root@mysql-db04 ~]# nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /etc/mha/app1/manager.log 2>&1 &

#在指定的binlog服务器上，创建data目录
[root@mysql-db03 ~]# mkdir /data/mysql/binlog/ -p

#必须进入该目录
[root@mysql-db03 mysql]# cd /data/mysql/binlog/

#使用mysqlbinlog实时拉取binlog
[root@mysql-db03 binlog]#  mysqlbinlog  -R --host=10.0.0.55 --user=mha --password=mha --raw  --stop-never mysql-bin.000001 &

#每一次mha的主库切换，myslqbinlog拉取会退出。需重新开启

5.基于GTID的MHA

什么是GITD
传统的主从复制是事件类：主库执行一条语句，从库拉取一条并执行
GTID的主从复制是事务类：主库开启一个事务，提交一次commit从库才拉取。如果主库最后提交的rollack。从库不会去拉取。优点：节省磁盘空间。过滤无用事务，提升性能。

GTID的新特性

  • 支持多线程复制:事实上是针对每个database开启相应的独立线程,即每个库有一个单独的(sql thread).
  • 支持启用GTID,在配置主从复制,传统的方式里,你需要找到binlog和POS点,然后change master to指向.
    在mysql5.6里,无须再知道binlog和POS点,只需要知道master的IP/端口/账号密码即可,因为同步复制是自动的,MySQL通过内部机制GTID自动找点同步.
  • 基于Row复制只保存改变的列,大大节省Disk Space/Network resources和Memory usage
  • 支持把Master 和Slave的相关信息记录在Table中，原来是记录在文件里,记录在表里,增强可用性
  • 支持延迟复制

开启GTID做主从

#1.修改配置文件开启GTID
vim /etc/my.cnf
[mysqld]
gtid_mode=ON                    # 开启GTID
enforce_gtid_consistency    # GTID参数
log-slave-updates      # 从库更新binlog
log-bin=mysql-bin      # binlog名称


## log-slave-updates参数
更新从库的binlog，以下几种情况使用：
1.双主集群
2.级联复制
3.开启GTID

#检查是否成功开启
mysql> show variables like '%gtid%';
+---------------------------------+-----------+
| Variable_name                   | Value     |
+---------------------------------+-----------+
| binlog_gtid_simple_recovery     | OFF       |
| enforce_gtid_consistency        | ON        |
| gtid_executed                   |           |
| gtid_mode                       | ON        |
| gtid_next                       | AUTOMATIC |
| gtid_owned                      |           |
| gtid_purged                     |           |
| simplified_binlog_gtid_recovery | OFF       |
+---------------------------------+-----------+

#2.创建主从复制用户

#3.直接在从库上change master
change master to
master_host='10.0.0.51',
master_password='123',
master_user='rep',
master_auto_position=1;                    # 直接自动提取binlog完成复制

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000009 |    13491 |              |                  | 375e975e-5836-11ea-a767-000c2935c9e0:1-58 |
+------------------+----------+--------------+------------------+-------------------------------------------+

#前半段为主库的UUID后半段为已经同步了多少个事务
Retrieved_Gtid_Set: 375e975e-5836-11ea-a767-000c2935c9e0:1-47
Executed_Gtid_Set: 375e975e-5836-11ea-a767-000c2935c9e0:1-47

6.MySQL 读写分离中间件 Atlas

主要功能：

  • 1.读写分离
  • 2.从库负载均衡
  • 3.IP过滤
  • 4.自动分表
  • 5.DBA可平滑上下线DB
  • 6.自动摘除宕机的DB

安装Atlas

[root@mysql-db04 ~]# rpm -ivh Atlas-2.2.1.el6.x86_64.rpm 
Preparing...                          ################################# [100%]
Updating / installing...
   1:Atlas-2.2.1-1                    ################################# [100%]

配置Atlas

[root@mysql-db06 /usr/local]# cd /usr/local/mysql-proxy/
[root@mysql-db04 mysql-proxy]# ll
total 0
drwxr-xr-x 2 root root  75 Feb 27 13:10 bin     #命令存放目录
drwxr-xr-x 2 root root  22 Feb 27 13:10 conf    #配置文件存放目录
drwxr-xr-x 3 root root 331 Feb 27 13:10 lib     #库文件存放目录
drwxr-xr-x 2 root root   6 Dec 17  2014 log     #日志文件存放目录

-----------------------------------------------------------------------------------------------------------------------
[root@mysql-db04 conf]# vim test.cnf 
#完整配置
## 完整配置文件
[mysql-proxy]

#带#号的为非必需的配置项目

#管理接口的用户名
admin-username = user

#管理接口的密码
admin-password = pwd

#Atlas后端连接的MySQL主库的IP和端口，可设置多项，用逗号分隔
proxy-backend-addresses = 10.0.0.55:3306

#Atlas后端连接的MySQL从库的IP和端口，@后面的数字代表权重，用来作负载均衡，若省略则默认为1，可设置多项，用逗号分隔
proxy-read-only-backend-addresses = 10.0.0.52:3306,10.0.0.53:3306,10.0.0.54:3306

#用户名与其对应的加密过的MySQL密码，密码使用PREFIX/bin目录下的加密程序encrypt加密，下行的user1和user2为示例，将其替换为你的MySQL的用户名和加密密码！
pwds = mha:O2jBXONX098=,root:3yb5jEku5h4=      # 使用自带脚本直接写入密码

#设置Atlas的运行方式，设为true时为守护进程方式，设为false时为前台方式，一般开发调试时设为false，线上运行时设为true,true后面不能有空格。
daemon = true

#设置Atlas的运行方式，设为true时Atlas会启动两个进程，一个为monitor，一个为worker，monitor在worker意外退出后会自动将其重启，设为false时只有worker，没有monitor，一般开发调试时设为false，线上运行时设为true,true后面不能有空格。
keepalive = true

#工作线程数，对Atlas的性能有很大影响，可根据情况适当设置
event-threads = 8

#日志级别，分为message、warning、critical、error、debug五个级别
log-level = error

#日志存放的路径
log-path = /usr/local/mysql-proxy/log

#SQL日志的开关，可设置为OFF、ON、REALTIME，OFF代表不记录SQL日志，ON代表记录SQL日志，REALTIME代表记录SQL日志且实时写入磁盘，默认为OFF
sql-log = ON

#慢日志输出设置。当设置了该参数时，则日志只输出执行时间超过sql-log-slow（单位：ms)的日志记录。不设置该参数则输出全部日志。
sql-log-slow = 10

#实例名称，用于同一台机器上多个Atlas实例间的区分
#instance = test

#Atlas监听的工作接口IP和端口
proxy-address = 0.0.0.0:3307

#Atlas监听的管理接口IP和端口
admin-address = 0.0.0.0:2345

#分表设置，此例中person为库名，mt为表名，id为分表字段，3为子表数量，可设置多项，以逗号分隔，若不分表则不需要设置该项
#tables = person.mt.id.3

#默认字符集，设置该项后客户端不再需要执行SET NAMES语句
charset = utf8

#允许连接Atlas的客户端的IP，可以是精确IP，也可以是IP段，以逗号分隔，若不设置该项则允许所有IP连接，否则只允许列表中的IP连接
#client-ips = 127.0.0.1, 192.168.1

#Atlas前面挂接的LVS的物理网卡的IP(注意不是虚IP)，若有LVS且设置了client-ips则此项必须设置，否则可以不设置
#lvs-ips = 192.168.1.1

#启动atlas
[root@mysql-db04 conf]# /usr/local/mysql-proxy/bin/mysql-proxyd test start

使用altas

对于atlas我们只需要学会使用他的管理接口

# 连接管理接口
[root@mysql-db04 conf]# mysql -uuser -ppwd -h127.0.0.1 -P2345

#查看帮助
mysql> SELECT * FROM help;
#查看后端
mysql> SELECT * FROM backends;
+-------------+----------------+-------+------+
| backend_ndx | address        | state | type |
+-------------+----------------+-------+------+
|           1 | 10.0.0.55:3306 | up    | rw   |
|           2 | 10.0.0.52:3306 | up    | ro   |
|           3 | 10.0.0.53:3306 | up    | ro   |
|           4 | 10.0.0.54:3306 | up    | ro   |
+-------------+----------------+-------+------+

user@127.0.0.1> select * from help;
+----------------------------+---------------------------------------------------------+
| command                    | description                                             |
+----------------------------+---------------------------------------------------------+
| SELECT * FROM help         | 查看help帮助
| SELECT * FROM backends     | 查看后端代理的数据库                     
| SET OFFLINE $backend_id    | 平滑下线数据库，例：SET OFFLINE 1; 加后端ID
| SET ONLINE $backend_id     | 平滑上线数据库，例：ADD MASTER 1; 加后端ID
| ADD MASTER $backend        | 添加一个主库，例：ADD MASTER 172.16.1.56:3306;
| ADD SLAVE $backend         | 添加一个从库，例：ADD SLAVE 172.168.1.57:3306;
| REMOVE BACKEND $backend_id | 删除一个后端的库：例：REMOVE BACKEND 1; 加后端ID
| SELECT * FROM clients      | 查看可连接管理接口客户端
| ADD CLIENT $client         | 添加一个客户端，例：ADD CLIENT 10.0.0.52;
| REMOVE CLIENT $client      | 删除一个客户端，例：REMOVE CLIENT 10.0.0.52;
| SELECT * FROM pwds         | 查看Atlas可连接用户
| ADD PWD $pwd               | 添加用户(密码会自动加密)例：ADD PWD mha:mha;
| ADD ENPWD $pwd             | 添加用户(需要加密后的密码)例：ADD ENPWD mha:O2jBXONX098=
| REMOVE PWD $pwd            | 删除用户，例：REMOVE PWD mha;
| SAVE CONFIG                | 保存到配置文件
| SELECT VERSION             | 查看版本信息
+----------------------------+---------------------------------------------------------+
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。
禁止转载，如需转载请通过简信或评论联系作者。

推荐阅读更多精彩内容