一台服务器上配置多台Mysql实例

一台服务器上配置多台Mysql实例
2011-07-25 14:20

1 先配置一个mysql数据库 并创一个可以关闭数据库的账号grant shutdown on *.* to multi_admin@'localhost' identified by '123456'; 装完第一个就创建 在第三步时 账号也就cp 过去了。 安装第一个数据库参照以前写过的文档
2 stop mysql
3 cp 第一个数据库的目录 到第二个数据库 第三个数据库 第四个数据库 例如：第一个数据库文件放到/usr/loca/mysq/var cp -P var var1 第三个和第四个一样

4 写配置文件 如下 /etc/my.cnf
[mysql]

character-sets-dir=/usr/local/mysql/share/mysql/charsets
default-character-set=utf8
disable-auto-rehash

[mysqladmin]
character-sets-dir=/usr/local/mysql/share/mysql/charsets
default-character-set=utf8

[mysqlcheck]
character-sets-dir=/usr/local/mysql/share/mysql/charsets
default-character-set=utf8

[mysqldump]
character-sets-dir=/usr/local/mysql/share/mysql/charsets
default-character-set=utf8

[mysqlimport]
character-sets-dir=/usr/local/mysql/share/mysql/charsets
default-character-set=utf8

[mysqlshow]
character-sets-dir=/usr/local/mysql/share/mysql/charsets
default-character-set=utf8

[myisamchk]
character-sets-dir=/usr/local/mysql/share/mysql/charsets

[myisampack]
character-sets-dir=/usr/local/mysql/share/mysql/charsets

# use [safe_mysqld] with mysql-3
[mysqld_safe]
err-log = /var/log/mysql/mysql.err


[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user = multi_admin
password = 123456
[mysqld0]
character-set-server = utf8
default-character-set = utf8
port = 3306
socket = /var/run/mysql/s0
#socket = /var/run/mysql/mysql1.sock
pid-file = /var/run/mysql/db-app1.pid
log-error = /var/log/mysql/mysql1err.log
#log = /var/log/mysql/db1-app.log
datadir = /usr/local/mysql/var
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
language = /usr/local/mysql/share/mysql/english
max_user_connections = 5
max_connections = 300
open_files_limit = 16384
skip-show-database
user = mysql
#skip-innodb
tmpdir = /tmp/
server-id = 1
skip-networking


[mysqld1]
character-set-server = utf8
default-character-set = utf8
port = 3307
socket = /var/run/mysql/s1
#socket = /var/run/mysql/mysql2.sock
pid-file = /var/run/mysql/db-app2.pid
datadir = /usr/local/mysql/var1
log-error = /var/log/mysql/mysql2err.log
#log = /var/log/mysql/db2-app.log
user = mysql
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
language = /usr/local/mysql/share/mysql/english
max_user_connections = 5
max_connections = 300
open_files_limit = 16384
skip-show-database
#user = mysql
#skip-innodb
tmpdir = /tmp/
server-id = 1
skip-networking

[mysqld2]
character-set-server = utf8
default-character-set = utf8
port = 3308
socket = /var/run/mysql/s2
#socket = /var/run/mysql/mysql3.sock
pid-file = /var/run/mysql/db-app3.pid
datadir = /usr/local/mysql/var2
log-error = /var/log/mysql/mysql3err.log
#log = /var/log/mysql/db3-app.log
user = mysql
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
language = /usr/local/mysql/share/mysql/english
max_user_connections = 5
max_connections = 300
open_files_limit = 16384
skip-show-database
#skip-innodb
tmpdir = /tmp/
server-id = 1
skip-networking

[mysqld3]
character-set-server = utf8
default-character-set = utf8
port = 3309
socket = /var/run/mysql/s3
#socket = /var/run/mysql/mysql4.sock
pid-file = /var/run/mysql/db-app4.pid
datadir = /usr/local/mysql/var3
log-error = /var/log/mysql/mysql4err.log
#log = /var/log/mysql/db4-app.log
user = mysql
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
language = /usr/local/mysql/share/mysql/english
max_user_connections = 5
max_connections = 300
open_files_limit = 16384
skip-show-database
#skip-innodb
tmpdir = /tmp/
server-id = 1
skip-networking
[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash


[isamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

5 用/usr/local/mysql/bin/mysqld_multi --config-file=/etc/my.cnf start 0-3 启动四个数据库 原来的/usr/local/mysql/share/mysql/mysql.server start 不要用了。 当然也可以只启动其中一个 或者几个 有start 后面的数字决定 0-3 代表着 my.cnf 文件中的 mysqld0-3

如果要停止数据库 同样的方法 把start 修改成 stop 也可以写一个脚本 然后直接运行脚本也行


6 在/usr/local/bin 创建一个 mysql文件 并给予执行的权限 内容如下；
#!/bin/sh

BIN=/usr/bin/mysql
SS=`echo $0 | sed -e "s/.*_//"`

if [ $SS = $0 ]; then
/usr/bin/mysql $*
else
if [ -n $SS ] ; then
/usr/bin/mysql -S /var/run/mysql/$SS $*
else
/usr/bin/mysql $*
fi
fi

注意：mysql的sock文件必须为s0 和 s1 s2 s3 s4 等等

然后 在同级目录下 创建 link 目录的是 可以用mysql_s0 mysql_s1 进入不同的数据库
ln -s mysql mysql_s0
ln -s mysql mysql_s1
后面的省略

7 创建一个 mysql_walk.sh 文件 赋予 执行权限 用途：执行这个文件 能直接查看到s1 s2 s3 s4的所有进程数 内容如下：

#!/bin/sh

for I in 0 1 2 3; do
mysqladmin -S /var/run/mysql/s${I} processlist
done

其中mysqladmin 文件如果不在/usr/sbin 那么就写绝对路径


说明 : 这样配置的mysql php程序连接时 服务器地址写localhost:3306 是不行的 必须写 localhost:/var/run/mysql/s0这样 /var/run/mysql/s0是第一个数据库的端口。