MySQL multi-instance configuration (i)

The centralized operation of the MySQL database enables the deployment of 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, distributing data files to different disks at the same time 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 migration and cleanup, and improves the overall resource utilization of the server through multi-instance binding. There are 2 ways to configure multi-instance, one is to provide configuration for all instances in MY.CNF, and one configuration file per instance. This paper mainly describes the first way of narration.
Another multi-instance configuration approach: MySQL multi-instance configuration (ii)
For the installation of MySQL single instance, please refer to: Linux MySQL source code 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).

Assuming that SQL Server is installed 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 are several different databases that can exist under one instance.

For access to the database under different instances, you can use Servername/instancename:portno to achieve an interview, and the default instance is Servername:portno.
Configure IP addresses for different instances, related access protocols. Ports, and so on.

The accessibility of an instance requires that the corresponding service for that instance be started. It is important to note that the instance name and the service name of the instance 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 contains control files, data files, online log files, parameter files, password files, and so on called databases.
An instance can only access a database. A database can be interviewed by multiple instances.

Mysql
The concept of MySQL instance is almost identical to MSSQL. A MySQL instance can exist or access n databases.
Different instances can be distinguished by different port numbers. The data for each instance can use a different disk folder.
MySQL multi-instance is managed through the Mysqld_multi tool.

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

3, initialize instance
   #为新实例创建数据文件夹并赋权
  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. Change the configuration file
suse11:~ # MORE/ETC/MY.CNF#本配置文件里仅提供了多实例的基本參数, the production environment according to the circumstances of their own accession
[Mysqld_multi]
Mysqld =/u01/app/mysql/bin/mysqld_safe
Mysqladmin =/u01/app/mysql/bin/mysqladmin
user = Admin#此帐户用于多实例关闭时使用. 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)