MySQL进行多实例配置再生产环境中非常常见,比如一些高配置数据库服务器,就会跑多个MySQL实例,借助多实例绑定的方式提高服务器的整体资源利用率。另外在MySQL5.7以上版本还不支持多源复制时,当你有多个主时,也可以使用一台服务器开多个MySQL实例做从。
MySQL多实例配置一般有两种方式:
第一种:基于mysqld_multi来实现。mysqld_multi用于管理多个mysqld的服务进程,这些mysqld服务进程程序可以用不同的socket或是监听于不同的端口,同时将数据文件分布到不同的磁盘以分散IO。mysqld_multi提供简单的命令用于启动,关闭和报告所管理的服务器的状态。从而减少生产环境的维护成本,方便后续的迁移和清理等工作。
第二种:使用每一个实例一个配置文件的方式,个人更倾向于使用这种方式。
本文使用第二种方式进行演示MySQL多实例配置
1)解压安装MySQL
$ tar xvf mysql-5.6.27-linux-glibc2.5-x86_64.tar.gz -C /usr/local/
$ cd /usr/local/
$ ln -sv mysql-5.6.27-linux-glibc2.5-x86_64 mysql
$ cd
1
2
3
4
$tarxvfmysql-5.6.27-linux-glibc2.5-x86_64.tar.gz-C/usr/local/
$cd/usr/local/
$ln-svmysql-5.6.27-linux-glibc2.5-x86_64mysql
$cd
2)创建用户调整权限
$ groupadd -g 3306 mysql
$ useradd -u 3306 -g mysql -s /sbin/nologin -M mysql
$ chown -R mysql.mysql /usr/local/mysql
1
2
3
$groupadd-g3306mysql
$useradd-u3306-gmysql-s/sbin/nologin-Mmysql
$chown-Rmysql.mysql/usr/local/mysql
3)创建多实例相关目录
$ mkdir -pv /data/mysql_3306
$ mkdir -pv /data/mysql_3307
$ mkdir -pv /data/mysql_3306/{data,log}
$ mkdir -pv /data/mysql_3307/{data,log}
1
2
3
4
$mkdir-pv/data/mysql_3306
$mkdir-pv/data/mysql_3307
$mkdir-pv/data/mysql_3306/{data,log}
$mkdir-pv/data/mysql_3307/{data,log}
$ chown -R mysql.mysql /data
1
$chown-Rmysql.mysql/data
4)初始化多个实例
$ /usr/local/mysql/scripts/mysql_install_db --datadir=/data/mysql_3306/data --user=mysql
$ /usr/local/mysql/scripts/mysql_install_db --datadir=/data/mysql_3307/data --user=mysql
1
2
$/usr/local/mysql/scripts/mysql_install_db--datadir=/data/mysql_3306/data--user=mysql
$/usr/local/mysql/scripts/mysql_install_db--datadir=/data/mysql_3307/data--user=mysql
查看各实例初始化文件
$ ls /data/mysql_3306/data/
auto.cnf ibdata1 ib_logfile1 mysql performance_schema test ib_logfile0
$ ls /data/mysql_3307/data/
auto.cnf ibdata1 ib_logfile1 mysql performance_schema test ib_logfile0
1
2
3
4
5
$ls/data/mysql_3306/data/
auto.cnfibdata1ib_logfile1mysqlperformance_schematestib_logfile0
$ls/data/mysql_3307/data/
auto.cnfibdata1ib_logfile1mysqlperformance_schematestib_logfile0
5)输出执行文件
$ vim /etc/profile.d/mysql.sh
export PATH=$PATH:/usr/local/mysql/bin
$ source /etc/profile.d/mysql.sh
1
2
3
$vim/etc/profile.d/mysql.sh
exportPATH=$PATH:/usr/local/mysql/bin
$source/etc/profile.d/mysql.sh
6)添加服务器脚本
$ cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
$ chmod +x /etc/init.d/mysqld
$ chkconfig --add mysqld
$ chkconfig mysqld on
1
2
3
4
$cp/usr/local/mysql/support-files/mysql.server/etc/init.d/mysqld
$chmod+x/etc/init.d/mysqld
$chkconfig--addmysqld
$chkconfigmysqldon
7)添加配置文件
$ /etc/my3306.cnf
[client]
user=root
port=3306
socket=/tmp/mysql.sock
[mysqld]
user=mysql
basedir = /usr/local/mysql
datadir=/data/mysql_3306/data
pid-file=/data/mysql_3306/data/mysql_3306.pid
port=3306
server_id=3306
socket=/tmp/mysql_3306.sock
character_set_server = utf8
skip_name_resolve = 1
max_connections = 800
max_connect_errors = 1000
max_allowed_packet = 16777216
log-error = /data/mysql_3306/log/error.log
binlog_format = mixed
expire_logs_days = 30
log-bin = /data/mysql_3306/log/mysql-bin.log
log-bin-index = /data/mysql_3306/log/mysql-bin.index
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
[client]
user=root
port=3306
socket=/tmp/mysql.sock
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql_3306/data
pid-file=/data/mysql_3306/data/mysql_3306.pid
port=3306
server_id=3306
socket=/tmp/mysql_3306.sock
character_set_server=utf8
skip_name_resolve=1
max_connections=800
max_connect_errors=1000
max_allowed_packet=16777216
log-error=/data/mysql_3306/log/error.log
binlog_format=mixed
expire_logs_days=30
log-bin=/data/mysql_3306/log/mysql-bin.log
log-bin-index=/data/mysql_3306/log/mysql-bin.index
$ cat /etc/my3307.cnf
[client]
root=root
port=3307
socket=/tmp/mysql.sock
[mysqld]
user=mysql
basedir = /usr/local/mysql
datadir=/data/mysql_3307/data
pid-file=/data/mysql_3307/data/mysql_3307.pid
port=3307
server_id=3307
socket=/tmp/mysql_3307.sock
character_set_server = utf8
skip_name_resolve = 1
max_connections = 800
max_connect_errors = 1000
max_allowed_packet = 16777216
log-error = /data/mysql_3307/log/error.log
binlog_format = mixed
expire_logs_days = 30
log-bin = /data/mysql_3307/log/mysql-bin.log
log-bin-index = /data/mysql_3307/log/mysql-bin.index
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
[client]
root=root
port=3307
socket=/tmp/mysql.sock
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql_3307/data
pid-file=/data/mysql_3307/data/mysql_3307.pid
port=3307
server_id=3307
socket=/tmp/mysql_3307.sock
character_set_server=utf8
skip_name_resolve=1
max_connections=800
max_connect_errors=1000
max_allowed_packet=16777216
log-error=/data/mysql_3307/log/error.log
binlog_format=mixed
expire_logs_days=30
log-bin=/data/mysql_3307/log/mysql-bin.log
log-bin-index=/data/mysql_3307/log/mysql-bin.index
8)启动MySQL实例
$ mysqld_safe --defaults-file=/etc/my3306.cnf &
$ mysqld_safe --defaults-file=/etc/my3307.cnf &
1
2
$mysqld_safe--defaults-file=/etc/my3306.cnf&
$mysqld_safe--defaults-file=/etc/my3307.cnf&
查看监控端口
$ netstat -anplt | grep mysql
tcp 0 0 :::3306 :::* LISTEN 44281/mysqld
tcp 0 0 :::3307 :::* LISTEN 44725/mysqld
1
2
3
$netstat-anplt|grepmysql
tcp00:::3306:::*LISTEN44281/mysqld
tcp00:::3307:::*LISTEN44725/mysqld
连接各个实例
$ mysql -S /tmp/mysql_3306.sock
mysql>
$ mysql -S /tmp/mysql_3307.sock
mysql>
1
2
3
4
5
$mysql-S/tmp/mysql_3306.sock
mysql>
$mysql-S/tmp/mysql_3307.sock
mysql>
MySQL多实例已经配置完成,在启动过程中,最好使用supervisor等进程管理工具来管理。
如果您觉得本站对你有帮助,那么可以支付宝扫码捐助以帮助本站更好地发展,在此谢过。