Mysql具有一次安装可以运行多个实例的功能，简单的说，MySQL可以以多实例的方式，实现一台服务器，运行在不同端口不同数据文件的MySQL，它们是相互独立的。每个实例的datadir,pid,port,socket是不同的。本文将通过两种方法阐述MySQL多实例的配置。如果你看到这篇文章，未来往事推荐选择方法一进行实施MySQL多实例配置，便于后期的自动化、高效运维管理。

为什么要这样做？这样做的好处是什么？
1、提高CPU的利用率；
2、其实配置mysql多实例运行就是新建数据库存放的位置和监听的socket端口，这样当client连接时就会经由不同的端口访问新建的数据库。创建多实例可以通过下载源码重新编译安装，这样做的好处是可以加入新数据库需要的特别配置。
…………………………


方法/方案一：
首先我们明确一些约定信息：

系统约定：CentOS 6.3
MySQL程序相关信息：
mysql version：5.5.20
basedir：/usr/local/webserver/mysql5520
datadir：/data/mysql_3306    //实例一
             /data/mysql_3307    //实例二
sysconfdir：/etc/my.cnf

一、基础知识：
如果选择使用该方法，首先我们需要了解下mysqld_multi：
mysqld_multi是管理多个mysqld的服务进程，这些服务进程程序用不同的unix socket或是监听于不同的端口，通过简单的命令，它可以启动，关闭和报告所管理的服务器的状态 。（也可以用此在一台机器上做mysql的主从复制）。

二、mysql5.5.20安装
这里不再赘述，下载源码后

cmake -<option>... && make && make install

三、多实例配置
1、建立配置文件
下面是本例中的配置文件my.cnf：

[client]
#password       = your_password
port            = 3306
socket          = /tmp/mysql/mysqld.sock
# MySQL Server

[mysqld_multi]
mysqld = /usr/local/webserver/mysql5520/bin/mysqld_safe
mysqladmin = /usr/local/webserver/mysql5520/bin/mysqladmin
log  = /var/log/mysqld_multi.log
user = multi_admin
password = fity@2013

[mysqld1]
port            = 3306
basedir         = /usr/local/webserver/mysql5520
datadir         = /data/mysql1
socket          = /tmp/mysql/mysqld3306.sock
#thread_concurrency  = 8
skip-external-locking
key_buffer_size = 512M
max_allowed_packet = 1024M
table_open_cache = 2048
sort_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 64
query_cache_type = 0
#query_cache_size = 32M
query_cache_size = 0M
query_cache_limit = 2M
thread_concurrency = 8
skip-name-resolve
skip-slave-start
wait_timeout = 600
default-storage-engine  = MyISAM
connect_timeout=30
max_user_connections = 200
max_connections = 1000
max_connect_errors = 10000
binlog_format = mixed
max_binlog_size = 128M
binlog_cache_size = 4194304
tmp_table_size = 256M
max_heap_table_size = 64M
#tmpdir = /data/3306mysql/tmp
#slave_load_tmpdir=/tmp
slow-query-log-file = /data/log/mysql/slowquery.log
long_query_time = 4
log-queries-not-using-indexes = true
log-slave-updates
init-connect = 'set names utf8'
log-bin=mysql-bin
server-id   = 1
#set-variable = max_connections=1000
net_buffer_length = 8K
myisam_sort_buffer_size = 8M
back_log = 500
expire_logs_days = 4
lower_case_table_names = 1
join_buffer_size = 4M


[mysqld2]
port            = 3307
basedir         = /usr/local/webserver/mysql5520
datadir         = /data/mysql2
socket          = /tmp/mysql/mysqld3307.sock
#thread_concurrency  = 8
skip-external-locking
key_buffer_size = 512M
max_allowed_packet = 1024M
table_open_cache = 2048
sort_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 64
query_cache_type = 0
#query_cache_size = 32M
query_cache_size = 0M
query_cache_limit = 2M
thread_concurrency = 8
skip-name-resolve
skip-slave-start
wait_timeout = 600
default-storage-engine  = MyISAM
connect_timeout=30
max_user_connections = 200
max_connections = 1000
max_connect_errors = 10000
binlog_format = mixed
max_binlog_size = 128M
binlog_cache_size = 4194304
tmp_table_size = 256M
max_heap_table_size = 64M
log-slave-updates
init-connect = 'set names utf8'
log-bin=mysql-bin
server-id   = 1
net_buffer_length = 8K
myisam_sort_buffer_size = 8M
back_log = 500
expire_logs_days = 4
lower_case_table_names = 1
join_buffer_size = 4M

[mysqldump]
quick
max_allowed_packet = 1024M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout = 600

由上面的配置文件我们可以看到这里我们在本机配置了两个MySQL实例：
MySQL实例一 -- [mysqld1]数据库实例信息：

datadir:/data/mysql1
port:3306
socket:/tmp/mysql/mysqld3306.sock
开启了MySQL慢查询日志记录

MySQL实例二 -- [mysqld2]数据库实例信息:

datadir:/data/mysql2
port:3307
socket:/tmp/mysql/mysqld3307.sock
未开启MySQL慢查询日志记录

根据以上的配置信息，可知我们配置了mysqld1,mysqld2两个mysql实例，每个实例都指定了不同的连接端口（是3306——3307）和数据存储路径（datadir所指定的就是它们各自数据的存储路径）等等一些其他的信息。

2、建立需要的文件夹和初始化数据库
[mysqld1]是一个默认的,在我们安装mysql时已经有了，所以不用管它。接下来我们要配置的是[mysqld2]
（1）建立[mysqld2]实例的数据存储目录

#mkdir /data/mysql2 -p
#chown mysql.mysql /data/mysql2

（2）初始化[mysqld2]实例的数据库：
你可以把默认的mysql数据库复制过来，也可以通过mysql工具重新初始化一个数据库，本例中是重新初始化的[mysqld2]实例数据库：

#/usr/local/webserver/mysql5520/scripts/mysql_install_db --user=mysql --basedir=/usr/local/webserver/mysql5520/ --datadir=/data/mysql2
备注：老版本mysql的mysql_install_db位于安装目录下bin中

3、管理mysql多实例：启动/关闭

mysqld_multi [OPTIONS] {start|stop|report} [GNR,GNR,GNR...]
mysqld_multi [OPTIONS] {start|stop|report} [GNR-GNR,GNR,GNR-GNR,...]

如：

#只启动第一个mysql实例服务，相关文件由my.cnf中[mysqld1]设定
#/usr/local/webserver/mysql5520/bin/mysqld_multi --defaults-extra-file=/etc/my.cnf start 1

#只停止第一个mysql服务，相关文件由my.cnf中[mysqld1]设定
#/usr/local/webserver/mysql5520/bin/mysqld_multi --defaults-extra-file=/etc/my.cnf stop 1  

#启动第1至2个mysql实例服务，本例中的全部mysql实例
#/usr/local/webserver/mysql5520/bin/mysqld_multi --defaults-extra-file=/etc/my.cnf start 1-2

然后，可以看到两个MySQL实例都已经成功的启动了

# netstat -lntp &#124; grep mysql
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      21955/mysqld        
tcp        0      0 0.0.0.0:3307                0.0.0.0:*                   LISTEN      23281/mysqld

开机启动：

#/etc/rc.local
/usr/local/webserver/mysql/bin/mysqld_multi --defaults-extra-file=/etc/my.cnf start 1-2

4、初始化[mysqld2]实例数据库的root密码：

# mysql -u root -P3307 -S /tmp/mysql/mysqld3307.sock
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY '123456';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456';
flush privileges;
quit

5、分别在[mysqld1]实例和[mysqld2]实例中建立多实例管理用户multi_admin

grant shutdown on *.* to 'multi_admin'@'localhost' identified by 'fity@2013';
flush privileges;

这里需要注意的是：这个用户和密码需要在两个实例中都有且赋权，否则无法关闭实例。

6、测试
登录mysql服务器，看数据库是否正确加载，权限是否正确

#mysql -u root -p123456 -P3306 -S /tmp/mysql/mysqld3306.sock  //登录mysql实例1
#mysql -u root -p123456 -P3307 -S /tmp/mysql/mysqld3307.sock  //登录mysql实例2

如果不指定socket路径，可能你会收到服务器抛出的如下错误：

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql/mysqld.sock' (2)

分析：个人认为出现这个错误是，默认会去找mysql.sock这个文件，而因为此物理机器上运行的是多个实例，所以这个文件不存在。
当然你可以查阅本博客这篇文章以了解mysql的连接方式及通信原理：《mysql连接类型与socket通信原理说明》https://www.fity.cn/post/348/

---

方法/方案二：
首先我们明确一些约定信息：

系统约定：CentOS 6.3
MySQL程序相关信息：
mysql version：5.5.20
basedir：/usr/local/webserver/mysql5520
datadir：/data/mysql_3306    //实例一
             /data/mysql_3307    //实例二
sysconfdir：/data/mysql_3306/my.cnf    //实例一
                 /data/mysql_3307/my.cnf    //实例二

一、mysql5.5.20安装：
这里不再赘述，下载源码后建立mysql相关运行用户，然后进行编译安装

cmake -<option>... && make && make install

二、初始化数据库

/usr/local/webserver/mysql5520/bin/mysql_install_db --basedir=/usr/local/webserver/mysql5520 --datadir=/data/mysql_3306 --user=mysql
/usr/local/webserver/mysql5520/bin/mysql_install_db --basedir=/usr/local/webserver/mysql5520 --datadir=/data/mysql_3307 --user=mysql

三、多实例配置
1、创建配置文件
#vi /data/mysql_3306/my.cnf
3306的配置文件如下：

[client]
#password       = your_password
port            = 3306
socket          = /tmp/mysql/mysqld_3306.sock
# MySQL Server

[mysqld]
port            = 3306
basedir         = /usr/local/webserver/mysql5520
datadir         = /data/mysql_3306
socket          = /tmp/mysql/mysqld_3306.sock
#thread_concurrency  = 8
skip-external-locking
key_buffer_size = 512M
max_allowed_packet = 1024M
table_open_cache = 2048
sort_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 64
query_cache_type = 0
#query_cache_size = 32M
query_cache_size = 0M
query_cache_limit = 2M
thread_concurrency = 8
skip-name-resolve
skip-slave-start
wait_timeout = 600
default-storage-engine  = MyISAM
connect_timeout=30
max_user_connections = 200
max_connections = 1000
max_connect_errors = 10000
binlog_format = mixed
max_binlog_size = 128M
binlog_cache_size = 4194304
tmp_table_size = 256M
max_heap_table_size = 64M
#tmpdir = /data/3306mysql/tmp
#slave_load_tmpdir=/tmp
slow-query-log-file = /data/log/mysql/slowquery.log
long_query_time = 4
log-queries-not-using-indexes = true
log-slave-updates
init-connect = 'set names utf8'
log-bin=mysql-bin
server-id   = 1
#set-variable = max_connections=1000
net_buffer_length = 8K
myisam_sort_buffer_size = 8M
back_log = 500
expire_logs_days = 4
lower_case_table_names = 1
join_buffer_size = 4M

[mysqldump]
quick
max_allowed_packet = 1024M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout = 600

#vi /data/mysql_3307/my.cnf
3307的配置文件如下：

[client]
#password       = your_password
port            = 3307
socket          = /tmp/mysql/mysqld_3307.sock
# MySQL Server

[mysqld]
port            = 3307
basedir         = /usr/local/webserver/mysql5520
datadir         = /data/mysql_3307
socket          = /tmp/mysql/mysqld_3307.sock
#thread_concurrency  = 8
skip-external-locking
key_buffer_size = 512M
max_allowed_packet = 1024M
table_open_cache = 2048
sort_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 64
query_cache_type = 0
#query_cache_size = 32M
query_cache_size = 0M
query_cache_limit = 2M
thread_concurrency = 8
skip-name-resolve
skip-slave-start
wait_timeout = 600
default-storage-engine  = MyISAM
connect_timeout=30
max_user_connections = 200
max_connections = 1000
max_connect_errors = 10000
binlog_format = mixed
max_binlog_size = 128M
binlog_cache_size = 4194304
tmp_table_size = 256M
max_heap_table_size = 64M
log-slave-updates
init-connect = 'set names utf8'
log-bin=mysql-bin
server-id   = 1
net_buffer_length = 8K
myisam_sort_buffer_size = 8M
back_log = 500
expire_logs_days = 4
lower_case_table_names = 1
join_buffer_size = 4M

[mysqldump]
quick
max_allowed_packet = 1024M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout = 600

2、创建自动启动文件
#vi /data/mysql_3306/mysqld
3306的启动文件如下：

#!/bin/bash
mysql_port=3306
mysql_username="admin"
mysql_password="password"
function_start_mysql()
{
printf "Starting MySQL...\n"
/bin/sh /usr/local/webserver/mysql5520/bin/mysqld_safe --defaults-file=/data/mysql_${mysql_port}/my.cnf 2>&1 > /dev/null &
}
function_stop_mysql()
{
printf "Stoping MySQL...\n"
/usr/local/webserver/mysql5520/bin/mysqladmin -u ${mysql_username} -p${mysql_password} -S /tmp/mysql/mysqld_${mysql_port}.sock shutdown
}
function_restart_mysql()
{
printf "Restarting MySQL...\n"
function_stop_mysql
function_start_mysql
}
function_kill_mysql()
{
kill -9 $(ps -ef | grep 'bin/mysqld_safe' | grep ${mysql_port} | awk '{printf $2}')
kill -9 $(ps -ef | grep 'libexec/mysqld' | grep ${mysql_port} | awk '{printf $2}')
}
case $1 in
start)
function_start_mysql;;
stop)
function_stop_mysql;;
kill)
function_kill_mysql;;
restart)
function_stop_mysql
function_start_mysql;;
*)
echo "Usage: /data/mysql_${mysql_port}/mysqld {start|stop|restart|kill}";;
esac

#vi /data/mysql_3307/mysqld
3307的启动文件如下：

#!/bin/bash
mysql_port=3307
mysql_username="admin"
mysql_password="password"
function_start_mysql()
{
printf "Starting MySQL...\n"
/bin/sh /usr/local/webserver/mysql5520/bin/mysqld_safe --defaults-file=/data/mysql_${mysql_port}/my.cnf 2>&1 > /dev/null &
}
function_stop_mysql()
{
printf "Stoping MySQL...\n"
/usr/local/webserver/mysql5520/bin/mysqladmin -u ${mysql_username} -p${mysql_password} -S /tmp/mysql/mysqld_${mysql_port}.sock shutdown
}
function_restart_mysql()
{
printf "Restarting MySQL...\n"
function_stop_mysql
function_start_mysql
}
function_kill_mysql()
{
kill -9 $(ps -ef | grep 'bin/mysqld_safe' | grep ${mysql_port} | awk '{printf $2}')
kill -9 $(ps -ef | grep 'libexec/mysqld' | grep ${mysql_port} | awk '{printf $2}')
}
case $1 in
start)
function_start_mysql;;
stop)
function_stop_mysql;;
kill)
function_kill_mysql;;
restart)
function_stop_mysql
function_start_mysql;;
*)
echo "Usage: /data/mysql_${mysql_port}/mysqld {start|stop|restart|kill}";;
esac

3、启动3306、3307的mysql

/data/mysql_3306/mysqld start
/data/mysql_3307/mysqld start

4、更改之前的密码（处于安全考虑，还需要删除系统中没有密码的帐号，这里省略了）：

/usr/local/webserver/mysql5520/bin/mysqladmin -uroot password 'password' -S /tmp/mysql/mysqld_3306.sock
/usr/local/webserver/mysql5520/bin/mysqladmin -uroot password 'password' -S /tmp/mysql/mysqld_3307.sock

5、登录测试并创建关闭mysql的帐号权限，mysqld脚本要用到！

/usr/local/webserver/mysql5520/bin/mysql -uroot -ppassword -S /tmp/mysql/mysqld_3306.sock
GRANT SHUTDOWN ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'password';
flush privileges;
/usr/local/webserver/mysql5520/bin/mysql -uroot -ppassword -S /tmp/mysql/mysqld_3307.sock
GRANT SHUTDOWN ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'password';
flush privileges;

创建了admin帐号以后脚本的stop功能和restart功能就正常了！

6、更改环境变量

#vi /etc/profile 添加下面一行内容
PATH=${PATH}:/usr/local/webserver/mysql5520/bin/
source /etc/profile

7、配置开机启动

#vi /etc/rc.local
/data/mysql_3306/mysqld start
/data/mysql_3307/mysqld start

管理的话，在本地都是采用 -S /tmp/mysql/mysqld_3306.sock，如果在远程可以通过不同的端口连接上去做管理操作。其他的和单击操作没有区别。

如果你习惯使用phpMyAdmin，你可以阅读这篇文章：《phpMyAdmin管理mysql多实例或多个mysql服务器》https://www.fity.cn/post/345.html

相关文章

• mongodb与mysql优缺点比较
• mysql replace
• sql查询过滤手机号码数据 mysql
• MySQL bin-log日志及expire-logs-days参数详解
• SQL去重统计