MySQL Single-host multi-instance Configuration

========================================================== ====================================================== Excerpted from hellodba

The single-host and multi-instance MySQL solution is to run multiple MySQL database instances on a physical PC server. Why? What are the benefits of doing so?

1. The rapid development of storage technology, Io is no longer a bottleneck

The CPU and I/O resources of ordinary PC servers are not balanced, because the I/O capabilities of disks are very limited. To meet the needs of applications, a large number of servers are often required, in this way, a large amount of CPU resources are wasted. However, the emergence of flash storage technology has changed all of this. The IO capability of a single machine is no longer a bottleneck. You can run multiple MySQL instances on a single machine to improve CPU utilization.

2. MySQL has low CPU usage for multiple cores

MySQL's low utilization of multi-core CPUs has always been a problem. MySQL versions earlier than MySQL 5.1 cannot be linearly expanded when the CPU exceeds 4 cores. Although MySQL has been improving this problem in subsequent versions, including InnoDB plugin and percona xtradb, both of which have improved the utilization of multi-core CPUs, the performance still cannot be improved as the CPU core increases. We currently use dual-channel Xeon servers. A single CPU has 4-8 cores. On the operating system, we can see 16-32 CPUs (each core has two threads ), four-way servers can reach 64-core or even more, so improving MySQL's CPU utilization for multiple cores is an important means to improve performance. Is a test data of percona:

========================================================== ====================================================== Excerpted from hellodba

Mysqld_multiAllows you to manage connections between multiple frames and different UNIX socket files and TCP/IP ports.MysqldProcess. It can start or stop servers or report their current status.

To callMysqld_multi, Use the following syntax:

shell> mysqld_multi [options] {start|stop|report} [GNR[,GNR] ...]

Start, stop, and report indicate the operations you want to perform. You can perform the specified operation on a single server or multiple servers, depending onGNRColumn. If this column does not exist,Mysqld_multiThis operation is performed on all servers in the option file.

The specific configuration is as follows:

#PWD

/Usr/local/MySQL/etc/

#Cat multi. CNF

[Mysqld_multi]

Mysqld =/usr/local/MySQL/bin/mysqld_safe

Mysqladmin =/usr/local/MySQL/bin/mysqladmin

User =Multi_admin

Password = 123123 # Make sure that the subsequent authorization password is the password here, otherwise the instance cannot be closed

 

[Mysqld2]

Socket =/tmp/MySQL. sock2

Port = 3307

PID-file =/var/run/mysqld/MySQL. pid2

Datadir =/data2

 

[Mysqld3]

Socket =/tmp/MySQL. sock3

Port = 3308

PID-file =/var/run/mysqld/MySQL. pid3

Datadir =/data3

 

[Mysqld4]

Socket =/tmp/MySQL. sock4

Port = 3309

PID-file =/var/run/mysqld/MySQL. pid4

Datadir =/data4

#Mkdir/data2/data3/data4

#Chown myql: mysql-r/data2/data3/data4

#CD/usr/local/MySQL

#./Scripts/mysql_install_db -- user = MySQL -- datadir =/data2

#./Scripts/mysql_install_db -- user = MySQL -- datadir =/data3

#./Scripts/mysql_install_db -- user = MySQL -- datadir =/data4

#/Usr/local/MySQL/bin/mysqld_multi \

-- Defaults-file =/usr/local/MySQL/etc/Multi. cnfstart 2-4// Enable instances from 2 to 4

//#/Usr/local/MySQL/bin/mysqld_multi \

-- Defaults-file =/usr/local/MySQL/etc/Multi. cnfstart 2, 4Enable instances 2 and 4

#Netstat-tulnp | grep MySQL

TCP 0 0 0.0.0.0: 3307 0.0.0.0: * Listen 15080/mysqld

TCP 0 0 0.0.0.0: 3308 0.0.0.0: * Listen 15088/mysqld

TCP 0 0 0.0.0.0: 3309 0.0.0.0: * Listen 15097/mysqld

#Mysql-u root-S/tmp/MySQL. sock2

Welcome to the mysqlmonitor. commands end with; or \ G.

Your MySQL connection ID is 1

Server version: 5.5.8-debug-log Source Distribution

 

Copyright (c) 2000,201 0, Oracle and/or its affiliates. All rights reserved.

This software comes withabsolutely no warranty. This is free software,

And you are welcome to modifyand redistribute it under the GPL V2 license

 

Type 'help; 'or' \ H' forhelp. Type '\ C' to clear the current input statement.

 

Mysql>Grant shutdown on *. * To multi_admin @ 'localhost' identified by '123 ';

Query OK, 0 rows affected (0.00 Sec)

 

Mysql>Flush privileges;

Query OK, 0 rows affected (0.01 Sec)

 

Mysql>Quit

Bye

#

Then, the instance can connect to and execute commands in sequence to grant permissions.

Mysql>Grant shutdown on *. * To multi_admin @ 'localhost' identified by '123 ';

Mysql>Flush privileges;

#/Usr/local/MySQL/bin/mysqld_multi \

-- Defaults-file =/usr/local/MySQL/etc/Multi. cnfstop 2-4

#Netstat-tulnp | grep MySQL

#

In addition, numa is closely related to the performance tuning of multiple MySQL instances. I will perform a detailed test in this regard.