第一章 五分钟成为一名DBA

确定数据库的大小

  • 通过下面的SQL,可以得到当前的数据和索引的总大小(以MB为单位)
1
2
3
4
5
6
7
8
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
FROM INFORMATION_SCHEMA.tables;

+----------+---------+----------+
| total_mb | data_mb | index_mb |
+----------+---------+----------+
|      130 |      87 |       44 |
+----------+---------+----------+

选择锁策略

  • 所选择的锁策略将决定在执行备份期间,应用程序是否可以对数据库执行写操作.默认情况下,mysqldump利用LOCK TABLES命令进行表级加锁,以便确保所有数据有一个一致的版本.

  • 下面的SQL语句将确认当前MySQL实例所使用的存储引擎

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    SELECT 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
2
3
4
5
6
SELECT table_schema,engine,
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,
COUNT(*) AS tables
FROM INFORMATION_SCHEMA.tables
GROUP BY table_schema,engine
ORDER BY 3 DESC;

运行mysqldump

1
2
3
4
5
6
7
8
9
bash-4.4# time mysqldump -uroot -p --all-databases > backup.sql
Enter password: 

real    0m4.271s
user    0m0.532s
sys     0m0.160s

bash-4.4# echo $?
0
  • 以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
2
3
4
5
6
7
8
9
10
mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |   3039831 | No        |
| binlog.000002 |      8472 | No        |
| binlog.000003 |  36918834 | No        |
| binlog.000004 |       822 | No        |
+---------------+-----------+-----------+
4 rows in set (0.00 sec)
  • --expire-logs-days选项会在预先设定好的天数之后自动删除这些文件,另外要用PURGE BINARY LOGS命令从文件系统删除这些文件,而不要用手工方式删除,这点也非常重要,因为在数据库和文件系统之间存在内在的引用关系.

第四章 使用MySQL复制

1
2
3
4
5
SELECT variable_name,variable_value
FROM performance_schema.global_status
WHERE variable_name LIKE 'COM_ALTER%'
OR variable_name LIKE 'COM_DROP%'
OR variable_name LIKE 'COM_CREATE%';