MySQL5.7 多实例
文中使用mysql5.7 版本实现多实例，端口为3306和3307。
1、多实例本质
在一台机器上开启多个不同的mysql服务端口（3306,3307），运行多个mysql服务进程，这些服务进程通过不同的socket监听不同的服务端口来提供各自的服务；
多个实例共用一套mysql安装程序，配置文件可以用同一个（但是最好不同，文中用不同配置文件），启动程序可以用同一个（最好不同，文本用不同启动脚本），数据文件是不同的（必须不同）；
服务器的硬件资源是公用的，逻辑上多实例是各自独立的；
2、多实例作用
有效利用服务器资源；节约服务器资源；
但是多实例肯定会存在资源互相抢占问题，当某个服务实例并发很高或者有慢查询时，会消耗整台服务器更多的内存、CPU等，势必导致其他实例运行也很慢。
3、多实例应用场景
资金比较紧张的公司；
并发访问不是特别大的业务；
4、安装多实例数据库
1.创建相应的目录
mkdir /mysql/{3306,3307}/{data,logs,conf,tmp} -p// 目录解释data # 存放数据logs # 存放mysql日志以及binlog日志conf # 存放mysql配置文件tmp # 存放mysql socket文件2.创建mysql用户，登录方式nologin，不创建家目录
groupadd mysql -g 1002useradd mysql -u 1002 -g 1002 -s /sbin/nologin -M3.安装一些依赖包
yum install ncurses-devel libaio-devel -y4.上传mysql 5.7至本机/application目录
// 将mysql5.7 解压并移动到/application/mysql-5.7.20 目录下mkdir -p /application/mysql-5.7.20 tar xf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz ./mysql-5.7.20mv mysql-5.7.20-linux-glibc2.12-x86_64/* mysql-5.7.20/rm -rf mysql-5.7.20-linux-glibc2.12-x86_64// 软连接ln -s /application/mysql-5.7.20/ /application/mysql// 将 /application/mysql/bin 目录加入PATH环境变量vim /etc/profileexport MYSQL_HOME=/application/mysql/binexport PATH=$PATH:$MYSQL_HOME5.每个实例配置my.cnf文件
3306实例
# vim /mysql/3306/conf/my.cnf[mysqld]basedir=/application/mysqldatadir=/mysql/3306/datatmpdir = /mysql/3306/tmpsocket=/mysql/3306/tmp/mysql.socklog-error=/mysql/3306/logs/mysql.logport=3306server_id=3306max_connections = 1000innodb_buffer_pool_size = 260Mskip_name_resolve# 字符集character-set-server = utf8mb4collation-server = utf8mb4_general_cinit_connect='SET NAMES utf8mb4'# bin-log日志log-bin=/mysql/3306/data/mysql-binbinlog_format=row# 慢日志slow_query_log = 1slow_query_log_file = /mysql/3306/logs/slow.loglog_queries_not_using_indexes = 1log_throttle_queries_not_using_indexes = 5long_query_time = 1[mysql]socket=/mysql/3306/tmp/mysql.sock[mysqladmin]socket=/mysql/3306/tmp/mysql.sock3307实例
# vim /mysql/3307/conf/my.cnf[mysqld]basedir=/application/mysqldatadir=/mysql/3307/datatmpdir = /mysql/3307/tmpsocket=/mysql/3307/tmp/mysql.socklog-error=/mysql/3307/logs/mysql.logport=3307server_id=3307max_connections = 1000innodb_buffer_pool_size = 260Mskip_name_resolve# 字符集character-set-server = utf8mb4collation-server = utf8mb4_general_ci# bin-log日志log-bin=/mysql/3307/data/mysql-binbinlog_format=row# 慢日志slow_query_log = 1slow_query_log_file = /mysql/3307/logs/slow.loglog_queries_not_using_indexes = 1log_throttle_queries_not_using_indexes = 5long_query_time = 1[mysql]socket=/mysql/3307/tmp/mysql.sock[mysqladmin]socket=/mysql/3307/tmp/mysql.sock6.使用mysqld命令初始化mysql数据库文件
// 授权mysql用户管理相应的mysql目录chown -R mysql:mysql /mysql/// 初始化多实例mysqld --defaults-file=/mysql/3306/conf/my.cnf --basedir=/application/mysql --initialize-insecure --user=mysqlmysqld --defaults-file=/mysql/3307/conf/my.cnf --basedir=/application/mysql --initialize-insecure --user=mysql7.通过mysqld_safe命令启动多实例mysql
mysqld_safe --defaults-file=/mysql/3306/conf/my.cnf &mysqld_safe --defaults-file=/mysql/3307/conf/my.cnf &8.检查操作
// 查看进程是否存在ps -ef | grep mysql | grep -v "grep"// 查看端口是否存在ss -lntup | grep -E "3306|3307" // 本地登录（-S 指定不同实例的socket）mysql -uroot -p -S /mysql/3306/tmp/mysql.sock9.多实例启动脚本
#!/bin/bashPORT=3306SOCK_FILE_LOCK=/mysql/${PORT}/tmp/mysql.sock.lock# startfunction func_start(){if [ -f ${SOCK_FILE_LOCK} ];then echo "MySQL ${PORT} has started..." exit 1else echo "MySQL ${PORT} is start..." mysqld_safe --defaults-file=/mysql/3306/conf/my.cnf >/dev/null & result=$? [ ${result} -ne 0 ] && echo "MySQL ${PORT} start failed..." || echo "MySQL ${PORT} start success.."fi}# stopfunction func_stop(){if [ -f ${SOCK_FILE_LOCK} ];then echo "MySQL ${PORT} is stop..." kill $(cat /mysql/${PORT}/tmp/mysql.sock.lock) count=0 while [ $count -ne 60 ] do ((count++)) # echo $count sleep 1 if [ ! -f ${SOCK_FILE_LOCK} ];then echo "MySQL ${PORT} stop success..." break else echo "MySQL ${PORT} is stop..." fi doneelse echo "MySQL ${PORT} don't start..." exit 1fi}case $1 in start) func_start;; stop) func_stop;; restart) func_stop sleep 3 func_start echo "MySQL ${PORT} restart success...";;esac只需要将PORT变量进行替换即可
赞 (0)