行到水穷处，坐看云起时
分类： Mysql/postgreSQL
2015-06-11 10:24:12
测试环境：
操作系统 6.4 x86
MySQL 5.5.38
1、什么是MySQL多实例？
MySQL多实例就是在一台机器上开启多个不同的服务端口（如：3306,3307,3308），运行多个MySQL服务进程，通过不同的socket监听不同的服务端口来提供各自的服务。各个实例之间是相互独立的，每个实例的datadir, port, socket, pid都是不同的。
2、MySQL多实例的特点
?有效利用服务器资源，当单个服务器资源有剩余时，可以充分利用剩余的资源提供更多的服务。
?资源互相抢占问题，当某个服务实例服务并发很高时或者开启慢查询时，会消耗更多的内存、CPU、磁盘IO资源，导致服务器上的其他实例提供服务的质量下降。
3、应用场景
?采用了数据伪分布式架构的原因，而项目启动初期又不一定有那多的用户量，为此先一组物理数据库服务器，但部署多个实例，方便后续迁移；
?为规避mysql对SMP架构不支持的缺陷，使用多实例绑定处理器的办法，把不同的数据库分配到不同的实例上提供数据服务；
?一台物理数据库服务器支撑多个数据库的数据服务，为提高mysql复制的从机的恢复效率，采用多实例部署；
?已经为双主复制的mysql数据库服务器架构，想部分重要业务的数据多一份异地机房的热备份，而mysql复制暂不支持多主的复制模式，且不给用户提供服务，为有效控制成本，会考虑异地机房部署一台性能超好的物理服务器，甚至外加磁盘柜的方式，为此也会部署多实例；
?传统游戏行业的MMO/MMORPG，以及Web Game，每一个服都对应一个数据库，而可能要做很多数据查询和数据订正的工作，为减少维护而出错的概率，也可能采用多实例部署的方式，按区的概念分配数据库；
4、约定
1、将所有的安装文件、配置文件、数据目录全部放存/mydata/data目录中，便于今后实现快速迁移、整体备份和快速复制；
2、在一台服务器上配置2个MySQL实例，分别绑定在3306、3307端口。
3、实例均采用my-medium.cnf 配置文件；我们可以根据实际需求定制各个实例的my.cnf配置。
my.cnf配置文件有两种方案：
1.
多个实例共用同一个my.cnf配置文件中，利用[mysqld1]、[mysqld2]、[mysqld*]标签实现不同实例的差异化配置；
2.
每一个实例单独一个my.cnf配置文件
14.04下安装MySQL
《MySQL权威指南(原书第2版)》清晰中文扫描版 PDF
Ubuntu 14.04 LTS 安装 LNMP Nginx\PHP5 (PHP-FPM)\MySQL
Ubuntu 14.04下搭建MySQL主从服务器
Ubuntu 12.04 LTS 构建高可用分布式 MySQL 集群
Ubuntu 12.04下源代码安装MySQL5.6以及Python-MySQLdb
--------------------------------------------------------------------------------
第一种方案：每一个实例单独一个my.cnf配置文件
datadir: /mydata/data/3306
/mydata/data/3307
my.cnf: /mydata/data/3306/my.cnf
/mydata/data/3307/my.cnf
5、安装MySQL（通用二进制方式）
1、创建mysql用户和组
[root@localhost ~]# groupadd -r mysql
[root@localhost ~]# useradd -r -g mysql -s /sbin/nologin mysql
2、目录规划
我们为每个实例单独创建一个目录：3306， 3307
[root@localhost ~]# mkdir -pv /mydata/data/330{6,7}
mkdir: created directory `/mydata/data/3306'
mkdir: created directory `/mydata/data/3307'
[root@localhost ~]# tree /mydata/data/
/mydata/data/
|-- 3306
`-- 3307
3、解压
[root@localhost ~]# tar xf mysql-5.5.38-linux2.6-i686.tar.gz -C /usr/local/src
[root@localhost ~]# cd /usr/local/
[root@localhost local]# ln -sv src/mysql-5.5.38-linux2.6-i686/ mysql
create symbolic link `mysql' to `src/mysql-5.5.38-linux2.6-i686/'
4、提供配置文件，并编辑
[root@localhost local]# cd mysql
[root@localhost mysql]# cp support-files/my-medium.cnf /mydata/data/3306/my.cnf
[root@localhost mysql]# cp support-files/my-medium.cnf /mydata/data/3307/my.cnf
# 这里是实验环境，所以简单配置。请各位看官根据实际需求调整
###3306
# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /tmp/mysql_3306.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql_3306.sock
pid-file = /mydata/data/3306/mysql.pid
user = mysql
basedir = /usr/local/mysql
datadir = /mydata/data/3306
###########################################################
###3307
# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3307
socket = /tmp/mysql_3307.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
port = 3307
socket = /tmp/mysql_3307.sock
pid-file = /mydata/data/3307/mysql.pid
user = mysql
basedir = /usr/local/mysql
datadir = /mydata/data/3307
5、修改数据目录的属主、属组
[root@localhost mysql]# chown -R mysql:mysql /mydata/data/3306
[root@localhost mysql]# chown -R mysql:mysql /mydata/data/3307
6、把mysql/bin目录添加到PATH
[root@localhost mysql]# vi /etc/profile.d/mysql.sh
# 添加
export PATH=$PATH:/usr/local/mysql/bin
[root@localhost mysql]# . /etc/profile.d/mysql.sh
7、初始化
# 初始化 实例1
[root@localhost mysql]# scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/mydata/data/3306 --user=mysql
# 初始化 实例2
[root@localhost mysql]# scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/mydata/data/3307 --user=mysql
8、启动/关闭 实例
这里有一个问题，每个实例如何读取各自的my.cnf配置文件呢？ 我们需要手动指定
?
/usr/local/mysql/bin/mysqld_safe
?
--defaults-file 手动指定配置文件
### 启动实例
[root@localhost mysql]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/mydata/data/3306/my.cnf &>/dev/null &
[1] 1526
[root@localhost mysql]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/mydata/data/3307/my.cnf &>/dev/null &
[2] 1832
[root@localhost mysql]# netstat -tulpn | grep -i mysql
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 1815/mysqld
tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 2121/mysqld
### 关闭实例， 先不要操作哈
[root@localhost ~]# /usr/local/mysql/bin/mysqladmin -uroot -p -S /tmp/mysql_3306.sock shutdown
[root@localhost ~]# /usr/local/mysql/bin/mysqladmin -uroot -p -S /tmp/mysql_3307.sock shutdown
# 注意：这里mysql的root用户并没有设置密码，密码提示直接敲 Enter即可
OK, 我们看到mysqld 监听在3306,3307两个端口上。
这里，我们也可以提供一个服务启动脚本来进行管理，大家去看看support-files/mysql.server这个服务启动脚本吧。
下面提供一个脚本模板：（大家根据需求更改）
#!/bin/bash
. /etc/init.d/functions
PORT=$2
USER=root
PASSWD=
MYSQLBIN='/usr/local/mysql/bin'
SOCKETFILE="/tmp/mysql_${PORT}.sock"
PIDFILE="/mydata/data/${PORT}/mysql.pid"
MYCNF="/mydata/data/${PORT}/my.cnf"
[[ $# -eq 2 ]] || {
echo "Usage: $0 {start|stop|restart|reload} {PORT}"
exit 1
}
mysql_start() {
[[ -e "$SOCKETFILE" ]] && {
action "MySQL port: $PORT IS already running" /bin/false
exit 0
} || {
action "Starting MySQL... please wait" /bin/true
$MYSQLBIN/mysqld_safe --defaults-file=$MYCNF &> /dev/null &
}
[[ "$?" == "0" ]] && {
action "MySQL has been Started" /bin/true
} || {
action "MySQL Started" /bin/false
}
}
mysql_stop() {
[[ ! -e "$SOCKETFILE" ]] && {
action "MySQL port:$PORT was already down" /bin/false
} || {
$MYSQLBIN/mysqladmin -u $USER -p$PASSWD -S $SOCKETFILE shutdown &>/dev/null
}
[[ "$?" == 0 ]] && {
action "MySQL port:$PORT has been Stopped" /bin/true
}
}
case "$1" in
'start')
mysql_start
;;
'stop')
mysql_stop
;;
'restart'|'reload')
mysql_stop
sleep 3
mysql_start
;;
*)
echo "Usage: $0 {start|stop|restart|reload} {PORT}"
esac
9、测试连接登录实例
### 我们需要指定通过哪一个套接字进行连接
# 注意：这里mysql的root用户并没有设置密码，所以可以直接登录
[root@localhost mysql]# /usr/local/mysql/bin/mysql -S /tmp/mysql_3306.sock
[root@localhost mysql]# /usr/local/mysql/bin/mysql -S /tmp/mysql_3307.sock
第二种方案：多个实例共用同一个my.cnf配置文件
datadir: /mydata/data/3306
/mydata/data/3307
my.cnf: /etc/my.cnf
一点基础知识：mysqld_multi
要配置MySQL多实例，首先我们需要了解一下mysqld_multi这个脚本。mysqld_multi是管理多个mysqld的服务进程。这些服务进程用不同的unix socket或是监听于不同的端口，通过简单的命令，它可以启动，关闭和报告所管理的服务器的状态 。
我们看一看官方的说明：
mysqld_multi is designedto manage several mysqld processes that listen for connections on differentUnix socket files and TCP/IP ports. It can start or stop servers, or reporttheir current status.
--如果我们的一个节点上有多个mysql，可以使用mysqld_multi 来管理。
mysqld_multi searchesfor groups named [mysqldN] in my.cnf (or in the file named by the --config-fileoption). N can be any positive integer. This number is referred to in thefollowing discussion as the option group number, or GNR. Group numbersdistinguish option groups from one another and are used as arguments to mysqld_multito specify which servers you want to start, stop, or obtain a status reportfor. Options listed in these groups are the same that you would use in the [mysqld]group used for starting mysqld. (See, for example, Section 2.10.1.2, “Startingand Stopping MySQL Automatically”.) However, when using multiple servers, it isnecessary that each one use its own value for options such as the Unix socketfile and TCP/IP port number. For more informationon which options must beunique per server in a multiple-server environment, see Section 5.6, “RunningMultiple MySQL Instances on One Machine”.
-- mysqld_multi 会在my.cnf 里搜索mysqldN的参数配置。
由于前面的步骤与上述方式相同，所以这里只是简述一下：
### 添加mysql用户和组
[root@localhost ~]# groupadd -r mysql
[root@localhost ~]# useradd -r -g mysql -s /sbin/nologin mysql
### 目录规划
[root@localhost ~]# mkdir -pv /mydata/data/330{6,7}
mkdir: created directory `/mydata'
mkdir: created directory `/mydata/data'
mkdir: created directory `/mydata/data/3306'
mkdir: created directory `/mydata/data/3307'
[root@localhost ~]# chown -R mysql:mysql /mydata/data/3306
[root@localhost ~]# chown -R mysql:mysql /mydata/data/3307
### 解压
[root@localhost ~]# tar xf mysql-5.5.38-linux2.6-i686.tar.gz -C /usr/local/src
### 创建链接
[root@localhost local]# ln -sv src/mysql-5.5.38-linux2.6-i686 mysql
`mysql' -> `src/mysql-5.5.38-linux2.6-i686'
### 把bin添加到PATH
[root@localhost mysql]# vi /etc/profile.d/mysql.sh
export PATH=$PATH:/usr/local/mysql/bin
[root@localhost mysql]# . /etc/profile.d/mysql.sh
提供配置文件：/etc/my.cnf
[root@localhost ~]# cd /usr/local/mysql
[root@localhost mysql]# cp support-files/my-small.cnf /etc/my.cnf
[root@localhost mysql]# vi /etc/my.cnf
# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock
# Here follows entries for some specific programs
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
log = /mydata/data/mysqld_multi.log
user = root
[mysqld3306]
port = 3306
socket = /tmp/mysqld_3306.sock
pid-file = /mydata/data/3306/mysqld.pid
datadir = /mydata/data/3306
basedir = /usr/local/mysql
lc-messages-dir = /usr/local/mysql/share/english
### These support master - master replication
#auto-increment-increment = 4
#auto-increment-offset = 1 # Since it is master 1
# log-bin = /data/mysql/binlogs/bin-log-mysqld1
# log-bin-index = /data/mysql/binlogs/bin-log-mysqld1.index
#binlog-do-db = # Leave this blank if you want to control it on slave
# max_binlog_size = 1024M
[mysqld3307]
port = 3307
socket = /tmp/mysqld_3307.sock
pid-file = /mydata/data/3307/mysqld.pid
datadir = /mydata/data/3307
basedir = /usr/local/mysql
lc-messages-dir = /usr/local/mysql/share/english
### Disable DNS lookups
#skip-name-resolve
### These support master - slave replication
#log-bin = /data/mysql/binlogs/bin-log-mysqld2
#log-bin-index = /data/mysql/binlogs/bin-log-mysqld2.index
#binlog-do-db = # Leave this blank if you want to control it on slave
#max_binlog_size = 1024M
### Relay log settings
#relay-log = /data/mysql/log/relay-log-mysqld2
#relay-log-index = /data/mysql/log/relay-log-mysqld2.index
#relay-log-space-limit = 4G
### Slow query log settings
#log-slow-queries = /data/mysql/log/slow-log-mysqld2
#long_query_time = 2
#log-queries-not-using-indexes
# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
pid-file = /mydata/data/mysqld.pid
datadir = /mydata/data
basedir = /usr/local/mysql
lc-messages-dir = /usr/local/mysql/share/english
skip-external-locking
key_buffer_size = 16K
max_allowed_packet = 1M
table_open_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 128K
### Incrase the max connections
max_connections = 200
### Set expiration time for logs, including binlogs
expire_logs_days = 14
### set the character as utf8
character-set-server = utf8
collation-server = utf8_unicode_ci
#skip-networking
server-id = 1
# Uncomment the following if you want to log updates
#log-bin=mysql-bin
# binary logging format - mixed recommended
#binlog_format=mixed
### set engine
default-storage-engine = INNODB
## enable per table data for innodb to shrink ibdata
innodb_file_per_table = 1
# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /usr/local/mysql/data
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /usr/local/mysql/data
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
[mysqlhotcopy]
interactive-timeout
[mysql.server]
user = mysql
[mysqld_safe]
log-error = /mydata/data/mysqld.log
pid-file = /mydata/data/mysqld.pid
open-files-limit = 8192
初始化实例：
### mysqld3306
[root@localhost mysql]# scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/mydata/data/3306 --user=mysql
### mysqld3307
[root@localhost mysql]# scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/mydata/data/3307 --user=mysql
提供多实例管理脚本：
[root@localhost mysql]# cp support-files/mysqld_multi.server /etc/init.d/
[root@localhost mysql]# vi /etc/init.d/mysqld_multi.server
#!/bin/sh
#
# A simple startup script for mysqld_multi by Tim Smith and Jani Tolonen.
# This script assumes that my.cnf file exists either in /etc/my.cnf or
# /root/.my.cnf and has groups [mysqld_multi] and [mysqldN]. See the
# mysqld_multi documentation for detailed instructions.
#
# This script can be used as /etc/init.d/mysql.server
#
# Comments to support chkconfig on Linux
# chkconfig: 2345 64 36
# description: A very fast and reliable SQL database engine.
#
# Version 1.0
#
basedir=/usr/local/mysql
bindir=/usr/local/mysql/bin
conf = /etc/my.cnf
export PATH=$PATH:$bindir
if test -x $bindir/mysqld_multi
then
mysqld_multi="$bindir/mysqld_multi";
else
echo "Can't execute $bindir/mysqld_multi from dir $basedir";
exit;
fi
case "$1" in
'start' )
"$mysqld_multi" --defaults-extra-file=$conf start $2
;;
'stop' )
"$mysqld_multi" --defaults-extra-file=$conf stop $2
;;
'report' )
"$mysqld_multi" --defaults-extra-file=$conf report $2
;;
'restart' )
"$mysqld_multi" --defaults-extra-file=$conf stop $2
"$mysqld_multi" --defaults-extra-file=$conf start $2
;;
*)
echo "Usage: $0 {start|stop|report|restart}" >&2
;;
esac
管理实例：
-- mysqld_multi 会在my.cnf 里搜索mysqldN的参数配置。
同时启动mysqld3306, mysqld3307
[root@localhost mysql]# /etc/init.d/mysqld_multi.server start 3306,3307
### 我们看到两个实例已经启动成功
[root@localhost mysql]# netstat -tulpn | grep -i mysql
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 2876/mysqld
tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 2877/mysqld
同时关闭mysqld3306, mysqld3307
### 先不要操作哈， 等测试登录后再关闭
[root@localhost mysql]# /etc/init.d/mysqld_multi.server stop 3306,3307
登录实例：
### 注意，因为这里mysql的root用户并没有设置密码，所以可以直接登录
### 登录mysqld3306
[root@localhost mysql]# mysql -uroot -S /tmp/mysqld_3306.sock
### 登录mysqld3307
[root@localhost mysql]# mysql -uroot -S /tmp/mysqld_3307.sock
### 或者
[root@localhost mysql]# mysql -uroot -h127.0.0.1 -P3306
总结：
1.
这里实验仅配置2个实例，只要你的机器足够强劲，那么可以配置更多的实例。
2.
本实验仅做了初步的设置，mysql的root用户密码， 以及其他匿名用户需要手动去设置或删除
3.
非常困扰的是如何手动指定配置文件，以及Mysql读取配置文件的顺序
4.
MySQL自带了几个不同的配置文件，放置在/opt/mysql/support-files目录下，分别是my-huge.cnf,my-innodb-heavy-4G.cnf,my-large.cnf,my-medium.cnf,my-small.cnf，通过名称我们可以很直观的了解到他们是针对不同的服务器配置的，本文中仅有的一点关于InnoDB的配置，是取自于my-small.cnf的，因为我是在虚拟机上进行的设置；在生产环境中，我们可以通过参考my-huge.cnf或my-innodb-heavy-4G.cnf中的部分参数配置，来对服务器进行优化；
5.
关于MySQL缓存参数的优化，主要用于提升I/O能力。
6.
在单机运行多实例的情况下，切忌使用 mysql -hlocalhost 或 直接忽略-h参数 登陆服务器，这应该算是MySQL的一个bug，就是如果使用localhost或忽略-h参数，而不是指定127.0.0.1的话，即使选择的端口是3307，还是会登陆到3306中去，因此应尽量避免这种混乱的产生，统一用127.0.0.1绑定端口 或 采用socket 来登陆；
问题：第二种方案 my.cnf 配置文件中，为什么还要配置 [mysqld] 呢？？
还希望各位帮忙解答一下。
