CentOS Lvs+keepalived+mysql implements MySQL database hot standby master replication-Pro-Test OK

Lab Environment:
linux:centos6.6
Virtual IP (VIP): 192.168.135.199
MySQL master:192.168.20.193
MySQL slave:192.168.20.195
(Here just for the sake of convenience to distinguish, a call master, a call slave, in fact, in the main master copy, are Master is also slave, no master and slave points)
Version of MySQL:
[Email protected] ~]# mysql-v
MySQL Ver 14.14 distrib 5.1.73, for Redhat-linux-gnu (x86_64) using ReadLine 5.1

Third, modify two MySQL configuration files

To modify the configuration file for master: vi/etc/my.cnf

#在 [mysqld] Add the following: #

Server-id = 193 #//As long as the Lord is never the same. I'm here with the IP address 3 bit convenient to distinguish Log-bin = Mysql-binbinlog_format = MIXED #非必需relay-log = mysqld-relay-binbinl og-ignore-db = mysqlbinlog-ignore-db = Information_schemabinlog-ignore-db = Performance_ schemareplicate-wild-ignore-table = Mysql.%replicate-wild-ignore-table = information_schema.% Replicate-wild-ignore-table = performance_schema.%

Modify the configuration file for slave: vi/etc/my.cnf
#在 [mysqld] Add the following: #

Server-id = 195log-bin = Mysql-binbinlog_format = MIXED #非必需relay-log = mysqld-relay-binbinlog-ignore-db = Mysqlbinlog-ig nore-db = information_schemabinlog-ignore-db = Performance_schemareplicate-wild-ignore-table = mysql.% replicate-wild-ignore-table = Information_schema.%replicate-wild-ignore-table = performance_schema.%

Iv. manual synchronization of the database

If there is already data on master, it is necessary to synchronize the two MySQL data on master and slave before performing the primary master interop, in many ways, tools Navicat for MySQL, phpMyAdmin, You can also direct the command line mysqldump

Use flush table with read lock before general master Backup, do global read lock, cannot write, then unlock tables after operation; release lock

v. Create an authorized replication user
Master execution:

Mysql> Grant REPLICATION SLAVE on * * to [e-mail protected] ' 192.168.20.193 ' identified by ' 123456 ';mysql> flush Privi Leges;

Slave execution:

Mysql> Grant REPLICATION SLAVE on * * to [e-mail protected] ' 192.168.20.195 ' identified by ' 123456 ';mysql> flush Privi Leges;

Six, start the mutual master from the mode
Execute the following command on the slave, note that slave_io_running and slave_sql_running must all be yes

Mysql> Show Master status;+------------------+----------+--------------+-------------------------------------- -------+| File | Position | binlog_do_db | binlog_ignore_db |+------------------+----------+--------------+------------------------------- --------------+|      mysql-bin.000001 |              334 | | Mysql,information_schema,performance_schema |+------------------+----------+--------------+-------------------- -------------------------+1 Row in Set (0.00 sec) mysql> Change master to master_host= ' 192.168.20.193 ', master_port= 3306, master_user= ' test ', master_password= ' 123456 ', master_log_file= ' mysql-bin.000001 ', master_log_pos=334; Query OK, 0 rows affected (0.06 sec) mysql> start slave; Query OK, 0 rows Affected (0.00 sec) mysql> Show slave status\g*************************** 1. Row *************************** slave_io_state:waiting for master to send event Master_hos t:192.168.20.193 MasTer_user:test master_port:3306 connect_retry:60 Master_log_file:mysql-bin .000001 read_master_log_pos:334 relay_log_file:mysqld-relay-bin.000002 relay_log_p               os:251 relay_master_log_file:mysql-bin.000001 Slave_io_running:yes Slave_sql_running:yes      Replicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:                   replicate_wild_do_table:replicate_wild_ignore_table:mysql.%,information_schema.%,performance_schema.%              last_errno:0 last_error:skip_counter:0 exec_master_log_pos:334 relay_log_space:407 Until_condition:none Until_log_file:until_log_pos: 0 Master_ssl_allowed:no Master_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cer T:master_ssl_cipher:master_ssl_key:seconds_behind_master:0master_ssl_verify_server_cert:no Las t_io_errno:0 last_io_error:last_sql_errno:0 last_sql_error:1 Row in Set (0.0 0 sec)

Execute the following command on master, and note that slave_io_running and slave_sql_running must all be yes

Mysql> Show Master status;+------------------+----------+--------------+-------------------------------------- -------+| File | Position | binlog_do_db | binlog_ignore_db |+------------------+----------+--------------+------------------------------- --------------+|      mysql-bin.000001 |              334 | | Mysql,information_schema,performance_schema |+------------------+----------+--------------+-------------------- -------------------------+1 Row in Set (0.00 sec) mysql> Change master to master_host= ' 192.168.20.195 ', master_port= 3306, master_user= ' test ', master_password= ' 123456 ', master_log_file= ' mysql-bin.000001 ', master_log_pos=334; Query OK, 0 rows affected (0.10 sec) mysql> start slave; Query OK, 0 rows Affected (0.00 sec) mysql> Show slave status\g*************************** 1. Row *************************** slave_io_state:waiting for master to send event Master_hos t:192.168.20.195 MasTer_user:test master_port:3306 connect_retry:60 Master_log_file:mysql-bin .000001 read_master_log_pos:334 relay_log_file:mysqld-relay-bin.000002 relay_log_p               os:251 relay_master_log_file:mysql-bin.000001 Slave_io_running:yes Slave_sql_running:yes      Replicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:                   replicate_wild_do_table:replicate_wild_ignore_table:mysql.%,information_schema.%,performance_schema.%              last_errno:0 last_error:skip_counter:0 exec_master_log_pos:334 relay_log_space:407 Until_condition:none Until_log_file:until_log_pos: 0 Master_ssl_allowed:no Master_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cer T:master_ssl_cipher:master_ssl_key:seconds_behind_master:0master_ssl_verify_server_cert:no Las t_io_errno:0 last_io_error:last_sql_errno:0 last_sql_error:1 Row in Set (0.0 0 sec)

To verify the primary master synchronization results:

Slave execute the following SQL statement

mysql> use hadoop;database changedmysql> CREATE TABLE user (id int (4) NOT null primary key Auto_increment,name Char () not NULL); Query OK, 0 rows affected (0.06 sec) mysql> insert into user values (null,1), (null,2), (null,3); Query OK, 3 Rows Affected (0.00 sec) Records:3 duplicates:0 warnings:0mysql> Delete from user where id=2; Query OK, 1 row Affected (0.00 sec) mysql> drop table user;                                                                                        Query OK, 0 rows Affected (0.00 sec) mysql> CREATE TABLE user (id int (4) NOT null primary key Auto_increment,name Char () not NULL); Query OK, 0 rows affected (0.07 sec) mysql> insert into user values (null,1), (null,2), (null,3); Query OK, 3 Rows Affected (0.00 sec) Records:3 duplicates:0 warnings:0mysql> Delete from user where id=2; Query OK, 1 row Affected (0.00 sec) mysql> Update user set Name= "in 195 machine modification" WHere id=1; Query OK, 1 row affected, 1 Warning (0.00 sec) Rows matched:1 changed:1 warnings:1mysql> select * FROM user;+----+- ---------------------+| ID |  Name |+----+----------------------+| 1 |  In 195 Machine Modification | | 3 | 3 |+----+----------------------+2 rows in Set (0.00 sec)

View on Master

mysql> use hadoop;database changedmysql> show tables; Empty Set (0.00 sec) mysql> SELECT * from user;+----+----------------------+| ID | Name                 |+----+----------------------+|  1 | In 195 machine Modification | |  3 | 3                    |+----+----------------------+2 rows in Set (0.00 sec)

vii. installation of lvs+keepalived

CD/USR/SRC Yum install gcc make wget openssl-devel popt-devel libnl libnl-devel kernel-devel ipvsadm-yln-s/usr/src/ker nels/2.6.32-642.3.1.el6.x86_64//usr/src/linux  wget http://www.keepalived.org/software/ Keepalived-1.2.7.tar.gz tar zxvf keepalived-1.2.7.tar.gz  cd keepalived-1.2.7  ./configure--with-kernel-dir= /usr/src/kernels/2.6.32-358.2.1.el6.x86_64/make && make install  cp/usr/local/etc/rc.d/init.d/ keepalived/etc/rc.d/init.d/  cp/usr/local/etc/sysconfig/keepalived/etc/sysconfig/  mkdir/etc/keepalived  cp/usr/local/etc/keepalived/keepalived.conf/etc/keepalived/  cp/usr/local/sbin/keepalived/usr/sbin/

Eight, configuration keepalived

Vi/etc/keepalived/keepalived.conf

! Configuration File for Keepalivedglobal_defs {notification_email {[email protected]} Notification_email_    From [email protected] smtp_server 127.0.0.1 smtp_connect_timeout router_id lvs_devel}vrrp_instance VI_1 {        State BACKUP interface eth0 virtual_router_id advert_int 1nopreempt Authentication { Auth_type PASS auth_pass 1111} virtual_ipaddress {192.168.20.199}}virtual_server 192.168.20.1     3306 {Delay_loop 6 Lb_algo RR lb_kind DR nat_mask 255.255.255.0 persistence_timeout Protocol TCP   Real_server 192.168.20.193 3306 {weight 1notify_down/etc/keepalived/mysqlcheck/mysql.shtcp_check {connect_port 3306 Connect_timeout 3 nb_get_retry 2 delay_before_retry 1}}} 

One of the things to note is:
State BACKUP #master和slave均配置为BACKUP状态
Nopreempt #为不强占模式, slave (slave) not set
Notify_down #定义监测realserver失败下一步的动作
Priority #slave要比master低
Create Notify_down scripts (both master and slave are created and can be created in master and copied to slave using SCP commands, such as Scp-r mysqlcheck [email protected]:/etc/ keepalived/)

Mkdir-p  /etc/keepalived/mysqlcheck/cd/etc/keepalived/mysqlcheck/vi Mysql.sh#!/bin/bashpkill keepalivedchmod u +x mysql.sh

is to kill the keepalived process and release the VIP

configuration file for Slave:

! Configuration File for keepalivedglobal_defs {   notification_email {     [email protected]   }   notification _email_from [email protected]   smtp_server 127.0.0.1   smtp_connect_timeout-   router_id lvs_devel}vrrp_ Instance vi_1 {State    BACKUP    interface eth0    virtual_router_id    1    Authentication {        Auth_type PASS        auth_pass 1111    }    virtual_ipaddress {        192.168.20.199    }}virtual_server 192.168.20.199 3306  {    delay_loop 6    Lb_algo rr    lb_kind DR    nat_mask 255.255.255.0    persistence_timeout    protocol TCP    real_server 192.168.20.195 3306 {weight 1notify_ Down/etc/keepalived/mysqlcheck/mysql.shtcp_check {  connect_port 3306  connect_timeout 3  nb_get_ Retry 2  delay_before_retry 1}  }

Both master and slave keepalived are configured to set the keepalived to boot and start the service, respectively:

Chkconfig keepalived on    service keepalived start #查看进程 [[email protected] ~]# PS aux | grep keepalivedroot      4696
   
    0.0  0.2 110756  1440?        Ss   16:12   0:00/usr/sbin/keepalived-droot      4698  0.0  0.6 112992  3036?        S    16:12   0:00/usr/sbin/keepalived-droot      4699  0.0  0.4 112860  2252?        S    16:12   0:00/usr/sbin/keepalived-droot      5236  0.0  0.1 103252   864 pts/1    s+   17:04   0:00 grep keepalived
   

And then

Enter the IP a command to see the virtual ip:192.168.20.199

Enter the IPVSADM-LN command to see your IP in the virtual server list is successful

Nine, Test (I was in this machine window test)

First, for the convenience of testing in both master and slave open a remote connection account open, password 123456

Mysql> Grant all privileges on * * to ' open ' @ '% ' identified by ' 123456 ';  mysql> flush Privileges;

1, the native test master-slave synchronization, through the VIP connection:

Go to the MySQL bin directory, connect to the virtual IP remotely using the new remote account above, and then via show variables like ' server_id '; see which MySQL is currently in use

2. Test keepalived to implement MySQL failover:

can be executed in master,/etc/init.d/mysqld stop, remote always execute, show vaiables like ' server_id '; discover that the card 1~3s,server_id becomes 195, that is, master becomes slave. Even if the restart MASTER,VIP will not be transferred, because the use of non-preemptive mode, until the slave service hangs, will transfer VIP

On the left is the analog MySQL hangs off, the right side is the local remote connection VIP status

CentOS Lvs+keepalived+mysql implements MySQL database hot standby master replication-Pro-Test OK