一台服务器上配置多台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是第一个数据库的端口。