With load balancing function MySQL server cluster deployment and implementation

1. Introduction

MySQL is a relational database management system (RDBMS) with high-speed, high-performance, multi-threaded, open source code, built on a client/server (CLIENT/SERVER) structure. It started in 1979 and was originally the Unireg database system that Michael Widenius created for the Swedish TCX company, and the Unireg did not have SQL (Structured Query Language Structured Query Language) interface that limited its application. In May 1996, Widenius developed the original version of MySQL and started publishing it on the Internet. MySQL's developers have been concerned about its performance from the outset, regardless of the feature set, until today, MySQL remains the same as the first principle of high speed high-performance.

Over time, MySQL has also added advanced features of large database products, such as stored procedures, views, triggers, and so on, to start deploying applications in Enterprise-class database systems [1]. In October 2008, Sun acquired the MySQL AB Company and began to enter the open source field. With the open source of the heavyweight operating system Solaris, SUN MySQL's share of the database market will increase further. Therefore, to deploy the MySQL server cluster with load balancing function in production environment has great practical significance for improving the speed, stability and scalability of enterprise database application system, and can also reduce the investment cost of application system effectively. This paper will take FreeBSD 7.0-release operating system as an example, using the replication (Replication) characteristics of MySQL database, the implementation method of deploying MySQL server cluster and relevant precautions are briefly introduced.

2. System Model

The structure of this cluster is a primary MySQL server (master) server with multiple dependent MySQL servers (Slave) to establish a replication (replication) connection, the primary server with the secondary server to achieve a certain degree of data synchronization, multiple subordinate servers to store the same copy of the data, Realize data redundancy and provide fault-tolerant function. When deploying the development application system, the database operation code is optimized, the write operation (such as update, INSERT) is directed to the primary server, and a large number of query operations (SELECT) are directed to the subordinate server to realize the load balancing function of the cluster. If the primary server fails, the secondary server transforms the role into the primary server, allowing the application to provide uninterrupted network service to the end user, after the primary server resumes operation, converts it to a subordinate server, stores a copy of the database, and continues to provide the data query retrieval service to the end user.

3. Deployment implementation

In this paper, a host server with three subordinate servers as an example, a brief introduction of the MySQL server cluster implementation scheme and concrete method steps.

3.1 System Deployment

Because the FreeBSD system requirements for machine hardware is lower, in order to reduce the cost of system deployment, both the primary and secondary server operating systems are FreeBSD 7.0-release, with minimal custom installation, and the system takes up only 254M of disk space after completion ( Regardless of the space occupied by the swap partition, it varies with the memory capacity of the specific machine. In order to give full play to the system hardware performance, MySQL database is compiled and installed with source code.

3.1.1 Installation FreeBSD System

Install the FreeBSD 7.0-release on the primary server and the secondary server, the specific installation method step is not the main content of this article, skip, if you have questions, you can refer to the FreeBSD system manual. What needs to be explained is to facilitate the maintenance management during the operation of the user system, to turn on the SSH service function of the system, to allow the root user remote login during the system installation configuration, and to turn off the remote login function of the root user after the normal operation. Set to Yes Permitrootlogin in/etc/ssh/sshd_config configuration file to allow root users to Telnet to the system, set to No is not allowed. After modifying the save file, execute the kill–hup ' cat/var/run/sshd.pid ' command to restart the sshd daemon, making the setting take effect [2]. The above operation requires root user privileges.

3.1.2 Compile and install MySQL

to MySQL official website http://www.mysql.com download MySQL database installation source code compression package, this example download is mysql-5.1.30.tar.gz, as root user login to the system, began to install MySQL database system.

Since the GNU C compiler is required to compile the source code for MySQL, and the FreeBSD system itself does not provide the compiler, the user must download and install the GNU C compiler [3]. To install the downloaded gnumake-3.81.tar.gz, install the GNU C compiler in the following steps:

[root@FreebsdMaster/softwares]#　tar　–zxvf　gnumake-3.81.tar.gz

[root@FreebsdMaster　/softwares]#　cd　make-3.81

[root@FreebsdMaster　/softwares/make-3.81]#　./configure　;　make;　make　install;　make　clean

The GNU C compiler is installed by default to/usr/local/bin, which calls the make command on an absolute path when MySQL is installed, such as #/usr/local/bin/make.

MySQL compiler installation is more complex, the specific operation and related considerations are as follows:

(1). Extract mysql-5.1.30.tar.gz and configure it

[root@FreebsdMaster　/softwares]#　tar　–zxvf　mysql-5.1.30.tar.gz
[root@FreebsdMaster　/softwares]#　cd　mysql-5.1.30

[root@FreebsdMaster　/softwares/mysql-5.1.30]#　./configure　\

-–prefix=/app/mysql5　-–with-charset=gb2312

Description: MySQL default installation directory is/usr/local/mysql, in order to enhance the flexibility of the system deployment, close to the user application system, you can use the--prefix parameters to customize the installation directory. The--with-charset parameter is to enable the MySQL database to support the Chinese gb2312 character set, if you need to support other character sets, use the--with-extra-charset parameter, the format is--with-extra-charset=charset1, CHARSET2, ...