MySQL series of multi-instance 1----Introduction

Recently, I will use a few blog to introduce MySQL's multi-instance is what, how to deploy and how to maintain, I hope to be helpful to everyone.

Introduced:

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.

Here is a metaphor to help you understand the nature of MySQL multi-instance.

MySQL Multi-instance is equivalent to renting a house, there are many tenants, each tenant rent a bedroom, this bedroom is equivalent to one of our MySQL instance. The entire rental is equivalent to a server. Together with the washing machine, bathroom, balcony is equivalent to our server on a variety of hardware resources, such as CPU, MEM, disk, and so on, these things are public resources, everyone shared.

In addition, multi-instance is not only MySQL, in fact, our daily transport dimension encountered many services can be deployed using multi-instance, and in the production environment is also very keen to use, and even in the portal application is also very broad, such as Nginx multi-instance, Apache multi-instance, Redis multi-instance and so on.

Now that we are all going to use MySQL multi-instance technology, what can MySQL multi-instance bring to the enterprise or have any advantages and disadvantages?

The advantages are as follows:

1, the effective use of server resources when a single server resource surplus, can make full use of the remaining resources to provide more services 2, save the 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 more than a few instances of 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 it is possible to deploy multiple instances with a set of physical database servers to facilitate subsequent schema expansion, migration

Disadvantages are as follows:

1. Resource preemption problem When a service instance is high or slow, 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.

Here is a repeat,MySQL multi-instance in the production environment of the application scenario!

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 deploy 6-9 instances in 3 servers, then cross-master from synchronous backup and read-write separation, to achieve the 6-9 server can achieve the effect of 2, the company's business traffic is not too large, 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, as long as the reasonable allocation of system resources, there will not be too big a 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 method (NUMA processor must support, However, the majority of processors now support the allocation of different databases to different instances to provide data services; 4, the traditional game industry mmo/mmorpg and 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.

Having said so much,how to deploy MySQL multi-instance? What are the deployment methods?

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 to centralize management management

Cons: Inconvenient 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

< for the third scenario, IM-based, personal testing regardless of 5.1 and 5.5 installed, there is no mysqlmanager, so there is no implementation of the plan for the time being, but also hope to forgive >

Above, that is, for MySQL multi-instance introduction, the next blog post will start for the different implementations of MySQL implementation.

This article is from the "not only Linux" blog, so be sure to keep this source http://nolinux.blog.51cto.com/4824967/1440882

MySQL series of multi-instance 1----Introduction