mysql multinom_mysql 配置多实例(mysqld_multi)

上次在一台机器上装了两个mysql，似乎比较繁琐，被上面否决了，这次换成配个多实例的。其实之前配过，没配成功，不过资料找多了，自然就理解大概的原理了。

一、环境：

操作系统：ubuntu 10.10

mysql版本：mysql 5.1.61

二、配置步骤

综合了下面这两份资料：

1.修改配置文件：/etc/apparmor.d/usr.sbin.mysqld

(否则安装不了其它的mysql实例，会出现类似：100304 22:34:18 [Warning] Can't create test file /var/lib/mysql1/PB.lower-test的错误)

默认权限路径如下(以空行分隔的是其它mysql实例的安装位置，主要是添加并修改/var所在的那些行)：

/etc/mysql/*.pem r,

/etc/mysql/conf.d/ r,

/etc/mysql/conf.d/* r,

/etc/mysql/my.cnf r,

/usr/sbin/mysqld mr,

/usr/share/mysql/** r,

/var/log/mysql.log rw,

/var/log/mysql.err rw,

/var/lib/mysql/ r,

/var/lib/mysql/** rwk,

/var/log/mysql/ r,

/var/log/mysql/* rw,

/var/run/mysqld/mysqld.pid w,

/var/run/mysqld/mysqld.sock w,

/var/log/mysql1.log rw,

/var/log/mysql1.err rw,

/var/lib/mysql1/ r,

/var/lib/mysql1/** rwk,

/var/log/mysql1/ r,

/var/log/mysql1/* rw,

/var/run/mysqld/mysqld1.pid w,

/var/run/mysqld/mysqld1.sock w,

/var/log/mysql2.log rw,

/var/log/mysql2.err rw,

/var/lib/mysql2/ r,

/var/lib/mysql2/** rwk,

/var/log/mysql2/ r,

/var/log/mysql2/* rw,

/var/run/mysqld/mysqld2.pid w,

/var/run/mysqld/mysqld2.sock w,

2.重启apparmor：/etc/init.d/apparmor restart

3.安装新的mysql实例(分别安装到/var/lib/mysql1和/var/lib/mysql2下)：

mysql_install_db --datadir=/var/lib/mysql1 --user=mysql

mysql_install_db --datadir=/var/lib/mysql2 --user=mysql

4.配置多个实例：编辑/et/mysql/my.cnf，添加如下配置(pie-file、socket、datadir、log的位置要和之前配的位置一样)

[mysqld_mulit]

mysqld = /usr/bin/mysqld_safe

mysqladmin = /usr/bin/mysqladmin

user = root

password = 11111

[mysqld1]

pid-file = /var/run/mysqld/mysqld1.pid

socket = /var/run/mysqld/mysqld1.sock

port = 3306

datadir = /var/lib/mysql1

user = mysql

log = /var/log/mysql1.log

key_buffer = 16M

max_allowed_packet = 16M

thread_stack = 192K

thread_cache_size = 8

myisam-recover = BACKUP

query_cache_limit = 1M

query_cache_size = 16M

expire_logs_days = 10

max_binlog_size = 100M

[mysqld2]

pid-file = /var/run/mysqld/mysqld2.pid

socket = /var/run/mysqld/mysqld2.sock

port = 3307

datadir = /var/lib/mysql2

user = mysql

log = /var/log/mysql2.log

key_buffer = 16M

max_allowed_packet = 16M

thread_stack = 192K

thread_cache_size = 8

myisam-recover = BACKUP

query_cache_limit = 1M

query_cache_size = 16M

expire_logs_days = 10

max_binlog_size = 100M

5.运行多个实例(mysqld_multi的参数意义可以看这个)：

mysqld_multi start 1,2

*6.查看是否正常运行：

netstat -nltp | grep mysql

7.修改root密码(这里设为123456，需要指定socket)

mysqladmin -uroot password '123456' -S /var/run/mysqld/mysqld1.sock

mysqladmin -uroot password '123456' -S /var/run/mysqld/mysqld2.sock

8.添加关闭mysql的权限

mysql -uroot -pime@mysql -S /var/run/mysqld/mysqld1.sock < "GRANT SHUTDOWN ON *.* TO 'root'@'localhost' IDENTIFIED BY '11111'"

mysql -uroot -pime@mysql -S /var/run/mysqld/mysqld2.sock < "GRANT SHUTDOWN ON *.* TO 'root'@'localhost' IDENTIFIED BY '11111'"

*9.关闭多个实例(用上面指定的账户关闭)

mysqld_multi --user=root --password=11111 stop 1,2