MySQL 5.6.x multi-instance Configuration

MySQL 5.6.x multi-instance Configuration
Prerequisites

For testing, the installation is in binary mode, which is simple, convenient, and fast.
MySQL-5.6.x binary version installation record

I do not like to configure multiple instances in mysqlmulti mode or multi-process mode.

1. Prepare the datadir directory of the database instance.

# Mkdir-p/usr/local/mysql/mysql3307
# Chown mysql: mysql/usr/local/mysql/mysql3307

Initialize the instance directory
#/Usr/local/mysql/scripts/mysql_install_db -- user = mysql -- basedir =/usr/local/mysql -- datadir =/usr/local/mysql/mysql3307

2. Prepare the configuration file

(1) modify the existing configuration file name my. cnf to 3306.cnf, and copy a configuration file name 3307.cnf.
(2) modify the configuration file 3306. cnf, port, sock location, pid location, datadir location, and so on.

[Client]
Port = 3306
Socket =/tmp/mysql3306.sock

[Mysqld]
Port = 3306
Socket =/tmp/mysql3306.sock
Datadir =/usr/local/mysql/data

(3) 3307. Modify the cnf configuration file

[Client]
Port = 3307
Socket =/tmp/mysql3307.sock

[Mysqld]
Port = 3307
Socket =/tmp/mysql3307.sock
Datadir =/usr/local/mysql/mysql3307

 

3. Start Multiple instances. the startup script can be added to the boot auto-start file.

/Usr/local/mysql/bin/mysqld_safe -- defaults-file =/etc/3306.cnf 2> & 1>/dev/null &
/Usr/local/mysql/bin/mysqld_safe -- defaults-file =/etc/3307.cnf 2> & 1>/dev/null &

 

4. Database Logon

#/Usr/local/mysql/bin/mysql-u root-p-P 3306-S/tmp/mysql3306.sock
#/Usr/local/mysql/bin/mysql-u root-p-P 3307-S/tmp/mysql3307.sock

Change instance password 3307:
#/Usr/local/mysql/bin/mysqladmin-u root password 'admin'-S/tmp/mysql3307.sock # Set the Administrator password

 

3306 Example 1 of Logon:

[Root @ test ~] # Mysql-uroot-p-S/tmp/mysql3306.sock
Enter password:
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 14
Server version: 5.6.24-log Source distribution

Copyright (c) 2000,201 5, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
Affiliates. Other names may be trademarks of their respective
Owners.

Type 'help; 'or' \ H' for help. type' \ C' to clear the current input statement.

Mysql>
Mysql> \ q
Bye

3307 logon example:

[Root @ test ~] # Mysql-uroot-p-S/tmp/mysql3307.sock
Enter password:
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 15
Server version: 5.6.24-log Source distribution

Copyright (c) 2000,201 5, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
Affiliates. Other names may be trademarks of their respective owners.

Type 'help; 'or' \ H' for help. type' \ C' to clear the current input statement.

Mysql>

The specified port number is also accessible:

Mysql-uroot-p-h127.0.0.1-P3307

5. Disable mysql multi-instance mode

#/Usr/local/mysql/bin/mysqladmin-u root-p-P 3306-S/tmp/mysql3306.sock shutdown
#/Usr/local/mysql/bin/mysqladmin-u root-p-P 3307-S/tmp/mysql3307.sock shutdown

Or use the kill method, or write the startup script.

This article permanently updates the link address: