Use MySQL + HeartBeat to automatically switch the MySQL master to ensure that the website does not interrupt services

Mysql HA achieves high availability of MYSQL

I. background

Most websites currently use the classic configuration like MYSQL + Linux + APACHE. It is important for website administrators to consider how to prevent the unavailability of the entire website caused by spof, among them, the Database server's high availability is the top priority.

For the high availability of databases, various commercial software vendors have their own solutions, such as Oracle OPS server and IBM DB2 (share-nothing architecture ). Recently, mysql AB also released the MYSQL cluster software, using a technology similar to IBM DB2.

MySQL cluster may be the best solution in the future. However, a simple and practical solution is provided for reference in terms of resource and integration. This article describes one of the most cost-effective Methods: Using Heartbeat 2.0 to configure a Linux high-availability cluster.

II. Implementation Principle

The Linux HA software heartbeat is used to automatically drift IP addresses. That is, when a server goes down, floating IP addresses (the external IP addresses of the entire cluster) automatically drift to another server.

The overall performance of synchronizing multiple databases on different machines through Mysql replication will be reduced by about 1%, and the availability and data security will be greatly improved, at the same time, server switching is transparent to end users, and terminal applications do not need to be changed.

Required hardware

The dual-network card is installed on the same server or worker with roughly the same configuration. The dual-network card is used for the dual-host heartbeat line)

Required Software

Linux HA software heartbeat (only two nodes are supported), the installation disk is integrated with the rpm package of this software

Home: http://www.linux-ha.org/

Mysql software

Home: http://www.mysql.com

Steps

1. Set the hardware environment as follows:

Computer Name:	Nic	IP address
Primary	Eth0 Eth1	192.168.0.10/255.255.255.0 10.0.0.1/255.0.0.0 (for heartbeat)
Backup	Eth0 Eth1	192.168.0.11/255.255.255.0 10.0.0.2/255.0.0.0 (for heartbeat

The eth0 of primary and backup are connected to your vswitch respectively.

The eth1 of primary and backup are directly connected by a patch cord for heartbeat.

Floating IP Address: 192.168.0.8/255.255.255.0

Ii. Install MYSQL

Install mysql database on the primary node

I downloaded it to the local device at the beginning, so I used the software to upload the mysql package to the root directory of the server.

1. Add users and groups running Mysql in linux

[Root @ primary ~] # Groupadd mysql

[Root @ primary ~] # Useradd-g mysql

2. decompress the source code package

[Root @ primary ~] # Tar-zxvf mysql-5.0.22.tar.gz

[Root @ primary ~] # Cd mysql-5.0.22

3. Configure Compilation

Configure the mysql installation directory and set the database storage location

[Root @ primary mysql-5.0.22] #./configure-prefix =/usr/local/mysql \

-Localstatedir =/var/lib/mysql

4. Compile and install

[Root @ primary mysql-5.0.22] # make

[Root @ primary mysql-5.0.22] # make install

5. Load the original authorization to the database

[Root @ primary mysql-5.0.22] #./scripts/mysql_install_db

6. copy the configuration file to the/etc directory.

[Root @ primary mysql-5.0.22] # cp support-files/my-medium.cnf/etc/my. cnf

7. copy the startup script to the Resource Directory

Cp support-files/mysql. server/etc/rc. d/init. d/mysqld

8. Add the mysql service to enable the mysql service automatically when the system starts.

[Root @ primary mysql-5.0.22] # chmod + x/etc/rc. d/init. d/mysqld

[Root @ primary mysql-5.0.22] # chkconfig-level 235 mysqld on

9. Change directory owner/var/lib/mysql

[Root @ primary mysql-5.0.22] # chown-R mysql. mysql/var/lib/mysql

10. Start mysql

[Root @ primary mysql-5.0.22] # service mysqld start

11. Set Environment Variables

[Root @ primary ~] # Add a line to vi/etc/profile. You do not need to enter a long path when running mysql.

Export PATH = $ PATH:/usr/local/mysql/bin

After setting, use the following command to set the setting to take effect immediately.

# Source/etc/profile

Install mysql on the backup server as follows:

Create a database in the mysql database for data synchronization and testing. After mysql is installed, there is an exercise database test, which will be used directly without being created. Create a table in the test database first.

Log on to the mysql database

Mysql-u root-p

(After the command is executed, the system prompts you to enter the password, enter the mysql root User Password correctly, and then log on to mysql. The following operations are performed under the mysql command line)

To use the test database, use the use command to select the database:

Mysql> use test

Create a table

The following uses the create table statement to specify the layout of the table to be created. The following is only a test table. I just created one:

Mysql> create table data (name VARCHAR (20), address VARCHAR (50), phone VARCHAR (20 ));

Query OK, 0 rows affected (0.11 sec)

Add a record to the table and use the insert into statement:

Mysql> insert into data (name, address, phone) values ('jhone', 'beijinging', '123456 ′);

Query OK, 1 row affected (0.02 sec)

View records in a table

Mysql> select * from data;

+ --- + ---- +

| Name | address | phone |

+ --- + ---- +

| Jhone | beijing | 138000000 |

+ --- + ---- +

1 row in set (0.05 sec)

1 2 3 4 Next Page