MHA cluster building

The beginning

This article mainly introduces how to build a MHA cluster, hoping to help you.

Basic environment introduction Linux:CentOS 7.4MySQL binlog server 5.7.21 + traditional replication (enable GTID without building binlog server) VIP:192.168.68.100 role SQL address hostname server_id type Master192.168.68.161server11413306 write Candicate master192.168.68.162server21423306 read Manager/Slave192.168.68.163server31433306 read / monitor / manage server1 master-slave replication build # create replication user grant replication slave on *. * to 'repl'@'192.168.68.%' identified by' repl4slave';flush privileges # create a monitoring user grant all privileges on *. * to 'xmm'@'192.168.68.%' identified by' xmmxmm';flush privileges MHA installation # Software installation yum localinstall-y mha4mysql-node-0.56-0.el6.noarch#SSH login check ssh server2ssh server3 if it is not a new master#master exported data mysqldump-- master-data=2-- single-transaction-R-- triggers-A > server1-all-180329.sqlVIP manual binding: / sbin/ip addr add 192.168.68.100 dev ens32 32 dev ens32 unbind: / sbin/ip addr del 192.168.68.100 dev ens32 server2 master-slave Copy scaffolding # configuration and enable replication change master to master_host='192.168.68.161' Master_port=3306,master_user='repl',master_password='repl4slave',master_auto_position=1 Start slave;#slave opens read_onlymysql-h227.0.0.1-p-e 'set global read_only=1'mysql-h227.0.0.1-p-e' set global relay_log_purge=0'MHA installation # software installation yum localinstall-y mha4mysql-node-0.56-0.el6.noarch#SSH login check ssh server1ssh server3 if master is not the new # imported data mysql- h227.0.0.1-uroot-p

< /tmp/data/server1-all-180329.sqlserver3主从复制搭建#导入数据、配置和开启复制mysql -h227.0.0.1 -uroot -p < /tmp/data/server1-all-180329.sqlchange master to master_host='192.168.68.135',master_port=3306,master_user='repl',master_password='repl4slave',master_auto_position=1;start slave;#slave开启read_onlymysql -h227.0.0.1 -p -e 'set global read_only=1'mysql -h227.0.0.1 -p -e 'set global relay_log_purge=0'MHA安装#软件安装yum localinstall -y mha4mysql-node-0.56-0.el6.noarchyum localinstall -y mha4mysql-manager-0.56-0.el6.noarch#SSH无密钥验证登陆ssh-keygencd ~/.ssh/cat id_rsa.pub >

< $running_time_threshold ); next if ( defined($command) && $command eq "Binlog Dump" ); next if ( defined($user) && $user eq "system user" ); next if ( defined($command) && $command eq "Sleep" && defined($query_time) && $query_time >

= 1); if ($type > = 1) {next if (defined ($command) & & $command eq "Sleep"); next if (defined ($command) & & $command eq "Connect");} if ($type > = 2) {next if (defined ($info) & & $info = ~ m / ^ select / I); next if (defined ($info) & $info = ~ m / ^ show / I);} push @ threads, $ref;} return @ threads } sub main {if ($command eq "stop") {# Gracefully killing connections on the current master # 1. Set read_only= 1 on the new master # 2. DROP USER so that no app user can establish new connections # 3. Set read_only= 1 on the current master # 4. Kill current queries # * Any database access failure will result in script die. My $exit_code = 1; eval {# Setting read_only=1 on the new master (to avoid accident) my $new_master_handler = new MHA::DBHelper (); # args: hostname, port, user, password, raise_error (die_on_error) _ or_not $new_master_handler- > connect ($new_master_ip, $new_master_port, $new_master_user, $new_master_password, 1); print current_time_us (). "Set read_only on the new master.. "; $new_master_handler- > enable_read_only (); if ($new_master_handler- > is_read_only ()) {print" ok.\ n ";} else {die" Failed!\ n ";} $new_master_handler- > disconnect (); # Connecting to the orig master, die if any database error happens my $orig_master_handler = new MHA::DBHelper () $orig_master_handler- > connect ($orig_master_ip, $orig_master_port, $orig_master_user, $orig_master_password, 1); # # Drop application user so that nobody can connect. Disabling per-session binlog beforehand $orig_master_handler- > disable_log_bin_local (); # print current_time_us (). "Drpping app user on the orig master..\ n"; print current_time_us () "drop vip $vip..\ n"; # drop_app_user ($orig_master_handler); & drop_vip (); # # Waiting for N * 100 milliseconds so that current connections can exit my $time_until_read_only = 15; $_ tstart = [gettimeofday]; my @ threads = get_threads_util ($orig_master_handler- > {dbh}, $orig_master_handler- > {connection_id}) While ($time_until_read_only > 0 & & $# threads > = 0) {if ($time_until_read_only% 5 = = 0) {printf "% s Waiting all running% d threads are disconnected.. (max% d milliseconds)\ n ", current_time_us (), $# threads + 1, $time_until_read_only * 100; if ($# threads

< 5 ) { print Data::Dumper->

New ([$_])-> Indent (0)-> Terse (1)-> Dump. "\ n" foreach (@ threads);}} sleep_until (); $_ tstart = [gettimeofday]; $time_until_read_only--; @ threads = get_threads_util ($orig_master_handler- > {dbh}, $orig_master_handler- > {connection_id}) Setting read_only=1 on the current master so that nobody (except SUPER) can write print current_time_us (). "Set read_only=1 on the orig master.. "; $orig_master_handler- > enable_read_only (); if ($orig_master_handler- > is_read_only ()) {print" ok.\ n ";} else {die" Failed!\ n ";} # # Waiting for M * 100 milliseconds so that current update queries can complete my $time_until_kill_threads = 5 Threads = get_threads_util ($orig_master_handler- > {dbh}, $orig_master_handler- > {connection_id}); while ($time_until_kill_threads > 0 & & $# threads > = 0) {if ($time_until_kill_threads% 5 = = 0) {printf "% s Waiting all running% d queries are disconnected.. (max% d milliseconds)\ n ", current_time_us (), $# threads + 1, $time_until_kill_threads * 100; if ($# threads

< 5 ) { print Data::Dumper->