MySQL 执行过程、单实例、多实例配置

758 阅读2分钟

一、 执行过程

  1. SQL 语句执行

  2. 连接层:

    • 连接协议:TCP/IP、socket
    • 提供验证:用户名、密码、ip 和 socket
    • 提供专业连接线程:接收 sql。返回结果
  3. SQL 层

    • 接收上层的 sql 语句
    • 验证语法:SQL_MODE
    • 语义检查:等四种语句的类型判断
    • 解析器、优化器和执行器
    • 根据查询缓存(默认关闭)使用 redis、tair 替代查询缓存功能
    • 提供日志记录
  4. 存储引擎层

二、 单实例配置

2.1 目录规划

/opt/software/mysql   # MySQL的安装目录
/data/mysql/    # 所有的MySQL实例的数据目录、备份目录、日志目录,都在该目录下,各个实例以端口号命名
/etc/my.conf    # MySQL 3306实例的默认配置文件

2.2 下载依赖

如果你的系统曾经安装过mariadb,请先卸载

yum remove -y mariadb* 
yum install -y epel-release
yum update -y
yum install -y cmake gcc-c++ ncurses-devel perl-Data-Dumper boost-doc boost-devel libaio-devel
yum install -y net-tools tree bash-completion lrzsz

2.3 安装

mkdir -p /opt/software && cd /opt/software
pwd 
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.20-linux-glibc2
tar -xvf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
mv mysql-5.7.20-linux-glibc2.12-x86_64 mysql
rm -rf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz && ls

2.4 添加环境变量

vim /etc/profile
export PATH=/opt/software/mysql/bin:$PATH
source /etc/profile

2.5 创建相关目录和MySQL用户

MySQL服务运行在非root用户环境,所以,我们先创建一个mysql用户,然后在创建相关的数据目录:

useradd mysql
mkdir -p /data/mysql/330{6,7,8,9}/{data,logs,backup}
tree /data/mysql/330*
chown -R mysql:mysql /opt/software/mysql/*
chown -R mysql:mysql /data/mysql/*

得到以下结果

image.png

2.6 初始化数据库

保证存放数据的目录是空的,避免不必要的问题

rm -rf /data/mysql/3306/data/*
mysqld --initialize-insecure  --user=mysql --basedir=/opt/software/mysql --datadir=/data/mysql/3306/data 

2.7 编写配置文件

cat  > /etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/opt/software/mysql
datadir=/data/mysql/3306/data
server_id=6
port=3306
socket=/tmp/mysql.sock
log_error=/data/mysql/3306/logs/mysql_error.log  #指定错误日志位置
[mysql]
socket=/tmp/mysql.sock
EOF
cd support-files/
touch /data/mysql/3306/logs/mysql_error.log # 手动创建文件
chown -R /data/mysql/3306/logs/ # 将 root 用户改为 mysql 用户
./mysql.server start

find / -name mysql.sock
mysql -uroot -p
show databases;
select user.host from mysql.user;

2.8 使用systemctl管理MySQL服务

cat  > /etc/systemd/system/mysqld.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://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=/opt/software/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000
EOF
netstat -lnp|grep 330 #显示当前启动的端口
./mysql.server stop
systemctl start mysqld.service
#systemctl start/restart/stop/status/enable/disable mysqld
mysql -uroot -p -S /tmp/mysql.sock

show processlist; #查看当前客户端
kill 4# 删除客户端连接 id

2.9 用户管理

用户权限以及远程用户管理

grant all on *.* to root@'localhost' identified by '123';
grant all on *.* to root@'%' identified by '123';
flush privileges;
select user,host from mysql.user;

三、 多实例配置

3.1 介绍

一台机器开启多个不同的 MySQL 实例,监听不同端口和提供不同的服务

数据隔离,逻辑隔离

通过多个配置文件以及多个启动程序来实现多实例

3.2 关闭单实例

systemctl stop mysqld
mv /etc/my.cnf /etc/my.cnf.bak

3.3 为每个实例创建配置文件

mkdir -p /data/mysql/330{6,7,8,9}/logs/errorlog
chown -R mysql:mysql /data/mysql/*
cat > /data/mysql/3307/my.cnf <<EOF
[mysqld]
basedir=/opt/software/mysql
datadir=/data/mysql/3307/data
socket=/data/mysql/3307/mysql.sock
log_error=/data/mysql/3307/logs/errorlog/mysql.log
port=3307
server_id=7
[client]
socket=/data/mysql/3307/mysql.sock
EOF

cat > /data/mysql/3308/my.cnf <<EOF
[mysqld]
basedir=/opt/software/mysql
datadir=/data/mysql/3308/data
socket=/data/mysql/3308/mysql.sock
log_error=/data/mysql/3308/logs/errorlog/mysql.log
port=3308
server_id=8
[client]
socket=/data/mysql/3308/mysql.sock
EOF

cat > /data/mysql/3309/my.cnf <<EOF
[mysqld]
basedir=/opt/software/mysql
datadir=/data/mysql/3309/data
socket=/data/mysql/3309/mysql.sock
log_error=/data/mysql/3309/logs/errorlog/mysql.log
port=3309
server_id=9
[client]
socket=/data/mysql/3309/mysql.sock
EOF

3.4 查看是否成功

#bash
ls /data/mysql/3307/
# backup  data  logs  my.cnf
cat /data/mysql/3307/my.cnf 
-- [mysqld]
basedir=/opt/software/mysql
datadir=/data/mysql/3307/data
socket=/data/mysql/3307/mysql.sock
log_error=/data/mysql/3307/logs/errorlog/mysql.log
port=3307
server_id=7
-- [client]
socket=/data/mysql/3307/mysql.sock

3.5 授权

chown -R mysql.mysql /data/mysql/*

3.6 初始化

systemctl stop mysqld
mv /etc/my.cnf /etc/my.cnf.bak
mysqld --initialize-insecure  --user=mysql --datadir=/data/mysql/3307/data --basedir=/opt/software/mysql
mysqld --initialize-insecure  --user=mysql --datadir=/data/mysql/3308/data --basedir=/opt/software/mysql
mysqld --initialize-insecure  --user=mysql --datadir=/data/mysql/3309/data --basedir=/opt/software/mysql

3.7 使用 systemctl 管理多实例

注:3306 使用了 mysqld.service 文件,3307 需要重新修改

at  > /etc/systemd/system/mysqld3307.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://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=/opt/software/mysql/bin/mysqld --defaults-file=/data/mysql/3307/my.cnf
LimitNOFILE = 5000
EOF

cat  > /etc/systemd/system/mysqld3308.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://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=/opt/software/mysql/bin/mysqld --defaults-file=/data/mysql/3308/my.cnf
LimitNOFILE = 5000
EOF

cat  > /etc/systemd/system/mysqld3309.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://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=/opt/software/mysql/bin/mysqld --defaults-file=/data/mysql/3309/my.cnf
LimitNOFILE = 5000
EOF

3.8 启动

systemctl start mysqld3307.service 
systemctl start mysqld3308.service 
systemctl start mysqld3309.service 
systemctl start mysqld.service 

3.9 连接管理

连接到指定的实例

方法1:基于 socket 连接(本地连接)

mysql -uroot -p123 -S /temp/mysql.sock -e "select @@server_id"
mysql -uroot -p -S /data/mysql/3307/mysql.sock -e "select @@server_id"
mysql -uroot -p -S /data/mysql/3308/mysql.sock -e "select @@server_id"
mysql -uroot -p -S /data/mysql/3309/mysql.sock -e "select @@server_id"

方法 2:基于 ip 地址(远程连接)

mysql -uroot -p123 -P3306  -e "select @@server_id"
mysql -uroot -p -h 127.0.0.1 -P3307  -e "select @@server_id"

区别

  • 3306
    • 配置文件目录:/etc/my.cnf
    • 启动: systemctl start mysql
    • Mysql.sock: /temp/mysql.sock
  • 3307——3309
    • 配置文件位置:各自的端口号目录中
    • systemctl 启动方式:都以 mysqld3307.service
    • systemctl start mysqld3307
    • Mysql.sock:/data/mysql/3307/mysql.sock