MySQL的多实例配置
有时候在一台物理机中需要多个测试环境,那么就有了搭建数据库的多个示例的需求;
多个实例的意思即:运行多份程序,实例与实例之间没有影响。要注意监听的端口需要不同
搭建环境:centos7.4,编译安装MariaDB-10.2.15版本,软件目录:/app/mysql/
1、创建运行的目录环境
[root@centos7 ~]# mkdir -p /mysqldb/{3306,3307,3308}/{etc,socket,pid,log,data,bin} [root@centos7 ~]# chown -R mysql:mysql /mysqldb/
2、初始化数据库
[root@centos7 ~]# cd /app/mysql/ [root@centos7 mysql]# scripts/mysql_install_db --datadir=/mysqldb/3306/data/ --user=mysql --basedir=/app/mysql/ [root@centos7 mysql]# scripts/mysql_install_db --datadir=/mysqldb/3307/data/ --user=mysql --basedir=/app/mysql/ [root@centos7 mysql]# scripts/mysql_install_db --datadir=/mysqldb/3308/data/ --user=mysql --basedir=/app/mysql/
以上为编译安装,安装目录为/app/mysql/ 需要先进入软件的安装目录然后执行初始化脚本,如果是yum安装的包,则直接运行mysql_install_db
3、提供配置文件并按需要修改
[root@centos7 mysql]# cp support-files/my-huge.cnf /mysqldb/3306/etc/my.cnf [root@centos7 mysql]# cp support-files/my-huge.cnf /mysqldb/3307/etc/my.cnf [root@centos7 mysql]# cp support-files/my-huge.cnf /mysqldb/3308/etc/my.cnf
[root@centos7 mysqldb]# cd /mysqldb/ [root@centos7 mysqldb]# vim 3306/etc/my.cnf [mysqld] port = 3306 datadir = /mysqldb/3306/data socket = /mysqldb/3306/socket/mysql.sock [root@centos7 mysqldb]# vim 3307/etc/my.cnf #按以上配置示例更改 [root@centos7 mysqldb]# vim 3308/etc/my.cnf
4、提供服务启动脚本
[root@centos7 ~]# cat mysqld #脚本示例 #!/bin/bash port=3306 #需要修改为当前实例的端口号 mysql_user="root" mysql_pwd="" cmd_path="/app/mysql/bin" #安装目录下的bin mysql_basedir="/mysqldb" #实例数据库文件所在目录 mysql_sock="${mysql_basedir}/${port}/socket/mysql.sock" function_start_mysql() { if [ ! -e "$mysql_sock" ];then printf "Starting MySQL... " ${cmd_path}/mysqld_safe --defaults-file=${mysql_basedir}/${port}/etc/my.cnf &> /dev/null & else printf "MySQL is running... " exit fi } function_stop_mysql() { if [ ! -e "$mysql_sock" ];then printf "MySQL is stopped... " exit else printf "Stoping MySQL... " ${cmd_path}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S ${mysql_sock} shutdown fi } function_restart_mysql() { printf "Restarting MySQL... " function_stop_mysql sleep 2 function_start_mysql } case $1 in start) function_start_mysql ;; stop) function_stop_mysql ;; restart) function_restart_mysql ;; *) printf "Usage: ${mysql_basedir}/${port}/bin/mysqld {start|stop|restart} " esac
[root@centos7 ~]# cp mysqld /mysqldb/3306/bin/ [root@centos7 ~]# cp mysqld /mysqldb/3307/bin/ [root@centos7 ~]# cp mysqld /mysqldb/3308/bin/ [root@centos7 ~]# vim /mysqldb/3306/bin/mysqld port=3306 [root@centos7 ~]# vim /mysqldb/3307/bin/mysqld port=3307 [root@centos7 ~]# vim /mysqldb/3308/bin/mysqld port=3308
5、修改脚本文件权限,防止密码被别人看到
[root@centos7 ~]# chmod 700 /mysqldb/3306/bin/mysqld [root@centos7 ~]# chmod 700 /mysqldb/3307/bin/mysqld [root@centos7 ~]# chmod 700 /mysqldb/3308/bin/mysqld
6、启动服务
[root@centos7 ~]# service mysqld stop #保证自己原来的服务停止,释放3306端口 [root@centos7 ~]# /mysqldb/3306/bin/mysqld start #启动服务 [root@centos7 ~]# /mysqldb/3307/bin/mysqld start [root@centos7 ~]# /mysqldb/3308/bin/mysqld start [root@centos7 ~]# ss -tnl #如果看到三个实例监听的端口都打开后说明服务启动正常 LISTEN 0 80 :::3306 :::* LISTEN 0 80 :::3307 :::* LISTEN 0 80 :::3308 :::*
7、连接测试
[root@centos7 ~]# mysql -S /mysqldb/3306/socket/mysql.sock #使用-S指定套接字文件 Server version: 10.2.15-MariaDB-log Source distribution MariaDB [(none)]> show variables like '%port'; #查看端口是否是3306 +---------------------+-------+ | Variable_name | Value | +---------------------+-------+ | extra_port | 0 | | large_files_support | ON | | port | 3306 | | report_port | 3306 | +---------------------+-------+ 4 rows in set (0.00 sec) [root@centos7 ~]# mysql -S /mysqldb/3307/socket/mysql.sock #再连接测试一下3307和3308 Server version: 10.2.15-MariaDB-log Source distribution MariaDB [(none)]> show variables like '%port'; +---------------------+-------+ | Variable_name | Value | +---------------------+-------+ | extra_port | 0 | | large_files_support | ON | | port | 3307 | | report_port | 3307 | +---------------------+-------+ 4 rows in set (0.00 sec) [root@centos7 ~]# mysql -S /mysqldb/3308/socket/mysql.sock Server version: 10.2.15-MariaDB-log Source distribution MariaDB [(none)]> show variables like '%port'; +---------------------+-------+ | Variable_name | Value | +---------------------+-------+ | extra_port | 0 | | large_files_support | ON | | port | 3308 | | report_port | 3308 | +---------------------+-------+ 4 rows in set (0.00 sec)
*8、停止实例
[root@centos7 ~]# /mysqldb/3306/bin/mysqld stop