mysq高级DBA运维学习之路（1）

1、哪些数据适合储存到数据库？

重要性比较高的数据
关系比较复杂的数据

2、什么是数据库管理系统（DBMS）？

RDBMS：关系型数据库管理系统（安全级别要求较高的数据）
NoSql：非关系型数据库管理系统（高性能存储数据，一般是配合RDBS使用，针对大数据处理和分布式·架构更加擅长。）

3、写出你知道的RDBMS和NoSql的数据库产品。

RDBMS：
MySql、Oracle、（MsSql）SqlServer、PG

NoSql：
键--值：memcached、redis
 文档：  mongodb

4、MySql简介及产品线

1）MySql行业主流版本：

 5.6
 5.7
 8.0（现在开始研究新特性）

2）企业版本选择

Oracle：
     MySql官方版
红帽：
     MariaDB
percona：
     PerconaDB
 ###4.3版本选择建议要上新环境
 1、一般选择官方主流版本
 2、GA版本（稳定版）
 3、6 -12 月的产品版本

mysql连接方式

Tcp/ip 方式（远程，本地）：mysql -uroot -p123123 -h 192.168.1.160 -P 3306
socket方式： mysql -uroot -p123123 -S /tmp/mysql.sock

mysqld程序结构

1.3.2一条sql语句的执行过程

1.3.2.1 连接层

1）提供连接协议：tcp/ip、socket
2) 提供验证：用户，密码，ip，socket文件
3）提供一个专门的连接线程：接收用户SQL。返回结果。通过以下语句可以查看连接线程基本情况。
mysql>show processlist;

1.3.2.2 SQL层（重点）

1）接收上层传送的SQL语句
2）语法验证魔模块：验证语句语法
3）语法检查：判断SQL语句的类型

DDL：数据定义语言
DCL：数据控制语言
DML：数据操作语言
DQL：数据查询语言

4）权限检查：用户对库表有没有权限
5）解释器：对语句执行前，进行SQL的预处理，生成解析树（执行计划）
6）优化器：根据解释器得出的多种执行计划，进行判断，选择最优的执行计划
   代价模型：资源（CPU IO MEM）的耗损评估性能好坏。
7）执行器：根据最优的执行计划，执行SQL语句，产生执行结果。
 执行结果：在磁盘的XXXX位置上。
8）提供查询缓存（默认没有开启）会使用redis tair替代查询缓存功能。
9）提供日志记录（日志管理章节）：binlog,默认是没有开启的。

1.3.2.3 存储引擎层（类似linux中的文件系统）

负责根据SQL层执行的结果，从磁盘上拿数据，将16进制数据

1.4.2 表（二维表）

表名
属性
列；列名（字段），列属性（数据类型，约束等）
数据行（记录）

1.5 物理存储结构引入’

1.5.1 库的物理存储结构

用文件系统的目录来存储
在mysql数据目录/data/mysql下创建abc目录，查询databases库，显示已创建“abc”的库

1.5.2 表的物理存储结构

以下面两个表为例：

MyISAM（一种引擎）的表

User.frm : 列的相关信息
User.MYD：数据行
User.MYI：索引

[root@oracle mysql]# ls -lrt user.*
-rw-r----- 1 mysql mysql 10816 Oct 10 00:46 user.frm
-rw-r----- 1 mysql mysql   500 Oct 10 00:53 user.MYD
-rw-r----- 1 mysql mysql  4096 Oct 12 06:30 user.MYI
[root@oracle mysql]# 

InnoDB（默认储存引擎）的表

time_zone.frm：储存列相关的信息
time_zone.ibd：数据行+索引

[root@oracle mysql]# ls -lrt time_zone.*
-rw-r----- 1 mysql mysql  8636 Oct 10 00:46 time_zone.frm
-rw-r----- 1 mysql mysql 98304 Oct 10 00:46 time_zone.ibd
[root@oracle mysql]# 

2.基础管理

2.1 用户、权限管理

2.1.1 用户

作用：

登录：管理数据库对象

定义：

用户@‘白名单’
允许那些IP可以登录数据库
支持的方式？
oldboy@'%'           :所有地址
oldboy@'10.0.0.%'  ：10.0.0.0网段的地址
oldboy@'localhost'   ：本机主机
oldboy@'10.0.0.5%' ：10.0.0.50~~10.0.0.59
oldboy@'10.0.0.0/255.255.255.254.0'   :子网掩码不是24位

管理操作:

增加;
mysql> create user oldboy@'192.168.1.%' identified by '123123'';
查询：
mysql> select * from user;
mysql> select host,user,authentication_string from user where user = 'oldboy';
修改：
mysql> alter user oldboy@'192.168.1.%' identified by 'oldboy123';
修改:
mysql> update mysql.user set authentication_string = password(123123) where user 
= 'oldboy';
删除：
mysql> drop user oldboy@'192.168.1.%';

权限管理操作

常用授权操作：
mysql> grant all on *.* to oldboy@'192.168.1.5' identified by '123123';

常用权限介绍：
select . insert , update, delete, create, drop, reload, shutdown, process, file,
references, index, alter, show databases, super, create temporary,
lock tables, execute, replication slave, replocation client, create view, show view, 
create routine,alter routine,create user, event,trigger, create tablespace.

all: 以上所有权限，一般是普通管理员拥有
with grant option: 超级管理员才具备的，给别的用户授权的功能

应用用户： create, delete.alter.update,grant

权限作用范围：
*.*                 ------> 管理员
wordpress.*   ----->开发和应用用户
wordpress.t1    --->

需求1：windows 机器的navicat登录到lkinux中的mysql，管理员用户
grant all on *.* to root@'%' identified by '123123';
需求2：创建一个应用用户app用户，能从windows上登录mysql，并操作app库
grant select , insert , update, delete , on app.* to app@'192.168.1.%' identified by '123123';

开发人员用户授权流程

1、权限
2、对那些库，哪些表进行操作
3、你从哪里来连接我
4、密码要求

提示: 8.0 在grant命令添加新特性

建用户和授权分开了
grant命令不支持修改密码的功能
create user oldboy@'192.168.1.5' identified by '123123';
grant all on *.*  to  oldboy@'192.168.1.5;

查看用户权限：
mysql> show grants for oldboy@'192.168.1.%';
收回用户权限：
mysql> revoke delete on app.* from app@'192.168.1.%';

连接管理：

自带客户端命令：

忘记密码:

1)调试启动方式 
mysql关闭状态下执行：
mysql> mysqld_safe  --skip-grant-tables &  ：跳过功能验证模块 不加载授权表 
（此状态下不安全，mysql两种连接方式socket和tcp，也即是本地连接和远程连接
 均可以修改，所以需要同时关闭tcp连接方式,只留下socket）
mysql> mysqld_safe  --skip-grant-tables --skip-networking  & (推荐使用)
mysql>mysql
mysql>flush privileges;
mysql> alter user root@'192.168.1.%' identified by '123123';

不禁用tcp

禁用tcp
手动刷新，执行命令（报错提示的是不验证）