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

Realization of High availability of mysql Database based on MHA+semi sync

2025-04-05 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

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

1. The principle of the experiment: 1. The topology diagram is as follows:

2. How it works: from crashing master saved binary log events (binlog events) to identifying relay logs (relay log) containing the latest slave application differences to other slave applications from master saved binary log events (binlog events) to upgrading one slave to a new master to enable other slave connections to new master to replicate 3, toolkit: MHA software consists of two parts Manager toolkit and Node toolkit 1, Manager toolkit mainly includes the following tools: masterha_check_ssh check MHA's SSH configuration status masterha_check_repl check MySQL replication status masterha_manger start MHAmasterha_check_status detect current MHA running status masterha_master_monitor detect whether master is down masterha_master_switch failover (automatic or manual) masterha_conf_host add or remove configured server information 2, Node toolkits: these tools are usually triggered by MHA Manager scripts It mainly includes the following tools: save_binary_logs saves and replicates master's binary log apply _ diff_relay_logs to identify different relay log events and apply them to other slavefilter_mysqlbinlog to remove unnecessary ROLLBACK events (MHA no longer uses this tool) purge_relay_logs clears the relay log (does not block SQL threads) 2, experimental environment preparation: 1. Four virtual hosts are prepared in vmware with hostnames of manager Master,slave1,slave2 system for centos7 corresponding to ip:192.168.190.128-1312, setenforce 0 turn off selinuxiptables-F clear firewall 3, configure yum source And epel source 4 and four virtual hosts to ensure time synchronization Ntpdate command 3. Experimental steps: 1. Four hosts implement ssh secret connection ssh-keygen any host generates a public key and private key pair ssh-copy-id 192.168.190.128 generates files in the .ssh directory scp-r / root/.ssh 192.168.190.129scp-r / root/.ssh 192.168.190.130scp-r / root/.ssh 192.168.190.1312, Manager node (1) yum install mha4mysql-manager-0.56-0.el6.noarch.rpm mha4mysql-node-0.56-0.el6.noarch.rpm (2) mkdir / data/mastermha/app1/-pv (3) vim / etc/mastermha/app1.cnf

[server default]

The administrative account of the user=mhauser master server

The password of password=centos mhauser

Manager_workdir=/data/mastermha/app1/ working directory

Manager_log=/data/mastermha/app1/manager.log log directory

Remote_workdir=/data/mastermha/app1/

Ssh_user=root ssh connection account

Repl_user=repluser copy account

Repl_password=centos copies the password of the account.

Ping_interval=1

[server1]

Hostname=192.168.190.129 master server

Candidate_master=1 has the opportunity to be promoted.

[server2]

Hostname=192.168.190.130 slave server

Candidate_master=1 has the opportunity to be promoted.

[server3]

Hostname=192.168.190.131 slave server

Candidate_master=1 has the opportunity to be promoted.

3. Master,slave1,slave2 node yum install mha4mysql-node-0.56-0.el6.noarch.rpm4, master node (1) systemctl start mariadb (2) vim / etc/my.cnf

Log-bin enables two-tier system

Server_id=1 server id, each node must be different

Innodb_file_per_table every library, every table

Skip_name_resolve=1 connects through ip without parsing the hostname

Log_slave_updates = 1 used when switching between master and slave

Plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" load semi-synchronous replication plug-ins, master and slave are installed, master bad, from the top to achieve semi-synchronous

Loose_rpl_semi_sync_master_enabled = 1 turns on the master's semi-synchronous function.

Loose_rpl_semi_sync_slave_enabled = 1 to enable the semi-synchronization function of slave

Loose_rpl_semi_sync_master_timeout = 5000

Mysql mysql > show master logs mysql > grant replication slave on *. * to repluser@'192.168.31.%' identified by 'centos'; mysql > grant all on *. * to mhauser@'192.168.190.%' identified by' magedu'; show variables like'% semi%';5, slave1 node and slave2 node vim / etc/my.cnf

[mysqld]

This value varies from node to node in server_id=2.

Log-bin

Read_only is read-only and not valid for superusers. Global variables will change when the master is switched from bad to master.

Relay_log_purge=0 does not delete relay logs

Skip_name_resolve=1 does not resolve hostnames and connects through ip

Plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" load semi-synchronous replication plug-ins, master and slave are installed, master bad, from the top to achieve semi-synchronous

Loose_rpl_semi_sync_master_enabled = 1 turns on the master's semi-synchronous function.

Loose_rpl_semi_sync_slave_enabled = 1 to enable the semi-synchronization function of slave

Loose_rpl_semi_sync_master_timeout = 5000

Mysql > CHANGE MASTER TO MASTER_HOST='MASTER_IP', MASTER_USER='repluser', MASTER_PASSWORD='centos', MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=245;6, test on manager node 1, masterha_check_ssh-conf=/etc/mastermha/app1.cnf to detect ssh connectivity 2, masterha_check_repl-conf=/etc/mastermha/app1.cnf to detect replication function

3. Masterha_manager-- conf=/etc/mastermha/app1.cnf starts mha management. 4. Stop the mariadb service of the master node and view the corresponding log.

It is found that the original master node has been transferred. If the master node fails, the master node can be reconfigured as a slave node in the cluster. You need to modify the manager configuration file again. Note: the / data/mastermha/app1/ will generate an empty app1.failover.complete file when the master-slave switch occurs for the first time. If you find that the file exists in this directory the next time, you will not be allowed to trigger the switch. If you switch again, delete it. The management tool of mha is executed at the front desk at one time, and it needs to be opened manually after a failure.

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