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

Turn connection

Introduction to https://www.cnblogs.com/tanxiaojun/p/10424699.htmlMHA

MHA (Master HighAvailability) is currently a relatively mature solution for MySQL high availability. It was developed by youshimaton, a Japanese DeNA company (now working for Facebook). It is a set of excellent high availability software for failover and master-slave upgrade in MySQL high availability environment. In the process of MySQL failover, MHA can automatically complete the failover operation of the database within 30 seconds, and in the process of failover, MHA can ensure the consistency of the data to the maximum extent in order to achieve high availability in the real sense.

There are two roles in MHA, one is MHA Node (data node) and the other is MHA Manager (management node).

MHA Manager can be deployed on a single machine to manage multiple master-slave clusters, or it can be deployed on a slave node. MHA Node runs on each MySQL server, and MHA Manager regularly detects the master nodes in the cluster. When the master fails, it automatically promotes the slave of the latest data to the new master, and then redirects all other slave to the new master. The entire failover process is completely transparent to the application

In the process of MHA automatic failover, MHA tries to save binary logs from the down primary server to ensure that the data is not lost as much as possible, but this is not always feasible. For example, if the primary server hardware fails or cannot be accessed through ssh, MHA cannot save binary logs and only fails over and loses the latest data. With semi-synchronous replication of MySQL 5.5, the risk of data loss can be greatly reduced. MHA can be combined with semi-synchronous replication. If only one slave has received the latest binary log, MHA can apply the latest binary log to all other slave servers, thus ensuring data consistency among all nodes

Asynchronous replication (Asynchronous replication)

The default replication of MySQL is asynchronous. The master database will immediately return the result to the client after executing the transaction committed by the client, and does not care whether the slave database has received and processed it. There will be a problem. If the master crash is dropped, the committed transaction on the master may not be transferred to the slave. If it is forced to promote the slave master at this time, the data on the new master may be incomplete.

Fully synchronous replication (Fully synchronousreplication)

When the master library executes a transaction, all slave libraries execute the transaction before returning to the client. Because you need to wait for all slaves to complete the transaction before returning, the performance of fully synchronous replication is bound to be seriously affected.

Semi-synchronous replication (Semisynchronous replication)

Between asynchronous replication and full synchronous replication, the main library does not return to the client immediately after executing the transaction committed by the client, but waits for at least one received from the library and written to the relay log before returning to the client. Compared with asynchronous replication, semi-synchronous replication improves the security of data, and it also causes a certain degree of delay, which is at least one TCP/IP round trip time. Therefore, semi-synchronous replication is best used in low-latency networks.

Let's take a look at the schematic of semi-synchronous replication:

Summary: similarities and differences between asynchronous and semi-synchronous

By default, the replication of MySQL is asynchronous, and after all update operations on Master are written to Binlog, it is not guaranteed that all updates are copied to Slave. Although asynchronous operation is efficient, when there is a problem with Master/Slave, there is a high risk of data being out of sync, and data may even be lost.

The purpose of introducing semi-synchronous replication in MySQL5.5 is to ensure that the data of at least one Slave is complete when something goes wrong with master. In case of timeout, you can also temporarily transfer to asynchronous replication to ensure the normal use of the business until a salve catches up and continues to switch to semi-synchronous mode.

working principle

Compared with other HA software, the purpose of MHA is to maintain the high availability of the Master library in MySQL Replication. Its most important feature is that it can repair the differential logs between multiple Slave, finally make all Slave data consistent, and then choose one of them to act as the new Master and point the other Slave to it.

-saves binary log events (binlogevents) from a crashed master. -identify the slave with the latest updates. -apply differential relay logs (relay log) to other slave. -apply binary log events (binlogevents) saved from master. -upgrade a slave to a new master. -make other slave connect to the new master for replication.

Currently, MHA mainly supports the architecture of one master and multiple slaves. To build MHA, you must have at least three database servers in a replication cluster. One master and two slaves, that is, one serves as master, one acts as standby master, and the other acts as slave database, because at least three servers are required.

Related software package

MHA monitoring server installation: mha4mysql-manager-0.55-1.el5.noarchlamore mha4mysql color nodeMet 0.54-1.el5.noarch other master-slave cluster server installation: mha4mysql-node-0.54-1.el5.noarch

MHA package official website address: https://code.google.com/archive/p/mysql-master-ha/

The following packages are used:

Mha4mysql-manager-0.55-1.el5.noarchmha4mysql-node-0.54-1.el5.noarch II. Build the basic environment of cluster architecture

Implementation environment:

Role IP address hostname Server Id type OSManager192.168.64.37manager

Management node Centos7.2x86_ 64master192.168.64.7master11 master mysqlCentos7.2x86 _ 64Candidate master192.168.64.17master22 from mysqlCentos7.2x86_64slave192.168.64.27slave3 to mysqlCentos7.2x86_64

Master provides write services, alternative master (actual slave, hostname master2) provides read services, and slave also provides related read services. Once the master goes down, the alternative master will be promoted to a new master,slave pointing to the new master,manager as the management server.

1. Check the selinux,firewalld settings after configuring all host IP addresses, and turn off all host selinux,firewalld services to facilitate later master-slave synchronization without error.

Vim / etc/sysconfig/selinuxSELINUX=disabled replace this line with systemctl stop firewalld to turn off the firewall

two。 Synchronize server time

Vim / etc/chrony.confserver 192.168.64.7 iburst master2 slave configuration synchronized with master1 time

3 、

Configure the epel source on all four host

Download address on the official website: click on the open link wget https://mirrors.ustc.edu.cn/epel/7/x86_64/Packages/e/epel-release-7-11.noarch.rpm

4. Establish a ssh non-interactive login environment on four hosts

[root@manager] # ssh-keygen-t rsa [root@manager] # ssh-copy-id-I id_rsa.pub 192.168.64.37 [root@manager] # scp authorized_keys id_rsa 192.168.64.17:/root/.ssh/ [root@manager] # scp authorized_keys id_rsa 192.168.64.27:/root/.ssh/ [root@manager] # scp authorized_keys id_rsa 192.168.64. 7:/root/.ssh/ test ssh login plane key [root@master ~] # ssh root@192.168.64.37 other hosts can test the following three, Configure master-slave replication for MySQL

1. Install the node node package (master1 master2,slave) on the master-slave node

[root@master ~] # lsanaconda-ks.cfg Downloads original-ks.cfg reset.sh VideosDesktop mha4mysql-node-0.54-0.el6.noarch.rpm Pictures reset.sqlDocuments Music Public Template [root@master ~] # l yum install mha*

2. Modify the my.cnf file and configure master-slave synchronization

Note: if the master MYSQL server already exists, the slave MYSQL server will only be built later. Before configuring data synchronization, copy the database to be synchronized from the master MYSQL server to the slave MYSQL server (for example, back up the database on the master MYSQL first, and then restore it on the slave MYSQL server with backup).

(1.) Host configuration of master1:

Innodb_file_per_tablelog_binread_onlyserver_id=0skip_name_resolve=1

(2.) Host configuration of master2:

Innodb_file_per_tableserver_id=2skip_name_resolve=1read_onlyrelay_log_purge=0log_bin

(3) Host configuration of slave:

Innodb_file_per_tableserver_id=3skip_name_resolve=1read_onlyrelay_log_purge=0log_bin

Note: restart the mariadb service of all hosts!

(3.) create an account "mharep" for master-slave replication, create it on (master1, master2) hosts, and create a MHA management account "manager" on all mysql servers.

Configuration on master1, master2 hosts:

Mysql > GRANT REPLICATION SLAVE ON *. * TO 'repluser'@'192.168.64.%' IDENTIFIED BY' centos';mysql > GRANT ALL ON *. * TO 'mhauser'@'192.168.64.%' IDENTIFIED BY' centos'

Configuration on the slave host:

Mysql > GRANT ALL ON *. * TO 'mhauser'@'192.168.64.%' IDENTIFIED BY' centos'

Start creating master-slave replication:

View the nodes of master1:

MariaDB [(none)] > show master logs +-+-+ | Log_name | File_size | +-+-+ | mariadb-bin.000001 | 502 | mariadb-bin.000002 | 487 | | mariadb-bin.000003 | 504 | | mariadb-bin.000004 | 245 | +-+-+ 4 rows in set (0.04 sec)

Configuration on the master2 host:

Create master-slave replication and enable slave function

Mysql > CHANGE MASTER TO MASTER_HOST='192.168.64.7',MASTER_USER='repluser', MASTER_PASSWORD='centos',MASTER_LOG_FILE='mariadb-bin.000004',MASTER_LOG_POS=245;mysql > start slave

Check the slave status of the master2 host. The following two values must be yes, indicating that the slave server can connect to the master server normally.

Slave_IO_Running:Yes Slave_SQL_Running:Yes

Configuration on the slave host:

Create master-slave replication and enable salve function

Mysql > CHANGE MASTER TO MASTER_HOST='192.168.64.7',MASTER_USER='repluser', MASTER_PASSWORD='centos',MASTER_LOG_FILE='mariadb-bin.000004',MASTER_LOG_POS=245;mysql > start slave

Check the slave status of the slave host. The following two values must be yes, indicating that the slave server can connect to the master server normally.

Slave_IO_Running:Yes Slave_SQL_Running:YesMariaDB [(none)] > show slave status\ G * * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.64.7 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000004 Read_Master_Log_Pos: 245 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 531 Relay_Master_Log_File: mariadb-bin.000004 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: 245 Relay_Log_Space: 827 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: 0Master_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: 11 row in set (0.00 sec) ERROR: No query specified

Note:

The first grant command is to create an account repluser for master-slave replication, which can be created on the host of master1 and master2.

The second grant command is to create the MHA management account manager, which needs to be created on all mysql servers. MHA will require remote login to the database in the configuration file, so do the necessary authorization.

4. Install and configure mysql-MHA

Mha includes manager node and data node, data node includes at least 3 hosts in the original MySQL replication structure, that is, 1 master and 2 slaves, when masterfailover, the master-slave structure can be guaranteed; the master-slave replication cluster only needs to install the node package.

Manager server: run the monitoring script, responsible for the monitoring and auto-failover;mha manager nodes need to install the Node package and manager package.

1. Two operation management nodes (mha4mysql-manager-0.55-0.el6.noarch.rpm and mha4mysql-node-0.54-0.el6.noarch.rpm) need to be installed on the manager host, and only the node node of MHA needs to be installed on the three database hosts.

[root@manager ~] # ls192.168.64.17 cobbler.ks mha4mysql-manager-0.55-0.el6.noarch.rpm192.168.64.27 ks-post.log mha4mysql-node-0.54-0.el6.noarch.rpm192.168.64.7 ks-post-nochroot.log original-ks.cfganaconda-ks.cfg ks-pre.log [root@manager ~] # yum install mha*

The other three database nodes need to install MHA's node node (the process is brief)!

Configure MHA

Like most Linux applications, the proper use of MHA depends on reasonable configuration files. The configuration file of MHA is similar to the my.cnf file configuration of mysql, which is configured in the way of param=value. The configuration file is located in the management node, and usually includes the hostname, mysql user name, password, working directory and so on of each mysql server.

(1.) Edit / etc/masterha/app1.conf, which reads as follows:

[root@manager ~] # vim / etc/mastermha/app1.cnf [server default] 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.64.7candidate_master= 1 [server2] hostname=192.168.64.17candidate_master= 1 [server3] hostname=192.168.64.27candidate_master=1

Explanation of configuration items:

Manager_workdir=/masterha/app1 / / set the working directory of manager manager_log=/masterha/app1/manager.log / / set the log of manager user=manager// setting Monitoring user manager password=123456 / / Monitoring user manager password ssh_user=root / / ssh connection user repl_user=mharep / / Master-Slave replication user repl_password=123.abc / / Master-Slave replication user password ping_interval=1 / / set the monitoring master library, the time interval between sending ping packages, default is 3 seconds Try to automatically railover master_binlog_dir=/usr/local/mysql/data / / set the location where master saves binlog when there is no response three times, so that MHA can find the log of master. Here, the data directory candidate_master=1// of mysql is set to candidate master. If this parameter is set, this slave database will be promoted to master database after master-slave switching occurs.

SSH validity verification:

[root@manager] # masterha_check_ssh-- conf=/etc/mastermha/app1.cnf Sun Apr 22 06:36:33 2018-[warning] Global configuration file / etc/masterha_default.cnf not found. Skipping./etc/masterha/app1.cnf:No such file or directory at / usr/share/perl5/vendor_perl/MHA/SSHCheck.pm line 148.[ root@manager ~] # masterha_check_ssh masterha_check_ SSH [root @ manager ~] # masterha_check_ssh-- conf=/etc/mastermha/app1.cnf Sun Apr 22 06:37:13 2018-[warning] Global configuration file / etc/masterha_default.cnf not found. Skipping.Sun Apr 22 06:37:13 2018-[info] Reading application default configurations from / etc/mastermha/app1.cnf..Sun Apr 22 06:37:13 2018-[info] Reading server configurations from / etc/mastermha/app1.cnf..Sun Apr 22 06:37:13 2018-[info] Starting SSH connection tests..Sun Apr 22 06:37:18 2018-[debug] Sun Apr 22 06:37:14 2018-[debug] Connecting via SSH from root@192.168.64. 27 (192.168.64.27 to root@192.168.64.7 22) Sun Apr 22 06:37:16 2018-[debug] ok.Sun Apr 22 06:37:16 2018-[debug] Connecting via SSH from root@192.168.64.27 (192.168.64.27 debug 22) to root@192.168.64.17 (192.168.64.17 debug 22). Sun Apr 22 06:37 17 2018-[debug] ok.Sun Apr 22 06:37:18 2018-[debug] Sun Apr 22 06:37:14 2018-[debug] Connecting via SSH from root@192.168.64.17 (192.168.64.17 Sun Apr 22) to root@192.168.64.7 (192.168.64.7 Sun Apr 22).. Sun Apr 22 06:37:16 2018-[debug] ok.Sun Apr 22 06:37:16 2018-[debug] Connecting Via SSH from root@192.168.64.17 (192.168.64.17) to root@192.168.64.27 (192.168.64.27). Sun Apr 22 06:37:17 2018-[debug] ok.Sun Apr 22 06:37:18 2018-[debug] Sun Apr 22 06:37:13 2018-[debug] Connecting via SSH from root@192.168.64.7 (192.168.64.7) to root@192 .168.64.17 (192.168.64.17 Sun Apr 22). Sun Apr 22 06:37:17 2018-[debug] ok.Sun Apr 22 06:37:17 2018-[debug] Connecting via SSH from root@192.168.64.7 (192.168.64.7 ok.Sun Apr 22) to root@192.168.64.27 (192.168.64.27 ok.Sun Apr 22).. @ @ @ WARNING: REMOTE HOST IDENTIFICATION HAS CHANGED! @ IT IS POSSIBLE THAT SOMEONE IS DOING SOMETHING NASTY ! Someone could be eavesdropping on you right now (man-in-the-middle attack)! It is also possible that a host key has just been changed.The fingerprint for the ECDSA key sent by the remote host isSHA256:080GPU/VjQmyb/Ije4lASHgZDXJv5E/QOqAcAv0wfV0.Please contact your system administrator.Add correct host key in / root/.ssh/known_hosts to get rid of this message.Offending ECDSA key in / root/.ssh/known_hosts:1Keyboard-interactive authentication is disabled to avoid man-in-the-middle attacks.Sun Apr 22 06:37:18 2018-[debug] ok.Sun Apr 22 06:37:18 2018-[info] All SSH connection tests passed successfully.

Validity verification of cluster replication:

Note: mysql databases must be started

[root@manager] # masterha_check_repl-- conf=/etc/mastermha/app1.cnf

Note: if the verification is successful, all servers and master-slave conditions will be automatically identified!

If you encounter this error during verification: Can't exec "mysqlbinlog".

The workaround is to execute on all servers:

[css] view plain copyln-s / usr/local/mysql/bin/* / usr/local/bin/

Start manager:

[root@manager] # masterha_manager-- conf=/etc/mastermha/app1.cnf Sun Apr 22 06:39:35 2018-[warning] Global configuration file / etc/masterha_default.cnf not found. Skipping.Sun Apr 22 06:39:35 2018-[info] Reading application default configurations from / etc/mastermha/app1.cnf..Sun Apr 22 06:39:35 2018-[info] Reading server configurations from / etc/mastermha/app1.cnf..

Note: when applying Unix/Linux, we usually want a program to run in the background, so we will often use & at the end of the program to let the program run automatically. For example, we want to run mysql in the background: / usr/local/mysql/bin/mysqld_safe-user=mysql&. But there are a lot of programs that are not like mysqld, so we need nohup commands.

Fifth, simulate faults and detect state transfer

1. Stop the mariadb service of master1

[root@master ~] # systemctl stop mariadb

(2.) View MHA Log

The log location specified in the above configuration file is / data / masterha/app1/manager.log

[root@manager ~] # cat/masterha/app1/manager.log

From the log information, you can see that master failover has been successful, and you can see the general process of failover.

(3) check the replication of slave

Log in to the Mysql of slave (192.168.64.27) to view the slave status

Mysql > show slave status\ G

You can see that the IP of master is now 192.168.64.17, which has been synchronized with 192.168.64.27 and originally synchronized with 192.168.64.7, indicating that MHA has promoted Candicatemaster (master2) to the new master,IO thread and SQL thread to run correctly, and the MHA has been built successfully!

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.