MySQL Multi-instance

MySQL Multi-instance

MySQL multi-instance, simple to understand is on a server, the MySQL service to open a number of different ports (such as 3306, 3307), running multiple service processes. These MySQL service processes use different sockets to listen to different data ports, thus providing their own services independently of each other.

On the same server, MySQL multi-instance will go to share a MySQL application, so when you deploy MySQL you only need to deploy a MySQL program, no multiple deployment. However, MySQL multiple instances will each use different MY.CNF configuration files, startup programs, and data files. In the provision of services, MySQL multi-instances logically appear to be independent, non-interfering, and multiple instances are based on the configuration file set values, to obtain the hardware resources of the relevant server.

.

The advantages are as follows:
1. Efficient Use of server resources
When a single server is out of resources, you can take advantage of the remaining resources to provide more services
2. Conserve server resources
When the company funds tight, but the database needs to provide services between the database, and also want to use the master-slave synchronization technology, at this time, many instances of the better
3. Convenient post-architecture extension
When a project of a company starts, it does not necessarily have a large number of users in the initial stage, so you can deploy multiple instances on it with a set of physical database servers to facilitate subsequent schema expansion, migration
Disadvantages are as follows:
1, the resource of mutual preemption problem
When a service instance is in high concurrency or has slow queries, the entire instance consumes more memory, CPU, and IO resources, which results in a poor quality of service from other instances on the server. This is, for example, the rental of the various tenants, whenever the morning to work, will wash, the occupancy rate of the toilet is large, all tenants will always happen to wait.
Multi-instance application scenarios
1, when a company's business visits are not too large, and want to save costs, and also hope that the different business database services can be as independent as possible, the provision of services can be mutually unaffected. In addition, we need to apply the master-slave synchronization technology to provide database backup or read/write separation service, as well as the extension and migration of database schema when the later business volume increases. At this point, Mysql Multi-instance is no better. For example, we can achieve the effect of 6-9 servers by deploying 6-9 instances on 3 servers and then cross-cutting from synchronous backup and read-write separation.
2, the company's business visits are not too big time, the server's resources are basically surplus state. This is a good fit for MySQL multi-instance applications. If you do better with SQL statement optimization, MySQL multi-instance is a technology that is worth using. Even if the late business concurrency is very large, so long as reasonable allocation of system resources, there will not be too much problem
3, in order to avoid MySQL on the SMP architecture does not support the defects, we can use the MySQL multi-instance binding processor approach (NUMA processor must support, but now most processors are supported) to allocate different databases to different instances to provide data services;
4, the traditional game industry MMO/MMORPG and the web game, each service will be corresponding to a database, and may often have to do a lot of data query and data correction work. At this point, in order to reduce the probability of maintenance errors, we can also adopt multi-instance deployment, the concept of the region to allocate the database.

.

3 ways to implement MySQL multi-instance
MySQL multi-instance routine, there are three kinds of programs can be implemented, these three kinds of scenarios have advantages and disadvantages, as follows:
1, based on multi-configuration files
Multiple instances are implemented by using multiple configuration files to start different processes.
Advantages: Simple logic, simple configuration
Cons: Not easy to manage.
2, based on Mysqld_multi
Use a separate configuration file to implement multiple instances with the official Mysqld_multi tool
Advantages: Easy Centralized Management
Cons:? Not easy to customize for each instance configuration
3. IM-based
Using the MySQL Instance Manager (Mysqlmanager), this method seems to be better, but it's a little complicated.
Advantages: Easy to centralize management
Disadvantage: High coupling degree. Im a hang, the instance is all hung
Not easy to customize for each instance configuration

Environment [SELinux off]
[Email protected] ~]# cat/etc/redhat-release
CentOS Release 6.9 (Final)

[Email protected] ~]# uname-r
2.6.32-696.3.2.el6.x86_64

[Email protected] ~]# Getenforce
Disabled
Note: If you have MySQL installed some parts are not to do, so here is highlighted is the configuration of the multi-instance section, the first part and the second part with------split
Deploy [4 instances]

• Download MySQL 5.7 Two package [recommended official DOWNLOAD] This download version is greater than 5.7.5
  [Email protected] ~]# wget wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz

• Unzip the MySQL 5.7 binary package to the specified directory
  [Email protected] ~]# tar zxvf mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz-c/usr/local/

• Create a MySQL soft link
  [Email protected] ~]# ln-s/usr/local/mysql-5.7.18-linux-glibc2.5-x86_64/usr/local/mysql

• Create a MySQL user
  [[email protected] ~]# useradd-r-s/sbin/nologin MySQL

• Create the Mysql-files directory in the MySQL binary package directory [MySQL data import/Export data exclusive directory]
  [Email protected] ~]# mkdir-v/usr/local/mysql/mysql-files
  mkdir:created directory '/usr/local/mysql/mysql-files '

• Create a multi-instance data Catalog
  [[email protected] ~]# mkdir-vp/data/mysql_data{1..4}
  mkdir:created directory /data‘ <br/>mkdir: created directory /data/mysql_data1 '
  mkdir:created directory '/data/mysql_data2 '

mkdir:created directory /data/mysql_data3‘ <br/>mkdir: created directory /data/mysql_data4 '

• To modify the user and owning group of the MySQL binary package directory
  1 [[Email protected] ~]# chown root.mysql-r/usr/local/mysql-5.7.18-linux-glibc2.5-x86_64

• Modify MySQL Multi-instance data directory with data import/export exclusive directory for the owning user and owning group

  [Email protected] ~]# chown mysql.mysql-r/usr/local/mysql/mysql-files/data/mysql_data{1..4}

• Configuring the MySQL configuration file/etc/my.cnf
  [Mysqld_multi]
  Mysqld =/usr/local/mysql/bin/mysqld
  Mysqladmin =/usr/local/mysql/bin/mysqladmin
  Log =/tmp/mysql_multi.log

[Mysqld1]
Set data directory [must be different in multiple instances]
DataDir =/data/mysql_data1
Set sock store file name [must be different in multiple instances]
Socket =/tmp/mysql.sock1
Set listening open ports [must be different in multiple instances]
Port = 3306
Set up run user
user = MySQL
Turn off monitoring
Performance_schema = Off
Setting the InnoDB cache size
Innodb_buffer_pool_size = 32M
Setting the Listening IP address
Bind_address = 0.0.0.0
Turn off DNS reverse resolution
Skip-name-resolve = 0

[Mysqld2]
DataDir =/data/mysql_data2
Socket =/tmp/mysql.sock2
Port = 3307
user = MySQL
Performance_schema = Off
Innodb_buffer_pool_size = 32M
Bind_address = 0.0.0.0
Skip-name-resolve = 0

[MYSQLD3]
DataDir =/data/mysql_data3
Socket =/tmp/mysql.sock3
Port = 3308
user = MySQL
Performance_schema = Off
Innodb_buffer_pool_size = 32M
Bind_address = 0.0.0.0
Skip-name-resolve = 0

[Mysqld4]
DataDir =/data/mysql_data4
Socket =/tmp/mysql.sock4
Port = 3309
user = MySQL
Performance_schema = Off
Innodb_buffer_pool_size = 32M
Bind_address = 0.0.0.0
Skip-name-resolve = 0

• Initializes each instance [will bring a random password in the output log after initialization is completed]
  [[email protected] ~]#/usr/local/mysql/bin/mysqld--ini Tialize--user=mysql--basedir=/usr/local/mysql--datadir=/data/mysql_data1
  [[email protected] ~]#/usr/ Local/mysql/bin/mysqld--initialize--user=mysql--basedir=/usr/local/mysql--datadir=/data/mysql_data2
  [[Email  protected] ~]#/usr/local/mysql/bin/mysqld--initialize--user=mysql--basedir=/usr/local/mysql--datadir=/ DATA/MYSQL_DATA3
  [[email protected] ~]#/usr/local/mysql/bin/mysqld--initialize--user=mysql--basedir=/ Usr/local/mysql--datadir=/data/mysql_data4

• Each instance turns on SSL connection
  [Email protected] ~]#/usr/local/mysql/bin/mysql_ssl_rsa_setup--user=mysql--basedir=/usr/local/mysql--datadir=/ Data/mysql_data1
  [Email protected] ~]#/usr/local/mysql/bin/mysql_ssl_rsa_setup--user=mysql--basedir=/usr/local/mysql--datadir=/ Data/mysql_data2
  [Email protected] ~]#/usr/local/mysql/bin/mysql_ssl_rsa_setup--user=mysql--basedir=/usr/local/mysql--datadir=/ Data/mysql_data3
  [Email protected] ~]#/usr/local/mysql/bin/mysql_ssl_rsa_setup--user=mysql--basedir=/usr/local/mysql--datadir=/ Data/mysql_data4

• Copy the multi-instance script to the service Management directory [/etc/init.d/]
  [Email protected] ~]# Cp/usr/local/mysql/support-files/mysqld_multi.server/etc/init.d/mysqld_multi

• Add Script Execution permissions
  [Email protected] ~]# chmod +x/etc/init.d/mysqld_multi

• Add in service Management
  [Email protected] ~]# chkconfig--add mysqld_multi

5. Start test

   • Check for multiple instance status
     [Email protected] ~]#/etc/init.d/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 are not running
     MySQL server from GROUP:MYSQLD4 are not running

• Start Multi-instance

  [[email protected] ~]#/etc/init.d/mysqld_multi start

• View multi-instance status
  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

• viewing instance listening ports
  [Email protected] ~]# NETSTAT-LNTP | grep mysqld
  TCP 0 0 0.0.0.0:3306 0.0.0.0: LISTEN 2673/mysqld
  TCP 0 0 0.0.0.0:3307 0.0.0.0: LISTEN 2676/mysqld
  TCP 0 0 0.0.0.0:3308 0.0.0.0: LISTEN 2679/mysqld
  TCP 0 0 0.0.0.0:3309 0.0.0.0: LISTEN 2682/mysqld

Connection
* Example 1

[Email protected] ~]#/usr/local/mysql/bin/mysql-s/tmp/mysql.sock1-p ' z+ilo*>s:3kw '
MySQL: [Warning] Using a password on the command line interface can is insecure.
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 6
Server version:5.7.18

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> Set password = ' 123456 ';
Query OK, 0 rows Affected (0.00 sec)

Separate configuration for each instance
MySQL Installation path:/usr/local/mysql
MySQL Data path:/usr/local/mysql/data
MySQL Port number: 3306
MySQL version: 5.7. Source distribution
Environment: Centos 7 (x86_64)

Initializing instances
#为新实例创建数据目录并赋权

mkdir -p  /usr/local/mysql/data3307mkdir -p  /usr/local/mysql/data3308chown mysql:mysql -R  /usr/local/mysql/data3307

Chown Mysql:mysql-r/usr/local/mysql/data3308

#初始化实例

suse11:~ # Cd/usr/local/mysql
[Email protected] ~]#/usr/local/mysql/bin/mysqld--initialize--user=mysql--basedir=/usr/local/mysql--datadir=/ Usr/local/mysql/3307/data
[Email protected] ~]#/usr/local/mysql/bin/mysqld--initialize--user=mysql--basedir=/usr/local/mysql--datadir=/ Usr/local/mysql/3308/data

Configure the My.cnf file for each instance here is only one example

VI//usr/local/mysql/3307/my3307.cnf

[Client]
Socket=/usr/local/mysql/mysql7.sock
port=3307
[Mysqld]
Basedir=/usr/local/mysql
Datadir=/usr/local/mysql/3307/data
User=mysql
Log-error=/usr/local/mysql/3307/data/mysql.err
Pid-file=/usr/local/mysql/3307/data/mysqld.pid
Socket=/usr/local/mysql/mysql7.sock
port=3307
[MySQL]
No-auto-rehash
Socket =/usr/local/mysql/mysql7.sock

Chown-r Mysql:mysql//usr/local/mysql/3307/data/my3307.cnf
Chown-r mysql:mysql/usr/local/mysql/3307/data/my3308.cnf

Start close Multi-instance

suse11:~ # mysqld_safe--defaults-file=/usr/local/mysql/3307/my3307.cnf >>/dev/null
suse11:~ # mysqld_safe--defaults-file=/usr/local/mysql/3307/my3308.cnf >>/dev/null

suse11:~ # Netstat-nltp|grep MySQL
TCP 0 0::: 3307::: LISTEN 64277/mysqld
TCP 0 0::: 3308:: : LISTEN 64597/mysqld

Grep Password/usr/local/mysql/data/mysql.err
Append temporary password CP to-P

#下面使用套接字方式连接到实例
Mysql-uroot-p ' v98hasg2. '-s/usr/local/mysql/mysql7.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实例
Mysqladmin-uroot-pxxx-s/usr/local/mysql/mysql7.sock shutdown
Mysqladmin-uroot-pxxx-s/usr/local/mysql/mysql7.sock shutdown

MySQL Multi-instance