Multiple MySQL instances

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. Generally, it is not recommended to run multiple MySQL instances on a server, especially the production environment.

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. Generally, it is not recommended to run multiple MySQL instances on a server, especially the production environment.

MySQL builds multiple series of instances

The so-called multi-instance is to build and run multiple MySQL instances on a 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.