MySQL master-slave replication and HIGH availability architecture construction of MHA

Introduction to Environment Architecture

The architecture is shown in the figure, and the IP addresses and roles of the four machines are as follows:

The name of the machine IP role permissions
Mysql_Master 192.168.31.199 The Master database Read-write, master library
Mysql_Slave1 192.168.31.165 Database Slave Read-only, slave library
Mysql_Slave2 192.168.31.142 Database Slave Read-only, slave library
Mysql_MHA 192.168.31.126 MHA Manager High availability monitoring

MySQL master-slave setup

MySQL Installation (3 PCS)

  1. If the mariadb database is installed on CentOS7 by default, you may encounter unexpected problems when saving Chinese.

    yum remove mariadb-libs.x86_64
Copy the code

  2. Download the MySql installation package

    wget http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm
Copy the code

  3. Installing software Packages

    rpm -ivh mysql-community-release-el7-5.noarch.rpm
Copy the code

  4. Install the MySQL service program

    yum install mysql-community-server
Copy the code

  5. Restart the MySQL service

    service mysqld restart
Copy the code

    At this point, MySQL is successfully installed on the server.

  6. Change the password

    Mysql -uroot -p mysql -uroot -p mysql -uroot -p mysql -uroot -p

    SET PASSWORD = PASSWORD('root123456');
Copy the code

  7. MySQL: /etc/rc.local MySQL: /etc/rc.local

    /etc/init.d/mysql start
Copy the code

Disabling the Firewall

Different MySQL to visit each other directly, need to close the Linux firewall, otherwise will increase in the config/etc/sysconfifig/iptables rules. Configuring firewalls is not the focus of this article, so all four servers have firewalls turned off.

systemctl stop firewalld
Copy the code

MySQL primary/secondary configuration

The Master node
Run the vi /etc/my. CNF command to modify the Master configuration file
# bin_log configuration
log_bin=mysql-bin 
server-id=1 
sync-binlog=1 
binlog-ignore-db=information_schema 
binlog-ignore-db=mysql 
binlog-ignore-db=performance_schema 
binlog-ignore-db=sys 
# relay_log configuration
relay_log=mysql-relay-bin 
log_slave_updates=1 
relay_log_purge=0
Copy the code
Restart the service
systemctl restart mysqld
Copy the code
The master library grants authorization to the slave library

Log in to MySQL and run the following command:

mysql> grant replication slave on*. *to root@The '%' identified by 'password'; 
mysql> grant all privileges on*. *to root@The '%' identified by 'password'; 
mysql> flush privileges; Master_log_file ='mysql-bin.000007',master_log_pos=154 mysql>show master status;
Copy the code

Slave node

Modify the MySQL configuration file my. CNF of the slaves and set the server ids of the two slaves to 2 and 3 respectively

# bin_log configuration
log_bin=mysql-bin 
Server ID = 2; server ID = 3
server-id=2 
sync-binlog=1 
binlog-ignore-db=information_schema 
binlog-ignore-db=mysql 
binlog-ignore-db=performance_schema 
binlog-ignore-db=sys 
# relay_log configuration
relay_log=mysql-relay-bin 
log_slave_updates=1 
relay_log_purge=0 
read_only=1
Copy the code
Restart the service
systemctl restart mysqld
Copy the code
Open the synchronization

Log in to MySQL and run the MySQL command on the Slave node, for example, run the following command (note that the parameters are the same as those displayed in the show master status operation) :

change master to master_host='192.168.31.199',master_port=3306,master_user='root',master_password ='123456',master_log_file='mysql-bin.000007',master_log_pos=154;

start slave; // Start synchronizationCopy the code

Configure semi-synchronous replication

The Master node

Log in to MySQL and run the following command to install the plug-in

install plugin rpl_semi_sync_master soname 'semisync_master.so';
show variables like '%semi%';
Copy the code

Run the vi /etc/my. CNF command to modify the MySQL configuration file

# Enable semi-synchronous replication automatically
rpl_semi_sync_master_enabled=ON 
rpl_semi_sync_master_timeout=1000
Copy the code

Restarting the MySQL service

systemctl restart mysqld
Copy the code

Slave node

Perform the following steps for both Slave nodes.

Log in to MySQL and run the following command to install the plug-in

install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Copy the code

Run the vi /etc/my. CNF command to modify the MySQL configuration file

# Enable semi-synchronous replication automatically
rpl_semi_sync_slave_enabled=ON
Copy the code

Restart the service

systemctl restart mysqld
Copy the code

Test the semi-synchronization status

Check whether semi-synchronization is enabled by checking parameters in the MySQL command line interface.

show variables like '%semi%';
Copy the code

Then check the MySQL log again.

cat /var/log/mysqld.log
Copy the code

You can view semi-synchronization information in logs, for example:

Start semi-sync binlog_dump to slave (server_id: 2), pos(mysql-bin.000005, 154)
Copy the code

MHA high availability construction

The four machines communicate with each other over SSH

Run the following command on each of the four servers to generate the public and private keys (note: the default value is used by continuously pressing newline enter)

ssh-keygen -t rsa
Copy the code

Run the following command on the three MySQL servers and enter the system password to copy the public key to the MHA Manager server

SSH - copy - id 192.168.31.126Copy the code

Then check on the MHA Manager server to see if the. SSH /authorized_keys file contains three public keys

cat /root/.ssh/authorized_keys
Copy the code

Execute the following command to add the public key of MHA Manager to the authorized_keys file (there should be four public keys at this point)

cat /root/.ssh/id_rsa.pub >> /root/.ssh/authorized_keys 
Copy the code

Execute the following command from the MHA Manager server to distribute the public key information to the other three MySQL servers.

SCP/root /. SSH/authorized_keys [email protected]: / root /. SSH/authorized_keys SCP/root /. SSH/authorized_keys [email protected]: / root /. SSH/authorized_keys SCP/root /. SSH/authorized_keys [email protected]: / root /. SSH/authorized_keysCopy the code

You can run the following command on MHA Manager to check whether SSH communication is achieved with the three MySQL servers.

SSH 192.168.31.199 exit SSH 192.168.31.165 exit SSH 192.168.31.142 exitCopy the code

MHA download and install

  • Nodes need to be installed on three MySQL servers

  • Manager and Node must be installed on the MHA Manager server

MHA node installation

Install mha4mysql-node on four servers.

MHA nodes rely on perl-dbD-mysql, so install perl-dbd-mysql first.

yum install perl-DBD-MySQL -y wget https://github.com/yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node-0.58-0.el7.centos.noarch.rpm RPM - the ivh mha4mysql - node - 0.58-0. El7. Centos. Noarch. RPMCopy the code
MHA manager installation

Install mha4mysql-node and mha4mysql-manager on the MHA Manager server.

The MHA manager relies on perl-Confifig-Tiny, perl-log-Dispatch, and perl-parallel ForkManager, which are also installed separately.

wget http://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm rpm -ivh epel-release-latest-7.noarch.rpm yum  install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager -y wget https://github.com/yoshinorim/mha4mysql- node/releases/download/v0.58 / mha4mysql - node - 0.58-0. El7. Centos. Noarch. RPM RPM - the ivh mha4mysql - node - 0.58-0. El7. Centos. Noarch. RPM wget HTTP: / / https://github.com/yoshinorim/mha4mysql- Manager/releases/download/v0.58 / mha4mysql - manager - 0.58-0. El7. Centos. Noarch. RPM RPM - the ivh Mha4mysql - manager - 0.58-0. El7. Centos. Noarch. RPMCopy the code

MHA configuration file

The MHA Manager server needs to provide a dedicated configuration file for each monitored Master/Slave cluster, and all Master/Slave clusters can share the global configuration.

Initialize the configuration directory
#Directory description 
# /var/log(CentOS directory) 
#/mha (mHA monitoring root directory) 
#/app1 (MHA monitoring instance root directory) 
#/manager.log (MHA monitoring instance log file) 

mkdir -p /var/log/mha/app1 
touch /var/log/mha/app1/manager.log 
Copy the code
Configure the monitoring global configuration file

vim /etc/masterha_default.cnf

[server default] 
Mysql > create a new user from master mysql by executing the following command
#create user 'mha'@'%' identified by '123123';
#grant all on *.* to mha@'%' identified by '123123'; 
#flush privileges; 
user=mha 
password=123123 
port=3306 
# SSH login account
ssh_user=root 
Copy the account and password from the library
repl_user=root 
repl_password=123456 
port=3306 
Number # ping
ping_interval=1 
# Host for secondary check
secondary_check_script=Masterha_secondary_check -s 192.168.31.199 -s 192.168.31.165 -s 192.168.31.142
Copy the code
Configure the monitoring instance configuration file

Run the mkdir -p /etc/mha command to create a directory, and then run the vim /etc/mha/app1. CNF command to edit the file

#MHA monitor instance root directory
manager_workdir=/var/log/mha/app1 
#MHA monitors instance log files
manager_log=/var/log/mha/app1/manager.log 
#[serverx] Indicates the server id
# the hostname hostname
#candidate_master can be used as the master library
#master_binlog_dir Binlog log file directory
[server1] 
hostname=192.168.31.199 
candidate_master=1 
master_binlog_dir="/var/lib/mysql" 

[server2] 
hostname=192.168.31.165 
candidate_master=1 
master_binlog_dir="/var/lib/mysql"

[server3] 
hostname=192.168.31.142 
candidate_master=1 
master_binlog_dir="/var/lib/mysql"
Copy the code

MHA configuration check

Example Perform SSH communication detection

On the MHA Manager server:

masterha_check_ssh --conf=/etc/mha/app1.cnf
Copy the code
Check MySQL primary/secondary replication

On the MHA Manager server:

masterha_check_repl --conf=/etc/mha/app1.cnf
Copy the code

If MySQL Replication Health is OK is displayed, the MySQL Replication cluster is OK.

MHA Manager start

On the MHA Manager server:

nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &
Copy the code

Run the following command to view the monitoring status:

masterha_check_status --conf=/etc/mha/app1.cnf
Copy the code

Run the following command to view monitoring logs:

tail -f /var/log/mha/app1/manager.log
Copy the code

Test MHA failover

Simulate a primary node crash

Run the open log command on the MHA Manager server:

tail -200f /var/log/mha/app1/manager.log
Copy the code

Shut down the Master MySQL server service to simulate a primary node crash

systemctl stop mysqld
Copy the code

View MHA logs to see which slave switches to master

show master status;
Copy the code