MySQL高可用架构MHA搭建和测试.docx-资料库

qmkakaxi-12669987-4744300845405675190.docx.pdf-第1页.png

第1页 / 共60页

qmkakaxi-12669987-4744300845405675190.docx.pdf-第2页.png

第2页 / 共60页

qmkakaxi-12669987-4744300845405675190.docx.pdf-第3页.png

第3页 / 共60页

qmkakaxi-12669987-4744300845405675190.docx.pdf-第4页.png

第4页 / 共60页

qmkakaxi-12669987-4744300845405675190.docx.pdf-第5页.png

第5页 / 共60页

qmkakaxi-12669987-4744300845405675190.docx.pdf-第6页.png

第6页 / 共60页

qmkakaxi-12669987-4744300845405675190.docx.pdf-第7页.png

第7页 / 共60页

qmkakaxi-12669987-4744300845405675190.docx.pdf-第8页.png

第8页 / 共60页

MySQL 高可用架构 MHA 搭建和测试目录 MySQL 高可用架构 MHA 原理与搭建 ........................................................... 错误！未定义书签。 MHA 简介................................................................................................................................... 1 基本环境 .................................................................................................................................... 3 MHA 安装配置........................................................................................................................... 3 Mysql 数据库安装 ............................................................................................................. 3 主从环境的搭建.............................................................................................................. 14 安装 MHA node ................................................................................................................15 安装 MHA manager..........................................................................................................16 配置节点互信 ................................................................................................................. 17 配置 MHA ......................................................................................................................... 18 检查状态 .......................................................................................................................... 20 启停 MHA manager 监控 .................................................................................................24 创建自动清除 relay log 脚本 .......................................................................................... 24 使用脚本引入 VIP ............................................................................................................26 实验验证 .................................................................................................................................. 28 1 测试自动切换 .............................................................................................................. 28 2 手动 failover ................................................................................................................ 38 3 在线切换 ...................................................................................................................... 46 4 修复宕机 master..........................................................................................................57 开启半同步复制...................................................................................................................... 59 MHA 简介 MHA（Master High Availability）目前在 MySQL 高可用方面是一个相对成熟的解决方案，它由日本 DeNA 公司 youshimaton（现就职于 Facebook 公司）开发，是一套优秀的作为 MySQL 高可用性环境下故障切换和主从提升的高可用软件。在 MySQL 故障切换过程中，MHA 能做到在 0~30 秒之内自动完成数据库的故障切换操作，并且在进行故障切换的过程中，MHA 能在最大程度上保证数据的一致性，以达到真正意义上的高可用。该软件由两部分组成：MHA Manager（管理节点）和 MHA Node（数据节点）。MHA Manager 可以单独部署在一台独立的机器上管理多个 master-slave 集群，也可以部署在一台 slave 节点上。MHA Node 运行在每台 MySQL 服务器上，MHA Manager 会定时探测集群中的 master 节点，当 master 出现故障时，它可以自动将最新数据的 slave 提升为新的 master，然后将

所有其他的 slave 重新指向新的 master。整个故障转移过程对应用程序完全透明。在 MHA 自动故障切换过程中，MHA 试图从宕机的主服务器上保存二进制日志，最大程度的保证数据的不丢失，但这并不总是可行的。例如，如果主服务器硬件故障或无法通过 ssh 访问，MHA 没法保存二进制日志，只进行故障转移而丢失了最新的数据。使用 MySQL 5.5 的半同步复制，可以大大降低数据丢失的风险。MHA 可以与半同步复制结合起来。如果只有一个 slave 已经收到了最新的二进制日志，MHA 可以将最新的二进制日志应用于其他所有的 slave 服务器上，因此可以保证所有节点的数据一致性。目前 MHA 主要支持一主多从的架构，要搭建 MHA,要求一个复制集群中必须最少有三台数据库服务器，一主二从，即一台充当 master，一台充当备用 master，另外一台充当从库，因为至少需要三台服务器，出于机器成本的考虑，淘宝也在该基础上进行了改造，目前淘宝 TMHA 已经支持一主一从。（出自：《深入浅出 MySQL(第二版)》） MHA 架构： MHA 工作原理总结为以下几条：

（1）从宕机崩溃的 master 保存二进制日志事件（binlog events）; （2）识别含有最新更新的 slave; （3）应用差异的中继日志(relay log) 到其他 slave; （4）应用从 master 保存的二进制日志事件(binlog events); （5）提升一个 slave 为新 master; （6）使用其他的 slave 连接新的 master 进行复制。 Git 介绍：https://github.com/yoshinorim/mha4mysql-manager/wiki 基本环境  操作系统 Red Hat Enterprise Linux Server release 7.4  数据库版本 5.7.21-log MySQL Community Server (GPL)  MHA 版本 0.58  虚拟 ip 10.68.28.62  因资源有限，将 MHA manager 安装在了 MySQL slave 节点上。MHA Manager 可以单独部署在一台独立的机器上管理多个 master-slave 集群，也可以部署在一台 slave 节点上服务器信息 mha 角色 MHA manager master candidate master slave 内网 ip 10.68.25.57 10.68.25.56 10.68.25.57 10.68.25.59 业务网 ip 10.68.28.57 10.68.28.56 10.68.28.57 10.68.28.59 主机名业务网卡功能 cdht7 cdht8 cdht10 ens224 ens224 ens224 监控复制组接受写请求接受读请求接受读请求涉及的主要配置文件和脚本所在路径: /etc/mha/app1.cnf MHA 配置文件 /usr/local/bin/master_ip_failover 自动故障转移脚本分为 keepalived 引入 VIP 和脚本引入 VIP /usr/local/bin/master_ip_online_change 手动故障转移脚本 /u01/mha/purge_relay_log.sh 自动清除 relay log 脚本 MHA 安装配置 Mysql 数据库安装上传 mysql-5.7.21-linux-glibc2.12-x86_64.tar.gz 到/opt 目录，执行下面命令：

tar -zvf mysql-5.7.21-linux-glibc2.12-x86_64.tar.gz mkdir -p /u01/data mv mysql-5.7.21-linux-glibc2.12-x86_64 /u01/mysql groupadd mysql useradd -r -s /sbin/nologin -g mysql mysql -d /usr/local/mysql usermod mysql -d /u01/mysql cd /u01/ chown -R mysql:mysql data/ mysql/ cd /u01/mysql/ bin/mysqld --initialize --user=mysql --basedir=/u01/mysql --datadir=/u01/data bin/mysql_ssl_rsa_setup --datadir=/u01/data cd /u01/mysql/support-files/ cp my-default.cnf /etc/my.cnf cp mysql.server /etc/init.d/mysql mkdir -p /u01/mysql/log touch /u01/mysql/log/mysql.log chown -R mysql:mysql /u01/data/ /u01/mysql/ /etc/init.d/mysqld start 配置文件内容如下： /etc/my.cnf 文件 [root@cdht7 ~]# cat /etc/my.cnf [mysqld] basedir = /u01/mysql datadir = /u01/data port = 3306 socket = /tmp/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Settings user and group are ignored when systemd is used. # If you need to run mysqld under a different user or group, # customize your systemd unit file for mariadb according to the lower_case_table_names = 1 # instructions in http://fedoraproject.org/wiki/Systemd max_allowed_packet=256M sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES sort_buffer_size = 16777216 key_cache_block_size = 2048 query_cache_size = 4194304 read_buffer_size = 262144 read_rnd_buffer_size slave_pending_jobs_size_max = 33554432 = 524288

sort_buffer_size = 524288 thread_cache_size = 18 max_connections = 500 max_allowed_packet = 41943040 wait_timeout=31536000 interactive_timeout=31536000 wait_timeout = 300 skip-name-resolve #skip-grant-tables max_connections=1000 max_connect_errors=200 log-bin=mysql-bin server-id=56 expire_logs_days=7 slow-query-log = ON long_query_time = 10 #慢查询开关 [mysqld_safe] log-error=/u01/mysql/log/mysql.log pid-file=/u01/mysql/tmp/mysql.pid # # include all files from the config directory # !includedir /etc/my.cnf.d /etc/init.d/mysqld 文件 [root@cdht7 ~]# cat /etc/init.d/mysqld #!/bin/sh # Copyright Abandoned 1996 TCX DataKonsult AB & Monty Program KB & Detron HB # This file is public domain and comes with NO WARRANTY of any kind # MySQL daemon start/stop script. # Usually this is put in /etc/init.d (at least on machines SYSV R4 based # systems) and linked to /etc/rc3.d/S99mysql and /etc/rc0.d/K01mysql. # When this is done the mysql server will be started when the machine is # started and shut down when the systems goes down. # Comments to support chkconfig on RedHat Linux # chkconfig: 2345 64 36

# description: A very fast and reliable SQL database engine. # Comments to support LSB init script conventions ### BEGIN INIT INFO # Provides: mysql # Required-Start: $local_fs $network $remote_fs # Should-Start: ypbind nscd ldap ntpd xntpd # Required-Stop: $local_fs $network $remote_fs # Default-Start: 2 3 4 5 # Default-Stop: 0 1 6 # Short-Description: start and stop MySQL # Description: MySQL is a very fast and reliable SQL database engine. ### END INIT INFO [mysqld] basedir= # If you install MySQL on some other places than /usr/local/mysql, then you # have to do one of the following things for this script to work: # # - Run this script from within the MySQL installation directory # - Create a /etc/my.cnf file with the following information: # # # - Add the above to any other configuration file (for example ~/.my.ini) # # - Add the path to the mysql-installation-directory to the basedir variable # # # If you want to affect other MySQL variables, you should make your changes # in the /etc/my.cnf, ~/.my.cnf or other MySQL configuration files. and copy my_print_defaults to /usr/bin below. # If you change base dir, you must also change datadir. These may get # overwritten by settings in the MySQL configuration files. basedir=/u01/mysql datadir=/u01/data # Default value, in seconds, afterwhich the script should timeout waiting # for server start. # Value here is overriden by value in my.cnf. # 0 means don't wait at all # Negative numbers mean to wait indefinitely service_startup_timeout=900 # Lock directory for RedHat / SuSE. lockdir='/var/lock/subsys'

lock_file_path="$lockdir/mysql" # The following variables are only set for letting mysql.server find things. # Set some defaults mysqld_pid_file_path= if test -z "$basedir" then basedir=/u01/mysql bindir=/u01/mysql/bin if test -z "$datadir" then datadir=/u01/data fi sbindir=/u01/mysql/bin libexecdir=/u01/mysql/bin else bindir="$basedir/bin" if test -z "$datadir" then datadir="$basedir/data" fi sbindir="$basedir/sbin" libexecdir="$basedir/libexec" fi # datadir_set is used to determine if datadir was set (and so should be # *not* set inside of the --basedir= handler.) datadir_set= # # Use LSB init script functions for printing messages, if possible # lsb_functions="/lib/lsb/init-functions" if test -f $lsb_functions ; then . $lsb_functions else log_success_msg() { echo " SUCCESS! $@" } log_failure_msg() { echo " ERROR! $@"

} fi PATH="/sbin:/usr/sbin:/bin:/usr/bin:$basedir/bin" export PATH mode=$1 # start or stop [ $# -ge 1 ] && shift other_args="$*" # uncommon, but needed when called from an RPM upgrade action # Expected: "--skip-networking --skip-grant-tables" # They are not checked here, intentionally, as it is the resposibility # of the "spec" file author to give correct arguments only. case `echo "testing\c"`,`echo -n testing` in ;; ;; echo_c='\c' ;; echo_c= echo_n=-n echo_c= echo_n= *c*,-n*) echo_n= *c*,*) *) esac parse_server_arguments() { for arg do case "$arg" in --basedir=*) basedir=`echo "$arg" | sed -e 's/^[^=]*=//'` bindir="$basedir/bin" if test -z "$datadir_set"; then datadir="$basedir/data" fi sbindir="$basedir/sbin" libexecdir="$basedir/libexec" ;; --datadir=*) datadir=`echo "$arg" | sed -e 's/^[^=]*=//'` datadir_set=1 ;; --pid-file=*) mysqld_pid_file_path=`echo "$arg" | sed -e 's/^[^=]*=//'` ;; --service-startup-timeout=*) "$arg" service_startup_timeout=`echo | sed -e 's/^[^=]*=//'` ;; esac done } wait_for_pid () {

资料库

MySQL高可用架构MHA搭建和测试.docx

相关推荐

数据库

热门标签

最新资料