一、MySQL多实例简介
MySQL多实例，简单地说，就是在一台服务器上同时开启多个不同的服务端口（如：3306、3307），同时运行多个MySQL服务进程，这些服务进程通过不同的socket监听来自不同的端口来提供服务；
多实例不仅节省物理主机成本，还有效提升了单台物理主机的CPU、磁盘I/O使用效率，而且还可以在多实例之间做部署数据库HA方案。
随着实例数量的增加，就面临统一管理问题，这样我们就需要用MySQL自带的管理程序 mysqld_multi 来进行管理...
二、MySQL启动流程
mysqld_multi #多实例管理程序
mysqld #MySQL最主要的启动方式，里面有很多参数；现在使用多实例就需要用新的mysql_safe 来启动mysql
mysql_safe #实则还是调用mysqld，并且会读取mysqld中的my.cnf配置参数来启动mysql，mysql_safe本身也有很多参数，但是这些参数会优先于my.cnf
my.cnf #mysql的配置文件
my.sock #mysql创建的sock文件，开启、停止、登陆和管理mysql都是通过这个接口文件
三、接下来基于mysql5.5.52版本，安装方法请看MySQL5.5.52编译安装，利用mysqld_multi配置一个多实例
1、停止单实例mysql数据库
|
1
2
|
[root@db01 ~]
# /etc/init.d/mysqld stop
Shutting down MySQL. SUCCESS!
|
2、禁止开机自启动
|
1
2
3
|
[root@db01 ~]
# chkconfig mysqld off
[root@db01 ~]
# chkconfig --list mysqld
mysqld 0:关闭 1:关闭 2:关闭 3:关闭 4:关闭 5:关闭6:关闭
|
3、创建多实例根目录/data/目录
|
1
|
[root@db01 ~]
# mkdir -p /data/{3306,3307}/data
|
4、拷贝mysqld_multi程序文件
|
1
|
[root@db01 ~]
# cp /application/mysql/support-files/mysqld_multi.server /etc/init.d/mysqld_multi.server
|
1）修改mysqld_multi.server路径配置
|
1
2
|
[root@db01 ~]
# sed -i 's#basedir=/usr/local/mysql#basedir=/application/mysql#g' /etc/init.d/mysqld_multi.server
[root@db01 ~]
# sed -i 's#bindir=/usr/local/mysql/bin#bindir=/application/mysql/bin#g' /etc/init.d/mysqld_multi.server
|
2）添加mysqld_multi用到的/etc/mysqld_multi.cnf配置文件
#这个模板文件可以用命令mysqld_multi --example导出来
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
[root@db01 ~]
# vim /etc/mysqld_multi.cnf
[mysqld_multi]
mysqld =
/application/mysql/bin/mysqld_safe
mysqladmin =
/application/mysql/bin/mysqladmin
#user = multi_admin
#password = my_password
[mysqld1]
socket =
/data/3306/mysql
.sock
port = 3306
pid-
file
=
/data/3306/mysql
.pid
datadir =
/data/3306/data
#language = /application/mysql/share/mysql/english
user = mysql
[mysqld2]
socket =
/data/3307/mysql
.sock
port = 3307
pid-
file
=
/data/3307/mysql
.pid
datadir =
/data/3307/data
#language = /application/mysql/share/mysql/english
user = mysql
|
5、配置MySQL多实例的文件权限
通过下面的命令授权mysql用户和用户组管理整个多实例的根目录/data
|
1
|
[root@db01 ~]
# chown -R mysql.mysql /data
|
6、初始化MySQL多实例的数据库文件
（1）初始化MySQL数据库
cd /application/mysql/scripts/ <==注意和MySQL5.1的路径不同，MySQL5.1不在MySQL bin路径下了
3306实例
/application/mysql/scripts/mysql_install_db \
--basedir=/application/mysql \
--datadir=/data/3306/data \
--user=mysql
3307实例
/application/mysql/scripts/mysql_install_db \
--basedir=/application/mysql \
--datadir=/data/3307/data \
--user=mysql
提示：--basedir=/application/mysql为MySQL的安装路径，--datadir为不同的实例数据目录
操作过程：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
[root@db01 ~]
# cd /application/mysql/scripts/
3306实例
[root@db01 scripts]
# /application/mysql/scripts/mysql_install_db \
> --basedir=
/application/mysql
\
> --datadir=
/data/3306/data
\
> --user=mysql
WARNING: The host
'db01'
could not be looked up with resolveip.
This probably means that your libc libraries are not 100 % compatible
with this binary MySQL version. The MySQL daemon, mysqld, should work
normally with the exception that host name resolving will not work.
This means that you should use IP addresses instead of hostnames
when specifying MySQL privileges !
Installing MySQL system tables...
161117 14:14:14 [Note]
/application/mysql/bin/mysqld
(mysqld 5.5.52) starting as process 46676 ...
OK
Filling help tables...
161117 14:14:15 [Note]
/application/mysql/bin/mysqld
(mysqld 5.5.52) starting as process 46683 ...
OK
如果有两个ok，就表示初始化成功
3307实例
[root@db01 scripts]
# /application/mysql/scripts/mysql_install_db \
> --basedir=
/application/mysql
\
> --datadir=
/data/3307/data
\
> --user=mysql
Installing MySQL system tables...
161117 14:18:20 [Note]
/application/mysql/bin/mysqld
(mysqld 5.5.52) starting as process 46733 ...
OK
Filling help tables...
161117 14:18:21 [Note]
/application/mysql/bin/mysqld
(mysqld 5.5.52) starting as process 46740 ...
OK
如果有两个ok，就表示初始化成功
|
7、启动多实例：
1）查看数据库状态
|
1
2
3
4
5
|
mysqld_multi --defaults-extra-
file
=
/etc/mysqld_multi
.cnf report
[root@db01 ~]
# mysqld_multi --defaults-extra-file=/etc/mysqld_multi.cnf report
Reporting MySQL servers
MySQL server from group: mysqld1 is not running
MySQL server from group: mysqld2 is not running
|
2）启动数据库
|
1
2
3
4
5
|
[root@db01 ~]
# mysqld_multi --defaults-extra-file=/etc/mysqld_multi.cnf start 1,2
[root@db01 ~]
# mysqld_multi --defaults-extra-file=/etc/mysqld_multi.cnf report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
|
3）查看端口
|
1
2
3
|
[root@db01 ~]
# ss -nlutp|grep 330
tcp LISTEN 0 50 *:3306 *:*
users
:((
"mysqld"
,47045,10))
tcp LISTEN 0 50 *:3307 *:*
users
:((
"mysqld"
,47041,10))
|
8、停止数据库
|
1
2
3
4
5
|
[root@db01 ~]
# mysqld_multi --defaults-extra-file=/etc/mysqld_multi.cnf stop 1,2
[root@db01 ~]
# mysqld_multi --defaults-extra-file=/etc/mysqld_multi.cnf report
Reporting MySQL servers
MySQL server from group: mysqld1 is not running
MySQL server from group: mysqld2 is not running
|
9、登陆数据库
1）启动数据库
|
1
|
[root@db01 ~]
# mysqld_multi --defaults-extra-file=/etc/mysqld_multi.cnf start 1,2
|
2）登录数据库
方法一：指定端口和主机IP，适合远程连接
|
1
|
mysql -uroot -h127.0.0.1 -P3306
|
方法二：指定socket登陆，适合在本机连接
|
1
|
mysql -S
/data/3307/mysql
.sock
|
操作演示
方法一：指定端口和主机IP，适合远程连接
|
1
2
3
4
5
6
7
8
9
10
11
12
|
[root@db01 ~]
# mysql -uroot -h127.0.0.1 -P3306
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection
id
is 1
Server version: 5.5.52 Source distribution
Copyright (c) 2000, 2016, Oracle and
/or
its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and
/or
its
affiliates. Other names may be trademarks of their respective
owners.
Type
'help;'
or
'\h'
for
help. Type
'\c'
to
clear
the current input statement.
mysql>
|
方法二：指定socket登陆，适合在本机连接
|
1
2
3
4
5
6
7
8
9
10
11
12
|
[root@db01 ~]
# mysql -S /data/3307/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection
id
is 1
Server version: 5.5.52 Source distribution
Copyright (c) 2000, 2016, Oracle and
/or
its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and
/or
its
affiliates. Other names may be trademarks of their respective
owners.
Type
'help;'
or
'\h'
for
help. Type
'\c'
to
clear
the current input statement.
mysql>
|
到这里MySQL多实例就配置完成啦O(∩_∩)O~~！！！
本文转自 炫维 51CTO博客，原文链接:http://blog.51cto.com/xuanwei/1881521
