MySQL Single-host multi-instance Configuration

MySQL Single-host multi-instance Configuration

Test environment:

Operating System CentOS 6.4x86

MySQL 5.5.38

1. What is MySQL multi-instance?

Multiple MySQL instances enable multiple service ports (such as 3306, 3307, and 3308) on one machine to run multiple MySQL service processes, different sockets are used to listen to different service ports to provide their own services. Each instance is independent of each other. The datadir, port, socket, and pid of each instance are different.

2. Features of MySQL multi-instance
• Effectively utilize server resources. When a single server resource is available, you can make full use of the remaining resources to provide more services.

• Resource preemption. When a service instance is highly concurrent or enables slow queries, more memory, CPU, and disk I/O resources are consumed, as a result, the quality of services provided by other instances on the server is reduced.

3. Application scenarios
• The reason why the pseudo-distributed data architecture is adopted, and the initial stage of the project does not necessarily have a large number of users. Therefore, a group of physical database servers should be established, but multiple instances should be deployed to facilitate subsequent migration;

• To avoid the disadvantages that mysql does not support the SMP architecture, bind multiple instances to a processor and allocate different databases to different instances to provide data services;

• A physical database server supports data services for multiple databases. To improve the recovery efficiency of mysql replication slave machines, multiple instances are deployed;

• For the dual-master replication mysql database server architecture, I would like to add a hot backup for some important business data in a remote data center. mysql replication does not support the multi-master replication mode at the moment, if you do not provide services to users, to effectively control costs, you may consider deploying a physical server with superior performance in a remote data center, or even multiple instances in addition to the Cabinet;

• The MMO/MMORPG and Web Game servers in the traditional gaming industry correspond to one database, and a lot of data query and correction work may be done to reduce the probability of errors caused by maintenance, you may also use multi-instance deployment to allocate databases by partition;

4. Conventions

1. store all the installation files, configuration files, and data Directories In the/mydata/data directory to facilitate fast migration, overall backup, and fast replication in the future;

2. Configure two MySQL instances on one server and bind them to ports 3306 and 3307 respectively.

3, The instance uses the my-medium.cnf configuration file; we can customize the my. cnf configuration of each instance according to the actual needs.

My. cnf configuration file has two solutions:
1.
Multiple instances share the same my. cnf configuration file and use the [mysqld1], [mysqld2], and [mysqld *] labels to implement differentiated configurations for different instances;

2.
Each instance has a separate my. cnf configuration file.

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

--------------------------------------------------------------------------------

Solution 1: each instance has a separate my. cnf configuration file.

Datadir:/mydata/data/3306

/Mydata/data/3307

My. cnf:/mydata/data/3306/my. cnf

/Mydata/data/3307/my. cnf

5. Install MySQL (Common binary mode)

1. Create mysql users and groups

[Root @ localhost ~] # Groupadd-r mysql
[Root @ localhost ~] # Useradd-r-g mysql-s/sbin/nologin mysql
2. Directory Planning

We create a separate directory for each instance: 3306,330 7
[Root @ localhost ~] # Mkdir-pv/mydata/data/330 {6, 7}
Mkdir: created directory '/mydata/data/8080'
Mkdir: created directory '/mydata/data/8080'
[Root @ localhost ~] # Tree/mydata/data/
/Mydata/data/
| -- 3306.
'-- 3307
3. Decompress

[Root @ localhost ~] # Tar mysql-5.5.38-linux2.6-i686.tar.gz-C/usr/local/src
[Root @ localhost ~] # Cd/usr/local/
[Root @ localhost local] # ln-sv src/mysql-5.5.38-linux2.6-i686/mysql
Create symbolic link 'mysql' to 'src/mysql-5.5.38-linux2.6-i686 /'
4. Provide the configuration file and edit it.
[Root @ localhost local] # cd mysql
[Root @ localhost mysql] # cp support-files/my-medium.cnf/mydata/data/3306/my. cnf
[Root @ localhost mysql] # cp support-files/my-medium.cnf/mydata/data/3307/my. cnf

# Here is the experiment environment, so it is easy to configure. Please adjust the audience according to your actual needs

### 3306
# The following options will be passed to all MySQL clients
[Client]
# Password = your_password
Port = 3306
Socket =/tmp/mysql_3306.sock

# Here follows entries for some specific programs

# The MySQL server
[Mysqld]
Port = 3306
Socket =/tmp/mysql_3306.sock
Pid-file =/mysql data/3306/mysql. pid
User = mysql
Basedir =/usr/local/mysql
Data =/mydata/data/3306

######################################## ###################

### 3307
# The following options will be passed to all MySQL clients
[Client]
# Password = your_password
Port = 3307
Socket =/tmp/mysql_3307.sock

# Here follows entries for some specific programs

# The MySQL server
[Mysqld]
Port = 3307
Socket =/tmp/mysql_3307.sock
Pid-file =/mysql data/3307/mysql. pid
User = mysql
Basedir =/usr/local/mysql
Data =/mydata/data/3307
5. Modify the owner and group of the Data Directory
[Root @ localhost mysql] # chown-R mysql: mysql/mydata/data/3306
[Root @ localhost mysql] # chown-R mysql: mysql/mydata/data/3307
6. Add the mysql/bin directory to the PATH
[Root @ localhost mysql] # vi/etc/profile. d/mysql. sh
# Add
Export PATH = $ PATH:/usr/local/mysql/bin
[Root @ localhost mysql] #./etc/profile. d/mysql. sh
7. Initialization
# Initializing instance 1
[Root @ localhost mysql] # scripts/mysql_install_db -- basedir =/usr/local/mysql -- datadir =/mydata/data/3306 -- user = mysql
# Initializing instance 2
[Root @ localhost mysql] # scripts/mysql_install_db -- basedir =/usr/local/mysql -- datadir =/mydata/data/3307 -- user = mysql
8. Start/close an instance

Here is a question: how can each instance read its own my. cnf configuration file? We need to manually specify

•
/Usr/local/mysql/bin/mysqld_safe

•
-- Defaults-file manually specify the configuration file

### Start an instance
[Root @ localhost mysql] #/usr/local/mysql/bin/mysqld_safe -- defaults-file =/mydata/data/3306/my. cnf &>/dev/null &
[1] 1526
[Root @ localhost mysql] #/usr/local/mysql/bin/mysqld_safe -- defaults-file =/mydata/data/3307/my. cnf &>/dev/null &
[2] 1832

[Root @ localhost mysql] # netstat-tulpn | grep-I mysql
Tcp 0 0 0.0.0.0: 3306 0.0.0.0: * LISTEN 1815/mysqld
Tcp 0 0 0.0.0.0: 3307 0.0.0.0: * LISTEN 2121/mysqld

### Disable an instance. Do not operate it first
[Root @ localhost ~] #/Usr/local/mysql/bin/mysqladmin-uroot-p-S/tmp/mysql_3306.sock shutdown
[Root @ localhost ~] #/Usr/local/mysql/bin/mysqladmin-uroot-p-S/tmp/mysql_3307.sock shutdown
# Note: the root user of mysql does not set a password here. Just press Enter when prompted.
OK. We can see that the mysqld listener is on ports 3306 and 3307.

Here, we can also provide a service startup script for management. Let's take a look at the support-files/mysql. server Service Startup Script.

The following is a script template: (you can change it as needed)
#! /Bin/bash

./Etc/init. d/functions

PORT = $2

USER = root
PASSWD =

MYSQLBIN = '/usr/local/mysql/bin'
SOCKETFILE = "/tmp/mysql _ $ {PORT}. sock"
PIDFILE = "/mydata/data/$ {PORT}/mysql. pid"
MYCNF = "/mydata/data/$ {PORT}/my. cnf"

[[$ #-Eq 2] | {
Echo "Usage: $0 {start | stop | restart | reload} {PORT }"
Exit 1
}

Mysql_start (){
[[-E "$ SOCKETFILE"] & {
Action "MySQL port: $ port is already running"/bin/false
Exit 0
} | {
Action "Starting MySQL... please wait"/bin/true
$ MYSQLBIN/mysqld_safe -- defaults-file = $ MYCNF &>/dev/null &
}

[["$? "=" 0 "] & {
Action "MySQL has been Started"/bin/true
} | {
Action "MySQL Started"/bin/false
}
}

Mysql_stop (){
[[! -E "$ SOCKETFILE"] & {
Action "MySQL port: $ PORT was already down"/bin/false
} | {
$ MYSQLBIN/mysqladmin-u $ USER-p $ PASSWD-S $ SOCKETFILE shutdown &>/dev/null
}
[["$? "= 0] & {
Action "MySQL port: $ PORT has been Stopped"/bin/true
}
}

Case "$1" in
'Start ')
Mysql_start
;;
'Stop ')
Mysql_stop
;;
'Restart' | 'reload ')
Mysql_stop
Sleep 3
Mysql_start
;;
*)
Echo "Usage: $0 {start | stop | restart | reload} {PORT }"
Esac

 

9. Test the connection logon instance

### Specify the socket through which to connect
# Note: the root user of mysql does not set a password, so you can log on directly.

[Root @ localhost mysql] #/usr/local/mysql/bin/mysql-S/tmp/mysql_3306.sock
[Root @ localhost mysql] #/usr/local/mysql/bin/mysql-S/tmp/mysql_3307.sock

For more details, please continue to read the highlights on the next page:

1 2 Next Page