Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Get to know the high availability of 7--MHA in MariaDB

2025-01-19 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

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

First, background introduction

In the traditional MySQL one-master-one-slave or one-master-multi-slave structure, there is a single point of fault hidden danger in the master node. In order to solve this fault, the high availability mode of MHA is introduced. MHA consists of manager and node. Manager can be installed on a separate server or on a MySQL server with a non-master role. One manager can manage multiple node collections, and each master/node cluster is called an application. All clusters can use a configuration file or create a separate configuration file for each cluster. Currently, MHA is composed of at least three hosts.

Second, the realization principle

MHA is highly available on the basis of MySQL master-slave replication, and its implementation principle is as follows:

Under normal circumstances, the manager node of MHA periodically checks the health status of all node nodes. When the master node of MySQL fails, the manager of MHA attempts to save the binary log locally from the master of the down MySQL, and compared with the relay logs of all node nodes, the slave host with the latest synchronization status is promoted to master host, and all slave hosts point to the new master host.

Third, the experimental environment

This experiment uses CentOS7.4 system, database version 5.5.56 MHA version mha4mysql-manager-0.58. Because relay logs need to be transferred between all servers, and master needs to be specified, key authentication needs to be established between all hosts, master and candidate nodes need to create accounts for binary logs copied from the server, MySQL each server needs to create accounts that can specify master, and master and candidate nodes cannot turn on the relay_log_purge option, in order to maximize data loss It is recommended that candidate be configured in semi-synchronous form.

MHA role

MySQL role

IP address

Enable the MySQL function

Manager

-

172.16.10.30/24

-

Node

Master

172.16.10.40/24

1. Binary log, relay log

two。 Copy accounts and manage accounts

3. Relay logs cannot be clipped

4. Semi-synchronous master plug-in

Node/candidate

Slave

172.16.10.50/24

1. Binary log, relay log

two。 Copy accounts and manage accounts

3. Relay logs cannot be clipped

4. Semi-synchronous slave plug-in

5. Read-only mode

Node

Slave

172.16.10.60/24

1. Relay log

two。 Read-only mode

Fourth, prepare for operation

Since it is cluster time synchronization and name resolution, it needs to be completed ahead of time, which is not discussed here.

1. All hosts trust each other

You can generate a pair of keys on one host and copy the keys and know_hosts files to the rest of the host, without requiring all hosts to generate key pairs to copy each other

[root@host3 ~] # ssh-keygen

[root@host3 ~] # ssh-copy-id 172.16.10.30

[root@host3 ~] # ssh-copy-id 172.16.10.40

[root@host3 ~] # ssh-copy-id 172.16.10.50

[root@host3 ~] # ssh-copy-id 172.16.10.60

[root@host3 .ssh] # scp-r id_rsa known_hosts root@172.16.10.40:/root/.ssh/

[root@host3 .ssh] # scp-r id_rsa known_hosts root@172.16.10.50:/root/.ssh/

[root@host3 .ssh] # scp-r id_rsa known_hosts root@172.16.10.60:/root/.ssh/

two。 Install the Mariadb database

[root@host4 ~] # yum install mariadb-server.x86_64-y

[root@host5 ~] # yum install mariadb-server.x86_64-y

[root@host6 ~] # yum install mariadb-server.x86_64-y

3.Master host operation

(1) modify the configuration file

[root@host4 ~] # vim / etc/my.cnf

[mysqld]

Datadir=/var/lib/mysql

Socket=/var/lib/mysql/mysql.sock

Skip_name_resolve=ON

Relay_log=mysql-relaylog

Relay_log_index=mysql-relaylog

Relay_log_purge=OFF

Slow_query_log=ON

Server-id=10

Innodb_file_per_table=ON

Binlog_format=ROW

Log_bin=mysql-binlog

Log_slave_updates=ON

(2) start and enter MySQL

[root@host4 ~] # systemctl start mariadb.service

[root@host4 ~] # mysql

(3) create an account for master-slave replication and database management

MariaDB [(none)] > grant replication slave on *. * to 'bak'@'172.16.10.%' identified by' bakpass'

MariaDB [(none)] > grant all on *. * to 'man'@'172.16.10.%' identified by' manpass'

MariaDB [(none)] > flush privileges

(4) enable and activate the semi-synchronous plug-in

MariaDB [(none)] > install plugin rpl_semi_sync_master soname 'semisync_master.so'

MariaDB [(none)] > set global rpl_semi_sync_master_enabled=ON

(5) determine the current binlog log and position

MariaDB [(none)] > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-binlog.000003 | 610 | |

+-+

4.Candidate host operation

(1) modify the configuration file

[root@host5 ~] # vim / etc/my.cnf

[mysqld]

Datadir=/var/lib/mysql

Socket=/var/lib/mysql/mysql.sock

Skip_name_resolve=ON

Relay_log=mysql-relaylog

Relay_log_index=mysql-relaylog

Relay_log_purge=OFF

Read_only=ON

Slow_query_log=ON

Server-id=20

Innodb_file_per_table=ON

Binlog_format=ROW

Log_bin=mysql-binlog

Log_slave_updates=ON

(2) start and enter MySQL

[root@host5 ~] # systemctl start mariadb.service

[root@host5 ~] # mysql

(3) create an account for master-slave replication and database management

MariaDB [(none)] > set global read_only=0

MariaDB [(none)] > grant replication slave on *. * to 'bak'@'172.16.10.%' identified by' bakpass'

MariaDB [(none)] > grant all on *. * to 'man'@'172.16.10.%' identified by' manpass'

MariaDB [(none)] > flush privileges

MariaDB [(none)] > set global read_only=1

(4) enable and activate the semi-synchronous plug-in

MariaDB [(none)] > install plugin rpl_semi_sync_slave soname 'semisync_slave.so'

MariaDB [(none)] > set global rpl_semi_sync_slave_enabled=ON

(5) specify the master server and the current binlog log and position of the master server

MariaDB [hellodb] > change master to

-> master_host='172.16.10.40'

-> master_user='bak'

-> master_password='bakpass'

-> master_port=3306

-> master_log_file='mysql-binlog.000003'

-> master_log_pos=610

-> master_connect_retry=10

(6) start the slave node

MariaDB [hellodb] > start slave [IO_THREAD | SQL_THREAD]

(7) View the status of slave nodes (Slave_IO_Running and Slave_SQL_Running show that Yes is successful)

MariaDB [(none)] > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 172.16.10.40

Master_User: bak

Master_Port: 3306

Connect_Retry: 10

Master_Log_File: mysql-binlog.000003

Read_Master_Log_Pos: 610

Relay_Log_File: mysql-relaylog.000002

Relay_Log_Pos: 532

Relay_Master_Log_File: mysql-binlog.000003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

5.Slave host operation

(1) modify the configuration file

[root@host6 ~] # vim / etc/my.cnf

[mysqld]

Datadir=/var/lib/mysql

Socket=/var/lib/mysql/mysql.sock

Skip_name_resolve=ON

Relay_log=mysql-relaylog

Relay_log_index=mysql-relaylog

Relay_log_purge=ON

Read_only=ON

Slow_query_log=ON

Server-id=30

Innodb_file_per_table=ON

(2) start and enter MySQL

[root@host6 ~] # systemctl start mariadb.service

[root@host6 ~] # mysql

(3) create an account to manage the database

MariaDB [(none)] > set global read_only=0

MariaDB [(none)] > grant replication slave on *. * to 'bak'@'172.16.10.%' identified by' bakpass'

MariaDB [(none)] > grant all on *. * to 'man'@'172.16.10.%' identified by' manpass'

MariaDB [(none)] > flush privileges

MariaDB [(none)] > set global read_only=1

(4) specify the master server and the current binlog log and position of the master server

MariaDB [hellodb] > change master to

-> master_host='172.16.10.40'

-> master_user='bak'

-> master_password='bakpass'

-> master_port=3306

-> master_log_file='mysql-binlog.000003'

-> master_log_pos=610

-> master_connect_retry=10

(5) start the slave node

MariaDB [hellodb] > start slave [IO_THREAD | SQL_THREAD]

(6) View the status of slave nodes (Slave_IO_Running and Slave_SQL_Running show that Yes is successful)

MariaDB [(none)] > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 172.16.10.40

Master_User: bak

Master_Port: 3306

Connect_Retry: 10

Master_Log_File: mysql-binlog.000003

Read_Master_Log_Pos: 610

Relay_Log_File: mysql-relaylog.000002

Relay_Log_Pos: 532

Relay_Master_Log_File: mysql-binlog.000003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

5. MHA high availability implementation

1. Install MHA softwar

(1) Primary node

[root@host3 mha] # yum install mha4mysql-manager-0.58-0.el7.centos.noarch.rpm mha4mysql-node-0.58-0.el7.centos.noarch.rpm-y

-masterha_check_ssh:MHA dependent SSH environment detection tool

-masterha_check_repl:MySQL replication environment detection tool

-masterha_manager:MHA service main program

-masterha_check_status:MHA running state detection tool

-masterha_master_monitor:MySQL master node availability testing tool

-masterha_master_switch:master node switching tool

-masterha_conf_host: add or remove configured nodes

-masterha_stop: a tool for shutting down MHA services

(2) slave node

[root@host4 mha] # yum install mha4mysql-node-0.58-0.el7.centos.noarch.rpm-y

-save_binary_logs: save and copy the binary log of master

-apply_diff_relay_logs: identify different relay log events and use them for other slave

-fiter_mysqlbinlog: remove unnecessary ROLLBACK events (MHA no longer uses this tool)

-purge_relay_logs: clear the relay log (does not block SQL threads)

two。 Create a configuration file on the Manager node of MHA and detect

[root@host3 ~] # mkdir / etc/mha

[root@host3 ~] # vim / etc/mha/app1.conf

[server default]

User=man

Password=manpass

Manager_workdir=/data/masterha/app1

Manager_log=/data/masterha/app1/manager.log

Remote_workdir=/data/masterha/app1

Ssh_user=root

Ping_interval=1

Repl_user=bak

Repl_password=bakpass

[server1]

Hostname=172.16.10.40

Candidate_master=1

[server2]

Hostname=172.16.10.50

Candidate_master=1

[server3]

Hostname=172.16.10.60

No_master=1

[root@host3 mha] # masterha_check_ssh-- conf=/etc/mha/app1.conf

[root@host3 mha] # masterha_check_repl-- conf=/etc/mha/app1.conf

The warning at this time is ignored, and MHA can use the-- global_conf option to specify the same configuration for multiple application

3. Start and view MHA

[root@host3] # masterha_manager-- conf=/etc/mha/app1.conf 2 > & 1 &

[root@host3] # masterha_check_status-- conf=/etc/mha/app1.conf

App1 (pid:2633) is running (0:PING_OK), master:172.16.10.40

4. Simulate master node failure

(1) disable the MySQL service of the master node

[root@host4 ~] # mysqladmin-uroot shutdown

(2) View the status at the Manager node

[root@host3] # masterha_check_status-- conf=/etc/mha/app1.conf

App1 is stopped (2:NOT_RUNNING).

MHA shuts down automatically when the node node fails, and does not start automatically when the failed node is restored.

(3) View the status of Candidate nodes

MariaDB [(none)] > show slave hosts

+-+

| | Server_id | Host | Port | Master_id | |

+-+

| | 30 | | 3306 | 20 | |

+-+

MariaDB [(none)] > show global variables like'% read_only%'

+-+ +

| | Variable_name | Value |

+-+ +

| | read_only | OFF |

+-+ +

You can find that the Candidate node has been upgraded to a Master node, and the read_only option is automatically turned off (although this option still exists in the configuration file)

(4) View status of slave node

MariaDB [(none)] > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 172.16.10.50

Master_User: bak

Master_Port: 3306

Connect_Retry: 10

Master_Log_File: mysql-binlog.000004

Read_Master_Log_Pos: 605

Relay_Log_File: mysql-relaylog.000002

Relay_Log_Pos: 532

Relay_Master_Log_File: mysql-binlog.000004

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

5. Restore the failed host

(1) put the repaired host online

(2) modify my.cnf file and add read_only=ON option

(3) use backup tools such as mysqldump to back up the database on the current master host

(4) use backup to restore on the failed host, start the MySQL service, and then specify a new Master host and binlog. If the semi-synchronous master host is down, you also need to change the semi-synchronous master and slave roles, and restart IO_thread.

(5) restart MHA on the manager node of MHA

Supplementary explanation

The following error occurred when MHA checked the replication status:

[root@host3] # masterha_check_repl-- conf=/etc/mha/app1.conf

Checking if super_read_only is defined and turned on..DBD::mysql::st execute failed: Unknown system variable 'super_read_only' at / usr/share/perl5/vendor_perl/MHA/SlaveUtil.pm line 245.

Fri May 4 11:09:28 2018-[error] [/ usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln208] Slaves settings check failed!

Fri May 4 11:09:28 2018-[error] [/ usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln416] Slave configuration failed.

Fri May 4 11:09:28 2018-[error] [/ usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations. At / usr/bin/masterha_check_repl line 48.

Fri May 4 11:09:28 2018-[error] [/ usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.

Fri May 4 11:09:28 2018-[info] Got exit code 1 (Not master dead).

The reason is that although the read_only option is set from the node in the 5.5.56-MariaDB version, it does not take effect for users with administrator privileges, so the super_read_only option is added after MySQL5.6 (Mariadb10.1), but there is no such option in the current version, so an error is reported. The solution is to finally change the version of MHA to mha4mysql-0.56.

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.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report