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配置方法就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持毛票票。