High-availability Mysql Hot Standby (Mysql_HA)

1. keepAlived + Mysql uses KeepAlived to implement high-availability MYSQL_HA cluster environment. MYSQL is a master-master synchronous replication relationship to ensure data consistency on the MYSQL server. KeepAlived is used to provide virtual IP addresses, keepAlived is used for Fault Monitoring, enabling automatic failover when Mysql fails. The environment topology is as follows :? Mysq

1. in the highly available MYSQL_HA cluster environment using KeepAlived, Mysql is the Master/Master synchronous replication relationship to ensure data consistency on the MYSQL server, use KeepAlived to provide virtual IP addresses and use KeepAlived to monitor faults, enabling automatic failover during Mysql failures. The environment topology is as follows :? Mysq

1. KeepAlived + Mysql

In the high-availability MYSQL_HA cluster environment using KeepAlived, MYSQL is the Master/Master synchronous replication relationship to ensure data consistency on the MYSQL server. KeepAlived is used to provide virtual IP addresses, keepAlived is used for Fault Monitoring, enabling automatic failover when Mysql fails.

The environment topology is as follows:

? Mysql? VIP: 192.168.187.61

Master1: 192.168.187.129

Master: 192.168.187.132

OS environment: Cent OS 5.9

Mysql version: Mysql5.5.31

2. Install mysql 2.1.Mysql

Because the Mysql of CentOS is still at 5.0.19, And we perform synchronous replication between Mysql, the Mysql version must be at least Mysql5.1, so we need to upgrade and install it.

> Using yum for installation, yum can help you solve the dependency conflict.

# Rpm-Uvh? Http://repo.webtatic.com/yum/centos/5/latest.rpm ?? // Install the latest mysql yum Source # Yum-y install Mysql55 MySQL55-* -- enablerepo = webtatic ?? // Install Mysql. The -- enablerepo parameter is used to specify the source

?

> Enable the mysql Service

# Service mysqld start? // Enable the mysql Service

?

> The installation password is blank. Set the root password.

# Mysqladmin-u root password '123 '? // Set the root password ?????

> Change the mysql configuration file

# Cp/usr/share/mysql/my-medium.cnf? /Etc/my. cnf? // Create the mysql configuration file my. cnf in the etc directory # Service mysqld restart // restart the mysql service

?

> Log on to Mysql

# Mysql-u root-p 1234 ?? ? // Set the root password Mysql> ??? ? // Log on to mysql

2.2.Mysql master/Master configuration 2.2.1 configuration file

Mysql replicates logs synchronously. Create a log file first.

# Touch/var/log/mysql/mysql-bin.log ?? // Create a log file # Chown mysql. mysql/var/log/mysql/mysql-bin.log? // Change the user and User Group of the log file to mysql

Configure the my. cnf file on the two mysql servers to be backed up as follows (Add the following configurations to my. cnf of the relevant servers respectively ):

Master1 (192.168.187.129)

Master (192.168.187.132)

# The service ID of the primary logo, which must be unique Server-id = 1 # Because MYSQL synchronizes data based on binary logs, the size of each log file is 1 GB. Log-bin =/var/log/mysql/mysql-bin.log # Name of the database to be synchronized Binlog-do-db = test # Databases that do not record logs, that is, databases that do not need to be synchronized Binlog-ignore-db = mysql # Use the log function on the slave server Log-slave-updates # Write the log file to the hard disk once after 1 log write operation (synchronize the log information once ). N = 1 is the safest practice, but the efficiency is the lowest. The default value is n = 0. Sync_binlog = 1 # Auto_increment: controls the AUTO_INCREMENT action of the auto-increment Column Used for MASTER-MASTER replication to prevent duplicate values, Auto_increment_increment = n indicates the number of servers, and n indicates the number of servers, Auto_increment_offset = 1: Set the step size to 1. In this case, the auto_increment field of the Master will generate the following values: 1, 3, 5, 7 ,... And other odd ID Auto_increment_offset = 1 Auto_increment_increment = 2 # Databases for image processing Replicate-do-db = test # Databases without Image Processing Replicate-ignore-db = mysql

# The service ID of the primary logo, which must be unique Server-id = 2 # Because MYSQL synchronizes data based on binary logs, the size of each log file is 1 GB. Log-bin =/var/log/mysql/mysql-bin.log # Name of the database to be synchronized Binlog-do-db = test # Databases that do not record logs, that is, databases that do not need to be synchronized Binlog-ignore-db = mysql # Use the log function on the slave server Log-slave-updates # Write the log file to the hard disk once after 1 log write operation (synchronize the log information once ). N = 1 is the safest practice, but the efficiency is the lowest. The default value is n = 0. Sync_binlog = 1 # Auto_increment: controls the AUTO_INCREMENT action of the auto-increment Column Used for MASTER-MASTER replication to prevent duplicate values, Auto_increment_increment = n indicates the number of servers, and n indicates the number of servers, Auto_increment_offset = 2: Set the step size to 2. In this way, the value generated by the auto_increment field of the Master is: 2, 4, 6, 8 ,... And other odd ID Auto_increment_offset = 2 Auto_increment_increment = 2 # Databases for image processing Replicate-do-db = test # Databases without Image Processing Replicate-ignore-db = mysql

2.2.2 view configuration information

After configuring the two servers according to the above configuration, restart the mysql service and use showmaster status to check the Master configurations of the two servers. The configuration is successful, as shown below:

NO1: Master1 (192.168.187.129)

# Mysql-u root-p 1234 Mysql> show master status; + ------------------ + ---------- + -------------- + ------------------ + | File ???????????? | Position | Binlog_Do_DB | Binlog_Ignore_DB | + ------------------ + ---------- + -------------- + ------------------ + | Mysql-bin.000001 | ????? 107 | test ???????? | Mysql ??????????? | + ------------------ + ---------- + -------------- + ------------------ +

NO2: Master1 (192.168.187.132)

# Mysql-u root-p 1234 Mysql> show master status; + ------------------ + ---------- + -------------- + ------------------ + | File ???????????? | Position | Binlog_Do_DB | Binlog_Ignore_DB | + ------------------ + ---------- + -------------- + ------------------ + | Mysql-bin.000001 | ????? 107 | test ???????? | Mysql ??????????? | + ------------------ + ---------- + -------------- + ------------------ +

2.2.3 create a permission account for synchronization

A. Create an account and grant the replication slave permission

Mysql> grant replication slave on *. * TO 'slave '@' % 'identified by '123 ';?? // Create an account with the username slave Mysql> flush privileges ;?? // Refresh the related permission table

Because of Bidirectional replication, both servers need to create a user for replication. The preceding statements can be reused on both sides, and the user name and password can be modified on their own.

B. Synchronization settings

The operations on Master1 (192.168.187.129) are as follows:

Mysql> stop slave ;? // Stop slave Mysql> change master to master_host = '192. 168.187.132 ', master_user = 'slave', master_password = '000000', master_log_file = 'mysql-bin.000001 ', master_log_pos = 192 ;? // Modify the value of the current Master. Because the master is a backup of each other, the Master of Master1 (192.168.187.129) is Master2 (192.168.187.132), and Master2 sets the copied user name as slave and the password as 1234, we learned from the show master status above that log_file is a mysql-bin.000001 and postion is 107. Mysql> start slave ;??? // Start salve and start Synchronization Mysql> show slave status ;?? // Check the slave status. If Slave_IO_Running: Yes ??? Slave_ SQL _Running: Yes ??? Seconds_Behind_Master: 0.

?

Master2 (192.168.187.132:

Mysql> stop slave ;? // Stop slave Mysql> change master to master_host = '192. 168.187.129 ', master_user = 'slave', master_password = '000000', master_log_file = 'mysql-bin.000001 ', master_log_pos = 192 ;? // Modify the value of the current Master. Because the master is a backup of each other, the Master of Master2 (192.168.187.132) is Master2 (192.168.187.129), and Master1 sets the copied user name to slave and the password to 1234, we learned from the show master status above that log_file is a mysql-bin.000001 and postion is 107. Mysql> start slave ;??? // Start salve and start Synchronization Mysql> show slave status ;? // Check the slave status. If Slave_IO_Running: Yes ??? Slave_ SQL _Running: Yes ??? Seconds_Behind_Master: 0.

C. Test results:

Step 1: Create a Test table with two fields: id and name. The id field is auto-incrementing. The two servers share the same structure, for example:

Step 2: I run an insert STATEMENT ON THE Master1 (192.168.187.129) Table and query it, for example:

Step 3: In Master2 (192.168.187.132), you can find that the data has been synchronized, for example:

?

3KeepAlived install and configure 3.1 KeepAlived

? See the KeepAlived Installation Method in "high-availability server Load balancer configuration (Haproxy + KeepAlived)" 5.1.

3.2 add keepalived to the service

Refer to "high-availability server Load balancer configuration method (Haproxy + KeepAlived)" 5.2? Add keepalived to the service

3.3 KeepAlived Configuration

After installation, configure it as follows:

There are two machines (MASTER1) in192.168.187.129And (Master2)192.168.187.132,Use (VIP) 192.168.187.61 as the virtual IP address.

Configure keepalived. conf in the/etc/keepalived folder of the two servers:

Master1 settings?

192.168.187.129

Global_defs {

?? Router_id Mysql_HA # Name of the current node

}

Vrrp_instance VI_1 {

??? StateBACKUP??????? # Both configuration nodes are BACKUP

Interface eth0 ?????? # Network interface for binding virtual IP addresses

Virtual_router_id 51 # VRRP group name. The two nodes must have the same settings to indicate that each node belongs to the same VRRP group.

Priority100???????? # Node priority. The priority of the other node is reduced by a bit.

Acvert_int 1 ???????? # Send interval of multicast information. The two nodes must be set the same

Nopreempt ????? # Do not preemptible. You can only set it on a machine with a higher priority. Do not set a machine with a lower priority.

Authentication {????? # Set verification information. The two nodes must be consistent.

??? Auth_type? PASS

??? Auth_pass? 1111

}

Virtual_ipaddress {?? # Specify the virtual IP address. The two nodes must have the same settings.

192.168.187.61

???? }

?? }

Virtual_server 192.168.187.61 3306 {?? # Linux virtual server (LVS) Configuration

Delay_loop 2 ??? ? # Check the real_server status every 2 seconds

Lb_algo wrr ????? # LVS scheduling algorithm, rr | wrr | lc | wlc | lblc | sh | dh

Lb_kind DR ???? ? # LVS cluster mode, NAT | DR | TUN

Persistence_timeout 60 ?? ? # Session persistence time

Protocol TCP ??? # Whether the protocol used is TCP or UDP

Real_server 192.168.187.129 3306 {

Weight 3 ?? # Weight

Notify_down? /Usr/local/bin/mysql. sh ?? ? # Scripts executed after the service is down

TCP_CHECK {

Connect_timeout 10 ?? # Connection timeout

Nb_get_retry 3 ?????? # Number of reconnections

Delay_before_retry 3? # Reconnection Interval

Connect_port 3306 ??? # Health Check Port

}

}

?

Master2 settings?

192.168.187.132

Global_defs {

?? Router_id Mysql_HA # Name of the current node

}

Vrrp_instance VI_1 {

??? StateBACKUP??????? # Both configuration nodes are BACKUP

Interface eth0 ?????? # Network interface for binding virtual IP addresses

Virtual_router_id 51 # VRRP group name. The two nodes must have the same settings to indicate that each node belongs to the same VRRP group.

Priority90???????? # Node priority. The priority of the other node is reduced by a bit.

Acvert_int 1 ???????? # Send interval of multicast information. The two nodes must be set the same

Authentication {????? # Set verification information. The two nodes must be consistent.

??? Auth_type? PASS

??? Auth_pass? 1111

}

Virtual_ipaddress {?? # Specify the virtual IP address. The two nodes must have the same settings.

192.168.187.61

???? }

?? }

Virtual_server 192.168.187.61 3306 {?? # Linux virtual server (LVS) Configuration

Delay_loop 2 ??? ? # Check the real_server status every 2 seconds

Lb_algo wrr ????? # LVS scheduling algorithm, rr | wrr | lc | wlc | lblc | sh | dh

Lb_kind DR ???? ? # LVS cluster mode, NAT | DR | TUN

Persistence_timeout 60 ?? ? # Session persistence time

Protocol TCP ??? # Whether the protocol used is TCP or UDP

Real_server 192.168.187.132 3306 {

Weight 3 ?? # Weight

Notify_down ?? /Usr/local/bin/mysql. sh ?? ? # Scripts executed after the service is down

TCP_CHECK {

Connect_timeout 10 ?? # Connection timeout

Nb_get_retry 3 ?????? # Number of reconnections

Delay_before_retry 3? # Reconnection Interval

Connect_port 3306 ??? # Health Check Port

}

}

Script/usr/local/bin/mysql. sh

# Vi? /Usr/local/bin/mysql. sh #! /Bin/sh Killall keepalived

?

3.4 KeepAlived Test

See KeepAlived test in "high-availability server Load balancer configuration method (Haproxy + KeepAlived)" 5.4.

4. Mysql Testing

Step 1: Open the three servers for viewing. The three servers are empty at the beginning.

Step 2: Insert a data entry to the VIP (192.168.187.61) server.

Step 3: check that all the data on the three servers has been synchronized.

When 192.168.187.129 is turned off as the host for downtime, there will also be no problems, and the virtual IP address will drift from 192.168.187.129

To 192.168.187.132.

?

5. Problems during installation and Solutions

NO1: Slave cannot be linked to the Master

Error:Slave cannot be linked to the Master ??

Cause: Bind-address: The default value is 127.0.0.1. You must change it.

Solution: Modify my. cnf and add the configuration shown in the red box!

NO2: mysql error 1129 error

Error:Mysql 1129 error! For example:

?????

Cause:The reason is that mysql blocks the ip connection.

Solution: Log on to the mysql database server and run the following command:

#? Mysqladmin-u root-p? Flush-hosts ;? Enter password:

?

Author: xuejiazhi posted on 17:36:01 Original article link

Read: 0 comments: 0 view comments

Original article address: high-availability Mysql dual-host Hot Standby (Mysql_HA), thanks to the original author for sharing.