mysql多实例

[size=large]MySQL多实例部署
By 赵佳佳, on 五月 26th, 2011

常规安装（共同步骤）：

tar -xzvf mysql_template5.1.53.tar.gz（源码编译后的tar包，或从mysql网站下载）
mv mysql_template5.1.53 /usr/local/mysql

groupadd mysql
useradd mysql -g mysql -M -s /sbin/nologin
mkdir /var/mysql_data
mkdir /var/mysql_log
mkdir /var/mysql_bak
mkdir /var/mysql_slow

chown -R mysql:mysql /var/mysql*

chown mysql:mysql -R /usr/local/mysql

##初始化数据
/usr/local/mysql/bin/mysql_install_db –user=mysql –basedir=/usr/local/mysql –datadir=/var/mysql_data –skip-name-resolve

##启动脚本
cp /usr/local/mysql/share/mysql/mysql.server /etc/init.d/mysql
chkconfig –add mysql
chkconfig –level 35 mysql on

##配置脚本
rm -rf /etc/my.cnf
nano /etc/my.cnf

##配置脚本
rm -rf /etc/my.cnf
vim /etc/my.cnf

/etc/init.d/mysql start

##登陆
mysql

一、mysqld_multi部署

#关掉mysql服务

service mysql stop

#创建目录3307（3308、3309以此类推）

cp -R /var/mysql_data3307 /var/mysql_data #同时初始化数据，也可以使用mysql_install_db脚本
mkdir /var/mysql_log3307
mkdir /var/mysql_bak3307
mkdir /var/mysql_slow3307

#授权

chown -R mysql:mysql /var/mysql*

#把/usr/local/mysql/bin加入PATH

##启动关闭多实例
/usr/local/mysql/bin/mysqld_multi –defaults-extra-file=/etc/my_multi.cnf start /stop 1-4

##登陆
/usr/local/mysql/bin/mysql –socket=/tmp/mysql.sock –port=3306
/usr/local/mysql/bin/mysql –socket=/tmp/mysql3307.sock –port=3307
/usr/local/mysql/bin/mysql –socket=/tmp/mysql3308.sock –port=3308
/usr/local/mysql/bin/mysql –socket=/tmp/mysql3309.sock –port=3309

#根据server_id迅速判断对应实例

select @@server_id;

##附上my_multi.cnf部分参数配置：

[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user=root

[mysqld1]
socket = /tmp/mysql.sock
port = 3306
pid-file =/var/mysql_data/hostname.pid
datadir = /var/mysql_data

skip-locking
#language = /usr/local/mysql/share/mysql/english
user = mysql
server_id =172193306
log-bin=/var/mysql_log/mysql-bin
relay-log=/var/mysql_log/relay-bin
[mysqld2]
port =3307
socket = /tmp/mysql3307.sock
pid-file =/var/mysql_data3307/hostname.pid3307
datadir = /var/mysql_data3307
skip-name-resolve
skip-locking
user = mysql
server_id =172193307
log-bin=/var/mysql_log3307/mysql-bin
relay-log=/var/mysql_log3307/relay-bin[/size]