MySQL多例項環境部署

一、MySQL多例項介紹

1、什麼是MySQL多例項

MySQL多例項就是在一臺機器上開啟多個不同的服務埠（如：3306,3307），執行多個MySQL服務程式，透過不同的socket監聽不同的服務埠來提供各自的服務:；

2、MySQL多例項的特點有以下幾點

(1)有效利用伺服器資源，當單個伺服器資源有剩餘時，可以充分利用剩餘的資源提供更多的服務。

(2)節約伺服器資源

(3)資源互相搶佔問題，當某個服務例項服務併發很高時或者開啟慢查詢時，會消耗更多的記憶體、CPU、磁碟IO資源，導致伺服器上的其他例項提供服務的質量下降；

3、部署mysql多例項的兩種方式

第一種是使用多個配置檔案啟動不同的程式來實現多例項，這種方式的優勢邏輯簡單，配置簡單，缺點是管理起來不太方便；

第二種是透過官方自帶的mysqld_multi使用單獨的配置檔案來實現多例項，這種方式定製每個例項的配置不太方面，優點是管理起來很方便，集中管理；

4、同一開發環境下安裝兩個資料庫，必須處理以下問題

配置檔案安裝路徑不能相同

資料庫目錄不能相同

啟動指令碼不能同名

埠不能相同

socket檔案的生成路徑不能相同

二、MySQL所例項安裝部署

1、在/usr/local/mysql解壓免編譯的二進位制包

[root@VM_2_13_centos mysql]# pwd

/usr/local/mysql

[root@VM_2_13_centos mysql]#

[root@VM_2_13_centos mysql]# ll

total 309288

drwxr-xr-x  2 root  root       4096 Mar 28 18:09 bin

-rw-r--r--  1 root  root      17987 Mar 28 18:09 COPYING

drwxr-xr-x  3 root  root       4096 Mar 28 18:09 data

drwxr-xr-x  2 root  root       4096 Mar 28 18:09 docs

drwxr-xr-x  3 root  root       4096 Mar 28 18:09 include

drwxr-xr-x  3 root  root       4096 Mar 28 18:09 lib

drwxr-xr-x  4 root  root       4096 Mar 28 18:09 man

drwxr-xr-x 13 mysql mysql      4096 Mar 28 17:57 mysql-5.6.36-linux-glibc2.5-x86_64

-rw-r--r--  1 mysql mysql 316320366 Mar 28 17:54 mysql-5.6.36-linux-glibc2.5-x86_64.tar.gz

drwxr-xr-x 10 root  root       4096 Mar 28 18:09 mysql-test

-rw-r--r--  1 root  root       2496 Mar 28 18:09 README

drwxr-xr-x  2 root  root       4096 Mar 28 18:09 scripts

drwxr-xr-x 28 root  root       4096 Mar 28 18:09 share

drwxr-xr-x  4 root  root       4096 Mar 28 18:09 sql-bench

drwxr-xr-x  2 root  root       4096 Mar 28 18:09 support-files

2、建立mysql組和mysql使用者

[root@VM_2_13_centos mysql]# groupadd -g 500 mysql

[root@VM_2_13_centos mysql]# useradd -u 501 -g mysql mysql

[root@VM_2_13_centos mysql]# id mysql

uid=501(mysql) gid=500(mysql) groups=500(mysql)

3、建立相關目錄

[root@VM_2_13_centos mysql]# mkdir -p /data/mysql/{mysql_3306,mysql_3307}

[root@VM_2_13_centos mysql]# ll /data/mysql/

total 8

drwxr-xr-x 2 root root 4096 Mar 28 18:04 mysql_3306

drwxr-xr-x 2 root root 4096 Mar 28 18:04 mysql_3307

[root@VM_2_13_centos mysql]# mkdir /data/mysql/mysql_3306/{data,log,tmp}

[root@VM_2_13_centos mysql]# mkdir /data/mysql/mysql_3307/{data,log,tmp}

4、修改目錄相關許可權

[root@VM_2_13_centos mysql]# chown -R mysql:mysql /data/mysql/

[root@VM_2_13_centos mysql]# chown -R mysql:mysql /usr/local/mysql/

5、新增環境變數

[root@VM_2_13_centos mysql]# echo 'export PATH=$PATH:/usr/local/mysql/bin' >>  /etc/profile

[root@VM_2_13_centos mysql]# source /etc/profile

6、複製my.cnf到/etc目錄下

[root@VM_2_13_centos mysql]# cp /usr/local/mysql/support-files/my-default.cnf /etc/my.cnf

7、修改my.cnf檔案

[root@VM_2_13_centos mysql]# vim /etc/my.cnf 

[client]

port=3306

socket=/tmp/mysql.sock

[mysqld_multi]

mysqld = /usr/local/mysql/bin/mysqld_safe

mysqladmin = /usr/local/mysql/bin/mysqladmin

log = /data/mysql/mysqld_multi.log

[mysqld]

user=mysql

basedir = /usr/local/mysql

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld3306]

mysqld=mysqld

mysqladmin=mysqladmin

datadir=/data/mysql/mysql_3306/data

port=3306

server_id=3306

socket=/tmp/mysql_3306.sock

log-output=file

slow_query_log = 1

long_query_time = 1

slow_query_log_file = /data/mysql/mysql_3306/log/slow.log

log-error = /data/mysql/mysql_3306/log/error.log

binlog_format = mixed

log-bin = /data/mysql/mysql_3306/log/mysql3306.bin

[mysqld3307]

mysqld=mysqld

mysqladmin=mysqladmin

datadir=/data/mysql/mysql_3307/data

port=3307

server_id=3307

socket=/tmp/mysql_3307.sock

log-output=file

slow_query_log = 1

long_query_time = 1

slow_query_log_file = /data/mysql/mysql_3307/log/slow.log

log-error = /data/mysql/mysql_3307/log/error.log

binlog_format = mixed

log-bin = /data/mysql/mysql_3307/log/mysql3307_bin

"/etc/my.cnf" 77L, 2317C written                              

8、初始化3306的資料庫

[root@VM_2_13_centos mysql]# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/data/mysql/mysql_3306/data --defaults-file=/etc/my.cnf

FATAL ERROR: please install the following Perl modules before executing /usr/local/mysql/scripts/mysql_install_db:

Data::Dumper

報錯原因缺少autoconf的依賴包

[root@VM_2_13_centos mysql]# yum install autoconf

9、再次初始化3306資料庫

[root@VM_2_13_centos ~]# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/data/mysql/mysql_3306/data --defaults-file=/etc/my.cnf

Installing MySQL system tables...2018-03-28 18:24:56 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2018-03-28 18:24:56 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.

2018-03-28 18:24:56 0 [Note] /usr/local/mysql//bin/mysqld (mysqld 5.6.36) starting as process 3434 ...

2018-03-28 18:24:56 3434 [Note] InnoDB: Using atomics to ref count buffer pool pages

2018-03-28 18:24:56 3434 [Note] InnoDB: The InnoDB memory heap is disabled

2018-03-28 18:24:56 3434 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins

2018-03-28 18:24:56 3434 [Note] InnoDB: Memory barrier is not used

2018-03-28 18:24:56 3434 [Note] InnoDB: Compressed tables use zlib 1.2.3

2018-03-28 18:24:56 3434 [Note] InnoDB: Using Linux native AIO

2018-03-28 18:24:56 3434 [Note] InnoDB: Using CPU crc32 instructions

2018-03-28 18:24:56 3434 [Note] InnoDB: Initializing buffer pool, size = 128.0M

2018-03-28 18:24:56 3434 [Note] InnoDB: Completed initialization of buffer pool

2018-03-28 18:24:56 3434 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!

2018-03-28 18:24:56 3434 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB

2018-03-28 18:24:56 3434 [Note] InnoDB: Database physically writes the file full: wait...

2018-03-28 18:24:56 3434 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB

2018-03-28 18:24:57 3434 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB

2018-03-28 18:24:57 3434 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0

2018-03-28 18:24:57 3434 [Warning] InnoDB: New log files created, LSN=45781

2018-03-28 18:24:57 3434 [Note] InnoDB: Doublewrite buffer not found: creating new

2018-03-28 18:24:57 3434 [Note] InnoDB: Doublewrite buffer created

2018-03-28 18:24:57 3434 [Note] InnoDB: 128 rollback segment(s) are active.

2018-03-28 18:24:57 3434 [Warning] InnoDB: Creating foreign key constraint system tables.

2018-03-28 18:24:57 3434 [Note] InnoDB: Foreign key constraint system tables created

2018-03-28 18:24:57 3434 [Note] InnoDB: Creating tablespace and datafile system tables.

2018-03-28 18:24:57 3434 [Note] InnoDB: Tablespace and datafile system tables created.

2018-03-28 18:24:57 3434 [Note] InnoDB: Waiting for purge to start

2018-03-28 18:24:57 3434 [Note] InnoDB: 5.6.36 started; log sequence number 0

2018-03-28 18:25:00 3434 [Note] Binlog end

2018-03-28 18:25:00 3434 [Note] InnoDB: FTS optimize thread exiting.

2018-03-28 18:25:00 3434 [Note] InnoDB: Starting shutdown...

2018-03-28 18:25:01 3434 [Note] InnoDB: Shutdown completed; log sequence number 1625977

OK

Filling help tables...2018-03-28 18:25:01 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2018-03-28 18:25:01 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.

2018-03-28 18:25:01 0 [Note] /usr/local/mysql//bin/mysqld (mysqld 5.6.36) starting as process 3458 ...

2018-03-28 18:25:01 3458 [Note] InnoDB: Using atomics to ref count buffer pool pages

2018-03-28 18:25:01 3458 [Note] InnoDB: The InnoDB memory heap is disabled

2018-03-28 18:25:01 3458 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins

2018-03-28 18:25:01 3458 [Note] InnoDB: Memory barrier is not used

2018-03-28 18:25:01 3458 [Note] InnoDB: Compressed tables use zlib 1.2.3

2018-03-28 18:25:01 3458 [Note] InnoDB: Using Linux native AIO

2018-03-28 18:25:01 3458 [Note] InnoDB: Using CPU crc32 instructions

2018-03-28 18:25:01 3458 [Note] InnoDB: Initializing buffer pool, size = 128.0M

2018-03-28 18:25:01 3458 [Note] InnoDB: Completed initialization of buffer pool

2018-03-28 18:25:01 3458 [Note] InnoDB: Highest supported file format is Barracuda.

2018-03-28 18:25:01 3458 [Note] InnoDB: 128 rollback segment(s) are active.

2018-03-28 18:25:01 3458 [Note] InnoDB: Waiting for purge to start

2018-03-28 18:25:01 3458 [Note] InnoDB: 5.6.36 started; log sequence number 1625977

2018-03-28 18:25:01 3458 [Note] Binlog end

2018-03-28 18:25:01 3458 [Note] InnoDB: FTS optimize thread exiting.

2018-03-28 18:25:01 3458 [Note] InnoDB: Starting shutdown...

2018-03-28 18:25:03 3458 [Note] InnoDB: Shutdown completed; log sequence number 1625987

OK

To start mysqld at boot time you have to copy

support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !

To do so, start the server, then issue the following commands:

  /usr/local/mysql//bin/mysqladmin -u root password 'new-password'

  /usr/local/mysql//bin/mysqladmin -u root -h VM_2_13_centos password 'new-password'

Alternatively you can run:

  /usr/local/mysql//bin/mysql_secure_installation

which will also give you the option of removing the test

databases and anonymous user created by default.  This is

strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:

  cd . ; /usr/local/mysql//bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl

  cd mysql-test ; perl mysql-test-run.pl

Please report any problems at

The latest information about MySQL is available on the web at

Support MySQL by buying support/licenses at

WARNING: Found existing config file /usr/local/mysql//my.cnf on the system.

Because this file might be in use, it was not replaced,

but was used in bootstrap (unless you used --defaults-file)

and when you later start the server.

The new default config file was created as /usr/local/mysql//my-new.cnf,

please compare it with your file and take the changes you need.

WARNING: Default config file /etc/my.cnf exists on the system

This file will be read by default by the MySQL server

If you do not want to use this, either remove it, or use the

--defaults-file argument to mysqld_safe when starting the server

9、再次初始化3307資料庫

[root@VM_2_13_centos ~]# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/data/mysql/mysql_3307/data --defaults-file=/etc/my.cnf

Installing MySQL system tables...2018-03-28 18:26:35 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2018-03-28 18:26:35 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.

2018-03-28 18:26:35 0 [Note] /usr/local/mysql//bin/mysqld (mysqld 5.6.36) starting as process 3559 ...

2018-03-28 18:26:35 3559 [Note] InnoDB: Using atomics to ref count buffer pool pages

2018-03-28 18:26:35 3559 [Note] InnoDB: The InnoDB memory heap is disabled

2018-03-28 18:26:35 3559 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins

2018-03-28 18:26:35 3559 [Note] InnoDB: Memory barrier is not used

2018-03-28 18:26:35 3559 [Note] InnoDB: Compressed tables use zlib 1.2.3

2018-03-28 18:26:35 3559 [Note] InnoDB: Using Linux native AIO

2018-03-28 18:26:35 3559 [Note] InnoDB: Using CPU crc32 instructions

2018-03-28 18:26:35 3559 [Note] InnoDB: Initializing buffer pool, size = 128.0M

2018-03-28 18:26:35 3559 [Note] InnoDB: Completed initialization of buffer pool

2018-03-28 18:26:35 3559 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!

2018-03-28 18:26:35 3559 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB

2018-03-28 18:26:35 3559 [Note] InnoDB: Database physically writes the file full: wait...

2018-03-28 18:26:35 3559 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB

2018-03-28 18:26:36 3559 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB

2018-03-28 18:26:36 3559 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0

2018-03-28 18:26:36 3559 [Warning] InnoDB: New log files created, LSN=45781

2018-03-28 18:26:36 3559 [Note] InnoDB: Doublewrite buffer not found: creating new

2018-03-28 18:26:36 3559 [Note] InnoDB: Doublewrite buffer created

2018-03-28 18:26:36 3559 [Note] InnoDB: 128 rollback segment(s) are active.

2018-03-28 18:26:36 3559 [Warning] InnoDB: Creating foreign key constraint system tables.

2018-03-28 18:26:36 3559 [Note] InnoDB: Foreign key constraint system tables created

2018-03-28 18:26:36 3559 [Note] InnoDB: Creating tablespace and datafile system tables.

2018-03-28 18:26:36 3559 [Note] InnoDB: Tablespace and datafile system tables created.

2018-03-28 18:26:36 3559 [Note] InnoDB: Waiting for purge to start

2018-03-28 18:26:36 3559 [Note] InnoDB: 5.6.36 started; log sequence number 0

2018-03-28 18:26:39 3559 [Note] Binlog end

2018-03-28 18:26:39 3559 [Note] InnoDB: FTS optimize thread exiting.

2018-03-28 18:26:39 3559 [Note] InnoDB: Starting shutdown...

2018-03-28 18:26:41 3559 [Note] InnoDB: Shutdown completed; log sequence number 1625977

OK

Filling help tables...2018-03-28 18:26:41 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2018-03-28 18:26:41 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.

2018-03-28 18:26:41 0 [Note] /usr/local/mysql//bin/mysqld (mysqld 5.6.36) starting as process 3583 ...

2018-03-28 18:26:41 3583 [Note] InnoDB: Using atomics to ref count buffer pool pages

2018-03-28 18:26:41 3583 [Note] InnoDB: The InnoDB memory heap is disabled

2018-03-28 18:26:41 3583 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins

2018-03-28 18:26:41 3583 [Note] InnoDB: Memory barrier is not used

2018-03-28 18:26:41 3583 [Note] InnoDB: Compressed tables use zlib 1.2.3

2018-03-28 18:26:41 3583 [Note] InnoDB: Using Linux native AIO

2018-03-28 18:26:41 3583 [Note] InnoDB: Using CPU crc32 instructions

2018-03-28 18:26:41 3583 [Note] InnoDB: Initializing buffer pool, size = 128.0M

2018-03-28 18:26:41 3583 [Note] InnoDB: Completed initialization of buffer pool

2018-03-28 18:26:41 3583 [Note] InnoDB: Highest supported file format is Barracuda.

2018-03-28 18:26:41 3583 [Note] InnoDB: 128 rollback segment(s) are active.

2018-03-28 18:26:41 3583 [Note] InnoDB: Waiting for purge to start

2018-03-28 18:26:41 3583 [Note] InnoDB: 5.6.36 started; log sequence number 1625977

2018-03-28 18:26:41 3583 [Note] Binlog end

2018-03-28 18:26:41 3583 [Note] InnoDB: FTS optimize thread exiting.

2018-03-28 18:26:41 3583 [Note] InnoDB: Starting shutdown...

2018-03-28 18:26:43 3583 [Note] InnoDB: Shutdown completed; log sequence number 1625987

OK

To start mysqld at boot time you have to copy

support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !

To do so, start the server, then issue the following commands:

  /usr/local/mysql//bin/mysqladmin -u root password 'new-password'

  /usr/local/mysql//bin/mysqladmin -u root -h VM_2_13_centos password 'new-password'

Alternatively you can run:

  /usr/local/mysql//bin/mysql_secure_installation

which will also give you the option of removing the test

databases and anonymous user created by default.  This is

strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:

  cd . ; /usr/local/mysql//bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl

  cd mysql-test ; perl mysql-test-run.pl

Please report any problems at

The latest information about MySQL is available on the web at

Support MySQL by buying support/licenses at

WARNING: Found existing config file /usr/local/mysql//my.cnf on the system.

Because this file might be in use, it was not replaced,

but was used in bootstrap (unless you used --defaults-file)

and when you later start the server.

The new default config file was created as /usr/local/mysql//my-new.cnf,

please compare it with your file and take the changes you need.

WARNING: Default config file /etc/my.cnf exists on the system

This file will be read by default by the MySQL server

If you do not want to use this, either remove it, or use the

--defaults-file argument to mysqld_safe when starting the server

11、檢視資料庫是否初始化成功

埠為3306資料庫

[root@VM_2_13_centos ~]# ll /data/mysql/mysql_3306/data/

-rw-rw---- 1 mysql mysql       56 Mar 28 18:45 auto.cnf

-rw-rw---- 1 mysql mysql 12582912 Mar 29 16:07 ibdata1

-rw-rw---- 1 mysql mysql 50331648 Mar 29 16:07 ib_logfile0

-rw-rw---- 1 mysql mysql 50331648 Mar 28 18:24 ib_logfile1

drwx------ 2 mysql mysql     4096 Mar 28 18:25 mysql

drwx------ 2 mysql mysql     4096 Mar 28 18:24 performance_schema

drwx------ 2 mysql mysql     4096 Mar 28 18:19 test

埠為3307資料庫

[root@VM_2_13_centos ~]# ll /data/mysql/mysql_3307/data/

-rw-rw---- 1 mysql mysql       56 Mar 28 18:29 auto.cnf

-rw-rw---- 1 mysql mysql 12582912 Mar 29 11:38 ibdata1

-rw-rw---- 1 mysql mysql 50331648 Mar 29 11:38 ib_logfile0

-rw-rw---- 1 mysql mysql 50331648 Mar 28 18:26 ib_logfile1

drwx------ 2 mysql mysql     4096 Mar 28 18:26 mysql

drwx------ 2 mysql mysql     4096 Mar 28 18:26 performance_schema

drwx------ 2 mysql mysql     4096 Mar 28 18:26 test

12、設定啟動檔案

[root@VM_2_13_centos ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql

13、mysqld_multi進行多例項管理

啟動全部例項：/usr/local/mysql/bin/mysqld_multi start

檢視全部例項狀態：/usr/local/mysql/bin/mysqld_multi report

啟動單個例項：/usr/local/mysql/bin/mysqld_multi start 3306

停止單個例項：/usr/local/mysql/bin/mysqld_multi stop 3306

檢視單個例項狀態：/usr/local/mysql/bin/mysqld_multi report 3306

14、啟動全部例項

[root@VM_2_13_centos ~]# /usr/local/mysql/bin/mysqld_multi start

[root@VM_2_13_centos ~]# mysqld_multi report

Reporting MySQL servers

MySQL server from group: mysqld3306 is running

MySQL server from group: mysqld3307 is running

15、檢視相關埠狀態

[root@VM_2_13_centos ~]# netstat -tulan

tcp6       0      0 :::3307                 :::*                    LISTEN     

tcp6       0      0 :::3306                 :::*                    LISTEN  

特殊情況：

如果無法透過mysqld_multi stop命令關閉一個mysql資料庫，可以嘗試使用下面的命令進行關閉

[root@VM_2_13_centos ~]# mysqladmin   -uroot   -p  -S  /tmp/mysql_3306.sock   shutdown                                

Enter password:

[root@VM_2_13_centos ~]# mysqld_multi report                    

Reporting MySQL servers

MySQL server from group: mysqld3306 is not running

MySQL server from group: mysqld3307 is running

作者：SEian.G（苦練七十二變，笑對八十一難）