Shulou(Shulou.com)06/01 Report--

Environment:

192.168.205.37: as MHA server

192.168.205.47: as Master server

192.168.205.57: as Slave1 server

192.168.205.67: as Slave2 server

Version:

OS: centos 7 1810 with mini install

Mariadb-server 5.5.60

Mha4mysql-manager-0.56-0.el6.noarch.rpm

Mha4mysql-node-0.56-0.el6.noarch.rpm

Purpose:

MHA uses perl script to write the program, when the master node down, if the slave node replication is not completed, when he becomes the master node, it needs to try to copy the binary log of the master node to the new master node to ensure the integrity of the data, so it needs sshkey verification. We use one as the management node, three of which are database nodes, one master and two slaves, simulating the master node downmachine to see how the slave is promoted to the master node.

To install the database, use the following script to install mariadb: [root@master data] # cat maridb_yum.sh #! / bin/bashID= `ip a show dev eth0 | sed-r'3 inet @ (. * inet) (. *) (/. *) @\ 2 databases'| cut-d. -f4`rpm-Q mariadb-server | | yum install-y mariadb-server [- d / data/mysql] | | mkdir-p / data/mysql [- d / data/logs] | | mkdir-p / data/logschown mysql:mysql / data/ {mysql Logs} sed-I's invalid datadirblade / etc/my.cnf | | sed-I'/\ [mysqld\] / a log-bin=/data/logs/bin' / etc/my.cnfgrep "innodb_file_per_table" / etc/my.cnf | | sed-I'/\ [mysqld\] / an innodb_file_per_table = on' / etc/my.cnfgrep "skip | _ name_resolve "/ etc/my.cnf | | sed-I'/\ [mysqld\] / a skip_name_resolve = on' / etc/my.cnfgrep" server-id "/ etc/my.cnf | | sed-I" /\ [mysqld\] / a server-id=$ID "/ etc/my.cnfservice mariadb restart modifies the configuration file of two slave nodes [root@slave1 data] # vi / etc/ my.cnf [mysqld] read_only relay_log_purge=0 # does not delete relay logs

Establish the replication location and account number of the primary node

MariaDB [(none)] > show master logs +-+-+ | Log_name | File_size | +-+-+ | bin.000001 | 30373 | | bin.000002 | 1038814 | bin.000003 | 30373 | +-+-+ 3 rows in set (0.00 sec) MariaDB [ (none)] > grant replication slave on *. * to repluser@'192.168.205.%' identified by 'centos' Query OK, 0 rows affected (0.00 sec) MariaDB [(none)] > grant all on *. * to mhauser@'192.168.205.%' identified by 'centos'; Query OK, 0 rows affected (0.00 sec)

Configure the slave node's change master to and start the thread

MariaDB [(none)] > CHANGE MASTER TO-> MASTER_HOST='192.168.205.47',-> MASTER_USER='repluser',-> MASTER_PASSWORD='centos',-> MASTER_PORT=3306,-> MASTER_LOG_FILE='bin.000003',-> MASTER_LOG_POS=245;Query OK, 0 rows affected (0.01 sec) MariaDB [(none)] > start slave Query OK 0 rows affected (0.00 sec) MariaDB [(none)] > show slave status\ gateway * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.205.47 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: bin.000003 Read_Master_Log_Pos: 401 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 679 Relay_Master_Log_File: bin.000003 Slave_IO_ Running: Yes Slave_SQL_Running: YesMHA implementation installation package Mha depends on epel source We are going to use [root@MHA] # yum install mha4mysql-manager-0.56-0.el6.noarch.rpm mha4mysql-node-0.56-0.el6.noarch.rpm to install the node package [root@master] # yum install mha4mysql-node-0.56-0.el6.noarch.rpm [root@slave1 data] # yum install mha4mysql-node-0.56-0.el6.noarch.rpm [root@slave2 data] # yum install mha4mysql-node-0 on all sql nodes .56-0.el6.noarch.rpm configuration ssh key authentication [root@MHA ~] # ssh-keygen [root@MHA ~] # ssh-copy-id 192.168.205.37 [root@MHA ~] # cat .ssh / authorized_keys [root@MHA ~] # scp-r .ssh 192.168.205.47:/root/ [root@MHA ~] # scp-r .ssh 192.168.205.57:/root/ [root@MHA ~] # scp-r .ssh 192.168 .205.67: / root/ generate configuration MHA file [root@MHA ~] # mkdir / etc/mha [root@MHA ~] # vim / etc/mha/app1.cnf [server default] master_binlog_dir=/data/logs/ user=mhauserpassword=centosmanager_workdir=/data/mastermha/app1/manager_log=/data/mastermha/app1/manager.logremote_workdir=/data/mastermha/app1/ssh_user=rootrepl_user=repluserrepl_password=centosping_interval= 1 [server1] hostname=192.168.205.47candidate_master= 1 [server2] Check ssh before hostname= 192.168.205.57 [server3] hostname=192.168.205.67candidate_master=1 starts Whether repl replication is ready [root@MHA ~] # masterha_check_ssh-- conf=/etc/mha/app1.cnf [root@MHA ~] # masterha_check_repl-- conf=/etc/mha/app1.cnf starts the process, which runs in the foreground and terminates when the switch is completed when the master node fails So to continue to restart the process [root@MHA ~] # masterha_manager-- conf=/etc/mha/app1.cnfMon Aug 12 23:33:22 2019-[warning] Global configuration file / etc/masterha_default.cnf not found. Skipping.Mon Aug 12 23:33:22 2019-[info] Reading application default configuration from / etc/mha/app1.cnf..Mon Aug 12 23:33:22 2019-[info] Reading server configuration from / etc/mha/app1.cnf.. test

We simulate a primary node down during a write operation to see if the server can switch normally and cannot replicate the lost data

MariaDB [(none)] > use test;Database changedMariaDB [test] > create table testlog (id int auto_increment primary key,name char (10), age int default 20); Query OK, 0 rows affected (0.01 sec) MariaDB [test] > MariaDB [test] > delimiter $MariaDB [test] > MariaDB [test] > create procedure sp_testlog ()-> begin-> declare i int;-> set I = 1 -> while i do insert into testlog (name,age) values (concat ('wang',i), I);-> set I = I + 1;-> end while;-> end$$Query OK, 0 rows affected (0.00 sec) MariaDB [test] > MariaDB [test] > delimiter; when MariaDB [test] > call sp_testlog; is writing data, we shut down 47 servers in time, MHA detects the node and responds, successfully promotes other hosts and exits the program. [root@MHA] # masterha_manager-- conf=/etc/mha/app1.cnfMon Aug 12 23:33:22 2019-[warning] Global configuration file / etc/masterha_default.cnf not found. Skipping.Mon Aug 12 23:33:22 2019-[info] Reading application default configuration from / etc/mha/app1.cnf..Mon Aug 12 23:33:22 2019-[info] Reading server configuration from / etc/mha/app1.cnf..Mon Aug 12 23:45:16 2019-[warning] Global configuration file / etc/masterha_default.cnf not found. Skipping.Mon Aug 12 23:45:16 2019-[info] Reading application default configuration from / etc/mha/app1.cnf..Mon Aug 12 23:45:16 2019-[info] Reading server configuration from / etc/mha/app1.cnf.. [root@MHA ~] #

We can check the log to see what happened. Slave2 is the main promotion.

[root@MHA data] # cat mastermha/app1/manager.log-Failover Report-app1: MySQL Master failover 192.168.205.47 (192.168.205.47) to 192.168.205.67 (192.168.205.67) succeededMaster 192.168.205.47 (192.168.205.47) is downloading check MHA Manager logs at MHA:/data/mastermha/app1/manager.log for details.Started automated (non-interactive) failover. The latest slave 192.168.205.57 (192.168.205.57) has all relay logs for recovery.Selected 192.168.205.67 (192.168.205.67) as a new master.192.168.205.67 (192.168.205.67): OK: Applying all logs succeeded.192.168.205.57 (192.168.205.57): This host has the latest relay log events.Generating relay diff files from the latest slave succeeded.192.168. 205.57 (192.168.205.57): OK: Applying all logs succeeded. Slave started, replicating from 192.168.205.67 (192.168.205.67 completed successfully 3306): Resetting slave info succeeded.Master failover to 192.168.205.67 (192.168.205.67 completed successfully. Let's verify it. Look at the status of slave on slave2 and find that it is gone, because it is the main MariaDB [(none)] > show slave status\ GEmpty set (0.00 sec). Check slave1. It is found that the master node points to slave2MariaDB [test] > show slave status\ gateway * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.205.67 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: bin.000005 Read_Master_Log_Pos: 245 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 523 Relay_Master_Log_File: bin.000005 Slave_IO_Running: Yes Slave_SQL_Running: Yes check on the new master node to see if read_only off is dropped. Find it, but don't forget to change it back to the configuration file. If you restart it, it will change to read_onlyMariaDB [(none)] > show variables like 'read_only'. +-+-+ | Variable_name | Value | +-+-+ | read_only | OFF | +-+-+ 1 row in set (0.00 sec)

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.