原创作品，允许转载，转载时请务必以超链接形式标明文章
原始出处 、作者信息和本声明。否则将追究法律责任。
http://suifu.blog.51cto.com/9167728/1850560
无论是迫于预算，亦或者是领导要求，多实例的安装也是DBA必须掌握的技术，他的启停和登录方式和单实例安装数据库略有不同，本文记录下如何完成MySQL5.6多实例部署。
首先我们看一下my.cnf和单实例的区分：
|
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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
|
[root@HE1 scripts]
#
cat
/etc/my
.cnf
[client]
#port = 3306
#socket = /tmp/mysql.sock
#default-character-set = utf8
[mysql]
#default-character-set = utf8
[mysqld3306]
port = 3306
basedir =
/usr/local/mysql
datadir =
/data/mysql_3306
socket =
/tmp/mysql_3306
.sock
slow_query_log_file =
/data/mysql_3306/slow
.log
log-error =
/data/mysql_3306/error
.log
log-bin =
/data/mysql_3306/mysql-bin
sync_binlog = 1
binlog_format = row
transaction_isolation = REPEATABLE-READ
innodb_buffer_pool_size = 100m
[mysqld3308]
port = 3308
basedir =
/usr/local/mysql
datadir =
/data/mysql_3308
socket =
/tmp/mysql_3308
.sock
slow_query_log = 1
slow_query_log_file =
/data/mysql_3308/slow
.log
log-error =
/data/mysql_3308/error
.log
long_query_time = 1
log-bin =
/data/mysql_3308/mysql-bin
sync_binlog = 1
binlog_cache_size = 4M
default-storage-engine = InnoDB
binlog_format = row
transaction_isolation = REPEATABLE-READ
innodb_buffer_pool_size = 100m
[mysqld_multi]
mysqld=
/usr/local/mysql/bin/mysqld_safe
mysqladmin=
/usr/local/mysql/bin/mysqladmin
[mysqldump]
quick
max_allowed_packet = 32M
|
可以看出，多实例的my.cnf实际上就是如上所示，本文为了演示实验环境，innodb_buffer_pool_size仅仅开了100m，真实的生产库中多实例部署该参数要开大些，两个实例该参数的值达到内存的50%-80%都可以。
下面开始初始化我们的数据库
首先创建我们的数据目录
|
1
2
3
|
[root@HE1 ~]
#mkdir -p /data/mysql_3306
[root@HE1 ~]
#mkdir -p /data/mysql_3308
[root@HE1 ~]
#echo "export PATH=$PATH:/usr/local/mysql/bin:/usr/local/mysql/lib" >>/etc/profile
|
进入到mysql的scripts文件夹下对数据库进行初始化，这里我们对3306端口数据库进行初始化
|
1
|
[root@HE1 scripts]
#./mysql_install_db --basedir=/usr/local/mysql --datadir=/data/mysql_3306 --defaults-file=/etc/my.cnf --user=mysql
|
这里我们对3308端口数据库进行初始化
|
1
2
|
[root@HE1
scripts]
# ./mysql_install_db --basedir=/usr/local/mysql --datadir=/data/mysql_3308 --defaults-file=/etc/my.cnf --user=mysql
|
初始化完成后，我们便可以启停数据库了，和单实例不同，多实例采用mysqld_multi来启停数据库
[root@HE1 bin]# ./mysqld_multi --defaults-file=/etc/my.cnf --user=root --password=MANAGER start 3306,3308
可以利用mysqld_multi的report命令来检测多实例的运行状况
|
1
2
3
4
5
|
[root@HE1 bin]
#
.
/mysqld_multi
report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3308 is running
|
登录方式和单实例大体相同，不过由于多实例的存在，我们需要指定不同的端口号
|
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
|
[root@HE1 bin]# mysql -uroot -p -P3306 -h 192.168.1.48
Enter
password
:
Welcome
to
the MySQL monitor. Commands
end
with
;
or
\g.
Your MySQL
connection
id
is
6 Server version: 5.6.16-log MySQL Community Server (GPL)
Copyright (c) 2000,
2014, 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> show databases;
+
--------------------+
|
Database
|
+
--------------------+
| information_schema
|
| 3306db |
| mysql |
| performance_schema
|
| test |
+
--------------------+
5
rows
in
set
(0.00 sec)
|
当然，利用socket文件登录也是可以的
|
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
|
[root@HE1 bin]#mysql -uroot -p -S /data/mysql_3306/mysql_3306.sock
Enter
password
:
Welcome
to
the MySQL monitor. Commands
end
with
;
or
\g.
Your MySQL
connection
id
is
7 Server version: 5.6.16-log MySQL Community Server (GPL)
Copyright (c) 2000,
2014, 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> show databases;
+
--------------------+
|
Database
|
+
--------------------+
| information_schema
|
| 3306db |
| mysql |
| performance_schema
|
| test |
+
--------------------+
5
rows
in
set
(0.00 sec)
|
这里是登录3308端口数据库
|
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
32
33
34
|
[root@HE1 bin]#mysql -uroot -p -P3308 -h 192.168.1.48
Enter
password
:
Welcome
to
the MySQL monitor. Commands
end
with
;
or
\g.
Your MySQL
connection
id
is
8 Server version: 5.6.16-log MySQL Community Server (GPL)
Copyright (c) 2000,
2014, 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.
Type
'help;'
or
'\h'
for
help. Type
'\c'
to
clear the
current
input statement.
mysql> show databases;
+
--------------------+
|
Database
|
+
--------------------+
| information_schema
|
| 3308db |
| mysql |
| performance_schema
|
| test |
+
--------------------+
5
rows
in
set
(0.00sec)
mysql> quit
Bye
|
利用3308端口的socket文件登录数据库
|
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
|
[root@HE1 bin]#mysql -uroot -p -S /data/mysql_3308/mysql_3308.sock
Enter
password
:
Welcome
to
the MySQL monitor. Commands
end
with
;
or
\g.
Your MySQL
connection
id
is
9 Server version: 5.6.16-log MySQL Community Server (GPL)
Copyright (c) 2000,
2014, 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> show databases;
+
--------------------+
|
Database
|
+
--------------------+
| information_schema
|
| 3308db |
| mysql |
| performance_schema
|
| test |
+
--------------------+
5
rows
in
set
(0.00sec)
|
至此，MySQL5.6多实例部署完成。
本文出自 “岁伏” 博客，请务必保留此出处http://suifu.blog.51cto.com/9167728/1850560
1214
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
