In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
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.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.