MySQL master/Master/Slave combined with keepalived for High Availability

MySQL master/Master/Slave combined with keepalived for High Availability

Test environment:

Master: 192.168.1.210 (CentOS6.5)

Slave: 192.168.1.211 (CentOS6.5)

VIP: 192.168.1.208

 

MySQL master/Master/Slave Mode Configuration

Step 1: Configure/etc/my. cnf for the Master service

[Mysqld] basedir =/usr/local/mysqldatadir =/var/lib/mysqlport = 3306 socket =/var/lib/mysql. sockserver_id = 1log-bin = mysql-binrelay-log = mysql-relay-binreplicate-wild-ignore-table = mysql. % # specify the database that does not need to be copied, mysql. % indicates all objects in the mysql database replicate-wild-ignore-table = test. % replicate-wild-ignore-table = information_schema. %

Step 2: Configure/etc/my. cnf for the Slave Service

[mysqld]basedir=/usr/local/mysqldatadir=/var/lib/mysqlport=3306socket=/var/lib/mysql/mysql.sockserver_id=2log-bin=mysql-binrelay-log=mysql-relay-binreplicate-wild-ignore-table=mysql.%replicate-wild-ignore-table=test.%replicate-wild-ignore-table=information_schema.%

Step 3: restart two master-slave mysql services

[root@master~]#servicemysqldrestartShuttingdownMySQL..[OK]StartingMySQL.[OK][root@slave~]#servicemysqldrestartShuttingdownMySQL..[OK]StartingMySQL.[OK]

 

Step 4: view the Master/Slave log-bin log status

Record the values of File and Position

[root@master~]#mysql-uroot-ppasswd-e'showmasterstatus'Warning:Usingapasswordonthecommandlineinterfacecanbeinsecure.+------------------+----------+--------------+------------------+-------------------+|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|+------------------+----------+--------------+------------------+-------------------+|mysql-bin.000001|414||||+------------------+----------+--------------+------------------+-------------------+

[root@slave~]#mysql-uroot-ppasswd-e'showmasterstatus'Warning:Usingapasswordonthecommandlineinterfacecanbeinsecure.+------------------+----------+--------------+------------------+-------------------+|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|+------------------+----------+--------------+------------------+-------------------+|mysql-bin.000001|414||||+------------------+----------+--------------+------------------+-------------------+

 

Step 5: Create a master-slave synchronization replication user

1. master

mysql>grantreplicationslaveon*.*to'replication'@'192.168.1.%'identifiedby'replication';mysql>flushprivileges;mysql>changemasterto->master_host='192.168.1.211',->master_user='replication',->master_password='replication',->master_port=3306,->master_log_file='mysql-bin.000001',->master_log_pos=414;mysql>startslave;

2. slave

mysql>grantreplicationslaveon*.*to'replication'@'192.168.1.%'identifiedby'replication';mysql>flushprivileges;mysql>changemasterto->master_host='192.168.1.210',->master_user='replication',->master_password='replication',->master_port=3306,->master_log_file='mysql-bin.000001',->master_log_pos=414;mysql>startslave;

Synchronization failure may require stopping or resetting slave

Mysql> stop slave;

Mysql> reset slave;

 

Step 6: Check the Server Load balancer status on the master node and Server Load balancer instance to verify that the master node replication mode is successfully configured.

1. master

2. slave

The MySQL service may need to be restarted during slave status synchronization.

[Root @ master ~] # Service mysqld restart [root @ slave ~] # Service mysqld restart

 

Step 7: Verify that the test1 database is created on the master and the Server Load balancer instance is used to check whether the database is synchronized.

1. Create a database named test1 on the master

[root@master~]#mysql-uroot-ppasswd-e'createdatabasetest1'

2. Check whether test1 is created synchronously on slave.

[root@slave~]#mysql-uroot-ppasswd-e'showdatabases'+--------------------+|Database|+--------------------+|information_schema||mysql||performance_schema||test1|+--------------------+

 

Install and configure keepalived to achieve dual-master high availability of MySQL

Step 1: Install keepalived

Method 1: Use yum to install keepalived. You need to install the epel-release source.

[Root @ master ~] # Rpm-ivh http://mirrors.opencas.cn/epel/6/ I #/epel-release-6-8.noarch.rpm?root@slave ~] # Rpm-ivh http://mirrors.opencas.cn/epel/6/i386/epel-release-6-8.noarch.rpm

[Root @ slave ~] # Yum-y install keepalived

View keepalived Directories

[root@slave~]#ls/usr/sbin/keepalived/usr/sbin/keepalived[root@slave~]#ls/etc/init.d/keepalived/etc/init.d/keepalived[root@slave~]#ls/etc/keepalived/keepalived.conf/etc/keepalived/keepalived.conf

 

Method 2: Download the source code package from the http://www.keepalived.org of keepalived Official Website

1. download the latest version of keepalived.

[Root @ master ~] # Wget http://www.keepalived.org/software/keepalived-1.2.19.tar.gz

[Root @ slave ~] # Wget http://www.keepalived.org/software/keepalived-1.2.19.tar.gz

2. Install the keepalived dependency package

[Root @ master ~] # Yum install pcre-devel openssl-devel popt-devel libnl-devel

3. decompress and install keepalived.

[root@master~]#tarzxfkeepalived-1.2.19.tar.gz[root@master~]#cdkeepalived-1.2.19[[email protected]]#./configure--prefix=/usr/local/keepalived--sysconf=/etc--with-kernel-dir=/usr/src/kernels/2.6.32-431.el6.x86_64

[[email protected]]#make[[email protected]]#makeinstall

View keepalived-Related Files

[[email protected]]#ls/etc/keepalived/keepalived.confsamples[[email protected]]#ls/etc/init.d/keepalived/etc/init.d/keepalived

Link/usr/local/keepalived/sbin/keepalived to/sbin/directory

[[email protected]]#ln-s/usr/local/keepalived/sbin/keepalived/sbin/

Set keepalived startup level

[[email protected]]#chkconfig--addkeepalived[[email protected]]#chkconfig--level35keepalivedon

 

Step 2: Configure keepalived

1. Master's keepalived. conf configuration file

! Detail {notification_email {[email protected] [email protected]} notification_email_fromkeepalived@localhostsmtp_server127.0.0.1smtp _ comment} vrrp_instanceHA_1 {stateBACKUP # both master and slave are configured as BACKUPinterfaceeth0 # specify the virtual_router_id80 # virtual route ID of, the master and slave nodes share the same priority100 # defines the priority, and slave sets 90advert_int2 # sets the interval between the master and slave synchronization checks nopreempt # Do not preemptible mode. Authentication {auth_typePASSauth_pass1111} virtual_ipaddress {# Set virtual IP addresses. You can set multiple virtual IP addresses. Each line has one IP address 192.168.1.208/24deveth0 # For MySQL external services, VIP} virtual_server192.168.1.2083306 {detail {# Listen to the local ipweight3policy_down/usr/local/keepalived/bin/mysql. shTCP_CHECK {connect_timeout8nb_get_retry3delay_before_retry3connect_port3306 }}}

Keepalived detection script. When one of the MySQL services fails, it automatically switches to the normal MySQL server to continue providing services.

[root@master~]#vim/usr/local/keepalived/bin/mysql.sh#!/bin/bashpkillkeepalived

 

2. keepalived. conf configuration file of Slave

! Detail {notification_email {[email protected] [email protected]} notification_email_fromkeepalived@localhostsmtp_server127.0.0.1smtp _ comment} vrrp_instanceHA_1 {stateBACKUP # both master and slave are configured as BACKUPinterfaceeth0 # specify the virtual_router_id80 # virtual route ID of, the master and slave nodes share the same priority90 # defines the priority. slave sets 90advert_int2 # sets the time interval for synchronization checks between the master and slave. authentication {auth_typePASSauth_pass1111} virtual_ipaddress {# Sets virtual IP addresses, each line has one IP address 192.168.1.208/24deveth0 # MySQL external service, that is, VIP }}virtual_server192.168.1.2083306 {detail {# Listen to the local ipweight3policy_down/usr/local/mysql/bin/mysql. shTCP_CHECK {connect_timeout8nb_get_retry3delay_before_retry3connect_port3306 }}}

 

Step 3: grant VIP root User Permissions

Authorize a remote host to log on to MySQL through VIP and test the data replication function.

mysql>grantallon*.*toroot@'192.168.1.208'identifiedby'741616710';mysql>flushprivileges;

Step 4: test the keepalived High Availability Function

1. log on to the remote host and log on to MySQL through VIP192.168.1.208 to view the MySQL connection status.

mysql>showvariableslike'hostname%';+---------------+--------+|Variable_name|Value|+---------------+--------+|hostname|master|+---------------+--------+1rowinset(0.00sec)

From the results shown above, we can see that the master is connected under normal circumstances.

 

2. Test the fault, stop the MySQL Service of the master, and check whether the service is transferred to the slave server again.

[root@master~]#servicemysqldstopShuttingdownMySQL....SUCCESS!

mysql>showvariableslike'hostname%';ERROR2013(HY000):LostconnectiontoMySQLserverduringquerymysql>showvariableslike'hostname%';ERROR2006(HY000):MySQLserverhasgoneawayNoconnection.Tryingtoreconnect...Connectionid:1268Currentdatabase:***NONE***+---------------+-------+|Variable_name|Value|+---------------+-------+|hostname|slave|+---------------+-------+1rowinset(0.01sec)

The test results show that keepalived has successfully transferred the MySQL service.