【若泽大数据】MySQL DBA 笔记（超详细）

MySQL: DBA

关系型数据的基础理论


文件系统上：文件
磁盘上存储空间的数据：可流式化


block: 块


10W, grep; 


数据管理软件：
层次模型
网状模型
关系模型
对象-关系模型
非关系模型


DBMS: Database Management System
RDBMS: 


关系模型中组件：
数据方案
表
视图：虚表，存储下来的select语句
索引
存储过程：代码段
存储函数：
触发器：
游标：
事务：ACID
用户：数据访问授权




数据存储机制：
数据按索引顺序存储：索引顺序文件
数据是按需随机存储：堆文件




MySQL核心组件：
连接池：认证、线程重用、连接数限制、内存检查、缓存；
SQL接口：DDL, DML, 关系型数据库的基本抽象；
parser: 查询转换、对象权限检查；
优化器：访问路径，性能相关的统计数据；
caches和buffers：与存储引擎自身相关的I/O性能提升工具；
存储引擎：
MyISAM、InnoDB(变种：XtraDB)、Memory、Merge、Federated、CSV、Archive、Blackholl、Aria、SphinxSE、TokuDB


安装方式：
rpm包：
os vendor
mysql
通用二进制格式
源码包


编译方式安装mariadb:
cmake


编译安装过程：
cmake . 
make
make install


MySQL的数据文件：文件和日志
文件：数据文件和索引文件
日志：事务日志、二进制日志、查询日志、慢查询日志、错误日志、中继日志


MySQL的服务器变量：
SHOW {GLOBAL|SESSION} VARIABLES [LIKE CLAUSE];


MySQL的状态变量：
SHOW {GLOBAL|SESSION} STATUS [LIKE CLAUSE];



回顾：


安装和访问MySQL Server:
初始化：
给root用户设置密码：
SET PASSWORD FOR 'username'@'host' = PASSWORD('your_password');
update mysql.user set password=PASSWORD('your_password') where user='username' and host='hostname or ip'
mysqladmin -uUSERNMAE -hHOSTNAME_OR_IP -p password 'new_password'


删除匿名用户：
DROP USER ''@'HOSTNAME'


配置文件：所有的mysql程序都可以以此文件作为其配置文件
例如：mysqld, mysql, mysqladmin, mysqld_safe
client: 客户端程序
server: 服务器端程序


[program]
parameter1 = 




获取帮助：mysqld --verbose --help
--option, -option: 命令行选项


Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf 


--defaults-extra-file=#：额外读取的配置文件；


--defaults-file=#: 仅读取此处指定的配置文件


mysql程序的类别：
服务器端程序：启动并监听于套接字上；mysqld, mysqld_safe, mysqld_multi
客户端程序：可通过mysql协议连入服务器并发出请求的；mysql, mysqlbinlog, mysqladmin, mysqldump等
工具程序：运行于服务器进程所在的主机，实现一些管理或维护操作，myisamchk


客户端程序的通用选项：
-u, --user=
-u root, -uroot, --user=root
-h, --host=
-p, --password=


--protocol=
tcp: 
socket: unix sock
pipe:
memory:


--port: 当Protocol是tcp时使用的端口；
--socket: 相当于--protocol socket


其它选项：
-D
--database=


mysql客户端程序：mysql
运行方式有两类：
交互式模式:
批模式：mysql < /path/from/somefile.sql


交互式模式：
命令有两类：
客户端命令：help可列出所有命令
clear, \c: 
ego, \G:
go, \g:
delimiter, \d:
quit, exit, \q: 
source, \. /path/from/somefile.sql: 
相当于mysql < /path/from/somefile.sql
system, \! COMMAND: 运行shell命令
use, \u DB_NAME: 将指定的库设为默认库
服务器端命令：help KEYWORD
SQL语句：
DDL
DML


help KEYWORD


选项：
-e 'SQL语句'




mysql -->mysql protocol (TCP/IP) --> mysqld


mysqladmin工具：


mysqladmin [options] command [arg] [command [arg]] ...


子命令：
create DB_NAME:
mysqldadmin [options] create DB_NAME;
drop DB_NAME:
status:
显示mysqld的简单要状态信息，专用选项
--sleep #: 间隔秒数
--count #: 显示的次数
extend-status: 显示mysqld的所有服务器状态变量
flush-privileges: 刷新授权表，相当于reload命令
flush-hosts: 清除dns缓存及被拒绝的客户端列表缓存
flush-logs: 滚动日志, 二进制日志和中继日志
flush-status: 重置各状态变量
flush-tables: 关闭当前打开的所有的表文件句柄；
flush-treads: 重置线程缓存；


password: 设置密码


ping: 测试服务器是否在线
processlist: 显示当前服务器上的所有线程


refresh: 相当于执行flush-hosts和flush-logs


shutdown: 关闭服务器进程 ；


start-slave, stop-slave: 启动、关闭从服务器线程；


variables: 显示服务器变量




mysql功能特性补充：
1、命令历史；
2、命令行编辑功能：
Ctrl+a: 快速移动光标至行首
Ctrl+e:               行尾
Ctrl+w: 删除光标之前的单词
Ctrl+u: 删除行首至光标处的所有内容
Ctrl+y: 粘贴使用Ctrl+w或Ctrl+u删除的内容
3、提示符：
mariadb> 等待输入命令
-> 续行
'> 还需要补全后半部单引号
"> 
`> 


mysql有很多图形化的客户端程序：
phpMyAdmin
Navicat for mysql
Toad for mysql
mysql front
sqlyog




Developing:
数据类型
SQL语句


开发DBA：数据库设计（E-R）、SQL语句编写、自定义函数、存储过程、存储函数、触发器、事件调度器
管理DBA：安装、升级服务器程序, 数据备份、恢复, 用户和权限管理, 指标监控、性能分析、基准测试, 语句优化, 数据字典, 按需配置服务器(服务器变量：默认的存储引擎、缓存、日志), 服务器的规模扩展架构设计及实施


SQL语言的组成部分：
DDL
DML
完整性定义语言：DDL
主键、外键、惟一键、条件、非空、事务
视图定义
事务控制
DCL


数据类型的功用：
1、存储的值类型；
2、占据的最大存储空间；
3、定长、变长；
4、如何被索引和排序；
5、是否能够被索引；


数据字典：系统编目（system catalog）
保存了数据库服务器上的元数据


元数据：
关系的名称
每个关系中各字段的名称
各字段的类型和长度
约束
每个关系上的视图的名字及视图的定义


授权的用户名字
用户的授权和帐户信息


统计类数据
每个关系中字段数；
每个关系中行数；
每个关系的存储方法；


元数据也通过数据库保存：
infomation_schema
mysql
performance_schema


数据类型：
字符型
CHAR
VARCHAR
BINARY
VARBINARY
TEXT
BLOB
数值型
精确数值型
INT
DECIMAL
近似数值型
FLOAT
DOUBLE
日期时间型：
DATE
TIME
DATETIME
YEAR
STAMP
内建类型:
ENUM
SET


字符型：
CHAR：255
VARCARH：65535
TINYTEXT: 255
TEXT: 65535
MEDIUMTEXT: 2^24
LONGTEXT: 2^32


BINARY: 255
VARBINARY：65535
TINYBLOB
BLOB
MEDIUMBLOG
LONGBLOB


修饰符：
NULL
NOT NULL
DEFAULT 'string'


CHARACTER SET 'set'
SHOW CHARACTER SET;


COLLATION 'collation'
SHOW COLLATION；


字符有通配符：
%: 匹配任意长度的任意字符
_: 匹配任意单个字符；


DEFAULT不用于BLOB类型；


整型：
TINYINT: 1Byte
SMALLINT: 2Bytes 
MEDIUMINT: 3Bytes 
INT：4Bytes 
BININT：8Bytes 


修饰符：
UNSIGNED
NULL
NOT NULL
DEFAULT #
AUTO_INCREMENT: 自动增长
特殊要求：非空，且必须是主键或惟一键；


浮点型：
FLOAT
DOUBLE


修饰符：
NOT NULL
NULL
DEFAULT
UNSIGNED


布尔型：
没有专用布尔型，其是TINYINT(1)的别名；


日期时间型：
DATE: 3Bytes
TIME: 3Bytes
DATETIME: 8Bytes
TIMESTAMP: 4Bytes
YEAR(2)：1Byte
YEAR(4): 1Byte


修饰符：
NULL
NOT NULL
DEFAULT VALUE


内建类型：
ENUM：枚举，表示仅能从给出的选项选择其中一个；
ENUM('string1','string2')
SET：集合, 表示能使用给出的元素组合成字符串
SET('a','b','c')


修饰符：
NULL
NOT NULL
DEFAULT ''




MySQL sql_mode: 
sql模式：用来限定mysqld的工作特性
TRADITIONAL
STRICT_TRANS_TABLES：对支持事务的表使用严格模式；
STRICT_ALL_TABLES：对所有表使用严格模式


服务器变量的类型：
全局：对所有会话都生效；
所有的会话在建立时都从全局继承，但继承完成后每个会话独立维护自己会话级变量
修改全局的服务器变量仅对之后建立的会话生效
要求有管理权限
会话：仅对当前会话有效；
修改即刻生效；
不要求管理权限


修改方式：
动态修改: 会话级别，立即生效；全局级别，新建立的会话有效；重启服务会失效
静态修改：要修改配置文件，或修改传递给mysqld的选项的值；重启后有效；

并非所有的服务器变量都支持动态修改；


查看服务器变量：
mysql> SHOW {GLOBAL|SESSION} VARIABLES [LIKE clause];


mysql> SELECT @@{GLOBAL|SESSION}.VARIABLE_NAME;


mysql> SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='';


mysql> SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME='';


动态修改变量的值：
mysql> SET {GLOBAL|SESSION} VARIABLE_NAME='VALUE'




让设置永久有效的方式：
[mysqld]
sql_mode = 'STRICT_ALL_TABLES'








回顾：数据类型、sql模型、查看修改服务器变量


数据类型：
字符型：
CHAR, BINARY
VARCHAR, VARBINARY
TEXT：TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
BLOB：
数值型：
精确数值：INT, DECIMAL
近似数值：FLOAT, DOUBLE, REAL, BIT
日期时间型：
日期
时间
日期时间
时间戳
年
内建类型：
ENUM
SET
布尔型：
TINYINT(1)


修饰符：NULL, NOT NULL, UNSIGNED, DEFAULT, AUTO_INCREMENT


sql_mode: mysql工作模式


服务器变量：
GLOBAL
SESSION


SHOW, SELECT @@
SET 


数据库、表、索引

MySQL中字符的大小写
1、SQL关键字及函数名不区字符大小写；
SELECT, Select, select
切记：遵循同一种风格
2、数据库、表、索引及视图的名称是否区分大小写取决于低层的OS及FS；
3、存储过程、存储函数及事件调度器不区分字符大小写；但触发器区分；
4、表别名不区分大小写；
5、字段中字符数据，类型为binary、blog、varbinary时区分大小写；其它的不区分；


SQL：
DDL
DML


DDL：CREATE/ALTER/DROP
数据库：
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
CHARACTER SET [=] charset
COLLATE [=] collation


MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%char%';


DROP {DATABASE | SCHEMA} [IF EXISTS] db_name


表：
约束：PRIMARY KEY, UNIQUE KEY, FOREIGN KEY
索引：特殊的数据结构，用于快速找到数据记录（行）
键可用作索引；并非所有索引都是键；