MySQL单服务器多实例的搭建

这里介绍我比较喜欢的两种方法。

第一种方法，多个配置文件（这种方法的优势是配置起来方便，但是管理不太方便）

这里面怎么部署第一个数据库实例就不讲了

新建目录——这个目录是第二个数据库实例的数据目录

mkdir mysql2

chown -R mysql:mysql mysql2

 

对第二个实例进行初始化

/Application/mysql/bin/mysqld --no-defaults --initialize --datadir=/Application/mysql2/data/ --user=mysql --basedir=/Application/mysql

通过初始化的输出信息可以看到密码

新建配置文件

cp /etc/my.cnf  /etc/my2.cnf

修改配置文件

vi /etc/my2.cnf

更改的内容有，datadir,port,socket,错误日志等，，，，，注意basedir不改动

 

启动第二个实例

mysqld_safe --defaults-file=/etc/my2.cnf &

 

监听端口

netstat -anp | grep mysql，这样便能看到第二个实例是否成长启动

 

第二种方法

首先我们编辑配置文件

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld_multi]
mysqld = /home/it_tool/mysql/bin/mysqld_safe
mysqladmin = /home/it_tool/mysql/bin/mysqladmin
user = mysql
pass = ******
log =/home/mysql/mysqld_multi.log

[mysqld]  
user=mysql  
basedir = /home/it_tool/mysql 

[mysqld1]
port        = 3306
socket      = /tmp/mysql3306.sock
basedir = /home/it_tool/mysql
datadir = /home/mysql/3306/
pid-file = /home/mysql/3306/mysqld.pid
skip-external-locking
character-set-server = utf8
secure_file_priv=

explicit_defaults_for_timestamp = true

log_timestamps=SYSTEM
log-bin=/home/mysql/3306/mysql-bin
log-error=/home/mysql/3306/mysqld.log
binlog_format=mixed
server-id   = 1
expire_logs_days = 0

[mysqld2]
port        = 3307
socket      = /tmp/mysql3307.sock
datadir = /home/mysql/3307/
pid-file = /home/mysql/3307/mysqld.pid
skip-external-locking
character-set-server = utf8
secure_file_priv=

explicit_defaults_for_timestamp = true

log_timestamps=SYSTEM
log-bin=/home/mysql/3307/mysql-bin
log-error=/home/mysql/3307/mysqld.log
binlog_format=mixed
server-id   = 2
expire_logs_days = 0

[mysqld3]
port        = 3308
socket      = /tmp/mysql3308.sock
datadir = /home/mysql/3308/
pid-file = /home/mysql/3308/mysqld.pid
skip-external-locking
character-set-server = utf8
secure_file_priv=

explicit_defaults_for_timestamp = true

log_timestamps=SYSTEM
log-bin=/home/mysql/3308/mysql-bin
log-error=/home/mysql/3308/mysqld.log
binlog_format=mixed
server-id   = 3
expire_logs_days = 0

然后进行初始化

mysqld --initialize --datadir=/home/mysql/3306 --user=mysql --basedir=/home/it_tool/mysql

mysqld --initialize --datadir=/home/mysql/3307 --user=mysql --basedir=/home/it_tool/mysql

mysqld --initialize --datadir=/home/mysql/3308 --user=mysql --basedir=/home/it_tool/mysql

然后我们就可以用mysqld_multi来对几个实例进行管理

mysqld_multi start 1,2,3         启动三个实例

mysqld_multi report               查看实例运行情况

    

mysqld_multi stop 1               停止一个或者三个实例(注意，配置文件里如果不配置用户名和密码，可能出现如下错误)   

如果你遇到其他错误也可以去这个日志文件/home/mysql/mysqld_multi.log查询错误原因