Teach you how to run multiple MySQL services on one machine

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

Part one, building multiple mysql services on one server.

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


一, Introduction

There is a mysqld_multi command in Mysql, which can be used to run multiple Mysql services on a physical server. Today I referred to some documents, tested it myself and passed it. I am really happy. Now I will share the operation process with you. Everyone!

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 also Almost, try again in a few days)

Planning: Run 4 mysql services:

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

Assume the server name: db-app2 (IP is 192.168.0.101),

The following until the second part is about mysql in db-app,


Second, preparation

Installation using mysql source program, suppose the configura option is used during installation?


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

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

--datadir will generate the database to /usr/local /mysql/data1

sysconfdir specifies the search path for the my.cnf configuration file used by mysql as /etc

Other mysql installation procedures are omitted.


According to It is mentioned in the Mysql Management Manual: Each Mysql service can be independent, so it all calls a different startup option in my.cnf - which is the GNR value mentioned below, uses different ports, and generates The respective socket files and service databases are independent (for more information, please refer to the English management manual of the mysql official website).


mysqld_multi is a service process that manages multiple mysqlds. These service process programs Different unix sockets may listen on different ports. It can start, stop and monitor the current service status.


----The program searches for the [mysqld#] section in my.cnf (or the configuration file customized in --config-file), "#" can be any positive integer . This positive integer is the segment sequence mentioned below, that is, GNR. The serial number of the segment is used as a parameter of mysqld_multi to distinguish different segments, so that you can control the start and stop of a specific mysqld process or obtain its report information. The parameters in these groups are just like the parameters of the groups required to start a mysqld. However, if you use 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, we can see that the most important of the multiple Mysql services 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 one account to start all mysql servers, because the same account is used. What is this account? It must be an account used by each mysql service, preferably a management account. The password below is the same as)

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

There are mysqld1, mysqld2, mysqld3, mysqld4 in my configuration file. That is to say, I will start 4 mysql services on different ports of the same server - 3306-3309. The database file path specified by each datadir is different, and each has its own different log file. Some other settings can use the original content of my.cnf.


ok, the important my.cnf is compiled, now create separate folders and initial databases for different mysql services in the configuration.

[mysqld1] is a default one, it was already there when we installed mysql, so don’t worry about it.


[mysqld2], just create a directory for it according to the configured path. That’s it. The directory will be changed to mysql management rights


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


Build a database, we can Copy the default mysql database to use other mysql accounts, and create other databases according to 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] , the same.


We may see if these directories exist.

db-app:/ # ls -l /usr/local/mysql/


drwxr-xr-x 6 mysql mysql 4096 Apr 9 17:54 data4

drwxr-x--- 2 mysql mysql 4096 Apr 9 17:14 data1

drwxr-xr- x 3 mysql mysql 4096 Apr 9 17:54 data2

drwxr-xr-x 3 mysql mysql 4096 Apr 9 17:54 data3


It can now be started through mysqld_multi.


Three, 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 you specified during installation)

db-app:/ # mysqld_multi [options] { start|stop|report} [GNR[,GNR]...]


start, stop and report refer to the operations you want to perform. You can specify an operation on a single service or on multiple services, as distinguished from the GNR list following the options. If no GNR list is specified, mysqld_multi will operate according to the option file in all services.


The value of each GNR is the sequence number of the group or the sequence 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, then the value of this item is 17. If you specify a range, use "-" (dash) to connect the two numbers. If the value of GNR is 10-13, it refers to group mysqld10 to group mysqld13. Multiple groups or group ranges can be specified on the command line, separated by "," (comma). There cannot be whitespace characters (such as spaces or tabs). Parameters following whitespace characters will be ignored. (Note: The GNR value is the value in mysqld# we defined 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, and the related files are set by mysql1 in my.cnf.


db-app: / # /usr/local/mysq/bin/mysqld_multi --config-file=/etc/my.cnf stop 1 Start and stop the first mysql service


db-app:/ # /usr /local/mysq/bin/mysqld_multi --config-file=/etc/my.cnf start 1-4, start the 1st to 4th mysql service, which is actually all I have 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 18:59 0:00 /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 18:59 0:00 /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 18:59 0:00 /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 18:59 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --port=3309 --socket=/tmp/mysql.sock4

.................................................


4. Client access

Any client access requires a specified access port to enter the specified database service. Otherwise, the MYSQL served by the Mysql default port (3306) will be used.

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

The second part, build multiple mysql main services on one server.

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


Tip: Create an account specifically for the following master-slave replication. The account repl used in this example has the password: '1234567890', which allows it to have select_priv, reload_priv, process_priv, grant_priv, super_priv, repl_slave_priv, repl_client_priv permissions, and can be accessed through any client, that is, the access client is '%'.

Mainly modify the content in my.cnf, so that each mysql can generate its own bin-log file and its own operating environment. Now paste all the contents of my my.cnf. Please refer to mysql for related parameters. 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 = 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 = 256M

max_allowed_packet = 1M

table_cache = 512

sort_buffer_size = 2M

read_buffer_size = 2M

myisam_sort_buffer_size = 64M

thread_cache = 32

query_cache_size = 32M

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 = 128M

max_allowed_packet = 1M

table_cache = 512

sort_buffer_size = 1M

read_buffer_size = 1M

myisam_sort_buffer_size = 32M

thread_cache = 32

query_cache_size = 16M

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 = 16M


[mysql]

no-auto-rehash

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

#safe-updates


[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


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


Starting multiple mysql services are the same,

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

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

the third part , Build multiple mysql slave services on one server.

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

Pre-selection preparation for building the slave server: It is recommended to use mysqld_multi to stop all mysql on the master server. Delete all files in the data directory except the database directory (there are 4 data directories in this article, datadir = data1 -- data1 in /usr/local/mysql/). Both the master and slaves use the same data directory path.

Use the Tar command to encapsulate each database, and use 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


Operation on the 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, please confirm whether the system account mysql has operating permissions on the mysql data directory in the master/slave server. If you cannot confirm, you can directly change the ownership of these directories. Can.

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


Operations on the 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 From the entire contents of /etc/my.cnf on the server.

Tip: There will be an account mentioned in my.cnf below: repl, the password is: '1234567890', this account is specially created above .

In fact, they are all the same. The main thing is to modify the content in my.cnf, so that each slave mysql can obtain its own bin-log through different ports of the main mysql to update the self-generated database content. Now paste all the contents of my my.cnf (from the server). Please refer to the mysql official manual for related parameters.


#[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 = 256M

max_allowed_packet = 1M

table_cache = 512 

sort_buffer_size = 2M 

read_buffer_size = 2M 

myisam_sort_buffer_size = 64M 

thread_cache = 32 

query_cache_size = 32M 

thread_concurrency = 2 

max_connections=500 

server-id = 2 

master-host = 192.168.0.100 

master-user = 'repl' 

master-password = '1234567890' 

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 = 128M 

max_allowed_packet = 1M 

table_cache = 512 

sort_buffer_size = 1M 

read_buffer_size = 1M 

myisam_sort_buffer_size = 32M 

thread_cache = 32 

query_cache_size = 16M 

thread_concurrency = 2 

max_connections=300 

server-id = 2 

master-host = 192.168.0.100 

master-user = 'repl' 

master-password = '1234567890' 

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 = 128M 

max_allowed_packet = 1M 

table_cache = 512 

sort_buffer_size = 1M 

read_buffer_size = 1M 

myisam_sort_buffer_size = 32M 

thread_cache = 32 

query_cache_size = 16M 

thread_concurrency = 2 

max_connections=300 

server-id = 2 

master-host = 192.168.0.100 

master-user = 'repl' 

master-password = '1234567890' 

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 = 128M 

max_allowed_packet = 1M 

table_cache = 512 

sort_buffer_size = 1M 

read_buffer_size = 1M 

myisam_sort_buffer_size = 32M 

thread_cache = 32 

query_cache_size = 16M 

thread_concurrency = 2 

max_connections=300 

server-id = 2 

master-host = 192.168.0.100 

master-user = 'repl' 

master-password = '1234567890' 

master-port = 3309 

report-host = net-app1 

master-connect-retry = 30 

log-bin 

log-slave-updates 



[mysqldump] 

quick 

max_allowed_packet = 16M 


[mysql] 

no-auto-rehash

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

#safe-updates


[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


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

Now that you are done, start the multiple mysql services on the two hosts respectively. In this way, every change in mysql of each main service will be automatically copied/updated to the corresponding database of 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 make a Mysql server replication solution with multiple masters and one slave! Can anyone give me some advice? Opinions! The structure is as follows.

There are master servers Server A, Server B and slave servers Server C. A and B are running different database applications. Assume that the database names are different. Server C (assuming that only one mysql service is running on these three PCs) includes all Mysql users of A and B servers and the same access rights, and is integrated into one Mysql service. C copies A through the master/slave method and B's database.


It’s almost like merging the mysql of two master servers into one slave server.

【Related recommendations】

1. Mysql free video tutorial

2. Teach you how to start and stop the Mysql service 2

3. Teach you how to start and stop one of the Mysql services

4. Example tutorial for processing special sql statements in mysql

5. Detailed explanation of how to write sql statements to delete tables in different databases

The above is the detailed content of Teach you how to run multiple MySQL services on one machine. For more information, please follow other related articles on the PHP Chinese website!