Running multiple MySQL services on the same machine

Mysql

**********************************************************

The first part is to build multiple MySQL services on a server.

**********************************************************

One, Introduction

In MySQL, there is a mysqld_multi command, can be used in a physical server to run multiple MySQL services, today reference some documents, personally tested and passed, really happy, now the operation of the process to share to everyone!

Operating system: Linux 2.6.13 (Slackware), other Linux should also be similar.

Database: Mysql 4.0.17 source program installation (I believe the latest 5.1.* is also similar, try again some days)

Planning: Running 4 MySQL services:

Assume server name: Db-app (IP is 192.168.0.100),

Assume server name: DB-APP2 (IP is 192.168.0.101),

The following is the second part, which tells about MySQL in Db-app,

Second, prepare

With the MySQL source program installed, assuming the installation of Configura with the choice of chlorine?

./configure--prefix=/usr/local/mysql--datadir=/usr/local/mysql/data1--sysconfdir=/etc

Note:--prefix will install MySQL to/usr/local/mysql,

--datadir Build the database/usr/local/mysql/data1

Sysconfdir is the search path for the MY.CNF configuration file that specifies that MySQL uses as/etc

Other MySQL installation process slightly.

As mentioned in the MySQL management manual: Each MySQL service can be standalone, so it calls a different startup option in a MY.CNF-the GNR value mentioned below, the use of different ports, the generation of the respective sockets file, and the service database is independent ( For more access to the official MySQL website English Management manual.

Mysqld_multi is a service process that manages multiple mysqld, and these service process programs have different UNIX sockets or are listening on different ports. He can start, stop, and monitor the current service status.

----program searches for [mysqld#] segments in MY.CNF (or in--config-file custom profiles), "#" can be any positive integer. This positive integer is the sequence of segments mentioned below, that is, the GNR. The sequence number of the segment is mysqld_multi to distinguish the different segments so that you can control the start, stop, or get his report information for a particular mysqld process. The parameters in these groups are the same as the parameters of the group needed to start a mysqld. However, if you are using multiple services, you must specify a UNIX socket or port for each service (excerpted from http://mifor.4dian.org using the Mysqld_multi program to manage multiple MySQL services).

From the above text you can see that the most important thing in the MySQL service is the my.cnf configuration file.

Now I post my my.cnf file.-----------------------

[Mysqld_multi]

Mysqld =/usr/local/mysql/bin/mysqld_safe

Mysqladmin =/usr/local/mysql/bin/mysqladmin

user = MySQL (use an account to start all MySQL servers, because it is using a similar account.) That one? This account must be each MySQL service to use the account number, preferably the management account, the following password and the same

Password = mypaswd

[Mysqld1]

Port = 3306

Socket =/tmp/mysql.sock1

Pid-file=/usr/local/mysql/data1/db-app1.pid

Log=/usr/local/mysql/data1/db-app.log

DataDir =/usr/local/mysql/data

user = MySQL

[Mysqld2]

Port = 3307

Socket =/tmp/mysql.sock2

Pid-file =/usr/local/mysql/data2/db-app2.pid

DataDir =/usr/local/mysql/data2

Log=/usr/local/mysql/data2/db-app.log

user = MySQL

[MYSQLD3]

Port = 3308

Socket =/tmp/mysql.sock3

Pid-file =/usr/local/mysql/data3/db-app3.pid3

DataDir =/usr/local/mysql/data3

Log=/usr/local/mysql/data3/db-app.log

user = MySQL

[Mysqld4]

Port = 3309

Socket =/tmp/mysql.sock4

Pid-file =/usr/local/mysql/data3/db-app4.pid

DataDir =/usr/local/mysql/data4

Log=/usr/local/mysql/data4/db-app.log

user = MySQL

[Mysqldump]

Quick

Max_allowed_packet = 16M

[MySQL]

No-auto-rehash

[Isamchk]

Key_buffer = 128M

Sort_buffer_size = 128M

Read_buffer = 2M

Write_buffer = 2M

[Myisamchk]

Key_buffer = 128M

Sort_buffer_size = 128M

Read_buffer = 2M

Write_buffer = 2M

[Mysqlhotcopy]interactive-timeout