读书笔记-Effective MySQL Replication Techniques in Depth - Ronald Bradford
第一章 五分钟成为一名DBA
确定数据库的大小
- 通过下面的SQL,可以得到当前的数据和索引的总大小(以MB为单位)
1 | SELECT ROUND(SUM(data_length + index_length)/1024/1024) AS total_mb,ROUND(SUM(data_length)/1024/1024) AS data_mb,ROUND(SUM(index_length)/1024/1024) AS index_mb |
选择锁策略
-
所选择的锁策略将决定在执行备份期间,应用程序是否可以对数据库执行写操作.默认情况下,
mysqldump
利用LOCK TABLES
命令进行表级加锁,以便确保所有数据有一个一致的版本. -
下面的SQL语句将确认当前MySQL实例所使用的存储引擎
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19SELECT table_schema,engine,COUNT(*) AS tables
FROM INFORMATION_SCHEMA.tables
WHERE table_schema NOT IN ('INFORMATION_SCHEMA','PERFORMANCE_SCHEMA')
GROUP BY table_schema,engine
ORDER BY 3 DESC;
+--------------------+--------------------+--------+
| TABLE_SCHEMA | ENGINE | tables |
+--------------------+--------------------+--------+
| performance_schema | PERFORMANCE_SCHEMA | 111 |
| sys | NULL | 100 |
| information_schema | NULL | 79 |
| mysql | InnoDB | 36 |
| book | InnoDB | 2 |
| mysql | CSV | 2 |
| book | MyISAM | 1 |
| sys | InnoDB | 1 |
| test | InnoDB | 1 |
+--------------------+--------------------+--------+
组合信息
1 | SELECT table_schema,engine, |
运行mysqldump
1 | bash-4.4# time mysqldump -uroot -p --all-databases > backup.sql |
- 以
time
命令作为mysqldump
命令的前缀将提供有关实际消耗时间的有用信息.
第二章 理解备份选项
术语
术语 | 描述 |
---|---|
静态备份 | 这是一种在给定时间进行备份的数据备份方式.通常,MySQL备份应当每日进行一次,例如凌晨两点进行 |
一致性备份 | 在这种数据备份方式中,所有与备份有关的信息都是一致的.例如,对正在运行的生产系统的文件系统进行备份,顺序拷贝文件时,就会产生不一致的备份.这可能导致在各个单独的文件之间信息的不匹配. |
静态恢复 | 恢复过程涉及两个重要的初始化步骤: 第一个是静态恢复;第二个是对有效的静态备份的验证 |
时间点恢复(PITR ) |
在进行了成功的静态恢复之后,通常都有必要再进行一次当前事务的PITR恢复.这时所恢复的就是从静态备份开始以来发生的所有数据操作,例如凌晨两点开始 |
维护窗口 | 备份通常是在系统基本无人使用时或某段预先设定的时间里进行,这个时间段就称为维护窗口. |
锁策略
LOCK TABLES
- 可为一个或多个特定的表提供
READ
或WRITE
锁. - 当使用
mysqldump
将--lock-tables
选项设置为enabled
时,本命令才可使用.
- 可为一个或多个特定的表提供
UNLOCK TABLES
UNLOCK TABLES
命令用于释放某个会话的所有当前锁.除了这一命令外,终止一个会话,执行一条START TRANSACTION
命令对同名表执行一条LOCK TABLES
命令也会隐含执行UNLOCK TABLES
命令.- 任何执行时间长于
wait_timeout
或interactive_timeout
选项值的备份,都会导致会话被关闭.这也会隐含执行UNLOCK TABLES
命令
FLUSH TABLES
- 当与可选关键词
WITH READ LOCK
一起使用时,FLUSH TABLES
命令如果能被成功执行的话,将得到数据的一致性视图.这个结果是采用一个全局维持的读锁,然后关闭所有当前打开的表之后才产生的.这个命令的执行需要耗费一定时间,因为它要求所有正在执行的SQL语句都完成才行. - 这个锁与
LOCK TABLES
命令对所有表的列表的锁是完全属于不同类型. - 这个锁可以通过使用
UNLOCK TABLES
命令或执行任何隐含着执行了UNLOCK TABLES
命令的操作而得到释放.
- 当与可选关键词
静态备份选项
- 文件系统冷备份
- SQL导出(dump)
- 表抽取
- 文件系统热快照
mylvmbackup
- InnoDB热备份
文件系统冷备份
- 缺点
- 在备份过程中,MySQL实例不可用
- 恢复过程中需要一个相似的配置系统,这个系统要具有与原系统相同的操作系统和目录结构
- 当重启MySQL时,MySQL的内存池要重新初始化.这会使系统耗费一些时间,以便为运行SQL语句提供最优性能.
- 优点
- 过程简单
- 允许使用任何文件系统备份工具来执行备份
mysqldump
1 | mysqldump -u[user] -p --routines --master-data --complete-insert --all-databases > backup.sql |
- 这个命令创建一个备份,该备份包含所有的表,视图和所存储的有关所有数据库模式和主二进制日志位置的
命令 | 说明 |
---|---|
--single-transaction |
它仅能用在只有InnoDB的环境中或支持MVCC的传统存储引擎中 |
--opt |
本选项的默认状态是enabled ,等同于: --add-drop-table ,--add-locks ,--create-options ,--quick ,--extended-insert ,--lock-tables ,--set-charset 和--disable-keys |
--lock-tables |
这个选项实际上被--opt 选项所暗含,其默认状态是enabled .带有--lock-tables 选项的mysqldump 命令一次只能锁一个模式的所有表,不能锁所有模式的所有表. |
--lock-all-tables,-x |
所有数据库中的所有表加锁.在整体转储过程中通过全局读锁定来实现.该选项自动关闭--single-transaction 和--lock-tables |
--routines,-R |
在转储的数据库中转储存储程序(函数和程序) |
--master-data[=value] |
该选项将二进制日志的位置和文件名写入到输出中.该选项要求有RELOAD权限,并且必须启用二进制日志.如果该选项值等于1,位置和文件名被写入CHANGE MASTER语句形式的转储输出,如果你使用该SQL转储主服务器以设置从服务器,从服务器从主服务器二进制日志的正确位置开始.如果选项值等于2,CHANGE MASTER语句被写成SQL注释.如果value被省略,这是默认动作. |
--all--database,-A |
转储所有数据库中的所有表.与使用--database 选项相同,在命令行中命名所有数据库. |
--complete-insert,-c |
使用包括列名的完整的INSERT语句. |
- 在MySQL中,例程(Routine)是一组预定义的SQL语句的集合,可以通过单个调用来执行.这些例程可以分为三种类型:存储过程(Stored Procedure)、函数(Function)和触发器(Trigger).
- 优点
- 数据是ASCII格式的,可以使用文本编辑器查看备份文件
mysqldump
命令使用MySQL
的c/s协议,所以不一定必须在同一台服务器上执行.有助于减少I/O写入需求及必须的磁盘容量,却会增加命令的执行时间和网络利用.- 支持跨操作系统的兼容解决方案.在
linux
上使用mysqldump
产生的备份可以windows上进行恢复.
二进制日志
1 | mysql> show binary logs; |
--expire-logs-days
选项会在预先设定好的天数之后自动删除这些文件,另外要用PURGE BINARY LOGS
命令从文件系统删除这些文件,而不要用手工方式删除,这点也非常重要,因为在数据库和文件系统之间存在内在的引用关系.
第四章 使用MySQL
复制
1 | SELECT variable_name,variable_value |
本博客所有文章除特别声明外,均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 HoleLin's Blog!