简介

介绍：阿里云 RDS（Relational Database Service）是一种稳定可靠、可弹性伸缩的在线数据库服务。

功能：提供容灾、备份、恢复、监控、报警、高可用、迁移等全套解决方案，彻底解决数据库运维的烦恼。

数据库：支持 MySQL、SQL Server、PostgreSQL、 MariaDB 等引擎。

优势：便宜易用（按需变配，灵活计费）、高性能（最佳实践，CloudDBA）、灾备设计（自动备份，异地容灾）、高安全性（防 DDoS 攻击，链路加密，访问控制，安全审计）。

基本概念：

• 实例：一个独立占用物理内存的数据库服务进程，用户可以设置不同的内存大小、磁盘空间和数据库类型。
• 数据库：在一个实例下创建的逻辑单元，一个实例可以创建多个数据库，数据库在实例内的命名唯一。
• 地域：是指物理的数据中心。
• 可用区：是指在同一地域内，电力和网络互相独立的物理区域。
• 连接数：实例当前总连接数，包括活跃连接数和总连接数。
• IOPS：实例的每秒 I/O 请求次数，单位：次/秒，衡量随机访问的性能。

流程

选型（owner：架构师）-> 申请（owner：项目经理）-> 交付（owner：云管理）-> 运维（owner：DBA）

选型

产品系列

功能对比

存储类型

特性对比

功能对比

实例规格

通用型 vs 独享型

网络类型

• 经典网络：实例之间不通过网络进行隔离，只能依靠实例自身的白名单策略来阻挡非法访问。
• 专有网络： VPC 是一种隔离的网络环境，安全性和性能均高于传统的经典网络，推荐使用专有网络。

最佳实践

运维

开通服务

登录：入口

RAM账号：raohui@1596633715004367.onaliyun.com

快速使用

创建实例：云管理团队提供

设置白名单：将通用白名单模式切换为高安全白名单模式，拥有更高的安全性

创建数据库：推荐库名与应用名称尽量一致，由小写字母、数字、下划线或中划线组成，默认字符集 utf8

创建账号

密码：由大写字母、小写字母、数字、特殊字符中的任意三种组成，特殊字符为 !@#$%^&*()_+-=

监控告警

监控

告警：统一告警，需要自行设置报警规则

备份恢复

默认备份

异地容灾

场景：对于数据可靠性有强需求的业务场景或是有监管需求的金融业务场景。

• 灾备实例：通过数据传输服务（DTS）实现主实例和异地灾备实例之间的实时同步，灾备实例与主实例配置完全相同，当主实例所在区域发生突发性自然灾害等状况，主节点（Master）和备节点（Slave）均无法连接时，可将异地灾备实例切换为主实例，在应用端修改数据库链接地址后，即可快速恢复应用的业务访问，计费：灾备实例 + DTS。
• 跨地域备份：自动将本地备份文件复制到另一个地域的 OSS 上，文件可保留 7~1825 天，即最多保留 5 年，计费：存储（0.001元/GB/小时） + 流量费。

高可用

购买时建议选择高可用版，或更高的三节点企业版，高可用版实例有一个备实例，三节点企业版实例有两个备实例，主备实例的数据会实时同步，业务只能访问主实例，备实例仅作为备份形式存在，不提供业务访问。

• 自动切换：实例默认为自动切换，当主实例出现故障无法访问时，会实现秒级自动切换到备实例。
• 手动切换：即使自动切换是开启状态，也可以手动进行主备切换。

单可用区

多可用区

读写分离

场景：在对数据库有少量写请求，但有大量读请求的应用场景下，单个实例可能无法承受读取压力，甚至对业务产生影响，为了分担数据库压力，创建只读实例，满足大量的数据库读取需求，增加应用的吞吐量。

原理：创建只读实例时会从备实例复制数据，数据与主实例一致，主实例的数据更新也会在主实例完成操作后立即自动同步到所有只读实例，也可以在只读实例上设置只读实例延时复制。

只读实例 vs 主备实例 vs 灾备实例

数据安全

提供了多样化的安全加固功能来保障用户数据的安全，其中包括但不限于：

① 网络：IP 白名单、VPC 网络、SSL（安全套接层协议）

② 存储：TDE（透明数据加密）、自动备份

③ 容灾：同城容灾（多可用区实例）、异地容灾（两地多中心)

白名单：可以让实例得到高级别的访问安全保护，建议定期维护。

• 默认的 IP 白名单只包含默认地址 127.0.0.1，表示任何设备均无法访问该 RDS 实例
• 0.0.0.0/0 表示允许任何设备访问 RDS 实例，请谨慎使用
• 高安全白名单模式下，经典网络白名单分组适用于公网访问，如果有公网设备要访问 RDS 实例，请将公网设备 IP 地址添加到经典网络白名单分组

SSL加密：SSL 在传输层对网络连接进行加密，能提升通信链路数据的安全性和完整性，但会同时增加网络连接响应时间，开通 SSL 加密后，应用或者客户端连接 RDS 时需要配置 SSL CA 证书。

透明数据加密 TDE：TDE（Transparent Data Encryption）可对数据文件执行实时 I/O 加密和解密，数据在写入磁盘之前进行加密，从磁盘读入内存时进行解密。TDE 不会增加数据文件的大小，开发人员无需更改任何应用程序，即可使用 TDE 功能。

云盘加密：阿里云免费提供云盘加密功能，基于块存储对整个数据盘进行加密，即使数据备份泄露也无法解密，最大限度保护数据安全，而且加密不会影响业务，应用程序也无需修改。

诊断优化

日志管理：错误日志、慢日志、主备切换日志，帮助故障定位分析

• 错误日志：记录 1 个月内数据库运行出错的日志。

• 慢日志：对 1 个月内数据库中执行时间超过 1 秒（可以在参数设置 中修改 long_query_time 来设置）的 SQL 语句进行统计汇总，控制台每分钟更新一次，实时的慢日志明细可以查看 mysql.slow_log 表。

• 主备切换日志：该功能适用于高可用版、三节点企业版。

优化分析：CloudDBA（慢SQL、实时会话、SQL优化、空间分析、死锁、诊断报告）

简介

数据库网关（Database Gateway，DG）无需本地开通公网端口，将本地数据库与云服务连接起来，且可以与阿里云产品（如：数据传输服务 DTS，数据库备份 DBS，数据管理 DMS，混合数据库管理 HDM 等）集成使用，提供安全、可靠、低成本的私网数据库接入能力。

原理

通过数据库网关，可以使用应用程序或云服务访问并管理本地 IDC 数据库和其他云厂商数据库。

工作原理：

① 本地安装一个数据库网关代理。

② 网关代理负责与数据库网关云端服务建立安全可信任的通道，不同账号、不同网关所建立的通道彼此隔离。

③ 通道建立后，当在云端使用数据库产品（如 HDM、DBS）需要访问该网络内的本地数据库时，数据库产品会通过已建立好的通道，访问至本地网络。

DBGateway

是 HDM 的一个组件，部署在用户环境中的 Daemon 程序，它负责进行数据库性能数据的采集、计算，不读取用户业务数据，并且通过加密压缩的方式进行通信。

目录结构

1
2
3
4
5
6
7
8
9
10
11

.
├── bin
│   ├── dbgateway
│   └── dbgateway-manager
├── conf
│   ├── dbgateway.conf
│   └── server.crt
└── log
    ├── dbgateway.err
    ├── dbgateway.log
    └── dbgateway_manager.log

常用命令

1
2
3
4
5
6
7
8
9
10
11

# 重启 
/opt/dbgateway/bin/dbgateway-manager -d restart

# 启动 
/opt/dbgateway/bin/dbgateway-manager -d start

# 停止 
/opt/dbgateway/bin/dbgateway-manager -d stop

# 删除
rm -rf /opt/dbgateway

API参考

简介

混合云数据库管理 （Hybrid Cloud Database Management， 简称 HDM) ，帮助企业打通混合云数据库架构，提供多环境、多数据库的统一监控、报警、运维、管理的能力，帮助文档。

核心功能：集群管理、统一监控、统一告警、Dashboard、故障诊断、性能优化、安全审计、诊断报告等

目前支持数据库：MySQL、Redis、MongoDB、PostgreSQL

支持环境：

目标用户：DBA

登录地址：入口

RAM账号：sre@1596633715004367.onaliyun.com

实践

开通服务

手册

实例接入

接入方式：阿里云 RDS、阿里云 ECS 自建数据库、本地 IDC 自建数据库、账号直连

功能对比：

接入步骤：

第一步：选择网络类型

• 如果是有公网的自建数据库，请选择“公网（经典网络）”。
• 如果是通过专线／VPN 方式连接阿里云 VPC 的本地 IDC 或者其他云，请选择“专有网络”。

第二步：部署网关

1

sudo wget -O install-dbgateway http://hdm-dbgateway-cn-hangzhou.oss-cn-hangzhou.aliyuncs.com/DBGateway/install-dbgateway && sudo /bin/bash install-dbgateway --id idc_iQn5Df7QC5docekj --token 70EE4B0C444E215700BEFD8B14DB026EF6A958FBE42DACDEE304FC91880A933D --endpoint master-hdm-cn-hangzhou.aliyuncs.com --region default --native

第三步：授权验证

1
2
3

CREATE USER 'hdm'@'%' IDENTIFIED BY PASSWORD 'Hdm@123!';
GRANT SHOW DATABASES, PROCESS, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'hdm'@'%';
GRANT SELECT ON *.*  TO 'hdm'@'%';

三种网络连接方式：

专线

• 专线是指阿里云接入点和本地数据中心之间的网络线路的抽象。
• 您需要通过一条租用的运营商专线将本地 IDC 连接到阿里云接入点，建立专线连接。
• 物理专线的私网连接不通过公网，因此与传统的公网相比，专线连接更加安全、可靠、速度更快、延迟更低。

VPN

VPN 网关是一款基于互联网通信技术，通过加密通道将企业数据中心和阿里云专有网络安全可靠地连接起来的服务。

互联网

本地 IDC 直接通过互联网和阿里云公有云互联，HDM 会对数据访问链路进行加密，用户无需担心安全问题。

统一监控

MySQL Server

TPS/QPS

QPS (Queries Per Second)，每秒 SQL 语句执行次数

TPS (Transactions Per Second)，每秒事务操作次数

会话连接

执行次数

流量吞吐（单位：KB）

InnoDB 存储引擎

InnoDB Buffer Pool 命中率（%）

InnoDB Data 读写吞吐量（单位：KB）

InnoDB Row Operations

MySQL 服务进程

MySQL CPU 利用率（%）

MySQL 内存使用量（单位：GB）

MySQL 存储空间使用量（单位：GB）

主机

主机 Load 负载

在 UNIX 系统中，系统负载是对当前 CPU 工作量的度量，被定义为特定时间间隔内运行队列中的平均线程数对于单个 CPU，有人认为如果 Load 超过 0.7 就算是超出正常范围了，这个值越低越好，负载过高会导致机器无法处理其他请求及操作，甚至导致宕机。

主机 CPU 利用率（%）

主机内存使用量（单位：MB）

主机网络吞吐（单位：KB）

统一告警

告警功能：配置模版、触发规则、屏蔽告警、解除屏蔽、告警订阅等

告警配置：告警模板 -> 告警联系组 -> 关联资源，告警模板默认配置 8 条告警，可自定义

问题诊断

实例会话：原理是执行 show processlist，查看活跃会话和全部会话，可快速诊断阻塞的 SQL，紧急情况下先结束会话，释放资源。

拓扑结构

注意：目前实例拓扑仅支持 MySQL

性能快照

与告警规则关联，通过自动触发方式产生性能快照，保存异常现场，方便 DBA 排查数据库异常。

1
2

场景示例：
MySQL数据库凌晨1点发生了CPU 90%的告警，导致正常业务响应时间变长，但是等DBA登录数据库上进行排查，异常已经消失，从监控历史上只能看到CPU飙高、活跃会话增长，没有慢SQL。在这种缺少数据、缺少现场的情况下，没有办法确认根本原因，同样的问题可能会持续发生，影响业务可用性。

性能优化

慢 SQL：架构设计、SQL分析 [样本 | 优化]

死锁

可通过以下手段降低死锁发生的概率：

• 如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会；
• 为表添加合理的索引，如果不走索引将会为表的每一行记录加锁，死锁的概率就会大大增大；
• 避免大事务，尽量将大事务拆成多个小事务来处理；大事务占用资源多，耗时长，与其他事务冲突的概率也会变高；
• 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
• 对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率。

空间分析：可以查看每张表的占用空间、索引空间、碎片率等，点击表名可以查看表结构和索引，异常条件：

• 实例的空间使用率大于 90%。
• 实例的库表总空间（物理）剩余可用天数估计不足 7 天。
• 实例可回收空间大于 60GB，且碎片率大于 5%。
• 单表大于 6G，并且碎片率大于 30%。
• 单表大于 50GB。
• 单表行数大于 500w，且平均行长大于 10KB。

安全审计

HDM 的安全审计功能，采用旁路的技术，采集并分析对数据库服务器的各类操作行为，实时地、智能地解析对数据库服务器的各种操作，自动识别高危 SQL、SQL 注入、新增访问来源等风险。

高危 SQL

在 HDM 中，会根据预设的规则库，自动识别三种类型的高危 SQL ：

① DDL（新建表、修改表结构、修改索引、重命名表等操作)；

② 全表更新（例如全表 Update 、全表 Delete 等)；

③ 大请求，默认规则是满足下面三个条件中的任意一个：

• 扫描行数 >= 100w
• 返回行数 >= 10w
• 更新行数 >= 10w

SQL 注入

所谓 SQL 注入，就是通过把 SQL 命令插入到 Web 表单提交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的 SQL 命令，严重危害数据库的健康。

1
2
3
4

示例：
SELECT 'xx' FROM 'xx' WHERE 1 = 1 AND 1 = 1
- Where 子句中连续出现两个常量运算表达式
- Where 子句中有部分条件总为真

新增访问来源

和历史的访问来源纪录进行对比，自动识别新增的访问来源，帮助用户确认是否存在未知的机器在访问或者读取数据库，默认规则是过去七天没有出现过的访问来源，即为新增访问来源。

诊断报告

功能：支持用户创建、查看、下载诊断报告

要求：诊断时间必须小于 24小时

内容：实例信息、健康状况（CPU / 连接数 / 活跃会话 / QPS / TPS）、告警列表、活跃会话列表、Top5 慢 SQL、Top5 表空间、死锁分析、性能趋势（CPU / 网络 / 内存 / QPS and TPS / 会话 / 空间变化）

容量评估

限制：目前仅支持 MySQL RDS

功能：容量建议 | 容量评估（CPU / 内存 / IOPS / 活跃会话 / 存储空间）

FA&Q

① 有些数据库服务器未连接外网，dbgateway 无法下载，实例如何接入？

通过搭建代理服务器 Squid 进行下载，原理如下图：

搭建代理服务器

青岛代理服务器地址：10.133.0.53:3128

1
2
3
4
5
6
7
8
9
10
11

# 安装squid
yum -y install squid

# 设置自启动
systemctl enable squid.service

# 配置squid
vim /etc/squid/squid.conf

# 启动squid
systemctl start squid.service

使用代理接入HDM

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

# 配置代理
echo  "
http_proxy=http://10.133.0.53:3128
https_proxy=http://10.133.0.53:3128
ssl_proxy=http://10.133.0.53:3128
tls_proxy=http://10.133.0.53:3128
ftp_proxy=http://10.133.0.53:3128
tcp_proxy=http://10.133.0.53:3128
export http_proxy
export https_proxy
export ssl_proxy
export ftp_proxy
export tcp_proxy
export tls_proxy" >> /etc/profile ;  source /etc/profile

# 配置wget代理
echo "
https_proxy=http://10.133.0.53:3128/
http_proxy=http://10.133.0.53:3128/
ftp_proxy=http://10.133.0.53:3128/" >> /etc/wgetrc ;  source /etc/wgetrc

# 下载install-dbgateway
sudo wget -O install-dbgateway http://hdm-dbgateway-cn-hangzhou.oss-cn-hangzhou.aliyuncs.com/DBGateway/install-dbgateway && sudo /bin/bash install-dbgateway --id idc_zqSS2oMpeyKubndD --token 3E24C01713C83B7328F9A2F4C411078DC820E63FAFBC058F6635FAC5B0B4B7F2 --endpoint master-hdm-cn-hangzhou.aliyuncs.com --region default --native

# 修改install-dbgateway，注释以下代码
curl -v --connect-timeout 3 --silent ${endpoint}:80 --output -
if [ $? != 0 -a $? != 52 ]; then
       echo "Error! Can not connect to ${endpoint}:80, please check your network environment."
       exit 1
fi

# 安装完成
sudo /bin/bash install-dbgateway --id idc_zqSS2oMpeyKubndD --token 3E24C01713C83B7328F9A2F4C411078DC820E63FAFBC058F6635FAC5B0B4B7F2 --endpoint master-hdm-cn-hangzhou.aliyuncs.com --region default --native

② 实例接入后，显示的 IP 不是真实的业务 IP ？

经与阿里云开发沟通，新版本在 /opt/dbgateway/conf/dbgateway.conf 中添加 gateway.local.ip= 配置项，dbgateway 优先使用配置中的 ip 。

③ 服务器断电启动后，dbgateway 服务未自动重启？

暂不支持

简介

文件系统：操作系统用来管理磁盘的功能，像 InnoDB、 MyISAM 这样的存储引擎都是把表存储在文件系统上。

安装目录：用来存储关于控制客户端程序和服务器程序的命令（如 mysql，mysqld，mysqld_safe 等）。

1
2
3
4
5
6
7

mysql> show variables like 'basedir';
+---------------+-------------------------------------+
| Variable_name | Value                               |
+---------------+-------------------------------------+
| basedir       | /usr/local/mysql-5.7.23-el7-x86_64/ |
+---------------+-------------------------------------+
1 row in set (0.00 sec)

数据目录：用来存储 MySQL 在运行过程中产生的数据。

1
2
3
4
5
6
7

mysql> show variables like 'datadir';
+---------------+-------------------+
| Variable_name | Value             |
+---------------+-------------------+
| datadir       | /data/mysql-tdds/ |
+---------------+-------------------+
1 row in set (0.00 sec)

系统数据库

• mysql

  它存储了 MySQL 的用户账户和权限信息，一些存储过程、事件的定义信息，一些运行过程中产生的日志信息，一些帮助信息以及时区信息等。

• information_schema

  存储了 MySQL 服务器维护的所有其他数据库的信息，比如有表、视图、触发器、列、索引等信息，这些并不是真实的用户数据，而是一些描述性信息，有时候也称之为元数据。

• performance_schema

  存储了 MySQL 服务器运行过程中的一些状态信息，算是对 MySQL 服务器的一个性能监控。包括统计最近执行了哪些语句，在执行过程的每个阶段都花费了多长时间，内存的使用情况等。

• sys

  主要是通过视图的形式把 information_schema 和 performance_schema 结合起来，让程序员可以更方便的了解 MySQL 服务器的一些性能。

目录结构

数据库

每个数据库都对应数据目录下的一个子目录，或者说对应一个文件夹。

1
2
3
4
5
6
7
8
9
10
11
12

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| employees          |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.01 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

.
├── auto.cnf
├── error.log
├── ib_buffer_pool
├── ibdata1
├── ib_logfile0
├── ib_logfile1
├── ibtmp1
├── mysql
├── mysql-binlog.000615
├── mysql-binlog.000616
├── mysql-binlog.000617
├── ...
├── mysql-binlog.index
├── mysql.pid
├── mysql.sock
├── mysql.sock.lock
├── performance_schema
├── slow.log
├── slow_query.log
├── sys
└── tdm

• ibdata1：系统表的空间文件，undo 信息保存在系统文件 ibdata1 。
• ib_logfile0：保存 InnoDB 引擎表的事务日志信息，其文件大小尺寸固定，不可以改变。
• *.index：索引文件，用于记录哪些日志文件正在被使用。
• mysql-binlog.*：记录数据库所有的 DDL 和 DML（除了数据查询语句）语句事件。

表

InnoDB表

1
2
3
4
5
6
7
8
9
10
11
12
13
14

.
├── db.opt
├── test.frm
└── test.ibd

# db.opt：这个文件中包含了该数据库的各种属性，例如：数据库的字符集和比较规则
default-character-set=utf8
default-collation=utf8_general_ci

# 表结构文件：存储表名称、列类型、约束条件、字符集等专门用于描述表结构的文件
test.frm

# InnoDB表数据存储文件
test.ibd

系统表空间（system tablespace）

ibdata1 是一个自扩展文件，默认 12M，配置文件可配置任意大小，不够用的时候会自己增加文件大小

1
2

innodb_data_home_dir  = /data/mysql
innodb_data_file_path = ibdata1:1G:autoextend

Tips：从 MySQL 5.5.7 到 MySQL 5.6.6 之间的各个版本中，我们表中的数据都会被默认存储到这个系统表空间

独立表空间（file-per-table tablespace）

在 MySQL 5.6.6 以及之后的版本，InnoDB 并不会默认的把各个表的数据存储到系统表空间中，而是为每一个表建立一个独立表空间。

1

test.ibd  # 用来存储test表中的数据和索引

innodb_file_per_table：指定使用系统表空间还是独立表空间来存储数据，该参数只对新建的表起作用，对于已经分配了表空间的表并不起作用。

• 当 innodb_file_per_table 的值为 0 时，代表使用系统表空间

• 当 innodb_file_per_table 的值为 1 时，代表使用独立表空间

1
2
3
4
5
6
7
8
9
10
11
12
13

mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

# test表从独立表空间移动到系统表空间
ALTER TABLE test TABLESPACE innodb_system;

# test表从系统表空间移动到独立表空间
ALTER TABLE test TABLESPACE innodb_file_per_table;

MyISAM表

MyISAM 并没有什么所谓的表空间一说，表数据都存放到对应的数据库子目录下，在 MyISAM 中的索引全部都是二级索引，该存储引擎的数据和索引是分开存放的，所以在文件系统中也是使用不同的文件来存储数据文件和索引文件。

1
2
3

test.frm  # 表结构文件
test.MYD  # 表数据文件
test.MYI  # 表索引文件

视图

视图是虚拟的表，也就是某个查询语句的一个别名而已，所以在存储视图的时候是不需要存储真实的数据的，只需要把它的结构存储起来就行了。和表一样描述视图结构的文件也会被存储到所属数据库对应的子目录下边，只会存储一个 视图名.frm 的文件。

1
2
3
4
5
6
7
8
9
10
11

.
├── db.opt
├── host_summary_by_file_io.frm
├── host_summary_by_file_io_type.frm
├── host_summary_by_stages.frm
├── host_summary_by_statement_latency.frm
├── host_summary_by_statement_type.frm
├── host_summary.frm
├── innodb_buffer_stats_by_schema.frm
├── innodb_buffer_stats_by_table.frm
└── innodb_lock_waits.frm

其他文件

数据目录除了存储数据文件以外，还包括为了更好运行程序的一些额外文件，主要包括这几种类型的文件：

进程文件

MySQL 服务器会把自己的进程 ID 写入到一个文件中 mysql.pid

日志文件

在 mysqld 服务运行过程中，会产生各种各样的日志，例如：查询日志、错误日志、二进制日志、redo 日志等

如何修改数据存储目录？

背景：随着数据量的增长，MySQL 数据所占的空间会越来越大，而默认情况下 MySQL 数据盘空间可能不够，这时候，就需要修改存储路径。

实施操作

• 停服务

1
2

/etc/init.d/mysqld stop
mysqladmin -S /tmp/mysql.sock -u'xx' -p'xx' shutdown

• 迁移目录

1
2
3

mkdir /data_sdb/mysql
cp -r /data/mysql/* /data_sdb/mysql
chown -R mysql:mysql /data_sdb/mysql

• 修改配置文件 my.cnf
• 启动服务
• 重新配置主从关系

1
2
3
4
5
6
7

CHANGE MASTER TO
MASTER_HOST='10.200.17.40',
MASTER_USER='repl',
MASTER_PASSWORD='Changeme_123',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000009',
MASTER_LOG_POS=4;

概述

InnoDB 是事务安全的 MySQL 存储引擎，通常来说，InnoDB 存储引擎是 OLTP 应用中核心表的首选存储引擎。被包括在 MySQL 数据库所有二进制发行版本中，从 MySQL 5.5 版本开始是默认的表存储引擎，其特点是行锁设计、支持 MVCC、支持外键、提供一致性非锁定读。

InnoDB 体系架构

后台线程

主要作用是负责刷新内存池中的数据，保证缓冲池中的内存缓存是最近的数据。此外将已修改的数据文件刷新到磁盘文件，同时保证在数据库发生异常的情况下 InnoDB 能恢复到正常运行状态。

• Master Thread

  最核心的后台线程，主要负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性，包括：脏页的刷新、合并插入缓冲（Insert Buffer）、Undo 页的回收等。

• IO Thread

  在 InnoDB 存储引擎中大量使用了 AIO（Async IO）来处理写 IO 请求，这样可以极大提高数据库的性能。而 IO Thread 的工作主要是负责这些 IO 请求的回调处理。共有四个线程：insert buffer thread、log thread、read thread、write thread。

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

  *************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 2020-03-22 13:39:29 0x7f1f6e651700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 17 seconds ... -------- FILE I/O -------- I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) I/O thread 2 state: waiting for completed aio requests (read thread) I/O thread 3 state: waiting for completed aio requests (read thread) I/O thread 4 state: waiting for completed aio requests (read thread) I/O thread 5 state: waiting for completed aio requests (read thread) I/O thread 6 state: waiting for completed aio requests (write thread) I/O thread 7 state: waiting for completed aio requests (write thread) I/O thread 8 state: waiting for completed aio requests (write thread) I/O thread 9 state: waiting for completed aio requests (write thread) Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] , ibuf aio reads:, log i/o's:, sync i/o's: Pending flushes (fsync) log: 0; buffer pool: 0 4407465 OS file reads, 16740662 OS file writes, 580038 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s

• Purge Thread

  事务被提交后，其所使用的 undolog 可能不再需要，因此需要 Purge Thread 来回收已经使用并分配的 undo 页。

  1 2 3 4 5 6

  mysql> show variables like 'innodb_purge_threads'; +----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | innodb_purge_threads | 4 | +----------------------+-------+

• Page Cleaner Thread

  其作用是将之前版本中脏页的刷新操作都放入到单独的线程中完成，进一步提高 InnoDB 存储引擎的性能。

内存

包括：缓冲池、Change 缓冲区、自适应哈希索引、Log 缓冲区。

缓冲池（Buffer Pool）

InnoDB 存储引擎是基于磁盘存储的，并将其中的记录按照页的方式进行管理。在数据库系统中，由于 CPU 速度和磁盘速度之间的鸿沟，基于磁盘的数据库系统通常使用缓冲池技术来提高数据库的整体性能。

缓冲池简单来说就是一块内存区域，通过内存的速度来弥补磁盘速度较慢对数据库性能的影响。

1）在数据库中进行读取的操作，首先将从磁盘读到的页存放在缓冲池中，下一次再读取相同的页时，首先判断该页是否在缓冲池中，若存在，称该页在缓冲池中被命中，直接读取该页，否则，读取磁盘上的页；

2）在数据中修改页的操作，首先修改在缓冲池中的页，然后再以一定的频率刷新到磁盘上，需要注意的是，页从缓冲池刷新回磁盘的操作并不是在每次页发生更新时触发，而是通过一种称为 CheckPoint 的机制刷新回磁盘。这也是为了提高数据库的整体性能。

对于 InnoDB 存储引擎而言，缓冲池的配置通过 innodb_buffer_pool_size 来设置，其大小直接影响着数据库的整体性能。

1
2
3
4
5
6

mysql> show variables like 'innodb_buffer_pool_size';
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| innodb_buffer_pool_size | 5368709120 |
+-------------------------+------------+

需要注意的是，缓冲池不只是缓存索引页和数据页，还包括：undo 页、插入缓冲、自适应哈希索引、锁信息、数据字典信息等。

另外，InnoDB 支持通过参数 innodb_buffer_pool_instances 配置多个缓冲池实例，好处是减少数据库内部的资源竞争，增加数据库的并发处理能力。

1
2
3
4
5
6

mysql> show variables like 'innodb_buffer_pool_instances';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| innodb_buffer_pool_instances | 4     |
+------------------------------+-------+

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67

----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size   81910
Free buffers       1025
Database pages     76901
Old database pages 28367
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4517686, not young 110552906
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1130723, created 2329581, written 4062902
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 76901, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   81920
Free buffers       1024
Database pages     76926
Old database pages 28376
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4518861, not young 130929511
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1144688, created 2335934, written 4047366
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 76926, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size   81910
Free buffers       1024
Database pages     76935
Old database pages 28379
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4466965, not young 100959189
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1003115, created 2300998, written 4047005
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 76935, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size   81920
Free buffers       1024
Database pages     76941
Old database pages 28382
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4496335, not young 103531776
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1128907, created 2334835, written 4138684
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 76941, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

LRU

通常来说，数据库中的缓冲池是通过 LRU（Latest Recent Used，最近最少使用）算法进行管理。即最频繁使用的页在 LRU 列表的最前端，而最少使用的页在 LRU 列表的尾端。当缓冲池不能存放新读取到的页时，将首先释放 LRU 列表尾端的页。

InnoDB 存储引擎中，缓冲池中页的大小默认为 16KB，稍有不同的是 InnoDB 存储引擎对传统的 LRU 算法做了一些优化，在 LRU 列表中还加入了 Midpoint 位置，新读取到的页，虽然是最新访问的页，但并不是直接放入到 LRU 列表的首部，而是放入到 Midpoint 位置。默认配置下，该位置在 LRU 列表长度的 5/8 处。Midpoint 位置可由 innodb_old_blocks_pct 控制。可以理解为 New Sublist 中的页都是最为活跃的热点数据。参数 innodb_old_blocks_time 用于表示页读取到 Midpoint 位置后需要等待多久才会被加入到 LRU 列表的热端。

1
2
3
4
5
6
7

mysql> show variables like 'innodb_old_blocks%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_old_blocks_pct  | 37    |
| innodb_old_blocks_time | 1000  |
+------------------------+-------+

通常情况下，Buffer pool hit rate 不应该小于 95%，如果发生用户需要排查释放由于全表扫描引起 LRU 列表被污染的问题。

Change Buffer

在 LRU 列表中的页被修改后，称该页为脏页（dirty page），即缓冲池中的页和磁盘上的页的数据产生了不一致。这时数据库会通过 CheckPoint 机制将脏页刷新回磁盘，而 Flush 列表中的页即为脏页列表。需要注意的是，脏页既存在于 LRU 列表中，也存在于 Flush 列表中。LRU 列表用来管理缓冲池中页的可用性，Flush 列表用来管理将页刷新回磁盘，二者互不影响。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 2198863872
Dictionary memory allocated 776332
Buffer pool size   131072
Free buffers       124908
Database pages     5720
Old database pages 2071
Modified db pages  910
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4, not young 0
0.10 youngs/s, 0.00 non-youngs/s
Pages read 197, created 5523, written 5060
0.00 reads/s, 190.89 creates/s, 244.94 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not
0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read
ahead 0.00/s
LRU len: 5720, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

1
2
3
4
5
6
7
8
9
10

mysql> SELECT (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
       WHERE PAGE_TYPE LIKE 'IBUF%') AS change_buffer_pages, 
       (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE) AS total_pages,
       (SELECT ((change_buffer_pages/total_pages)*100)) 
       AS change_buffer_page_percentage;
+---------------------+-------------+-------------------------------+
| change_buffer_pages | total_pages | change_buffer_page_percentage |
+---------------------+-------------+-------------------------------+
|                  25 |        8192 |                        0.3052 |
+---------------------+-------------+-------------------------------+

Redo Log Buffer

InnoDB 存储引擎首先将重做日志信息放入到这个缓冲区，然后再按一定的频率将其刷新到重做日志文件。该值可由 innodb_log_buffer_size 控制。

1
2
3
4
5
6

mysql> show variables like 'innodb_log_buffer_size';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| innodb_log_buffer_size | 16777216 |
+------------------------+----------+

重做日志在下面三种情况下会将重做日志缓冲内容刷新到外部磁盘的重做日志文件中：

• Master Thread 每一秒将重做日志缓冲刷新到重做日志文件；
• 每个事务提交时会将重做日志缓冲刷新到重做日志文件；
• 当重做日志缓冲池剩余空间小于 1/2 时，重做日志缓冲刷新到重做日志文件；

InnoDB 关键特性

• 插入缓冲（Insert Buffer）
• 两次写（Double Write）
• 自适应哈希索引（Adaptive Hash Index）
• 异步 IO（Async IO）
• 刷新临近页（Flush Neighbor Page）

简介

字符集：计算机中只能存储二进制数据，那字符串该如何存储字符串呢？是靠建立字符与二进制数据的映射关系，将一个字符映射成一个二进制数据的过程叫做编码，将一个二进制数据映射到一个字符的过程叫做解码，抽象出一个字符集的概念来描述某个字符范围的编码规则。

比较规则：英文名为 collation，用于比较两个字符的大小。

常用字符集

• ASCII 字符集

  共收录 128 个字符，包括空格、标点符号、数字、大小写字母和一些不可见字符。

  1 2	'L' -> 01001100（十六进制：0x4C，十进制：76） 'M' -> 01001101（十六进制：0x4D，十进制：77）

• ISO 8859-1 字符集

  共收录 256 个字符，是在 ASCII 字符集的基础上又扩充了 128 个西欧常用字符（包括德法两国的字母），这个字符集也有一个别名 latin1。

• utf8 字符集

  收录地球上能想到的所有字符，而且还在不断扩充。这种字符集兼容 ASCII 字符集，采用变长编码方式，编码一个字符需要使用 1～4 个字节，比方说这样：

  1 2	'L' -> 01001100（十六进制：0x4C） '啊' -> 111001011001010110001010（十六进制：0xE5958A）

  Tips： utf8 只是 Unicode 字符集的一种编码方案，Unicode 字符集可以用 utf8、utf16、utf32 这几种编码方案，utf8 使用 1～4 个字节编码一个字符。

查看字符集（character set/charset）

1
2
3
4
5
6
7

mysql> show charset like 'utf8%';
+---------+---------------+--------------------+--------+
| Charset | Description   | Default collation  | Maxlen |
+---------+---------------+--------------------+--------+
| utf8    | UTF-8 Unicode | utf8_general_ci    |      3 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci |      4 |
+---------+---------------+--------------------+--------+

查看比较规则（collation）

1
2
3
4
5
6
7

mysql> show collation like 'utf8%general_ci';
+--------------------+---------+----+---------+----------+---------+
| Collation          | Charset | Id | Default | Compiled | Sortlen |
+--------------------+---------+----+---------+----------+---------+
| utf8_general_ci    | utf8    | 33 | Yes     | Yes      |       1 |
| utf8mb4_general_ci | utf8mb4 | 45 | Yes     | Yes      |       1 |
+--------------------+---------+----+---------+----------+---------+

utf8 字符集默认的比较规则是 utf8_general_ci，它是一种通用的比较规则，_ci 结尾，表示不区分大小写。

字符集和比较规则

MySQL 有四个级别的字符集和比较规则，分别是：

• 服务器级别
• 数据库级别
• 表级别
• 列级别

1
2
3
4
5
6
7
8
9
10
11
12
13

mysql> show variables like 'character%';
+--------------------------+----------------------------------------------------+
| Variable_name            | Value                                              |
+--------------------------+----------------------------------------------------+
| character_set_client     | utf8                                               |
| character_set_connection | utf8                                               |
| character_set_database   | utf8                                               |
| character_set_filesystem | binary                                             |
| character_set_results    | utf8                                               |
| character_set_server     | latin1                                             |
| character_set_system     | utf8                                               |
| character_sets_dir       | /usr/local/mysql-5.7.23-el7-x86_64/share/charsets/ |
+--------------------------+----------------------------------------------------+

• character_set_server： 默认的内部操作字符集
• character_set_client： 服务器解码请求时使用的字符集
• character_set_connection：连接层字符集
• character_set_results： 服务器向客户端返回数据时使用的字符集
• character_set_database： 当前选中数据库的默认字符集
• character_set_system： 系统元数据(字段名等)字符集

服务器级别

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

# 控制参数
character_set_server
collation_server

# 配置文件设置
[client]
default-character-set  = utf8

[mysql]
default-character-set  = utf8

[mysqld]
character-set-server   = utf8
collation-server       = utf8_general_ci

# 查看方法
mysql> SHOW VARIABLES LIKE 'character_set_server';
+----------------------+--------+
| Variable_name        | Value  |
+----------------------+--------+
| character_set_server | utf8   |
+----------------------+--------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'collation_server';
+------------------+-------------------+
| Variable_name    | Value             |
+------------------+-------------------+
| collation_server | utf8_general_ci   |
+------------------+-------------------+
1 row in set (0.00 sec)

数据库级别

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

# 控制参数
character_set_database
collation_database

# 创建语法
CREATE DATABASE 数据库名
    [[DEFAULT] CHARACTER SET 字符集名称]
    [[DEFAULT] COLLATE 比较规则名称];

ALTER DATABASE 数据库名
    [[DEFAULT] CHARACTER SET 字符集名称]
    [[DEFAULT] COLLATE 比较规则名称];
    
# 查看方法
mysql> use test;
mysql> SELECT @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8                     | utf8_general_ci      |
+--------------------------+----------------------+

规则

• 创建数据库时，指定了 CHARACTER SET 或 COLLATE，则以对应的字符集、比较规则为准。
• 创建数据库时，没有指定字符集、排序规则，则以 character_set_server、collation_server 为准。
• character_set_database 和 collation_database 这两个系统变量是只读的，不能通过修改这两个变量的值而改变当前数据库的字符集和比较规则。

表级别

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

# 创建语法
CREATE TABLE 表名 (列的信息)
    [[DEFAULT] CHARACTER SET 字符集名称]
    [COLLATE 比较规则名称]]

ALTER TABLE 表名
    [[DEFAULT] CHARACTER SET 字符集名称]
    [COLLATE 比较规则名称]

# 查看方法
mysql> show table status from test\G
*************************** 1. row ***************************
           Name: test
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 5
 Avg_row_length: 3276
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 6
    Create_time: 2019-09-24 18:00:54
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment:

规则

• 明确了 charset_name、collation_name，则采用 charset_name、collation_name。
• charset_name、collation_name 均未明确，则采用数据库的字符集、比较规则设置。

列级别

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# 创建语法
CREATE TABLE 表名(
    列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称],
    其他列...
);

ALTER TABLE 表名 MODIFY 列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称];

# 查看方法
mysql> SELECT CHARACTER_SET_NAME, COLLATION_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA="test" AND TABLE_NAME="test" AND COLUMN_NAME="name";
+--------------------+-----------------+
| CHARACTER_SET_NAME | COLLATION_NAME  |
+--------------------+-----------------+
| utf8               | utf8_general_ci |
+--------------------+-----------------+

规则

• 明确了 charset_name、collation_name 均明确，则采用 charset_name、collation_name 为准。
• charset_name、collation_name 均未明确，则以表的字符集、比较规则为准。

小结

• 如果创建或修改列时没有显式的指定字符集和比较规则，则该列默认用表的字符集和比较规则
• 如果创建或修改表时没有显式的指定字符集和比较规则，则该表默认用数据库的字符集和比较规则
• 如果创建或修改数据库时没有显式的指定字符集和比较规则，则该数据库默认用服务器的字符集和比较规则

总结

• 字符集是某个字符范围的编码规则。
• 比较规则是针对某个字符集中的字符比较大小的一种规则。
• MySQL 中一个字符集可以有若干种比较规则，其中有一个默认的比较规则。
• MySQL 有四个级别的字符集和比较规则，优先级排序：列 > 表 > 库 > 服务器。

配置文件

MySQL 默认配置文件：/etc/my.cnf，自动 生成工具

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

[client]
port                      = 3306
socket                    = /data/mysql/mysql.sock
default-character-set     = utf8

[mysql]
default-character-set     = utf8
no-auto-rehash

[mysqld]
port                      = 3306
basedir                   = /usr/local/mysql
datadir                   = /data/mysql
socket                    = /data/mysql/mysql.sock
pid-file                  = /data/mysql/mysql.pid
character-set-server      = utf8
collation-server          = utf8_general_ci
skip_name_resolve

# LOG
slow_query_log            = 1
long_query_time           = 5
log_error                 = /data/mysql/error.log
slow_query_log_file       = /data/mysql/slow.log

# INNODB
innodb_data_home_dir      = /data/mysql
innodb_buffer_pool_size   = 10240M
innodb_log_file_size      = 2G
innodb_data_file_path     = ibdata1:1G:autoextend
innodb_buffer_pool_instances = 4
innodb_max_undo_log_size  = 4G

# OTHER
back_log                  = 1024
max_connections           = 1000
interactive_timeout       = 600
wait_timeout              = 600
sort_buffer_size          = 16M
join_buffer_size          = 16M
lower_case_table_names    = 1
log_timestamps            = SYSTEM
explicit_defaults_for_timestamp = 1
log_queries_not_using_indexes   = 1

server-id                 = 3306100
log-bin                   = /data/mysql/mysql-bin
sync_binlog               = 1
max_binlog_size           = 1024M
binlog-format             = row
expire_logs_days          = 7

• [mysql]：该部分由 MySQL 命令行客户端读取
• [client]：该部分由所有连接的客户端读取（包括 mysql cli）
• [mysqld]：该部分由 MySQL 服务器读取
• [mysqldump]：该部分由 mysqldump 命令读取
• [mysqld_safe]：该部分由 mysqld_safe 命令读取

参数类型

• 静态参数：重启 MySQL 服务后才能使之生效。
• 动态参数：可以在不重新启动 MySQL 服务的情况下立即生效。

参数详解

• MySQL · 配置参数 · innodb_buffer_pool_size
• MySQL · 配置参数 · max_connections

CHAR ：是一种定长字符串类型，并且必须在圆括号内用一个大小修饰符来定义，这个大小修饰符的范围从 0～255。比指定长度大的值将被截短，而比指定长度小的值将会用空格作填补。

VARCHAR ：是一种可变长度的字符串类型，并且也必须带有一个范围指示器。

选择

选择最小的数据范围，因为这样可以大大减少磁盘空间及磁盘 I/O 读写开销，减少内存占用，减少CPU占用率

MySQL 中常用的两种时间储存类型分别是 datetime 和 timestamp，建表时该如何选择呢？

占用空间

表示范围

时区

• timestamp 占 4 个字节，而且是以 utc 格式储存，它会自动检索当前时区并进行转换，存和取可能不一样。

• datetime 占 8 个字节储存，不会进行时区的检索，存和取一样。

• 区别：如果存进去的是 NULL，timestamp 会自动储存当前时间，而 datetime 会储存 NULL。

测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

# 创建测试表
create table timedemo(st timestamp,dt datetime);

# 插入数据
insert into timedemo values (null,null), (now(),now()), ('20161001193450','20161001193450');

# timestamp 会自动储存当前时间，而 datetime 会储存 NULL
mysql> select * from timedemo;
+---------------------+---------------------+
| st                  | dt                  |
+---------------------+---------------------+
| 2019-09-24 14:00:15 | NULL                |
| 2019-09-24 14:00:15 | 2019-09-24 14:00:15 |
| 2016-10-01 19:34:50 | 2016-10-01 19:34:50 |
+---------------------+---------------------+

# 将时区修改为东9区，会发现 timestamp 比 datetime 多一小时
mysql> set time_zone='+9:00';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like "time_zone";
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| time_zone     | +09:00 |
+---------------+--------+
1 row in set (0.00 sec)

mysql> select * from timedemo;
+---------------------+---------------------+
| st                  | dt                  |
+---------------------+---------------------+
| 2019-09-24 15:00:15 | NULL                |
| 2019-09-24 15:00:15 | 2019-09-24 14:00:15 |
| 2016-10-01 20:34:50 | 2016-10-01 19:34:50 |
+---------------------+---------------------+
3 rows in set (0.00 sec)

选择

• 如果在时间上要超过 Linux 时间的，或者服务器时区不一样的就建议选择 datetime。
• 如果是想要使用自动插入时间或者自动更新时间功能，可以使用 timestamp。
• 存储年使用 year，存储日期使用 date。

场景

常见有以下两种场景：

• 主从复制中断，主 binlog 缺失，需要重新搭建从库
• 单主模式，升级为主从模式

方案

主从同版本场景，使用 Percona XtraBackup 工具不停机不锁表在线搭建从库

操作步骤

• 主从服务器，安装 XtraBackup

• 配置互信

• 备份主库

1

$ innobackupex --defaults-file=/etc/my.cnf --user='xx' --password='xx' --no-timestamp /data/backup

Tips：备份目录要依据服务器磁盘分配，否则会占满磁盘，影响业务。

• 将备份传输至从库

1

$ scp -r /data/backup/* root@x.x.x.x:/data/backup

• 恢复备份至从库数据目录

1
2
3
4
5

$ mysqladmin -uroot -p'xx' shutdown
$ innobackupex --defaults-file=/etc/my.cnf --apply-log /data/backup
$ mv mysql mysql_bak
$ mkdir mysql
$ innobackupex --defaults-file=/etc/my.cnf --copy-back /data/backup

• 启动 mysqld

1
2

$ chown -R mysql:mysql mysql
$ /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql >/dev/null &

• 配置主从复制关系

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

# 获取Master binlog位置
$ cat xtrabackup_binlog_info
mysql-binlog.000297	900834433

# 配置复制
CHANGE MASTER TO 
MASTER_HOST='x.x.x.x',
MASTER_USER='repl',
MASTER_PASSWORD='Changeme_123',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000297', 
MASTER_LOG_POS=900834433,
MASTER_CONNECT_RETRY=10;

# 启动同步
mysql> start slave;

• 检查是否配置成功

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.133.7.5
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000271
          Read_Master_Log_Pos: 945378251
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 230109
        Relay_Master_Log_File: mysql-bin.000271
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                           .....

实践案例