MySQL-DBA课程-Day02

今日内容

第二章 MySQL基础管理

1. 用户管理

1.1 作用
登录
管理对象
1.2 长成什么样?
用户名@'白名单'
1.2.1 用户名:
不要太长 , 和业务有关
emp_user01
grant all on . to wordpress@'%' identified '123';
1.2.2 白名单?
user@'10.0.0.56'
user@'%'
user@'10.0.0.%' 255.255.255.0 24位
user@'10.0.0.0/255.255.254.0' 23位
user@'10.0.0.5%' 50-59
user@'localhost' 本地
常用:
user@'10.0.0.%'
user@'10.0.0.0/255.255.254.0'
user@'10.0.0.5%' 50-59
user@'localhost' 本地
1.2.3 用户管理
创建用户:
mysql> CREATE USER oldguo@'10.0.0.%' IDENTIFIED BY '123';
查询用户:
mysql> select user,host from mysql.user;
mysql> select user,host,authentication_string from mysql.user;
修改用户:
mysql> alter user oldguo@'10.0.0.%' identified by '123456';
删除用户:
mysql> drop user oldguo@'10.0.0.%';
说明:
8.0+ 版本: 必须先创建用户再授权.
8.0以前: 可以grant 授权时自动创建用户.

2. 权限管理

2.1 MySQL权限列表
mysql> show privileges;
2.2 授权和回收权限
GRANT 权限 ON 权限作用范围 TO 用户 IDENTIFIED BY '123' with grant option;

权限 : 
ALL :
Alter                  
Alter routine          
Create                 
Create routine         
Create temporary tables
Create view            
Create user            
Delete                 
Drop                   
Event                  
Execute                
File                   
Grant option           
Index                  
Insert                 
Lock tables            
Process                
Proxy                  
References             
Reload                 
Replication client     
Replication slave      
Select                 
Show databases         
Show view              
Shutdown               
Super                  
Trigger                
Create tablespace      
Update                 
Usage

权限作用范围 :
*.* ====> 一般是管理员会设置的方法
oldguo.* ====> 一般是业务用户会设置的方法
oldguo.t1 ====> 一般是业务用户户设置的方法
2.3 企业授权案例
(1)授权一个管理员用户oldguo,可以从10网段任意地址登录管理数据库
GRANT ALL ON *.* TO oldguo@'10.0.0.%' IDENTIFIED BY '123' with grant option;
(2)授权一个业务用户app,可以从10网段地址访问app库的所有表
grant select,update,insert,delete ON app.* TO app@'10.0.0.%' IDENTIFIED BY '123' ;
(3)授权一个开发用户dev,可以对dev库进行业务开发

2.4 root管理员密码忘记或被篡改如何处理?
(1) 关闭数据库,启动到"单用户"模式
[root@db01 data_3306]# systemctl stop mysqld
[root@db01 data_3306]# mysqld_safe --skip-grant-tables --skip-networking &
(2) 无密码登录MySQL
[root@db01 data_3306]# mysql
mysql> alter user root@'localhost' identified by '123456';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
(如果不刷新权限，会有报错)
mysql> flush privileges;
mysql> alter user root@'localhost' identified by '123456';

(3) 重启数据库到正常模式
[root@db01 data_3306]# systemctl restart mysqld

2.5 查询用户权限
mysql> show grants for app@'10.0.0.%';

2.6 回收权限
mysql> revoke delete,drop on app.* from 'app'@'10.0.0.%';

3. MySQL的连接管理

3.1 自带客户端工具
3.1.1 mysql
-u 用户名
-p 密码
-h IP
-P 端口
-S socket位置
-e 免交互
< 导入SQL脚本
例子:(1) TCP连接串远程登录
注:需要提前创建好远程用户
mysql> grant all on *.* to oldguo@'10.0.0.%' identified by '123';
[root@db01 data_3306]# mysql -uroot -p -h 10.0.0.51 -P 3306
(2) Socket连接方式
注:需要提前创建好localhost用户
mysql> grant all on *.* to oldguo@'localhost' identified by '123';
[root@db01 data_3306]# mysql -uoldguo -p -S /tmp/mysql.sock
Enter password:
如何验证一个用户是通过本地还是远程和登录的.
show processlist;
(3) 免交互执行命令
[root@db01 ~]# mysql -uroot -p -e "show processlist"
(4) 导入SQL脚本

[root@db01 ~]# mysql -uroot -p < t100w.sql
Enter password: 
mysql> source /root/world.sql

3.1.2 mysqladmin
(1) 修改密码 [root@db01 ~]# mysqladmin -uroot -p123456 password 123
[root@db01 ~]# mysql -uroot -p123
(2) 关闭数据库 [root@db01 ~]# mysqladmin -uroot -p123 shutdown

3.2 第三方开发工具
sqlyog
navicat
workbench

3.3 应用程序连接
php-mysql
pip3 install mysql
jar
go

4. MySQL的启动关闭

systemctl ---> mysql.server start
-----> mysqld_safe ----> mysqld

5. MySQL的初始化配置

5.1 初始化配置方法
源码安装定制 < 初始化配置文件 < 命令行启动时定制

5.2 初始化配置文件
[root@db01 data_3306]# mysqld --help --verbose |grep my.cnf
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

建议一个mysql实例一个配置文件

5.3 配置文件书写格式

[root@db01 data_3306]# cat /etc/my.cnf 
[mysqld]
user=mysql
port=3306
basedir=/usr/local/mysql57
datadir=/data/mysql/data_3306
server_id=6
socket=/tmp/mysql.sock
[mysql]
socket=/tmp/mysql.sock

标签项 ====> [mysqld]
服务器端 [server]: [mysqld],[mysqld_safe] ====> 影响到MySQL启动
客户端 [clinet] : [mysql] ,[mysqldump] ====> 影响本地客户端程序
配置项 ====> key=value

5.4 自定制初识化配置文件位置
mysqld --defaults-file=/opt/a.cnf &

6. 多实例的规划和配置

分布式架构中应用广泛
6.1 端口和目录
rm -rf /data/mysql/data_{3307,3308,3309}
mkdir -p /data/mysql/data_{3307,3308,3309}
6.2 配置文件准备

cat > /data/mysql/my3307.cnf <<EOF
[mysqld]
user=mysql
port=3307
basedir=/usr/local/mysql57
datadir=/data/mysql/data_3307
server_id=7
socket=/tmp/mysql3307.sock
EOF
cat > /data/mysql/my3308.cnf <<EOF
[mysqld]
user=mysql
port=3308
basedir=/usr/local/mysql57
datadir=/data/mysql/data_3308
server_id=8
socket=/tmp/mysql3308.sock
EOF
cat > /data/mysql/my3309.cnf <<EOF
[mysqld]
user=mysql
port=3309
basedir=/usr/local/mysql57
datadir=/data/mysql/data_3309
server_id=9
socket=/tmp/mysql3309.sock
EOF

6.3 授权
[root@db01 ~]# chown -R mysql.mysql /data/

6.4 初始化数据

mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql57 --datadir=/data/mysql/data_3307
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql57 --datadir=/data/mysql/data_3308
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql57 --datadir=/data/mysql/data_3309

6.5. 启动多实例
[root@db01 mysql]# mysqld --defaults-file=/data/mysql/my3307.cnf &
[root@db01 mysql]# mysqld --defaults-file=/data/mysql/my3308.cnf &
[root@db01 mysql]# mysqld --defaults-file=/data/mysql/my3309.cnf &
[root@db01 mysql]# netstat -tulnp

6.6. 使用 systemd 管理多实例

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/mysql57/bin/mysqld --defaults-file=/data/mysql/my3307.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/mysql57/bin/mysqld --defaults-file=/data/mysql/my3308.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/mysql57/bin/mysqld --defaults-file=/data/mysql/my3309.cnf
LimitNOFILE = 5000
EOF

pkill mysqld
systemctl start mysqld3307
systemctl start mysqld3308
systemctl start mysqld3309

========================================

第三章 SQL 基础

1. SQL介绍

1.1 简介
结构化查询语言.
1.2 SQL标准
SQL89 SQL92 SQL99 SQL03 SQL05

1.3 SQL_MODE
除数为零
日期
mysql> select @@sql_mode;

1.4 SQL 类型
DDL : 数据定义语言 : 库名,库属性,表名,表属性,列(列名,列属性)
DCL : 数据控制语言 : 权限
DML : 数据操作语言 : 数据行
DQL : 数据查询语言 : 数据行

1.5 SQL功能
管理,操作数据库对象:
库: 库名,库属性
表: 表名,表属性,列(列名,列属性),数据行

2. MySQL规范性存储限制

2.1 字符集Charset
utf8 : 最大字节长度3个.
utf8mb4 : 最大字节长度4个. 可以存储emoji表情字符.
mysql> show charset;

2.2 排序规则 (校对规则)
show collation;
默认是大小写不敏感.
utf8mb4_general_ci
utf8mb4_bin

2.3 数据类型
2.3.1 数字类型
tinyint 1字节长度数字 ===> 11111111 ===> 0-2^8-1 ===> -2^7-27-1 (3位)
int 4字节长度 ====> 0-2^32-1 ====> -2^31 - 2^31-1 (10位数)
bigint 8字节长度 ====> 0-2^64-1 ====> -2^63 - 2^63-1 (20位数)

2.3.2 字符串
char(10) : 定长类型,最多10个字符,占用存储空间一定.最多存储255个字符.
varchar(10):
变长类型,最多10个字符,按需分配存储空间.
需要额外1个字符或2个字符存储字符长度

因素: 变长的字符串列,90%几率都是varchar
具体原因是什么?
节省空间,还有没有别的原因?
遗留的问题..

enum('m','f')
............1 2

2.3.3 时间类型
DATETIME
范围为从 1000-01-01 00:00:00.000000 至 9999-12-31 23:59:59.999999。

TIMESTAMP
1970-01-01 00:00:00.000000 至 2038-01-19 03:14:07.999999。
timestamp会受到时区的影响

2.3.4 二进制类型
2.3.5 JSON(8.0)

昨日回顾

(1) 数据库种类
RDBMS : MySQL
NoSQL : Redis,MongoDB,ES
NEWSQL
(2) MySQL版本选择
小版本 GA
(3) 安装
5.7+版本
初始化数据:
mysqld --initialize --user=mysql --basedir=/usr/local/mysql57 --datadir=/data/mysql/data_3306（自动设置密码，后方有打印）

mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql57 --datadir=/data/mysql/data_3306（不安全初始化，无密码）

5.7 以前版本:

/usr/local/mysql56/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql56 --datadir=/data/mysql/data_3306
(4) 体系结构
一条SQL语句的执行过程
连接层

连接协议: TCP,Socket
验证
native
sha2
连接线程
接受SQL
返回结构
show processlist;

SQL层

语法
语义
权限
解析 ---> 执行计划
预处理 ---> 代价计算(cost)
优化器 ---> 选择代价低的
执行器 ---> 结果----> 段--->区---->页
查询QC(query_cache)

案例: 5.7.23 , 16C_64G , 版本按月分区表 , 做压力测试

开启QC之前:
select * from a where aa=xxx;
QPS 3000+

开启之后:
select * from a where aa=xxx;
QPS 600+

热点数据,可以用redis缓存数据库承当.

日志记录(按需开启)
binlog
general_log

存储引擎层
去系统找需要的数据页.返回到SQL,结构化数据成表.

MySQL 物理存储结构

段 : 一个表就是一个段.由1个或多个区构成.
区 : 连续的64个page,默认是1M,最小的存储分配单元.
页 : 连续4个OS block,默认16KB,最小的IO单元.