# 上传脚本[root@master ~]# tar zxf auto-install-mysql57-glibc.tar.gz [root@master ~]# cd auto-install-mysql57-glibc[root@master auto-install-mysql57-glibc]# ls
auto-install-mysql57-glibc.sh my.cnf mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz
[root@master auto-install-mysql57-glibc]# ./auto-install-mysql57-glibc.sh set password=password('123');
2、配置 master 主服务器
master
# 开启二进制日志[root@master ~]# vi /etc/my.cnf[mysqld]
log-bin=master-bin
binlog-format= MIXED
server-id=1# 重启 MySQL 服务[root@master ~]# systemctl restart mysqld[root@master ~]# ls /usr/local/mysql/data/
auto.cnf client-key.pem ib_logfile1 master.err mysql.sock.lock server-cert.pem
ca-key.pem ib_buffer_pool ibtmp1 master.pid performance_schema server-key.pem
ca.pem ibdata1 master-bin.000001 mysql private_key.pem sys
client-cert.pem ib_logfile0 master-bin.index mysql.sock public_key.pem
# 登录 MySQL 程序,给从服务器授权
grant replication slave on *.* to 'myslave'@'192.168.72.%' identified by '123456';# 刷新权限
flush privileges;# MySQL 返回了主服务器的二进制日志(binary log)的当前状态
mysql> show master status;+-------------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+-------------------+----------+--------------+------------------+-------------------+| master-bin.000001|603||||+-------------------+----------+--------------+------------------+-------------------+1 row inset(0.03 sec)
[root@manager ~]# cat /usr/local/bin/master_ip_failover#!/usr/bin/env perl# Copyright (C) 2011 DeNA Co.,Ltd.# 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.72.200/24';
my $key ='1';
my $ssh_start_vip ="/sbin/ifconfig ens33:$key $vip up";
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 {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(){return0 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";}
# 在 master 节点[root@master ~]# mysql -uroot -p123
grant all privileges on *.* to 'mha'@'192.168.72.%' identified by 'manager';
grant replication slave on *.* to 'myslave'@'192.168.72.%' identified by '123456';# 三台数据库节点操作
ln -s /usr/local/mysql/bin/mysqlbinlog /usr/sbin/
ln -s /usr/local/mysql/bin/mysql /usr/sbin/
5、测试
# 测试 ssh 无密码认证,如果正常最后会输出 successfully
masterha_check_ssh -conf=/etc/masterha/app1.cnf
# 测试 mysq 主从连接情况,如果正常最后会输出 MySQL Replication Health is OK.
masterha_check_repl -conf=/etc/masterha/app1.cnf
传统的MySQL主从架构存在的问题单点故障目前 MySQL 已经成为市场上主流数据库之一,考虑到业务的重要性,MySQL 数据库单点问题已成为企业网站架构中最大的隐患。随着技术的发展,MHA 的出现就是解决MySQL 单点的问题。另外随着企业数据量越来越庞大,数据库的压力又成为企业的另一个瓶颈,MySQL 多主多从架构的出现可以减轻 MySQL 本身的压力。