MYSQL multi-instance configuration and usage tutorial

Original http://www.111cn.net/database/mysql/58651.htm

In actual development, you may need to deploy multiple MYSQL instances on one server. We recommend that you use the MYSQL official solution mysqld_multi.

1. modify my. cnf

For example, a reference configuration that defines two instances:

The Code is as follows:

[Mysqld_multi] Mysqld =/usr/local/mysql/bin/mysqld_safe Mysqladmin =/usr/local/mysql/bin/mysqladmin User = your_user Password = your_password [Mysqld1] Datadir =/data/db/my1 # Connection Port = 3306 Socket =/tmp/mysql3306.sock # Binlog Log-bin =/data/db/mylog1/mysql-bin Binlog_format = mixed Binlog_cache_size = 32 M Expire_logs_days = 30 [Mysqld2] Datadir =/data/db/my2 # Connection Port = 3307 Socket =/tmp/mysql3307.sock # Binlog Log-bin =/data/db/mylog2/mysql-bin Binlog_format = mixed Binlog_cache_size = 32 M Expire_logs_days = 3

 
2. Create a data directory

Mkdir-p/data/db/my2Chown mysql. mysql/data/db/my1-RChown mysql. mysql/data/db/my2-R

The Code is as follows:

3. initialize DB

The Code is as follows:
/Usr/local/mysql/scripts/mysql_install_db -- datadir =/data/db/my1/-uroot (mysql_install_db is also an official MYSQL tool) /Usr/local/mysql/scripts/mysql_install_db -- datadir =/data/db/my2/-uroot Chown mysql. mysql/data/db/my1/-R Chown mysql. mysql/data/db/my2/-R

4. Install the tool

The Code is as follows: www.111cn.net
Cp/usr/local/mysql/bin/my_print_defaults/usr/bin/ Cp/usr/local/mysql/bin/mysqld_multi/usr/bin/

5. Create and authorize users

The Code is as follows:
Create user "your_user" @ "192.168.1. %" identified by 'your _ password '; Grant all privileges on *. * TO "your_user" @ "192.168.1.% "; Flush privileges;

So far, mysql multi-instance configuration has been completed. We can see that multiple MYSQL instances share my. cnf. Multi-instance command line management:
1. Start mysql

The Code is as follows:
Mysqld_multi start 1 start instance 1 Mysqld_multi start 1-2 start instance 1, 2

2. mysql restart

The Code is as follows:
Mysqld_multi restart 1 restart instance 1 Mysqld_multi restart 1-2 restart instance 1, 2

3. Close mysql

The Code is as follows:
Mysqld_multi stop 1 disable instance 1 Mysqld_multi stop 1-2 close instance 1, 2

4. log on to instance 2 through the command line

The Code is as follows:
Mysql-u your_user-p your_password-P3307-S/tmp/mysql3307.sock

The

Mysqld_multi startup, shutdown, and status check
To call mysqld_multi, use the following syntax:

Shell> mysqld_multi [options] {start | stop | report} [GNR [, GNR]...]

Start, stop, and report indicate the operations you want to perform. You can perform the specified operation on a single server or multiple servers, depending on the GNR column after the option name. If this column does not exist, mysqld_multi is used by all servers in the option file.

Each GNR value represents an option group number or group number range. The GNR value should be the number at the end of the group name in the option file. For example, the GNR of the group [mysqld17] is 17. To specify the range of group numbers, separate the first and last numbers with a break number. The GNR value 10-13 indicates [mysqld10] to [mysqld13]. You can specify multiple groups or group ranges in the command line and separate them with commas. The GNR Column cannot contain spaces (spaces or tabs). content after spaces is ignored.

This command uses the consumer group [mysqld1] to start a single server:

The Code is as follows:
Shell>/usr/local/mysql/bin/mysqld_multi -- defaults-extra-file =/etc/my. cnf start 1

Run the following command to stop multiple servers: [mysql8] and [mysqld10] to [mysqld13]:

The Code is as follows:
Shell>/usr/local/mysql/bin/mysqld_multi -- defaults-extra-file =/etc/my. cnf stop 8, 10-13

Or close a single server.

The Code is as follows:
Shell> mysqladmin-u root-S/tmp/mysql3306.sock shutdown

Run the following command to view multiple servers: [mysql8] and [mysqld10] to [mysqld13]:

The Code is as follows:
Shell>/usr/local/mysql/bin/mysqld_multi -- defaults-extra-file =/etc/my. cnf report 8, 10-13

Articles you may be interested in

• Mysql multi-instance STARTUP configuration