MySQL master replication + Keepalived to create a highly available MySQL Cluster

MySQL master replication + Keepalived to create a highly available MySQL Cluster

In response to the company's needs, we have created a safer mysql cluster to implement mysql failover. After several days of research, we can finally share the results.

First, introduce the functions of this cluster solution.

1. the mysql server is automatically transferred after the fault is rectified, and is automatically switched back after the repair.

2. Automatic mysql service failover, which is automatically switched back after repair

3. transfer within several seconds

The following describes the experiment environment. Modify the response parameters as needed.

Production Environment MySQL master/Master synchronization primary key conflict handling

MySQL + KeepAlived + LVS single-point write master/Master synchronization high-availability architecture Experiment

MySQL master/Master synchronization Configuration

MySQL master-slave copy notes in CentOS 6.3

MySQL master-master replication in Linux

Lab environment:

Mysql1 ip: 10.1.1.20

Mysql2 ip: 10.1.1.21

Mysql vip: 10.1.1.25

Centos 6.5 32-bit (Virtual Machine environment) is installed on all three machines)

Experiment started !!!

1. Install mysql and create master-master synchronization.

I believe that the master-slave synchronization will be done by everyone. In the same way, the master-master synchronization is the relationship between the two machines, and the write on any machine will be synchronized.

The process of installing mysql is not explained. yum is fine.

Configure master/Master Synchronization

1. Configure/etc/my. cnf

[Mysqld]

Datadir =/var/lib/mysql

Socket =/var/lib/mysql. sock

User = mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

Symbolic-links = 0

Log-bin = binlog # enable the binlog Function

Log-bin-index = binlog. index

Sync_binlog = 0

Server_id = 1 # the two machines cannot be the same. One is the same as the other.

 

[Mysqld_safe]

Log-error =/var/log/mysqld. log

Pid-file =/var/run/mysqld. pid

2. Configure the synchronization account on the two machines respectively.

10.1.1.20:

[Root @ localhost ~] # Mysql

Welcome to the MySQL monitor. Commands end with; or \ g.

Your MySQL connection id is 2

Server version: 5.0.77-log Sourcedistribution

 

Type 'help; 'or' \ H' for help. Type '\ C' toclear the buffer.

 

Mysql> GRANT replication slave ON *. * TO 'AB' @ '% 'identified by '123 ';

Query OK, 0 rows affected (0.00 sec)

 

Mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

10.1.1.21:

[Root @ localhost ~] # Mysql

Welcome to the MySQL monitor. Commands end with; or \ g.

Your MySQL connection id is 2

Server version: 5.0.77-log Sourcedistribution

 

Type 'help; 'or' \ H' for help. Type '\ C' toclear the buffer.

 

Mysql> GRANT replication slave ON *. * TO 'AB' @ '% 'identified by '123 ';

Query OK, 0 rows affected (0.00 sec)

 

Mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

Note: This article is written in an experimental environment, so no security issues are taken into account. The synchronization account is also the highest permission. Set the response permission based on the actual situation !!

3. Set Synchronization

10.1.1.20:

Mysql> flush tables with read lock;

Mysql> show master status;

+ --------------- + ---------- + -------------- + ------------------ +

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+ --------------- + ---------- + -------------- + ------------------ +

| Binlog.000003 | 365 |

+ --------------- + ---------- + -------------- + ------------------ +

1 row in set (0.03 sec)

Mysql> unlock tables;

Query OK, 0 rows affected (0.03 sec)

10.1.1.21:

Mysql> change master tomaster_host = '10. 1.1.20', master_port = 3306, master_user = 'AB', master_password = '000000', master_log_file = 'binlog. 100', master_log_pos = 123;

Query OK, 0 rows affected (0.06 sec)

Mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

Mysql> show slave status \ G # After executing this command, pay attention to the following two parameters. All must be yes.

Slave_IO_Running: Yes

Slave_ SQL _Running: Yes

In turn, perform the same operation.

10.1.1.21:

Mysql> flush tables with read lock;

Mysql> show master status;

+ --------------- + ---------- + -------------- + ------------------ +

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+ --------------- + ---------- + -------------- + ------------------ +

| Binlog.000004 | 207 |

+ --------------- + ---------- + -------------- + ------------------ +

1 row in set (0.03 sec)

Mysql> unlock tables;

Query OK, 0 rows affected (0.03 sec)

10.1.1.20:

 

Mysql> change master tomaster_host = '10. 1.1.21 ', master_port = 3306, master_user =' AB ', master_password = '000000', master_log_file = 'binlog. 100', master_log_pos = 123;

Query OK, 0 rows affected (0.06 sec)

Mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

Mysql> show slave status \ G # After executing this command, pay attention to the following two parameters. All must be yes.

Slave_IO_Running: Yes

Slave_ SQL _Running: Yes

The master/Master synchronization is complete. You can perform a simple test to write data on the two machines to see if the data will be synchronized to the other machine.

PS: if the error "Slave_IO_Running: NO" is returned, check whether the account to be synchronized is created normally!

Ii. Install keepalived and set monitoring

Keepalived is installed on two MySQL servers.

First, install keepalived without explanation.

After installation, configure vim/etc/keepalived. conf as follows:

Configuration File 10.1.1.20

! Configuration File for keepalived

Global_defs {

Notification_email {

Acassen@firewall.loc

Failover@firewall.loc

Sysadmin@firewall.loc

}

Notification_email_from Alexandre.Cassen@firewall.loc

Smtp_server 127.0.0.1

Smtp_connect_timeout 30

Router_id LVS_DEVEL

}

 

Vrrp_instance VI_1 {

State backup # both configurations are BACKUP

Interface eth0

Virtual_router_id 51

Priority 100 # priority, and the other is changed to 90

Advert_int 1

Nopreempt # It is not preemptible and can only be set on machines with higher priority. Machines with lower priority are not set.

Authentication {

Auth_type PASS

Auth_pass 1111

}

Virtual_ipaddress {

10.1.1.25

}

}

 

Virtual_server 10.1.1.25 3306 {

Delay_loop 6

Lb_algo wrr

Lb_kind DR

Persistence_timeout 50 # session persistence time

Protocol TCP

 

Real_server 10.1.1.20 3306 {

Weight 3

Notify_down/tmp/nimei. sh # execute this script when detecting that the mysql service is down (the script should be written by yourself)

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

}

}

}

Configuration File 10.1.1.21

! Configuration File for keepalived

Global_defs {

Notification_email {

Acassen@firewall.loc

Failover@firewall.loc

Sysadmin@firewall.loc

}

Notification_email_from Alexandre.Cassen@firewall.loc

Smtp_server 127.0.0.1

Smtp_connect_timeout 30

Router_id LVS_DEVEL

}

 

Vrrp_instance VI_1 {

State backup

Interface eth0

Virtual_router_id 51

Priority 90

Advert_int 1

Authentication {

Auth_type PASS

Auth_pass 1111

}

Virtual_ipaddress {

10.1.1.25

}

}

 

Virtual_server 10.1.1.25 3306 {

Delay_loop 6

Lb_algo wrr

Lb_kind DR

Persistence_timeout 50

Protocol TCP

 

Real_server 10.1.1.21 3306 {

Weight 3

Notify_down/tmp/nimei. sh

TCP_CHECK {

Connect_timeout 10

Nb_get_retry 3

Delay_before_retry 3

Connect_port 3306

}

}

}

Write a script to monitor whether the mysql service is down, and write the script according to the location of the above configuration file.

Vim/tmp/nimei. sh

#! /Bin/sh

Pkill keepalived

The script is very simple. The purpose is to trigger this script when keepalived detects that the mysql service is down, to kill the keepalived process and let another machine take over.

After modification, start the keeplived service.

The whole cluster is set up

Iii. Test

Find a machine and use a virtual ip address to connect to mysql

[Root @ localhost html] # mysql-uab-h 10.1.1.25-p123

Welcome to the MySQL monitor. Commands end with; or \ g.

Your MySQL connection id is 736

Server version: 5.1.66-log Source distribution

 

Copyright (c) 2000,201 2, Oracle and/or its affiliates. All rights reserved.

 

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help; 'or' \ H' for help. type' \ C' to clear the current input statement.

Mysql>

Then you can close a machine or the mysql service of a machine to see if it can be connected !!

Thank you !!

This article permanently updates the link address: