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 http://bugs.mysql.com/

 

The latest information about MySQL is available on the web at

 

  http://www.mysql.com

 

Support MySQL by buying support/licenses at http://shop.mysql.com

 

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 http://bugs.mysql.com/

 

The latest information about MySQL is available on the web at

 

  http://www.mysql.com

 

Support MySQL by buying support/licenses at http://shop.mysql.com

 

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（苦练七十二变，笑对八十一难）

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31015730/viewspace-2153184/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31015730/viewspace-2153184/