**************************************************** ***********
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】
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!
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
