一台机子上运行多个mysql实例

安装说明：

一份mysql程序文件，一个配置文件，运行多个mysql实例(每个实例有各自不同的数据目录)。为了测试的便利，本实验只用两个示例来练习。

mysql程序安装目录：/usr/local/mysql_5.6.38
mysql实例的数据目录：/data/mysql/data1和/data/mysql/data2，分别对应3306和3307端口实例的数据目录

安装过程

实例化数据目录

通过运行 $MYSQL_HOME/scripts/mysql_install_db 脚本实例化mysql数据目录

[root@host3 mysql_5.6.38]# scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql_5.6.38/ --datadir=/data/mysql/data1/
[root@host3 mysql_5.6.38]# scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql_5.6.38/ --datadir=/data/mysql/data2/

修改配置文件

该配置在客户端连接3306实例的时候不需要加socket参数，可是在连接3307实例的时候一定要加socket参数，否则登录不上

配置信息如下所示

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

[client]
port=3306
socket=/data/mysql/data1/mysql.sock
default-character-set=utf8

[mysqld_multi]
mysqld=/usr/local/mysql_5.6.38/bin/mysqld_safe
mysqladmin=/usr/local/mysql_5.6.38/bin/mysqladmin
#user=mysql
#password=123456

[mysqld1]
port=3306
socket=/data/mysql/data1/mysql.sock
key_buffer_size=16M
max_allowed_packet=8M
basedir=/usr/local/mysql_5.6.38
datadir=/data/mysql/data1
#binary logging
log-bin=mysql-bin
binlog-format=Row
character-set-server=utf8
default-storage-engine=INNODB
log-error=/data/mysql/data1/mysqld.log
pid-file=/data/mysql/data1/mysqld.pid

[mysqld2]
port=3307
socket=/data/mysql/data2/mysql.sock
key_buffer_size=16M
max_allowed_packet=8M
basedir=/usr/local/mysql_5.6.38
datadir=/data/mysql/data2
#binary logging
log-bin=mysql-bin
binlog-format=Row
character-set-server=utf8
default-storage-engine=INNODB
log-error=/data/mysql/data2/mysqld.log
pid-file=/data/mysql/data2/mysqld.pid
[mysqldump]
quick

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

运行mysqld_safe（第一次安装的时候如此运行）

[root@host3 mysql_5.6.38]# ./bin/mysqld_safe --user=mysql --basedir=/usr/local/mysql_5.6.38/ --datadir=/data/mysql/data1/ --port=3306 --socket=/data/mysql/data1/mysql.sock &
[root@host3 mysql_5.6.38]# ./bin/mysqld_safe --user=mysql --basedir=/usr/local/mysql_5.6.38/ --datadir=/data/mysql/data2/ --port=3307 --socket=/data/mysql/data2/mysql.sock &

停止mysql服务

[root@host3 mysql_5.6.38]# ./support-files/mysqld_multi.server stop 1,2

或者

[root@host3 mysql_5.6.38]# ./bin/mysqladmin -u root -S /data/mysql/data1/mysql.sock -P 3306 -p123456 shutdown
[root@host3 mysql_5.6.38]# ./bin/mysqladmin -u root -S /data/mysql/data2/mysql.sock -P 3307 -p123456 shutdown

后续启动服务

执行mysqld_multi.server脚本之前请根据安装的实际路径修改 basedir和bindir的值

[root@host3 mysql_5.6.38]# ./support-files/mysqld_multi.server start 1,2

客户端登录

[root@host3 mysql_5.6.38]# ./bin/mysql --user=root --socket=/data/mysql/data1/mysql.sock --port=3306 -p

参考文档

管理多个mysql实例

运行mysql多实例

用客户端程序连接mysql多实例