Run multiple Mysql services on the same machine (1)

Source: Internet
Author: User
The first part is to build a multi-mysql service on a server. i. xuyan has the mysqld_multi command in Mysql, which can be used to run multiple Mysql services on a physical server. I am so glad to see some documents and test and pass them in person, share the operation process with you! Operating System: Linux2.6.13 (Slackware). Other versions of Linux should also

The first part is to build a multi-mysql service on a server. i. xuyan has the mysqld_multi command in Mysql, which can be used to run multiple Mysql services on a physical server. I am so glad to see some documents and test and pass them in person, share the operation process with you! Operating System: Linux 2.6.13 (Slackware), other versions of Linux should also

The first part is to build a multi-mysql service on a server.

I. xuyan
There is a mysqld_multi command in Mysql that can be used to run multiple Mysql services on one physical server. I am so glad to refer to some documents and test it and pass it in person. Now I will share the operation process with you!
Operating System: Linux 2.6.13 (Slackware). Other versions of Linux should be similar.
Database: Mysql 4.0.17 source program installation (I believe the latest 5.1. * is similar, try again in a few days)
Plan: run four mysql services:
Assume that the server name is db-app (IP Address: 192.168.0.100 ),
Assume the server name: db-app2 (IP: 192.168.0.101 ),
Mysql in db-app,

2. Preparation
Use the mysql source program for installation. Suppose that the configura option is used during installation. Chlorine?

./Configure -- prefix =/usr/local/mysql -- datadir =/usr/local/mysql/data1 -- sysconfdir =/etc
Note: -- prefix install MYSQL to/usr/local/mysql,
-- Datadir generates/usr/local/mysql/data1 from the database
Sysconfdir is the search path of the my. cnf configuration file used by mysql to/etc.
Other mysql installation procedures are omitted.

According to the Mysql Management Manual, each Mysql service can be independent, so it calls. different startup options in cnf-the GNR value mentioned in the following section, which uses different ports to generate their own socket files, the Service database is independent (for more information, see the mysql official website's English Management Manual ).

Mysqld_multi is a service process that manages multiple mysqld. These service process programs have different unix sockets or listen on different ports. It can start, stop, and monitor the current service status.

---- The program searches for [mysqld #] segments in my. cnf (or in the custom configuration file -- config-file). "#" can be any positive integer. This positive integer is the segment sequence mentioned below, I .e., GNR. The serial number of a segment is used as the parameter of mysqld_multi to distinguish different segments, so that you can control the startup, stop, or obtain report information of a specific mysqld process. The parameters in these groups are the same as those in the group required to start a mysqld. However, if you use multiple services, you must specify a unix socket or port for each service (from using the mysqld_multi program in the http://mifor.4dian.org to manage multiple MySQL services ).

From the above text, we can see that the most important Mysql service is the my. cnf configuration file.
Now I have pasted 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 the same account is used. Which account must be used by every mysql service? It is best to manage the account. The password below is 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 = 16 M

[Mysql]
No-auto-rehash

[Isamchk]
Key_buffer = 128 M
Sort_buffer_size = 128 M
Read_buffer = 2 M
Write_buffer = 2 M

[Myisamchk]
Key_buffer = 128 M
Sort_buffer_size = 128 M
Read_buffer = 2 M
Write_buffer = 2 M

[Mysqlhotcopy]
Interactive-timeout

My configuration files include mysqld1, mysqld2, mysqld3, and mysqld4. That is to say, I will start four mysql services on different ports of the same server -- 3306-3309. The database file paths specified by each datadir are different and each has different log files. Some other settings are available in my. cnf's original content.

OK, important my. cnf compiled, and now create their respective folders and initial databases for different mysql services in the configuration.
[Mysqld1] is a default. It is already available when we install mysql, so don't worry about it.

[Mysqld2]. You only need to create a directory for it based on the configuration path. You can change this directory to mysql management right.

Db-app: // # mkdir/usr/local/mysql/data2

To create a database, we can copy the default mysql database to take advantage of other mysql accounts, and create other databases based on the application.

Db-app: // # cp/usr/local/mysql/data1/mysql/usr/local/mysql/data2-R
Db-app: // # chmod mysql. mysql/usr/local/mysql/data2-R

[Mysqld3], [mysqld4], same.


We may check whether these directories exist.
Db-app:/# ls-l/usr/local/mysql/

Drwxr-xr-x 6 mysql 4096 Apr 9 data4
Drwxr-x --- 2 mysql 4096 Apr 9 data1
Drwxr-xr-x 3 mysql 4096 Apr 9 data2
Drwxr-xr-x 3 mysql 4096 Apr 9 data3

Now you can start it through mysqld_multi.

3. mysqld_multi command.
Use the following parameters to start mysqld_multi: (Note: This command is in the bin directory of mysql, as mentioned above. /configure -- prefix =/usr/local/mysql, so the file should be in/usr/local/mysq/bin, which depends on the path specified during installation)
Db-app:/# mysqld_multi [options] {start | stop | report} [GNR [, GNR]...]

Start, stop, and report are the operations you want to perform. You can specify an operation on a separate service or multiple services, which is different from the GNR list after the option. If the GNR list is not specified, mysqld_multi operates on all services according to the option file.

The value of each GNR is the serial number of a group or the serial number range of a group. The value of this item must be the last number of the group name. For example, if the group name is mysqld17, the value of this item is 17. if a range is specified, use "-" to connect two numbers. If the value of GNR is 10-13, it means that the group mysqld10 is in the group mysqld13. You can specify multiple groups or group ranges in the command line and separate them with commas. Blank characters (such as spaces or tabs) are not allowed. parameters after blank characters are ignored. (Note: The GNR value is the value defined in mysqld # In my. cnf. I only have 1-4 here ).


Db-app: // #/usr/local/mysq/bin/mysqld_multi -- config-file =/etc/my. cnf start 1 only starts the first mysql service. set mysql1 in cnf.

Db-app: // #/usr/local/mysq/bin/mysqld_multi -- config-file =/etc/my. cnf stop 1 start and end the first mysql Service

Db-app: // #/usr/local/mysq/bin/mysqld_multi -- config-file =/etc/my. cnf start 1-4, start the 1st-4 MySQL service, which is actually all here.

Db-app: // #/usr/local/mysq/bin/mysqld_multi -- config-file =/etc/my. cnf report 1-4

View startup:

Db-app:/# ps aux

Root 10467 0.0 0.2 2712 1300 pts/0 S/bin/sh/usr/local/mysql/bin/mysqld_safe -- port = 3306 -- socket =/tmp/mysql. sock1
Root 10475 0.0 0.2 2712 1300 pts/0 S/bin/sh/usr/local/mysql/bin/mysqld_safe -- port = 3307 -- socket =/tmp/mysql. sock2
Root 10482 0.0 0.2 2716 1300 pts/0 S/bin/sh/usr/local/mysql/bin/mysqld_safe -- port = 3308 -- socket =/tmp/mysql. sock3
Root 10487 0.0 0.2 2716 1300 pts/0 S/bin/sh/usr/local/mysql/bin/mysqld_safe -- port = 3309 -- socket =/tmp/mysql. sock4
........................................ .....

4. Client Access
To access any client, you must specify the access port to access the specified database service. Otherwise, the default Mysql port (3306) is used.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.