MySQL Master Master Interop mode (keepalived)

MySQL Dual master-high availability

1. Single DB instance Installation
   

   Please refer to: Binary package installation MySQL

2. Resource planning

Host Name	OS Version	MySQL version	Host IP	MySQL VIP
Db01.lyk.com	CentOS 6.4	Mysql-5.6.21-linux-glibc2.5-x86_64	172.31.30.12	172.31.30.222
Db02.lyk.com	CentOS 6.4	Mysql-5.6.21-linux-glibc2.5-x86_64	172.31.30.11

3. Modify the MySQL configuration file

To modify the DB01 configuration file:

vi/etc/my.cnf# in [mysqld] Add the following #server-id = 100log-bin =/usr/local/mysql/data/ttpai-binbinlog_format = MIXED # Non-required Relay-log =/usr/local/mysql/data/ttpai-relay-binbinlog-ignore-db = Mysqlbinlog-ignore-db = Testbinlog-ignore-db = information_schemabinlog-ignore-db = Performance_schemareplicate-wild-ignore-table = mysql.% replicate-wild-ignore-table = test.%replicate-wild-ignore-table = Information_schema.%replicate-wild-ignore-table = performance_schema.%

To modify the DB02 configuration file:

vi/etc/my.cnf# in [mysqld] Add the following #server-id = 110log-bin =/usr/local/mysql/data/ttpai-binbinlog_format = MIXED # Non-required Relay-log =/usr/local/mysql/data/ttpai-relay-binbinlog-ignore-db = Mysqlbinlog-ignore-db = Testbinlog-ignore-db = information_schemabinlog-ignore-db = Performance_schemareplicate-wild-ignore-table = mysql.% replicate-wild-ignore-table = test.%replicate-wild-ignore-table = Information_schema.%replicate-wild-ignore-table = performance_schema.%

4. manually synchronizing the database

If you already have MySQL data on the DB01, then you need to synchronize the two MySQL data on the DB01 and DB02 before performing the primary master interop, first back up the MySQL data on DB01 and execute the following sql:

Mysql> FLUSH TABLES with READ LOCK;

Without exiting the terminal (eject lock failure), open a session, directly package MySQL data file or mysqldump tool export:

Cd/usr/local/mysql/tar ZCVF data.tar.gz data/

Transfer the data.tar.gz to DB02 and restart DB01 and DB02 in turn.

In fact, you can use the Mysqldump statement to back up without executing the Read lock statement, at the very least the personal test is that the data is not lost or the synchronization exception occurs. Use the following command:

Mysqldump--DEFAULT-CHARACTER-SET=GBK--opt--triggers-r-e--hex-blob--single-transaction--master-data=2 ttpai > Ttpai.sql

Where--master-data=2 can lock the Binlog file name and coordinates.

5. Create an authorized replication user

DB01 Execution:

Mysql> Grant REPLICATION SLAVE on * * to [e-mail protected] ' 172.31.30.11 ' identified by ' lyk123 ';

DB02 Execution:

Mysql> Grant REPLICATION SLAVE on * * to [e-mail protected] ' 172.31.30.12 ' identified by ' lyk123 ';

Where the value of the coordinate can be executed as follows SQL:

mysql> show master status;+------------------+-----------+--------------+------------------- -------------------------------+-------------------+| file              | position  | binlog_do_db | binlog_ignore_ db                                  | executed_gtid _set |+------------------+-----------+--------------+--------------------------------------------------+- ------------------+| ttpai-bin.000093 | 502389306 |               | mysql,test,information_schema,performance_schema |                     |+------------------+-----------+--------------+--------------------------------------------------+-------------------+ 

If you use mysqldump, you can use the following command:

Head-n50 Ttpai.sql

6. start the mutual master slave mode

Execute on DB02:

Mysql>change MASTER to master_log_file= ' ttpai-bin.000050 ', master_log_pos=754861035,master_host= ' 172.31.30.12 ', Master_user= ' lYK ', master_password= ' lyk123 '; mysql>start slave;

Execute on DB01:

Mysql>change MASTER to master_log_file= ' ttpai-bin.000050 ', master_log_pos=754861035,master_host= ' 172.31.30.12 ', Master_user= ' lYK ', master_password= ' lyk123 '; mysql>start slave;

To verify the synchronization results:

Mysql>show slave status\g;

Since this MySQL mutual master from has been finished.

7. Installing lvs+keepalived

Yum instal keepalived Ipvsadm

8. Edit the keepalived configuration file

Configuration file for DB01:

vi /etc/keepalived/keepalived.conf global_defs {   notification_email {      [email protected]   }   notification_email_ From [email protected]   smtp_server 127.0.0.1   smtp_connect_ timeout 30   router_id lvs_mysql}vrrp_instance ha_1 {     state backup    interface bond0    virtual_router_id  80    priority 100    advert_int 2     nopreempt        authentication {         auth_type pass        auth_pass 1111     }        virtual_ipaddress {       172.31.30.222        }}virtual_server 172.31.30.222 3306 {     delay_loop 2    lb_algo wlc    lb_ kind dr    nat_mask 255.255.255.0    protocol tcp     persistence_timeout 60    real_server 172.31.30.12 3306  {        weight 1         notify_down /etc/keepalived/mysqlcheck/mysql.sh        tcp_ check {            connect_port 3306             connect_timeout 3             nb_get_retry 2             delay_before_retry 1        }     }}

One of the things to note is:

• State BACKUP #DB01和DB02均配置为BACKUP状态

• Nopreempt #为不强占模式, DB02 (slave) not set

• Notify_down #定义监测realserver失败下一步的动作

• Priority #DB02要比DB01低
  

• Interface Bond0 #没什么特别, but it was the most time-consuming pain point of my entire erection, explained later
  

Scripts to create Notify_down (both DB01 and DB02 are created):

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

is actually to kill keepalived process, release VIP.

Configuration file for DB02:

global_defs {   notification_email {     [email  protected]   }   notification_email_from [email protected]    smtp_server 127.0.0.1   smtp_connect_timeout 30   router_id  LVS_Mysql}vrrp_instance HA_1 {    state BACKUP     interface bond0    virtual_router_id 80    priority  90    advert_int 2         authentication {        auth_type pass         auth_pass 1111    }         virtual_ipaddress {      172.31.30.222         }}virtual_server 172.31.30.222 3306 {    delay_loop 2    lb_algo wlc     lb_kind DR    nat_mask 255.255.255.0     protocol tcp    persistence_timeout 60    real_server  172.31.30.11 3306 {        weight 1         notify_down /etc/keepalived/mysqlcheck/mysql.sh         TCP_CHECK {             connect_port 3306             Connect_timeout 3            nb_get_retry  2            delay_before_retry 1         }    }} 

DB01 and DB02 start-up keepalived:

/etc/init.d/keepalived start

9. Testing

Test master-Slave synchronization via VIP connection:

Mysql-h172.31.30.222-utest-ptest ttpaimysql> show vaiables like ' server_id '; #可以确认那台为master状态mysql > CREATE Table ... #到slave状态的数据库看都是否正常 .....

Test keepalived for MySQL failover:

Can be executed at DB01

/etc/init.d/mysqld stop

Remote always executes

Show vaiables like ' server_id ';

Discover that the card 1~3s,server_id becomes 110, that is, Master becomes DB02.

Even if the restart DB01,VIP will not be transferred, because the use of the non-preemption mode, until the DB02 service is hung, the VIP will be transferred.

Ten: summary

For the next interface bond0:

All the configuration is very smooth, but in the test, found VIP connection database, the execution of SQL card, ping VIP in Windows or VIP DB host IP, drops, there is no sense of the broken egg. began to confused, and then carefully looked at the principle of the LVS Dr Mode, the original and the MAC address associated, and their own environment Nic did Bond0 (mode=0), but on the switch did not do port aggregation, you can think of network card mac disorder.

Solve:

Configuring the Switch (H3C)

#创建端口聚合组 (different models, different commands, not the same) interface bridge-aggregation2description bondingport Access VLAN 30interface Bridge-aggregation3description bondingport Access VLAN 30# will make the bonding NIC join the corresponding aggregation group interface Gigabitethernet1/0/3port Access VLAN 30port link-aggregation Group 3 ...

Everything back to normal, this incident occurred, I do not know is too low-end, or we have not met, search information really did not receive, hehe.

Attention:

LVS Dr Principle

Note Points for bonding mode

This article is from the "Focus on the Database" blog, please be sure to keep this source http://lgdvsehome.blog.51cto.com/3360656/1591525

MySQL Master Master Interop mode (keepalived)