MySQL builds a series of multiple instances. The so-called multi-instance is to build and run multiple MySQL instances on one server. each instance uses different service ports and listens through different sockets. physically, each instance has an independent parameter configuration file and database.
In general, it is not recommended to run multiple MySQL instances on a server, especially in the production environment, because this will cause a high proportion of resources. However, in some high-availability environments, there is such a need. for example, in a Failover Cluster Environment built using Heartbeat, if the master and slave servers only run one MySQL instance, the standby server is usually idle, causing waste of resources. if one MySQL instance runs separately, the other party is required to take over the task when one party fails, that is, two MySQL instances are run simultaneously. Based on this requirement, the MySQL server should be reasonably configured so that it can run multiple instances at the same time.
The key to how to build and run multiple MySQL instances on a server is how to assign independent parameter files to each instance. below are several common multi-instance solutions, learn and familiarize yourself with each other.
System environment
OS: CentOS 5.8 (x86_64) kernel: 2.6.18-308. el5 DB: MySQL 5.5.17
I. use the source code package to install MySQL for multiple instances
The source package is the most flexible installation package. you can customize some path parameters and install them in any path. Therefore, you can build multiple MySQL instances on one server. In this example, install two MySQL instances as follows:
1. preparations
Starting from MySQL 5.5, use the cmake tool to compile the source code package. Therefore, you must install it first. For more information, see other documents. In addition, the ncurses-devel and bison dependency packages must be installed before compilation. Otherwise, the compilation will fail.
-Install the dependency package
# Rpm-ivh ncurses-devel-5.5-24.20060715.x86_64.rpm
# Rpm-ivh bison-2.3-2.1.x86_64.rpm
-Create a mysql system Group and user
# Groupadd-g 497 mysql
# Useradd-u 499-g mysql
-- Unzip the installation package
# Tar-zxvf mysql-5.5.17.tar.gz
# Cd mysql-5.5.17
2. Compile and install
LMySQL instance 1:
(Port: 3306, installation directory:/usr/local/mysqla, data file directory:/data/lib/mysqla)
-Custom compilation
# Cmake-DCMAKE_INSTALL_PREFIX =/usr/local/mysqla-DMYSQL_TCP_PORT = 3306-DMYSQL_DATADIR =/data/lib/mysqla-DMYSQL_UNIX_ADDR =/data/lib/mysqla/mysql. sock-DSYSCONFDIR =/usr/local/mysqla-DWITH_MYISAM_STORAGE_ENGINE = 1-rows = bytes = 1-rows = 1-rows = 1-DWITH_READLINE = 1-DWITH_SSL = yes-DDEFAULT_CHARSET = utf8-DDEFAULT_COLLATION = utf8_general_ci-DEXTRA_CHARSETS = all
# Make
# Make install
-Create a system database and a system table
# Cd/usr/local/mysqla/
# Scripts/mysql_install_db -- user = mysql -- basedir =/usr/local/mysqla -- datadir =/data/lib/mysqla
LMySQL instance 2:
(Port: 3307, installation directory:/usr/local/mysqlb, data file directory:/data/lib/mysqlb)
-- Delete old cache and object files
Before re-compilation, clear the original cache information and object files as follows:
# Cd mysql-5.5.17
# Make clean
# Rm-rf CMakeCache.txt
-Custom compilation
# Cmake-DCMAKE_INSTALL_PREFIX =/usr/local/mysqlb-DMYSQL_TCP_PORT = 3307-DMYSQL_DATADIR =/data/lib/mysqlb-DMYSQL_UNIX_ADDR =/data/lib/mysqlb/mysql. sock-DSYSCONFDIR =/usr/local/mysqlb-DWITH_MYISAM_STORAGE_ENGINE = 1-rows = bytes = 1-rows = 1-rows = 1-DWITH_READLINE = 1-DWITH_SSL = yes-DDEFAULT_CHARSET = utf8-DDEFAULT_COLLATION = utf8_general_ci-DEXTRA_CHARSETS = all
# Make
# Make install
-Create a system database and a system table
# Cd/usr/local/mysqlb/
# Scripts/mysql_install_db -- user = mysql -- datadir =/data/lib/mysqlb
3. configuration parameter file
After the above two compilation and creation of the System database, two MySQL instances are installed and constructed, and the parameter files are configured respectively.
-View the default location of the parameter file
#/Usr/local/mysqla/bin/mysql -- help | grep '/my. cnf'
/Etc/my. cnf/etc/mysql/my. cnf/usr/local/mysqla/my. cnf ~ /. My. cnf
#/Usr/local/mysqlb/bin/mysql -- help | grep '/my. cnf'
/Etc/my. cnf/etc/mysql/my. cnf/usr/local/mysqlb/my. cnf ~ /. My. cnf
It can be seen that three of the four default parameter files of two MySQL instances are the same, so they cannot be used. Otherwise, they cannot be distinguished. of course, the same parameter section of multiple instances can be configured in this way, but it is not easy to manage.
To make each MySQL instance have an independent parameter file, only 3rd default parameter files can be used to store the parameter files in the basedir directory of each instance ). In addition, make sure that there are no 4th parameter files because they have a higher priority level. if they exist, they will overwrite the preceding parameter file settings.
The detailed configuration of the parameter file is not described here. assume that the parameter files have been configured for the two MySQL instances, and they are stored in their respective installation directories, that is, under the/usr/local/mysqla | mysqlb directory.
4. start the instance
To start a MySQL instance, you must set the correct permissions for the datadir Directory. Otherwise, the instance fails to be started. this step should have been set by default. check whether the permission is set. if not set, run the following command to set the permission.
# Chown-R mysql. mysql/
-- Start two MySQL instances
#/Usr/local/mysqla/bin/mysqld_safe -- user = mysql &
#/Usr/local/mysqlb/bin/mysqld_safe -- user = mysql &
-View processes
# Ps-ef | grep mysql
Root 1694 26876 0 00:00:00 pts/2 more/usr/local/mysqla/support-files/mysql. server
Root 2270 13474 0 00:00:00 pts/1/bin/sh/usr/local/mysqla/bin/mysqld_safe -- user = mysql
Mysql 2805 2270 00:00:00 pts/1/usr/local/mysqla/bin/mysqld -- basedir =/usr/local/mysqla -- datadir =/data/lib/mysqla -- plugin-dir =/usr/local/mysqla/lib/plugin -- user = mysql -- log-error =/data/lib/mysqla/mysql. err -- pid-file =/data/lib/mysqla/mysql. pid -- socket =/data/lib/mysqla/mysql. sock -- ports = 3306
Root 2828 13474 0 00:00:00 pts/1/bin/sh/usr/local/mysqlb/bin/mysqld_safe -- user = mysql
Mysql 3361 2828 25 00:00:00 pts/1/usr/local/mysqlb/bin/mysqld -- basedir =/usr/local/mysqlb -- datadir =/data/lib/mysqlb -- plugin- dir =/usr/local/mysqlb/lib/plugin -- user = mysql -- log-error =/data/lib/mysqlb/mysql. err -- pid-file =/data/lib/mysqlb/mysql. pid -- socket =/data/lib/mysqlb/mysql. sock -- ports = 3307
Root 3381 13474 0 00:00:00 pts/1 grep mysql
We can see that two MySQL instances (processes) are successfully started)
-To disable the service, run the following command:
#/Usr/local/mysqla/bin/mysqladmin shutdown
#/Usr/local/mysqlb/bin/mysqladmin shutdown
5. change to service management mode
To facilitate management, change it to the service management method as follows:
-Copy Service files
# Cp/usr/local/mysqla/support-files/mysql. server/etc/rc. d/init. d/mysqla
# Cp/usr/local/mysqlb/support-files/mysql. server/etc/rc. d/init. d/mysqlb
-Add a service
# Chkconfig -- add mysqla
# Chkconfig -- list mysqla
Mysqla 0: off 1: off 2: on 3: on 4: on 5: on 6: off
# Chkconfig -- add mysqlb
# Chkconfig -- list mysqlb
Mysqlb 0: off 1: off 2: on 3: on 4: on 5: on 6: off
-Start the service
# Service mysqla start
Starting MySQL... [OK]
[Root @ db ~] # Service mysqlb start
Starting MySQL... [OK]
At this point, we have successfully built two MySQL instances through the source code package, which have independent ports, parameter files, and databases.
II. install MySQL using a binary package to implement multiple instances
A binary package is actually a compiled source code package. you cannot isolate parameter files of each instance by customizing the default path of the parameter file. However, you must note that: the default path of the 3rd parameter files refers to basedir (installation directory), which can be used to isolate parameter files of each instance. Therefore, one server can also build multiple MySQL instances.
Install two MySQL instances as follows:
1. preparations
-Create a mysql system Group and user
# Groupadd-g 497 mysql
# Useradd-u 499-g mysql
-- Unzip the installation package
Decompress the binary installation package to the/usr/local/directory and change it to mysql. this is the default directory of the basedir parameter during binary package compilation.
# Tar zxvf mysql-5.5.17-linux2.6-x86_64.tar.gz-C/usr/local/
# Cd/usr/local/
# Music mysql-5.5.17-linux2.6-x86_64 mysql
-Copy the decompressed mysql and generate the following two folders to use as the installation directory for the two instances.
# Cd/usr/local/
# Cp-R mysql mysqla
# Cp-R mysql mysqlb
# Ll
Drwxr-xr-x 12 root 4096 Sep 27 14:33 mysql
Drwxr-xr-x 12 root 4096 Sep 27 14:36 mysqla
Drwxr-xr-x 12 root 4096 Sep 27 mysqlb
Note: the mysql directory is retained here to configure hard connections so that the mysql client program can be accessed in any directory.
2. create a system database
Create a system database for two MySQL instances. the basedir is/usr/loca/mysqla | mysqlb, and the datadir is/data/lib/mysqla | mysqlb, as follows:
# Cd/usr/local/mysql/
# Scripts/mysql_install_db -- user = mysql -- basedir =/usr/local/mysqla -- datadir =/data/lib/mysqla
# Scripts/mysql_install_db -- user = mysql -- basedir =/usr/local/mysqlb -- datadir =/data/lib/mysqlb
3. configuration parameter file
To make each MySQL instance have an independent parameter file, store it in the corresponding installation directory (basedir). ensure that the parameter file does not exist in the default path to prevent reading errors.
Assume that the parameter files are configured, and the ports are 3306 and 3307, and the basedir is/usr/local/mysqla and/usr/local/mysqlb respectively, datadir is/data/lib/mysqla and/data/lib/mysqlb respectively.
4. start the instance
In this case, you can specify a parameter file for the instance through -- defaults-file when starting the instance, as shown below:
#/Usr/local/mysql/bin/mysqld_safe -- defaults-file =/usr/local/mysqla/my. cnf &
#/Usr/local/mysql/bin/mysqld_safe -- defaults-file =/usr/local/mysqlb/my. cnf &
When you close an instance, you also need to specify its parameter file, otherwise it will fail.
#/Usr/local/mysql/bin/mysqladmin -- defaults-file =/usr/local/mysqla/my. cnf shutdown
#/Usr/local/mysql/bin/mysqladmin -- defaults-file =/usr/local/mysqlb/my. cnf shutdown
5. change to service management mode
The binary package does not use the default parameter file, so the modification to the service management method is a little complicated. you need to modify the basedir and datadir parameters in the service startup file as follows:
-Copy Service files
# Cp/usr/local/mysql/support-files/mysql. server/etc/rc. d/init. d/mysqla
# Cp/usr/local/mysql/support-files/mysql. server/etc/rc. d/init. d/mysqlb
-Add a service
# Chkconfig -- add mysqla
# Chkconfig -- add mysqlb
-Modify the service startup file
Edit the two service startup files and modify the parameters basedir and datadir as the appropriate path, as shown below:
# Vi/etc/rc. d/init. d/mysqla
Basedir =/usr/local/mysqla
Datadir =/data/lib/mysqla
# Vi/etc/rc. d/init. d/mysqlb
Basedir =/usr/local/mysqlb
Datadir =/data/lib/mysqlb
Note: you can explicitly set basedir so that each MySQL instance has an independent parameter file.
-Start the service
# Service mysqla start
Starting MySQL... [OK]
# Service mysqlb start
Starting MySQL... [OK]
# Service mysqlb status
MySQL running (30326) [OK]
# Service mysqla status
MySQL running (29755) [OK]
After being changed to the service management mode, it is convenient to start or close the service. However, you must specify the socket file when logging on to the database locally because the default/tmp/mysql. sock does not exist, for example:
# Mysql -- socket =/data/lib/mysqla/mysql. sock
# Mysql -- socket =/data/lib/mysqlb/mysql. sock
III. install MySQL using RPM packages for multiple instances
The file layout of the RPM Package is fixed and cannot be modified. Therefore, only one MySQL instance can be installed on one server. However, we know that MySQL instances are started by calling the mysqld_safe command, and the mysqld_safe command can explicitly specify a parameter file through the -- defaults-file parameter, therefore, you can install and run multiple MySQL instances on one server, but you only need to make some adjustments to the service startup file. Next, let's try this method.
1. install MySQL
-Install the server and client package
# Rpm-ivh MySQL-server-5.5.17-1.rhel5.x86_64.rpm
# Rpm-ivh MySQL-client-5.5.17-1.rhel5.x86_64.rpm
-Start the service
# Service mysql start
Starting MySQL... [OK]
-View the parameter values of basedir and datadir.
# Mysql
Mysql> show variables like 'basedir ';
+ --------------- + ------- +
| Variable_name | Value |
+ --------------- + ------- +
| Basedir |/usr |
+ --------------- + ------- +
1 row in set (0.00 sec)
Mysql> show variables like 'datadir ';
+ --------------- + ----------------- +
| Variable_name | Value |
+ --------------- + ----------------- +
| Datadir |/var/lib/mysql/|
+ --------------- + ----------------- +
1 row in set (0.01 sec)
It can be seen that basedir is/usr by default, and datadir is/var/lib/mysql by default.
2. create a system database
By default, the RPM package has created a system database, which is located in the/var/lib/mysql directory and copied to the/data/lib directory, name mysqla and mysqlb respectively to create the system databases of the two instances.
# Service mysql stop
Shutting down MySQL. [OK]
# Cp-r/var/lib/mysql // data/lib/mysqla
# Cp-r/var/lib/mysql // data/lib/mysqlb
Of course, you can also create it using mysql_install_db, which is located in the/usr/bin directory, as shown below:
#/Usr/bin/mysql_install_db -- user = mysql -- datadir =/data/lib/mysqla
#/Usr/bin/mysql_install_db -- user = mysql -- datadir =/data/lib/mysqlb
3. configuration parameter file
Configure two parameter files named mya respectively. cnf, myb. cnf, which is stored in the/etc/Directory. the port numbers are 3306 and 3307, and the datadir paths are/data/lib/mysqla and/data/lib/mysqlb.
4. start the instance
Like a binary package with multiple instances, the parameter file can only be specified through -- defaults-file when the instance is started or closed, as shown below:
#/Usr/bin/mysqld_safe -- defaults-file =/etc/mya. cnf &
#/Usr/bin/mysqld_safe -- defaults-file =/etc/myb. cnf &
#/Usr/bin/mysqladmin -- defaults-file =/etc/mya. cnf shutdown
#/Usr/bin/mysqladmin -- defaults-file =/etc/myb. cnf shutdown
5. modify the service management mode
For convenience, you need to change it to the service management mode and perform the following modifications.
-Create a service startup file
Create two corresponding service startup files, mysqla and mysqlb, based on the service startup file mysql.
# Cd/etc/rc. d/init. d/
# Cp mysql mysqla
# Cp mysql mysqlb
-Add a service
# Chkconfig -- add mysqla
# Chkconfig -- add mysqlb
-Delete the original service startup file and service (avoid false startup)
# Chkconfig -- del mysql
# Rm/etc/rc. d/init. d/mysql
-Modify the service startup file
Edit the service startup file and make the following adjustments:
# Vi/etc/rc. d/init. d/mysqla
L 1: add a variable and set it to a parameter file.
My_cnf =/etc/mya. cnf
L 2: modify the extra_args parameter value to allow the parse_server_arguments function to parse parameter values such as datadir
(Add part in red)
Extra_args = ""
If test-r "$ basedir/my. cnf"
Then
Extra_args = "-e $ basedir/my. cnf"
Else
If test-r "$ datadir/my. cnf"
Then
Extra_args = "-e $ datadir/my. cnf"
Else
Extra_args = "-e $ my_cnf"
Fi
Fi
L 3: Use -- defaults-file in the mysqld_safe command to explicitly specify a parameter file
$ Bindir/mysqld_safe -- defaults-file = $ my_cnf -- datadir = "$ datadir" -- pid-file = "$ mysqld_pid_file_path" $ other_args>/dev/null 2> & 1 & amp &
Note: Make sure that the -- defaults-file parameter is followed by mysqld_safe. no other parameter exists in the middle; otherwise, the startup fails.
L 4: modify lock_file_path. if it is not modified, it is still unclear about the impact, but it should be modified just in case.
Lock_file_path = "$ lockdir/mysqla"
L 5: find 'status' and replace pidof, the command for obtaining the mysqld process, with ps.
# Mysqld_pid = 'pidof $ libexecdir/mysqld'
Mysqld_pid = 'PS -- user = mysql-f | grep "$ libexecdir/mysqld -- defaults-file = $ my_cnf" | awk '{print $2 }''
Note: The pidof command is used to obtain the process number of $ libexecdir/mysqld to determine the status of the MySQL instance. because the path and name are fixed, the process corresponding to each instance cannot be distinguished, the Status is disordered. you can use the filter function of the ps command to achieve this requirement and find the corresponding process for each instance.
Adjust the service startup file for the second MySQL instance in the same way.
-Start the service
[Root @ db mysqla] # service mysqla start
Starting MySQL... [OK]
[Root @ db mysqla] # service mysqlb start
Starting MySQL... [OK]
So far, we have installed two MySQL instances on one server through the RPM package.
4. use mysqld_multi to implement multiple instances
Mysqld_multi is a service script for MySQL to manage multiple MySQL instances in a unified manner. it searches for [mysqld #] segment sequence (#) from MySQL parameter files, can be any positive integer), as its own parameter, to distinguish different segments, and then control the startup, stop, or obtain report information of a specific mysqld process (MySQL instance.
Next, let's take a look at how it manages multiple MySQL instances.
1. configuration parameter file
Mysqld_multi has nothing to do with the installation package type. Therefore, both the source code package, binary package, and RPM package contain this script. assume that MySQL has been successfully installed.
In view of the working mechanism of mysqld_multi, you must configure [mysqld_multi] items and multiple [mysqld #] segments in MySQL parameter files. each [mysqld] segment corresponds to one MySQL instance, use different ports and unix socket to listen to files.
In this example, two [mysqld #] sections are configured, as follows:
# Vi/etc/my. cnf
[Mysqld_multi]
Mysqld =/usr/bin/mysqld_safe
Mysqladmin =/usr/bin/mysqladmin
[Mysqld1]
Port = 3306
Servers-id = 3306
Datadir =/data/lib/mysqla
Socket =/data/lib/mysqla/mysql. sock
Pid-file =/data/lib/mysqla/mysql. pid
Character-set-server = utf8
Default_storage_engine = InnoDB
Log-bin =/data/lib/mysqla/mysql-bin
Binlog_format = row
Sync-binlog = 1
Slow-query-log = on
Slow-query-log-file =/data/lib/mysqla/mysql-slow.log
Log_error =/data/lib/mysqla/mysql. err
Max_connections = 2000
Back_log = 50
Skip-external-locking
Skip-name-resolve
# Skip-networking
Key_buffer_size = 256 M
Max_allowed_packet = 1 M
Table_open_cache = 2000
Sort_buffer_size = 1 M
Read_buffer_size = 1 M
Read_rnd_buffer_size = 4 M
Myisam_sort_buffer_size = 64 M
Thread_cache_size = 8
Query_cache_size = 16 M
Thread_concurrency = 8
Innodb_data_home_dir =/data/lib/mysqla
Innodb_data_file_path = ibdata1: 10 M: autoextend
Innodb_log_group_home_dir =/data/lib/mysqla
Innodb_buffer_pool_size = 256 M
Innodb_additional_mem_pool_size = 20 M
Innodb_log_file_size = 64 M
Innodb_log_buffer_size = 8 M
Innodb_flush_log_at_trx_commit = 1
Innodb_lock_wait_timeout = 50
[Mysql2]
Port = 3307
Servers-id = 3307
Datadir =/data/lib/mysqlb
Socket =/data/lib/mysqlb/mysql. sock
Pid-file =/data/lib/mysqlb/mysql. pid
Character-set-server = utf8
Default_storage_engine = InnoDB
Log-bin =/data/lib/mysqlb/mysql-bin
Binlog_format = row
Sync-binlog = 1
Slow-query-log = on
Slow-query-log-file =/data/lib/mysqlb/mysql-slow.log
Log_error =/data/lib/mysqlb/mysql. err
Max_connections = 2000
Back_log = 50
Skip-external-locking
Skip-name-resolve
# Skip-networking
Key_buffer_size = 256 M
Max_allowed_packet = 1 M
Table_open_cache = 2000
Sort_buffer_size = 1 M
Read_buffer_size = 1 M
Read_rnd_buffer_size = 4 M
Myisam_sort_buffer_size = 64 M
Thread_cache_size = 8
Query_cache_size = 16 M
Thread_concurrency = 8
Innodb_data_home_dir =/data/lib/mysqlb
Innodb_data_file_path = ibdata1: 10 M: autoextend
Innodb_log_group_home_dir =/data/lib/mysqlb
Innodb_buffer_pool_size = 256 M
Innodb_additional_mem_pool_size = 20 M
Innodb_log_file_size = 64 M
Innodb_log_buffer_size = 8 M
Innodb_flush_log_at_trx_commit = 1
Innodb_lock_wait_timeout = 50
[Mysqld_multi] specifies mysqld_safe and mysqladmin command tools. mysqld_multi manages MySQL instances through these two tools. The two [mysqld #] segments correspond to two MySQL instances, with ports 3306 and 3307 and datadir respectively/data/lib/mysqla and/data/lib/mysqlb.
2. use mysqld_multi
-Start and close a MySQL instance through mysqld_multi
#/Usr/bin/mysqld_multi start | stop 1
#/Usr/bin/mysqld_multi start | stop 2
Note: Parameters 1 and 2 indicate [mysqld1] and [mysqld2] in the parameter configuration file, which correspond to two MySQL instances. if the segment sequence to be operated is not entered here, all [mysqld] segments are enabled and disabled by default.
For convenience, you can change it to the service management method as follows:
-Copy Service files
Copy the mysqld_multi.server file to the/etc/rc. d/init. d/directory, and change the name of the simple vertex.
(The location of the mysqld_multi.server file varies depending on the installation method)
# Cp/usr/share/mysql/mysqld_multi.server/etc/rc. d/init. d/mysqld_m
-Add a service
# Chkconfig -- add mysqld_m
# Chkconfig -- list mysqld_m
Mysqld_m 0: off 1: off 2: on 3: on 4: on 5: on 6: off
-Modify service files
Modify the two parameters in the/etc/rc. d/init. d/mysqld_m file as follows:
# Vi/etc/rc. d/init. d/mysqld_m
Basedir =/usr/
Bindir =/usr/bin
Note: the default values of these two parameters are/usr/local/mysql and/urs/local/mysql/bin. the actual values of different installation packages may be different, modify the values of these two parameters according to your actual situation so that the startup script can locate the/usr/bin/mysqld_multi file and grant the operation permission to the file.
-Start and close an instance
After the modification, we can start the mysqld instance through mysqld_multi. Its syntax is as follows:
# Mysqld_m [options] [GNR [, GNR]...]
# Service mysqld_m start | stop 1
# Service mysqld_m start | stop 2
3. deficiencies
Mysqld_multi allows you to manage multiple MySQL instances in a unified manner. However, some shortcomings are also found during use. First, the options are too simple, including start, stop, and report, the status item does not exist, so the status of the service cannot be viewed. second, when the service is started or shut down, the start and stop operations are only performed without tracking the operation results, therefore, the operation cannot be successful.