Detail mycat+haproxy+keepalived to build high availability load balancing mysql Cluster

Summary

Nowadays, clustering is widely used to optimize database performance, while oracle cluster is expensive to invest in software and hardware. mysql is more recommended to use mycat to build database cluster. Here is how to use mycat+haproxy+keepalived to build a high available load balancing system belonging to mysql database.

Main ideas:

Simply put, mysql master and standby replication - > using mycat to achieve load balancing.

Comparing the common ways of separation of reading and writing, mycat is recommended. The community is active and stable.

The following is a demonstration based on centos7 operating system.

Architecture diagram

The following is the architecture of the building.

 

Keeping alived on host 1 and host 2 will preempt VIP for the server. After preempting vip, access to the host can be accessed either through the original ip or directly through vip. When one of the machines goes down, VIP will drift to another machine. When we access VIP, haproxy will distribute requests to two Mycats to achieve load balancing according to configuration, and haproxy will detect M. Is ycat alive?

Next, I will introduce the construction of the lower back-end database.

I. Installation and deployment of mysql

1. Installation Dependence

yum install tcl gcc perl perl-JSON.noarch perl-Time-HiRes.x86_64 -y

2. Create the base directory and modify the subgroup of the folder

mkdir -p /data/datafile
mkdir -p /data/log
chown -R mysql:mysql /data

3. Install mysql database by rpm

3.1 Unloading Self-contained Pack

sudo yum remove -y mariadb*

3.2. Installation of rpm

rpm -ivh mysql-community-common-5.7.26-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-5.7.26-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-compat-5.7.26-1.el7.x86_64.rpm
rpm -ivh mysql-community-devel-5.7.26-1.el7.x86_64.rpm
rpm -ivh mysql-community-embedded-5.7.26-1.el7.x86_64.rpm
rpm -ivh mysql-community-embedded-devel-5.7.26-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-5.7.26-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-5.7.26-1.el7.x86_64.rpm
rpm -ivh mysql-community-test-5.7.26-1.el7.x86_64.rpm

4. mysql database configuration

4.1. Modify my.cnf parameter of database (note server-id parameter)

# vi /etc/my.cnf
[mysqld]
datadir=/data/datafile
socket=/var/lib/mysql/mysql.sock
log-error=/data/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
character_set_server=utf8
bind-address = 0.0.0.0
lower_case_table_names=1
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
symbolic-links=0
max_connections=900
max_allowed_packet=500M
#Binary configuration
server-id = 1
log-bin = /data/log/mysql-bin.log
log-bin-index =/data/log/binlog.index
log_bin_trust_function_creators=1
expire_logs_days=7

4.2. Restart database initialization data

service mysqld restart

4.3. Modify database password

4.3.1. View the default password of the database

#cat /data/log/mysqld.log|grep password

4.3.2. Change password

#mysql -u root -p
set global validate_password_policy=0;
SET PASSWORD = PASSWORD('xxxxx');

4.3.3. Creating databases and users

create schema tms_prod default character set utf8;
set global validate_password_policy=0;
GRANT ALL PRIVILEGES ON *.* TO tms_prod@'%' identified by 'xxx';

4.3.4. Restart mysql database

service mysqld restart

 

2. Configuring mysql master and slave

Principle:

1. Master Library Authorized Synchronization Account

On the primary server, set up an account from the database and use REPLICATION SLAVE to grant permissions, such as:

set global validate_password_policy=0;
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'fxxx';

2. Modify the configuration file my.cnf of the main database

Open BINLOG and set the value of server-id. After modification, Mysql service must be restarted.

#vi /etc/my.cnf
-----------------------------------------configuration file-----------------------------------------
#Ser-id means guaranteeing uniqueness in the entire Mysql cluster
[mysqld]
datadir=/data/datafile
socket=/var/lib/mysql/mysql.sock
log-error=/data/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
character_set_server=utf8
bind-address = 0.0.0.0
lower_case_table_names=1
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
symbolic-links=0
max_connections=900
max_allowed_packet=500M
#Binary configuration
server-id = 1
log-bin = /data/log/mysql-bin.log
log-bin-index =/data/log/binlog.index
log_bin_trust_function_creators=1
expire_logs_days=7
-----------------------------------------configuration file-----------------------------------------

3. View the status of the primary server

You can view the current binary log name and offset of the primary server. The purpose of this operation is to restore data from this point after starting from the database.

mysql> show master status \G;

4. Configuration of mysql from server

Restart database server after configuration modification

#vi /etc/my.cnf
---------------------------------------configuration file-------------------------------------------
[mysqld]
datadir=/data/datafile
socket=/var/lib/mysql/mysql.sock
log-error=/data/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
character_set_server=utf8
bind-address = 0.0.0.0
lower_case_table_names=1
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
symbolic-links=0
max_connections=900
max_allowed_packet=500M
#Binary configuration from database
server-id = 2
log-bin = /data/log/mysql-bin.log
log-bin-index =/data/log/binlog.index
log_bin_trust_function_creators=1
expire_logs_days=7
----------------------------------------configuration file------------------------------------------

5. Open Synchronization from Library

5.1 Configuration synchronization of slave Libraries

When configuring mysql master-slave replication, the CHANGE MASTER TO operation is needed on the slave to determine the host IP, user name, password, binlog file, binlog location and other information that need synchronization.

change master to configure and change the parameters used by slave server to connect master server so that slave server can read the master server's binlog and slave server's relay log. Also update master info and relay log info database. If slave io and sql threads on the slave machine have been started before executing this statement, stop slave must be executed first.

change master to
MASTER_HOST='xx.xx.xx',
MASTER_USER='repl',
MASTER_PASSWORD='xxxx',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=437;

5.2. Open master-slave synchronization

After opening, pay attention to the status of slave library.

When both Slave_IO_Running and Slave_SQL_Running are YES, the master-slave synchronization setup is successful.

mysql> start slave;
mysql> show slave status \G

Tags: Database MySQL RPM mycat

Posted on Wed, 09 Oct 2019 11:31:24 -0700 by smarty_pockets