**************************************** ******************
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.