MYSQL实战-MHA搭建及问题概括
发布时间:2022-03-25 18:39:08 所属栏目:MySql教程 来源:互联网
导读:MYSQL实战-MHA搭建及问题总结: 1.服务器 192.168.56.21 mydb1 #Master 192.168.56.22 mydb2 #Slave 192.168.56.23 mydb3 #MHA manager 2. 所有机器修改vim /etc/hosts 192.168.56.21 mydb1 192.168.56.22 mydb2 192.168.56.23 mydb3 3.设置SSH公钥免密码
MYSQL实战-MHA搭建及问题总结: 1.服务器 192.168.56.21 mydb1 #Master 192.168.56.22 mydb2 #Slave 192.168.56.23 mydb3 #MHA manager 2. 所有机器修改vim /etc/hosts 192.168.56.21 mydb1 192.168.56.22 mydb2 192.168.56.23 mydb3 3.设置SSH公钥免密码登录 #root用户操作(/root),pwd mkdir ~/.ssh chmod 700 ~/.ssh cd ~/.ssh ssh-keygen -t rsa #一直回车 ls -al cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys ls -al chmod 600 ~/.ssh/authorized_keys cd .. scp -r .ssh 192.168.56.22:/root/ scp -r .ssh 192.168.56.23:/root/ ssh mydb1 ssh mydb2 ssh mydb3 4.下载mha4mysql-manager和mha4mysql-node https://github.com/yoshinorim/mha4mysql-manager https://github.com/yoshinorim/mha4mysql-node 5.在三个节点(node 和 manager)安装perl-DBD-MySQL,用光盘作yum源,#包括了3个包:Perl-DBD-MySQL,mysql-libs,perl-DBI yum install perl-DBD-MySQL yum install perl-DBI yum install mysql-libs 6.在三个节点安装node(包括管理节点) yum -y install perl-CPAN 下载perl需要的依赖包库 perl -MCPAN -e "install inc::Module::Install 在依赖包库中,查找和安装Makefile.PL需要的包,具体程序是 use inc::Module::Install; 参考https://perlmaven.com/cant-locate-inc-module-install-in-inc 上面实际下载的是Module-Install-1.18.tar.gz 这个包 //perl -MCPAN -e shell //perl -MCPAN -e "install Module::Depends" 参考 http://cache.baiducontent.com/c?m=9f65cb4a8c8507ed4fece7631046893b4c4380147d8c8c4668d4e419ce3b4c413037bfa6663f405a8e906b6075ab4f5bbdf06270670123b598c8d40895ac925f75ce786a6459db0144dc42f39a5125b07fcd0caef14ef0ba8169cfef9480810a44cb23127af7e78a2a424bdd6f814f6df4a6e85f135d07bb9d6b39ae&p=8b2a971cc89401ff57ee967c525285&newp=80769a478a8702c308e2977c0f43cc231610db2151d7d0106b82c825d7331b001c3bbfb423241a05d8ce7c6d04aa4b5feaf4357534072ba3dda5c91d9fb4c57479d463771c&user=baidu&fm=sc&query=inc/Module/Install%2Epm+in+%40INC&qid=e9eec041000013a8&p1=10 tar xzvf mha4mysql-node-0.56.tar.gz cd mha4mysql-node-0.56 perl Makefile.PL 注意 在安装perl install模块后,这里终于可以正常执行了,在执行PL后,系统会验证哪些包missing,这时就可以根据他的提示安装少的包了 make && make install 7.在管理节点安装manager //可以先查看本机已安的包名 rpm -qa perl-Config-Tiny perl-Params-Validate perl-MIME-Types perl-Email-Date-Format perl-Mail-Sender perl-Mail-Sendmail perl-TimeDate perl-MailTools perl-MIME-Lite perl-Log-Dispatch perl-Parallel-ForkManager 共11个包 rpm -qa perl-Config-Tiny perl-Params-Validate perl-MIME-Types perl-Email-Date-Format perl-Mail-Sender perl-Mail-Sendmail perl-TimeDate perl-MailTools perl-MIME-Lite perl-Log-Dispatch perl-Parallel-ForkManager | xargs rpm -ivh 上面命令直接安装 rpm -ivh perl-Config-Tiny-2.12-1.el5.rf.noarch.rpm rpm -ivh perl-Params-Validate-0.95-1.el5.rf.i386.rpm rpm -ivh perl-MIME-Types-1.28-2.el6.noarch.rpm rpm -ivh perl-Email-Date-Format-1.002-5.el6.noarch.rpm rpm -ivh perl-Mail-Sender-0.8.16-3.el6.noarch.rpm rpm -ivh perl-Mail-Sendmail-0.79-12.el6.noarch.rpm rpm -ivh perl-TimeDate-1.16-11.1.el6.noarch.rpm rpm -ivh perl-MailTools-2.04-4.el6.noarch.rpm rpm -ivh perl-MIME-Lite-3.027-2.el6.noarch.rpm rpm -ivh perl-Log-Dispatch-2.26-1.el5.rf.noarch.rpm rpm -ivh perl-Parallel-ForkManager-0.7.5-2.2.el5.rf.noarch.rpm 如果上面包都没有,直接从yum源下载并安装 yum install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes -y yum install perl-Time-HiRes #光盘yum源 tar xzvf mha4mysql-manager-0.56.tar.gz cd mha4mysql-manager-0.56 perl Makefile.PL make && make install 8 配置主从同步GTID方式 这里用的是init.sh脚本,能自动搭建主从GTID服务器,且在一台机器上。当机器上没有mysql用户时,此脚本有些问题,在脚本初始化后要 做的修改是 show grants for 'rpl_user@127.0.0.1'; 主库上建立repl用户 grant replication slave on *.* to rpl_user@% identified by '1234'; 从库上去掉多余的rpl_user,注意要先去掉权限才能删除这个用户 revoke replication slave on *.* from rpl_user@127.0.0.1 revoke replication slave on *.* from 'rpl_user'@'%'; drop user 'rpl_user'@'%'; 9修改root用户默认密码 安装mysql开始 root用户没有密码,怕影响mha,所以建立root用户密码 use mysql; mysql> (老版本)update user set password=password("123456") where user="root"; (5.7.11)update user set authentication_string=password("123456") where user="root"; flush privileges; ln -s /usr/local/mysql_5.7/bin/mysql /usr/bin //软链接调用mysql命令 ####################常用命令############### /usr/local/bin/masterha_check_ssh --conf=/u01/mha/etc/app.cnf /usr/local/bin/masterha_check_repl --conf=/u01/mha/etc/app.cnf /usr/local/bin/masterha_manager --conf=/u01/mha/etc/app.cnf & /u01/mha/etc/masterha_check_status --conf=/u01/mha/etc/app.cnf /usr/local/bin/masterha_stop --conf=/u01/mha/etc/app.cnf masterha_check_ssh --conf=/u01/mha/etc/app.cnf //监控ssh是否正常 masterha_check_repl --conf=/u01/mha/etc/app.cnf //监控主从复制是否正常 MHA masterha_check_repl 检测过程 ###################切换###################### 一、failover故障切换 1.模拟主库宕机 [root@mydb3~]# ssh mydb1 "killall -r mysqld" 2.查看管理节点日志,可以看到VIP已经漂移 [root@mydb3 ~]# cat /u01/mha/log/manager.log |grep -i vip 3. 验证VIP是否位于节点mydb2 [root@mydb3 ~]# ssh mydb2 "ifconfig |grep 56.201 -B1" 4. 查看管理节点MHA切换日志 [root@mydb3 ~]# tail /u01/mha/log/manager.log 5. new master(old slave) show master statusG 6. new slave(old:master) (1)打开MySQL mysqld_safe --defaults-file=/u01/my3306/my.cnf & (2)检查数据库 show master statusG show slave statusG (3)在管理节点日志中查主库的日志文件和位置 cat /u01/mha/etc/app/manager.log |grep -i change (4)在slave连接master CHANGE MASTER TO MASTER_HOST='192.168.56.21', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=120, MASTER_USER='rep', MASTER_PASSWORD='rep123'; 7.启动管理节点 /usr/local/bin/masterha_manager --conf=/u01/mha/etc/app.cnf & /usr/local/bin/masterha_manager --conf=/u01/mha/etc/app.cnf --ignore_last_failover & 二、switchover线上切换 1.master:关闭event_scheduler(即192.168.56.21) set global event_scheduler=off; 2.manager:关闭管理进程 (即192.168.56.23) /usr/local/bin/masterha_stop --conf=/u01/mha/etc/app.cnf 3.manager:检查配置文件 /u01/mha/etc/app.cnf 有没有被修改破坏。如果破坏需要重新编辑正确配置文件:/u01/mha/etc/app.cnf cp /u01/mha/etc/app.cnf.bak /u01/mha/etc/app.cnf 4.开始切换: /u01/mha/etc/masterha_master_switch --master_state=alive --conf=/u01/mha/etc/app.cnf 5.new master(old slave) mysql> show master statusG *************************** 1. row *************************** File: binlog.000021 Position: 299 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec) 6.new slave(old master) CHANGE MASTER TO MASTER_HOST='192.168.56.22', MASTER_PORT=3306, MASTER_LOG_FILE='binlog.000021', MASTER_LOG_POS=299, MASTER_USER='rep', MASTER_PASSWORD='rep123'; mysql> start slave; Query OK, 0 rows affected (0.01 sec) mysql> show slave statusG *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.56.12 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: binlog.000017 Read_Master_Log_Pos: 299 Relay_Log_File: relaylog.000002 Relay_Log_Pos: 280 Relay_Master_Log_File: binlog.000017 Slave_IO_Running: Yes Slave_SQL_Running: Yes 7.启动管理节点 /u01/mha/etc/masterha_manager --conf=/u01/mha/etc/app.cnf & /u01/mha/etc/masterha_manager --conf=/u01/mha/etc/app.cnf --remove_dead_master_conf --ignore_last_failover https://www.cnblogs.com/zhoujinyi/p/3808673.html #################配置文件################### vim /u01/mha/etc/app.cnf [server default] user = root password = 123456 ssh_user = root repl_user = rpl_user repl_password = rpl_pass ping_interval = 1 ping_type = SELECT manager_workdir=/u01/mha/etc/app manager_log=/u01/mha/log/manager.log remote_workdir=/u01/mha/etc/app master_binlog_dir="/u01/mysql_5.7/s1/log/binlog" #master_ip_failover_script="/u01/mha/etc/master_ip_failover" #master_ip_online_change_script="/u01/mha/etc/master_ip_failover" shutdown_script="" report_script="" #check_repl_delay=0 [server1] hostname=iZ2zehy7gff0kpg1swp1czZ port=33091 master_binlog_dir="/apps/dbdat/mysql5_data33091/log" candidate_master=1 ignore_fail=1 [server2] hostname=iZ2zehy7gff0kpg1swp1czZ port=33092 master_binlog_dir="/u01/mysql_5.7/s2/log/binlog" candidate_master=1 ignore_fail=1 --------------------------------------------------------------------------------- vim /u01/mha/etc/master_ip_failover #!/usr/bin/env perl use strict; use warnings FATAL => 'all'; use Getopt::Long; my ( $command, $ssh_user, $orig_master_host, $orig_master_ip, $orig_master_port, $new_master_host, $new_master_ip, $new_master_port ); my $vip = '192.168.56.201/24'; # Virtual IP my $key = "1"; my $int = "eth0"; my $ssh_start_vip = "/sbin/ifconfig $int:$key $vip"; my $ssh_stop_vip = "/sbin/ifconfig $int:$key down"; my $arp_effect = "/sbin/arping -Uq -s192.168.56.201 -I $int 192.168.56.1 -c 3"; # Virtual IP and gat eway #my $test = "echo successfull >/tmp/test.txt"; $ssh_user = "root"; GetOptions( 'command=s' => $command, 'ssh_user=s' => $ssh_user, 'orig_master_host=s' => $orig_master_host, 'orig_master_ip=s' => $orig_master_ip, 'orig_master_port=i' => $orig_master_port, 'new_master_host=s' => $new_master_host, 'new_master_ip=s' => $new_master_ip, 'new_master_port=i' => $new_master_port, ); exit &main(); sub main { print "nnIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===nn"; if ( $command eq "stop" || $command eq "stopssh" ) { # $orig_master_host, $orig_master_ip, $orig_master_port are passed. # If you manage master ip address at global catalog database, # invalidate orig_master_ip here. my $exit_code = 1; eval { print "Disabling the VIP on old master: $orig_master_host n"; &stop_vip(); $exit_code = 0; }; if ($@) { warn "Got Error: $@n"; exit $exit_code; } exit $exit_code; } elsif ( $command eq "start" ) { # all arguments are passed. # If you manage master ip address at global catalog database, # activate new_master_ip here. # You can also grant write access (create user, set read_only=0, etc) here. my $exit_code = 10; eval { print "Enabling the VIP - $vip on the new master - $new_master_host n"; &start_vip(); $exit_code = 0; }; if ($@) { warn $@; exit $exit_code; } exit $exit_code; } elsif ( $command eq "status" ) { print "Checking the Status of the script.. OK n"; #`ssh $ssh_user@cluster1 " $ssh_start_vip "`; &status(); exit 0; } else { &usage(); exit 1; } } # A simple system call that enable the VIP on the new master sub start_vip() { `ssh $ssh_user@$new_master_host " $ssh_start_vip "`; `ssh $ssh_user@$new_master_host " $arp_effect "`; # `ssh $ssh_user@$new_master_host " $test "`; } # A simple system call that disable the VIP on the old_master sub stop_vip() { `ssh $ssh_user@$orig_master_host " $ssh_stop_vip "`; } sub status() { print `ssh $ssh_user@$orig_master_host " ip add show $int "`; } sub usage { "Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_maste r_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=portn"; } (编辑:大连站长网) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |
站长推荐