MySQLDBA手册.pdf简体中文版资源-CSDN文库

mysql

sql

需积分: 5 13 浏览量 2024-04-05 14:21:24 上传评论收藏 7.14MB PDF 举报

资源推荐

资源详情

资源评论

MySQL Guide for Oracle DBA

Chaoyang Han

Page  2 
 
目录 
关于 MySQL ....................................................................... 1 
MySQL 介绍 ..................................................................... 1 
MySQL 发展历史 ................................................................. 1 
MySQL 版本 ..................................................................... 2 
MySQL 的优势 ................................................................... 2 
MySQL 各个版本的变化 ............................................................. 3 
MySQL 5.5 ...................................................................... 3 
MySQL 5.6 ...................................................................... 3 
MySQL 5.7 ...................................................................... 4 
MySQL 8.0 ...................................................................... 5 
MySQL 8.0 的新增功能 ............................................................. 6 
Data dictionary ................................................................ 6 
Atomic Data Definition Statements (Atomic DDL) ................................. 6 
Security and account management ................................................ 6 
Resource management ............................................................ 6 
InnoDB enhancements ............................................................ 6 
Character set support .......................................................... 7 
Optimizer ...................................................................... 7 
Internal temporary tables ...................................................... 7 
Logging ........................................................................ 7 
Backup lock .................................................................... 7 
MySQL 安装手册 ................................................................... 7 
Install MySQL on Unix .......................................................... 7 
Installing MySQL on Linux ...................................................... 8 
安装文件目录结构信息 ........................................................... 8 
安装 MySQL 8.0.12 ................................................................ 9 
检查 MySQL 环境 ................................................................. 9 
创建安装目录 ................................................................... 9 
解压 MySQL ..................................................................... 9 
配置用户和环境变量 ............................................................ 14 
初始化 MySQL .................................................................. 15 
InnoDB 架构 ..................................................................... 15 
InnoDB In-Memory Structures ................................................... 16 
InnoDB On-Disk Structures ..................................................... 16 
innodb_dedicated_server ....................................................... 17 
Innodb Crash Recovery ......................................................... 18 
初始化...................................................................... 18 
系统检查.................................................................... 18 
重做日志.................................................................... 18 
日志扫描.................................................................... 18 
优化........................................................................ 19 

Page  3 
 
源码分析.................................................................... 19 
Innodb Cluster .................................................................. 20 
InnoDB Cluster 组件 ........................................................... 20 
在生产环境下部署 .............................................................. 21 
安装思路.................................................................... 22 
安装基础环境 ............................................................. 22 
创建集群 ................................................................. 23 
添加节点到集群中 ......................................................... 23 
使用 MySQL Router 连接集群 ................................................ 23 
安装过程.................................................................... 23 
环境准备 ................................................................. 23 
需要准备的软件 ........................................................... 23 
安装 MySQL ................................................................ 23 
安装 MySQL Shell .......................................................... 23 
安装 MySQL Router ......................................................... 23 
创建集群 ................................................................. 23 
添加实例节点 node02 ....................................................... 25 
添加实例节点 node03 ...................................................... 25 
安装 MySQL Router ......................................................... 25 
对比 MySQL 与 Oracle ............................................................. 25 
MySQL Architecture ............................................................ 26 
MySQL Redo and Undo ......................................................... 27 
MySQL 的 LSN ................................................................. 29 
MySQL 初始化 .................................................................. 30 
帮助信息.................................................................... 30 
初始化步骤 .................................................................. 30 
安全加固.................................................................... 34 
MySQL 日志 .................................................................... 34 
初始化参数 .................................................................... 35 
查看/修改系统参数 ........................................................... 35 
数据库字符集 .................................................................. 36 
创建数据库表 .................................................................. 36 
MySQL 表空间 .................................................................. 37 
InnoDB 最大表空间限制 ....................................................... 38 
InnoDB engine tablespaces ................................................... 40 
System tablespace ........................................................ 40 
Redo Logs ................................................................... 40 
UNDO tablespace and logs ................................................. 41 
Temporary tablespace ..................................................... 41 
General tablespace ....................................................... 41 
Tablespace Data Encryption ............................................... 42 
MySQL InnoDB Configuration sample ........................................ 42 
UNDO and Temporary tablespaces sample .................................... 43 
InnoDB TDE using a keyring_file plugin ................................... 45 

Page  4 
 
创建用户 ...................................................................... 46 
MySQL 8.0 密码选项 .......................................................... 47 
MySQL 常用命令 ................................................................ 47 
MySQL 权限管理 ................................................................ 48 
USER 表 ..................................................................... 48 
DB 表 ....................................................................... 48 
HOST 表 ..................................................................... 48 
TABLE_PRIV 表 ............................................................... 48 
COLUMNS_PRIV 表 ............................................................. 48 
授权样例.................................................................... 48 
错误代码查看 .................................................................. 49 
如何查看 DDL 创建语句 .......................................................... 49 
SHOW 语法 ................................................................... 49 
访问数据库常用客户端 .......................................................... 51 
块校验工具 .................................................................... 51 
文本文件的加载 ................................................................ 51 
登录数据库 .................................................................... 51 
数据库的备份 .................................................................. 51 
MySQL 备份数据的方式 ........................................................ 52 
常用的备份工具 .............................................................. 52 
mysqlbackup .............................................................. 52 
percona-xtrabackup ....................................................... 55 
mysqldump ................................................................ 58 
MySQL 官方监控工具 ............................................................ 59 
MySQL 常用工具 ................................................................ 61 
分析工具.................................................................... 61 
GUI......................................................................... 62 
MySQL 配置修改 .................................................................. 62 
MySQL 安装目录说明 ............................................................ 62 
配置文件的位置 ................................................................ 62 
Windows 系统配置文件读取 ...................................................... 62 
Linux 系统配置文件读取 ........................................................ 63 
配置文件内容 .................................................................. 63 
MySQL 数据库存储引擎 ............................................................ 72 
存储引擎简介 .................................................................. 72 
InnoDB...................................................................... 72 
MyISAM...................................................................... 72 
MRG_MYISAM .................................................................. 73 
MEMORY...................................................................... 73 
CSV......................................................................... 73 
ARCHIVE..................................................................... 74 
BLACKHOLE ................................................................... 74 
PERFORMANCE_SCHEMA .......................................................... 74 
FEDERATED ................................................................... 74 

Page  5 
 
其他........................................................................ 74 
常用引擎对比 .................................................................. 74 
查看存储引擎 .................................................................. 75 
设置存储引擎 .................................................................. 75 
如何选择合适的存储引擎 ........................................................ 75 
MySQL 优化 ...................................................................... 75 
优化工具 ...................................................................... 76 
使用 explain ................................................................ 77 
使用 profile ................................................................ 78 
MySQL 优化概述 ................................................................ 83 
总体优化原则 .................................................................. 83 
索引优化 ...................................................................... 84 
索引类型.................................................................... 84 
索引优化.................................................................... 84 
SQL 语句优化 .................................................................. 85 
如何捕获低效 sql ............................................................ 85 
慢查询优化的基本步骤 ........................................................ 85 
优化原则.................................................................... 86 
数据库表优化 .................................................................. 86 
Scheme 设计与数据类型优化 ................................................... 86 
其他建议 ...................................................................... 87 
MySQL 自带工具 .................................................................. 88 
命令行使用程序 ................................................................ 89 
MySQL 服务器端使用工具程序 .................................................. 89 
MySQL 安装相关程序 .......................................................... 89 
MySQL 客户端使用工具程序 .................................................... 89 
MySQL 程序开发工具 .......................................................... 89 
MySQL 管理实用程序 .......................................................... 89 
其他程序.................................................................... 90 
MySQL Workbench 客户端 ........................................................ 90 
MySQL 官方工具 .................................................................. 91 
Database Operations ........................................................... 91 
General Utilities ............................................................. 91 
High Availability ............................................................. 91 
Server Operations ............................................................. 91 
How to get Utilities .......................................................... 91 
MySQL 的安全 .................................................................... 91 
安全规则或建议 ................................................................ 92 
数据库常用操作 .................................................................. 92 
连接数据库 .................................................................... 92 
MySQL 命令语法 ................................................................ 92 
MySQL 命令连接数据库 .......................................................... 92 
开启 MySQL 的远程帐号 .......................................................... 93 
MySQL 修改密码 ................................................................ 94 