如何运行多个MySQL服务器实例

                                 如何运行多个MySQL服务器实例

    某日因项目需要在单独的MySQL服务器上再运行一个实例,原来已经有该服务器已经有
一个MySQL实例在运行了.再增加一个实例,通过mysqld_multi.server脚本来实现.
原有的/etc/my.cnf文件配置内容如下

[mysqld]
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
port=3306
innodb-file-per-table
innodb_log_file_size=100000000

更改后内容如下

[mysqld1]
user=mysql
datadir=/usr/local/mysql/data
basedir=/usr/local/mysql
socket=/tmp/mysql.sock
port=3306
innodb-file-per-table
innodb_log_file_size=100000000
mysqld=/usr/local/mysql/bin/mysqld
mysqladmin=/usr/local/mysql/bin/mysqladmin

[mysqld2]
user=mysql
datadir=/usr/local/abc
basedir=/usr/local/mysql
socket=/tmp/mysql1.sock
pid_file=/usr/local/abc/it.pid
port=3307
innodb-file-per-table
innodb_log_file_size=100000000
mysqld=/usr/local/mysql/bin/mysqld
mysqladmin=/usr/local/mysql/bin/mysqladmin

其中有几个变量需要注意socket,basedir,user,mysqld,port,pid-file都需要定义成不同的值.
而且datadir千万不要在相同的目录中，否则会引数据错误.

初始化数据字典

[root@localhost mysql]# ./scripts/mysql_install_db --user=mysql --datadir=/usr/local/abc
Installing MySQL system tables...
OK
Filling help tables...
OK

主要是通过datadir来识别新的实例

开始启动多个实例，原来的实例是1,新加了一个实例1

[root@localhost mysql]# /usr/local/mysql/support-files/mysqld_multi.server start 1

.................

Version: '5.5.33-rel31.1-log'  socket: '/tmp/mysql.sock'  port: 3306  Percona Server with XtraDB (GPL), Release rel31.1, Revision 566

[root@localhost mysql]# /usr/local/mysql/support-files/mysqld_multi.server start 2
........................

Version: '5.5.33-rel31.1'  socket: '/tmp/mysql1.sock'  port: 3307  Percona Server with XtraDB (GPL), Release rel31.1, Revision 566

确认两个实例都已经成功启动

[root@localhost mysql]# /usr/local/mysql/support-files/mysqld_multi.server report 1
WARNING: Log file disabled. Maybe directory or file isn't writable?
mysqld_multi log file version 2.16; run: Fri Mar  7 12:58:12 2014
Reporting MySQL (Percona Server) servers
MySQL (Percona Server) from group: mysqld1 is running

[root@localhost mysql]# /usr/local/mysql/support-files/mysqld_multi.server report 2
WARNING: Log file disabled. Maybe directory or file isn't writable?
mysqld_multi log file version 2.16; run: Fri Mar  7 12:58:14 2014
Reporting MySQL (Percona Server) servers
MySQL (Percona Server) from group: mysqld2 is running

[root@localhost mysql]# netstat -nltp | grep mysqld
tcp        0      0 :::3306                     :::*                        LISTEN      19560/mysqld       
tcp        0      0 :::3307                     :::*                        LISTEN      19605/mysqld