Multiple Mysql instances on one machine in centos

Multiple Mysql instances on one machine in centos
One machine runs multiple mysql services (using mysqld_multi)

| Report | font size subscription

*****************
* ** Preparations ***
*****************
According to the Mysql Management Manual, each Mysql service can be independent, so it calls. the different startup options in cnf are the GNR values mentioned below. Different ports are used to generate their own socket files, and the Service database is independent.
The required preparations are as follows: (they are all ready-made)
1. You only need to install a set of mysql server software.
2. mysqld_multi
3. You need to modify/etc/my. cnf.

Explanation:
1./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 indicates that the search path of the my. cnf configuration file used by mysql is/etc.
Other mysql installation procedures are omitted.

2. mysqld_multi is a service process that manages multiple mysqld. These service processes use different unix sockets or listen on different ports. It can start, stop, and monitor the current service status.

3. The program searches for [mysqld #] segments in my. cnf (or the custom configuration file in-config-file), and "#" 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 multiple services are used, you must specify a unix socket or port for each service.

Paste my/etc/my. cnf file below
(Only the sections that involve multiple mysql services are posted)

[Mysqld_multi] ### newly added
Mysqld =/home/usr/local/bin/mysqld_safe ### newly added
Mysqladmin =/home/usr/local/mysql/bin/mysqladmin ### newly added
User = root ### newly added
Password = root ### newly added

[Mysqld1] ### change mysqld to mysqld1 and add a GNR Value
Port = 3306
Socket =/tmp/mysql. sock
Skip-locking
Key_buffer = 16 M
Max_allowed_packet = 1 M
Table_cache = 64
Sort_buffer_size = 512 K
Net_buffer_length = 8 K
Read_buffer_size = 256 K
Read_rnd_buffer_size = 512 K
Myisam_sort_buffer_size = 8 M
Innodb_file_per_table
Default-character-set = utf8
Log = general-log
Log-slow-queries = slow-log
Log-bin = mysql-bin
Server-id = 1
Innodb_log_group_home_dir =/home/anmh/mysql_test/arch_mysql/
Innodb_log_arch_dir =/home/anmh/mysql_test/arch_mysql/
Innodb_lock_wait_timeout = 5

[Mysqld2] ### newly added, with a GNR value of 2
Port = 3307 ### change to a new port
Socket =/tmp/mysql2.sock ### change to a new socket
Datadir =/home/anmh/mysql1/datadir/### change to a new directory
Key_buffer = 16 M
Max_allowed_packet = 1 M
Table_cache = 64
Sort_buffer_size = 512 K
Net_buffer_length = 8 K
Read_buffer_size = 256 K
Read_rnd_buffer_size = 512 K
Myisam_sort_buffer_size = 8 M
Innodb_file_per_table
Default-character-set = utf8
Log =/home/anmh/mysql1/logdir/general-log ### change to a new directory
Log-slow-queries =/home/anmh/mysql1/logdir/slow-log ### change to a new directory
Log-bin =/home/anmh/mysql1/logdir/bin-log ### change to a new directory
Innodb_log_group_home_dir =/home/anmh/mysql1/datadir/idb ### change it to a new directory
Innodb_log_arch_dir =/home/anmh/mysql1/datadir/idb/archdir ### change it to a new directory
Innodb_lock_wait_timeout = 5 ### change it to a new directory.

Here, my configuration file contains two services, mysql1 and mysqld2, respectively using 3306 and 3307, and each datadir path is also different. Some other settings are available in my. cnf's original content.

**************************
* ** Create directories and necessary libraries ***
**************************
The directory is the path in my. cnf above.
Mkdir/home/anmh/mysql1
Mkdir/home/anmh/mysql1/logdir/home/anmh/mysql1/datadir
Mkdir/home/anmh/mysql1/datadir/idb
Mkdir/home/anmh/mysql1/datadir/idb/archdir

The necessary database refers to the mysql database (the reason data permission)
Copy in mysql1 directly here
# Cp-Rf/usr/local/var/mysql/home/anmh/mysql1/datadir
# Chmod-R 777/home/anmh/mysql1/datadir

*************************
* ** Mysqld_multi command ***
*************************
Usage: # 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 a hyphen (-) 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 ).

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

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

#/Usr/local/mysq/bin/mysqld_multi-config-file =/etc/my. cnf start 1-4. start the 1st-4mysql service, which is actually all about me here.

#/Usr/local/mysq/bin/mysqld_multi-config-file =/etc/my. cnf report 1-4

View startup:

# 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

*****************
* ** Problems ***
*****************
I. 2nd servers cannot be started;
Solution:
View err logs (in the datadir directory by default)
The error message is:
100919 11:02:07 [ERROR] cocould not use/home/anmh/mysql1/general-log for logging (error 13 ). turning logging off for the whole duration of the MySQL server process. to turn it on again: fix the cause, shutdown the MySQL server and restart it.
/Usr/local/libexec/mysqld: File '/home/anmh/mysql1/slow-log' not found (Errcode: 13)
100919 11:02:07 [ERROR] cocould not use/home/anmh/mysql1/slow-log for logging (error 13 ). turning logging off for the whole duration of the MySQL server process. to turn it on again: fix the cause, shutdown the MySQL server and restart it.
/Usr/local/libexec/mysqld: File '/home/anmh/mysql1/bin-log.index' not found (Errcode: 13)
100919 11:02:07 [ERROR] Aborting
100919 11:02:07 [Note]/usr/local/libexec/mysqld: Shutdown complete
View the specific error description:
[Root @ dbadb1 bin] #/usr/local/bin/perror 13
OS error code 13: Permission denied

The reason is:
1. Set the log directory error: the original directory is/home/anmh/mysql1/logdir /. However, it is set to/home/anmh/mysql1/
Modify the directory settings of log * in/etc/my. cnf
Log =/home/anmh/mysql1/logdir/general-log
Log-slow-queries =/home/anmh/mysql1/logdir/slow-log
Log-bin =/home/anmh/mysql1/logdir/bin-log

2. The directory of the file has no permission:
[Root @ dbadb1 mysql1] # ll
Drwxr-xr-x 3 root 4096 Sep 19 :03 datadir
Drwxr-xr-x 2 root 4096 Sep 19 :03 logdir
[Root @ dbadb1 mysql1] # chmod-Rf 777 logdir datadir
[Root @ dbadb1 mysql1] # ll
Drwxr-xr-x 3 root 4096 Sep 19 11: 03 DATAD
Drwxr-xr-x 2 root 4096 Sep 19 :03 logdir

2. How to connect to the specified mysql Server
1. Use socket:
When the mysql server is started, each mysql server socket has been established.
Use-S to specify socket Login
[Root @ dbadb1 bin] # mysql-uroot-p-S/tmp/mysql2.sock ### here we want to log on to 2nd mysql
Enter password:
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 10
Server version: 5.0.40-log Source distribution
Type 'help; 'or' \ H' for help. Type '\ C' to clear the buffer.
Mysql>

2. Use tcp
You need to specify the ip address or host, and add the port
[Root @ dbadb1 bin] # mysql-uroot-p-h10.254.3.44-P3307 ### here is the new port for logging on to mysql 2nd: 3307. If this parameter is not added, the default value is 3306.
Enter password:
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 11
Server version: 5.0.40-log Source distribution
Type 'help; 'or' \ H' for help. Type '\ C' to clear the buffer.
Mysql>

All introductions are complete. If you have any questions, please leave a message.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~

How to compile and install Mysql in the source code and support multiple Mysql instances on one machine

1. Install the Mysql5.1 server for a single instance
Shell> groupadd mysql
Shell> useradd-g mysql
Shell> gunzip | mysql-VERSION.tar.gz | tar-xvf-
Shell> cd mysql-VERSION
Shell>. /configure -- prefix =/usr/local/mysql -- with-charset = utf8 -- without-debug -- with-client-ldflags =-all-static -- with-mysqld-ldflags =- all-static -- disable-shared -- with-mysqld-user = mysql -- with-extra-charsets = gb2312, big5, gbk
Shell> make
Shell> make install
Shells> cp support-files/my-medium.cnf/etc/my. cnf

Cp support-files/mysql. server/etc/init. d/mysqld
Chmod 755/etc/init. d/mysqld
Chkconfig -- add mysqld
Chkconfig -- level 2345 mysqld on
PATH =/usr/local/mysql/bin: $ PATH
Echo "PATH =/usr/local/mysql/bin: \ $ PATH">/etc/profile

Shell> cd/usr/local/mysql
Shell> bin/mysql_install_db -- user = mysql
Shell> chown-R root.
Shell> chown-R mysql var
Shell> chgrp-R mysql.
Shell> bin/mysqld_safe -- user = mysql &


Ii. Install the Mysql5.1 server on multiple instances


1. Install the Mysql server listening on port 3306
Shell> groupadd mysql
Shell> useradd-g mysql
Shell> gunzip | mysql-VERSION.tar.gz | tar-xvf-
Shell> cd mysql-VERSION
Shell>. /configure -- prefix =/usr/local/mysql3306 -- with-unix-socket-path =/usr/local/mysql3306/var/mysql. sock -- sysconfdir =/usr/local/mysql3306/etc -- with-charset = utf8 -- without-debug -- with-client-ldflags =-all-static -- with-mysqld-ldflags = -all-static -- disable-shared -- with-mysqld-user = mysql -- with-extra-charsets = gb2312, big5, gbk
Shell> make
Shell> make install
Shell> mkdir/usr/local/mysql3306/etc
Shell> cp support-files/my-medium.cnf/usr/local/mysql3306/etc/my. cnf

Cp support-files/mysql. server/etc/init. d/mysqld3306
Chmod 755/etc/init. d/mysqld3306
Chkconfig -- add mysqld3306
Chkconfig -- level 2345 mysqld3306 on
PATH =/usr/local/mysql3306/bin: $ PATH
Echo "PATH =/usr/local/mysql3306/bin: \ $ PATH">/etc/profile

Shell> cd/usr/local/mysql3306
Shell> bin/mysql_install_db -- user = mysql
Shell> chown-R root.
Shell> chown-R mysql var
Shell> chgrp-R mysql.
Shell> bin/mysqld_safe -- user = mysql &

Modify my. cnf to set the listening port to 3306.

Ii. Install the Mysql server listening port 3307
Shell> groupadd mysql
Shell> useradd-g mysql
Shell> gunzip | mysql-VERSION.tar.gz | tar-xvf-
Shell> cd mysql-VERSION
Shell>. /configure -- prefix =/usr/local/mysql3307 -- with-unix-socket-path =/usr/local/mysql3307/var/mysql. sock -- sysconfdir =/usr/local/mysql3307/etc -- with-charset = utf8 -- without-debug -- with-client-ldflags =-all-static -- with-mysqld-ldflags = -all-static -- disable-shared -- with-mysqld-user = mysql -- with-extra-charsets = gb2312, big5, gbk
Shell> make
Shell> make install
Shell> mkdir/usr/local/mysql3307/etc
Shell> cp support-files/my-medium.cnf/usr/local/mysql3307/etc/my. cnf

Cp support-files/mysql. server/etc/init. d/mysql3307
Chmod 755/etc/init. d/mysql3307
Chkconfig -- add mysql3307
Chkconfig -- level 2345 mysql3307 on
PATH =/usr/local/mysql3306/bin: $ PATH
Echo "PATH =/usr/local/mysql3306/bin: \ $ PATH">/etc/profile

Shell> cd/usr/local/mysql3307
Shell> bin/mysql_install_db -- user = mysql
Shell> chown-R root.
Shell> chown-R mysql var
Shell> chgrp-R mysql.
Shell> bin/mysqld_safe -- user = mysql &

Modify my. cnf to set the listening port to 3307.


Iii. Compile Parameter Parsing
Compile Parameter Parsing
-- Prefix =/usr/local/mysql3306 specify the installation path
-- With-unix-socket-path =/usr/local/mysql3306/var/mysql. sock specifies the socket file of the socket. It must be specified for multiple instances. Otherwise,/tmp/mysql is used by default for multiple instances. sock
-- Sysconfdir =/usr/local/mysql3306/etc mysql configuration file after the instance is started, [PREFIX/etc] In the installation path by default. The purpose is to emphasize the need to configure my for each instance. cnf and/etc/my. cnf does not exist because the startup script usually reads/etc/my first. cnf
-- With-charset = utf8 default Character Set
-- With-extra-charsets = gb2312, big5, and gbk support other character sets to reduce unnecessary overhead. Mysql supports more than 20 character sets by default.
-- Without-debug removes the debugging mode, which is said to be optimized.
-- Enable-aggreger uses the assembly version of some character functions, which is said to optimize the performance.
-- With-client-ldflags =-all-static compile the client in pure static mode. If the client is not required on the server instance, do not use this option. However, we recommend that you keep it and log on to the database server, mysql is a default habit.
-- With-mysqld-ldflags =-all-static compile the server in pure static mode. It is said to be useful for performance optimization.
-- Disable-shared does not provide dynamic link libraries
-- With-mysqld-user = mysql startup user set to mysql

Run multiple MySQL servers on the same machine

Mysql mysqld_multi command, which can be used to run multiple Mysql services on one physical server

I. Preparations

1. Install MySQL source code

[Root @ localhost ~] # Tar zxvf mysql-5.0.80.tar.gz
[Root @ localhost ~] # Cd mysql-5.0.80
[Root @ localhost mysql-5.0.80] #./configure -- prefix =/usr/local/mysql
[Root @ localhost mysql-5.0.80] # make
[Root @ localhost mysql-5.0.80] # make install

2. Create an account

[Root @ localhost mysql-5.0.80] # adduser mysql
[Root @ localhost mysql-5.0.80] # chown-R mysql: root/usr/local/mysql

3. initialize the authorization table

[Root @ localhost mysql] # su mysql
[Root @ localhost mysql] # cd/usr/local/mysql
[Root @ localhost mysql] #./bin/mysql_install_db

Ii. Multiple MySQL services

Create my. cnf

[Root @ localhost mysql] # touch/etc/my. cnf
[Root @ localhost mysql] # vi/etc/my. cnf

The content of my. cnf is as follows:

[Mysqld_multi]
Mysqld =/usr/local/mysql/bin/mysqld_safe
Mysqladmin =/usr/local/mysql/bin/mysqladmin
User = mysql
Password = d3.zone

[Mysqld1]
Port = 3306
Socket =/tmp/mysql. sock1
Pid-file =/usr/local/mysql/var1/db-app1.pid
Log =/usr/local/mysql/var1/db-app.log
Datadir =/usr/local/mysql/var1
User = mysql

[Mysqld2]
Port = 3307
Socket =/tmp/mysql. sock2
Pid-file =/usr/local/mysql/var2/db-app2.pid
Datadir =/usr/local/mysql/var2
Log =/usr/local/var2/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

Create different folders and initial databases for different mysql services in the configuration.

[Root @ localhost mysql] # cd/usr/local/mysql
[Root @ localhost mysql] # mv var./var1
[Root @ localhost mysql] # cp var1./var2-R

Now, you can use the mysqld_multi parameter to start mysqld_multi

[Root @ localhost mysql] #/usr/local/mysql/bin/mysqld_multi -- config-file =/etc/my. cnf start 1-2

Use the report parameter to view the running status

So far, our two mysql services have been started. You can add more mysql services as needed, so I will not be here.

When the client accesses the mysql service started through mysqld_multi, you must specify the corresponding mysql service port

On the local server, you can also specify the mysql service through the mysql socket.

[Root @ localhost mysql] # mysql -- socket =/tmp/mysql. sock1-uroot-p