MySQL multi-instance configuration (ii)

The centralized operation of the MySQL database enables you to deploy multiple MySQL instances on a single MySQL database server. This feature is implemented through Mysqld_multi. Mysqld_multi is used to manage multiple mysqld service processes, which can use different sockets or listen to different ports while distributing data files to different disks to scatter IO. Mysqld_multi provides simple commands for starting, shutting down, and reporting the status of managed servers. Therefore, it can reduce the maintenance cost of the production environment, facilitate the subsequent migration and cleanup, and improve the overall resource utilization of the server by multi-instance binding. There are 2 ways to configure multiple instances, one is to provide configuration for all instances in my.cnf, and one to use each instance of a configuration file. This article mainly describes the second approach.
For the first multi-instance configuration, please refer to: MySQL multi-instance configuration (i)
For MySQL Single instance installation please refer to: Linux under the MySQL source installation full version

1. Differences in multiple instances of each database
MSSQL
The instance in MSSQL refers to a single default instance on a SQL Server server. The default instance name is the machine name servername (or IP).
If you install SQL Server on the same machine, we can name the instance servername/instancename.
That is, there can be multiple different instances on a single SQL Server server. There can be multiple different databases under one instance.
For access to databases under different instances, access is achieved using SERVERNAME/INSTANCENAME:PORTNO, and the default instance is Servername:portno.
Configure IP addresses, associated access protocols, ports, and so on for different instances.
The accessibility of the instance requires that the corresponding service for that instance be started. It is important to note that the instance name and the instance service name are not the same.
The default instance service name is MSSQLSERVER, and the service name for the named instance is mssql$instance_name.

Oracle
An Oracle server consists of an Oracle instance and an Oracle database. That is: Oracle Server = Oracle Instance + Oracle Database
Instances of Oracle are mainly composed of SGA,PGA and a bunch of background processes, called instances.
A collection of physical files includes control files, data files, online log files, parameter files, password files, and so on called databases.
An instance can access only one database, and one database can be accessed by multiple instances.

Mysql
The MySQL instance concept is similar to MSSQL, where a MySQL instance can exist or access n databases.
Different instances can be distinguished by different port numbers, and the data for each instance can use a different disk directory.
MySQL multi-instance is managed through the Mysqld_multi tool.

2, the existing environment
MySQL Installation path:/u01/app/mysql
MySQL Data path:/u01/app/mysqldata/data3306
MySQL Port number: 3306
MySQL version: 5.6.12 Source distribution
OS Environment: SUSE Linux Enterprise Server SP3 (x86_64)

    #为新实例创建数据目录并赋权
  suse11:~ # mkdir-p/u01/app/mysqldata/ data3606  
  suse11:~ # mkdir-p/u01/app/mysqldata/data3706
  suse11:~ # chown Mysql:mysql-r/u01/app/mysqldata/data3606
  suse11:~ # chown Mysql:mysql-r/u01/app/mysqldata/data3706
  
   #初始化实例
  suse11:~ # cd/u01/app/ MySQL
  suse11:/u01/app/mysql #./scripts/mysql_install_db--user=mysql--ldata=/u01/app/mysqldata/ data3606/
  suse11:/u01/app/mysql #./scripts/mysql_install_db--user=mysql--ldata=/u01/app/ mysqldata/data3706/
  # Author:leshami
  # blog   : http://blog.csdn.net/leshami

4. Configure the my.cnf file for each instance
# VI/U01/APP/MYSQLDATA/DATA3606/MY3606.CNF

[Mysqld]
Socket =/tmp/mysql3606.sock
Port = 3606
Pid-file =/u01/app/mysqldata/data3606/mysql3606.pid
DataDir =/u01/app/mysqldata/data3606
Basedir =/u01/app/mysql
user = MySQL
server-id=3606
[Client]
Port = 3606
Socket =/tmp/mysql3606.sock

[MySQL]
No-auto-rehash
Socket =/tmp/mysql3606.sock
Prompt=\\[email protected]\\h[\\d]> \ \

# VI/U01/APP/MYSQLDATA/DATA3706/MY3706.CNF

[Mysqld]
Socket =/tmp/mysql3706.sock
Port = 3706
Pid-file =/u01/app/mysqldata/data3706/mysql3706.pid
DataDir =/u01/app/mysqldata/data3706
Basedir =/u01/app/mysql
user = MySQL
server-id=3706
[Client]
Port = 3706
Socket =/tmp/mysql3706.sock

[MySQL]
No-auto-rehash
Socket =/tmp/mysql3706.sock
Prompt=\\[email protected]\\h[\\d]> \ \

suse11:~ # Chown-r MYSQL:MYSQL/U01/APP/MYSQLDATA/DATA3606/MY3606.CNF
suse11:~ # Chown-r MYSQL:MYSQL/U01/APP/MYSQLDATA/DATA3706/MY3706.CNF

5, start off multi-instance
suse11:~ # Mysqld_safe--DEFAULTS-FILE=/U01/APP/MYSQLDATA/DATA3606/MY3606.CNF &
suse11:~ # Mysqld_safe--DEFAULTS-FILE=/U01/APP/MYSQLDATA/DATA3706/MY3706.CNF &
suse11:~ # Netstat-nltp|grep MySQL
TCP 0 0::: 3606:::* LISTEN 64277/mysqld
TCP 0 0::: 3706:::* LISTEN 64597/mysqld

suse11:~ # mysqladmin-uroot password ' xxx '-s/tmp/mysql3606.sock
suse11:~ # mysqladmin-uroot password ' xxx '-s/tmp/mysql3706.sock

#下面使用套接字方式连接到实例
suse11:~ # mysql-uroot-pxxx-s/tmp/mysql3606.sock
[Email protected][(None)]>

suse11:~ # mysql-uroot-pxxx-s/tmp/mysql3706.sock
[Email protected][(None)]>

#下面使用TCP方式连接到实例
suse11:~ # mysql-uroot-pxxx-p3606--protocol=tcp
[Email protected][(None)]> exit

suse11:~ # mysql-uroot-pxxx-p3706--protocol=tcp
[Email protected][(None)]>

#关闭mysql实例
suse11:~ # mysqladmin-uroot-pxxx-s/tmp/mysql3606.sock shutdown
suse11:~ # mysqladmin-uroot-pxxx-s/tmp/mysql3706.sock shutdown

MySQL multi-instance configuration (ii)