1. MySQL的体系结构及基础管理
-
MySQL C/S结构 **
server端: mysqld
clint 端: mysql ,mysqldump , 第三方程序(开发) -
mysqld核心程序结构 *****
SQL语句 : 结构化查询语言
SQL分类 :DDL(数据定义语言),DCL(数据控制语言),DML(数据操作语言)...
-
SQL语句的执行过程
连接层:
(1)提供连接协议
TCP/IP: 网络Scoket=IP+Port
Socket: scoket文件
(2)验证用户名密码,ip
(3)提供连接线程 负责: SQL接收和结果的反馈
show processlist;
SQL层 *****
(1)语法检查
(2)语义检查
(3)权限检查
(5)解析: 生成多种执行计划树.
(6)优化器: 判断哪一种执行计划,代价最低. 资源代价: CPU,IO,MEM
(7)执行器:执行优化器选择后的执行计划执行. 得到需要查询数据的存储位置.
存储引擎层(文件系统)
(1)生成一致性事务快照
(2)根据不同语句,实现不同锁处理
(3)从undo快照获取我们需要查询数据
(4) 根据SQL层提供的取数据的方法,拿到数据
(5)转回给SQL层,结构化成表,通过连接线程返回结果.
-
物理存储结构
宏观:-
MyISAM
存储引擎表:(5.5-)
user.frm :表结构
user.MYD :表数据
user.MYI :索引
-
InnoDB 存储引擎表(5.5+):
time_zone.frm :表结构
time_zone.ibd :数据+索引(IOT)
-
微观: *****
segment(段) : 一个(非分区表)表就是一个段 ,由一个或者多个区构成.
extent(区) : <连续>的64个page,默认1M.
page(页) : MySQL的最小物理IO单元,默认16KB,<连续>的4个OS block.
- MySQL逻辑结构 **
库 ------> 目录
表 ------> 表文件
列 : 列名 + 列属性(约束,数据类型,其他属性)
行 : 记录
属性: 数据行,权限等…
1.1 MySQL的用户管理
-
用户的管理命令
(1) 查 5.7+ mysql> select user,host ,authentication_string from mysql.user; 5.7- mysql> select user,host ,password from mysql.user; (2) 增 mysql> create user oldguo@'10.0.0.%'; (3) 改 mysql> alter user oldguo@'10.0.0.%' identified by '123'; (5) 删 drop user oldguo@'10.0.0.%';
1.2 权限管理
-
8.0+ :支持角色功能,自由打包需要的权限.
-
7.2.2 权限定义方法
GRANT 权限 ON 权限作用范围 TO 用户 IDENTIFIED BY '密码'; GRANT SELECT, INSERT, UPDATE, DELETE ON oldguo.* TO oldguo@'10.0.0.%' IDENTIFIED BY '123'
-
权限管理
例子1: 创建并授权超级管理员用户root,能够通过10网段任意地址管理数据库. mysql> grant all on *.* to root@'10.0.0.%' identified by '123' with grant option; 查询用户权限 mysql> show grants for root@'10.0.0.%'; 例子2: 创建一个应用用户app,能够通过10网段访问(SELECT, INSERT, UPDATE, DELETE)app库的所有表 grant SELECT,INSERT,UPDATE,DELETE on app.* to app@'10.0.0.%' identified by '123'; 回收: grant SELECT,INSERT,UPDATE,DELETE on app.* to app@'10.0.0.%' identified by '123'; mysql> revoke delete on app.* from app@'10.0.0.%';
2. 8.0.16二进制安装&& 5.7.26升级到8.0(扩展项)
[root@db01 /usr/local]# mv mysql-8.0.16-linux-glibc2.12-x86_64 mysql80
[root@db01 /usr/local]# chown -R mysql.mysql mysql80
[root@db01 /usr/local]# mkdir -p /data/mysql/data80
[root@db01 /usr/local]# chown -R mysql.mysql /data/
[root@db01 /usr/local]# mv /etc/my.cnf /etc/my.cnf.bak
####编辑配置文件
[root@db01 ~]# cat /etc/my80.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql80
datadir=/data/mysql/data80
socket=/tmp/mysql80.sock
port=3380
####启动
[root@db01 ~]# /usr/local/mysql80/bin/mysqld_safe --defaults-file=/etc/my80.cnf &
3. MySQL的启动关闭
-
3.1 Sys V
service mysqld start/stop/restart/status
-
3.2 systemd
[root@db01 ~]# systemctl start/stop/restart/status mysqld --socket=/tmp/aa.sock
-
3.3 手工启动方式(维护性操作时)
/etc/init.d/mysqld -----> /usr/local/mysql/bin/mysqld_safe ----> /usr/local/mysql/bin/mysqld
-
例子: 管理员用户root@‘localhost’,密码丢失 .
-
(1. 通过跳过授权表参数+跳TCP/IP的参数,启动数据库
--skip-grant-tables : 跳过授权表,连接层关闭验证模块.
--skip-networking : 关闭连接层TCP/IP,只留socket文件连接
-
(2. 无密码登录到MySQL
mysql
-
(3. 修改密码
mysql> flush privileges; mysql> alter user root@'localhost' identified by '123';
-
(5. 正常重启数据库
[root@db01 ~]# service mysqld restart
-
-
2.5 mysql的其他关闭方式
[root@db01 ~]# mysqladmin -uroot -p -S /tmp/mysql.sock shutdown
4. 初始化配置
-
4.0 初始化配置方式
(1) 配置文件 *****
(2) 命令行 ***
(3) 编译安装 * -
4.1 配置文件默认读取顺序(/etc/my.cnf)
[root@db01 ~]# mysqld --help --verbose |grep my.cnf /etc/my.cnf ---> /etc/mysql/my.cnf ----> /usr/local/mysql/etc/my.cnf ---> ~/.my.cnf scoket=A B C D 自己定义默认位置: mysqld或者mysqld_safe 加入 --defaults-file=/opt/oldguo.conf
-
4.2 配置文件内容
-
4.2.1 影响范围
服务端启动和工作
本地客户端连接(socket) -
4.2.2 内容结构
标签:
服务端: [mysqld] [mysqld_safe] [server]
客户端: [mysql] [mysqldump] [client]例子: [root@db01 ~]# cat /etc/my.cnf [mysqld] user=mysql basedir=/usr/local/mysql datadir=/data/mysql/data socket=/tmp/mysql.sock [mysql] socket=/tmp/mysql.sock
-
5. 多实例
-
5.1 规划
(1) 端口 : 3307,3308,3309
(2) 数据 : /data/330{7…9}/data
(3) 配置文件: /data/330{7…9}/my.cnf
port=3307/3308/3309
server_id=7/8/9
datadir=/data/330{7…9}/data
socket=/tmp/mysql330{7…9}.sock -
5.2 干活
(1) 创建关键目录 mkdir -p /data/330{7..9}/data chown -R mysql.mysql /data/* (2) 初始化数据 [root@db01 /data]# mv /etc/my.cnf /tmp mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3307/data mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3308/data mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3309/data (3) 准备配置文件 cat > /data/3307/my.cnf <<EOF [mysqld] user=mysql basedir=/usr/local/mysql datadir=/data/3307/data port=3307 server_id=7 socket=/tmp/mysql3307.sock EOF cat > /data/3308/my.cnf <<EOF [mysqld] user=mysql basedir=/usr/local/mysql datadir=/data/3308/data port=3308 server_id=8 socket=/tmp/mysql3308.sock EOF cat > /data/3309/my.cnf <<EOF [mysqld] user=mysql basedir=/usr/local/mysql datadir=/data/3309/data port=3309 server_id=9 socket=/tmp/mysql3309.sock EOF (5) 准备启动脚本 cat > /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=/usr/local/mysql/bin/mysqld --defaults-file=/data/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=/usr/local/mysql/bin/mysqld --defaults-file=/data/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=/usr/local/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf LimitNOFILE = 5000 EOF (6) 启动多实例 [root@db01 ~]# systemctl start mysqld3307 [root@db01 ~]# systemctl start mysqld3308 [root@db01 ~]# systemctl start mysqld3309 [root@db01 ~]# netstat -tulnp|grep mysqld tcp6 0 0 :::3307 :::* LISTEN 10327/mysqld tcp6 0 0 :::3308 :::* LISTEN 10361/mysqld tcp6 0 0 :::3309 :::* LISTEN 10395/mysqld