Week 14 homework

1. MariaDB master-slave replication principle

Answer:

  • Data operations on the primary MySQL server will be recorded in the binary log
  • The dump thread on the master MySQL server will send the data in the binary log to the slave MySQL server
  • Receive the data from the binary log of the master MySQL server through IO thread from the MySQL server, and write these data into its own relay log
  • Finally, the data in the MySQL server is written to the MySQL server through the thread

 

2. MariaDB one master one slave architecture construction

Prepare two new CentOS 7 6 server, where the server address of the host called node1 is 192.168.130.132, and the server address of the host called node2 is 192.168.130.133

Answer:

Primary mysql server: node1

 

  • Add the following lines to the configuration file

 

[root@node1 ~]#vim /etc/my.cnf
[mysqld]
server-id=132
log_bin=/data/mysql_log/binlog
binlog_format=row

  • Create the directory where the binary log is located and start the service
[root@node1 ~]#mkdir /data/mysql_log
[root@node1 ~]#chown mysql.mysql /data/mysql_log
[root@node1 ~]#systemctl restart mariadb
  • Create a user for synchronization and view the location of the binary log
MariaDB [(none)]> GRANT replication slave ON *.* TO 'repluser'@'192.168.130.%' IDENTIFIED BY 'magedu';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show master logs;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000001 |       245 |
+---------------+-----------+
1 row in set (0.00 sec)

 

From MySQL server: node2

  • Add the following line to the configuration file and start the service
[root@node2 ~]#vim /etc/my.cnf
[mysqld]
server-id=133

[root@node2 ~]#systemctl start mariadb
  • Execute the following statement in mysql
MariaDB [(none)]> CHANGE MASTER TO
    -> MASTER_HOST='192.168.130.132',
    -> MASTER_USER='repluser',
    -> MASTER_PASSWORD='magedu',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='binlog.000001',
    -> MASTER_LOG_POS=245;
Query OK, 0 rows affected (0.00 sec)
  • Start 2 threads on the slave server
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.7
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000001
Read_Master_Log_Pos: 398
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 679
Relay_Master_Log_File: binlog.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 398
Relay_Log_Space: 975
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 132
1 row in set (0.00 sec)

 

Test:

  • Import database on primary MySQL server
[root@node1 ~]#mysql < hellodb_innodb.sql 
  • View from MySQL server
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

 

3. MariaDB cascaded replication

In the previous section, we completed the master-slave replication architecture of one master-slave MariaDB, but sometimes we expect to have a backup MariaDB node, which only backs up the stored data and does not need to provide external services. In order to realize this function, we can adjust the previous master-slave architecture, that is, open the binary file of the slave node, and then configure it with a remote synchronization data user, and then use a new server as the slave server to synchronize the slave server data. Next, we add a new centos7 to the original architecture 6. As node 3, the IP address is 192.168.130.134

Answer:

Intermediate MySQL server: node2

  • Open the binary log in the configuration file of the intermediate MySQL server and restart the service
[root@node2 ~]#vim /etc/my.cnf

[mysqld]
server-id=133
log_bin
log_slave_updates
binlog_format=row

[root@node2 ~]#systemctl restart mariadb

  • Make a full backup of the intermediate MySQL server and send it to node3
[root@node2 ~]#mysqldump -uroot -A -F --single-transaction --master-data=1 > /backup/all.sql
[root@node2 ~]#scp /backup/all.sql 192.168.130.134:/backup

 

Node 3: node3

  • Modify the just received all SQL file
CHANGE MASTER TO 
MASTER_HOST='10.0.0.17',
MASTER_USER='repluser',
MASTER_PASSWORD='magedu',
MASTER_PORT=3306,
MASTER_LOG_FILE='mariadb-bin.000002', MASTER_LOG_POS=245;
  • Add the server ID to the node3 configuration file and restore the backup. Since the location of the binary file has been specified in the backup file before, node 3 will naturally know the future synchronization location after restoration
[root@node3 ~]#vim /etc/my.cnf
[mysqld]
server-id=134

[root@node3 ~]#systemctl start mariadb
[root@node3 ~]#mysql < /backup/all.sql

  • Open 2 threads
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)


#You can see that some databases and tables on node1 have been restored to node3

[root@node3 ~]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.65-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> use hellodb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.00 sec)

 

test

  • Calling procedure on node1 to produce testlog table
[root@node1 ~]#mysql hellodb < testlog.sql 
[root@node1 ~]#mysql hellodb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 5.5.65-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [hellodb]> call sp_testlog;
  • Check for synchronization on node3
MariaDB [hellodb]> select count(*) from testlog
    -> ;
+----------+
| count(*) |
+----------+
|   100000 |
+----------+
1 row in set (0.02 sec)

 

4. MariaDB semi synchronous replication

Answer:

environment

  • 3 CentOS 8 hosts
  • Main server: 10.0.0.8
  • Slave server: 10.0.0.18
  • Slave server: 10.0.0.28

Primary MySQL server

  • Modify the configuration file and add the following lines
[mysqld]
server-id=8
log-bin=/data/mysql-log/bin-log
plugin-load-add=semisync_master
rpl-semi-sync-master-enabled
rpl-semi-sync-master-timeout=3000
  • Create a user to copy data and view the binary log location
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'rpluser'@'10.0.0.%' IDENTIFIED BY 'magedu';
Query OK, 0 rows affected (3.001 sec)

MariaDB [(none)]> show master logs;
+----------------+-----------+
| Log_name       | File_size |
+----------------+-----------+
| bin-log.000001 |       349 |
| bin-log.000002 |       524 |
+----------------+-----------+
2 rows in set (0.000 sec)
  • View the semi synchronous status of the master node
MariaDB [(none)]> SHOW VARIABLES LIKE '%semi%';
+---------------------------------------+--------------+
| Variable_name                         | Value        |
+---------------------------------------+--------------+
| rpl_semi_sync_master_enabled          | ON           |
| rpl_semi_sync_master_timeout          | 3000         |
| rpl_semi_sync_master_trace_level      | 32           |
| rpl_semi_sync_master_wait_no_slave    | ON           |
| rpl_semi_sync_master_wait_point       | AFTER_COMMIT |
| rpl_semi_sync_slave_delay_master      | OFF          |
| rpl_semi_sync_slave_enabled           | OFF          |
| rpl_semi_sync_slave_kill_conn_timeout | 5            |
| rpl_semi_sync_slave_trace_level       | 32           |
+---------------------------------------+--------------+
9 rows in set (0.001 sec)

MariaDB [(none)]> SHOW STATUS LIKE '%semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |
| Rpl_semi_sync_master_get_ack               | 1     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 1     |
| Rpl_semi_sync_master_no_times              | 1     |
| Rpl_semi_sync_master_no_tx                 | 1     |
| Rpl_semi_sync_master_request_ack           | 1     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
| Rpl_semi_sync_slave_send_ack               | 0     |
| Rpl_semi_sync_slave_status                 | OFF   |
+--------------------------------------------+-------+
18 rows in set (0.001 sec)

 

All from MySQL server

  • Modify the configuration file and add the following lines
[mysqld]
server-id=18
plugin-load-add=semisync_slave
rpl-semi-sync-slave-enabled
  • Restart the service, add the master node, and start the slave node thread
MariaDB [(none)]> CHANGE MASTER TO
    -> MASTER_HOST='10.0.0.8',
    -> MASTER_USER='rpluser',
    -> MASTER_PASSWORD='magedu',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='bin-log.000001',
    -> MASTER_LOG_POS=349;
Query OK, 0 rows affected (0.027 sec)

 MariaDB [(none)]> start slave;
 Query OK, 0 rows affected (0.002 sec)

  • View the semi synchronous status of the slave node
MariaDB [(none)]> SHOW VARIABLES LIKE '%semi%';
+---------------------------------------+--------------+
| Variable_name                         | Value        |
+---------------------------------------+--------------+
| rpl_semi_sync_master_enabled          | OFF          |
| rpl_semi_sync_master_timeout          | 10000        |
| rpl_semi_sync_master_trace_level      | 32           |
| rpl_semi_sync_master_wait_no_slave    | ON           |
| rpl_semi_sync_master_wait_point       | AFTER_COMMIT |
| rpl_semi_sync_slave_delay_master      | OFF          |
| rpl_semi_sync_slave_enabled           | ON           |
| rpl_semi_sync_slave_kill_conn_timeout | 5            |
| rpl_semi_sync_slave_trace_level       | 32           |
+---------------------------------------+--------------+
9 rows in set (0.001 sec)

MariaDB [(none)]> SHOW STATUS LIKE '%semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 0     |
| Rpl_semi_sync_master_get_ack               | 0     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_request_ack           | 0     |
| Rpl_semi_sync_master_status                | OFF   |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
| Rpl_semi_sync_slave_send_ack               | 1     |
| Rpl_semi_sync_slave_status                 | ON    |
+--------------------------------------------+-------+
18 rows in set (0.001 sec)

 

test

Stop all slave nodes, add a new database in the master node, and create it successfully after 3 seconds

#If the slave node is not closed, create a database on the master node, which takes effect immediately
MariaDB [(none)]> create database db1;
Query OK, 1 row affected (0.001 sec)

#All slave nodes
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.004 sec)

#It is implemented on the primary node. It takes 3 seconds to display success
MariaDB [(none)]> create database db2;
Query OK, 1 row affected (3.001 sec)

 

5. MariaDB high availability solution MHA

Answer:

Environmental Science:

  • 10.0.0.7: centos7, MHA management end
  • 10.0.0.8: centos8,Master
  • 10.0.0.18: centos18,Slave
  • 10.0.0.28: centos28,Slave

Configure MHA management end

  • Install mha4mysql manager and mha4mysql node packages, both of which are version 0.56

 

[root@mha ~]#ls mha4mysql-*
mha4mysql-manager-0.56-0.el6.noarch.rpm  mha4mysql-node-0.56-0.el6.noarch.rpm
[root@mha ~]#yum install -y mha4mysql-*.rpm
  • SSH KEY verification between nodes
[root@mha ~]#ssh-keygen
[root@mha ~]#ssh-copy-id 10.0.0.7
[root@mha ~]#rsync -av .ssh 10.0.0.8:/root
[root@mha ~]#rsync -av .ssh 10.0.0.18:/root
[root@mha ~]#rsync -av .ssh 10.0.0.28:/root
  • Implement MHA configuration file in MHA management node
[root@mha ~]#mkdir /etc/mastermha
[root@mha ~]#cd /etc/mastermha
[root@mha mastermha]#vim app1.cnf 
[server default]
user=mhauser
password=magedu
manager_workdir=/data/mastermha/app1/
manager_log=/data/mastermha/app1/manager.log
remote_workdir=/data/mastermha/app1/
ssh_user=root
repl_user=rpluser
repl_password=magedu
ping_interval=1
master_ip_failover_script=/usr/local/bin/master_ip_failover
report_script=/usr/local/bin/sendmail.sh
check_repl_delay=0
[server1]
hostname=10.0.0.8
[server2]
hostname=10.0.0.18
[server3]
hostname=10.0.0.28
candidate_master=1
  • Configure "switch master node script" and "send mail script after node switching"
#When the primary node fails, execute the script of switching nodes
[root@mha mastermha]#vim /usr/local/bin/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 = '10.0.0.100';#Set Virtual IP
my $gateway = '10.0.0.254';#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";
}

[root@mha mastermha]#chmod +x /usr/local/bin/master_ip_failover 
#Send email notification after setting the switching node
[root@mha ~]#vim .mailrc
set from=465454572@qq.com
set smtp=smtp.qq.com
set smtp_auth_user=465454572@qq.com
set smtp_auth_password=QQ Email authorization code

[root@mha ~]#vim /usr/local/bin/sendmail.sh 
echo "MySQL is down" | mail -s "MHA Warning" 465454572@qq.com

[root@mha mastermha]#chmod +x /usr/local/bin/sendmail.sh

 

 

Configure master-slave nodes and start semi synchronous replication

  • Install mha4mysql-node-0.56-0 on all master and slave nodes el6. noarch. rpm
[root@master ~]#yum install -y mha4mysql-node-0.56-0.el6.noarch.rpm
[root@slave1 ~]#yum install -y mha4mysql-node-0.56-0.el6.noarch.rpm
[root@slave2 ~]#yum install -y mha4mysql-node-0.56-0.el6.noarch.rpm
  • Add the following lines to the Master master node configuration file
[root@master ~]#vim /etc/my.cnf.d/mariadb-server.cnf 
[mysqld]
server-id=8
log-bin
skip-name-resolve
general-log    #The general log can be opened selectively
plugin-load-add=semisync_master    #Load the master node plug-in for semi synchronous replication
rpl-semi-sync-master-enabled    #Enable semi synchronous replication as the master node
rpl-semi-sync-master-timeout=3000    #Set the timeout for semi synchronous replication
  • Start the Master and create the Master-slave replication user and the user connecting all nodes of MHA
[root@master ~]#systemctl start mariadb
[root@master ~]#mysql
MariaDB [(none)]> GRANT replication slave ON *.* TO rpluser@'10.0.0.%' IDENTIFIED BY 'magedu';
Query OK, 0 rows affected (3.001 sec)

MariaDB [(none)]> GRANT all ON *.* TO mhauser@'10.0.0.%' IDENTIFIED BY 'magedu';
Query OK, 0 rows affected (0.000 sec)

  MariaDB [(none)]> SHOW MASTER LOGS;
  +--------------------+-----------+
  | Log_name | File_size |
  +--------------------+-----------+
  | mariadb-bin.000001 | 28198 |
  | mariadb-bin.000002 | 722 |
  +--------------------+-----------+
  2 rows in set (0.000 sec)

  • Configure the virtual ip address (VIP) connected with MHA on the master node
#Both master and slave nodes must install ifconfig command, because after switching the master node, when the virtual address floats to the new master node, the ifconfig command is used in the script. If there is no ifconfig command, the virtual address cannot float to the new master node
[root@master ~]#yum install -y net-tools

[root@master ~]#ifconfig eth0:1 10.0.0.100
[root@master ~]#ifconfig eth0:1
eth0:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 10.0.0.100  netmask 255.0.0.0  broadcast 10.255.255.255
        ether 00:0c:29:a8:d1:eb  txqueuelen 1000  (Ethernet)
  • Configure slave1 and slave2 and start synchronization
#Modify the configuration file and set up synchronization
#slave1
[root@slave1 ~]#vim /etc/my.cnf.d/mariadb-server.cnf 
[mysqld]
server-id=18
log-bin
read-only
skip-name-resolve
relay-log-purge=0
plugin-load-add=semisync_slave
rpl-semi-sync-slave-enabled

[root@slave1 ~]#systemctl restart mariadb
[root@slave1 ~]#mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.17-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CHANGE MASTER TO
    -> MASTER_HOST='10.0.0.8',
    -> MASTER_USER='rpluser',
    -> MASTER_PASSWORD='magedu',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='mariadb-bin.000001',
    -> MASTER_LOG_POS=28198;
Query OK, 0 rows affected (0.025 sec)
[root@slave2 ~]#mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.17-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CHANGE MASTER TO
    -> MASTER_HOST='10.0.0.8',
    -> MASTER_USER='rpluser',
    -> MASTER_PASSWORD='magedu',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='mariadb-bin.000001',
    -> MASTER_LOG_POS=28198;
Query OK, 0 rows affected (0.004 sec)

MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.002 sec)


#slave2
[root@slave2 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=28
log-bin
read-only
skip-name-resolve
relay-log-purge=0
plugin-load-add=semisync_slave
rpl-semi-sync-slave-enabled

[root@slave2 ~]#mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.17-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CHANGE MASTER TO
    -> MASTER_HOST='10.0.0.8',
    -> MASTER_USER='rpluser',
    -> MASTER_PASSWORD='magedu',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='mariadb-bin.000001',
    -> MASTER_LOG_POS=28198;
Query OK, 0 rows affected (0.004 sec)

MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.002 sec)

#install ifconfig command
[root@slave1 ~]#yum install -y net-tools

  [root@slave2 ~]#yum install -y net-tools

 

Check MHA related environment

  • Check the ssh connection of each node
[root@mha ~]#masterha_check_ssh --conf=/etc/mastermha/app1.cnf 
Sun Sep 27 13:14:52 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sun Sep 27 13:14:52 2020 - [info] Reading application default configuration from /etc/mastermha/app1.cnf..
Sun Sep 27 13:14:52 2020 - [info] Reading server configuration from /etc/mastermha/app1.cnf..
Sun Sep 27 13:14:52 2020 - [info] Starting SSH connection tests..
Sun Sep 27 13:14:54 2020 - [debug] 
Sun Sep 27 13:14:52 2020 - [debug]  Connecting via SSH from root@10.0.0.18(10.0.0.18:22) to root@10.0.0.8(10.0.0.8:22)..
Sun Sep 27 13:14:53 2020 - [debug]   ok.
Sun Sep 27 13:14:53 2020 - [debug]  Connecting via SSH from root@10.0.0.18(10.0.0.18:22) to root@10.0.0.28(10.0.0.28:22)..
Warning: Permanently added '10.0.0.28' (ECDSA) to the list of known hosts.
Sun Sep 27 13:14:53 2020 - [debug]   ok.
Sun Sep 27 13:14:54 2020 - [debug] 
Sun Sep 27 13:14:53 2020 - [debug]  Connecting via SSH from root@10.0.0.28(10.0.0.28:22) to root@10.0.0.8(10.0.0.8:22)..
Sun Sep 27 13:14:53 2020 - [debug]   ok.
Sun Sep 27 13:14:53 2020 - [debug]  Connecting via SSH from root@10.0.0.28(10.0.0.28:22) to root@10.0.0.18(10.0.0.18:22)..
Sun Sep 27 13:14:54 2020 - [debug]   ok.
Sun Sep 27 13:14:54 2020 - [debug] 
Sun Sep 27 13:14:52 2020 - [debug]  Connecting via SSH from root@10.0.0.8(10.0.0.8:22) to root@10.0.0.18(10.0.0.18:22)..
Warning: Permanently added '10.0.0.18' (ECDSA) to the list of known hosts.
Sun Sep 27 13:14:53 2020 - [debug]   ok.
Sun Sep 27 13:14:53 2020 - [debug]  Connecting via SSH from root@10.0.0.8(10.0.0.8:22) to root@10.0.0.28(10.0.0.28:22)..
Warning: Permanently added '10.0.0.28' (ECDSA) to the list of known hosts.
Sun Sep 27 13:14:54 2020 - [debug]   ok.
Sun Sep 27 13:14:54 2020 - [info] All SSH connection tests passed successfully.
  • Check master-slave replication
[root@mha ~]#masterha_check_repl --conf=/etc/mastermha/app1.cnf 
MySQL Replication Health is OK.

 

Turn on MHA

[root@mha ~]#masterha_manager --conf=/etc/mastermha/app1.cnf 
Sun Sep 27 13:36:56 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sun Sep 27 13:36:56 2020 - [info] Reading application default configuration from /etc/mastermha/app1.cnf..
Sun Sep 27 13:36:56 2020 - [info] Reading server configuration from /etc/mastermha/app1.cnf..

[root@mha ~]#masterha_check_status --conf=/etc/mastermha/app1.cnf
app1 (pid:36419) is running(0:PING_OK), master:10.0.0.8
[root@mha ~]#

 

Check whether MHA is effective

  • Close Master node
[root@master ~]#killall -9 mysqld
  • After MHA completes switching, it will exit automatically
[root@mha ~]#masterha_manager --conf=/etc/mastermha/app1.cnf 
Sun Sep 27 13:36:56 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sun Sep 27 13:36:56 2020 - [info] Reading application default configuration from /etc/mastermha/app1.cnf..
Sun Sep 27 13:36:56 2020 - [info] Reading server configuration from /etc/mastermha/app1.cnf..
  Creating /data/mastermha/app1 if not exists..    ok.
  Checking output directory is accessible or not..
   ok.
  Binlog found at /var/lib/mysql, up to mariadb-bin.000002
Sun Sep 27 13:41:00 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sun Sep 27 13:41:00 2020 - [info] Reading application default configuration from /etc/mastermha/app1.cnf..
Sun Sep 27 13:41:00 2020 - [info] Reading server configuration from /etc/mastermha/app1.cnf..
[root@mha ~]#
  • Check slave1 and find that its master has changed to 10.0.0.28, indicating that the handover is successful
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 10.0.0.28
                   Master_User: rpluser
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mariadb-bin.000002
           Read_Master_Log_Pos: 344
                Relay_Log_File: mariadb-relay-bin.000002
                 Relay_Log_Pos: 557
         Relay_Master_Log_File: mariadb-bin.000002
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
. . . 
  • And virtual ip (vip) has also floated to the original slave2 (10.0.0.28)
[root@slave2 ~]#ifconfig
eth0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 10.0.0.28  netmask 255.255.255.0  broadcast 10.0.0.255
        inet6 fe80::20c:29ff:fedb:fe0e  prefixlen 64  scopeid 0x20<link>
        ether 00:0c:29:db:fe:0e  txqueuelen 1000  (Ethernet)
        RX packets 49233  bytes 66673333 (63.5 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 7061  bytes 1118804 (1.0 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

eth0:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 10.0.0.100  netmask 255.0.0.0  broadcast 10.255.255.255
        ether 00:0c:29:db:fe:0e  txqueuelen 1000  (Ethernet)

 

 

 

Posted by irish21 on Sat, 14 May 2022 11:13:53 +0300