MySQL multi-instance configuration (i)

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. This reduces the maintenance cost of the production environment, facilitates the subsequent migration and cleanup work, and improves the overall resource utilization of the server through 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 first method.
For the second multi-instance configuration, please refer to: MySQL multi-instance configuration (ii)
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.

   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/ data3406  
  suse11:~ # mkdir-p/u01/app/mysqldata/data3506
  suse11:~ # chown Mysql:mysql-r/u01/app/mysqldata/data3406
  suse11:~ # chown Mysql:mysql-r/u01/app/mysqldata/data3506
  
   #初始化实例
  suse11:~ # cd/u01/app/ MySQL
  suse11:/u01/app/mysql #./scripts/mysql_install_db--user=mysql--ldata=/u01/app/mysqldata/ data3406/
  suse11:/u01/app/mysql #./scripts/mysql_install_db--user=mysql--ldata=/u01/app/ mysqldata/data3506/
  # Author:leshami
  # blog  : http://blog.csdn.net/leshami

4. Modify the configuration file
suse11:~ # MORE/ETC/MY.CNF#本配置文件中仅提供了多实例的基本参数, the production environment is self-adding according to the situation
[Mysqld_multi]
Mysqld =/u01/app/mysql/bin/mysqld_safe
Mysqladmin =/u01/app/mysql/bin/mysqladmin
user = Admin#此帐户用于多实例关闭时使用, you need to create and authorize on each instance
Password = XXX#使用统一的密码便于管理

[mysqld3306]
Socket =/tmp/mysql3306.sock
Port = 3306
Pid-file =/u01/app/mysqldata/data3306/mysql3306.pid
DataDir =/u01/app/mysqldata/data3306
Basedir =/u01/app/mysql
user = MySQL
server-id=3306

[mysqld3406]
Socket =/tmp/mysql3406.sock
Port = 3406
Pid-file =/u01/app/mysqldata/data3406/mysql3406.pid
DataDir =/u01/app/mysqldata/data3406
Basedir =/u01/app/mysql
user = MySQL
server-id=3406

[mysqld3506]
Socket =/tmp/mysql3506.sock
Port = 3506
Pid-file =/u01/app/mysqldata/data3506/mysql3506.pid
DataDir =/u01/app/mysqldata/data3506
Basedir =/u01/app/mysql
user = MySQL
server-id=3506

5, start off multi-instance
suse11:~ # Mysqld_multi Report
Reporting MySQL Servers
MySQL server from group:mysqld3306 are not running
MySQL server from group:mysqld3406 are not running
MySQL server from group:mysqld3506 are not running

suse11:~ # Mysqld_multi Start 3306
suse11:~ # Mysqld_multi Start 3406,3506#可以同时启动多个实例
suse11:~ # NETSTAT-NTLP | grep MySQL
TCP 0 0::: 3306:::* LISTEN 14786/mysqld
TCP 0 0::: 3406:::* LISTEN 15103/mysqld
TCP 0 0::: 3506:::* LISTEN 15371/mysqld

SUSE11:/TMP # ls *.sock
Mysql3306.sock Mysql3406.sock Mysql3506.sock

#为新实例修改密码及创建账户
Suse11:/tmp # mysql-uroot-pxxx-s./mysql3306.sock#3306已经有初始密码
[Email protected][(None)]> Grant shutdown on * .' admin ' @ ' localhost 'identified by ' xxx ' with GRANT option;

Suse11:/tmp # mysql-uroot-p-S/mysql3406.sock
Enter Password: #此时密码为空
  [Email protected][(None)]> Set password for' root ' @ ' localhost ' =password (' xxx ' ));

[Email protected][(None)]> Grant shutdown on * .' admin ' @ ' localhost 'identified by ' xxx ' with GRANT option;

Suse11:/tmp # mysql-uroot-p-S/mysql3506.sock
Enter Password: #此时密码为空
  [Email protected][(None)]> Set password for' root ' @ ' localhost ' =password (' xxx ' ));

[Email protected][(None)]> Grant shutdown on * .' admin ' @ ' localhost 'identified by ' xxx ' with GRANT option;

   #使用TCP方式登录测试
  suse11:/tmp # mysql-uroot-pxxx-p3506
   [ email protected][(none   
    #检查多实例的状态
  suse11:/tmp # Mysqld_multi report
   reporting MySQL Servers
  mysql server from group:mysqld3306 is running
  mysql server From group:mysqld3406 are running
  mysql server from group:mysqld3506 is running

#停止多实例服务器
suse11:~ # Mysqld_multi Stop 3306
suse11:~ # Mysqld_multi Report 3306
Reporting MySQL Servers
MySQL server from group:mysqld3306 are not running
suse11:~ # Mysqld_multi Stop 3406
suse11:~ # Mysqld_multi Stop 3506

#多实例服务器的日志
suse11:~ # Tail/u01/app/mysql/share/mysqld_multi.log
Stopping MySQL servers

Warning:using a password on the command line interface can is insecure.
141017 23:40:09 mysqld_safe mysqld from PID File/u01/app/mysqldata/data3406/mysql3406.pid ended
Mysqld_multi log file version 2.16; RUN:FRI Oct 17 23:40:09 2014

Stopping MySQL servers

Warning:using a password on the command line interface can is insecure.
141017 23:40:11 mysqld_safe mysqld from PID File/u01/app/mysqldata/data3506/mysql3506.pid ended

#一次启动多个实例
suse11:~ # Mysqld_multi Start 3306-3506
suse11:~ # Mysqld_multi Report
Reporting MySQL Servers
MySQL server from group:mysqld3306 is running
MySQL server from group:mysqld3406 is running
MySQL server from group:mysqld3506 is running

6. Configure multi-instance self-start
# echo "/u01/app/mysql/bin/mysqld_multi start 3306-3506" >>/etc/init.d/boot.local

MySQL multi-instance configuration (i)