Mysql GTID Mha配置方法
發(fā)布日期：2022-06-09 11:16 | 文章來(lái)源：CSDN
Gtid + Mha +Binlog server配置：
1：測(cè)試環(huán)境
OS：CentOS 6.5
Mysql：5.6.28
Mha：0.56
192.168.1.21 mysql1 M1
192.168.1.22 mysql2 S1
192.168.1.23 mysql3 S2 Mha manage、Binlog server
2：配置/etc/my.cnf相關(guān)參數(shù),在3各節(jié)點(diǎn)中分別配置
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
設(shè)置root密碼，創(chuàng)建復(fù)制用戶：
mysql> use mysql;
mysql> GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "oracle123";
mysql> update user set Password = password('oracle123') where User='root';
mysql> flush privileges;
mysql> GRANT replication slave ON *.* TO 'repl'@'%' identified by 'oracle';
mysql> flush privileges;
3：在mysql2、mysql3配置Gtid復(fù)制
CHANGE MASTER TO
MASTER_HOST = '192.168.1.21',
MASTER_PORT = 3306,
MASTER_USER = 'repl',
MASTER_PASSWORD = 'oracle',
MASTER_AUTO_POSITION = 1;
start slave;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
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
1 row in set (0.00 sec)
4：安裝Mha
rpm -Uvh epel-release-6-8.noarch.rpm
配置SSH等效：
在所有節(jié)點(diǎn)都執(zhí)行
ssh-keygen -t rsa ssh-copy-id -i /root/.ssh/id_rsa.pub root@mysql1 ssh-copy-id -i /root/.ssh/id_rsa.pub root@mysql2 ssh-copy-id -i /root/.ssh/id_rsa.pub root@mysql3
測(cè)試ssh登錄，在3各節(jié)點(diǎn)分別測(cè)試：
ssh myqsl1 ssh myqsl2 ssh myqsl3
binlog server配置：在mysql3
mkdir -p /mysql/backup/binlog /usr/local/mysql/bin/mysqlbinlog -R --raw --host=192.168.1.20 --user='root' --password='oracle123' --stop-never mysql- bin.000003 &
最后那個(gè)binlog文件時(shí)給定從那個(gè)binlog文件開(kāi)始。另外需要注意，當(dāng)mysql1上的mysql進(jìn)程退出后，binlog server也會(huì)退出。
需要安裝一些包做支持，使用yum網(wǎng)絡(luò)源；如安裝遇到問(wèn)題可以嘗試yum update更新yum源或yum clean all清除緩存
在每個(gè)節(jié)點(diǎn)安裝 mha4mysql-node
yum -y install perl-DBD-MySQL ncftp
rpm -Uvh mha4mysql-node-0.56-0.el6.noarch.rpm
在mysql3上安裝mha-manager
yum install perl yum install cpan yum install perl-Config-Tiny yum install perl-Time-HiRes yum install perl-Log-Dispatch yum install perl-Parallel-ForkManager
如果安裝perl-Log-Dispatch，perl-Parallel-ForkManager安裝包報(bào)錯(cuò)：
需要先安裝epel(可以參考https://fedoraproject.org/wiki/EPEL）
rpm -Uvh mha4mysql-manager-0.56-0.el6.noarch.rpm
5：配置Mha,在mysql3
mkdir -p /etc/masterha/app1
vi /etc/masterha/app1.cnf
[server default]
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故障時(shí)候，切換時(shí)slave有延遲，可在那里切不過(guò)來(lái)
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 #如果這個(gè)節(jié)點(diǎn)掛了，mha將不可用，加上這個(gè)參數(shù)slave掛了一樣可以用
[binlog1] #binlog server需要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/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
);
my $vip = '192.168.1.20';#Virtual IP
my $gateway = '192.168.1.1';#Gateway IP
my $interface = 'eth0';
my $key = "1";
my $ssh_start_vip = "/sbin/ifconfig $interface:$key $vip;/sbin/arping -I $interface -c 3 -s $vip $gateway >/dev/null 2>&1";
my $ssh_stop_vip = "/sbin/ifconfig $interface:$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" ) {
# $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 {
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" ) {
# 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();
$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;
}
}
# A simple system call that enable the VIP on the new master
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";
}
chmod 777 /etc/masterha/app1/
配置文件測(cè)試：
# masterha_check_ssh --conf=/etc/masterha/app1.cnf Thu May 26 23:25:35 2016 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Thu May 26 23:25:35 2016 - [info] Reading application default configuration from /etc/masterha/app1.cnf.. Thu May 26 23:25:35 2016 - [info] Reading server configuration from /etc/masterha/app1.cnf.. Thu May 26 23:25:35 2016 - [info] Starting SSH connection tests.. Thu May 26 23:25:35 2016 - [debug] Thu May 26 23:25:35 2016 - [debug] Connecting via SSH from root@192.168.1.21(192.168.1.21:22) to root@192.168.1.22(192.168.1.22:22).. Thu May 26 23:25:35 2016 - [debug] ok. Thu May 26 23:25:35 2016 - [debug] Connecting via SSH from root@192.168.1.21(192.168.1.21:22) to root@192.168.1.23(192.168.1.23:22).. Thu May 26 23:25:35 2016 - [debug] ok. Thu May 26 23:25:36 2016 - [debug] Thu May 26 23:25:35 2016 - [debug] Connecting via SSH from root@192.168.1.22(192.168.1.22:22) to root@192.168.1.21(192.168.1.21:22).. Thu May 26 23:25:35 2016 - [debug] ok. Thu May 26 23:25:35 2016 - [debug] Connecting via SSH from root@192.168.1.22(192.168.1.22:22) to root@192.168.1.23(192.168.1.23:22).. Thu May 26 23:25:36 2016 - [debug] ok. Thu May 26 23:25:36 2016 - [debug] Thu May 26 23:25:36 2016 - [debug] Connecting via SSH from root@192.168.1.23(192.168.1.23:22) to root@192.168.1.21(192.168.1.21:22).. Thu May 26 23:25:36 2016 - [debug] ok. Thu May 26 23:25:36 2016 - [debug] Connecting via SSH from root@192.168.1.23(192.168.1.23:22) to root@192.168.1.22(192.168.1.22:22).. Thu May 26 23:25:36 2016 - [debug] ok. Thu May 26 23:25:36 2016 - [info] All SSH connection tests passed successfully. #masterha_check_repl --conf=/etc/masterha/app1.cnf Thu May 26 22:52:30 2016 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Thu May 26 22:52:30 2016 - [info] Reading application default configuration from /etc/masterha/app1.cnf.. Thu May 26 22:52:30 2016 - [info] Reading server configuration from /etc/masterha/app1.cnf.. Thu May 26 22:52:30 2016 - [info] MHA::MasterMonitor version 0.56. Thu May 26 22:52:31 2016 - [info] GTID failover mode = 1 Thu May 26 22:52:31 2016 - [info] Dead Servers: Thu May 26 22:52:31 2016 - [info] Alive Servers: Thu May 26 22:52:31 2016 - [info] 192.168.1.21(192.168.1.21:3306) Thu May 26 22:52:31 2016 - [info] 192.168.1.22(192.168.1.22:3306) Thu May 26 22:52:31 2016 - [info] 192.168.1.23(192.168.1.23:3306) Thu May 26 22:52:31 2016 - [info] Alive Slaves: Thu May 26 22:52:31 2016 - [info] 192.168.1.22(192.168.1.22:3306) Version=5.6.28-log (oldest major version between slaves) log-bin:enabled Thu May 26 22:52:31 2016 - [info] GTID ON Thu May 26 22:52:31 2016 - [info] Replicating from 192.168.1.21(192.168.1.21:3306) Thu May 26 22:52:31 2016 - [info] Primary candidate for the new Master (candidate_master is set) Thu May 26 22:52:31 2016 - [info] 192.168.1.23(192.168.1.23:3306) Version=5.6.28-log (oldest major version between slaves) log-bin:enabled Thu May 26 22:52:31 2016 - [info] GTID ON Thu May 26 22:52:31 2016 - [info] Replicating from 192.168.1.21(192.168.1.21:3306) Thu May 26 22:52:31 2016 - [info] Not candidate for the new Master (no_master is set) Thu May 26 22:52:31 2016 - [info] Current Alive Master: 192.168.1.21(192.168.1.21:3306) Thu May 26 22:52:31 2016 - [info] Checking slave configurations.. Thu May 26 22:52:31 2016 - [info] read_only=1 is not set on slave 192.168.1.22(192.168.1.22:3306). Thu May 26 22:52:31 2016 - [info] read_only=1 is not set on slave 192.168.1.23(192.168.1.23:3306). Thu May 26 22:52:31 2016 - [info] Checking replication filtering settings.. Thu May 26 22:52:31 2016 - [info] binlog_do_db= , binlog_ignore_db= Thu May 26 22:52:31 2016 - [info] Replication filtering check ok. Thu May 26 22:52:31 2016 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking. Thu May 26 22:52:31 2016 - [info] HealthCheck: SSH to 192.168.1.23 is reachable. Thu May 26 22:52:31 2016 - [info] Binlog server 192.168.1.23 is reachable. Thu May 26 22:52:31 2016 - [info] Checking recovery script configurations on 192.168.1.23(192.168.1.23:3306).. Thu May 26 22:52:31 2016 - [info] Executing command: 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 Thu May 26 22:52:31 2016 - [info] Connecting to root@192.168.1.23(192.168.1.23:22).. Creating /etc/masterha/app1 if not exists.. ok. Checking output directory is accessible or not.. ok. Binlog found at /mysql/backup/binlog, up to mysql-bin.000004 Thu May 26 22:52:31 2016 - [info] Binlog setting check done. Thu May 26 22:52:31 2016 - [info] Checking SSH publickey authentication settings on the current master.. Thu May 26 22:52:31 2016 - [info] HealthCheck: SSH to 192.168.1.21 is reachable. Thu May 26 22:52:31 2016 - [info] 192.168.1.21(192.168.1.21:3306) (current master) +--192.168.1.22(192.168.1.22:3306) +--192.168.1.23(192.168.1.23:3306) Thu May 26 22:52:31 2016 - [info] Checking replication health on 192.168.1.22.. Thu May 26 22:52:31 2016 - [info] ok. Thu May 26 22:52:31 2016 - [info] Checking replication health on 192.168.1.23.. Thu May 26 22:52:31 2016 - [info] ok. Thu May 26 22:52:31 2016 - [info] Checking master_ip_failover_script status: Thu May 26 22:52:31 2016 - [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 IN SCRIPT TEST====/sbin/ifconfig eth1:1 down==/sbin/ifconfig eth1:1 192.168.1.20;/sbin/arping -I eth1 -c 3 -s 192.168.1.20 192.168.1.1 >/dev/null 2>&1=== Checking the Status of the script.. OK Thu May 26 22:52:34 2016 - [info] OK. Thu May 26 22:52:34 2016 - [warning] shutdown_script is not defined. Thu May 26 22:52:34 2016 - [info] Got exit code 0 (Not master dead). MySQL Replication Health is OK.
MHA啟動(dòng)及關(guān)閉
nohup masterha_manager --conf=/etc/masterha/app1.cnf > /etc/masterha/app1/manager.log < /dev/null 2>&1 &
檢查是否啟動(dòng)：
masterha_check_status --conf=/etc/masterha/app1.cnf app1 (pid:11447) is running(0:PING_OK), master:192.168.1.21
停止Mha：
masterha_stop --conf=/etc/masterha/app1.cnf Stopped app1 successfully. [3]+ Exit 1 nohup masterha_manager --conf=/etc/masterha/app1.cnf > /etc/masterha/app1/manager.log < /dev/null 2>&1
測(cè)試：
說(shuō)明，每次測(cè)試完成后，需要清理/etc/masterha/app1下的日志，然后啟動(dòng)Mha manager.
1：關(guān)閉mysql1上的mysql，查看從庫(kù)從那里同步，以及mha日志輸出
2：恢復(fù)mysql1為mysql2的slave，change master語(yǔ)句可以在/etc/masterha/app1/manager.log里找到。
在配置GTID復(fù)制時(shí)候遇到 1032錯(cuò)誤，用以下方法解決
mysql> show global variables like '%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 | +---------------------------------+------------------------------------------------------------------------------------+ stop slave; set gtid_next='9ee7c7af-cbf3-11e5-bf75-000c2923e459:4'; begin; commit; set gtid_next='automatic'; start slave; show slave status\G;
以上這篇Mysql GTID Mha配置方法就是小編分享給大家的全部?jī)?nèi)容了，希望能給大家一個(gè)參考，也希望大家多多支持本站。
版權(quán)聲明：本站文章來(lái)源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請(qǐng)保持原文完整并注明來(lái)源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來(lái)源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來(lái)，僅供學(xué)習(xí)參考，不代表本站立場(chǎng)，如有內(nèi)容涉嫌侵權(quán)，請(qǐng)聯(lián)系alex-e#qq.com處理。
相關(guān)文章
關(guān)注官方微信