Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to build CentOS7 MySQL5.7 high availability, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let Xiaobian take you to understand.

Database architecture: one master and two slaves

Master:192.168.8.57

Slave1:192.168.8.58

Slave2:192.168.8.59

Manager:192.168.8.60

MHA Toolkit:

Mha4mysql-manager-0.58.tar.gz

Mha4mysql-node-0.58.tar.gz

The Manager toolkit mainly includes the following tools:

Masterha_check_ssh checks the SSH configuration status of MHA

Masterha_check_repl checks MySQL replication status

Masterha_manger starts MHA

Masterha_check_status detects the current MHA running status

Masterha_master_monitor detects whether master is down.

Masterha_master_switch controls failover (automatic or manual)

Masterha_conf_host adds or removes configured server information

The Node toolkit (these tools are usually triggered by MHA Manager scripts and do not require human manipulation) mainly includes the following tools:

Save_binary_logs saves and copies binary logs of master

Apply_diff_relay_logs identifies differential relay log events and applies their differential events to other slave

Filter_mysqlbinlog removes unnecessary ROLLBACK events (MHA no longer uses this tool)

Purge_relay_logs clears relay logs (does not block SQL threads)

1. Configure MySQL5.7

1. In the configuration file added, the three servers are the same, only need to modify server-id and log-bin.

Note: binlog-do-db and replicate-ignore-db settings must be the same. MHA detects the filtering rules at startup. If the filtering rules are different, MHA does not initiate monitoring and failover, which is not set here.

Server-id=57log-bin=mysql-bingtid_mode = on# enable gtid, must be fully master-slave enforce_gtid_consistency = 1log_slave_updates = semi-synchronous replication is enabled otherwise a primary key error will be reported when switching between master and slave automatically plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" loose_rpl_semi_sync_master_enabled = 1loose_rpl_semi_sync_slave_enabled = 1loose_rpl_semi_sync_master_timeout = 5000

two。 Start the server and configure the master and slave (abbreviated here, there are many Baidu)

MySQL5.7 loads the validate_password module by default to control the password length and rules. You can close the module and add validate_password = off in the configuration file, or execute set global validate_password_policy=0; on the mysql command line to cancel the password rules.

Configure replication and monitoring users on three servers, all three servers will be added.

Add replication user

Grant replication slave on *. * to 'repl'@'192.168.8.%' identified by' mysql'

Note that the repl user must be used here, otherwise the replication user will not be found later.

Add monitoring users

Grant all privileges on *. * to 'root'@'192.168.8.%' identified by' mysql'

Execute on slave1 and slave2

Change master to master_host='192.168.8.57', master_port=3306, master_user='repl', master_password='mysql', master_auto_position=1

Set read-only from the server, do not write in the configuration file, focus!

Set global read_only=1

2. Configure MHA

1. Install dependency packages (all nodes)

Yum install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker perl-CPAN

two。 Install manager

Tar xf mha4mysql-manager-0.58.tar.gz cd mha4mysql-manager-0.58perl Makefile.PLmake & & make install

Install node (all four servers are installed)

Tar xf mha4mysql-node-0.58.tar.gzcd mha4mysql-node-0.58perl Makefile.PLmake & & make install

After the installation is completed, the corresponding script is generated under the / usr/local/bin directory, and the relevant script is copied to the / usr/local/bin directory (executed by the manager server)

Cp-ra / root/mha4mysql-manager-0.57/samples/scripts/* / usr/local/bin

Master_ip_failover

# it is not necessary for vip to manage scripts when switching automatically. If we use keepalived, we can write scripts to manage vip, such as monitoring mysql. If mysql is abnormal, we can stop keepalived, so that vip will drift automatically.

Master_ip_online_change

# it is not necessary to manage vip when switching online. You can also write a simple shell to complete it.

Power_manager

# it is not necessary to shut down the script of the host after a failure occurs

Send_report

# because it is not necessary to send an alarm script after a failover, you can write a simple shell to complete it.

3. Configure SSH login without password authentication

Master

Ssh-keygen-t rsassh-copy-id-I ~ / .ssh/id_rsa.pub root@192.168.8.58ssh-copy-id-I ~ / .ssh/id_rsa.pub root@192.168.8.59ssh-copy-id-I ~ / .ssh/id_rsa.pub root@192.168.8.60

Slave1

Ssh-keygen-t rsassh-copy-id-I ~ / .ssh/id_rsa.pub root@192.168.8.57ssh-copy-id-I ~ / .ssh/id_rsa.pub root@192.168.8.59ssh-copy-id-I ~ / .ssh/id_rsa.pub root@192.168.8.60

Slave2

Ssh-keygen-t rsassh-copy-id-I ~ / .ssh/id_rsa.pub root@192.168.8.57ssh-copy-id-I ~ / .ssh/id_rsa.pub root@192.168.8.58ssh-copy-id-I ~ / .ssh/id_rsa.pub root@192.168.8.60

Manager

Ssh-keygen-t rsassh-copy-id-I ~ / .ssh/id_rsa.pub root@192.168.8.57ssh-copy-id-I ~ / .ssh/id_rsa.pub root@192.168.8.58ssh-copy-id-I ~ / .ssh/id_rsa.pub root@192.168.8.59

4. Configure MHA

Modify / usr/local/bin/master_ip_failover, where scripts are used to manage vip

Copy all the following code into it and modify it according to your actual situation

#! / usr/bin/env perl# Copyright (C) 2011 DeNA Co.,Ltd.## This program is free software; you can redistribute it and/or modify# it under the terms of the GNU General Public License as published by# the Free Software Foundation; either version 2 of the License, or# (at your option) any later version.## This program is distributed in the hope that it will be useful,# but WITHOUT ANY WARRANTY; without even the implied warranty of# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the# GNU General Public License for more details.## You should have received a copy of the GNU General Public License# along with this program; if not, write to the Free Software# Foundation, Inc.,# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA## Note: This is a sample script and is not complete. Modify the script based on your environment.use strict;use warnings FATAL = > 'all';use Getopt::Long;use MHA::DBHelper;my ($command, $ssh_user, $orig_master_host, $orig_master_ip, $orig_master_port, $new_master_host, $new_master_ip, $new_master_port, $new_master_user, $new_master_password); my $vip =' 192.168.88 Universe key ='1' My $ssh_start_vip = "/ sbin/ifconfig enp0s3:$key $vip"; my $ssh_stop_vip = "/ sbin/ifconfig enp0s3:$key down" 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,' new_master_user=s' = >\ $new_master_user, 'new_master_password=s' = >\ $new_master_password,) Exit & main (); sub main {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 {# updating global catalog, etc $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 {my $new_master_handler = new MHA::DBHelper (); # args: hostname, port, user, password, raise_error_or_not $new_master_handler- > connect ($new_master_ip, $new_master_port, $new_master_user, $new_master_password, 1); # # Set read_only=0 on the new master $new_master_handler- > disable_log_bin_local () Print "Set read_only=0 on the new master.\ n"; $new_master_handler- > disable_read_only (); # # Creating an app user on the new master print "Creating app user on the new master..\ n"; FIXME_xxx_create_user ($new_master_handler- > {dbh}); $new_master_handler- > enable_log_bin_local (); $new_master_handler- > disconnect () # # Update master ip on the catalog database, etc # FIXME_xxx; $exit_code = 0;}; if ($@) {warn $@; # If you want to continue failover, exit 10. Exit $exit_code;} exit $exit_code;} elsif ($command eq "status") {# do nothing exit 0;} else {& usage (); exit 1 } sub usage {print "Usage: master_ip_failover-- command=start | stop | stopssh | status-- orig_master_host=host-- orig_master_ip=ip-- orig_master_port=port-- new_master_host=host-- new_master_ip=ip-- new_master_port=port\ n";}

5. Modify MHA configuration file

Mkdir-p / etc/masterhacp mha4mysql-manager-0.58/samples/conf/app1.cnf / etc/masterha/ [server default] manager_workdir=/var/log/masterha/app1 manager_log=/var/log/masterha/app1/manager.log master_binlog_dir=/mysql/data master_ip_failover_script= / usr/local/bin/master_ip_failover master_ip_online_change_script= / usr/local/bin/master_ip_online_change password=mysql user=root Ping_interval=1 remote_workdir=/tmprepl_password=mysqlrepl_user=repl report_script=/usr/local/bin/send_reportsecondary_check_script= / usr/local/bin/masterha_secondary_check-s 192.168.8.58-s 192.168.8.59 shutdown_script= "" ssh_user=root [server1] hostname=192.168.8.57port= 3306 [server2] hostname=192.168.8.58port=3306candidate_master=1 check_repl_delay=0 [server3] hostname=192.168.8.59port=3306

6. Test MHA

Test the connection of ssh

Masterha_check_ssh-conf=/etc/masterha/app1.cnf

Test the connectivity of the mysq cluster

Masterha_check_repl-conf=/etc/masterha/app1.cnf

Check the status of MHA

Masterha_check_status-conf=/etc/masterha/app1.cnf

Start MHA

Nohup masterha_manager-conf=/etc/masterha/app1.cnf-remove_dead_master_conf-ignore_last_failover

/ var/log/masterha/app1/manager.log 2 > & 1 &

-remove_dead_master_conf this parameter means that when a master-slave switch occurs, the ip of the old master library will be removed from the configuration file.

-location of manger_log logs

-ignore_last_failover by default, if MHA detects continuous downtime and the interval between two outages is less than 8 hours, Failover will not be performed. This restriction is to avoid ping-pong effect. This parameter means that the file generated by the last MHA trigger switch is ignored. By default, after the MHA switch occurs, the app1.failover.complete file will be generated in the log directory, that is, the / data I set above. The switch will not be triggered if the file is found to exist in the directory next time, unless the file is deleted after the first switch. For convenience, it is set to-ignore_last_failover here.

To make it easy to start and stop MHA, create the following script

Cat masterha_start.shnohup masterha_manager-conf=/etc/masterha/app1.cnf-remove_dead_master_conf-ignore_last_failover

/ var/log/masterha/app1/manager.log 2 > & 1 & cat masterha_stop.sh masterha_stop-- conf=/etc/masterha/app1.cnf

7. Check the startup status of MHA

Tail-f / var/log/masterha/app1/manager.log

If the last line is

[info] Ping (SELECT) succeeded, waiting until MySQL doesn't respond..

Indicates that the startup is successful.

8.master add vip

Execute on master

/ sbin/ifconfig enp0s3:1 192.168.8.88 MiB 24 [ifconfigenp0s3: flags=4163 mtu 1500 inet 192.168.57 netmask 255.255.255.0 broadcast 192.168.255 inet6 fe80::5198:593b:cdc5:1f90 prefixlen 64 scopeid 0x20 ether 08:00:27:c0:45:0d txqueuelen 1000 (Ethernet) RX packets 72386 bytes 9442794 (9.0 MiB) RX errors 0 dropped 0 overruns 0 frame 0 TX Packets 24221 bytes 2963104 (2.8MiB) TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0enp0s3:1: flags=4163 mtu 1500 inet 192.168.88 netmask 255.255.255.0 broadcast 192.168.255 ether 08:00:27:c0:45:0d txqueuelen 1000 (Ethernet) lo: flags=73 mtu 65536 inet 127.0.0.1 netmask 255.0.0.0 inet6:: 1 prefixlen 128 scopeid 0x10 loop txqueuelen 1000 (Local Loopback) RX packets 84 bytes 9492 RX errors 0 dropped 0 overruns 0 frame 0 TX packets 84 bytes 9492 (9.2 KiB) TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0virbr0: flags=4099 mtu 1500 inet 192.168.122.1 netmask 255.255.0 broadcast 192.168.122.255 ether 52:54:00:f4:55:bb txqueuelen 1000 (Ethernet) RX packets 0 bytes 0 (0.0B) RX errors 0 dropped 0 overruns 0 frame 0 TX packets 0 bytes 0 (0.0B) TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0 Thank you for reading this article carefully I hope the article "how to build MySQL5.7 high availability of CentOS7" shared by the editor will be helpful to you. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.