mysql 多次使用的结果转临时表 mysql 多实例

MySQL多实例搭建

toc

一、什么是MySQL多实例

MySQL的多实例就是在一台机器上开启多个不同的服务端口，运行多个MySQL服务进程，使用不同的socket来监听这多个不同的端口以此提供服务，这一点和Oracle的多实例类似。这些MySQL的实例共用相同的MySQL但是使用的参数文件是不一样的，相应的数据文件也不同。提供服务的时候从逻辑上看各自独立，各自获取的硬件资源可以灵活设定。

二、MySQL多实例优劣势

有效的利用服务器资源。当单个服务器的资源有剩余的时候可以将多余的资源有效的利用起来，而且还实现了资源的逻辑隔离。

节约经济消耗。例如需要多个数据库来搭建主从，但是又只有一台服务器。

当单个数据库并发很高或计算资源需求很高时。整个实例会消耗大量系统的CPU，IO等资源。这样其他实例的可利用资源就会变少产生问题。无法实现实例资源的绝对隔离。

三、如何部署MySQL多实例

部署的方式有两种：1. 使用mysqld_multi工具，用单独的配置文件实现多实例配置复杂但是管理方便。

2.设置多个配置文件启动，这样启动不同进程实现多实例。原理简单，但是不易管理。

1. 使用mysqld_multi搭建

1.1 环境规划

系统版本

数据库版本

主机名

端口

CentOS Linux release 7.6.1810 (Core)
mysql-5.7.30-linux-glibc2.12-x86_64
mysqlmulti
3306,3307,3308

MySQL安装包下载：

系统初始化完成，selinux关闭，防火墙关闭，主机名设置等

1.2 创建用户组以及相关目录和授权

[root@mysqlmulti ~]# groupadd mysql
[root@mysqlmulti ~]# useradd -g mysql mysql -d /home/mysql -s /sbin/nologin
[root@mysqlmulti ~]# mkdir -p /data/mysql/mysql_3306/{data,logs,tmp}
[root@mysqlmulti ~]# mkdir -p /data/mysql/mysql_3307/{data,logs,tmp}
[root@mysqlmulti ~]# mkdir -p /data/mysql/mysql_3308/{data,logs,tmp}
[root@mysqlmulti ~]# chown -R mysql.mysql /data/

1.3 安装需要的包

[root@mysqlmulti ~]# yum -y install make gcc-c++ cmake bison-devel ncurses-devel readline-devel libaio-devel perl libaio wget lrzsz vim libnuma* bzip2 xz

1.4 修改系统限制参数

[root@mysqlmulti ~]# vim /etc/security/limits.conf

#追加以下内容

* soft nofile 20480
* hard nofile 65535
* soft nproc 20480
* hard nproc 65535

1.5 修改内核参数

[root@mysqlmulti ~]# sysctl -p
vm.swappiness = 0
net.ipv4.tcp_max_syn_backlog = 65535
net.ipv4.tcp_max_tw_buckets = 8000
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_fin_timeout = 10
net.ipv4.ip_local_port_range = 1024 65535
net.ipv4.tcp_max_syn_backlog = 4096
net.ipv4.tcp_fin_timeout = 30
net.core.somaxconn = 65535
net.core.netdev_max_backlog = 65535
net.core.wmem_default = 87380
net.core.wmem_max = 16777216
net.core.rmem_default = 87380
net.core.rmem_max = 16777216

1.6 解压安装包并建立链接

[root@mysqlmulti ~]# tar -xJf mysql-8.0.15-linux-glibc2.12-x86_64.tar.xz -C /opt/
[root@mysqlmulti ~]# ln -s /opt/mysql-8.0.15-linux-glibc2.12-x86_64 /usr/local/mysql

1.7 改写配置文件

[root@mysqlmulti ~]# cat /etc/my.cnf
[client]
port=3306
socket=/tmp/mysql.sock
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
log = /data/mysql/mysqld_multi.log
[mysqld]
basedir = /usr/local/mysql
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
#3306数据库
[mysqld3306]
mysqld=mysqld
mysqladmin=mysqladmin
datadir=/data/mysql/mysql_3306/data
port=3306
server_id=3306
socket=/tmp/mysql_3306.sock
log-output=file
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /data/mysql/mysql_3306/log/slow.log
log-error = /data/mysql/mysql_3306/log/error.log
binlog_format = mixed
log-bin = /data/mysql/mysql_3306/log/mysql3306_bin
#3307数据库
[mysqld3307]
mysqld=mysqld
mysqladmin=mysqladmin
datadir=/data/mysql/mysql_3307/data
port=3307
server_id=3307
socket=/tmp/mysql_3307.sock
log-output=file
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /data/mysql/mysql_3307/log/slow.log
log-error = /data/mysql/mysql_3307/log/error.log
binlog_format = mixed
log-bin = /data/mysql/mysql_3307/log/mysql3307_bin
#3308数据库
[mysqld3308]
mysqld=mysqld
mysqladmin=mysqladmin
datadir=/data/mysql/mysql_3308/data
port=3308
server_id=3308
socket=/tmp/mysql_3308.sock
log-output=file
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /data/mysql/mysql_3308/log/slow.log
log-error = /data/mysql/mysql_3308/log/error.log
binlog_format = mixed
log-bin = /data/mysql/mysql_3308/log/mysql3308_bin

1.8 进行初始化

[root@mysqlmulti bin]# /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --datadir=/data/mysql/mysql_3306/data --initialize-insecure --user=mysql &
2020-08-06T09:07:57.185748Z 0 [System] [MY-013170] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.15) initializing of server has completed
[root@mysqlmulti bin]# /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --datadir=/data/mysql/mysql_3307/data --initialize-insecure --user=mysql &
2020-08-06T09:15:04.283372Z 0 [System] [MY-013170] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.15) initializing of server has completed
[root@mysqlmulti bin]# /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --datadir=/data/mysql/mysql_3308/data --initialize-insecure --user=mysql &
2020-08-06T09:15:51.033914Z 0 [System] [MY-013170] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.15) initializing of server has completed

1.9 查看数据库是否初始化成功

3306数据库

[root@mysqlmulti data]# pwd
/data/mysql/mysql_3306/data
[root@mysqlmulti data]# ls
auto.cnf client-cert.pem ibdata1 #innodb_temp performance_schema server-cert.pem undo_001
ca-key.pem client-key.pem ib_logfile0 mysql private_key.pem server-key.pem undo_002
ca.pem ib_buffer_pool ib_logfile1 mysql.ibd public_key.pem sys

3307数据库

[root@mysqlmulti data]# pwd
/data/mysql/mysql_3307/data
[root@mysqlmulti data]# ls
auto.cnf client-cert.pem ibdata1 #innodb_temp performance_schema server-cert.pem undo_001
ca-key.pem client-key.pem ib_logfile0 mysql private_key.pem server-key.pem undo_002
ca.pem ib_buffer_pool ib_logfile1 mysql.ibd public_key.pem sys

3308数据库

[root@mysqlmulti data]# pwd
/data/mysql/mysql_3308/data
[root@mysqlmulti data]# ls
auto.cnf client-cert.pem ibdata1 #innodb_temp performance_schema server-cert.pem undo_001
ca-key.pem client-key.pem ib_logfile0 mysql private_key.pem server-key.pem undo_002
ca.pem ib_buffer_pool ib_logfile1 mysql.ibd public_key.pem sys

1.10 设置启动

[root@mysqlmulti ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
1.11 使用mysql_multi进行多实例管理
[root@mysqlmulti ~]# /usr/local/mysql/bin/mysqld_multi start ##启动全部实例
[root@mysqlmulti ~]# /usr/local/mysql/bin/mysqld_multi report ##查看全部实例状态
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is running
MySQL server from group: mysqld3308 is running

四、需要注意的问题

配置文件安装路径不能相同

数据库目录不能相同

启动脚本不能同名

端口不能相同

socket文件的生成路径不能相同