MySQL多实例部署
2019-11-27 本文已影响0人
唯爱熊
一.什么是多实例
1）多套后台进程+线程+内存结构
2）多个配置文件
a.多个端口
b.多个socket文件
c.多个日志文件
d.多个server_id
3）多套数据
二.多实例部署
说明：数据库安装请参考链接文档：https://www.jianshu.com/p/22ccea85428e
2.1创建配置文件存放目录
[root@db02 ~]# mkdir /data/{13307,13308,13309} -p
2.2准备不同的配置文件
#编辑13307配置文件
[root@db02 ~]# vim /data/13307/my.cnf
[mysqld]
port = 13307
log-bin = mysql-bin
server_id = 7
datadir = /data/13307/data
basedir = /application/mysql
socket = /data/13307/data/mysql.sock
log_error = /data/13307/data/error.log
pid_file = /data/13307/data/mysql.pid
[client]
socket = /data/13307/data/mysql.sock
#编辑13308配置文件
[root@db02 ~]# vim /data/13308/my.cnf
[mysqld]
port = 13308
log-bin = mysql-bin
server_id = 8
datadir = /data/13308/data
basedir = /application/mysql
socket = /data/13308/data/mysql.sock
log_error = /data/13308/data/error.log
pid_file = /data/13308/data/mysql.pid
[client]
socket = /data/13308/data/mysql.sock
#编辑13309配置文件
[root@db02 ~]# vim /data/13309/my.cnf
[mysqld]
port = 13309
log-bin = mysql-bin
server_id = 9
datadir = /data/13309/data
basedir = /application/mysql
socket = /data/13309/data/mysql.sock
log_error = /data/13309/data/error.log
pid_file = /data/13309/data/mysql.pid
[client]
socket = /data/13309/data/mysql.sock
2.3初始化出不同的datadir
#切换到初始化目录
[root@db02 ~]# cd /application/mysql/scripts/
#初始化13307数据
[root@db02 /application/mysql/scripts]# ./mysql_install_db --user=mysql --defaults-file=/data/13307/my.cnf --basedir=/application/mysql --datadir=/data/13307/data
Installing MySQL system tables...2019-11-27 19:54:11 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-11-27 19:54:11 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.
2019-11-27 19:54:11 0 [Note] /application/mysql/bin/mysqld (mysqld 5.6.40-log) starting as process 26472 ...
OK
Filling help tables...2019-11-27 19:54:14 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-11-27 19:54:14 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.
2019-11-27 19:54:14 0 [Note] /application/mysql/bin/mysqld (mysqld 5.6.40-log) starting as process 26494 ...
OK
出现两个ok 这样才证明初始化成功。
#初始化13308数据
[root@db02 /application/mysql/scripts]# ./mysql_install_db --user=mysql --defaults-file=/data/13308/my.cnf --basedir=/application/mysql --datadir=/data/13308/data
#初始化13309数据
[root@db02 /application/mysql/scripts]# ./mysql_install_db --user=mysql --defaults-file=/data/13309/my.cnf --basedir=/application/mysql --datadir=/data/13309/data
2.5修改目录权限
[root@db02 /application/mysql/scripts]# chown -R mysql.mysql /data/1330*
2.6启动服务
#启动13307
[root@db02 /application/mysql/scripts]# mysqld_safe --defaults-file=/data/13307/my.cnf &
#启动13308
[root@db02 /application/mysql/scripts]# mysqld_safe --defaults-file=/data/13308/my.cnf &
#启动13309
[root@db02 /application/mysql/scripts]# mysqld_safe --defaults-file=/data/13309/my.cnf &
#查看端口确认端口是否已经启动
[root@db02 /application/mysql/scripts]# ss -lntp|grep 133
LISTEN 0 80 :::13307 :::* users:(("mysqld",pid=26890,fd=11))
LISTEN 0 80 :::13308 :::* users:(("mysqld",pid=27068,fd=11))
LISTEN 0 80 :::13309 :::* users:(("mysqld",pid=27235,fd=11))
#关闭实例
[root@db02 /application/mysql/scripts]# mysqladmin -S /data/13307/data/mysql.sock shutdown
[root@db02 /application/mysql/scripts]# mysqladmin -S /data/13308/data/mysql.sock shutdown
[root@db02 /application/mysql/scripts]# mysqladmin -S /data/13309/data/mysql.sock shutdown
2.7本地连接实例
#连接13307
#指定socket连接
root@db02 /application/mysql/scripts]# mysql -S /data/13307/data/mysql.sock
#连接13308
[root@db02 /application/mysql/scripts]# mysql -S /data/13308/data/mysql.sock
#连接13309
[root@db02 /application/mysql/scripts]# mysql -S /data/13309/data/mysql.sock
2.8编写连接脚本
[root@db02 ~]# vim /usr/local/bin/mysql13307
mysql -S /data/13307/data/mysql.sock
[root@db02 ~]# vim /usr/local/bin/mysql13308
mysql -S /data/13308/data/mysql.sock
[root@db02 ~]# vim /usr/local/bin/mysql13309
mysql -S /data/13309/data/mysql.sock
#授予可执行权限
[root@db02 ~]# chmod +x /usr/local/bin/mysql1330*
测试连接
[root@db02 ~]# mysql13307
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.40-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
9.编写启动脚本
[root@db02 ~]# vim /usr/lib/systemd/system/mysql13307.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=https://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/13307/my.cnf
[root@db02 ~]# vim /usr/lib/systemd/system/mysql13308.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=https://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/13308/my.cnf
[root@db02 ~]# vim /usr/lib/systemd/system/mysql13309.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=https://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/13309/my.cnf
测试
说明：默认只能使用一种方式进行实例管理，这里需要按照之前的方式先关闭实例，之后测试。
#关闭实例
[root@db02 ~]# mysqladmin -S /data/13307/data/mysql.sock shutdown
191127 21:55:11 mysqld_safe mysqld from pid file /data/13307/data/mysql.pid ended
[3]+ Done mysqld_safe --defaults-file=/data/13307/my.cnf (wd: /application/mysql/scripts)
(wd now: ~)
[root@db02 ~]# mysqladmin -S /data/13308/data/mysql.sock shutdown
191127 21:55:19 mysqld_safe mysqld from pid file /data/13308/data/mysql.pid ended
[2]+ Done mysqld_safe --defaults-file=/data/13308/my.cnf (wd: /application/mysql/scripts)
(wd now: ~)
[root@db02 ~]# mysqladmin -S /data/13309/data/mysql.sock shutdown
191127 21:55:26 mysqld_safe mysqld from pid file /data/13309/data/mysql.pid ended
[1]+ Done mysqld_safe --defaults-file=/data/13309/my.cnf (wd: /application/mysql/scripts)
(wd now: ~)
#启动实例
[root@db02 ~]# systemctl start mysql13307.service
[root@db02 ~]# systemctl start mysql13308.service
[root@db02 ~]# systemctl start mysql13309.service
#查看端口是否处于监听状态
[root@db02 ~]# ss -lntp|grep 133
LISTEN 0 80 :::13307 :::* users:(("mysqld",pid=12421,fd=11))
LISTEN 0 80 :::13308 :::* users:(("mysqld",pid=12449,fd=11))
LISTEN 0 80 :::13309 :::* users:(("mysqld",pid=12477,fd=11))
至此多实例部署完成。
