MySQL multi-instance introduction and implementation

One, what is MySQL multi-instance

MySQL multi-instance, simply put, is to open a number of different MySQL service ports (such as 3306,3307) on a single server, running multiple MySQL service processes. These service processes use different sockets to listen on different service ports to provide their own services.

These MySQL instances share a common set of MySQL installers that use different my.cnf configuration files, startup programs, and data files. When providing services, MySQL multi-instance logically appears to be independent, each instance according to the configuration file settings, to obtain the relevant hardware resources of the server.

Second, MySQL multi-instance advantages and disadvantages

Excellent

1. Efficient use of server resources, when a single server resources are left, you can make full use of the remaining server resources to provide more services.

2. Conserve server resources when the company funds tight, but the database needs to provide separate services, and the need for master-slave synchronization and other technologies, the use of multi-instance is best.

Inferior

1. Resource-to-mutual preemption issues

When an instance service is in high concurrency or has slow queries, it consumes more resources such as memory, CPU, disk IO, and so on, which causes the quality of access to other instances on the server to degrade, and server resources preempt each other.

Three, MySQL multi-instance application scenario

1. Choice of capital-intensive companies

When the company's business visits are not too large, but also reluctant to spend money, but at the same time hope that the different services of the database service independent, but also require master-slave synchronization and other technologies to provide backup or read-write separation services, using multi-instance is the best.

2. Concurrent access is not a particularly large business

When the company's business visits are not too large, the server resources are basically idle more, this is very suitable for multi-instance applications. If the SQL statement is optimized, multiple instances are a good technique to use. Even if the concurrency is very large, so long as the reasonable allocation of system resources, there will not be too much problem.

Four, MySQL multi-instance realization method

There are two ways that MySQL can be implemented in multiple instances, both of which have pros and cons.

The first is to use multiple configuration files to launch different processes to implement multiple instances, the advantages of this method is simple, simple configuration, the disadvantage is not easy to manage.

The second is the use of the official Mysqld_multi with a separate configuration file to achieve multi-instance, this way to customize the configuration of each instance is not too much, the advantage is easy to manage, centralized management.

V. Using the first method to achieve multiple instances

Installation steps

(This article has installed the MySQL program, you can refer to http://iywdd.blog.51cto.com/11177578/1768135 installation)

Upload multi-instance configuration files (the general work is configured, the configuration of this article is attached below)

[[email protected] ~]# cd /data/    #进入下载目录 [[Email protected] data ]# rz     #使用rz上传做好的配置文件 [[email protected] data]# unzip  mysqlmutil.zip  #解压上传的配置文件Archive:  mysqlmutil.zip   creating: 3306/   inflating: 3306/my.cnf                inflating: 3306/mysql                  creating: 3307/  inflating: 3307/my.cnf                inflating: 3307/mysql               [[email protected]  data]# tree ./   #查看解压的内容 ./├── 3306│   ├── my.cnf│    └── mysql├── 3307│&nbSp;  ├── my.cnf│   └── mysql├── mysqlmutil.zip 

Initializing the database

Modify the owner of the file to prevent initialization of the database failure

[[email protected] data]# chown-r mysql.mysql 3306 3307[[email protected] data]# ll-d 3306 3307drwxr-xr-x 2 mysql MySQL 4096 April 13:39 3306drwxr-xr-x 2 mysql mysql 4096 April 17 13:39 3307

Initial database

MySQL installation path (/application/mysql/)

Initialize First database

[Email protected] data]#/application/mysql/scripts/mysql_install_db--user=mysql--basedir=/application/mysql/-- Datadir=/data/3306/datainstalling MySQL system tables ... OK #出现OK表示成功Filling Help Tables ... OK #出现OK表示成功To start mysqld at boot time, omit the copysupport-files/mysql.server to the right place for your system. 。。。。。

Initializing a second database

[Email protected] data]#/application/mysql/scripts/mysql_install_db--user=mysql--basedir=/application/mysql/-- Datadir=/data/3307/data Installing MySQL system tables ... OK #出现OK表示成功Filling Help Tables ... OK #出现OK表示成功To start mysqld at boot time and you have to copysupport-files/mysql.server to the right place for your system

--user=mysql#my. cnf [mysqld] under "user = MySQL"

--basedir=/application/mysql/#表示mysql软件的目录

--datadir=/data/3307/data #表示数据存放的目录

Start test

Change the permissions to 500, the file is involved in the database password

[Email protected]/]# chmod 500/data/{3306,3307}/mysql[[email protected]/]# ll/data/{3306,3307}/mysql-r-x------ 1 MySQL MySQL 1016 April 7 07:17/data/3306/mysql-r-x------1 mysql mysql 1016 April 7 07:18/data/3307/mysql

Start

Start using Mysqld_safe to specify the configuration file

Instance One

[Email protected]/]#/data/3306/mysql startstarting MySQL ...                   [[email protected]/]# netstat-lntup|grep mysql|grep-v greptcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 18504/mysqld

Example Two

[[email protected] /]# /data/3307/mysql start                  starting mysql ... [[email protected] /]# netstat -lntup|grep mysql|grep -v greptcp         0      0 0.0.0.0:3307                 0.0.0.0:*                    listen       19225/mysqld        tcp         0      0 0.0.0.0:3306                 0.0.0.0:*             &nbSp;      listen      18504/mysqld 

Turn off use mysqladmin to specify user shutdown

Close the database

[[email protected]/]#/data/3307/mysql stopstoping mysql.../application/mysql/bin/mysqladmin:connect to server at ' localhost ' failederror: ' Access denied for user ' root ' @ ' localhost ' (using Password:yes) '

Unable to close, prompt MySQL account password is incorrect

#配置文件my. cnf#############################

mysql_user= "Root" #用户是root

Mysql_pwd= "123456" #密码123456

#配置文件my. cnf#############################

Configure Mysqladmin account password

[Email protected]/]# mysqladmin-uroot password ' 123456 '-s/data/3307/mysql.sock #多实例需要指定sock

Stop instance Two

[[email protected] /]# /data/3307/mysql stop                                                              Stoping  Mysql... [[email protected] /]# netstat -lntup|grep mysql|grep -v grep                                             tcp        0       0 0.0.0.0:3306                 0.0.0.0:*                    listen      18504/mysqld

Restart database

[[email protected]/]#/data/3307/mysql start[[email protected]/]#/data/3307/mysql stop

The difference between MySQL multi-instance (My.cnf,mysql)

#my. cnf

Distinguish one

650) this.width=650; "src=" http://s5.51cto.com/wyfs02/M02/7F/80/wKioL1cg3VmSIg1OAAHMz18tovo669.jpg "style=" float: none; "title=" my difference one. jpg "alt=" wkiol1cg3vmsig1oaahmz18tovo669.jpg "/>

Port and data directory, socket storage path is not the same

Difference Two

650) this.width=650; "src=" http://s5.51cto.com/wyfs02/M00/7F/82/wKiom1cg3JCA3vRQAAGC3ejX3Rc847.jpg "style=" float: none; "Title=" my difference two. jpg "alt=" wkiom1cg3jca3vrqaagc3ejx3rc847.jpg "/>

There's no change in the ballpark, just 3306 to 3307.

Difference Three

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M00/7F/80/wKioL1cg3qPiZAtVAAKhM56gr7k292.jpg "title=" My differences three. jpg "alt=" wkiol1cg3qpizatvaakhm56gr7k292.jpg "/> There is nothing changed here and above, the only thing to note is that" Server-id "cannot be the same

#mysql

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M01/7F/80/wKioL1cg3veiXhQtAAFtEdEMdv8038.jpg "title=" MySQL difference. jpg "alt=" wkiol1cg3veixhqtaaftedemdv8038.jpg "/>

Now if you add more than one instance, change 3306 to 3308,server-id=4 to add a third multi-instance

This article is from the "Operations Zone" blog, please be sure to keep this source http://iywdd.blog.51cto.com/11177578/1768416

MySQL multi-instance introduction and implementation