MySQL Multi-instance deployment

There are two ways that MySQL can be implemented in multiple instances, both of which have pros and cons.
The first is to use multiple configuration files to launch different processes to implement multiple instances, the advantages of this method is simple, simple configuration, the disadvantage is not easy to manage.
The second is the use of the official Mysqld_multi with a separate configuration file to achieve multiple instances, this way to customize the configuration of each instance is not too, the advantage is easy to manage, centralized management

The first uses multiple profiles to start multiple

1. Install and compile the database

2. Initializing the database

/usr/local/mysql_3306/bin/mysql_install_db--basedir=/usr/local/mysql_3306--datadir=/data/dbdata_3306--user= Mysql
/usr/local/mysql_3307/bin/mysql_install_db--basedir=/usr/local/mysql_3307--datadir=/data/dbdata_3307--user= Mysql
/usr/local/mysql_3308/bin/mysql_install_db--basedir=/usr/local/mysql_3308--datadir=/data/dbdata_3308--user= Mysql

3. Create the respective configuration file (need to specify the respective data directory, port, socket file)

Vim/data/dbdata_3306/my.cnf

Vim/data/dbdata_3307/my.cnf

Vim/data/dbdata_3308/my.cnf

4. Start each instance with a configuration issue

/bin/sh/usr/local/mysql_3306/bin/mysqld_safe--DEFAULTS-FILE=/DATA/DBDATA_3306/MY.CNF &

/bin/sh/usr/local/mysql_3307/bin/mysqld_safe--DEFAULTS-FILE=/DATA/DBDATA_3307/MY.CNF &

/bin/sh/usr/local/mysql_3308/bin/mysqld_safe--DEFAULTS-FILE=/DATA/DBDATA_3308/MY.CNF &

To close different instances

A.kill-9 Process ID

B./bin/sh/usr/local/mysql_3306/bin/mysqladmin-u ${mysql_username}-p${mysql_password}-s/data/dbdata_${mysql_port }/mysql.sock shutdown

The second approach is configured with the official Mysql_multi

1. Install and initialize the database as in the first way

2. Edit the configuration file (three instances of the configuration file are put together)

Vi/etc/my.cnf

[Mysqld_multi]
Mysqld =/usr/local/mysql/bin/mysqld_safe
Mysqladmin =/usr/local/mysql/bin/mysqladmin
user = Admin
Password = password

[Mysqld1]//define Instance 3306

Socket/data/dbdata_3306/mysql.sock

Port = 3306

Pid-file =/data/dbdata_3306/3306.pid

DataDir =/data/dbdata_3306

..........

[Mysqld2]//define Instance 3307

Socket/data/dbdata_3307/mysql.sock

Port = 3307

Pid-file =/data/dbdata_3307/3307.pid

DataDir =/data/dbdata_3307

..........

[MYSQLD3]//define instance 3308

Socket/data/dbdata_3308/mysql.sock

Port = 3308

Pid-file =/data/dbdata_3308/3308.pid

DataDir =/data/dbdata_3308

..........

3. Start each instance

/usr/local/mysql/bin/mysqld_multi Start 1

/usr/local/mysql/bin/mysqld_multi Start 2

/usr/local/mysql/bin/mysqld_multi Start 3

Or

/usr/local/mysql/bin/mysqld_multi Start 1-3

This article from "Banging blog" blog, declined reprint!

MySQL Multi-instance deployment