一、体系结构
MySQL C/S模型
-
Server : mysqld
-
Client :
- socket:仅本地连接使用
- tcp/ip:应用连接使用(远程和本地)
#TCP/IP方式(远程、本地) mysql -uroot -p123456 -h 10.0.0.51 -P3306 #socket方式(仅本地) mysql -uroot -p123456 -S /tmp/mysql.sock
MySQL 实例
mysqld --- > master thread ---> 干活的线程 + 预分配的内存结构
实例=mysqld+master thread + N thread + 内存区域
mysqld程序结构
一条SQL语句的执行过程
-
连接层
(1)提供连接协议:TCP/IP 、SOCKET
(2)提供验证:user、password,IP,SOCKET
(3)提供专用连接线程:接收用户SQL,返回结果
通过以下语句可以查看到连接线程基本情况mysql> show processlist;
-
SQL层
(1)接收上层传送的SQL语句
(2)语法验证模块:验证语句语法,是否满足SQL_MODE
(3)语义检查:判断SQL语句的类型
(4)权限检查:用户对库表有没有权限
(5)解析器:对语句执行前,进行预处理,生成解析树(执行计划),说白了就是生成多种执行方案.
(6)优化器:根据解析器得出的多种执行计划,进行判断,选择最优的执行计划
(7)执行器:根据最优执行计划,执行SQL语句,产生执行结果 -
存储引擎层(简单介绍)
负责根据SQL层执行的结果,从磁盘上拿数据。
将16进制的磁盘数据,交由SQL结构化化成表,
连接层的专用线程返回给用户。
MySQL 逻辑结构
MySQL 物理存储结构
INNODB表底层存储结构引入
-
段:一个非分区表就是一个段(segment)
-
区:大小为1M,默认是连续的64个page(extent)
-
页:MySQL最小的IO单元,默认为16kb(page)
二、MySQL 基础管理
用户
作用:登录mysql,管理数据库逻辑对象
定义方式
用户名@白名单(主机列表)
wordpress@'localhost' #本机
wordpress@'10.0.0.1' #单IP
wordpress@'10.0.0.%' #网段
wordpress@'10.0.0.5%' #50-59
wordpress@'10.0.0.0/255.255.254.0'
wordpress@'db01' #主机名
wordpress@'%' #所有
管理操作
# 创建用户
create user zzgw@'localhost';
# 查看用户信息
select user ,host ,authentication_string from mysql.user;
# 删除用户
drop user zzgw@'localhost';
# 创建用户同时创建密码
create user zzgw@'10.4.7.%' identified by '123';
# 修改密码
alter user zzgw@'10.4.7.%' identified by '123456';
权限
权限的作用:开启用户管理对象的能力
权限的定义
# grant 权限 on 库.表 to '用户名'@'白名单' identified by '密码';
grant all on blog.* to zzgw@'10.4.7.%' identified by '123';
8.0版本以前,grant 可以创建用户并授权。8.0版本以后必须先创建用户在授权
权限的授权范围
*.* #全库级别,一般为管理员
zzgw.* #单库级别,应用或开发
zzgw.t1 #单表级别,很少使用
常用权限
ALL:
SELECT,INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE
ALL : 以上所有权限,一般是普通管理员拥有的
with grant option:超级管理员才具备的,给别的用户授权的功能
权限操作
# 授予增删改查权限
grant SELECT,DELETE,INSERT,UPDATE on app.* to app@'10.4.7.%' identified by '123';
# 查看权限
show grants for zzgw@'10.4.7.%';
# 回收权限
revoke delete on blog.* from zzgw@'10.4.7.%';
本地管理员用户密码忘记
关闭数据
systemctl stop mysqld
将数据库启动到无验证模式
mysqld_safe --skip-grant-tables --skip-networking &
#参数说明
--skip-grant-tables //跳过授权表
--skip-networking //禁用TCP/IP连接方式
# 8.x出于安全考虑,默认拒绝用root账号启动mysql服务(--user=xxx)
mysqld --user=mysql --skip-grant-tables --skip-networking &
修改管理员密码
flush privileges;
alter user root@'localhost' identified by '123456';
quit
重启数据库为正常模式
pkill mysqld
systemctl start mysqld
MySQL连接管理
mysql命令行工具MySQL连接管理用来连接访问mysql数据库
用法格式:
# mysql [options] [db_name]
参数:
-u, --user=name #指定登录用户名
-p, --password #指定登录密码(注意是小写p),一定要放到最后面
-h, --host=name #指定数据库的主机地址
-P, --port=xxx #指定数据库的端口号(大写P)
-S, --socket=name #指定socket文件
-e, --execute=name #使用非交互式操作(在shell终端执行sql语句)
< #导入数据库
初始化配置
控制MySQL的启动,影响到客户端的连接。
初始化配置文件的默认读取顺序
#依次读取,后读取到的会覆盖之前读取到的配置
/etc/my.cnf --> /etc/mysql/my.cnf --> /usr/local/mysql/etc/my.cnf --> ~/.my.cnf
#指定读取配置文件
mysqld_safe --defaults-file=/opt/oldguo.cnf &
初始化配置文件格式
服务端
[mysqld]
[mysqld_safe]
[server]
客户端
[mysql]
[mysqldump]
[mysqladmin]
[client]
初始化配置文件基础模板
[mysqld]
user=mysql
server_id=6
port=3306
basedir=/application/mysql
datadir=/data/mysql/data
log_error=/data/mysql/data/mysql.log
socket=/data/mysql/mysql.sock
[mysql]
socket=/data/mysql/mysql.sock
三、MySQL 多实例
准备数据目录
mkdir -p /data/330{7,8,9}/data
准备配置文件
这里我直接使用的脚本
#!/bin/bash
MyPort='3307 3308 3309'
for i in ${MyPort}
do
echo "[mysqld]
basedir=/application/mysql
datadir=/data/${i}/data
socket=/data/${i}/mysql.sock
log_error=/data/${i}/mysql.log
port=${i}
server_id=${i}
log_bin=/data/${i}/mysql-bin" >/data/${i}/my.cnf
done
手动写入配置
cat > /data/3307/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
log_error=/data/3307/mysql.log
port=3307
server_id=7
log_bin=/data/3307/mysql-bin
EOF
cat > /data/3308/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3308/data
socket=/data/3308/mysql.sock
log_error=/data/3308/mysql.log
port=3308
server_id=8
log_bin=/data/3308/mysql-bin
EOF
cat > /data/3309/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3309/data
socket=/data/3309/mysql.sock
log_error=/data/3309/mysql.log
port=3309
server_id=9
log_bin=/data/3309/mysql-bin
EOF
初始化三套数据
mysqld --initialize-insecure --user=mysql --datadir=/data/3307/data --basedir=/application/mysql
mysqld --initialize-insecure --user=mysql --datadir=/data/3308/data --basedir=/application/mysql
mysqld --initialize-insecure --user=mysql --datadir=/data/3309/data --basedir=/application/mysql
systemd管理多实例
cd /etc/systemd/system
cp mysqld.service mysqld3307.service
cp mysqld.service mysqld3308.service
cp mysqld.service mysqld3309.service
#vim mysqld3307.service
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
#vim mysqld3308.service
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
#vim mysqld3309.service
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
启动多实例
systemctl start mysqld3307.service
systemctl start mysqld3308.service
systemctl start mysqld3309.service
验证多实例
# netstat -lntup|grep 330[7-9]
tcp6 0 0 :::3307 :::* LISTEN 18353/mysqld
tcp6 0 0 :::3308 :::* LISTEN 18387/mysqld
tcp6 0 0 :::3309 :::* LISTEN 18421/mysqld
# mysql -S /data/3307/mysql.sock -e "select @@server_id"
+-------------+
| @@server_id |
+-------------+
| 3307 |
+-------------+
# mysql -S /data/3308/mysql.sock -e "select @@server_id"
+-------------+
| @@server_id |
+-------------+
| 3308 |
+-------------+
# mysql -S /data/3309/mysql.sock -e "select @@server_id"
+-------------+
| @@server_id |
+-------------+
| 3309 |
+-------------+