Mysql GTID Mha configuration method, mysqlgtidmha Configuration

Mysql GTID Mha configuration method, mysqlgtidmha Configuration

Gtid + Mha + Binlog server Configuration:

1: Test Environment

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: configure the/etc/my. cnf parameters and configure them in each node of 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 

Set the root password and create a copy user:

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: Configure Gtid replication in mysql2 and mysql3

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: 11 row in set (0.00 sec)

4. Install Mha

Rpm-Uvh epel-release-6-8.noarch.rpm

Equivalent to configuring SSH:

Run on all nodes

ssh-keygen -t rsassh-copy-id -i /root/.ssh/id_rsa.pub root@mysql1ssh-copy-id -i /root/.ssh/id_rsa.pub root@mysql2ssh-copy-id -i /root/.ssh/id_rsa.pub root@mysql3

Test ssh logon. Test on each of the three nodes:

ssh myqsl1ssh myqsl2ssh myqsl3

Binlog server configuration: In 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 &

The last binlog file is given starting from the binlog file. In addition, when the mysql process on mysql1 exits, the binlog server also exits.

You need to install some packages for support and use the yum network source. If Installation encounters problems, you can try yum update to update the yum source or yum clean all to clear the cache.

Install mha4mysql-node on each node

Yum-y install perl-DBD-MySQL ncftp
Rpm-Uvh mha4mysql-node-0.56-0.el6.noarch.rpm

Install mha-manager on mysql3

yum install perlyum install cpanyum install perl-Config-Tinyyum install perl-Time-HiRes yum install perl-Log-Dispatchyum install perl-Parallel-ForkManager

If you install perl-Log-Dispatch and perl-Parallel-ForkManager, the following error occurs:

Epel needs to be installed first (see https://fedoraproject.org/wiki/EPEL)

Rpm-Uvh mha4mysql-manager-0.56-0.el6.noarch.rpm

5: Configure Mha in mysql3

Mkdir-p/etc/masterha/app1vi/etc/masterha/app1.cnf [server default] user = root password = oracle123manager_workdir =/etc/masterha/app1manager_log =/etc/masterha/app1/ manager. logremote_workdir =/etc/masterha/login = rootrepl_user = repluserrepl_password = oracleping_interval = login =/etc/masterha/app1/login [server1] hostname = 192.168.1.21 # ssh_port = login =/mysq L/logscheck_repl_delay = 0 # slave delay during master failover, candidate_master = 1 [server2] hostname = 192.168.1.22 # ssh_port = 9999master_binlog_dir =/mysql/logscandidate_master = 1 [server3] hostname = 192.168.1.23 # ssh_port = login =/mysql/ logsno_master = 1ignore_fail = 1 # If this node fails, mha will be unavailable. You can use [binlog1] # binlog server to use the mysqlbinlog command hostname = 192.168.1.23master _ binlog_dir =/mysql/backup/binlog when the slave parameter is disabled. # Read the binlog storage location ignore_fail = 1no_master = 1vi/etc/masterha/app1/master_ip_failover #! /Usr/bin/env perluse 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 = '2017. 168.1.20 '; # Virtual IPmy $ gateway = '2017. 168.1.1 '; # Gateway IPmy $ 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;} 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;} 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 mastersub start_vip () {'ssh $ ssh_user \@$ new_master_host \ "$ ssh_start_vip \"';} # A simple system call that disable the VIP on the old_mastersub stop_vip () {'ssh $ ssh_user \@$ orig_master_host \ "$ ssh_stop_vip \" ';} sub usage {print "Usage: restart -- 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/

Configuration File test:

# masterha_check_ssh --conf=/etc/masterha/app1.cnfThu 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.cnfThu 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 = 1Thu 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:enabledThu May 26 22:52:31 2016 - [info]   GTID ONThu 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:enabledThu May 26 22:52:31 2016 - [info]   GTID ONThu 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.000004Thu 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 startup and Shutdown

Nohup masterha_manager -- conf =/etc/masterha/app1.cnf>/etc/masterha/app1/manager. log </dev/null 2> & 1 &

Check whether startup is Enabled:

masterha_check_status --conf=/etc/masterha/app1.cnfapp1 (pid:11447) is running(0:PING_OK), master:192.168.1.21

Stop Mha:

masterha_stop --conf=/etc/masterha/app1.cnfStopped app1 successfully.[3]+ Exit 1         nohup masterha_manager --conf=/etc/masterha/app1.cnf > /etc/masterha/app1/manager.log < /dev/null 2>&1

Test:

Note: after each test, you need to clear the logs under/etc/masterha/app1 and start Mha manager.

1: Disable mysql on mysql1, view slave Database Synchronization and mha log output

2: Restore the slave where mysql1 is mysql2. The change master statement can be found in/etc/masterha/app1/manager. log.

1032 error occurs when GTID replication is configured. Use the following methods to solve this problem:

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; 

The above Mysql GTID Mha configuration method is a small part of the Content shared to everyone, I hope to give you a reference, but also hope you can support a lot of help homes.