Run multiple MySQL services on the same machine

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

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.

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

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

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

Tip: create an account for the following master-slave replication. In this example, use the account repl with the password '123456' to allow it to have select_priv, reload_priv, process_priv, grant_priv, super_priv, repl_slave_priv, repl_client_priv permission, and can be accessed by any customer, that is, the access customer is '% '.

It is mainly to modify my. the content in cnf allows each mysql to generate its own bin-log file and its runtime environment. for details about all cnf parameters, see mysql document.

# [Client]

# Password = your_password

# Port = 3306

# Socket =/tmp/mysql. sock

[Mysqld_multi]

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

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

User = mysql

Password = mypasswd

[Mysqld1]

Port = 3306

Socket =/tmp/mysql. sock1

Skip-locking

Pid-file =/usr/local/mysql/data/net-app1a.pid

Datadir =/usr/local/mysql/data

Log =/usr/local/mysql/data/net-app1.log

User = mysql

Log-slow-queries =/usr/local/mysql/data/slowquery. log

Long_query_time = 2

Key_buffer = 256 M

Max_allowed_packet = 1 M

Table_cache = 512

Sort_buffer_size = 2 M

Read_buffer_size = 2 M

Myisam_sort_buffer_size = 64 M

Thread_cache = 32

Query_cache_size = 32 M

Thread_concurrency = 2

Max_connections = 500

Log-bin

Log-bin =/usr/local/mysql/data/app-net1_1-bin

Server-id = 1

[Mysqld2]

Port = 3307

Socket =/tmp/mysql. sock2

Pid-file =/usr/local/mysql/data2/net-app1b.pid

Datadir =/usr/local/mysql/data2

Log =/usr/local/mysql/data2/net-app1.log

User = mysql

Log-slow-queries =/usr/local/mysql/data2/slowquery. log

Long_query_time = 10

Key_buffer = 128 M

Max_allowed_packet = 1 M

Table_cache = 512

Sort_buffer_size = 1 M

Read_buffer_size = 1 M

Myisam_sort_buffer_size = 32 M

Thread_cache = 32

Query_cache_size = 16 M

Thread_concurrency = 2

Max_connections = 300

Log-bin

Log-bin =/usr/local/mysql/data2/app-net1_2-bin

Server-id = 1

[Mysqld3]

Port = 3308

Socket =/tmp/mysql. sock3

Skip-locking

Pid-file =/usr/local/mysql/data3/net-app1c.pid

Datadir =/usr/local/mysql/data3

Log =/usr/local/mysql/data3/net-app1.log

User = mysql

Log-bin

Log-bin =/usr/local/mysql/data3/app-net1_3-bin

Server-id = 1

[Mysqld4]

Port = 3309

Socket =/tmp/mysql. sock4

Skip-locking

Pid-file =/usr/local/mysql/data1/app-net1d.pid

Datadir =/usr/local/mysql/data1

Log =/usr/local/mysql/data1/net-app1.log

User = mysql

Log-bin

Log-bin =/usr/local/mysql/data1/app-net1_4-bin

Server-id = 1

[Mysqldump]

Quick

Max_allowed_packet = 16 M

[Mysql]

No-auto-rehash

# Remove the next comment character if you are not familiar with SQL

# Safe-updates

[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

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

Starting multiple mysql services is the same,

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

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

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

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

Preparations for building the slave server pre-selection: We recommend that you use mysqld_multi to stop all mysql instances on the master server. delete all files in the data directory except the database directory (four data directory libraries in this article are available, data1 -- data1 in datadir =/usr/local/mysql ). both master and slave nodes use the same data directory path.

Run the Tar command to encapsulate each database and run the sftp command put/get to the slave server (db-app1 192.168.0.101 ).

The following operations are for reference:

Operations on the db-app host

Db-app:/# tar-cf data1.tar/usr/local/mysql/data1

Db-app:/# tar-cf data2.tar/usr/local/mysql/data2

Db-app:/# tar-cf data3.tar/usr/local/mysql/data3

Db-app:/# tar-cf data4.tar/usr/local/mysql/data4

Actions on a db-app1 host

Db-app1:/# tar xvf data1.tar

Db-app1:/# tar xvf data2.tar

Db-app1:/# tar xvf data3.tar

Db-app1:/# tar xvf data4.tar

At the same time, make sure that the system account mysql has operation permissions on the mysql data directory on the master/slave server. If you cannot confirm, you can directly modify the ownership of these directories.

Operations on the db-app host

Db-app: // # chown mysql. mysql/usr/local/mysql/data1-R

Db-app: // # chown mysql. mysql/usr/local/mysql/data2-R

Db-app: // # chown mysql. mysql/usr/local/mysql/data3-R

Db-app: // # chown mysql. mysql/usr/local/mysql/data4-R

Actions on a db-app1 host

Db-app1:/# chown mysql. mysql/usr/local/mysql/data1-R

Db-app2:/# chown mysql. mysql/usr/local/mysql/data2-R

Db-app3:/# chown mysql. mysql/usr/local/mysql/data3-R

Db-app4:/# chown mysql. mysql/usr/local/mysql/data4-R

The following is all the content of/etc/my. cnf on the server.

Tip: The following my. cnf will mention an account: repl with the password of '000000'. This account is specially created above.

They are all the same, mainly modifying my. the content in cnf allows each mysql to obtain their own bin-log through different ports of the primary mysql to update the self-generated database content. paste my. all content of cnf (from the server). For details about the parameters, see mysql official manual.

# [Client]

# Password = your_password

# Port = 3306

# Socket =/tmp/mysql. sock

[Mysqld_multi]

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

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

User = mysql

Password = netmoniit

[Mysqld1]

Port = 3306

Socket =/tmp/mysql. sock1

Skip-locking

Pid-file =/usr/local/mysql/data/net-app1a.pid

Datadir =/usr/local/mysql/data

Log =/usr/local/mysql/data/net-app1.log

User = mysql

Log-slow-queries =/usr/local/mysql/data/slowquery. log

Long_query_time = 2

Key_buffer = 256 M

Max_allowed_packet = 1 M

Table_cache = 512

Sort_buffer_size = 2 M

Read_buffer_size = 2 M

Myisam_sort_buffer_size = 64 M

Thread_cache = 32

Query_cache_size = 32 M

Thread_concurrency = 2

Max_connections = 500

Server-id = 2

Master-host = 192.168.0.100

Master-user = 'repl'

Master-password = '000000'

Master-port = 3309

Report-host = net-app1

Master-connect-retry = 30

Log-bin

Log-slave-updates

[Mysqld2]

Port = 3307

Socket =/tmp/mysql. sock2

Pid-file =/usr/local/mysql/data2/net-app1b.pid

Datadir =/usr/local/mysql/data2

Log =/usr/local/mysql/data2/net-app1.log

User = mysql

Log-slow-queries =/usr/local/mysql/data2/slowquery. log

Long_query_time = 10

Key_buffer = 128 M

Max_allowed_packet = 1 M

Table_cache = 512

Sort_buffer_size = 1 M

Read_buffer_size = 1 M

Myisam_sort_buffer_size = 32 M

Thread_cache = 32

Query_cache_size = 16 M

Thread_concurrency = 2

Max_connections = 300

Server-id = 2

Master-host = 192.168.0.100

Master-user = 'repl'

Master-password = '000000'

Master-port = 3309

Report-host = net-app1

Master-connect-retry = 30

Log-bin

Log-slave-updates

[Mysqld3]

Port = 3308

Socket =/tmp/mysql. sock3

Pid-file =/usr/local/mysql/data3/net-app1c.pid

Datadir =/usr/local/mysql/data3

Log =/usr/local/mysql/data3/net-app1.log

User = mysql

Log-slow-queries =/usr/local/mysql/data3/slowquery. log

Long_query_time = 10

Key_buffer = 128 M

Max_allowed_packet = 1 M

Table_cache = 512

Sort_buffer_size = 1 M

Read_buffer_size = 1 M

Myisam_sort_buffer_size = 32 M

Thread_cache = 32

Query_cache_size = 16 M

Thread_concurrency = 2

Max_connections = 300

Server-id = 2

Master-host = 192.168.0.100

Master-user = 'repl'

Master-password = '000000'

Master-port = 3309

Report-host = net-app1

Master-connect-retry = 30

Log-bin

Log-slave-updates

[Mysqld3]

Port = 3308

Socket =/tmp/mysql. sock4

Pid-file =/usr/local/mysql/data4/net-app1d.pid

Datadir =/usr/local/mysql/data4

Log =/usr/local/mysql/data4/net-app1.log

User = mysql

Log-slow-queries =/usr/local/mysql/data4/slowquery. log

Long_query_time = 10

Key_buffer = 128 M

Max_allowed_packet = 1 M

Table_cache = 512

Sort_buffer_size = 1 M

Read_buffer_size = 1 M

Myisam_sort_buffer_size = 32 M

Thread_cache = 32

Query_cache_size = 16 M

Thread_concurrency = 2

Max_connections = 300

Server-id = 2

Master-host = 192.168.0.100

Master-user = 'repl'

Master-password = '000000'

Master-port = 3309

Report-host = net-app1

Master-connect-retry = 30

Log-bin

Log-slave-updates

[Mysqldump]

Quick

Max_allowed_packet = 16 M

[Mysql]

No-auto-rehash

# Remove the next comment character if you are not familiar with SQL

# Safe-updates

[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

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

After successful, the mysql service on the two hosts is started separately. In this way, each mysql of each master service changes and is automatically copied/updated to the database corresponding to the slave server.

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

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

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

5. Future testing,

Next, I want to create a multi-master and One-slave Mysql Server replication solution! You can give me some comments! The structure is as follows.

Assume that the master Server A, Server B, and slave Server C, A, and B run different database applications. Server C (assuming that only one mysql service is run on the three PCs), including all Mysql users of Server B and the same access permissions, and integrating them into A Mysql service. c. Copy databases A and B in Master/Slave Mode.

The difference is that the mysql of the two master servers is merged into one slave server.