MySQL Multi-instance configuration

What is a multi-instance

To put it simply, MySQL multi-instance is to open multiple different service ports on a single server (for example 3306/3307 : running multiple MySQL service processes concurrently, these service processes provide services through different sockets that listen to different service ports.
These MySQL multiple instances share a set of MySQL installers that use different my.cnf (and can same) configuration files, startup programs (or the same), and data files. In providing services, multi-instance MySQL logically appears to be independent, they are based on the configuration file corresponding to the set value of the server response to the number of resources.

When you do the following, you first install MySQL before you can do the following

install MySQL:http://www.cnblogs.com/HByang/p/9177505.html

Configuration of multiple instances

1. Introduction to the Environment

[Email protected] ~]$ cat/etc/redhat-Release CentOS release6.8(Final) [Root@DB01 ~]$ uname-R2.6.32-642. El6.x86_64[root@DB01 ~]$ uname-Mx86_64[root@DB01 ~]$ MYSQL-UROOT-P123123-E"select version ();"warning:using a password on the command line interface can is insecure.+-----------+| Version () |+-----------+| 5.6.38 |+-----------+#Single Instance installation location:[Root@DB01 ~]$ ll/application/ Total4lrwxrwxrwx1 root root 6 13:23 MySQL->/application/mysql-5.6.38/drwxr-xr-x MySQL mysql 4096 Mar 6 13:49 mysql-5.6.38

2. Creation of multiple instances

[Email protected] ~]$ mkdir-p/data/{3306,3307}[root@DB01 ~]$ cp-a/application/mysql/my.cnf/data/3306/[Root@DB01 ~]$ cp-a/application/mysql/my.cnf/data/3307/[Root@DB01 ~]$ vim/data/3306/My.cnfuser=Mysqlport= 3306Socket=/data/3306/Mysql.sockbasedir=/application/Mysqldatadir=/data/3306/Datapid-file=/data/3306/Mysql.pidlog-error=/data/3306/Mysql_3306.err=================================>[Root@DB01 ~]$ vim/data/3307/My.cnfuser=Mysqlport= 3307Socket=/data/3307/Mysql.sockbasedir=/application/Mysqldatadir=/data/3307/Datapid-file=/data/3307/Mysql.pidlog-error=/data/3307/mysql_3307.err

3. Create a startup script

[Email protected] ~]$ vim/data/3306/Mysqld

#!/bin/bashport=3306Mysql_user="Root"Cmdpath="/application/mysql/bin"Mysql_sock="/data/${port}/mysql.sock"Mysqld_pid_file_path=/data/${port}/${port}. Pidstart () {if[!-E"$mysql _sock"];then printf"starting mysql...\n"/bin/sh${cmdpath}/mysqld_safe--defaults-file=/data/${port}/MY.CNF--pid-file=$mysqld _pid_file_path2>&1 >/dev/null &Sleep3Elseprintf"MySQL is running...\n"Exit1Fi}stop () {if[!-E"$mysql _sock"];then printf"MySQL is stopped...\n"Exit1Elseprintf"stoping mysql...\n"Mysqld_pid= ' Cat"$mysqld _pid_file_path"`         if(kill-0$mysqld _pid2>/dev/null) then kill$mysqld _pidSleep2fi Fi}restart () {printf"restarting mysql...\n"Stop Sleep2Start}case" $" inchstart) Start;    stop) stop;;    restart) restart;; *) printf"Usage:/data/${port}/mysql {start|stop|restart}\n"Esac

[Email protected] ~]$ cp/data/3306/mysqld/data/3307/

[Email protected] ~]$ Vim/data/3307/mysqld

Open Mysqld Script just modify the port number on line 2nd to save exit

4, the right to empower

[Email protected] ~]$ chown-r mysql.mysql/data/[root@DB01 ~]$ chmod +x/data/3306/mysqld[root@DB0 1 ~]$ chmod +x/data/3307/mysqld

5. Initialize

cd/application/mysql/scripts. /mysql_install_db  --defaults-file=/data/3306/my.cnf--basedir=/application/mysql/--datadir=/data/ 3306/data--user=$?   ---> test succeeded (0 indicates success is not 0 failure). /mysql_install_db  --defaults-file=/data/3307/my.cnf--basedir=/application/mysql/--datadir=/data/ 3307/data--user=$?

6, to start

[Email protected] ~]$/data/3306/mysqld startstarting MySQL ... [Root@DB01 ~]$/data/3307/mysqld startstarting MySQL ... [Root@DB01 ~]$ Netstat-lntup |  0 0::: 3306:::* LISTEN 2104/0 0::: 3307:::* LISTEN 2719/mysqld login Test [root@DB01 ~]$ mysql-s/da ta/3306/Mysql.sock [Root

To this our simple MySQL multi-instance configuration is complete, as for the multi-instance optimization according to the business requirements to set

MySQL Multi-instance configuration