1. MySQL基本概念和安装部署指南
MySQL基本概念和安装部署指南
第一章 数据库基础概念
1.1 什么是数据库
在操作系统出现之后,随着计算机应用范围的扩大、需要处理的数据迅速膨胀。最初,数据与程序一样,以简单的文件作为主要存储形式。以这种方式组织的数据在逻辑上更简单,但可扩展性差,访问这种数据的程序需要了解数据的具体组织格式。当系统数据量大或者用户访问量大时,应用程序还需要解决数据的完整性、一致性以及安全性等一系列的问题。因此,必须开发出一种系统软件,它应该能够像操作系统屏蔽了硬件访问复杂性那样,屏蔽数据访问的复杂性。由此产生了数据管理系统,即数据库。
数据库是结构化信息或数据的有序集合,一般以电子形式存储在计算机系统中。通常由数据库管理系统(DBMS)来控制。在现实中,数据、DBMS及关联应用一起被称为数据库系统,通常简称为数据库。
为了提高数据处理和查询效率,当今最常见的数据库通常以行和列的形式将数据存储在一系列的表中,支持用户便捷地访问、管理、修改、更新、控制和组织数据。另外,大多数数据库都使用结构化查询语言(SQL)来编写和查询数据。
1.2 数据库发展史
数据库技术经历了从简单文件系统到现代复杂数据库系统的演进过程:
- 1964年前: 文件系统
- 1964: 网状数据库管理系统IDS
- 1968: 层次数据库管理系统IMS
- 1970: E.F.Codd提出关系模型理论
- 1973: IBM开始System R研发(Ingres启动)
- 1976: 实体-关系模型Entity-Relationship(ER)
- 1979: Oracle诞生
- 1983: DB2诞生
- 1985: 对象模型
- 1986-1987: SQL成为关系型数据库标准语言
- 1988: Microsoft SQL Server首次出现以及微软收购Sybase的核心概念
- 1991: Informer出版《如何将数据整合》
- 1992: Jim Gray出版《事务处理》
- 1993: 首次提出OLAP概念
- 1996: PostgreSQL、MySQL诞生
- 2003-2006: Google File System、Google Bigtable、Google MapReduce
- 2007: HBase诞生
- 2008: Dynamo论文
- 2009-2010: MongoDB诞生、Redis、Neo4j
- 2012: Google发表Spanner论文
- 2015: TiDB诞生、CockroachDB诞生、Aurora诞生
- 2017: AWS发表Aurora论文
- 未来: 更高性能、分布式数据库、云原生数据库、数据安全
1.3 常见的数据库类型
1.3.1 关系型数据库(RDBMS)
特点: 使用表格(表)来组织和存储数据,表之间通过关系建立连接。
例子: MySQL、Oracle Database、Microsoft SQL Server、PostgreSQL等。
应用场景: 适用于需要事务处理、数据一致性和复杂查询的应用,如企业管理系统、金融系统等。
1.3.2 NoSQL数据库
特点: 不依赖固定的表结构,可以处理半结构化和非结构化数据。通常更具有分布式、高可扩展性。
例子: MongoDB(文档型)、Cassandra(列式存储)、Redis(键值存储)、Neo4j(图形数据库)等。
应用场景: 适用于大数据、实时数据处理、分布式系统等场景,例如社交媒体、日志分析、物联网应用等。
1.3.3 NewSQL数据库
特点: 结合了关系型数据库的ACID特性和分布式系统的可扩展性,旨在解决传统关系型数据库在大规模分布式环境中的性能瓶颈问题。
例子: Google Spanner、CockroachDB等。
应用场景: 适用于需要同时满足关系型数据库事务特性和分布式系统可扩展性需求的场景。
1.3.4 In-Memory数据库
特点: 将数据存储在内存中,提高访问速度和响应时间。
例子: Redis、Memcached等。
应用场景: 适用于对读取性能要求较高、可以牺牲部分持久性的场景,如缓存、实时分析等。
1.3.5 时序数据库
特点: 专门用于处理按时间顺序存储和查询的数据。
例子: InfluxDB、OpenTSDB等。
应用场景: 适用于物联网、监控系统、日志数据等需要按时间序列进行查询和分析的场景。
1.3.6 图形数据库
特点: 用于存储和查询图结构的数据,适用于处理实体之间复杂的关系。
例子: Neo4j、ArangoDB等。
应用场景: 适用于社交网络分析、推荐系统、网络关系分析等场景。
1.4 事务的ACID
ACID(Atomicity、Consistency、Isolation、Durability)是事务处理的四个基本属性,用于确保数据库操作的可靠性和一致性。
1.4.1 Atomicity(原子性)
考虑一笔银行转账操作,涉及从一个账户减少金额并将相同金额增加到另一个账户。在原子性的要求下,这两个操作要么同时成功,要么同时失败。如果在扣款后发生错误,那么转账应该被回滚,保持事务的原子性。
1.4.2 Consistency(一致性)
假设一个在线商店的数据库中有产品库存信息,要求每次成功的订单都要保持库存的一致性。如果一个订单成功提交,但库存更新失败,系统应该回滚订单操作,以保持库存和订单数量的一致性。
1.4.3 Isolation(隔离性)
假设有两个用户同时尝试在银行系统中转账。隔离性要求一个用户的转账操作不应受到另一个用户同时进行的转账操作的影响。即使两个转账操作同时执行,它们也应该在事务的隔离环境中执行,以防止数据不一致。
1.4.4 Durability(持久性)
考虑一个在线投票系统,在用户投票后,系统将投票结果写入数据库。持久性要求一旦投票结果被确认写入数据库,即使系统崩溃或发生故障,投票结果也应该在系统恢复后仍然可用,以保持事务的持久性。
1.5 OLTP和OLAP
1.5.1 OLTP(联机事务处理)
定义: OLTP是一种面向日常业务操作的数据库处理方式,用于支持日常的事务性操作,例如插入、更新和删除记录。
特点: OLTP系统通常涉及大量的短期交易,每个交易可能只涉及到少量的数据。它强调实时性、高并发和数据一致性。
目标: 主要用于记录业务活动的原子性操作,例如订单处理、在线购物、银行交易等。OLTP系统通常针对操作型数据库设计,采用规范化的数据库结构以避免数据冗余。
性能指标: OLTP系统的性能关注点通常在于处理速度、并发性和事务的一致性。
1.5.2 OLAP(联机分析处理)
定义: OLAP是一种用于支持决策支持和业务智能的数据库处理方式,强调对大量历史数据的分析和查询。
特点: OLAP系统通常涉及复杂的查询和分析操作,需要处理大量的数据。它强调数据的分析、汇总和报告功能。
目标: 主要用于支持决策制定、趋势分析、数据挖掘等,例如销售趋势分析、业务预测等。OLAP系统通常采用多维度数据库结构,以便更好地支持多维度数据的查询和分析。
性能指标: OLAP系统的性能关注点通常在于查询速度、数据汇总和灵活性。
1.5.3 两者对比
- 用途不同: OLTP主要用于支持业务的日常操作,而OLAP主要用于支持决策制定和业务智能的分析
- 数据模型不同: OLTP通常采用规范化的数据模型,而OLAP通常采用维度模型
- 查询复杂度: OLTP处理相对简单的查询,OLAP处理更复杂的查询和分析操作
- 实时性要求: OLTP要求实时处理,而OLAP更注重历史数据的综合分析,对实时性要求相对较低
第二章 MySQL基础知识
2.1 什么是MySQL
MySQL是最流行的开源数据库(https://db-engines.com/en/ranking)。
MySQL被广泛用于Web开发、企业应用、嵌入式系统等各种场景。其稳定性、性能和灵活性使其成为许多开发者和企业的首选数据库系统之一。
2.2 MySQL的关键特点
-
关系型数据库管理系统(RDBMS): MySQL采用关系型数据库模型,数据存储在表格中,表之间通过关系建立联系。这种结构有助于更有效地组织和管理数据。
-
开源: MySQL是开源软件(遵循GPL协议),用户可以免费获取、使用、修改和分发它。这使得MySQL成为许多小型和中小型项目的首选数据库。
-
跨平台性: MySQL可以在各种操作系统上运行,包括Windows、Linux、macOS等,提供了很大的灵活性。
-
SQL语言: MySQL使用结构化查询语言(SQL)作为与数据库交互的标准语言。SQL提供了一套强大的操作数据库的命令,包括查询、更新、插入和删除等。
-
ACID属性: MySQL遵循ACID属性,确保事务的原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)和持久性(Durability)。
-
复制和故障转移: MySQL支持复制机制,允许将数据从一个数据库服务器复制到另一个服务器,以实现数据备份、负载均衡和故障转移。
-
存储引擎: MySQL支持多种存储引擎,如InnoDB、MyISAM等,每种引擎有不同的特性和优势,使用户能够根据具体需求选择合适的存储引擎。
-
大社区和支持: 由于MySQL的广泛使用,它拥有庞大的社区和支持网络,用户可以在社区中找到大量的文档、教程和解决问题的资源。
2.3 MySQL的历史
- 1995年: MySQL 1.0发布,仅供内部使用
- 1996年: MySQL 3.11.1发布,直接跳过了MySQL 2.x版本
- 1999年: MySQL AB公司成立。同年,发布MySQL 3.23,该版本集成了Berkeley DB存储引擎。该引擎由Sleepycat公司开发,支持事务。在集成该引擎的过程中,对源码进行了改造,为后续可插拔式存储引擎架构奠定了基础
- 2000年: ISAM升级为MyISAM存储引擎。同年,MySQL基于GPL协议开放源码
- 2002年: MySQL 4.0发布,集成了后来大名鼎鼎的InnoDB存储引擎。该引擎由Innobase公司开发,支持事务,支持行级锁,适用于OLTP等高并发场景
- 2005年: MySQL 5.0发布,开始支持游标,存储过程,触发器,视图,XA事务等特性。同年,Oracle收购Innobase公司
- 2008年: Sun以10亿美金收购MySQL AB。同年,发布MySQL 5.1,其开始支持定时器(Event scheduler),分区,基于行的复制等特性
- 2009年: Oracle以74亿美金收购Sun公司
- 2010年: MySQL 5.5发布
- 2013年: MySQL 5.6发布
- 2015年: MySQL 5.7发布
- 2018年: MySQL 8.0发布
2.4 MySQL知识体系
MySQL知识体系包括以下核心模块:
- 安装: 二进制安装、源码安装、包管理工具安装、操作系统兼容性
- 复制: MySQL Enterprise Backup、mysqlpump、mysqldump、Xtrabackup、Clone Plugin、MySQL Shell Backup Server、物理增量的方式
- 监控: MySQL Enterprise Monitor、Zabbix、Prometheus(PMM)
- DDL: Online DDL、pt-online-schema-change、gh-ost、原生的online
- 中间件: MySQL Router、ProxySQL
- 高可用: MHA、Orchestrator、Group Replication(InnoDB Cluster)
- InnoDB: 体系结构、索引、事务、锁
- MySQL工具集: sysbench、Percona Toolkit
- Binlog: 设置Binlog、Binlog中的时间相关元素
- 规则: 活跃数据、规范制造、数据转换、技术基础白皮书
第三章 MySQL体系架构
3.1 MySQL架构图
MySQL采用分层架构设计,从上到下主要包括:
┌─────────────────────────────────────────────────┐
│ Client Connectors │
│ JDBC, ODBC, .NET, PHP, Python, PERL, C API │
└─────────────────────────────────────────────────┘
↓
┌─────────────────────────────────────────────────┐
│ MySQL SERVER │
│ │
│ ┌─────────────────────────────────────────┐ │
│ │ Connection Pool │ │
│ │ Connection Handling, Authentication │ │
│ │ & Security │ │
│ └─────────────────────────────────────────┘ │
│ │
│ ┌──────────┬──────────┬──────────┬─────────┐ │
│ │ SQL │ Parser │Optimizer │ Caches │ │
│ │Interface │ │ │ │ │
│ │DML, DDL, │ Lexical, │Rewriting,│ Global &│ │
│ │Stored │Semantic, │Order of │ Engine │ │
│ │Procedure,│Syntactic,│Scanning &│Specific │ │
│ │Triggers, │Code │ Indexes │ Caches │ │
│ │Views,etc │Generation│ │ │ │
│ └──────────┴──────────┴──────────┴─────────┘ │
│ │
│ ┌─────────────────────────────────────────┐ │
│ │ Pluggable Storage Engines │ │
│ │ │ │
│ │ MyISAM│InnoDB│Federated│Archive│Merge │ │
│ │ │ │ │ │Memory │ │
│ │ │ │ │ │Cluster│ │
│ │ │ │ │ │Example│ │
│ └─────────────────────────────────────────┘ │
└─────────────────────────────────────────────────┘
↓
┌─────────────────────────────────────────────────┐
│ File System │
│ NTFS, ext4, SAN, NAS │
│ │
│ Logs and Files │
│ Binary, Error, Slow, General, │
│ Redo, Undo, Data, Index │
└─────────────────────────────────────────────────┘
3.2 各组件的作用
3.2.1 Connectors(连接器)
MySQL Connector是MySQL提供的一组官方的数据库连接器,用于在不同编程语言中连接和操作MySQL数据库。这些连接器充当数据库驱动程序,提供了与MySQL服务器进行通信的接口。
3.2.2 连接池和线程管理
连接池是一个保存和复用数据库连接的缓冲区。它允许在需要时从池中获取连接,而不是每次请求时都重新创建连接。线程管理是MySQL用于处理客户端请求的组件。对于每个客户端连接,MySQL会为其分配一个线程来处理查询和其他数据库操作。这里会验证用户的账号密码。
3.2.3 SQL Interface(SQL接口)
接受客户端发起的SQL语句。
3.2.4 Parser(查询解析器)
解析器是MySQL数据库管理系统中的一个重要组件,其主要作用是解析SQL语句。解析器负责将用户提交的SQL语句转换为MySQL数据库可以理解和执行的内部数据结构,以便数据库引擎能够执行相应的操作。
MySQL解析器作用包括:
-
语法分析: 解析器对SQL语句进行语法分析,确保其符合MySQL支持的SQL语法规范。如果SQL语句有语法错误,解析器将报告错误信息。
-
构建查询树: 解析器将合法的SQL语句转换为一棵查询树(Query Tree)或查询语法树(Syntax Tree)。这颗树结构表示SQL语句的层次结构和语法关系,有助于数据库引擎进行后续的优化和执行。
-
表达式分析: 解析器负责识别SQL语句中的表达式,包括数学运算、逻辑运算、函数调用等。它将表达式解析为内部表示,以便数据库引擎能够正确执行这些表达式。
-
权限检查: 解析器可能涉及到对用户权限的检查,确保用户有权执行提交的SQL语句。这包括对表、列的访问权限等。
3.2.5 Optimizer(优化器)
MySQL优化器是MySQL中的一个关键组件,其主要作用是分析和优化SQL查询语句,以提高查询性能。优化器的主要作用包括:查询重写、索引选择、连接顺序优化、子查询优化等。
3.2.6 Caches(缓存)
MySQL中有多种类型的缓存,用于提高查询性能和减轻数据库负担:
-
查询缓存(Query Cache): 查询缓存存储已经执行过的查询的结果集,当相同的查询再次被执行时,可以直接返回缓存中的结果,而不必再次执行查询。注意:在MySQL 8.0版本中,查询缓存被移除,因为它在高并发和写入负载下可能导致性能问题。
-
InnoDB缓冲池(InnoDB Buffer Pool): InnoDB存储引擎使用缓冲池来缓存表数据和索引。这是一个内存区域,用于存储热点数据,减少对磁盘的读取次数。可以通过配置参数
innodb_buffer_pool_size来调整InnoDB缓冲池的大小。
3.3 可插拔的存储引擎
MySQL是一个支持可插拔存储引擎的关系型数据库管理系统。每个存储引擎都有其独特的特性、优势和适用场景。
3.3.1 InnoDB
特点: 默认的存储引擎,支持事务、行级锁定、外键等特性,适用于事务性应用。
适用场景: 适用于大多数OLTP(联机事务处理)应用,要求事务支持和数据一致性。
3.3.2 MyISAM
特点: 不支持事务,表级锁定,适用于读密集型操作。
适用场景: 适用于读操作频繁、写操作相对较少的场景,比如数据仓库、日志分析等。
3.3.3 Memory(或Heap)
特点: 将表数据存储在内存中,适用于需要非常快速访问的临时表。
适用场景: 适用于需要频繁读写的临时数据,但不适合用于持久性数据。
3.3.4 CSV
特点: 将表数据存储为逗号分隔的文本文件,适用于数据交换和导入导出。
适用场景: 适用于数据导入导出、数据交换等场景。
3.3.5 Archive
特点: 压缩存储,适用于存储大量归档数据。
适用场景: 适用于大量历史数据的归档和查询。
3.3.6 Blackhole
特点: 丢弃写入的数据,不存储实际数据,用于数据复制和同步。
适用场景: 适用于数据复制和同步,将写入操作传递到其他MySQL服务器。
3.3.7 TokuDB
特点: 支持高度压缩、高性能的存储引擎。
适用场景: 适用于大数据量、高写入负载的场景,例如日志、分析等。
3.4 文件系统
文件系统是计算机系统用于组织和存储文件的一种机制,它提供了文件的层次结构、存储管理、文件访问权限和元数据等功能。文件系统通常在存储介质(如硬盘、固态硬盘、光盘等)上创建一个逻辑层,用户和应用程序可以通过文件系统来对文件进行访问和管理。常见的文件系统包括:EXT3、EXT4、XFS。
3.5 日志系统
MySQL的日志系统包括:错误日志、慢查询日志、General log、Binary Log、Relay log、Redo log等。
3.6 一条SQL查询语句的执行流程
客户端
↓
连接器(验证账号密码、权限获取、连接维持)
↓
分析器(词法分析、语法分析)
↓
优化器(执行计划生成、索引选择)
↓
执行器(权限校验、调用存储引擎接口)
↓
存储引擎(数据读写)
3.6.1 连接器
连接器负责跟客户端建立连接、获取权限、维持和管理连接。
如果用户名密码不对,会收到一个"Access denied for user"的错误。
如果用户名密码正确,连接器会从权限表里面查出用户拥有的权限。这就意味着,如果修改了权限,只对新创建的连接生效。
因为建立连接的成本比较高,所以推荐使用长连接。
3.6.2 分析器
词法分析: 将输入的SQL语句分解成一个个的词(token)。每个词代表语句中的一个基本单位,比如关键字、标识符、运算符、常数等。
语法分析: 将词法单元序列转换成语法树。语法树反映了SQL语句的结构,以及各个组成部分之间的关系。语法分析器会检查语法是否符合SQL语言的规定,同时构建语法树。
语法分析器会根据语法规则,判断SQL语句是否满足MySQL语法。如果不满足,则提示"You have an error in your SQL syntax"。
3.6.3 优化器
优化器负责分析查询语句并选择执行计划,以最优的方式检索或操作数据。优化器的目标是选择执行计划,使得查询的执行效率最高,从而提高系统性能。
优化器的主要工作包括:
- 查询解析
- 查询优化
- 成本估算
- 索引优化
- 统计信息使用
- 动态优化
3.6.4 执行器
执行器负责执行查询计划并返回结果的组件。执行器的主要作用包括:
- 执行计划的执行
- 结果集的处理
- 锁管理
- 事务管理
- 异常处理
- 返回结果
- 资源管理
第四章 MySQL安装与部署
4.1 操作系统支持列表
在安装MySQL之前,首先需要确认操作系统的支持情况。
官方文档: https://www.mysql.com/support/supportedplatforms/database.html
支持周期: https://www.mysql.com/support/eol-notice.html
MySQL支持的主流操作系统包括:
- Oracle Linux / Red Hat / CentOS / Rocky Linux (x86_64, arm64)
- Oracle Solaris (SPARC_64)
- Ubuntu (x86_64)
- SUSE Enterprise Linux (x86_64)
- Debian GNU/Linux (x86_64)
- Microsoft Windows Server (x86_64)
- Microsoft Windows (x86_64)
4.2 MySQL版本选择
选择MySQL版本的原则:
- 选择GA版本
- 选择20之后的小版本
- 新业务可以直接上MySQL 8.0了
MySQL的版本命名遵循一种特定的模式,通常采用X.Y.Z的形式,其中X、Y和Z分别代表主版本号、次版本号和修订版本号:
-
主版本号(X): 表示引入了重大改变或者不兼容的变化。如果两个版本的主版本号不同,那么它们可能不兼容,需要开发者进行适应和更新。
-
次版本号(Y): 表示引入了一些新的功能,但是这些功能与之前版本兼容,不会导致现有的应用程序出现问题。通常,增加次版本号表示有一些较大的功能改进。
-
修订版本号(Z): 表示引入了一些小的改进、bug修复或者性能优化,这些变化对现有应用程序没有不兼容的影响。
例如,版本号为5.7.23的MySQL,其中5是主版本号,7是次版本号,23是修订版本号。
4.3 MySQL的下载
官方文档: https://dev.mysql.com/doc/refman/8.0/en/
下载地址: https://dev.mysql.com/downloads/mysql/
下载时需要选择:
- MySQL版本(如8.0.35)
- 操作系统(如Linux - Generic)
- OS版本(如Linux - Generic (glibc 2.17) (x86, 64-bit))
4.4 MySQL二进制包安装
4.4.1 创建用户
# groupadd mysql
# useradd -g mysql mysql
4.4.2 解压二进制包
# cd /usr/local/
# wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.35-linux-glibc2.17-x86_64.tar.xz
# tar xvf mysql-8.0.35-linux-glibc2.17-x86_64.tar.xz
# ln -s mysql-8.0.35-linux-glibc2.17-x86_64 mysql
4.4.3 编辑配置文件
# vim /etc/my.cnf
[client]
socket = /data/mysql/3306/data/mysql.sock
[mysqld]
basedir = /usr/local/mysql
datadir = /data/mysql/3306/data
user = mysql
port = 3306
socket = /data/mysql/3306/data/mysql.sock
log_error = /data/mysql/3306/data/mysqld.err
log_timestamps = system
log-bin = mysql-bin
server-id = 1
4.4.4 创建数据目录
# mkdir -p /data/mysql/3306/data
# chown mysql.mysql /data/mysql/3306/data/
4.4.5 初始化实例
# /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize
如果没有错误输出,就意味着实例初始化成功。
4.4.6 启动实例
# /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &
4.4.7 登录实例
# grep password /data/mysql/3306/data/mysqld.err
# /usr/local/mysql/bin/mysql -uroot -S /data/mysql/3306/data/mysql.sock -p
4.4.8 修改密码
alter user user() identified by '123456';
4.5 判断实例是否启动成功
- 查看错误日志
- 查看mysqld进程是否启动
4.6 定位实例启动失败的原因
- 查看错误日志
- 通过mysqld启动
# /usr/local/mysql/bin/mysqld --defaults-file=my.cnf &
- 只指定几个必要的参数启动
# /usr/local/mysql/bin/mysqld --no-defaults --basedir=/usr/local/mysql --datadir=/data/mysql/3306/data/ --user=mysql
如果还是不行,可通过strace查看MySQL启动过程中的系统调用情况。
4.7 MySQL源码包安装
源码编译需要安装相关依赖:
# cd /usr/src/
# wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-boost-8.0.35.tar.gz
# yum install cmake3 gcc gcc-c++ glibc ncurses-devel openssl-devel libaio-devel
# tar xvf mysql-boost-8.0.35.tar.gz
# cd mysql-8.0.35/
# mkdir build
# cd build
# cmake3 /usr/src/mysql-8.0.35/ -DWITH_BOOST=/usr/src/mysql-8.0.35/boost/boost_1_77_0 -DENABLE_DOWNLOADS=1 -DBUILD_CONFIG=mysql_release
# make -j 4
# make install
常见报错: GCC版本过低
解决方法:
# yum install centos-release-scl
# yum install devtoolset-11-gcc devtoolset-11-gcc-c++ devtoolset-11-binutils
# scl enable devtoolset-11 bash
# gcc --version
4.8 MySQL服务管理
4.8.1 SysV Init脚本
# cd /usr/local/mysql/
# cp support-files/mysql.server /etc/init.d/mysqld
管理mysqld服务:
# /etc/init.d/mysqld status
# /etc/init.d/mysqld stop
# /etc/init.d/mysqld start
设置开机自启动:
# chkconfig mysqld on
注意: 默认的服务管理脚本并不适用,需要修改配置文件路径参数。
4.8.2 Systemd
创建systemd服务配置文件:
# vim /usr/lib/systemd/system/mysqld.service
[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
Type=forking
PIDFile=/data/mysql/3306/data/mysqld.pid
TimeoutSec=0
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --pid-file=/data/mysql/3306/data/mysqld.pid --daemonize $MYSQLD_OPTS
EnvironmentFile=-/etc/sysconfig/mysql
LimitNOFILE = 65535
Restart=on-failure
RestartPreventExitStatus=1
PrivateTmp=false
使用systemd管理服务:
# systemctl daemon-reload
# systemctl start mysqld
# systemctl stop mysqld
# systemctl status mysqld
设置开机自启动:
# systemctl enable mysqld
第五章 MySQL配置文件参数
5.1 MySQL 8.0配置文件参数模板
[client]
socket = /data/mysql/3306/data/mysql.sock
[mysql]
no-auto-rehash
[mysqld]
# General
user = mysql
port = 3306
basedir = /usr/local/mysql
datadir = /data/mysql/3306/data
socket = /data/mysql/3306/data/mysql.sock
pid_file = /data/mysql/3306/data/mysql.pid
character_set_server = utf8mb4
transaction_isolation = READ-COMMITTED
sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
log_error = /data/mysql/3306/log/mysqld.err
default_time_zone = '+8:00'
log_timestamps = system
tmpdir = /data/mysql/3306/tmp
secure_file_priv = /data/mysql/3306/tmp
# Slow log
slow_query_log = ON
long_query_time = 0.5
slow_query_log_file = /data/mysql/3306/slowlog/slow.log
# Connection
back_log = 2048
max_connections = 500
max_connect_errors = 10000
interactive_timeout = 1800
wait_timeout = 1800
thread_cache_size = 128
max_allowed_packet = 1G
skip_name_resolve = ON
# Session
read_buffer_size = 2M
read_rnd_buffer_size = 4M
sort_buffer_size = 4M
join_buffer_size = 4M
# InnoDB
innodb_buffer_pool_size = 6144M
innodb_buffer_pool_instances = 4
innodb_log_file_size = 512M
innodb_log_files_in_group = 2
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 1
innodb_undo_tablespaces = 2
innodb_max_undo_log_size = 1024M
innodb_undo_log_truncate = 1
innodb_page_cleaners = 8
innodb_io_capacity = 200
innodb_io_capacity_max = 500
innodb_data_file_path = ibdata1:1G:autoextend
innodb_flush_method = O_DIRECT
innodb_purge_threads = 4
innodb_autoinc_lock_mode = 2
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_flush_neighbors = 1
innodb_checksum_algorithm = crc32
innodb_strict_mode = ON
innodb_print_all_deadlocks = ON
innodb_numa_interleave = ON
innodb_open_files = 65535
innodb_adaptive_hash_index = OFF
# Replication
server_id = 528884
log_bin = /data/mysql/3306/binlog/mysql-bin
relay_log = /data/mysql/3306/relaylog/relay-bin
sync_binlog = 1
binlog_format = ROW
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = ON
log_slave_updates = ON
binlog_expire_logs_seconds = 604800
slave_rows_search_algorithms = 'INDEX_SCAN,HASH_SCAN'
skip_slave_start = ON
slave_net_timeout = 60
binlog_error_action = ABORT_SERVER
super_read_only = ON
# Semi-Sync Replication
plugin_load = "validate_password.so;semisync_master.so;semisync_slave.so"
rpl_semi_sync_master_enabled = ON
rpl_semi_sync_slave_enabled = ON
rpl_semi_sync_master_timeout = 1000
# GTID
gtid_mode = ON
enforce_gtid_consistency = ON
binlog_gtid_simple_recovery = ON
# Multithreaded Replication
slave-parallel-type = LOGICAL_CLOCK
slave-parallel-workers = 8
slave_preserve_commit_order = ON
transaction_write_set_extraction = XXHASH64
binlog_transaction_dependency_tracking = WRITESET_SESSION
binlog_transaction_dependency_history_size = 25000
# Others
open_files_limit = 65535
max_heap_table_size = 32M
tmp_table_size = 32M
table_open_cache = 65535
table_definition_cache = 65535
table_open_cache_instances = 64
5.2 关键参数说明
5.2.1 连接相关参数
- max_connections: 最大连接数
- max_connect_errors: 最大连接错误次数
- interactive_timeout: 交互式连接超时时间
- wait_timeout: 非交互式连接超时时间
- thread_cache_size: 线程缓存大小
5.2.2 InnoDB相关参数
- innodb_buffer_pool_size: 缓冲池大小,通常设置为物理内存的50%-80%
- innodb_log_file_size: Redo日志文件大小
- innodb_flush_log_at_trx_commit: 事务提交时的刷盘策略(1=每次提交刷盘)
- innodb_io_capacity: IO容量设置
5.2.3 复制相关参数
- server_id: 服务器唯一标识
- log_bin: 二进制日志
- binlog_format: 二进制日志格式(ROW/STATEMENT/MIXED)
- sync_binlog: 二进制日志刷盘策略
第六章 企业级MySQL自动化部署
6.1 前言
面对几千台数据库服务器,面对每天几套到十几套MySQL集群的申请,仅10余人的DBA团队,如何实现快速交付集群呢?
6.2 MySQL部署基础
MySQL的单实例普通部署,大约需要以下几步:
- 下载软件
- 创建目录
- 创建账号
- 编辑配置文件
- 初始化
- 开启实例
- 创建库、账号等
6.3 自动化实现的考虑因素
要实现自动化部署,需要考虑以下因素:
6.3.1 主机的初始化
- 文件句柄数和最大进程数
- 透明大页
- 磁盘的IO调度算法
- SELinux
- NUMA
- SWAP
- NTP
- 操作系统参数优化
6.3.2 MySQL推荐架构
生产使用的MySQL几乎都是集群模式,即主从复制,1主2从这样。这就需要大家编写部署集群的脚本,同时可能还要申请VIP,域名,备份,监控,高可用等。
┌────────┐
│ 主库 │
│ My │
└────────┘
│
┌──────┴──────┐
│ │
┌────────┐ ┌────────┐
│ 从库1 │ │ 从库2 │
│ My │ │ My │
│高可用切换│ │备份、抽数│
└────────┘ └────────┘
6.4 自动化部署流程
6.4.1 物理机混合部署的流程与架构
环境分类:
- 打包机器
- 平台管理机器
- MySQL部署机器
流程:
┌─────────────┐
│ 打包环境 │
│ │
│ 按版本打包 │
│ MySQL软件 │
└─────────────┘
↓
┌─────────────────────────────┐
│ MySQL部署流程 │
│ │
│ 1. 初始化项目 │
│ 2. 编辑配置文件 │
│ 3. 初始化MySQL │
│ 4. 开启MySQL │
│ 5. 初始化数据库 │
│ 6. 创建账号及权限控制 │
└─────────────────────────────┘
↓
┌─────────────────────────────┐
│ 集群搭建 │
│ │
│ 1. 全局配置参数 │
│ 2. 已建好主MySQL配置 │
│ 3. 全局配置参数 │
│ │
│ YUM包管理工具全自动化快捷包 │
│ 自动识别版本 │
│ 编辑配置相关配置环境细 │
└─────────────────────────────┘
↓
┌─────────────────────────────┐
│ 监控平台 │
│ │
│ 1. 申请VIP │
│ 2. 域名绑定 │
│ 3. 监控告警配置 │
│ 4. 初始化备份脚本 │
│ 5. 应用接入,确保 │
│ 中请请式到现场确保后 │
│ 6. 自动化流程部署, │
│ 监控告警通过 │
│ 部署正常推荐 │
│ 7. 申请核心,vip │
│ 监控减负机, 循环, 监控等│
└─────────────────────────────┘
↓
┌─────────────┐
│ 交付集群 │
└─────────────┘
6.4.2 服务器初始化脚本
对于一个新的服务器,需要进行相关环境初始化:
- 创建mysql账号
- 按照mysql的依赖包
- 创建目录
- 部署DBA的管理脚本
- 注册监控
- 拷贝可启动的mysql初始化包
6.4.3 部署MySQL
部署MySQL主要包括以下部分:
- 下载软件包
- 配置实例(需要开发脚本,更改配置文件的端口、server_id等)
- 开启实例
- 搭建与交付集群(开发脚本,搭建主从复制集群,交付集群)
第七章 MySQL版本新特性
7.1 MySQL 5.5的新特性
- InnoDB代替MyISAM成为MySQL默认的存储引擎
- 多核扩展,能更充分地使用多核CPU
- InnoDB的性能提升,包括支持索引的快速创建,表压缩,I/O子系统的性能提升,PURGE操作从主线程中剥离出来,Buffer Pool可拆分为多个Instances
- 半同步复制
- 引入utf8mb4字符集,可用来存储emoji表情
- 引入metadata locks(元数据锁)
- 分区表的增强,新增两个分区类型:RANGE COLUMNS和LIST COLUMNS
- MySQL企业版引入线程池
- 可配置IO读写线程的数量(innodb_read_io_threads,innodb_write_io_threads)。在此之前,其数量为1,且不可配置
- 引入innodb_io_capacity选项,用于控制脏页刷新的数量
7.2 MySQL 5.6的新特性
- GTID复制
- 无损复制
- 延迟复制
- 基于库级别的并行复制
- mysqlbinlog可远程备份binlog
- 对TIME, DATETIME和TIMESTAMP进行了重构,可支持小数秒。DATETIME的空间需求也从之前的8个字节减少到5个字节
- Online DDL。ALTER操作不再阻塞DML
- 可传输表空间(transportable tablespaces)
- 统计信息的持久化。避免主从之间或数据库重启后,同一个SQL的执行计划有差异
- 全文索引
- InnoDB Memcached plugin
- EXPLAIN可用来查看DELETE,INSERT,REPLACE,UPDATE等DML操作的执行计划,在此之前,只支持SELECT操作
- 分区表的增强,包括最大可用分区数增加至8192,支持分区和非分区表之间的数据交换,操作时显式指定分区
- Redo Log总大小的限制从之前的4G扩展至512G
- Undo Log可保存在独立表空间中,因其是随机IO,更适合放到SSD中。但仍然不支持空间的自动回收
- 可dump和load Buffer pool的状态,避免数据库重启后需要较长的预热时间
- InnoDB内部的性能提升,包括拆分kernel mutex,引入独立的刷新线程,可设置多个purge线程
- 优化器性能提升,引入了ICP,MRR,BKA等特性,针对子查询进行了优化
可以说,MySQL 5.6是MySQL历史上一个里程碑式的版本,这也是目前生产上应用得最广泛的版本。
7.3 MySQL 5.7的新特性
- 组复制
- InnoDB Cluster
- 多源复制
- 增强半同步(AFTER_SYNC)
- 基于WRITESET的并行复制
- 在线开启GTID复制
- 在线设置复制过滤规则
- 在线修改Buffer pool的大小
- 在同一长度编码字节内,修改VARCHAR的大小只需修改表的元数据,无需创建临时表
- 可设置NUMA架构的内存分配策略(innodb_numa_interleave)
- 透明页压缩(Transparent Page Compression)
- UNDO表空间的自动回收
- 查询优化器的重构和增强
- 可查看当前正在执行的SQL的执行计划(EXPLAIN FOR CONNECTION)
- 引入了查询改写插件(Query Rewrite Plugin),可在服务端对查询进行改写
- EXPLAIN FORMAT=JSON会显示成本信息,这样可直观的比较两种执行计划的优劣
- 引入了虚拟列,类似于Oracle中的函数索引
- 新实例不再默认创建test数据库及匿名用户
- 引入ALTER USER命令,可用来修改用户密码,密码的过期策略,及锁定用户等
- mysql.user表中存储密码的字段从password修改为authentication_string
- 表空间加密
- 优化了Performance Schema,其内存使用减少
- Performance Schema引入了众多instrumentation。常用的有Memory usage instrumentation,可用来查看MySQL的内存使用情况,Metadata Locking Instrumentation,可用来查看MDL的持有情况,Stage Progress instrumentation,可用来查看Online DDL的进度
- 同一触发事件(INSERT,DELETE,UPDATE),同一触发时间(BEFORE,AFTER),允许创建多个触发器。在此之前,只允许创建一个触发器
- InnoDB原生支持分区表,在此之前,是通过ha_partition接口来实现的
- 分区表支持可传输表空间特性
- 集成了SYS数据库,简化了MySQL的管理及异常问题的定位
- 原生支持JSON类型,并引入了众多JSON函数
- 引入了新的逻辑备份工具-mysqlpump,支持表级别的多线程备份
- 引入了新的客户端工具-mysqlsh,其支持三种语言:JavaScript, Python and SQL。两种API:X DevAPI,AdminAPI,其中,前者可将MySQL作为文档型数据库进行操作,后者用于管理InnoDB Cluster
- mysql_install_db被mysqld --initialize代替,用来进行实例的初始化
- 原生支持systemd
- 引入了super_read_only选项
- 可设置SELECT操作的超时时长(max_execution_time)
- 可通过SHUTDOWN命令关闭MySQL实例
- 引入了innodb_deadlock_detect选项,在高并发场景下,可使用该选项来关闭死锁检测
- 引入了Optimizer Hints,可在语句级别控制优化器的行为,如是否开启ICP,MRR等,在此之前,只有Index Hints
- GIS的增强,包括使用Boost.Geometry替代之前的GIS算法,InnoDB开始支持空间索引
7.4 MySQL 8.0的新特性
- 引入了原生的,基于InnoDB的数据字典。数据字典表位于mysql库中,对用户不可见,同mysql库的其它系统表一样,保存在数据目录下的mysql.ibd文件中
- Atomic DDL
- 重构了INFORMATION_SCHEMA,其中,部分表已重构为基于数据字典的视图,在此之前,其为临时表
- PERFORMANCE_SCHEMA查询性能提升,其已内置多个索引
- 不可见索引(Invisible index)
- 降序索引
- 直方图
- 公用表表达式(Common table expressions)
- 窗口函数(Window functions)
- 角色(Role)
- 资源组(Resource Groups),可用来控制线程的优先级及其能使用的资源,目前,能被管理的资源只有CPU
- 引入了innodb_dedicated_server选项,可基于服务器的内存来动态设置innodb_buffer_pool_size,innodb_log_file_size和innodb_flush_method
- 快速加列(ALGORITHM=INSTANT)
- JSON字段的部分更新(JSON Partial Updates)
- 自增主键的持久化
- 可持久化全局变量(SET PERSIST)
- 默认字符集由latin1修改为utf8mb4
- 默认开启UNDO表空间,且支持在线调整数量(innodb_undo_tablespaces)。在MySQL 5.7中,默认不开启,若要开启,只能初始化时设置
- 备份锁
- Redo Log的优化,包括允许多个用户线程并发写入log buffer,可动态修改innodb_log_buffer_size的大小
- 默认的认证插件由mysql_native_password更改为caching_sha2_password
- 默认的内存临时表由MEMORY引擎更改为TempTable引擎,相比于前者,后者支持以变长方式存储VARCHAR,VARBINARY等变长字段。从MySQL 8.0.13开始,TempTable引擎支持BLOB字段
- Grant不再隐式创建用户
- SELECT ... FOR SHARE和SELECT ... FOR UPDATE语句中引入NOWAIT和SKIP LOCKED选项,解决电商场景热点行问题
- 正则表达式的增强,新增了4个相关函数,REGEXP_INSTR(),REGEXP_LIKE(),REGEXP_REPLACE(),REGEXP_SUBSTR()
- 查询优化器在制定执行计划时,会考虑数据是否在Buffer Pool中。而在此之前,是假设数据都在磁盘中
- ha_partition接口从代码层移除,如果要使用分区表,只能使用InnoDB存储引擎
- 引入了更多细粒度的权限来替代SUPER权限,现在授予SUPER权限会提示warning
- GROUP BY语句不再隐式排序
- MySQL 5.7引入的表空间加密特性可对Redo Log和Undo Log进行加密
- information_schema中的innodb_locks和innodb_lock_waits表被移除,取而代之的是performance_schema中的data_locks和data_lock_waits表
- 引入performance_schema.variables_info表,记录了参数的来源及修改情况
- 增加了对于客户端报错信息的统计(performance_schema.events_errors_summary_xxx)
- 可统计查询的响应时间分布(call sys.ps_statement_avg_latency_histogram())
- 支持直接修改列名(ALTER TABLE ... RENAME COLUMN old_name TO new_name)
- 用户密码可设置重试策略(Reuse Policy)
- 移除PASSWORD()函数。这就意味着无法通过"SET PASSWORD ... = PASSWORD('auth_string')"命令修改用户密码
- 代码层移除Query Cache模块,故Query Cache相关的变量和操作均不再支持
- BLOB, TEXT, GEOMETRY和JSON字段允许设置默认值
- 可通过RESTART命令重启MySQL实例
第八章 MySQL日志系统
8.1 错误日志
MySQL错误日志是记录MySQL服务器运行时错误和警告信息的重要文件。错误日志对于故障排除、性能监控和维护数据库非常有用。
8.1.1 错误日志相关参数
log_timestamps: 影响错误日志、General log、慢查询日志中的时间戳的时区,默认为UTC,建议设置为SYSTEM(本地系统时区)。
log_output: 用于控制通用查询日志(General Query Log)和慢查询日志(Slow Query Log)的输出位置,可设置FILE(将日志信息输出到文件),TABLE(将日志信息输出到表),FILE,TABLE(同时将日志信息输出到文件和表),NONE(禁用日志输出)。
log_error_verbosity: 调整错误日志中记录的信息的详细程度。
| log_error_verbosity | Permitted Message Priorities |
|---|---|
| 1 | ERROR |
| 2 | ERROR, WARNING |
| 3 | ERROR, WARNING, INFORMATION |
mysql> show variables like 'log_error';
+---------------+----------------------------------+
| Variable_name | Value |
+---------------+----------------------------------+
| log_error | /data/mysql/3306/data/mysqld.err |
+---------------+----------------------------------+
mysql> select * from performance_schema.error_log order by logged desc limit 1\G
*************************** 1. row ***************************
LOGGED: 2024-01-04 21:04:06.961777
THREAD_ID: 15450
PRIO: Warning
ERROR_CODE: MY-013360
SUBSYSTEM: Server
DATA: Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
8.2 General log
MySQL的General Query Log(通用查询日志)记录了MySQL服务器上的所有查询语句,包括连接和断开连接的信息。General Query Log对于调试和分析数据库活动非常有用,但在生产环境中使用时要注意它可能对性能产生负面影响。
mysql> show variables like '%general%';
+------------------+---------------------------------+
| Variable_name | Value |
+------------------+---------------------------------+
| general_log | OFF |
| general_log_file | /data/mysql/3306/data/mysql.log |
+------------------+---------------------------------+
8.3 慢查询日志
MySQL慢查询日志(Slow Query Log)记录执行时间超过指定阈值的查询语句。慢查询日志对于性能优化和发现潜在性能问题非常有用。
8.3.1 如何开启慢日志
slow_query_log = ON
slow_query_log_file = /data/mysql/3306/data/mysql-slow.log
long_query_time = 1
8.3.2 慢日志相关参数
- log_queries_not_using_indexes: 控制是否记录未使用索引的查询
- log_slow_admin_statements: 控制是否记录管理命令
- log_slow_slave_statements: 控制是否记录从库重放的慢SQL
- min_examined_row_limit: 查询检查行数少于此数量的查询不会被记录到慢查询日志中
- log_throttle_queries_not_using_indexes: 如果启用了log_queries_not_using_indexes,则log_throttle_queries_not_using_indexes变量会限制每分钟可写入慢查询日志的此类查询的数量。默认为0,不限制
- log_slow_extra: MySQL 8.0.14引入的,会记录更详细的性能指标
注意事项: 在MySQL 8.0.14之后,慢查询日志中的时间戳表示慢语句开始执行的时间。在8.0.14之前,时间戳表示慢语句完成执行后记录的时间。
8.4 二进制日志
二进制日志(Binary Log)是MySQL中一种用于记录数据库中发生的更改的日志。它包含了对数据库执行的所有修改操作,如插入、更新和删除,但不包括查询操作。二进制日志对于数据恢复、主从复制和基于位置点的恢复等方面起着关键作用。
8.4.1 二进制日志常用参数
- log_bin: 控制二进制日志是否启用
- binlog_format: 设置二进制日志的格式。常见的取值有STATEMENT、ROW和MIXED。STATEMENT记录SQL语句,ROW记录行的变化,MIXED是STATEMENT和ROW的混合模式
- expire_logs_days: 设置二进制日志文件的过期时间。指定了多少天后自动删除过期的二进制日志文件
- binlog_row_image: 控制在ROW模式下记录的二进制日志中的数据行的格式。常见取值有FULL和MINIMAL。FULL记录所有数据,而MINIMAL仅记录变化的部分
- binlog_expire_logs_seconds: 与expire_logs_days类似,指定了过期时间,但是以秒为单位。用于设置二进制日志文件的过期时间
- binlog_rows_query_log_events: 如果启用,将记录到二进制日志中的行事件对应的SQL语句
- max_binlog_size: 如果启用,将记录到二进制日志中的行事件对应的SQL查询语句
- sync_binlog: 控制每次事务提交时是否将二进制日志刷新到磁盘
8.5 Relay Log
Relay Log用于存储从主服务器复制到从服务器的二进制日志事件,以便从服务器能够重新执行这些事件,保持与主服务器的数据一致性。
8.5.1 Relay Log的相关参数
mysql> show variables like '%relay%';
+---------------------------+---------------------------------------------+
| Variable_name | Value |
+---------------------------+---------------------------------------------+
| max_relay_log_size | 0 |
| relay_log | mysql-relay-bin |
| relay_log_basename | /data/mysql/3306/data/mysql-relay-bin |
| relay_log_index | /data/mysql/3306/data/mysql-relay-bin.index |
| relay_log_info_file | relay-log.info |
| relay_log_info_repository | TABLE |
| relay_log_purge | ON |
| relay_log_recovery | OFF |
| relay_log_space_limit | 0 |
| sync_relay_log | 10000 |
| sync_relay_log_info | 10000 |
+---------------------------+---------------------------------------------+
各参数的作用:
- max_relay_log_size: 设置中继日志的最大大小。当中继日志大小达到此限制时,MySQL将切换到下一个中继日志文件
- relay_log: 指定中继日志的基本名称。实际的中继日志文件名将附加一个递增的序列号
- relay_log_basename: 指定中继日志文件的基本路径和名称
- relay_log_index: 指定中继日志索引文件的路径和名称
- relay_log_info_file: 指定保存中继日志信息的文件的名称
- relay_log_info_repository: 指定用于存储中继日志信息的存储库类型。这里设置为TABLE表示使用表存储中继日志信息
- relay_log_purge: 控制中继日志的清理。设置为ON表示启用自动清理
- relay_log_recovery: 控制MySQL在启动时是否执行中继日志的恢复
- relay_log_space_limit: 设置中继日志空间的限制。当中继日志的总大小达到此限制时,MySQL将停止写入中继日志
- sync_relay_log: 控制在每个事务提交时是否强制将中继日志刷新到磁盘,指定刷新频率,例如10000表示每提交10000个事务后刷新一次
- sync_relay_log_info: 控制在每个事务提交时是否强制将中继日志信息文件刷新到磁盘。指定刷新频率,例如10000表示每提交10000个事务后刷新一次
附录:实践建议与注意事项
A.1 操作系统优化建议
- 文件句柄数: 建议设置为65535或更高
- 透明大页: 建议关闭,使用
echo never > /sys/kernel/mm/transparent_hugepage/enabled - 磁盘调度算法: SSD使用noop或none,机械硬盘使用deadline
- SELinux: 建议关闭或设置为permissive模式
- NUMA: 关闭NUMA或设置
innodb_numa_interleave=ON - SWAP: 设置
vm.swappiness=1 - NTP: 确保时间同步
A.2 MySQL安全最佳实践
- 定期更新MySQL版本
- 使用强密码策略
- 限制root用户的远程访问
- 定期审查用户权限
- 启用二进制日志
- 定期备份数据
- 监控异常访问
A.3 性能优化建议
- 合理配置缓冲池: innodb_buffer_pool_size通常设置为物理内存的50%-80%
- 优化查询: 使用EXPLAIN分析查询计划
- 合理使用索引: 避免过多索引,定期检查未使用的索引
- 监控慢查询: 定期分析慢查询日志
- 使用连接池: 减少连接创建开销
- 读写分离: 使用主从复制实现读写分离
A.4 故障排查方法
- 查看错误日志: 首先检查错误日志
- 检查系统资源: CPU、内存、磁盘IO、网络
- 查看进程状态:
SHOW PROCESSLIST - 检查锁等待: 使用Performance Schema
- 分析慢查询: 使用慢查询日志和pt-query-digest
- 查看复制状态:
SHOW SLAVE STATUS
A.5 备份恢复策略
- 全量备份: 每周进行一次全量备份
- 增量备份: 每天进行增量备份
- 二进制日志备份: 实时备份二进制日志
- 定期测试恢复: 确保备份可用
- 异地备份: 将备份存储在不同地理位置
浙公网安备 33010602011771号