MySQL multi-instance configuration using method

MySQL multi-instance is to install the MySQL database software on one server, configure different listening ports, the application can connect different databases according to the port, the library and the library do not affect each other.

1. Download and install the MySQL database

[Email protected] ~]# wget http://mirrors.sohu.com/mysql/MySQL-5.6/mysql-5.6.23-linux-glibc2.5-x86_64.tar.gz

[Email protected] ~]# tar xvf mysql-5.6.23-linux-glibc2.5-x86_64.tar.gz-c/usr/local/

[Email protected] ~]# cd/usr/local/

[Email protected] local]# MV mysql-5.6.23-linux-glibc2.5-x86_64/mysql-5.6.23
[Email protected] local]# chown-r root:mysql mysql-5.6.23/
[Email protected] local]# chown-r mysql:mysql mysql-5.6.23/data/

[Email protected] local]# CD mysql-5.6.23/

[Email protected] mysql-5.6.23]#/scripts/mysql_install_db--user=mysql--group=mysql--basedir=/usr/local/ mysql-5.6.23--datadir=/usr/local/mysql-5.6.23/data

[Email protected] mysql-5.6.23]# cp-a my.cnf/etc/
[Email protected] mysql-5.6.23]# cp-a support-files/mysql.server/etc/init.d/mysqld

[[email protected] mysql-5.6.23]# chkconfig--add MySQL

[Email protected] mysql-5.6.23]# chkconfig mysqld on

2. Modify the configuration file

[[email protected] mysql-5.6.23]# VIM/ETC/MY.CNF--Other parameters can be deleted, if you need to add parameters can be added in each instance option

[Mysqld_multi]
Mysqld=/usr/local/mysql-5.6.23/bin/mysqld_safe
Mysqladmin=/usr/local/mysql-5.6.23/bin/mysqladmin
Log=/var/log/mysql.log

[Mysqld1]
Socket=/tmp/mysql1.sock
port=3306
Server-id=1
Pid-file=/data/mysql1.pid
Datadir=/data1
User=mysql

[Mysqld2]
Socket=/tmp/mysql2.sock
port=3307
server-id=2
Pid-file=/data/mysql2.pid
Datadir=/data2
User=mysql

[MYSQLD3]
Socket=/tmp/mysql3.sock
port=3308
Server-id=3
Pid-file=/data/mysql3.pid
Datadir=/data3
User=mysql

[Mysqld4]
Socket=/tmp/mysql4.sock
port=3309
Server-id=4
Pid-file=/data/mysql4.pid
Datadir=/data4
User=mysql

[Email protected] mysql-5.6.23]#

3. Create the Data catalog and the primary database

[[email protected] mysql-5.6.23]# mkdir/data{1,2,3,4}--Create a folder for data storage

[[email protected] mysql-5.6.23]# chown-r mysql:mysql/data{1,2,3,4}--Add the appropriate permissions

[Email protected] mysql-5.6.23]#/usr/local/mysql-5.6.23/scripts/mysql_install_db--user=mysql--group=mysql-- basedir=/usr/local/mysql-5.6.23--datadir=/data1 &--Initial database

[Email protected] mysql-5.6.23]#/usr/local/mysql-5.6.23/scripts/mysql_install_db--user=mysql--group=mysql-- basedir=/usr/local/mysql-5.6.23--datadir=/data2 &

[Email protected] mysql-5.6.23]#/usr/local/mysql-5.6.23/scripts/mysql_install_db--user=mysql--group=mysql-- basedir=/usr/local/mysql-5.6.23--datadir=/data3 &

[Email protected] mysql-5.6.23]#/usr/local/mysql-5.6.23/scripts/mysql_install_db--user=mysql--group=mysql-- basedir=/usr/local/mysql-5.6.23--DATADIR=/DATA4 &

[Email protected] mysql-5.6.23]#/usr/local/mysql-5.6.23/bin/mysqld_multi--defaults-extra-file=/etc/my.cnf start 1,2,3,4--Start the DB instance

[[email protected] mysql-5.6.23]#/usr/local/mysql-5.6.23/bin/mysqld_multi Report--see if the instance is started
Reporting MySQL Servers
MySQL server from Group:mysqld1 is running
MySQL server from Group:mysqld2 is running
MySQL server from GROUP:MYSQLD3 is running
MySQL server from Group:mysqld4 is running

[Email protected] mysql-5.6.23]# Netstat-antup | grep MySQL
TCP 0 0::: 3307:::* LISTEN 22774/mysqld
TCP 0 0::: 3308:::* LISTEN 22811/mysqld
TCP 0 0::: 3309:::* LISTEN 22801/mysqld
TCP 0 0::: 3306:::* LISTEN 22810/mysqld
[Email protected] mysql-5.6.23]#

4. Log in each instance to verify that each instance is independent of each other
[[email protected] mysql-5.6.23]# mysqladmin-u root password ' system '-s/tmp/mysql1.sock--Modify the initial password
Warning:using a password on the command line interface can is insecure.
[[email protected] mysql-5.6.23]# mysql-u root-p-s/tmp/mysql1.sock--login Example 1
Enter Password:
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 4
Server version:5.6.23 MySQL Community Server (GPL)

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

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

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

mysql> CREATE Database Tong;
Query OK, 1 row affected (0.02 sec)

Mysql> \u Tong
Database changed
Mysql> CREATE TABLE T (a int);
Query OK, 0 rows affected (0.29 sec)

Mysql> INSERT INTO T values (1);
Query OK, 1 row affected (0.05 sec)

Mysql> exit
Bye
[[email protected] mysql-5.6.23]# mysql-u root-p-s/tmp/mysql2.sock--Instance 2 cannot use the password of instance 1
Enter Password:
ERROR 1045 (28000): Access denied for user ' root ' @ ' localhost ' (using Password:yes)
[Email protected] mysql-5.6.23]# mysqladmin-u root password ' system2 '-s/tmp/mysql2.sock
Warning:using a password on the command line interface can is insecure.
[[email protected] mysql-5.6.23]# mysql-u root-p-s/tmp/mysql2.sock--Instance 2 can be logged in
Enter Password:
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 6
Server version:5.6.23 MySQL Community Server (GPL)

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

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

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

Mysql> \u Tong
ERROR 1049 (42000): Unknown database ' Tong '
Mysql> exit
Bye
[Email protected] mysql-5.6.23]#

5. Close a Single instance

[[email protected] mysql-5.6.23]#/usr/local/mysql-5.6.23/bin/mysqld_multi stop 1-2

[Email protected] mysql-5.6.23]#/usr/local/mysql-5.6.23/bin/mysqld_multi report
Reporting MySQL Servers
MySQL server from Group:mysqld1 are not running
MySQL server from Group:mysqld2 are not running
MySQL server from GROUP:MYSQLD3 is running
MySQL server from Group:mysqld4 is running
[Email protected] mysql-5.6.23]#

This article is from the "Days Together" blog, please be sure to keep this source http://tongcheng.blog.51cto.com/6214144/1640977

MySQL multi-instance configuration using method