mysql MHA搭建
架构
ip 一主两从
192.168.6.36 主
192.168.6.128 从1
192.168.7.93 从2
在3台上搭建mysql
在mysql官网 下载 mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz 下载通用的
主1配置文件
[mysqld] basedir=/data/mysql/mysql datadir=/data/mysql/data socket=/tmp/mysql.sock server_id=51 port=3306 secure-file-priv=/tmp #autocommit=0 log_bin=/data/binlog/mysql-bin binlog_format=row gtid-mode=on enforce-gtid-consistency=true log-slave-updates=1 # 这个比较重要，直接影响同步的性能 延时等问题.mysql5.7多源复制参数，5.7之前版本可不加 sync_binlog=1 auto_increment_increment=2 auto_increment_offset=1 sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES default-time_zone='+8:00' [mysql] prompt=db01 [\d]>
从1配置文件
[mysqld] basedir=/data/mysql/mysql datadir=/data/mysql/data socket=/tmp/mysql.sock server_id=52 port=3306 secure-file-priv=/tmp #autocommit=0 log_bin=/data/binlog/mysql-bin binlog_format=row gtid-mode=on enforce-gtid-consistency=true log-slave-updates=1 default-time_zone = '+8:00' # 这个比较重要，直接影响同步的性能 延时等问题.mysql5.7多源复制参数，5.7之前版本可不加 #sync_binlog=1 #auto_increment_increment=2 #auto_increment_offset=1 #sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES [mysql] prompt=db02 [\d]>
从2 配置文件
[mysqld] basedir=/data/mysql/mysql datadir=/data/mysql/data socket=/tmp/mysql.sock server_id=53 port=3306 secure-file-priv=/tmp #autocommit=0 log_bin=/data/binlog/mysql-bin binlog_format=row gtid-mode=on enforce-gtid-consistency=true log-slave-updates=1 #master_info_repository=TABLE #relay_log_info_repository=TABLE default-time_zone = '+8:00' #复制并发数设置
# 多源复制设置 #slave_parallel_workers = 16 [mysql] prompt=db03 [\d]>
mkdir /data/mysql -p
mkdir /data/mysql/data -p
cd /data/mysql
tar -xvf mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz
ln -s mysql-5.7.35-linux-glibc2.12-x86_64 mysql
cd /data/mysql/mysql/bin
chown -R mysql:mysql /data/mysql/
# 初始化，# --initialize-insecure 没有密码 #--initialize 密码在日志中
./mysqld --defaults-file=/etc/my.cnf --initialize-insecure --user=mysql --basedir=/data/mysql/mysql --datadir=/data/mysql/data
cp /data/mysql/mysql/support-files/mysql.server /etc/init.d/mysqld
service mysqld start
做主从
在主上操作
grant replication slave on *.* to repl@'%' identified by '123';
在从上操作
CHANGE MASTER TO MASTER_HOST='192.168.6.36', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';
start slave;
# 查看从的状态
show slave status\G;
#看到
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
主从成功
做MHA
mha官网：https://code.google.com/archive/p/mysql-master-ha/ github
下载地址：https://github.com/yoshinorim/mha4mysql-manager/wiki/Downloads
在3台mysql 中安装 mha4mysql-node
yum install perl-DBD-MySQL -y perl-Log-Dispatch perl-Parallel-ForkManager rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm
Manager软件安装
使用从2 当做 Manager 也可以重新选一台
yum install -y perl-Config-Tiny epel-release perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes
rpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm
在从2 上操作
创建配置文件目录 mkdir -p /etc/mha
创建日志目录 mkdir -p /var/log/mha/app1
编辑mha配置文件
vim /etc/mha/app1.cnf
[root@mysql03 mha]# cat app1.cnf.bak
[server default]
manager_log=/var/log/mha/app1/manager
manager_workdir=/var/log/mha/app1
master_binlog_dir=/data/binlog
password=mha
# 探测心跳的间隔时间，默认3次
ping_interval=2
repl_password=123
repl_user=repl
ssh_user=root
user=mha
# vip的配置
master_ip_failover_script=/usr/local/bin/master_ip_failover
# 发送邮件的脚本
report_script=/usr/local/bin/send_report
[server1]
hostname=192.168.6.36
port=3306
[server2]
hostname=192.168.6.128
port=3306
[server3]
hostname=192.168.7.93
port=3306
master_ip_failover文件
需要改动的地方
my $vip = '192.168.168.100/24'; # vip my $key = "1"; my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip"; #ens33 网卡名
my $ssh_stop_vip = "/sbin/ifconfig ens33:$key down"; #ens33 网卡名
#!/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.168.100/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, '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 { print "Enabling the VIP - $vip on the new master - $new_master_host \n"; &start_vip(); &stop_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\@$orig_master_host \" $ssh_start_vip \"`; exit 0; } else { &usage(); exit 1; } } sub start_vip() { `ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`; } # 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 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"; }
send_report 文件
需要改动的地方
my $smtp='smtp.126.com'; my $mail_from='from@126.com'; #发送的邮件 my $mail_user='from@126.com'; #发送的邮件 my $mail_pass='password'; #smtp的密码 不是账号的密码
#my $mail_to=['to1@qq.com','to2@qq.com'];
my $mail_to='to@126.com'; # 发送的邮箱
#!/usr/bin/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 Mail::Sender; use Getopt::Long; #new_master_host and new_slave_hosts are set only when recovering master succeeded my ( $dead_master_host, $new_master_host, $new_slave_hosts, $subject, $body ); my $smtp='smtp.126.com'; my $mail_from='from@126.com'; my $mail_user='from@126.com'; my $mail_pass='password'; #my $mail_to=['to1@qq.com','to2@qq.com']; my $mail_to='to@126.com'; GetOptions( 'orig_master_host=s' => \$dead_master_host, 'new_master_host=s' => \$new_master_host, 'new_slave_hosts=s' => \$new_slave_hosts, 'subject=s' => \$subject, 'body=s' => \$body, ); # Do whatever you want here mailToContacts($smtp,$mail_from,$mail_user,$mail_pass,$mail_to,$subject,$body); sub mailToContacts { my ($smtp, $mail_from, $mail_user, $mail_pass, $mail_to, $subject, $msg ) = @_; open my $DEBUG, ">/var/log/masterha/app1/mail.log" or die "Can't open the debug file:$!\n"; my $sender = new Mail::Sender { ctype => 'text/plain;charset=utf-8', encoding => 'utf-8', smtp => $smtp, from => $mail_from, auth => 'LOGIN', TLS_allowed => '0', authid => $mail_user, authpwd => $mail_pass, to => $mail_to, subject => $subject, debug => $DEBUG }; $sender->MailMsg( { msg => $msg, debug => $DEBUG } ) or print $Mail::Sender::Error; return 1; } exit 0;
