MySQL 多实例配置指南

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

相关文章

已有 4 条评论
  1. 沐风

    中秋快乐哈~

    沐风
    1. Rinald

      @沐风

      中秋愉快!

      Rinald
  2. even

    能不能设置不指定socket自动登陆呀

    even
    1. Rinald

      @even

      这个没测试过  你可以测试下

      Rinald

此处评论已关闭