一台数据库服务器为什么运行多个实例？
有一台MySQL数据库服务器硬件利用率往往在30%左右，那剩余的70%岂不是浪费了，这时就可以考虑运行多个MySQL服务，你可以做主从架构，读写分离，数据库隔离，容纳更多的并发链接，充分利用硬件资源，让服务器性能发挥最大化。
1、编译安装MySQL5.5
|
1
2
3
4
5
6
7
8
9
10
11
12
|
# yum install -y cmake gcc gcc-c++ make cmake ncurses ncurses-devel
# groupadd mysql
# useradd -g mysql -s /sbin/nologin mysql
# tar zxvf mysql-5.5.31.tar.gz
# cd mysql-5.5.31
# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql5.5 -DSYSCONFDIR=/usr/local/mysql5.5/etc -DMYSQL_DATADIR=/usr/local/mysql5.5/data
# make && make install
# mkdir /usr/local/mysql5.5/etc
# cp support-files/my-medium.cnf /usr/local/mysql5.5/etc/my.cnf
# chown -R mysql.mysql /usr/local/mysql5.5/
# echo ‘PATH=$PATH:/usr/local/mysql5.5/bin' >> /etc/profile
# source /etc/profile
|
3、初始化数据库实例
|
1
2
3
4
|
# mkdir /usr/local/mysql5.5/data3306 #创建数据库数据目录
# mkdir /usr/local/mysql5.5/data3307
# /usr/local/mysql5.5/scripts/mysql_install_db --basedir=/usr/local/mysql5.5/ --datadir=/usr/local/mysql5.5/data3306/ --user=mysql
# /usr/local/mysql5.5/scripts/mysql_install_db --basedir=/usr/local/mysql5.5/ --datadir=/usr/local/mysql5.5/data3307/ --user=mysql
|
2、配置多实例信息并使用mysqld_multi工具集中管理
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
# cd /usr/local/mysql5.5/etc/
# vi my.cnf
[mysqld_multi]
mysqld =
/usr/local/mysql5
.5
/bin/mysqld_safe
mysqladmin =
/usr/local/mysql5
.5
/bin/mysqladmin
user = root
#登陆数据库用户
password = 123
#登陆数据库密码，用于关闭数据库，两台数据库密码设置一样
[mysqld1]
user = mysql
port = 3306
socket =
/tmp/mysql3306
.sock
pid-
file
=
/usr/local/mysql5
.5
/data3306/mysql
.pid
basedir =
/usr/local/mysql5
.5
datadir =
/usr/local/mysql5
.5
/data3306
log_error =
/var/log/mysql/3306_error
.log
[mysqld2]
user = mysql
port = 3307
socket =
/tmp/mysql3307
.sock
pid-
file
=
/usr/local/mysql5
.5
/data3307/mysql
.pid
basedir =
/usr/local/mysql5
.5
datadir =
/usr/local/mysql5
.5
/data3307
log_error =
/var/log/mysql/3307_error
.log
|
3、启动实例1,2
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
# 单个实例启动或关闭
# mysqld_multi --defaults-file=/usr/local/mysql5.5/etc/my.cnf start/stop 1
# mysqld_multi --defaults-file=/usr/local/mysql5.5/etc/my.cnf start/stop 2
# 双实例同时启动或关闭
# mysqld_multi --defaults-file=/usr/local/mysql5.5/etc/my.cnf start/stop 1,2
# 查看MySQL是否启动
# mysqld_multi --defaults-file=./my.cnf report 1,2
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
# netstat -antp |grep mysql
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 20896
/mysqld
tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 21499
/mysqld
# 加入开机启动
echo
"/usr/local/mysql5.5/mysqld_multi --defaults-file=/usr/local/mysql5.5/etc/my.cnf start 1,2"
>>
/etc/rc
.
local
|
注：如果不想用mysqld_multi工具，可以独立实例配置文件，自己启动或关闭，如下：
|
1
2
|
# /usr/local/mysql5.5/bin/mysqld_safe --defaults-extra-file=/usr/local/mysql5.5/etc/my3306.cnf --datadir=/usr/local/mysql5.5/data3306/ --user=mysql &
# mysqladmin -uroot -p123 -S/tmp/mysql3306.sock shutdown
|
4、本地登录
|
1
2
3
4
5
|
# 首次登录先设置密码
# mysql -S /tmp/mysql3306.sock -P 3307 -uroot -p #直接回车进入
mysql>
set
password=password(
'123'
);
# mysql -S /tmp/mysql3307.sock -P 3307 -uroot -p
mysql>
set
password=password(
'123'
);
|
5、远程登录（先开启远程访问权限）
|
1
2
3
|
mysql> grant all on *.* to
'root'
@
'%'
identified by
'123'
;
# mysql -h 192.168.1.10 -P 3306 -uroot -p123
# mysql -h 192.168.1.10 -P 3307 -uroot -p123
|
本文转自 李振良OK 51CTO博客，原文链接：http://blog.51cto.com/lizhenliang/1636016，如需转载请自行联系原作者
