文档目录
一. MySQL安装
MySQL安装可参考该博客:
二. Linux下配置MySQL多实例
注意: 该部分需要在(一)的基础上进行
我的多实例目录规划如下:
路径作用 | 绝对位置 |
---|---|
mysql安装路径 | /opt/modules/mysql |
各实例数据目录 | /opt/modules/mysql/data/{port} |
各实例日志文件 | /opt/modules/mysql/logs/{port}/error.log |
运行时产生的pid文件 | /opt/modules/mysql/run/{port}/mysqld.pid |
socket文件 | /var/lib/mysql/{port}/mysql.sock |
各实例配置文件 | /etc/mysql/{port}/my.cnf |
2.1 创建相关目录并赋权
# 这里以3308端口号的实例为例,如配置其它端口号,只需修改命令里的端口号即可
mkdir -p /opt/modules/mysql/data/3308
mkdir -p /opt/modules/mysql/run/3308
mkdir -p /opt/modules/mysql/logs/3308
touch /opt/modules/mysql/logs/3308/error.log
mkdir -p /var/lib/mysql/3308
chown -R mysql:mysql /var/lib/mysql
chown -R mysql:mysql /opt/modules/mysql
chmod -R 755 /opt/modules/mysql
mkdir -p /etc/mysql/3308
2.2 修改配置文件
vi /etc/mysql/3308/my.cnf
#3308配置,注意:不同实例只需将这里的3308替换即可
[mysqld]
datadir=/opt/modules/mysql/data/3308
port=3308
socket=/var/lib/mysql/3308/mysql.sock
basedir=/opt/modules/mysql
server-id=3308
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
max_connections=400
innodb_file_per_table=64
lower_case_table_names=1
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
[mysqld_safe]
log-error=/opt/modules/mysql/logs/3308/error.log
pid-file=/opt/modules/mysql/run/3308/mysqld.pid
[client]
port=3308
socket=/var/lib/mysql/3308/mysql.sock
2.3 初始化并启动指定实例
# 初始化 这里需要记住命令输出的最后一串字符串,其为MySQL登录的初始密码 在MySQL安装目录下的bin目录执行
./mysqld --initialize --user=mysql --datadir=/opt/modules/mysql/data/3308 --basedir=/opt/modules/mysql
# 启动MySQL 这里需要指定启动配置文件
nohup /bin/sh /opt/modules/mysql/bin/mysqld_safe --defaults-file=/etc/mysql/3308/my.cnf --user=mysql &
2.4 修改密码并开放远程连接
# 因为这里我们不是以默认的3306端口启动,所以需要指定socket文件
# 输入的密码为上文mysql初始化命令输出的最后一串字符串
mysql -uroot -p -S /var/lib/mysql/3308/mysql.sock
# 设置密码
mysql> set password for root@localhost = password('yourpassword');
# 开放远程连接
mysql> use mysql;
mysql> update user set user.Host='%' where user.User='root';
mysql> flush privileges;
2.5 远程连接测试
使用SQLyog测试远程连接,新建连接
输入连接信息,和端口号
点击测试连接:
可以看到,连接正常,Linux下配置mysql多实例成功
2.6 多实例启停脚本
当我们同一台服务器部署多个MySQL实例时,多个MySQL实例的维护会变得非常复杂
基于上述我们对MySQL目录的规划,我们可以为这些实例提供一个统一的启停脚本来维护
(通过传入端口号作为参数来区分不同的MySQL实例)
vim /opt/modules/mysql/bin/mysql_instance.sh
#!/bin/bash
# MySQL多实例统一启停脚本 参数1: 执行命令(start|stop|restart) 参数2:端口号(通过端口号来启停对应实例)
#参数3: mysql用户名,参数4: mysql密码
port=$2
mysql_user=$3
mysql_pwd=$4
cmd_path="/opt/modules/mysql/bin"
mysql_sock="/var/lib/mysql/${port}/mysql.sock"
#startup function
function_start_mysql(){
if [ ! -e "$mysql_sock" ];then
printf "Starting Mysql...\n"
/bin/sh ${cmd_path}/mysqld_safe --defaults-file=/etc/mysql/${port}/my.cnf 2>&1 >/dev/null & #启动命令
else
printf "Mysql is running...\n"
exit
fi
}
#stop function
function_stop_mysql(){
if [ ! -e "$mysql_sock" ];then
printf "Mysql is stopped...\n"
exit
else
printf "Stoping Mysql...\n"
${cmd_path}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S ${mysql_sock} shutdown #停止命令
fi
}
#restart function
function_restart_mysql(){
printf "Restart Mysql...\n"
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:${cmd_path}/mysql.sh {start|stop|restart}\n"
esac
脚本执行示例:
# 启动3308端口号的mysql实例
sh /opt/modules/mysql/bin/mysql_instance.sh start 3308 root 123456
# 重启
sh /opt/modules/mysql/bin/mysql_instance.sh restart 3308 root 123456
#