MySQL搭建系列之多实例

   所谓多实例，就是在一台服务器上搭建、运行多个MySQL实例，每个实例使用不同的服务端口，通过不同的socket监听；物理上，每个实例拥有独立的参数配置文件及数据库。

通常情况下，一台服务器是不建议运行多个MySQL实例的，尤其是生产环境，因为这会造成资源相互强占问题。但在一些高可用环境中，却有这样的需求，比如：利用Heartbeat搭建的故障转移集群环境，若主备服务器只运行一个MySQL实例，则备用服务器平常处于闲置状态，造成了资源浪费；若各自运行一个MySQL实例，则在一方故障时，要求另一方接管其任务，即同时运行两个MySQL实例。基于这个需求，应合理配置MySQL服务器，使其具备同时运行多个实例的能力。

如何才能在一台服务器上搭建、运行多个MySQL实例，其关键在于如何为每个实例分配独立的参数文件，以下是几种常用的多实例方案，分别来学习、熟悉一下。

系统环境

OS：CentOS 5.8 (x86_64)     内核：2.6.18-308.el5     DB：MySQL 5.5.17

一． 采用源码包安装MySQL实现多实例

源码包是最灵活的安装包，可定制编译一些路径参数，可安装到任何路径，因此可在一台服务器搭建多个MySQL实例。本例安装两个MySQL实例，步骤如下：

1.  准备工作

从MySQL 5.5开始，改用cmake工具来编译源码包，所以要首先安装它，可参考其它文档，此处不做讲解。另外，编译之前还需安装ncurses-devel、bison两个依赖包，否则会编译失败。

――安装依赖包

# rpm -ivh ncurses-devel-5.5-24.20060715.x86_64.rpm

# rpm -ivh bison-2.3-2.1.x86_64.rpm

――创建mysql系统组及用户

# groupadd -g 497 mysql

# useradd –u 499 -g mysql mysql

――解压安装包

# tar -zxvf mysql-5.5.17.tar.gz

# cd mysql-5.5.17

2.  编译、安装

l  MySQL实例1：

（端口：3306，安装目录：/usr/local/mysqla，数据文件目录：/data/lib/mysqla）

――定制编译

# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysqla-DMYSQL_TCP_PORT=3306 -DMYSQL_DATADIR=/data/lib/mysqla-DMYSQL_UNIX_ADDR=/data/lib/mysqla/mysql.sock -DSYSCONFDIR=/usr/local/mysqla-DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1-DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1-DWITH_PARTITION_STORAGE_ENGINE=1 -DENABLED_LOCAL_INFILE=1 -DWITH_READLINE=1-DWITH_SSL=yes -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci-DEXTRA_CHARSETS=all

# make

# make install

――创建系统数据库及系统表

# cd /usr/local/mysqla/

# scripts/mysql_install_db --user=mysql--basedir=/usr/local/mysqla --datadir=/data/lib/mysqla

l  MySQL实例2：

（端口：3307，安装目录：/usr/local/mysqlb，数据文件目录：/data/lib/mysqlb）

――删除旧的缓存及对象文件

再次编译之前，要清除原来的缓存信息及对象文件，如下：

# cd mysql-5.5.17

# make clean

# rm -rf CMakeCache.txt

――定制编译

# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysqlb-DMYSQL_TCP_PORT=3307 -DMYSQL_DATADIR=/data/lib/mysqlb-DMYSQL_UNIX_ADDR=/data/lib/mysqlb/mysql.sock -DSYSCONFDIR=/usr/local/mysqlb-DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1-DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1-DWITH_PARTITION_STORAGE_ENGINE=1 -DENABLED_LOCAL_INFILE=1 -DWITH_READLINE=1-DWITH_SSL=yes -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci-DEXTRA_CHARSETS=all

# make

# make install

――创建系统数据库及系统表

# cd /usr/local/mysqlb/

# scripts/mysql_install_db --user=mysql--datadir=/data/lib/mysqlb

3.  配置参数文件

经过上面两次编译并创建系统数据库，安装、搭建了两个MySQL实例，接下来分别为其配置参数文件。

――查看参数文件缺省位置

# /usr/local/mysqla/bin/mysql --help|grep '/my.cnf'

/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysqla/my.cnf ~/.my.cnf

# /usr/local/mysqlb/bin/mysql --help|grep '/my.cnf'

/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysqlb/my.cnf ~/.my.cnf

从中可以看出，两个MySQL实例的4个缺省参数文件中有3个是相同的，所以不能采用，否则就无法区分了；当然，多个实例间相同的参数部分可如此配置，只不过不便于管理。

为了使每个MySQL实例拥有一个独立的参数文件，只能采用第3个缺省参数文件，即将参数文件存放到各实例自己的安装目录下（basedir）。另外，还需保证不存在第4个参数文件，因为它的优先级别较高，若存在则会覆盖前面的参数文件设置。

关于参数文件的详细配置此处不做说明了，假设已为两个MySQL实例配置好了各自的参数文件，并分别存放在各自的安装目录下，即/usr/local/mysqla|mysqlb目录下。

4.  启动实例

启动MySQL实例，需要为datadir目录设置正确的权限，否则实例启动失败；这一步缺省应该已经设置，检查一下，若未设置，则通过如下命令设置。

# chown -R mysql.mysql mysql/

――启动两个MySQL实例

# /usr/local/mysqla/bin/mysqld_safe --user=mysql &

# /usr/local/mysqlb/bin/mysqld_safe --user=mysql &

――查看进程

# ps -ef|grep mysql

root      1694 26876  0 13:04 pts/2    00:00:00 more/usr/local/mysqla/support-files/mysql.server

root      2270 13474  0 13:13 pts/1    00:00:00 /bin/sh/usr/local/mysqla/bin/mysqld_safe --user=mysql

mysql     2805  2270  113:13 pts/1    00:00:00 /usr/local/mysqla/bin/mysqld--basedir=/usr/local/mysqla --datadir=/data/lib/mysqla--plugin-dir=/usr/local/mysqla/lib/plugin --user=mysql--log-error=/data/lib/mysqla/mysql.err --pid-file=/data/lib/mysqla/mysql.pid--socket=/data/lib/mysqla/mysql.sock --port=3306

root      2828 13474  0 13:13 pts/1    00:00:00 /bin/sh/usr/local/mysqlb/bin/mysqld_safe --user=mysql

mysql     3361  2828 25 13:13 pts/1    00:00:00 /usr/local/mysqlb/bin/mysqld--basedir=/usr/local/mysqlb --datadir=/data/lib/mysqlb --plugin-dir=/usr/local/mysqlb/lib/plugin--user=mysql --log-error=/data/lib/mysqlb/mysql.err--pid-file=/data/lib/mysqlb/mysql.pid --socket=/data/lib/mysqlb/mysql.sock--port=3307

root      3381 13474  0 13:13 pts/1    00:00:00 grep mysql

可以看出，成功启动了两个MySQL实例（进程）

――若要关闭服务，可执行如下命令

# /usr/local/mysqla/bin/mysqladmin shutdown

# /usr/local/mysqlb/bin/mysqladmin shutdown

5.  修改为服务管理方式

为了方便管理，将其修改为服务管理方式，如下：

――拷贝服务文件

# cp /usr/local/mysqla/support-files/mysql.server/etc/rc.d/init.d/mysqla

# cp /usr/local/mysqlb/support-files/mysql.server/etc/rc.d/init.d/mysqlb

――添加服务

# chkconfig --add mysqla

# chkconfig --list mysqla

mysqla          0:off   1:off  2:on    3:on    4:on   5:on    6:off

# chkconfig --add mysqlb

# chkconfig --list mysqlb

mysqlb          0:off   1:off  2:on    3:on    4:on   5:on    6:off

――启动服务

# service mysqla start

Starting MySQL..                                          [  OK  ]

[root@db ~]# service mysqlb start

Starting MySQL..                                          [  OK  ]

至此，我们通过源码包成功搭建了两个MySQL实例，它们拥有独立的端口、参数文件及数据库。

二． 采用二进制包安装MySQL实现多实例

二进制包其实就是已经过编译的源码包，不能再通过定制参数文件缺省路径的方法来隔离各个实例的参数文件了；但有一点需要说明的是：第3个参数文件的缺省路径指的是basedir（安装目录），可通过它来隔离各实例的参数文件，因此一台服务器也就可搭建多个MySQL实例了。

同样安装两个MySQL实例，步骤如下：

1.  准备工作

――创建mysql系统组及用户

# groupadd -g 497 mysql

# useradd -u 499 -g mysql mysql

――解压安装包

将二进制安装包解压到/usr/local/目录中，并改名为mysql，这是二进制包编译时basedir参数的缺省目录。

# tar zxvf mysql-5.5.17-linux2.6-x86_64.tar.gz -C /usr/local/

# cd /usr/local/

# mv mysql-5.5.17-linux2.6-x86_64 mysql

――拷贝解压后的mysql，生成如下两个文件夹，以用作两个实例的安装目录

# cd /usr/local/

# cp -R mysql mysqla

# cp -R mysql mysqlb

# ll

drwxr-xr-x 12 root root     4096 Sep 27 14:33 mysql

drwxr-xr-x 12 root root     4096 Sep 27 14:36 mysqla

drwxr-xr-x 12 root root     4096 Sep 27 14:37 mysqlb

备注：此处保留mysql这个目录，是为了配置硬连接，以便在任何目录下，都可以访问mysql客户端程序。

2.  创建系统数据库

为两个MySQL实例创建系统数据库，basedir分别为/usr/loca/mysqla|mysqlb，datadir分别为/data/lib/mysqla|mysqlb，如下：

# cd /usr/local/mysql/

# scripts/mysql_install_db --user=mysql--basedir=/usr/local/mysqla --datadir=/data/lib/mysqla

# scripts/mysql_install_db --user=mysql--basedir=/usr/local/mysqlb --datadir=/data/lib/mysqlb

3.  配置参数文件

为了使每个MySQL实例拥有独立的参数文件，将其存放在各自的安装目录下（basedir），且需保证缺省路径下不存在参数文件，以防读取错误。

假设参数文件已配置好，端口分别为3306、3307，basedir分别为/usr/local/mysqla、/usr/local/mysqlb，datadir分别为/data/lib/mysqla、/data/lib/mysqlb。

4.  启动实例

这种情况下，可在启动实例时通过--defaults-file为其指定一个参数文件，如下：

# /usr/local/mysql/bin/mysqld_safe--defaults-file=/usr/local/mysqla/my.cnf &

# /usr/local/mysql/bin/mysqld_safe--defaults-file=/usr/local/mysqlb/my.cnf &

关闭实例时，同样需要指定其参数文件，否则会失败。

# /usr/local/mysql/bin/mysqladmin--defaults-file=/usr/local/mysqla/my.cnf shutdown

# /usr/local/mysql/bin/mysqladmin--defaults-file=/usr/local/mysqlb/my.cnf shutdown

5.  修改为服务管理方式

二进制包没有采用缺省参数文件，所以修改为服务管理方式稍微有点复杂，需要修改服务启动文件中的basedir、datadir参数，如下：

――拷贝服务文件

# cp /usr/local/mysql/support-files/mysql.server/etc/rc.d/init.d/mysqla

# cp /usr/local/mysql/support-files/mysql.server/etc/rc.d/init.d/mysqlb

――添加服务

# chkconfig --add mysqla

# chkconfig --add mysqlb

――修改服务启动文件

编辑两个服务启动文件，修改basedir、datadir参为合适的路径，如下：

# vi /etc/rc.d/init.d/mysqla

basedir=/usr/local/mysqla

datadir=/data/lib/mysqla

# vi /etc/rc.d/init.d/mysqlb

basedir=/usr/local/mysqlb

datadir=/data/lib/mysqlb

备注：通过显式设置basedir，以使每个MySQL实例拥有独立的参数文件。

――启动服务

# service mysqla start

Starting MySQL..                                          [  OK  ]

# service mysqlb start

Starting MySQL..                                          [  OK  ]

# service mysqlb status

MySQL running (30326)                                      [  OK  ]

# service mysqla status

MySQL running (29755)                                      [  OK  ]

修改为服务管理方式后，启动、关闭服务方便了，但在本地登录数据库，还需指定socket文件，因为缺省的/tmp/mysql.sock并不存在，如：

# mysql --socket=/data/lib/mysqla/mysql.sock

# mysql --socket=/data/lib/mysqlb/mysql.sock

三． 采用RPM包安装MySQL实现多实例

RPM包的文件布局是固定的，无法修改，因此一台服务器通常只能安装一个MySQL实例。但我们知道，无论何种方式，MySQL实例都是通过调用mysqld_safe命令来启动的，而mysqld_safe命令可通过--defaults-file参数来显式指定一个参数文件，因此同样可在一台服务器安装、运行多个MySQL实例，只不过要对服务启动文件做一些调整，下面就来尝试一下这个方法。

1.  安装MySQL

――安装server及client包

# rpm -ivh MySQL-server-5.5.17-1.rhel5.x86_64.rpm

# rpm -ivh MySQL-client-5.5.17-1.rhel5.x86_64.rpm

――启动服务

# service mysql start

Starting MySQL..                                          [  OK  ]

――查看basedir和datadir参数值

# mysql

mysql> show variables like 'basedir';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| basedir       |/usr  |

+---------------+-------+

1 row in set (0.00 sec)

 

mysql> show variables like 'datadir';

+---------------+-----------------+

| Variable_name | Value          |

+---------------+-----------------+

| datadir       |/var/lib/mysql/ |

+---------------+-----------------+

1 row in set (0.01 sec)

从中可以看出basedir缺省为/usr，datadir缺省为/var/lib/mysql。

2.  创建系统数据库

缺省情况下，RPM包已创建了系统数据库，位于/var/lib/mysql目录下，拷贝这个目录到/data/lib目录下，并分别命名为mysqla、mysqlb，以此来创建两个实例的系统数据库。

# service mysql stop

Shutting down MySQL.                                       [  OK  ]

# cp -r /var/lib/mysql/ /data/lib/mysqla

# cp -r /var/lib/mysql/ /data/lib/mysqlb

当然，也可通过mysql_install_db工具来创建，它位于/usr/bin目录下，如下：

# /usr/bin/mysql_install_db --user=mysql--datadir=/data/lib/mysqla

# /usr/bin/mysql_install_db --user=mysql--datadir=/data/lib/mysqlb

3.  配置参数文件

配置两个参数文件，分别命名为mya.cnf、myb.cnf，存放到/etc/目录下；端口号分别为3306、3307，datadir路径分别为/data/lib/mysqla、/data/lib/mysqlb。

4.  启动实例

同二进制包多实例一样，此时启动、关闭实例时只能通过--defaults-file指定参数文件，如下：

# /usr/bin/mysqld_safe --defaults-file=/etc/mya.cnf &

# /usr/bin/mysqld_safe --defaults-file=/etc/myb.cnf &

# /usr/bin/mysqladmin --defaults-file=/etc/mya.cnf shutdown

# /usr/bin/mysqladmin --defaults-file=/etc/myb.cnf shutdown

5.  修改服务管理方式

为了方便，还是要将其修改为服务管理方式，执行如下修改操作。

――创建服务启动文件

根据服务启动文件mysql，创建两个对应服务启动文件mysqla、mysqlb

# cd /etc/rc.d/init.d/

# cp mysql mysqla

# cp mysql mysqlb

――添加服务

# chkconfig --add mysqla

# chkconfig --add mysqlb

――删除原来的服务启动文件及服务（避免误启动）

# chkconfig --del mysql

# rm /etc/rc.d/init.d/mysql

――修改服务启动文件

编辑服务启动文件，进行如下调整，主要有以下几个地方：

# vi /etc/rc.d/init.d/mysqla

l  1：添加一个变量，设置为参数文件

my_cnf=/etc/mya.cnf

l  2：修改extra_args参数值，目的是让parse_server_arguments函数从中解析出datadir等参数值

（红色字体为添加部分）

extra_args=""

if test -r "$basedir/my.cnf"

then

  extra_args="-e$basedir/my.cnf"

else

  if test -r"$datadir/my.cnf"

  then

    extra_args="-e$datadir/my.cnf"

  else

   extra_args="-e $my_cnf"

  fi

fi

l  3：在mysqld_safe命令中使用--defaults-file以便显式指定一个参数文件

$bindir/mysqld_safe --defaults-file=$my_cnf--datadir="$datadir" --pid-file="$mysqld_pid_file_path"$other_args >/dev/null 2>&1 &

注意：一定要将--defaults-file参数紧跟mysqld_safe，中间不能有其他参数，否则启动失败。

l  4：修改lock_file_path，若未修改，虽还不清楚有啥影响，但以防万一，还是修改之

lock_file_path="$lockdir/mysqla"

l  5：找到'status'，将获取mysqld进程的命令pidof替换为ps

#mysqld_pid=`pidof $libexecdir/mysqld`

mysqld_pid=`ps --user=mysql -f|grep "$libexecdir/mysqld--defaults-file=$my_cnf"|awk '{print $2}'`

说明：pidof命令用于获取$libexecdir/mysqld的进程号，进而判断MySQL实例的状态，由于路径及名称都是固定的，所以无法区分每个实例对应的进程，导致Status混乱；用ps命令的过滤功能可实现这个需求，针对每个实例找到其对应的进程。

按照同样的方法，为第二个MySQL实例调整服务启动文件。

――启动服务

[root@db mysqla]# service mysqla start

Starting MySQL..                                          [  OK  ]

[root@db mysqla]# service mysqlb start

Starting MySQL..                                          [  OK  ]

至此，我们通过RPM包在一台服务器上安装了两个MySQL实例。

四． 采用mysqld_multi工具实现多实例

mysqld_multi是MySQL自带的一个统一管理多个MySQL实例的服务脚本，它会从MySQL的参数文件中搜索[mysqld#]段序列（#即GNR，可以是任意的正整数），作为自己的参数，以此来区分不同的段，进而控制特定的mysqld进程（MySQL实例）的启动、停止或者获取报告信息。

下面我们采用来了解一下它是如何管理多个MySQL实例的。

1.  配置参数文件

mysqld_multi与安装包类型无关，所以无论是源码包、二进制包，还是RPM包都自带了这个程序脚本，假设MySQL已成功安装。

鉴于mysqld_multi的工作机制，要求MySQL的参数文件中配置[mysqld_multi]项，及多个[mysqld#]段，每个[mysqld]段对应一个MySQL实例，使用不同的端口及unix socket监听文件。

本例配置两个[mysqld#]段，如下：

# vi /etc/my.cnf

[mysqld_multi]

mysqld     =/usr/bin/mysqld_safe

mysqladmin = /usr/bin/mysqladmin

 

[mysqld1]

port            = 3306

server-id       = 3306

 

datadir         =/data/lib/mysqla

socket          =/data/lib/mysqla/mysql.sock

pid-file        =/data/lib/mysqla/mysql.pid

 

character-set-server   =utf8

default_storage_engine = InnoDB

 

log-bin          =/data/lib/mysqla/mysql-bin

binlog_format    = row

sync-binlog      = 1

 

slow-query-log      = on

slow-query-log-file = /data/lib/mysqla/mysql-slow.log

log_error        =/data/lib/mysqla/mysql.err

 

max_connections         =2000

back_log                =50

 

skip-external-locking

skip-name-resolve

#skip-networking

 

key_buffer_size         =256M

max_allowed_packet      =1M

table_open_cache        = 2000

sort_buffer_size        =1M

read_buffer_size        =1M

read_rnd_buffer_size    =4M

myisam_sort_buffer_size = 64M

thread_cache_size       =8

query_cache_size        =16M

thread_concurrency      =8

 

innodb_data_home_dir     = /data/lib/mysqla

innodb_data_file_path    = ibdata1:10M:autoextend

innodb_log_group_home_dir = /data/lib/mysqla

innodb_buffer_pool_size        = 256M

innodb_additional_mem_pool_size = 20M

innodb_log_file_size           = 64M

innodb_log_buffer_size         = 8M

innodb_flush_log_at_trx_commit = 1

innodb_lock_wait_timeout       = 50

 

[mysql2]

port            = 3307

server-id       = 3307

 

datadir         =/data/lib/mysqlb

socket          =/data/lib/mysqlb/mysql.sock

pid-file        =/data/lib/mysqlb/mysql.pid

 

character-set-server   =utf8

default_storage_engine = InnoDB

 

log-bin          =/data/lib/mysqlb/mysql-bin

binlog_format    = row

sync-binlog      = 1

 

slow-query-log      = on

slow-query-log-file = /data/lib/mysqlb/mysql-slow.log

log_error        =/data/lib/mysqlb/mysql.err

 

max_connections         =2000

back_log                =50

 

skip-external-locking

skip-name-resolve

#skip-networking

 

key_buffer_size         =256M

max_allowed_packet      =1M

table_open_cache        = 2000

sort_buffer_size        =1M

read_buffer_size        =1M

read_rnd_buffer_size    =4M

myisam_sort_buffer_size = 64M

thread_cache_size       =8

query_cache_size        =16M

thread_concurrency      =8

 

innodb_data_home_dir     = /data/lib/mysqlb

innodb_data_file_path    = ibdata1:10M:autoextend

innodb_log_group_home_dir = /data/lib/mysqlb

innodb_buffer_pool_size        = 256M

innodb_additional_mem_pool_size = 20M

innodb_log_file_size           = 64M

innodb_log_buffer_size         = 8M

innodb_flush_log_at_trx_commit = 1

innodb_lock_wait_timeout       = 50

[mysqld_multi]项指定了mysqld_safe及mysqladmin命令工具，mysqld_multi也就是通过这两个工具来管理MySQL实例的。两个[mysqld#]段，分别对应两个MySQL实例，端口分别为3306、3307，datadir分别为/data/lib/mysqla、/data/lib/mysqlb。

2.  使用mysqld_multi

――通过mysqld_multi启动、关闭MySQL实例

# /usr/bin/mysqld_multi start|stop 1

# /usr/bin/mysqld_multi start|stop 2

备注：此处的1、2分别表示参数配置文件中的[mysqld1]、[mysqld2]，对应着两个MySQL实例；若此处不输入要操作的段序列，则缺省启动、关闭所有[mysqld]段。

为了方便，将其修改为服务管理方式，具体操作如下：

――拷贝服务文件

将mysqld_multi.server文件拷贝到/etc/rc.d/init.d/目录下，并改个简单点的名字

（mysqld_multi.server文件的具体位置因安装方式不同而不同）

# cp /usr/share/mysql/mysqld_multi.server/etc/rc.d/init.d/mysqld_m

――添加服务

# chkconfig --add mysqld_m

# chkconfig --list mysqld_m

mysqld_m        0:off  1:off   2:on    3:on   4:on    5:on    6:off

――修改服务文件

修改/etc/rc.d/init.d/mysqld_m文件中的两个参数，如下：

# vi /etc/rc.d/init.d/mysqld_m

basedir=/usr/

bindir=/usr/bin

备注：这两个参数的缺省值分别为/usr/local/mysql、/urs/local/mysql/bin，不同类型的安装包，实际值可能不同，根据自己的实际情况，修改这两个参数的值，以便启动脚本能够找到/usr/bin/mysqld_multi文件，并赋予该文件操作权限。

――启动、关闭实例

修改完成后，我们就可以通过mysqld_multi启动mysqld实例了，其语法如下：

#mysqld_m [options][GNR[,GNR]...]

# service mysqld_m start|stop 1

# service mysqld_m start|stop 2

3.  不足之处

mysqld_multi统一管理多个MySQL实例，虽有方便之处，但在使用过程中，也发现了一些不足之处，一是选项过于简单，只有start、stop、report，而没有status项，所以无法查看服务的状态；二是在启动、关闭服务时，只是执行start、stop这么一个操作，而不跟踪操作结果，所以无法确保操作成功执行