Configuration of master-slave mode for MySQL databases

MySQL dual-master-High Availability

Resource Planning

Host name OS version MySQL version host IP address 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

Modify the configuration file of DB01:

Vi/etc/my. cnf
# Add the following content in [mysqld #
Servers-id = 100
Log-bin =/usr/local/mysql/data/ttpai-bin
Binlog_format = MIXED # Not Required
Relay-log =/usr/local/mysql/data/ttpai-relay-bin
Binlog-ignore-db = mysql
Binlog-ignore-db = test
Binlog-ignore-db = information_schema
Binlog-ignore-db = performance_schema
Replicate-wild-ignore-table = mysql. %
Replicate-wild-ignore-table = test. %
Replicate-wild-ignore-table = information_schema. %
Replicate-wild-ignore-table = performance_schema. %

Modify the configuration file of DB02:

Vi/etc/my. cnf
# Add the following content in [mysqld #
Servers-id = 110
Log-bin =/usr/local/mysql/data/ttpai-bin
Binlog_format = MIXED # Not Required
Relay-log =/usr/local/mysql/data/ttpai-relay-bin
Binlog-ignore-db = mysql
Binlog-ignore-db = test
Binlog-ignore-db = information_schema
Binlog-ignore-db = performance_schema
Replicate-wild-ignore-table = mysql. %
Replicate-wild-ignore-table = test. %
Replicate-wild-ignore-table = information_schema. %
Replicate-wild-ignore-table = performance_schema. %

4. Manually synchronize databases

If MySQL data already exists on DB01, you must synchronize the data of the two MySQL databases on DB01 and DB02 before performing master-slave operations. First, back up the MySQL data on DB01, run the following SQL statement:

Mysql> flush tables with read lock;

If you do not exit the terminal (the launch lock becomes invalid), enable a session and directly package the MySQL data file or export it using the mysqldump tool:

Cd/usr/local/mysql/
Tar zcvf data.tar.gz data/

Transmit data.tar.gz to DB02 and restart DB01 and DB02.

In fact, you can use the mysqldump statement to back up data without executing the read lock statement. At least in personal testing, data will not be lost or synchronization exceptions will occur. Run the following command:

Mysqldump -- default-character-set = gbk -- opt -- triggers-R-E -- hex-blob -- single-transaction -- master-data = 2 ttpai> ttpai. SQL

-Master-data = 2 can lock the binlog file name and coordinates.

5. Create an authorized copy User

DB01 execution:

Mysql> grant replication slave on *. * TO lyk @ '192. 31.30.11 'identified by 'lyk123 ';

DB02 execution:

Mysql> grant replication slave on *. * TO lyk @ '192. 31.30.12 'identified by 'lyk123 ';

You can run the following SQL statement to obtain the coordinates:

Mysql> show master status;
+ ------------------ + ----------- + -------------- + -------------------------------------------------- + ----------------- +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+ ------------------ + ----------- + -------------- + -------------------------------------------------- + ----------------- +
| Ttpai-bin.000093 | 502389306 | mysql, test, information_schema, cece_schema |
+ ------------------ + ----------- + -------------- + -------------------------------------------------- + ----------------- +
If mysqldump is used, run the following command:

Head-n50 ttpai. SQL

6. Start the master-slave mode.

Run the following command on DB02:

Mysql> CHANGE MASTER
MASTER_LOG_FILE = 'ttpai-bin.000050 ',
MASTER_LOG_POS = 754861035,
MASTER_HOST = '1970. 31.30.12 ',
MASTER_USER = 'lyk ',
MASTER_PASSWORD = 'lyk123 ';
Mysql> start slave;

Run the following command on DB01:

Mysql> CHANGE MASTER
MASTER_LOG_FILE = 'ttpai-bin.000050 ',
MASTER_LOG_POS = 754861035,
MASTER_HOST = '1970. 31.30.12 ',
MASTER_USER = 'lyk ',
MASTER_PASSWORD = 'lyk123 ';
Mysql> start slave;
Verify the synchronization result:

Mysql> show slave statusG;

Since then, Mysql has completed master-slave interaction.

7. Install lvs + keepalived

Yum instal keepalived ipvsadm

8. Edit the keepalived configuration file.

DB01 configuration file:

Vi/etc/keepalived. conf
Global_defs {
Notification_email {
Sysadmin@lyk.com
   }
Notification_email_from monitor@lyk.com
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
Priority100
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
        }
    }
}

Note the following:

State BACKUP # both DB01 and DB02 are configured as BACKUP
Nopreempt # indicates the non-occupying mode, and DB02 (slave) does not need to be set.
Notify_down # define the next action for monitoring realserver failures
Priority # DB02 is lower than DB01
Interface bond0 # Nothing special, but it is the most time-consuming pain point of my entire building. I will explain it later.
 

Create the policy_down script (both DB01 and DB02 need to be created ):

Mkdir-p/etc/keepalived/mysqlcheck/
Cd/etc/keepalived/mysqlcheck/
Vi mysql. sh
#! /Bin/sh
Pkill keepalived
Chmod u + x mysql. sh

It is actually killing the keepalived process and releasing the VIP.

DB02 configuration file:

Global_defs {
Notification_email {
Sysadmin@lyk.com
   }
Notification_email_from monitor@lyk.com
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
        }
    }
}

Start keepalived for DB01 and DB02:

/Etc/init. d/keepalived start

9. Test

Test master-slave synchronization and connect through VIP:

Mysql-h172.31.30.222-utest-ptest ttpai
Mysql> show vaiables like 'server _ id'; # check that the instance is in the master state.
Mysql> create table... # check whether all databases in the slave status are normal.
.....
Test keepalived for MySQL failover:

It can be executed in DB01.

/Etc/init. d/mysqld stop
 

Remote always-on execution

Show vaiables like 'server _ id ';

Card 1 ~ found ~ 3 s, server_id changes to 110, that is, master changes to DB02.

Even if DB01 is restarted, the VIP will not be transferred because it adopts the non-preemption mode and will not be transferred until the service of DB02 fails.

 

10: Conclusion

Let's talk about interface bond0:

All the configurations are smooth, but during the test, we found that the VIP is connected to the database. When the SQL statement is executed, the host IP address of the VIP or VIP database is pinged under windows, and packet loss occurs; is there any feeling of broken eggs. I started to get confused. Later I carefully read the principle of the lvs DR mode on one side. It was originally associated with the MAC address, and my environment Nic was bond0 (mode = 0 ), however, no port aggregation is performed on the vSwitch, and the nic mac is considered to be disordered.

Solution:

Configure a vSwitch (H3C)

# Create a port aggregation group (different models and commands are called differently)
Interface Bridge-Aggregation2
Description bonding
Port access vlan 30
Interface Bridge-Aggregation3
Description bonding
Port access vlan 30
# Add the bonding Nic to the corresponding aggregation group
Interface GigabitEthernet1/0/3
Port access vlan 30
Port link-aggregation group 3
......
Everything is going back to normal. In this case, I don't know if I am too low-end or if I haven't met anyone. I really didn't receive any information, huh, huh.

Note:

Lvs dr principle

Pay attention to the bonding mode