Mysql-5.6.x多实例配置

1, 准备数据库实例datadir目录

mkdir -p /opt/data/mysql56/mysql3307
chown mysql:mysql /opt/data/mysql56/mysql3307

初始化实例目录

/opt/data/mysql56/scripts/mysql_install_db \
--defaults-file=/etc/my.cnf \
--user=mysql \
--basedir=/opt/data/mysql56 \
--datadir=/opt/data/mysql56/mysql3307/data

2. 准备配置文件

(1) 修改现有配置文件名my.cnf修为3306.cnf,并复制一个配置文件名为3307.cnf (2) 3306.cnf配置文件修改，端口,sock位置,pid位置,datadir位置等。

[client]
port = 3306
socket = /opt/data/mysql56/mysql.sock
default-character-set = utf8
[mysqld]
skip-name-resolve
user = mysql
basedir = /opt/data/mysql56
datadir = /opt/data/mysql56/data
port = 3306
server_id = 10
socket = /opt/data/mysql56/mysql.sock
pid-file = /opt/data/mysql56/mysql.pid
log-error = /opt/data/mysql56/data/mysql.err
log-bin = /opt/data/mysql56/data/mysql-bin
character-set-server = utf8

(3) 3307.cnf配置文件修改

[client]
port = 3307
socket = /opt/data/mysql56/mysql3307/mysql.sock
default-character-set = utf8
[mysqld]
skip-name-resolve
user = mysql
basedir = /opt/data/mysql56
datadir = /opt/data/mysql56/mysql3307/data
port = 3307
server_id = 10
socket = /opt/data/mysql56/mysql3307/mysql.sock
pid-file = /opt/data/mysql56/mysql3307/mysql.pid
log-error = /opt/data/mysql56/mysql3307/data/mysql.err
log-bin = /opt/data/mysql56/mysql3307/data/mysql-bin
character-set-server = utf8

3. 启动多实例，启动脚本可以加入到开机自启动文件中。

/opt/data/mysql56/bin/mysqld_safe --defaults-file=/etc/3306.cnf 2>&1 >/dev/null &    
/opt/data/mysql56/bin/mysqld_safe --defaults-file=/etc/3307.cnf 2>&1 >/dev/null &

4. 数据库登录

/opt/data/mysql56/bin/mysql -u root -p -P 3306 -S /opt/data/mysql56/mysql.sock
/opt/data/mysql56/bin/mysql -u root -p -P 3307 -S /opt/data/mysql56/mysql3307/mysql.sock

修改3307实例密码:

/opt/data/mysql56/bin/mysqladmin -u root password '123456' -S /opt/data/mysql56/mysql3307/mysql.sock
#设置管理员密码

指定端口号也是可以访问的：

mysql -uroot -p -hlocalhost -P3307

5. 关闭mysql多实例方式

/opt/data/mysql56/bin/mysqladmin -u root -p -P 3306 -S /opt/data/mysql56/mysql.sock shutdown    
/opt/data/mysql56/bin/mysqladmin -u root -p -P 3307 -S /opt/data/mysql56/mysql3307/mysql.sock shutdown