Shulou(Shulou.com)05/31 Report--

This article mainly introduces the configuration and switching mode of MHA in mysql. It is very detailed and has certain reference value. If you are interested, you must finish it.

Master node / MHA management node: 172.31.217.183

Slave node / MHA member node: 172.31.217.182

Semi-sync is turned on.

Database version 5.7

Configure password-free login

Master node:

Root@bd-dev-mingshuo-183:/opt/soft#ssh-keygen-t rsa

Generating public/private rsa key pair.

Enter file in which to save the key (/ root/.ssh/id_rsa):

Enter passphrase (empty for no passphrase):

Enter same passphrase again:

Your identification has been saved in / root/.ssh/id_rsa.

Your public key has been saved in / root/.ssh/id_rsa.pub.

The key fingerprint is:

36:39:6b:1e:40:f2:85:31:db:d0:3e:ab:05:0e:fd:37 root@bd-dev-mingshuo-183

The key's randomart image is:

+-[RSA 2048]-+

| +. | |

| | B. |

| |.. + .o |

|. + o.o. | |

| | oooSo |

| | .o + + E |

| oval. . | |

| .o. | |

|. | |

+-+

Root@bd-dev-mingshuo-183:/opt/soft#ssh-copy-id-I / root/.ssh/id_rsa.pub root@172.31.217.182

Root@172.31.217.182's password:

Now try logging into the machine, with "ssh 'root@172.31.217.182'", and check in:

.ssh / authorized_keys

To make sure we haven't added extra keys that you weren't expecting.

Root@bd-dev-mingshuo-183:/u01#ssh-copy-id-I / root/.ssh/id_rsa.pub root@172.31.217.183

Root@172.31.217.183's password:

Now try logging into the machine, with "ssh 'root@172.31.217.183'", and check in:

.ssh / authorized_keys

To make sure we haven't added extra keys that you weren't expecting.

Slave node:

Ssh-keygen-t rsa

Ssh-copy-id-I / root/.ssh/id_rsa.pub root@172.31.217.183

Ssh-copy-id-I / root/.ssh/id_rsa.pub root@172.31.217.182

Slave node:

Mysql > set global read_only=1

Query OK, 0 rows affected (0.00 sec)

Mysql > show variables like 'read_only'\ G

* * 1. Row *

Variable_name: read_only

Value: ON

1 row in set (0.00 sec)

A read_only of 1 means read-only, and 0 means read and write. Read-only from the library does not affect the log application of slave. However, do not write parameters to the parameter file, because it is possible that when the slave is switched to master, ordinary users will not be able to write. Of course, this parameter is optional during the configuration of mha.

Deploy the installation package

The manager node installs the manager package

Install the node package on all nodes

Install the node package first

Rpm-ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm

Yum install mha4mysql-manager-0.58-0.el7.centos.noarch.rpm

Create a mha management account on master

Grant all privileges on *. * to mha@'172.31.217.%' identified by 'oracle'

Flush privileges

Create a directory to store mha configuration files and mha logs

Mkdir-p / u01/mha/log

Chown mysql.mysql-R mha

Edit configuration file

Vi / u01/mha/mha.cnf

[server default]

Manager_log=/u01/mha/log/manager.log

Manager_workdir=/u01/mha/log

Master_binlog_dir=/u01/mysql/3306/data

User=mha

Password=oracle

Ping_interval=2

Repl_user=repl_user

Repl_password=oracle

Ssh_user=root

[server1]

Hostname=172.31.217.183

Port=3306

[server2]

Hostname=172.31.217.182

Port=3306

Optional parameters for profile:

[server default] module:

Ping_interval=1 / / sets the monitoring master library. The time interval for sending ping packets is 3 seconds by default. Railover is performed automatically when there is no response after three attempts.

Remote_workdir=/tmp / / sets the location where the remote mysql will be saved when the switch occurs

Report_script=/usr/local/send_report / / sets the script for alarms sent after a switch occurs

Shutdown_script= "/ / set up to close the failed host script (the main function of this script is to shut down the host and put it in the event of a brain fissure, which is not used here) from the library module:

Candidate_master=1 / / is set as a candidate master. If this parameter is set, the slave library will be promoted to the master library after the master-slave switch occurs, even if the master library is not the latest slave of the event in the cluster.

Check_repl_delay=0 / / by default, if a slave lags behind the relay logs of master 100m, MHA will not select the slave as a new master, because the recovery of this slave will take a long time. Setting check_repl_delay=0,MHA to trigger the switch will ignore the replication delay when selecting a new master. This parameter is very useful for hosts with candidate_master=1 set. Because this candidate must be the new master during the handover.

Detect synchronization and ssh login

Masterha_check_ssh-conf=/u01/mha/mha.cnf

Masterha_check_repl-conf=/u01/mha/mha.cnf

Many mistakes have been reported in the middle, and part of the solution:

Ln-s / opt/mysql-5.7.23/bin/mysql / usr/bin/mysql

Ln-s / opt/mysql-5.7.23/bin/mysqlbinlog / usr/bin/mysqlbinlog

Uninstall mha4mysql-manager-0.58-0.el7.centos.noarch.rpm and install mha4mysql-manager-0.56-0.el6.noarch.rpm

Start mha

Nohup masterha_manager-- conf=/u01/mha/mha.cnf > / u01/mha/log/manager.log 2 > & 1 &

Check mha status

Root@bd-dev-mingshuo-183:/opt/soft#masterha_check_status-conf=/u01/mha/mha.cnf

Mha (pid:24910) is running (0:PING_OK), master:172.31.217.183 configuration VIP

Add under the server default module

Master_ip_failover_script=/usr/local/bin/master_ip_failover

Copy master_ip_failover from the source package to / usr/local/bin/

Cd / opt/soft/MHAsoft/mha4mysql-manager-0.56/samples/scripts

Cp-ra master_ip_failover / usr/local/bin/master_ip_failover

Modify / usr/local/bin/master_ip_failover

My $vip = '172.31.217.203 ip 24mm; # here is the virtual ip you want to set up

My $key ='1'

My $ssh_start_vip = "/ sbin/ifconfig eth3:$key $vip"; # change the name of your network card here

My $ssh_stop_vip = "/ sbin/ifconfig eth3:$key down"; Note:

My (

$command, $ssh_user, $orig_master_host, $orig_master_ip

$orig_master_port, $new_master_host, $new_master_ip, $new_master_port

);

Add the above to here

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

); configure the network card VIP

Ifconfig eth3:1 172.31.217.203/24

Ifconfig

Eth3 Link encap:Ethernet HWaddr 54:0F:5D:2C:4D:77

Inet addr:172.31.217.202 Bcast:172.31.217.255 Mask:255.255.255.0

Inet6 addr: fe80::560f:5dff:fe2c:4d77/64 Scope:Link

UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1

RX packets:74742667 errors:0 dropped:0 overruns:0 frame:0

TX packets:12 errors:0 dropped:0 overruns:0 carrier:0

Collisions:0 txqueuelen:1000

RX bytes:52680755472 (49.0 GiB) TX bytes:740 (740.0 b)

Eth3:1 Link encap:Ethernet HWaddr 54:0F:5D:2C:4D:77

Inet addr:172.31.217.203 Bcast:172.31.217.255 Mask:255.255.255.0

UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1

Stop mha

Masterha_stop-conf=/u01/mha/mha.cnf

Turn on mha again

Nohup masterha_manager-- conf=/u01/mha/mha.cnf > / u01/mha/log/manager.log 2 > & 1 &

Error report:

Bareword "FIXME_xxx" not allowed while "strict subs" in use at / usr/local/bin/master_ip_failover line 98.

Execution of / usr/local/bin/master_ip_failover aborted due to compilation errors.

Mon Sep 17 10:56:04 2018-[error] [/ usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln226] Failed to get master_ip_failover_script status with return code 255 Mon Sep 0.

Mon Sep 17 10:56:04 2018-[error] [/ usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln424] Error happened on checking configurations. At / usr/bin/masterha_manager line 50

Mon Sep 17 10:56:04 2018-[error] [/ usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln523] Error happened on monitoring servers.

Mon Sep 17 10:56:04 2018-[info] Got exit code 1 (Not master dead).

Just comment out the lines related to FIXME_xxx.

Turn on mha again

Nohup masterha_manager-- conf=/u01/mha/mha.cnf > / u01/mha/log/manager.log 2 > & 1 &

Ok!

Close the main library

Mysqladmin-uroot-poracle shutdown

Check the reserve database

Mysql > show slave status

Empty set (0.00 sec)

Mysql > show master status\ G

* * 1. Row *

File: slave-relay-bin.000002

Position: 154

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

The standby library has been automatically cut into the main library. The mha software on the stopped main library also stopped automatically. Restore the previous master-slave relationship:

If you pull up the stopped master library now, you will find that the master library has not actively joined the cluster.

Query log location of the main database:

Mysql > show master status\ G

* * 1. Row *

File: master-bin.000005

Position: 154

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

Prepare the library:

Change master to

Master_host='bd-dev-mingshuo-183'

Master_port=3306

Master_user='repl_user'

Master_password='oracle'

Master_log_file='master-bin.000005'

Master_log_pos=154

Start slave

The mha software is enabled in the main library. Note that the-ignore_last_failover parameter is added here, otherwise an error will be reported:

Mon Sep 17 14:45:56 2018-[warning] Global configuration file / etc/masterha_default.cnf not found. Skipping.

Mon Sep 17 14:45:56 2018-[info] Reading application default configuration from / u01 Universe mha.cnf..

Mon Sep 17 14:45:56 2018-[info] Reading server configuration from / u01 Universe mha.cnf..

Mon Sep 17 14:45:56 2018-[info] MHA::MasterMonitor version 0.56.

Mon Sep 17 14:45:56 2018-[error] [/ usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln193] There is no alive slave. We can't do failover

Mon Sep 17 14:45:56 2018-[error] [/ usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln424] Error happened on checking configurations. At / usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm line 326

Mon Sep 17 14:45:56 2018-[error] [/ usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln523] Error happened on monitoring servers.

Mon Sep 17 14:45:56 2018-[info] Got exit code 1 (Not master dead).

Open the mha software:

Nohup masterha_manager-ignore_last_failover-- conf=/u01/mha/mha.cnf > / u01/mha/log/manager.log 2 > & 1 &

The above is the process of automatic failover. Let's test the manual failover later.

Stop mha manager:

Masterha_stop-conf=/u01/mha/mha.cnf

Stop the master database

Mysqladmin-uroot-poracle shutdown

Manual switching

Masterha_master_switch-master_state=dead-conf=/u01/mha/mha.cnf-dead_master_host=172.31.217.183-dead_master_port=3306-new_master_host=172.31.217.182-new_master_port=3306-ignore_last_failover

The above is the process of automatic failover, and then we will test the online switching:

Manager node:

Stop mha manager:

Masterha_stop-conf=/u01/mha/mha.cnf

Masterha_master_switch-conf=/u01/mha/mha.cnf-master_state=alive-new_master_host=172.31.217.182-new_master_port=3306-orig_master_is_new_slave-running_updates_limit=100

Mon Sep 17 15:47:29 2018-[info] MHA::MasterRotate version 0.56.

Mon Sep 17 15:47:29 2018-[info] Starting online master switch..

Mon Sep 17 15:47:29 2018-[info]

Mon Sep 17 15:47:29 2018-[info] * Phase 1: Configuration Check Phase..

Mon Sep 17 15:47:29 2018-[info]

Mon Sep 17 15:47:29 2018-[warning] Global configuration file / etc/masterha_default.cnf not found. Skipping.

Mon Sep 17 15:47:29 2018-[info] Reading application default configuration from / u01 Universe mha.cnf..

Mon Sep 17 15:47:29 2018-[info] Reading server configuration from / u01 Universe mha.cnf..

Mon Sep 17 15:47:29 2018-[info] GTID failover mode = 0

Mon Sep 17 15:47:29 2018-[info] Current Alive Master: 172.31.217.183 (172.31.217.183)

Mon Sep 17 15:47:29 2018-[info] Alive Slaves:

Mon Sep 17 15:47:29 2018-[info] 172.31.217.182 (172.31.217.182 Version=5.7.23-log (oldest major version between slaves) log-bin:enabled

Mon Sep 17 15:47:29 2018-[info] Replicating from bd-dev-mingshuo-183 (172.31.217.183 3306)

It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on 172.31.217.183 (172.31.217.183)? (YES/no): YES

Mon Sep 17 15:47:33 2018-[info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take long time..

Mon Sep 17 15:47:33 2018-[info] ok.

Mon Sep 17 15:47:33 2018-[info] Checking MHA is not monitoring or doing failover..

Mon Sep 17 15:47:33 2018-[info] Checking replication health on 172.31.217.182.

Mon Sep 17 15:47:33 2018-[info] ok.

Mon Sep 17 15:47:33 2018-[info] 172.31.217.182 can be new master.

Mon Sep 17 15:47:33 2018-[info]

From:

172.31.217.183 (172.31.217.183) 3306 (current master)

+-- 172.31.217.182 (172.31.217.182) 3306)

To:

172.31.217.182 (172.31.217.182) (new master)

+-- 172.31.217.183 (172.31.217.183) 3306)

Starting master switch from 172.31.217.183 (172.31.217.183) to 172.31.217.182 (172.31.217.182)? (yes/NO): yes

Mon Sep 17 15:47:55 2018-[info] Checking whether 172.31.217.182 (172.31.217.182) is ok for the new master..

Mon Sep 17 15:47:55 2018-[info] ok.

Mon Sep 17 15:47:55 2018-[info] 172.31.217.183 (172.31.217.183): SHOW SLAVE STATUS returned empty result. To check replication filtering rules, temporarily executing CHANGE MASTER to a dummy host.

Mon Sep 17 15:47:55 2018-[info] 172.31.217.183 (172.31.217.183): Resetting slave pointing to the dummy host.

Mon Sep 17 15:47:55 2018-[info] * * Phase 1: Configuration Check Phase completed.

Mon Sep 17 15:47:55 2018-[info]

Mon Sep 17 15:47:55 2018-[info] * Phase 2: Rejecting updates Phase..

Mon Sep 17 15:47:55 2018-[info]

Master_ip_online_change_script is not defined. If you do not disable writes on the current master manually, applications keep writing on the current master. Is it ok to proceed? (yes/NO): yes

Mon Sep 17 15:48:32 2018-[info] Locking all tables on the orig master to reject updates from everybody (including root):

Mon Sep 17 15:48:32 2018-[info] Executing FLUSH TABLES WITH READ LOCK..

Mon Sep 17 15:48:32 2018-[info] ok.

Mon Sep 17 15:48:32 2018-[info] Orig master binlog:pos is master-bin.000007:154.

Mon Sep 17 15:48:32 2018-[info] Waiting to execute all relay logs on 172.31.217.182 (172.31.217.182) 3306.

Mon Sep 17 15:48:32 2018-[info] master_pos_wait (master-bin.000007:154) completed on 172.31.217.182 (172.31.217.182) Executed 0 events.

Mon Sep 17 15:48:32 2018-[info] done.

Mon Sep 17 15:48:32 2018-[info] Getting new master's binlog name and position..

Mon Sep 17 15:48:32 2018-[info] slave-relay-bin.000002:154

Mon Sep 17 15:48:32 2018-[info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='172.31.217.182', MASTER_PORT=3306, MASTER_LOG_FILE='slave-relay-bin.000002', MASTER_LOG_POS=154, MASTER_USER='repl_user', MASTER_PASSWORD='xxx'

Mon Sep 17 15:48:32 2018-[info] Setting read_only=0 on 172.31.217.182 (172.31.217.182) 3306.

Mon Sep 17 15:48:32 2018-[info] ok.

Mon Sep 17 15:48:32 2018-[info]

Mon Sep 17 15:48:32 2018-[info] * Switching slaves in parallel..

Mon Sep 17 15:48:32 2018-[info]

Mon Sep 17 15:48:32 2018-[info] Unlocking all tables on the orig master:

Mon Sep 17 15:48:32 2018-[info] Executing UNLOCK TABLES..

Mon Sep 17 15:48:32 2018-[info] ok.

Mon Sep 17 15:48:32 2018-[info] Starting orig master as a new slave..

Mon Sep 17 15:48:32 2018-[info] Resetting slave 172.31.217.183 (172.31.217.183) and starting replication from the new master 172.31.217.182 (172.31.217.182).

Mon Sep 17 15:48:32 2018-[info] Executed CHANGE MASTER.

Mon Sep 17 15:48:32 2018-[info] Slave started.

Mon Sep 17 15:48:32 2018-[info] All new slave servers switched successfully.

Mon Sep 17 15:48:32 2018-[info]

Mon Sep 17 15:48:32 2018-[info] * Phase 5: New master cleanup phase..

Mon Sep 17 15:48:32 2018-[info]

Mon Sep 17 15:48:32 2018-[info] 172.31.217.182: Resetting slave info succeeded.

Mon Sep 17 15:48:32 2018-[info] Switching master to 172.31.217.182 (172.31.217.182) completed successfully.

Note that there will be a place to ask you during the switching process.

Master_ip_online_change_script is not defined. If you do not disable writes on the current master manually, applications keep writing on the current master. Is it ok to proceed? (yes/NO): yes

There is no write to the disable main library, and the application that connects to it will continue to write to it after switching. Is that ok?

Here I'm just testing the usability of this online switching process, so I typed in yes.

The mha software pauses after the handover is complete.

These are all the contents of the article "what are the configuration and switching modes of MHA in mysql". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.