Shulou(Shulou.com)05/31 Report--

This article shows you how to deploy MySQL + Heartbeat + DRBD architecture, the content is concise and easy to understand, it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

1. MySQL is highly available to generate business requirements:

In the actual production scenario of an enterprise, the one-master and multi-slave MySQL database architecture is the most commonly used DB architecture scheme, which is easy to deploy and easy to maintain, and the read and write separation of the master and slave libraries of the application service team can be achieved by configuring simple agents or through programs, and multiple slave libraries can also achieve load balancing of multiple slave libraries through agents such as LVS or Haproxy, sharing the reading pressure and eliminating single point problems. However, in the MYSQL database architecture, it is not difficult to find that although there are multiple slave libraries, there is only one master library, that is to say, once the master library is down, the written business will be terminated, and the downtime of one slave database will not affect it. So how to solve the problem of a single point in the master database? in fact, the simplest solution is to do a good job of monitoring, and then after the master database downtime, the manager will manually select the fastest slave database to change to the master database. Then synchronize the other slave libraries with the new master library, this solution is simple and easy, but it needs to be handled manually, and it is not high enough for some demanding situations, which requires the MySQL+Heartbeat+DRBD architecture solution described below.

MySQL+Heartbeat+DRBD Architecture Scheme description:

Under normal circumstances:

1. Heartbeat monitors and checks peer-to-peer services through serial port lines or Ethernet cable straight-chain network cards, and is responsible for performing automatic switching of resources such as drbd,mysql,vip.

2. MYSQL_S is a highly available hot backup for MYSQL_M. Normally, MYSQL_M provides a partition sdb1 for MYSQL to use.

3. Do RAID10 or RAID0 for physical disks, and choose them according to performance and redundancy requirements.

4. Between servers, servers and switches are directly gigabit network cards to do bongding

5. Application services (including but not limited to web) access MYSQL slaves through VIP, and access load-balanced slave pools through different VIP.

In case of failure:

1. Heartbeat of MYSQL_S performs health check on MYSQL_M through serial port line or Ethernet network cable. When MYSQL_M is found to be dead, services such as DRBD/MYSQL and dynamic switching of load VIP are automatically started on MYSQL_S to ensure that the main library business is taken over normally and services are automatically provided to the outside world.

2. MYSQL on MYSQL_M is in / dev/sdb1 partition, and high availability handover is achieved on MYSQL_S at the same time after failure

3. MYSQL client after failure, slave library, etc. Connected by VIP and MYSQL_S 's MYSQL service, MYSQL provides services normally.

4. When the MYSQL_M fault is fixed, you can automatically retrieve your original business from MYSQL_S, but it is generally not recommended to do so. If there is a performance difference between the two servers, it is best to switch manually.

2. MYSQL high capacity production requirement description

This case assumes that there are three MYSQL servers, and their IP is

MYSQL_M 10.0.0.3

MYSQL_S 10.0.0.4

MYSQL_C 10.0.0.5

The MYSQL directory of MYSQL_M is / data, and the access VIP provided by the front end is 10.0.0.103

Configuration goal: a period of primary MYSQL server MYSQL_M downtime, and the MYSQL and virtual IP on this server will automatically switch when the hot standby server MYSQL_S continues to provide services, so as to achieve the goal of no business impact after high availability downtime of MYSQL

There will be a special problem here, that is, how the previous multiple slave libraries can be synchronized with the new master database, after practice, the MYSQL database is synchronized through DRBD, and when the slave library MYSQL is synchronized with the master library VIP, when the master MYSQL goes down, the VIP drifts to the hot standby master MYSQL. By default, within 60 seconds, the slave library can be connected to the new VIP, thus automatically synchronizing with the new master database. Through MYSQL synchronization to do double master mode, it is difficult to achieve automatic synchronization between master library, stand-alone slave library and new master library. This is also the difficulty of this architecture.

Tip: the master / slave high availability mode of MYSQL database service described in this article is highly available for MYSQL database service, or it can be a two-way high availability mode of master and master.

3. System environment

# # MYSQL_M # Master Library, Master Server

Eth0:10.0.0.3 # Management IP for data forwarding within LAN

Eth2:172.16.1.3 # MYSQL server heartbeat connection (straight chain)

VIP:10.0.0.103 # is used to provide external MYSQL storage system service VIP

# # MYSQL_S # standby server for the main library

Eth0:10.0.0.4

Eth2:172.16.1.4

# # MYSQL_C # mysql slave library

Eth0:10.0.0.5

At the same time, MYSQL_M needs to add a 1G disk, and MYSQL_S needs to add a 1.5G disk. For testing

4. Pre-deployment preparation

# configure hostname and hosts file.

Host name should be based on uname-n

[root@MYSQL_S ~] # uname-n

MYSQL_S

[root@MYSQL_S] # uname-m

X86_64

= =

MYSQL_M

Cp / etc/hosts / etc/hosts.bak

Cp / etc/sysconfig/network / etc/sysconfig/network.bak

Sed-I'$a 10.0.0.3 MYSQL_M' / etc/hosts

Sed-I'$a 10.0.0.4 MYSQL_S' / etc/hosts

Sed-I'/ HOSTNAME=/d' / etc/sysconfig/network

Sed-I'/ $/ aHOSTNAME=MYSQL_M' / etc/sysconfig/network

= =

MYSQL_S

Sed-I'$a 10.0.0.3 MYSQL_M' / etc/hosts

Sed-I'$a 10.0.0.4 MYSQL_S' / etc/hosts

Sed-I'/ HOSTNAME=/d' / etc/sysconfig/network

Sed-I'/ $/ aHOSTNAME=MYSQL_S' / etc/sysconfig/network

# #

Start Test:

[root@MYSQL_S ~] # ping MYSQL_M

PING MYSQL_M (10.0.0.3) 56 (84) bytes of data.

64 bytes from MYSQL_M (10.0.0.3): icmp_seq=1 ttl=64 time=0.347 ms

64 bytes from MYSQL_M (10.0.0.3): icmp_seq=2 ttl=64 time=0.297 ms

^ C

-MYSQL_M ping statistics

2 packets transmitted, 2 received, 0% packet loss, time 1424ms

Rtt min/avg/max/mdev = 0.297 ms 0.322 ms

[root@MYSQL_S ~] # ping MYSQL_S

PING MYSQL_S (10.0.0.4) 56 (84) bytes of data.

64 bytes from MYSQL_S (10.0.0.4): icmp_seq=1 ttl=64 time=0.027 ms

64 bytes from MYSQL_S (10.0.0.4): icmp_seq=2 ttl=64 time=0.043 ms

^ C

-MYSQL_S ping statistics

2 packets transmitted, 2 received, 0% packet loss, time 1226ms

Rtt min/avg/max/mdev = 0.027max 0.035max 0.043Universe 0.008 ms

[root@MYSQL_S ~] #

= =

[root@MYSQL_M ~] # ping MYSQL_S

PING MYSQL_S (10.0.0.4) 56 (84) bytes of data.

64 bytes from MYSQL_S (10.0.0.4): icmp_seq=1 ttl=64 time=0.720 ms

64 bytes from MYSQL_S (10.0.0.4): icmp_seq=2 ttl=64 time=0.346 ms

64 bytes from MYSQL_S (10.0.0.4): icmp_seq=3 ttl=64 time=0.329 ms

^ C

-MYSQL_S ping statistics

3 packets transmitted, 3 received, 0 packet loss, time 2150ms

Rtt min/avg/max/mdev = 0.329 ms 0.465 ms 0.720 pound 0.180

[root@MYSQL_M ~] # ping MYSQL_M

PING MYSQL_M (10.0.0.3) 56 (84) bytes of data.

64 bytes from MYSQL_M (10.0.0.3): icmp_seq=1 ttl=64 time=0.022 ms

64 bytes from MYSQL_M (10.0.0.3): icmp_seq=2 ttl=64 time=0.131 ms

^ C

-MYSQL_M ping statistics

2 packets transmitted, 2 received, 0% packet loss, time 1388ms

Rtt min/avg/max/mdev = 0.022 ms 0.076 max 0.131 max 0.055

[root@MYSQL_M ~] #

# # end

# both servers add the following heartbeat routing production environment recommended to add route. It does not add or affect the construction now.

/ sbin/route add-host 172.16.1.4 dev eth2

/ sbin/route add-host 172.16.1.3 dev eth2

Echo'/ sbin/route add-host 172.16.1.3 dev eth2' > > / etc/rc.local

Echo'/ sbin/route add-host 172.16.1.4 dev eth2' > > / etc/rc.local

# start

[root@MYSQL_M ~] # ping 172.16.1.4

PING 172.16.1.4 (172.16.1.4) 56 (84) bytes of data.

64 bytes from 172.16.1.4: icmp_seq=1 ttl=64 time=1.56 ms

64 bytes from 172.16.1.4: icmp_seq=2 ttl=64 time=0.310 ms

^ C

-172.16.1.4 ping statistics-

2 packets transmitted, 2 received, 0% packet loss, time 1267ms

Rtt min/avg/max/mdev = 0.310, 0.935, 1.561, 0.626 ms

[root@MYSQL_M ~] #

= =

[root@MYSQL_S] # / sbin/route add-host 172.16.1.3 dev eth2

[root@MYSQL_S ~] # ping 172.16.1.3

PING 172.16.1.3 (172.16.1.3) 56 (84) bytes of data.

64 bytes from 172.16.1.3: icmp_seq=1 ttl=64 time=0.391 ms

^ C

-172.16.1.3 ping statistics-

1 packets transmitted, 1 received, 0 packet loss, time 897ms

Rtt min/avg/max/mdev = 0.391 ms 0.391 ms

[root@MYSQL_S ~] #

3. Install heartbeat

Download and install the epel package. Both servers need to operate.

Mkdir-p / home/lvnian/tools & & cd / home/lvnian/tools

Wget http://mirrors.opencas.cn/epel/6/x86_64/epel-release-6-8.noarch.rpm

Rpm-qa | grep epel

[epel-release-6-8.noarch.rpm] & & rpm-ivh epel-release-6-8.noarch.rpm

Rpm-qa | grep epel

B. Install heartbeat. The following is deployed in the CentOS version system. Both servers have to operate. All use yum install heartbeat*-y

Yum install heartbeat-y

C. Copy the configuration file, resource file and license file of ha to / etc/ha.d/

Ll / usr/share/doc/heartbeat-3.0.4/

Cd / usr/share/doc/heartbeat-3.0.4/

Cp ha.cf haresources authkeys / etc/ha.d/

D, start configuration

Cd / etc/ha.d

Mv ha.cf ha.bak

Mv authkeys authkeys.bak

Mv haresources haresources.bak

Note: the following three files, ha.cf, authkeys and haresources, are the same in both master and slave.

# configure ha.cf

Cat > / etc/ha.d/ha.cf CHANGE MASTER TO

-> MASTER_HOST='10.0.0.103', MASTER_PORT=3306

-> MASTER_USER='rep'

-> MASTER_PASSWORD='lvnian123456'

-> MASTER_LOG_FILE='mysql-bin.000003'

-> MASTER_LOG_POS=302

Query OK, 0 rows affected (0.08 sec)

Mysql > start slave

Query OK, 0 rows affected (0.03 sec)

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 10.0.0.4

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000004

Read_Master_Log_Pos: 9759

Relay_Log_File: S_MYSQL-relay-bin.000005

Relay_Log_Pos: 9905

Relay_Master_Log_File: mysql-bin.000004

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:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 9759

Relay_Log_Space: 10106

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

1 row in set (0.00 sec)

ERROR:

No query specified

Mysql >

Successful performance:

[root@S_MYSQL ~] # mysql-uroot-e "show slave status\ G;" | egrep "Slave_IO_Runnin | Slave_SQL_Running"

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

[root@S_MYSQL ~] #

The appearance of the above two yes proves that the master-slave synchronization of the database is successful.

Once again, use heartbeat to enable the mysql master library to switch between master and slave. Take a look at the state of the mysql client, that is, the slave library.

Use the following command to see the master / slave switching and data synchronization in slave database 10.0.0.5:

Mysql-uroot-e "show slave status\ G;" | egrep "Slave_IO_Runnin | Slave_SQL_Running"

For i in `echo 100`; do mysql-uroot-e "show slave status\ G;" | egrep "Slave_IO_Runnin | Slave_SQL_Running"; sleep 10x echo-e "= $I =\ n"; done

Use the following command to switch between active and standby

/ usr/share/heartbeat/hb_standby

/ usr/share/heartbeat/hb_takeover

Write data to the main database

For n in `seq 2000 2300`; do mysql-uroot-e "use lvnian;insert test values"; "sleep 10; done"

For n in `echo 100`; do mysql-uroot-e "use lvnian; select * from test;" | tail-5; sleep 10: echo-e "= $I ="; done

Mysql-uroot-e "use lvnian; select * from test;"

# # #

[root@MYSQL_C ~] # for i in `echo 100`; do mysql-uroot-e "show slave status\ G;" | egrep "Slave_IO_Runnin | Slave_SQL_Running"; sleep 10 X echo-e "= $I =\ n"; done

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

= 1 =

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

= 2 =

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

= 3 =

Slave_IO_Running: No

Slave_SQL_Running: Yes

= 4 =

Slave_IO_Running: No

Slave_SQL_Running: Yes

= 5 =

Slave_IO_Running: No

Slave_SQL_Running: Yes

= 6 =

Slave_IO_Running: No

Slave_SQL_Running: Yes

= 7 =

Slave_IO_Running: No

Slave_SQL_Running: Yes

= 8 =

Slave_IO_Running: No

Slave_SQL_Running: Yes

= 9 =

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

When you use the master / slave switch above, synchronize the master database from the library. Since it takes 10 seconds to check the synchronization, it is concluded that when switching between master and slave, mysql may not have a delay of about 1 minute, which makes it impossible to use mysql database normally.

The above content is how to deploy the MySQL + Heartbeat + DRBD architecture. Have you learned the knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.