Mysql GTID Mha配置方法
Gtid+Mha+Binlogserver配置：
1：测试环境
OS：CentOS6.5
Mysql：5.6.28
Mha：0.56
192.168.1.21mysql1M1
192.168.1.22mysql2S1
192.168.1.23mysql3S2Mhamanage、Binlogserver
2：配置/etc/my.cnf相关参数,在3各节点中分别配置
binlog-format=ROW log-slave-updates=true gtid-mode=on enforce-gtid-consistency=true master-info-repository=TABLE relay-log-info-repository=TABLE sync-master-info=1 slave-parallel-workers=2 binlog-checksum=CRC32 master-verify-checksum=1 slave-sql-verify-checksum=1 binlog-rows-query-log_events=1
设置root密码，创建复制用户：
mysql>usemysql;
mysql>GRANTALLPRIVILEGESON*.*TOroot@"%"IDENTIFIEDBY"oracle123";
mysql>updateusersetPassword=password('oracle123')whereUser='root';
mysql>flushprivileges;
mysql>GRANTreplicationslaveON*.*TO'repl'@'%'identifiedby'oracle';
mysql>flushprivileges;
3：在mysql2、mysql3配置Gtid复制
CHANGEMASTERTO MASTER_HOST='192.168.1.21', MASTER_PORT=3306, MASTER_USER='repl', MASTER_PASSWORD='oracle', MASTER_AUTO_POSITION=1; startslave; mysql>showslavestatus\G ***************************1.row*************************** Slave_IO_State:Waitingformastertosendevent Master_Host:192.168.1.21 Master_User:repl Master_Port:3306 Connect_Retry:60 Master_Log_File:mysql-bin.000003 Read_Master_Log_Pos:524 Relay_Log_File:mysql-relay-bin.000002 Relay_Log_Pos:734 Relay_Master_Log_File:mysql-bin.000003 Slave_IO_Running:Yes Slave_SQL_Running:Yes Replicate_Do_DB: ...... Master_SSL_Crlpath: Retrieved_Gtid_Set:9ee7c7af-cbf3-11e5-bf75-000c2923e459:1-2 Executed_Gtid_Set:9ee7c7af-cbf3-11e5-bf75-000c2923e459:1-2 Auto_Position:1 1rowinset(0.00sec)
4：安装Mha
rpm-Uvhepel-release-6-8.noarch.rpm
配置SSH等效：
在所有节点都执行
ssh-keygen-trsa ssh-copy-id-i/root/.ssh/id_rsa.pubroot@mysql1 ssh-copy-id-i/root/.ssh/id_rsa.pubroot@mysql2 ssh-copy-id-i/root/.ssh/id_rsa.pubroot@mysql3
测试ssh登录，在3各节点分别测试：
sshmyqsl1 sshmyqsl2 sshmyqsl3
binlogserver配置：在mysql3
mkdir-p/mysql/backup/binlog /usr/local/mysql/bin/mysqlbinlog-R--raw--host=192.168.1.20--user='root'--password='oracle123'--stop-nevermysql- bin.000003&
最后那个binlog文件时给定从那个binlog文件开始。另外需要注意，当mysql1上的mysql进程退出后，binlogserver也会退出。
需要安装一些包做支持，使用yum网络源；如安装遇到问题可以尝试yumupdate更新yum源或yumcleanall清除缓存
在每个节点安装mha4mysql-node
yum-yinstallperl-DBD-MySQLncftp
rpm-Uvhmha4mysql-node-0.56-0.el6.noarch.rpm
在mysql3上安装mha-manager
yuminstallperl yuminstallcpan yuminstallperl-Config-Tiny yuminstallperl-Time-HiRes yuminstallperl-Log-Dispatch yuminstallperl-Parallel-ForkManager
如果安装perl-Log-Dispatch，perl-Parallel-ForkManager安装包报错：
需要先安装epel(可以参考https://fedoraproject.org/wiki/EPEL）
rpm-Uvhmha4mysql-manager-0.56-0.el6.noarch.rpm
5：配置Mha,在mysql3
mkdir-p/etc/masterha/app1
vi/etc/masterha/app1.cnf
[serverdefault]
user=root
password=oracle123
manager_workdir=/etc/masterha/app1
manager_log=/etc/masterha/app1/manager.log
remote_workdir=/etc/masterha/app1
ssh_user=root
repl_user=repluser
repl_password=oracle
ping_interval=3
master_ip_failover_script=/etc/masterha/app1/master_ip_failover
[server1]
hostname=192.168.1.21
#ssh_port=9999
master_binlog_dir=/mysql/logs
check_repl_delay=0#防止master故障时候，切换时slave有延迟，可在那里切不过来
candidate_master=1
[server2]
hostname=192.168.1.22
#ssh_port=9999
master_binlog_dir=/mysql/logs
candidate_master=1
[server3]
hostname=192.168.1.23
#ssh_port=9999
master_binlog_dir=/mysql/logs
no_master=1
ignore_fail=1#如果这个节点挂了，mha将不可用，加上这个参数slave挂了一样可以用
[binlog1]#binlogserver需要mysqlbinlog命令
hostname=192.168.1.23
master_binlog_dir=/mysql/backup/binlog#读取binlog存放位置
ignore_fail=1
no_master=1
vi/etc/masterha/app1/master_ip_failover
#!/usr/bin/envperl
usestrict;
usewarningsFATAL=>'all';
useGetopt::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='192.168.1.20';#VirtualIP
my$gateway='192.168.1.1';#GatewayIP
my$interface='eth0';
my$key="1";
my$ssh_start_vip="/sbin/ifconfig$interface:$key$vip;/sbin/arping-I$interface-c3-s$vip$gateway>/dev/null2>&1";
my$ssh_stop_vip="/sbin/ifconfig$interface:$keydown";
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();
submain{
print"\n\nINSCRIPTTEST====$ssh_stop_vip==$ssh_start_vip===\n\n";
if($commandeq"stop"||$commandeq"stopssh"){
#$orig_master_host,$orig_master_ip,$orig_master_portarepassed.
#Ifyoumanagemasteripaddressatglobalcatalogdatabase,
#invalidateorig_master_iphere.
my$exit_code=1;
eval{
print"DisablingtheVIPonoldmaster:$orig_master_host\n";
&stop_vip();
$exit_code=0;
};
if($@){
warn"GotError:$@\n";
exit$exit_code;
}
exit$exit_code;
}
elsif($commandeq"start"){
#allargumentsarepassed.
#Ifyoumanagemasteripaddressatglobalcatalogdatabase,
#activatenew_master_iphere.
#Youcanalsograntwriteaccess(createuser,setread_only=0,etc)here.
my$exit_code=10;
eval{
print"EnablingtheVIP-$viponthenewmaster-$new_master_host\n";
&start_vip();
$exit_code=0;
};
if($@){
warn$@;
exit$exit_code;
}
exit$exit_code;
}
elsif($commandeq"status"){
print"CheckingtheStatusofthescript..OK\n";
`ssh$ssh_user\@$orig_master_host\"$ssh_start_vip\"`;
exit0;
}
else{
&usage();
exit1;
}
}
#AsimplesystemcallthatenabletheVIPonthenewmaster
substart_vip(){
`ssh$ssh_user\@$new_master_host\"$ssh_start_vip\"`;
}
#AsimplesystemcallthatdisabletheVIPontheold_master
substop_vip(){
`ssh$ssh_user\@$orig_master_host\"$ssh_stop_vip\"`;
}
subusage{
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";
}
chmod777/etc/masterha/app1/
配置文件测试：
#masterha_check_ssh--conf=/etc/masterha/app1.cnf ThuMay2623:25:352016-[warning]Globalconfigurationfile/etc/masterha_default.cnfnotfound.Skipping. ThuMay2623:25:352016-[info]Readingapplicationdefaultconfigurationfrom/etc/masterha/app1.cnf.. ThuMay2623:25:352016-[info]Readingserverconfigurationfrom/etc/masterha/app1.cnf.. ThuMay2623:25:352016-[info]StartingSSHconnectiontests.. ThuMay2623:25:352016-[debug] ThuMay2623:25:352016-[debug]ConnectingviaSSHfromroot@192.168.1.21(192.168.1.21:22)toroot@192.168.1.22(192.168.1.22:22).. ThuMay2623:25:352016-[debug]ok. ThuMay2623:25:352016-[debug]ConnectingviaSSHfromroot@192.168.1.21(192.168.1.21:22)toroot@192.168.1.23(192.168.1.23:22).. ThuMay2623:25:352016-[debug]ok. ThuMay2623:25:362016-[debug] ThuMay2623:25:352016-[debug]ConnectingviaSSHfromroot@192.168.1.22(192.168.1.22:22)toroot@192.168.1.21(192.168.1.21:22).. ThuMay2623:25:352016-[debug]ok. ThuMay2623:25:352016-[debug]ConnectingviaSSHfromroot@192.168.1.22(192.168.1.22:22)toroot@192.168.1.23(192.168.1.23:22).. ThuMay2623:25:362016-[debug]ok. ThuMay2623:25:362016-[debug] ThuMay2623:25:362016-[debug]ConnectingviaSSHfromroot@192.168.1.23(192.168.1.23:22)toroot@192.168.1.21(192.168.1.21:22).. ThuMay2623:25:362016-[debug]ok. ThuMay2623:25:362016-[debug]ConnectingviaSSHfromroot@192.168.1.23(192.168.1.23:22)toroot@192.168.1.22(192.168.1.22:22).. ThuMay2623:25:362016-[debug]ok. ThuMay2623:25:362016-[info]AllSSHconnectiontestspassedsuccessfully. #masterha_check_repl--conf=/etc/masterha/app1.cnf ThuMay2622:52:302016-[warning]Globalconfigurationfile/etc/masterha_default.cnfnotfound.Skipping. ThuMay2622:52:302016-[info]Readingapplicationdefaultconfigurationfrom/etc/masterha/app1.cnf.. ThuMay2622:52:302016-[info]Readingserverconfigurationfrom/etc/masterha/app1.cnf.. ThuMay2622:52:302016-[info]MHA::MasterMonitorversion0.56. ThuMay2622:52:312016-[info]GTIDfailovermode=1 ThuMay2622:52:312016-[info]DeadServers: ThuMay2622:52:312016-[info]AliveServers: ThuMay2622:52:312016-[info]192.168.1.21(192.168.1.21:3306) ThuMay2622:52:312016-[info]192.168.1.22(192.168.1.22:3306) ThuMay2622:52:312016-[info]192.168.1.23(192.168.1.23:3306) ThuMay2622:52:312016-[info]AliveSlaves: ThuMay2622:52:312016-[info]192.168.1.22(192.168.1.22:3306)Version=5.6.28-log(oldestmajorversionbetweenslaves)log-bin:enabled ThuMay2622:52:312016-[info]GTIDON ThuMay2622:52:312016-[info]Replicatingfrom192.168.1.21(192.168.1.21:3306) ThuMay2622:52:312016-[info]PrimarycandidateforthenewMaster(candidate_masterisset) ThuMay2622:52:312016-[info]192.168.1.23(192.168.1.23:3306)Version=5.6.28-log(oldestmajorversionbetweenslaves)log-bin:enabled ThuMay2622:52:312016-[info]GTIDON ThuMay2622:52:312016-[info]Replicatingfrom192.168.1.21(192.168.1.21:3306) ThuMay2622:52:312016-[info]NotcandidateforthenewMaster(no_masterisset) ThuMay2622:52:312016-[info]CurrentAliveMaster:192.168.1.21(192.168.1.21:3306) ThuMay2622:52:312016-[info]Checkingslaveconfigurations.. ThuMay2622:52:312016-[info]read_only=1isnotsetonslave192.168.1.22(192.168.1.22:3306). ThuMay2622:52:312016-[info]read_only=1isnotsetonslave192.168.1.23(192.168.1.23:3306). ThuMay2622:52:312016-[info]Checkingreplicationfilteringsettings.. ThuMay2622:52:312016-[info]binlog_do_db=,binlog_ignore_db= ThuMay2622:52:312016-[info]Replicationfilteringcheckok. ThuMay2622:52:312016-[info]GTID(withauto-pos)issupported.SkippingallSSHandNodepackagechecking. ThuMay2622:52:312016-[info]HealthCheck:SSHto192.168.1.23isreachable. ThuMay2622:52:312016-[info]Binlogserver192.168.1.23isreachable. ThuMay2622:52:312016-[info]Checkingrecoveryscriptconfigurationson192.168.1.23(192.168.1.23:3306).. ThuMay2622:52:312016-[info]Executingcommand:save_binary_logs--command=test--start_pos=4--binlog_dir=/mysql/backup/binlog--output_file=/etc/masterha/app1/save_binary_logs_test--manager_version=0.56--start_file=mysql-bin.000004 ThuMay2622:52:312016-[info]Connectingtoroot@192.168.1.23(192.168.1.23:22).. Creating/etc/masterha/app1ifnotexists..ok. Checkingoutputdirectoryisaccessibleornot.. ok. Binlogfoundat/mysql/backup/binlog,uptomysql-bin.000004 ThuMay2622:52:312016-[info]Binlogsettingcheckdone. ThuMay2622:52:312016-[info]CheckingSSHpublickeyauthenticationsettingsonthecurrentmaster.. ThuMay2622:52:312016-[info]HealthCheck:SSHto192.168.1.21isreachable. ThuMay2622:52:312016-[info] 192.168.1.21(192.168.1.21:3306)(currentmaster) +--192.168.1.22(192.168.1.22:3306) +--192.168.1.23(192.168.1.23:3306) ThuMay2622:52:312016-[info]Checkingreplicationhealthon192.168.1.22.. ThuMay2622:52:312016-[info]ok. ThuMay2622:52:312016-[info]Checkingreplicationhealthon192.168.1.23.. ThuMay2622:52:312016-[info]ok. ThuMay2622:52:312016-[info]Checkingmaster_ip_failover_scriptstatus: ThuMay2622:52:312016-[info]/etc/masterha/app1/master_ip_failover--command=status--ssh_user=root--orig_master_host=192.168.1.21--orig_master_ip=192.168.1.21--orig_master_port=3306 INSCRIPTTEST====/sbin/ifconfigeth1:1down==/sbin/ifconfigeth1:1192.168.1.20;/sbin/arping-Ieth1-c3-s192.168.1.20192.168.1.1>/dev/null2>&1=== CheckingtheStatusofthescript..OK ThuMay2622:52:342016-[info]OK. ThuMay2622:52:342016-[warning]shutdown_scriptisnotdefined. ThuMay2622:52:342016-[info]Gotexitcode0(Notmasterdead). MySQLReplicationHealthisOK.
MHA启动及关闭
nohupmasterha_manager--conf=/etc/masterha/app1.cnf>/etc/masterha/app1/manager.log&1&
检查是否启动：
masterha_check_status--conf=/etc/masterha/app1.cnf app1(pid:11447)isrunning(0:PING_OK),master:192.168.1.21
停止Mha：
masterha_stop--conf=/etc/masterha/app1.cnf Stoppedapp1successfully. [3]+Exit1nohupmasterha_manager--conf=/etc/masterha/app1.cnf>/etc/masterha/app1/manager.log&1
测试：
说明，每次测试完成后，需要清理/etc/masterha/app1下的日志，然后启动Mhamanager.
1：关闭mysql1上的mysql，查看从库从那里同步，以及mha日志输出
2：恢复mysql1为mysql2的slave，changemaster语句可以在/etc/masterha/app1/manager.log里找到。
在配置GTID复制时候遇到1032错误，用以下方法解决
mysql>showglobalvariableslike'%gtid%'; +---------------------------------+------------------------------------------------------------------------------------+ |Variable_name|Value| +---------------------------------+------------------------------------------------------------------------------------+ |binlog_gtid_simple_recovery|OFF| |enforce_gtid_consistency|ON| |gtid_executed|88b05570-2599-11e6-880a-000c29c18cf5:1-3, 9ee7c7af-cbf3-11e5-bf75-000c2923e459:1-4| |gtid_mode|ON| |gtid_owned|| |gtid_purged|| |simplified_binlog_gtid_recovery|OFF| +---------------------------------+------------------------------------------------------------------------------------+ stopslave; setgtid_next='9ee7c7af-cbf3-11e5-bf75-000c2923e459:4'; begin; commit; setgtid_next='automatic'; startslave; showslavestatus\G;
以上这篇MysqlGTIDMha配置方法就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持毛票票。