MySql multiple servers on linux

MySql官方文件對一臺伺服器上執行多個servers說得不太清楚，經多次嘗試，得到如下安裝過程，記錄備忘。

1.編譯安裝MySql
# groupadd dba
# useradd -g dba mysql
# su - mysql
$ tar zxvf /tmp/mysql-5.1.47.tar.gz
$ cd mysql-5.1.47
$ ./configure --prefix=/mysql47 --with-extra-charsets=latin1,gbk,utf8 --with-plugins=partition,heap,innobase,myisam,myisammrg,csv --enable-assembler --with-tcp-port=3307 --with-unix-socket-path=/tmp/mysql47.sock
$ make -j 2
$ make install

2.配置my.cnf
su - root
# cd /mysql47/share/mysql
# cp my-medium.cnf /etc/my.cnf
# chown -R mysql:dba /etc/my.cnf
# su - mysql
$ vi /etc/my.cnf
註釋或刪除[mysqld]相關內容，替換為如下內容：
[mysqld_multi]
mysqld = /mysql47/bin/mysqld_safe
mysqladmin = /mysql47/bin/mysqladmin
user = multi_admin
password = multipass
[mysqld1]
socket = /tmp/mysql47.sock
port = 3307
pid-file = /mysql47/oel5.pid1
datadir = /mysql47/data
log-error = /mysql47/log/alert.log
log_slow_queries = /mysql47/log/slow.log
user = root
[mysqld2]
socket = /tmp/mysql47.sock2
port = 3308
pid-file = /mysql47/oel5.pid2
datadir = /mysql47/data1
log-error = /mysql47/log/alert1.log
log_slow_queries = /mysql47/log/slow1.log
user = root

3.初始化資料庫
su - mysql
$ cd /mysql47
$ bin/mysql_install_db --basedir=/mysql47 --datadir=/mysql47/data --user=mysql
$ bin/mysqld_safe --datadir=/mysql47/data &
$ bin/mysqladmin -u root -S /tmp/mysql47.sock password 'q47'
$ bin/mysql -S /tmp/mysql47.sock -pq47
mysql> use mysql;
mysql> GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost' IDENTIFIED BY 'multipass';
mysql> exit
$ bin/mysqladmin -S /tmp/mysql47.sock -pq47 shutdown

$ bin/mysql_install_db --basedir=/mysql47 --datadir=/mysql47/data1 --user=mysql    (看清楚了，指定第二個庫的資料檔案路徑)
$ bin/mysqld_safe --datadir=/mysql47/data1 &
$ bin/mysqladmin -u root -S /tmp/mysql47.sock2 password 'q49'  （第二個庫的sock檔案）
$ bin/mysql -S /tmp/mysql47.sock2 -pq49
mysql> use mysql;
mysql> GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost' IDENTIFIED BY 'multipass';
mysql> exit
$ bin/mysqladmin -S /tmp/mysql47.sock2 -pq49 shutdown

4.使用mysqld_multi啟停資料庫
在my.cnf檔案中定義了[mysql1]、[mysql2]，用mysqld_multi命令根據序號便可啟停庫。
$ cd /mysql47
$ bin/mysqld_multi start 1
$ bin/mysqld_multi start 2
$ netstat -ntlp  （此時能檢視到3307、3308埠已開）
$ bin/mysqld_multi stop 1
$ bin/mysqld_multi stop 2
$ netstat -ntlp  （驗證）

5.client登入
$ cd /mysql47
$ bin/mysqld_multi start 1
$ bin/mysqld_multi start 2

登入[mysql1]
$ cd /mysql47
$ bin/mysql -u root -S /tmp/mysql47.sock -pq47

登入[mysql2]
$ cd /mysql47
$ bin/mysql -u root -S /tmp/mysql47.sock2 -pq49