MySQL-MHA build

MHA MySQLDatabase cluster deployment method

Insert picture description here


MHA MySQLServer planning list

Insert picture description here


Check external network yumsources
Because many dependency packages need to be used during the software installation process, it is necessary to temporarily release Internet access rights and use public network yumsources.

#yum list
Insert picture description here


If the above information cannot be displayed, the troubleshooting steps are as follows:
1. Whether the server can access the Internet
2. Check Yumwhether the configuration file exists (after installing the system, the yumconfiguration file is located in the following path by default)

Insert picture description here


Host IPbinding
1. Binding cluster IP(on each host Proceeding) #vim /etc/hosts

Insert picture description here


MySQLPassword-free login between servers
Configure root sshpassword-free login:
1. Enter the command on each master: cd /root/.sshenter rsathe directory where the public key and private key files are stored, and delete the id_rsa,id_rsa.pubfiles in the directory . (Note: If /rootthe absence .sshdirectory is created manually: mkdir /root/.ssh)
2, .sshenter the command in the directory:

ssh-keygen -t rsa

After three times of carriage return, id_rsa,id_rsa.pubfiles will be generated in this directory . Other hosts also use this method to generate key files.
3. Log in DBmysql01and enter the following command to id_rsa.pubcopy the contents of the public key to the authorized_keysfile.

cat id_rsa.pub >> authorized_keys

4, log in other hosts, the key file contents of other hosts are each copied to the authorized_keysfile, the command is as follows:
(Note: if there is no ssh-copy-idcommand, manually id_rsa.pubcontents of each copy into the authorized_keysfile)
(if it can not execute a command, then it (Need to install a dependency package
yum -y install openssh-clients)

ssh-copy-id -i /root/.ssh/id_rsa.pub [email protected] #登录	DBmysql01,	将公钥拷贝到DBmysql02的authorized_keys中
ssh-copy-id -i /root/.ssh/id_rsa.pub [email protected] #登录	DBmysql01,	将公钥拷贝到DBmysql03的authorized_keys中
ssh-copy-id -i /root/.ssh/id_rsa.pub [email protected] #登录	DBmysql01,	将公钥拷贝到DBmysql04的authorized_keys中
ssh-copy-id -i /root/.ssh/id_rsa.pub [email protected] #登录	DBmysql02,	将公钥拷贝到DBmysql01的authorized_keys中
ssh-copy-id -i /root/.ssh/id_rsa.pub [email protected] #登录	DBmysql02,	将公钥拷贝到DBmysql03的authorized_keys中
ssh-copy-id -i /root/.ssh/id_rsa.pub [email protected] #登录	DBmysql02,	将公钥拷贝到DBmysql04的authorized_keys中
ssh-copy-id -i /root/.ssh/id_rsa.pub [email protected] #登录	DBmysql03,	将公钥拷贝到DBmysql01的authorized_keys中
ssh-copy-id -i /root/.ssh/id_rsa.pub [email protected] #登录	DBmysql03,	将公钥拷贝到DBmysql02的authorized_keys中
ssh-copy-id -i /root/.ssh/id_rsa.pub [email protected] #登录	DBmysql03,	将公钥拷贝到DBmysql04的authorized_keys中
ssh-copy-id -i /root/.ssh/id_rsa.pub [email protected] #登录	DBmysql04,	将公钥拷贝到DBmysql01的authorized_keys中
ssh-copy-id -i /root/.ssh/id_rsa.pub [email protected] #登录	DBmysql04,	将公钥拷贝到DBmysql02的authorized_keys中
ssh-copy-id -i /root/.ssh/id_rsa.pub [email protected] #登录	DBmysql04,	将公钥拷贝到DBmysql03的authorized_keys中

5. Authorization authorized_keysfile, operate on all hosts,
log DBmysql01in .ssh, enter the command in the directory:

chmod 600 authorized_keys

The effect is as follows: So

Insert picture description here


far, the password-free login has been set up. Note that sshyou need to enter the password when you log in for the first time , and you can log in without password when you visit again.

Install Mysqldatabase software
1. mysqlAdd the following system parameters on each database server

#vim /etc/security/limits.conf
root hard    nproc 204800
root soft    nproc 204800
root hard    nofile 204800
root soft    nofile 204800
mysql hard    nproc 204800
mysql soft    nproc 204800
mysql hard    nofile 204800
mysql soft    nofile 204800

2. To YUMinstall all the hosts, you need to open the
mysqldownload site of the external network software:https://dev.mysql.com/downloads/repo/yum/

3. Configuration mysql YUMsource, the operating system here isCENTOS 7

#vim /etc/yum.repos.d/mysql-5.7.repo

# Enable to use MySQL 5.7
[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/$basearch/
enabled=1
gpgcheck=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
Insert picture description here


Insert picture description here


Delete conflicting mariadbconflicts (delete on all nodes)

Insert picture description here


Insert picture description here


installation (install on all hosts)
method one, network installationmysql5.7:

#yum install -y mysql-community-server

Method 2: RPMPackage installation, rpmupload the downloaded package to the server

#rpm -ivh mysql-community-client-5.7.24-1.el7.x86_64.rpm mysql-community-common-5.7.24-1.el7.x86_64.rpm mysql-community-libs-5.7.24-1.el7.x86_64.rpm mysql-community-server-5.7.24-1.el7.x86_64.rpm
Insert picture description here
#启动mysql
systemctl start mysqld.service
#查看mysql状态    
systemctl status mysqld.service 
#查看root密码第一次启动mysql数据库的密码 
grep 'temporary password' /var/log/mysqld.log
#登录数据库并修改初始密码
mysql -uroot -p
#修改数据库密码
Mysql>ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!'; 
#强制刷新
Mysql>flush privileges;
#选择数据库
mysql> use mysql;  
Database changed
#将用户名为root的改为admin
mysql> update user set user="admin" where user="root";
mysql> flush privileges;
这样便改了,但是密码并没有变

Configuration Mysqldatabase parameter file my.cnf
my.cnfcluster configuration and parameter optimization(DBmysql01、DBmysql02、DBmysql03):

# vim /etc/my.cnf

DBmysql01The my.cnffiles:

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[client]
default-character-set=utf8
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
character-set-server = utf8

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

##禁用dns解析主机名
skip-name-resolve

###以下是主从参数配置
master_info_repository=TABLE
relay_log_info_repository=TABLE
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16

# 服务器的ID,必须唯一
server_id=1
# 复制过滤:不需要备份的数据库(MySQL库一般不同步)
#binlog-ignore-db=mysql
#开启gtid模式
gtid_mode=ON
#强制gtid一直性,用于保证启动gitd后事务的安全
enforce-gtid-consistency=true
# 开启二进制日志功能,名字可以随便取,最好有含义(比如项目名)
log-bin=weixin-mysql-bin
# 为每个 session 分配的内存,在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M
# 主从复制的格式(mixed,statement,row,默认格式是 statement)
binlog_format=row
# 二进制日志自动删除/过期的天数。默认值为 0,表示不自动删除。
expire_logs_days=60
# 跳过主从复制中遇到的所有错误或指定类型的错误,避免 slave 端复制中断。 
# 如:1062 错误是指一些主键重复,1032 错误是因为主从数据库数据不一致
slave_skip_errors=1062
# 作为从服务器时的中继日志
relay_log=weixin-mysql-relay-bin
# log_slave_updates 表示 slave 将复制事件写进自己的二进制日志
log_slave_updates=1
# 主键自增规则,避免主从同步ID重复的问题
auto_increment_increment=2  # 自增因子(每次加2)
auto_increment_offset=1     # 自增偏移(从1开始),单数

##for slow query log
slow_query_log                      =on                             #    off
slow_query_log_file                 =slow.log                       #    hostname.log
long_query_time                     =1.000000                       #    10.000000

DBmysql02The my.cnffiles:

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[client]
default-character-set=utf8
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
character-set-server = utf8

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

##禁用dns解析主机名
skip-name-resolve

###以下是主从参数配置
master_info_repository=TABLE
relay_log_info_repository=TABLE
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16

# 服务器的ID,必须唯一
server_id=2
# 复制过滤:不需要备份的数据库(MySQL库一般不同步)
#binlog-ignore-db=mysql
#开启gtid模式
gtid_mode=ON
#强制gtid一直性,用于保证启动gitd后事务的安全
enforce-gtid-consistency=true
# 开启二进制日志功能,名字可以随便取,最好有含义(比如项目名)
log-bin=weixin-mysql-bin
# 为每个 session 分配的内存,在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M
# 主从复制的格式(mixed,statement,row,默认格式是 statement)
binlog_format=row
# 二进制日志自动删除/过期的天数。默认值为 0,表示不自动删除。
expire_logs_days=60
# 跳过主从复制中遇到的所有错误或指定类型的错误,避免 slave 端复制中断。 
# 如:1062 错误是指一些主键重复,1032 错误是因为主从数据库数据不一致
slave_skip_errors=1062
# 作为从服务器时的中继日志
relay_log=weixin-mysql-relay-bin
# log_slave_updates 表示 slave 将复制事件写进自己的二进制日志
log_slave_updates=1
# 主键自增规则,避免主从同步ID重复的问题
auto_increment_increment=2  # 自增因子(每次加2)
auto_increment_offset=1     # 自增偏移(从1开始),单数

##for slow query log
slow_query_log                      =on                             #    off
slow_query_log_file                 =slow.log                       #    hostname.log
long_query_time                     =1.000000                       #    10.000000

DBmysql03The my.cnffiles:

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[client]
default-character-set=utf8
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
character-set-server = utf8

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

##禁用dns解析主机名
skip-name-resolve

###以下是主从参数配置
master_info_repository=TABLE
relay_log_info_repository=TABLE
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16

# 服务器的ID,必须唯一
server_id=3
# 复制过滤:不需要备份的数据库(MySQL库一般不同步)
#binlog-ignore-db=mysql
#开启gtid模式
gtid_mode=ON
#强制gtid一直性,用于保证启动gitd后事务的安全
enforce-gtid-consistency=true
# 开启二进制日志功能,名字可以随便取,最好有含义(比如项目名)
log-bin=weixin-mysql-bin
# 为每个 session 分配的内存,在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M
# 主从复制的格式(mixed,statement,row,默认格式是 statement)
binlog_format=row
# 二进制日志自动删除/过期的天数。默认值为 0,表示不自动删除。
expire_logs_days=60
# 跳过主从复制中遇到的所有错误或指定类型的错误,避免 slave 端复制中断。 
# 如:1062 错误是指一些主键重复,1032 错误是因为主从数据库数据不一致
slave_skip_errors=1062
# 作为从服务器时的中继日志
relay_log=weixin-mysql-relay-bin
# log_slave_updates 表示 slave 将复制事件写进自己的二进制日志
log_slave_updates=1
# 主键自增规则,避免主从同步ID重复的问题
auto_increment_increment=2  # 自增因子(每次加2)
auto_increment_offset=1     # 自增偏移(从1开始),单数

##for slow query log
slow_query_log                      =on                             #    off
slow_query_log_file                 =slow.log                       #    hostname.log
long_query_time                     =1.000000                       #    10.000000

MySQLDatabase masternode data import
1. Export business data:

mysqldump -h 172.16.128.211 -uroot -proot --database depspayhub >/tmp/depspayhub_20190915.sql
mysqldump -h 172.16.128.211 -uroot -proot --database dmps_area >/tmp/dmps_area_20190915.sql
mysqldump -h 172.16.128.211 -uroot -proot --database dmps_single >/tmp/dmps_single_20190915.sql

2. Choose a mysqlserver to import the table structure, initial data, etc., as a masternode.
Data import ( db3306-20190426.sqlfor business system related database data):

mysql -uadmin -p </tmp/db3306-20190426.sql 

MySQLMaster-slave configuration
1. DBmysql01、DBmysql02、DBmysql03Create a replcopy account respectively

(备注10.17.248.%是指允许访问数据库的网段地址)
Mysql>grant replication slave on *.* to [email protected]'10.17.248.%' identified by '888888';  

mysql8.0The version creation user is different, the command is as follows:

#CREATE USER 'username'@'host' IDENTIFIED BY 'password';
例:CREATE USER 'repl'@'10.101.19.%' IDENTIFIED BY 'password';

##开启admin用户远程连接登录权限
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'10.17.248.%' IDENTIFIED BY '123456';

mysql8.0用户有授权有所不同,命令如下:
grant replication slave on *.* to 'admin'@'172.16.16.%';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'10.17.248.%';

查数据库用户
Mysql> select distinct concat ('user: ''',user,'''@''',host,''';')as qieru from mysql.user;

2. masterBack up data on

#mysqldump -uadmin -p --master-data=2  --single-transaction  --set-gtid-purged=off -A >db3306-`date +%Y%m%d`.sql

2, the backup data are respectively turned out to DBmysql02, DBmysql03the

#mysql -uadmin -p </opt/Mysql/db3306-20190915.sql

3. Respectively execute on the slave:

mysql> change master to master_host='10.17.248.27',master_user='repl',master_password='888888',master_auto_position=1; 
Query OK, 0 rows affected (0.02 sec)

#从节点启动
mysql> start slave; 
Query OK, 0 rows affected (0.01 sec)

mysql> 

4. View master、slave

Insert picture description here


Insert picture description here


Error handling
View the master and slave node configuration /etc/my.cof配files, check the settings, gtid_mode=ON
reauthorize GRANT ALL PRIVILEGES ON *.* TO 'admin'@'10.17.248.%';and re-designate the master node

change master to master_host='10.17.248.27',master_user='repl',master_password='888888',master_auto_position=1;
Insert picture description here


mysql 8.0The caching_sha2_passwordauthentication mechanism is used by default -changed from the original mysql_native_passwordto caching_sha2_password.
The 5.7upgraded 8.0version will not change the authentication method of existing users, but new users will default to using the new caching_sha2_passwordclient that does not support the new encryption method.
Modify the user's password and encryption method. Enter in command line modemysql

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
Insert picture description here


5. At this point, the Mysqlmaster-slave configuration is complete.

MHA ManagerSoftware installation
1. DBmysql04Install epelsource and dependent packages on

#yum install epel-release -y
#yum install -y perl-Config-Tiny perl-Email-Date-Format perl-Log-Dispatch perl-Mail-Sender perl-Mail-Sendmail perl-MIME-Lite perl-MIME-Types perl-Parallel-ForkManager

#Install mhasoftware

#yum install mha4mysql-manager-0.58-0.el7.centos.noarch.rpm mha4mysql-node-0.58-0.el7.centos.noarch.rpm -y

2. After installation, as shown in the figure below: The

Insert picture description here


Managertoolkit mainly includes the following tools:

masterha_check_ssh              检查MHA的SSH配置状况
masterha_check_repl             检查MySQL复制状况
masterha_manger                 启动MHA
masterha_check_status           检测当前MHA运行状态
masterha_master_monitor         检测master是否宕机
masterha_master_switch          控制故障转移(自动或者手动)
masterha_conf_host              添加或删除配置的server信息
Insert picture description here


Configuration MHA Managerparameter
1, configuration MHAdirectory

mkdir /etc/masterha/                ##创建配置文件所在目录
mkdir /var/log/masterha/		    ##创建日志存放路径
mkdir /var/log/masterha/app1/       ##创建日志存放路径

Authorized file directory is readable and writablecd /etc/masterha/

chmod 775 /etc/masterha/ /var/log/masterha/ /var/log/masterha/app1/
chmod 775 /var/log/masterha/app1/manager.log

3. The configuration /etc/masterha/app1.cnf# file does not need to be manually created. The
App1.cnfcontent is as follows:

[server default]
manager_workdir=/var/log/masterha/app1.log     #设置manager的工作目录
manager_log=/var/log/masterha/app1/manager.log  #设置manager的日志
master_binlog_dir=/var/lib/mysql  #设置master 保存binlog的位置,以便MHA可以找到master的日志,我这里的也就是mysql的数据目录
master_ip_failover_script= /usr/local/bin/master_ip_failover    #设置自动failover时候的切换脚本
master_ip_online_change_script= /usr/local/bin/master_ip_online_change  #设置手动切换时候的切换脚本
password=123456    #设置mysql中root用户的密码,这个密码是前文中创建监控用户的那个密码
user=root               #设置监控用户root
ping_interval=1         #设置监控主库,发送ping包的时间间隔,默认是3秒,尝试三次没有回应的时候自动进行railover
remote_workdir=/tmp     #设置远端mysql在发生切换时binlog的保存位置
repl_password=123456    #设置复制用户的密码
repl_user=repl          #设置复制环境中的复制用户名
#report_script=/usr/local/send_report   #设置发生切换后发送的报警的脚本
#secondary_check_script= /usr/local/bin/masterha_secondary_check -s server03 -s server02            
shutdown_script=""      #设置故障发生后关闭故障主机脚本(该脚本的主要作用是关闭主机放在发生脑裂,这里没有使用)
ssh_user=root           #设置ssh的登录用户名

[server1]
hostname=10.17.248.27
port=3306

[server2]
hostname=10.17.248.28
port=3306
candidate_master=1   #设置为候选master,如果设置该参数以后,发生主从切换以后将会将此从库提升为主库,即使这个主库不是集群中事件最新的slave
check_repl_delay=0   #默认情况下如果一个slave落后master 100M的relay logs的话,MHA将不会选择该slave作为一个新的master,因为对于这个slave的恢复需要花费很长时间,通过设置check_repl_delay=0,MHA触发切换在选择一个新的master的时候将会忽略复制延时,这个参数对于设置了candidate_master=1的主机非常有用,因为这个候选主在切换的过程中一定是新的master

[server3]
hostname=10.17.248.29
port=3306

VIP(Virtual IP) configuration
to configure VIPthe script: master_ip_failover, master_ip_online_changeput to /usr/local/binthe next (attention to the need to manually create, see details below)

Insert picture description here


Insert picture description here


1) master_ip_failoverscript reads:

#!/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 = '10.17.248.68/16';
my $key = '1';
my $ssh_start_vip = "/sbin/ip addr add $vip dev eth0"; #网卡名称
my $ssh_stop_vip = "/sbin/ip addr del $vip dev eth0";  #网卡名称

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 "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";

    if ( $command eq "stop" || $command eq "stopssh" ) {

        my $exit_code = 1;
        eval {
            print "Disabling the VIP on old master: $orig_master_host \n";
            &stop_vip();
            $exit_code = 0;
        };
        if ([email protected]) {
            warn "Got Error: [email protected]\n";
            exit $exit_code;
        }
        exit $exit_code;
    }
    elsif ( $command eq "start" ) {

        my $exit_code = 10;
        eval {
            print "Enabling the VIP - $vip on the new master - $new_master_host \n";
            &start_vip();
            $exit_code = 0;
        };
        if ([email protected]) {
            warn [email protected];
            exit $exit_code;
        }
        exit $exit_code;
    }
    elsif ( $command eq "status" ) {
        print "Checking the Status of the script.. OK \n";
        exit 0;
    }
    else {
        &usage();
        exit 1;
    }
}

sub start_vip() {
    `ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
}
sub stop_vip() {
     return 0  unless  ($ssh_user);
    `ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;
}

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";
}

2) master_ip_online_changeScript content:

#!/usr/bin/env perl
use strict;  
use warnings FATAL =>'all';  

use Getopt::Long;  

my $vip = '10.17.248.68/16';  # Virtual IP  
my $key = "1";  
my $ssh_start_vip = "/sbin/ip addr add $vip dev eth0";  #网卡名称
my $ssh_stop_vip = "/sbin/ip addr del $vip dev eth0";  #网卡名称
my $exit_code = 0;  

my (  
  $command,              $orig_master_is_new_slave, $orig_master_host,  
  $orig_master_ip,       $orig_master_port,         $orig_master_user,  
  $orig_master_password, $orig_master_ssh_user,     $new_master_host,  
  $new_master_ip,        $new_master_port,          $new_master_user,  
  $new_master_password,  $new_master_ssh_user,  
);  
GetOptions(  
  'command=s'                => \$command,  
  'orig_master_is_new_slave' => \$orig_master_is_new_slave,  
  'orig_master_host=s'       => \$orig_master_host,  
  'orig_master_ip=s'         => \$orig_master_ip,  
  'orig_master_port=i'       => \$orig_master_port,  
  'orig_master_user=s'       => \$orig_master_user,  
  'orig_master_password=s'   => \$orig_master_password,  
  'orig_master_ssh_user=s'   => \$orig_master_ssh_user,  
  '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,  
  'new_master_ssh_user=s'    => \$new_master_ssh_user,  
);  


exit &main();  

sub main {  

#print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";  

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 "\n\n\n***************************************************************\n";  
            print "Disabling the VIP - $vip on old master: $orig_master_host\n";  
            print "***************************************************************\n\n\n\n";  
&stop_vip();  
            $exit_code = 0;  
        };  
        if ([email protected]) {  
            warn "Got Error: [email protected]\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 "\n\n\n***************************************************************\n";  
            print "Enabling the VIP - $vip on new master: $new_master_host \n";  
            print "***************************************************************\n\n\n\n";  
&start_vip();  
            $exit_code = 0;  
        };  
        if ([email protected]) {  
            warn [email protected];  
            exit $exit_code;  
        }  
        exit $exit_code;  
}  
elsif ( $command eq "status" ) {  
        print "Checking the Status of the script.. OK \n";  
        `ssh $orig_master_ssh_user\@$orig_master_host \" $ssh_start_vip \"`;  
        exit 0;  
}  
else {  
&usage();  
        exit 1;  
}  
}  

# A simple system call that enable the VIP on the new master  
sub start_vip() {  
`ssh $new_master_ssh_user\@$new_master_host \" $ssh_start_vip \"`;  
}  
# A simple system call that disable the VIP on the old_master  
sub stop_vip() {  
`ssh $orig_master_ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;  
}  

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";  
}
chmod 775 /usr/local/bin/master_ip_failover
chmod 775 /usr/local/bin/master_ip_online_change

Start MHA Managerservice

1. Start the MHAservice

#nohup masterha_manager --conf=/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/masterha/app1/manager.log 2>&1 &

Note the use of:
in when shellprompted in nohupsuccess, the need to press any keyboard key on the terminal to return to shellenter the command window, and then by shellthe input exitto exit the terminal; if the nohuppoint to close the program button to close the terminal directly after performing successful, this At that time, the command corresponding to the command will be disconnected session, causing the nohupcorresponding process to be notified that it needs to be together shutdown, and it will not function to call the program to continue running in the background after closing the terminal.

Insert picture description here


2. Check the status:

#masterha_check_status --conf=/etc/masterha/app1.cnf
Insert picture description here


3. You need to check whether the VIP has been added normally at the first startup, if not, you need to add it manually.
1) View VIP:

#ip add
Insert picture description here


2) If it does not exist VIPat the first startup , manually add it on the Master host:

#添加
ip addr add 10.17.248.68/16 dev eth0 
#删除  
ip addr del 10.17.248.68/16 dev eth0	  

4. sshPassword-free login check

masterha_check_ssh --conf=/etc/masterha/app1.cnf
Insert picture description here


5. MysqlMaster-slave replication check

masterha_check_repl --conf=/etc/masterha/app1.cnf
Insert picture description here


For common problems such as restarting of related services, please check daily maintenance

Deployment verification

Insert picture description here


Database cluster service restart and maintenance
Database normal shutdown steps

1 Close the MHA service.
2 Check the server where the slave node is located.
3 Close the mysqld service of the slave node.
4 Close the mysqld service of the master node.

1. Turn off the mhaservice first

  ps aux|grep mha
Insert picture description here
 kill -9 进程号

2. First check slavethe server where the node is located. You need to log in to the database to check whether it is a slavenode or not through the following command

Mysql>show slave status\G;   
Insert picture description here


Shut down the slavenode's mysqlservice

systemctl stop mysqld.service 

4. Shut down the masternode's mysqlservice

systemctl stop mysqld.service 

Normal database startup steps

1 Open the mysqld service of the master node
2 Open the mysqld service of the
slave node 3 Check whether the status of the slave node is normal
4 Open the MHA service

1. Turn on masterthe mysql service of the node

systemctl start mysqld.service  

2. Turn on slavethe mysql service of the node

systemctl start mysqld.service  

3. To check slavewhether the node is normal, you need to log in to the database

Insert picture description here


4. Open the MHAservice:

nohup masterha_manager --conf=/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/masterha/app1/manager.log 2>&1 &

exit
#注意要执行完以上命令,不要直接通过鼠标点击关闭窗口,要通过exit退出

5. Check VIPwhether it is on Masterthe host, if not, you need to add it manually

ip add
Insert picture description here


MHA ManagereService off/on

1. Restart the MHAservice
1) Close the MHAservice

ps aux|grep mha
Insert picture description here
kill -9 进程号

2) Turn on the MHAservice

nohup masterha_manager --conf=/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/masterha/app1/manager.log 2>&1 &
ls
exit
注意要执行完以上三步命令,不要直接通过鼠标点击关闭窗口

6. Check the status of the cluster, and the following information appears to indicate that the cluster is operating normally

master_check_status --conf=/etc/masterha/app1.cnf
Insert picture description here


masterRejoin the cluster after the failed node hangs up

When the masternode MHAgoes down, it will automatically failoverselect a new one master, and remove the failed node. The steps to rejoin the cluster after repairing the faulty node are as follows:
2. After the faulty node mysqldservice is started normally, check whether there is any slaveinformation.

Insert picture description here


3. Specify masterand enableslave

Mysql>change master to master_host='10.17.248.28(当前的master)',master_user='repl',master_password='123456',master_auto_position=1; 
Mysql>start slave;

4. Check that the slavestatus is normal and mastersynchronize the data.

Insert picture description here


5. Reconfigure MHAthe configuration file: /etc/masterha/app1.cnf

Insert picture description here


re-add the failed node:

Insert picture description here


common problem.
Problem 1: Use the yuminstallation software time report C. annot find a valid baseurl for repo: base/7/x86_64
Solution: check whether the server can access the external network

Question two:

ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty
当前GTID_EXECUTED参数已经有值,而从集群倒出来的dump文件中包含了	 SET @@GLOBAL.GTID_PURGED的操作

Solution:

方法一:reset mater这个操作可以将当前库的GTID_EXECUTED值置空
方法二:--set-gtid-purged=off在dump导出时,添加--set-gtid-purged=off参数,避免将gtid信息导出mysqldump -uroot -p --set-gtid-purged=off -d sso>sso1.sql在添加参数前的导出文件
Insert picture description here


File after adding parameters:

Insert picture description here


Question 3: MySQLModify the original rootname, use a new name, and change the password
Solution: After logging in, switch touser

mysql> use mysql;  选择数据库
Database changed
mysql> update user set user="admin" where user="root";将用户名为root的改为admin
mysql> flush privileges;
这样便改了,但是密码并没有变

After closing and logging in mysqlagain, use the command:
when logging in againMySQL

mysql -uadmin -p
#输密码是原来的密码,登进去之后再改密码
格式:mysql> set password for 用户名@localhost = password('新密码'); 
例子:mysql> set password for [email protected] = password('hello'); 

Problem 4: Install the MHAsoftware package error.

Insert picture description here


Solution: Install the mysql-community-libs-compatpackage.

Insert picture description here


Problem 5: Can adminnot log in

Insert picture description here


to the account remotely. Solution: Re-authorize

Mysql>grant replication slave on *.* to [email protected]'10.17.248.%' identified by '888888';  (备注10.17.248.%是指允许访问数据库的网段地址)

mysql8.0版本创建用户有所不同,命令如下:
#CREATE USER 'username'@'host' IDENTIFIED BY 'password';
例:CREATE USER 'repl'@'10.101.19.%' IDENTIFIED BY 'password';

##开启admin用户远程连接登录权限
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'10.17.248.%' IDENTIFIED BY '123456';

mysql8.0用户有授权有所不同,命令如下:
grant replication slave on *.* to 'admin'@'172.16.16.%';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'10.17.248.%';

查数据库用户
Mysql> select distinct concat ('user: ''',user,'''@''',host,''';')as qieru from mysql.user;

Question 6: The master-slave replication check failure report error-as shown in the figure.

Insert picture description here


Solution: The master-slave database replication verifies that the user replre-authorizes to change the password or specify the encryption method, and restart the database

ALTER USER 'repl'@'172.16.16.%' IDENTIFIED WITH mysql_native_password BY '密码';
Insert picture description here


Insert picture description here