4

###数据库的高可用配置（mysql）

 2 years ago
source link: https://blog.51cto.com/u_15397018/5398967
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

###数据库的高可用配置（mysql）

推荐 原创

韦建国 2022-06-21 10:35:12 ©著作权

文章标签 mysql 文章分类 Linux 系统/运维 yyds干货盘点 阅读数422

一、环境配置：

　　1、操作系统版本：CentOS7

　　2、MySQL版本：5.7.28

　　3、VIP（虚IP）：10.0.0.140

　　4、机器列表及功能：

hostname

server_id

角色及功能

10.0.0.158

Monitor Host（监控复制组）/ Master（响应写请求）

10.0.0.159

Candidate Master（响应读请求）

10.0.0.160

Slave（响应读请求）

二、搭建

1、在s142、s143、s144机器上安装mysql5.7

①下载mysql-5.7.28，URL：​ ​https://downloads.mysql.com/archives/community/​​ 我这里下载的是64位版本

②下载后文件为：mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz

2. 卸载自带的mariadb和mysql

①检查是否安装了mariadb和mysql，有时候默认安装了

②如果没有，就可以安装mysql，如果有，需要先卸载（remove后为上面命令查询到的内容，全文件名，我这里没有，没法展示）

[root@localhost ~]#rpm -qa | grep mariadb
[root@localhost ~]#rpm -qa | grep mysql



②如果没有，就可以安装mysql，如果有，需要先卸载（remove后为上面命令查询到的内容，全文件名，我这里没有，没法展示）



[root@localhost ~]#yum remove mariadb-xxx



3.解压文件，修改目录名方便配置



[root@localhost ~]#tar -zxvf mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz -C /opt/soft/
[root@localhost ~]#mkdir -p /opt/soft
[root@localhost ~]#cd /opt/soft
[root@soft ~]#mv mysql-5.7.28-linux-glibc2.12-x86_64 mysql-5.7.28



4.在/usr/local/目录下创建到/opt/soft/mysql-5.7.28的软链接



[root@localhost ~]#cd /usr/local
[root@soft ~]#ln -s /opt/soft/mysql-5.7.28 mysql



###数据库的高可用配置（mysql）_mysql



5.添加mysql用户，修改mysql目录权限，并用此用户执行应用



[root@localhost ~]#useradd -s /bin/false -M mysql
[root@localhost ~]#cd /opt/soft
[root@soft ~]#chown -R mysql:mysql mysql-5.7.28



6.拷贝配置文件，将mysql的配置文件拷贝为/etc/目录下的my.cnf，并修改配置文件



[root@localhost ~]#vim /etc/my.cnf[mysqld]
# binlog 配置
log-bin=/usr/local/mysql/logs/mysql-bin.log
expire-logs-days=14
max-binlog-size=500M
server-id=1
# GENERAL
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/mysql.sock
user=mysql
default-storage-engine=InnoDB
character-set-server=utf8lower_case_table_names = 1explicit_defaults_for_timestamp=true
[mysqld_safe]
log-error=/usr/local/mysql/mysql-error.log
pid-file=/usr/local/mysql/mysqld.pid
[client]
socket=/usr/local/mysql/mysql.sock
[mysql]
default-character-set=utf8
socket=/usr/local/mysql/mysql.sock



7.安装mysql，进入mysql目录执行以下命令



cd /opt/soft/mysql-5.7.28
mkdir -p /usr/local/mysql/logs
chown mysql:mysql /usr/local/mysql/logs
bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data



    注：如果出现如下错误，说明需要安装依赖包：



###数据库的高可用配置（mysql）_mysql_02



① 安装autoconf依赖包：



yum -y install autoconf



②再次执行



bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data



出现以下信息，代表成功，要保存一下密码，



###数据库的高可用配置（mysql）_mysql_03



8.拷贝启动程序，将mysql的启动程序拷贝到/etc/init.d/目录下



cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld



###数据库的高可用配置（mysql）_mysql_04



 9.安装完，启动mysql服务



service mysqld start



注：如果出现如下错误：



[root@s144 support-files]# service mysqld start
Starting MySQL.2020-01-31T23:14:27.412533Z mysqld_safe error: log-error set to '/usr/local/mysql/mysql-error.log', however file don't exists. Create writable for user 'mysql'.
ERROR! The server quit without updating PID file (/usr/local/mysql/data/s144.pid).



说明mysql-error.log不存在，手动去创建，并修改权限



cd /opt/soft/mysql-5.7.28
touch mysql-error.log
chown mysql:mysql mysql-error.log



###数据库的高可用配置（mysql）_mysql_05



 出现SUCCESS，说明启动成功



###数据库的高可用配置（mysql）_mysql_06



10.配置环境变量，编辑/etc/profile，方便在任何地方用mysql命令



vim /etc/profile
#mysql
export MYSQL_HOME=/usr/local/mysql
export PATH=$PATH:$MYSQL_HOME/bin



注：别忘记重新编译  /etc/profile



source /etc/profile



11.登录mysql，修改密码



①首次登录没有密码，提示输入密码时，输入第7步安装时生成的密码：p5j2jfX7am.h



mysql -uroot -p



###数据库的高可用配置（mysql）_mysql_07



 ②这里要先使用alter user重置密码，不然会报错，我这里 修改mysql root用户密码 为  111111 ：



mysql> alter user 'root'@'localhost' identified by '111111';
mysql> flush privileges;



###数据库的高可用配置（mysql）_mysql_08



③ 至此本机登录密码修改完成，若是想让其他机器访问，需要配置远程访问：



GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '111111' WITH GRANT OPTION;



12.创建复制用户及复制配置



①在主节点（master）上配置复制用户：



create user canal_repl_user;
grant replication slave on *.*  to canal_repl_user identified by '111111';
flush privileges;
grant all on *.* to root identified by '111111';



②在从节点(slave)上执行主从复制命令：



CHANGE MASTER TO
MASTER_HOST='192.168.30.142',
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1,
MASTER_USER='canal_repl_user',
MASTER_PASSWORD='111111';



注：



#master_log_file='master-bin.000001',#5.6后不需要指定



#master_log_pos=189;



③启动主从复制



START SLAVE;



④查看主从复制信息



SHOW SLAVE STATUS;



⑤说明主从复制成功，可以在主库中创建一个库，看看从库是否同步



###数据库的高可用配置（mysql）_mysql_09



 13.在每台机器上安装yum源头及MHA依赖的perl包



wget http://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
rpm -ivh epel-release-latest-7.noarch.rpm
yum install -y perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager



如图安装成功：



###数据库的高可用配置（mysql）_mysql_10



###数据库的高可用配置（mysql）_mysql_11



1 4.配置ssh免密登录



①分别在s142/s143/s144机器上生成ssh秘钥：



ssh-keygen



②将各自公钥id_rsa.pub发送到另外两台机器，并追加到 ~/.ssh/authorized_keys中：



s142:



将文件 id_rsa.pub 改名 id_rsa_142.pub（这里需要自己改一下，笔记报错）
scp id_rsa_142.pub s143:~/.ssh/
scp id_rsa_142.pub s144:~/.ssh/



s143:



将文件 id_rsa.pub 改名 id_rsa_143.pub（这里需要自己改一下，笔记报错）
scp id_rsa_143.pub s142:~/.ssh/
scp id_rsa_143.pub s144:~/.ssh/



s144:



将文件 id_rsa.pub 改名 id_rsa_144.pub（这里需要自己改一下，笔记报错）
scp id_rsa_144.pub s142:~/.ssh/
scp id_rsa_144.pub s143:~/.ssh/



s142:



cat id_rsa_143.pub >> authorized_keys
cat id_rsa_144.pub >> authorized_keys



s143:



cat id_rsa_142.pub >> authorized_keys
cat id_rsa_144.pub >> authorized_keys



s144:



cat id_rsa_143.pub >> authorized_keys
cat id_rsa_142.pub >> authorized_keys



15.安装MHA 



①分别在s142、s143、s144上下载node安装包并安装：



wget https://qiniu.wsfnk.com/mha4mysql-node-0.58-0.el7.centos.noarch.rpm
rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm



###数据库的高可用配置（mysql）_mysql_12



 ②在s142上安装manager



wget https://qiniu.wsfnk.com/mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm



###数据库的高可用配置（mysql）_mysql_13



16.配置MHA Manager



①配置全局配置文件



新建 /etc/masterha_default.cnf （一定要是这个路径，不然后期masterha_check_ssh会提示未找到全局文件）



vim /etc/masterha_default.cnf

[server default]
user=root
password=111111
ssh_user=root
repl_user=canal_repl_user
repl_password=111111
ping_interval=1
#master_binlog_dir=/usr/local/mysql/logs
secondary_check_script=masterha_secondary_check -s s142 -s s143 -s s144 
master_ip_failover_script="/opt/soft/mha/scripts/master_ip_failover"
master_ip_online_change_script="/opt/soft/mha/scripts/master_ip_online_change"
report_script="/opt/soft/mha/scripts/send_report"



②配置主配置文件



新建/opt/soft/mha/app1/app1.cnf文件，并配置如下信息：



[server default]
manager_workdir=/opt/soft/mha
manager_log=/opt/soft/mha/manager.log

password=111111
user=root

ping_interval=1

repl_password=111111
repl_user=canal_repl_user

#master_binlog_dir=/usr/local/mysql/logs
#secondary_check_script=masterha_secondary_check -s s142 -s s143 -s s144
#master_ip_failover_script="/opt/soft/mha/scripts/master_ip_failover"
#master_ip_online_change_script="/opt/soft/mha/scripts/master_ip_online_change"
#report_script="/opt/soft/mha/scripts/send_report"

#ssh用户
ssh_user=root

[server1]
hostname=s142
port=3306
master_binlog_dir=/usr/local/mysql/logs
candidate_master=1
check_repl_delay=0

[server2]
hostname=s143
port=3306
master_binlog_dir=/usr/local/mysql/logs
candidate_master=1
check_repl_delay=0

[server3] 
hostname=s144
port=3306
master_binlog_dir=/usr/local/mysql/logs
ignore_fail=1
no_master=1



③配置VIP切换



为了防止脑裂发生,推荐生产环境采用脚本的方式来管理虚拟 ip,而不是使用 keepalived来完成。



vim /opt/soft/mha/scripts/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
    );

#定义VIP变量
    my $vip = '192.168.30.140/24';
    my $key = '1';
    my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip";
    my $ssh_stop_vip = "/sbin/ifconfig ens33:$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,
    );

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 ($@) {
                warn "Got Error: $@\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 ($@) {
            warn $@;
            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";
}



④配置VIP脚本



vim /opt/soft/mha/scripts/master_ip_online_change

#!/bin/bash
source /root/.bash_profile

vip=`echo '192.168.30.140/24'`  #设置VIP
key=`echo '1'`

command=`echo "$1" | awk -F = '{print $2}'`
orig_master_host=`echo "$2" | awk -F = '{print $2}'`
new_master_host=`echo "$7" | awk -F = '{print $2}'`
orig_master_ssh_user=`echo "${12}" | awk -F = '{print $2}'`
new_master_ssh_user=`echo "${13}" | awk -F = '{print $2}'`

#要求服务的网卡识别名一样，都为ens33(这里是)
stop_vip=`echo "ssh root@$orig_master_host /usr/sbin/ifconfig ens33:$key down"`
start_vip=`echo "ssh root@$new_master_host /usr/sbin/ifconfig ens33:$key $vip"`

if [ $command = 'stop' ]
  then
    echo -e "\n\n\n****************************\n"
    echo -e "Disabled thi VIP - $vip on old master: $orig_master_host \n"
    $stop_vip
    if [ $? -eq 0 ]
      then
    echo "Disabled the VIP successfully"
      else
    echo "Disabled the VIP failed"
    fi
    echo -e "***************************\n\n\n"
  fi

if [ $command = 'start' -o $command = 'status' ]
  then
    echo -e "\n\n\n*************************\n"
    echo -e "Enabling the VIP - $vip on new master: $new_master_host \n"
    $start_vip
    if [ $? -eq 0 ]
      then
    echo "Enabled the VIP successfully"
      else
    echo "Enabled the VIP failed"
    fi
    echo -e "***************************\n\n\n"
fi



⑤.配置报警邮件脚本



首先配置邮件发送设置信息



#mail邮件发送程序，需要先配置好发送这信息



vim /etc/mail.rc

set [email protected]
set smtp=smtp.163.com
set smtp-auth-user=qixing
#拿163邮箱来说这个不是密码，而是授权码
set smtp-auth-password=qixing
set smtp-auth=login



编写邮件发送脚本：



vim /opt/soft/mha/script/send_report


#!/bin/bash
source /root/.bash_profile
# 解析变量
orig_master_host=`echo "$1" | awk -F = '{print $2}'`
new_master_host=`echo "$2" | awk -F = '{print $2}'`
new_slave_hosts=`echo "$3" | awk -F = '{print $2}'`
subject=`echo "$4" | awk -F = '{print $2}'`
body=`echo "$5" | awk -F = '{print $2}'`
#定义收件人地址
email="[email protected]"

tac /var/log/mha/app1/manager.log | sed -n 2p | grep 'successfully' > /dev/null
if [ $? -eq 0 ]
    then
    messages=`echo -e "MHA $subject 主从切换成功\n master:$orig_master_host --> $new_master_host \n $body \n 当前从库:$new_slave_hosts"` 
    echo "$messages" | mail -s "Mysql 实例宕掉，MHA $subject 切换成功" $email >>/tmp/mailx.log 2>&1 
    else
    messages=`echo -e "MHA $subject 主从切换失败\n master:$orig_master_host --> $new_master_host \n $body" `
    echo "$messages" | mail -s ""Mysql 实例宕掉，MHA $subject 切换失败"" $email >>/tmp/mailx.log 2>&1  
fi



⑥将脚本赋予可执行权限



chmod +x /opt/soft/mha/scripts/master_ip_failover 
chmod +x /opt/soft/mha/scripts/master_ip_online_change 
chmod +x /opt/soft/mha/scripts/send_report



17.验证MHA配置信息是否正常



① 检查ssh配置：



masterha_check_ssh --conf=/opt/soft/mha/app1/app1.cnf



###数据库的高可用配置（mysql）_mysql_14



成功！！！



②(在所有机器执行)



ln -s /usr/local/mysql/bin/mysqlbinlog /usr/local/bin/mysqlbinlog
ln -s /usr/local/mysql/bin/mysql /usr/local/bin/mysql



③检查主从复制情况：



masterha_check_repl --conf=/opt/soft/mha/app1/app1.cnf



###数据库的高可用配置（mysql）_mysql_15



  健康！！！



18.在master节点上绑定VIP，只需绑定一次，后续会随主备切换而自动切换



ifconfig ens33:1 192.168.30.140/24



①如过遇到问题，需手动删除，可执行如下命令：



ifconfig ens33:1 del 192.168.30.140或ifconfig ens33:1 down #关闭vip



②可以查看绑定VIP是否成功：



ip addr



###数据库的高可用配置（mysql）_mysql_16



 说明绑定成功！



19.在MHA的manager节点上启动MHA管理进程



nohup masterha_manager --conf=/opt/soft/mha/app1/app1.cnf --ignore_last_failover /opt/soft/mha/app1/manager.log 2>&1



 &命令参数：



--remove_dead_master_conf       该参数代表当发生主从切换后，老的主库的ip将会从配置文件中移除。



--manger_log                    日志存放位置



观察manager.log日志，查看是否有成功，一般最后打印如下日志，说明成功：



Thu Jul  2 15:00:05 2020 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..



###数据库的高可用配置（mysql）_mysql_17



20.查看MHA状态



masterha_check_status --conf=/opt/soft/mha/app1/app1.cnf



###数据库的高可用配置（mysql）_mysql_18



 说明MHA正在运行中，主节点是s142



21.停止MHA管理进程



masterha_stop --conf=/opt/soft/mha/app1/app1.cnf



manager.log日志会打印终止日志：



###数据库的高可用配置（mysql）_mysql_19



22.手动进行主备切换(在进行手动切换前要先停值manager进程)



masterha_master_switch --conf=/opt/soft/mha/app1/app1.cnf --master_state=alive --new_master_host=s143 --orig_master_is_new_slave --running_updates_limit=10000 --interactive=0



###数据库的高可用配置（mysql）_mysql_20



 说明切换成功！

        

        

            

                report
            

        

    







        
Recommend

        



                

    
About Joyk

    
 





Aggregate valuable and interesting links.

Joyk means Joy of geeK