This is the 7th day of my participation in Gwen Challenge

preface

Recently, I did a project, which considered some risks, including the risk of mysql downtime. Mysql applied for two servers. So I decided to do a master master copy, with Keepalived drift to achieve high availability.

Feasibility of scheme

First of all, master master replication ensures data synchronization between two machines. Keeplalived uses virtual IP to ensure quick switching between our mysql servers. The scheme is feasible.

Set up steps

First we prepare two mysql servers that have been set up. The IP addresses are 15.1.1.46 and 15.1.1.50 respectively

Modifying a Configuration File

In the my.cf file on the first mysql server, we added the following.

Server-id =1 log-bin=mysql-bin binlog-ignore-db=mysql binlog-ignore-db=information_schema Binlog-do-db =mydatabase (optional) binlog_format=STATEMENT auto-increment-increment = 2 Auto-increment-offset = 1Copy the code

The overall situation after the increase is as follows:

[mysqld] server-id=1 log-bin=mysql-bin binlog_format=STATEMENT auto-increment-increment = 2 auto-increment-offset = 1 Sql_mode =NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES basedir = /home/mysql-5.7.25 datadir = /home/mysql-5.7.25/data port Sock character-set-server=utf8 log-error = /home/mysql-5.7.25/data/mysqld.log pid-file = / home/mysql - 5.7.25 / data/mysqld pidCopy the code

The first machine is configured. The second machine is configured as follows:

server-id=2
log-bin=mysql-bin
binlog_format=STATEMENT
auto-increment-increment = 2
auto-increment-offset = 2
Copy the code

Increment is the same as the first one, but increment is different to prevent self-increasing conflicts. The overall configuration file is as follows:

[mysqld] server-id=2 log-bin=mysql-bin binlog_format=STATEMENT auto-increment-increment = 2 auto-increment-offset = 2 Sql_mode =NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES basedir = /home/mysql-5.7.25 datadir = /home/mysql-5.7.25/data port Sock character-set-server=utf8 log-error = /home/mysql-5.7.25/data/mysqld.log pid-file = / home/mysql - 5.7.25 / data/mysqld pidCopy the code

Restart both machines

service mysql restart
Copy the code

The second machine copies the first, and the first machine acts as the host

The first machine executes as follows:

GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123456';
Copy the code

Display the current bin-log location

show master status;
Copy the code



Remember file and postion at the top

Switch to the second machine and perform the following

change master to Master_host = '15.1.1.46, master_user =' root ', master_password = '123456', master_log_file = '000001' mysql - bin., master_log_pos = 15 4.Copy the code

Open the copy

start slave;
Copy the code

Shows slave replication

show slave status\G;
Copy the code



Both are yes to prove successful replication.

The first machine replicates the second, and the second serves as the host

The second machine executes as follows:

GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123456';
Copy the code

Show the position of the master

show master status;
Copy the code

Switch to the first machine and run the following command

change master to Master_host = '15.1.1.50, master_user =' root ', master_password = '123456', master_log_file = '000007' mysql - bin., master_log_pos = 15 4.Copy the code

Then start replication start slave; Show slave status\G; If both are yes in the figure below, the replication is successful.

Keepalived drift ensures high availability

GCC openssl-devel popt-devel (2) install keepalived and decompress tar -zxvf keepalive-1.2.15.tar.gz CD keepalived-1.2.15./configure –prefix=/usr/local/keepalived make && make install (4) the initialization configuration file copy work cp/usr/local/keepalived/etc/rc. D/init. D/keepalived/etc/rc. D/init. D cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig mkdir /etc/keepalived cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/ cp /usr/local/keepalived/sbin/keepalived /usr/sbin/(5) chkconfig –add keepalived chkconfig keepalived on (6) Enter host /etc/keepalived. Modify the keepalived.conf file inside

! Configuration File for keepalived global_defs { router_id MYSQL-HA } vrrp_script check_mysql { script "/home/mysql/mysql.sh" interval 1 weight 2 } vrrp_instance VI_1 { state MASTER interface ens33 virtual_router_id 51 priority 100 advert_int 1 authentication { auth_type PASS auth_pass 1111 } track_script { check_mysql } Virtual_ipaddress {15.1.1.99}}Copy the code

When mysql hangs, Keepalived monitors when mysql hangs, keepalived can drift.

#! / bin/bash ps - ef | grep mysqld | grep -v grep & > / dev/null if [$? - eq 0] then echo "normal operation of the mysql service!!!!!!" Else service Keepalived stop echo "mysql server stopped! Please solve it in time!!" fiCopy the code

(8) Write a script from the machine keepalived above the steps inside the Keepalived conf according to the following change. Nothing else. State MASTER changed to BACKUP priority 100 changed to 90 (9) Keepalived enabled

service keepalived start
Copy the code

conclusion

Mysql high availability has been set up.