MySQL 备份

细致的备份让人可以安心睡觉

为什么要备份

我们第一点想到的是灾难恢复，即使是对技术不感兴趣的领导，也会很自然的想到这一点, 他们经常会担忧万一出了事故，数据是否安全，系统是否能够即时从事故中恢复。对于我们开发人员来说，备份还有一个很重要的作用就是用于开发和测试，我们开发人员经常需要使用最新的生产环境数据来更新测试服务器，我们需要定期把备份文件还原到测试服务器以供开发和测试。

备份之前的信息收集

在备份之前我们需要尽可能地收集相关的信息，比如服务器的版本，有哪些定时任务在操作数据库, 有哪些应用和数据库关联等等, 将这些信息列一个清单以方便以后查阅对照. 对于数据库本身的信息，我们需要确认数据库的大小，使用了那些存储引擎等。我们可以使用下面的 sql 查询需要的数据库信息, 以方便对数据库进行审核:

  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)/104/1024) AS index_mb,
		 COUNT(*) as tables
  FROM information_schema.tables
  GROUP BY table_schema, engine
  ORDER BY 3 DESC;
  

在我本地执行结果如下:

+--------------------+--------------------+----------+---------+----------+--------+
| table_schema       | engine             | total_mb | data_mb | index_mb | tables |
+--------------------+--------------------+----------+---------+----------+--------+
| meihe              | InnoDB             |       63 |      44 |      190 |     86 |
| mysql              | InnoDB             |       10 |      10 |        0 |      9 |
| peatio_development | InnoDB             |        1 |       1 |        4 |     35 |
| mysql              | MyISAM             |        1 |       1 |        1 |     22 |
| information_schema | MEMORY             |        0 |       0 |        0 |     51 |
| mysql              | CSV                |        0 |       0 |        0 |      2 |
| sys                | InnoDB             |        0 |       0 |        0 |      1 |
| performance_schema | PERFORMANCE_SCHEMA |        0 |       0 |        0 |     87 |
| information_schema | InnoDB             |        0 |       0 |        0 |     10 |
| sys                | NULL               |     NULL |    NULL |     NULL |    100 |
+--------------------+--------------------+----------+---------+----------+--------+
10 rows in set, 48 warnings (0.32 sec)

我们发现数据库使用了多种存储引擎，有的使用 InnoDB, 有的使用 MyISAM, 不同的引擎会对我们的备份参数产生影响，比如锁策略。

我们要备份的对象长什么样

在备份前，我们需要再做一些额外的工作，比如熟悉 my.cnf, mysql 数据库文件的目录结构等.

my.cnf 是 mysql 的配置文件, 网上有很多样本文件，这里不详细贴出来了，我们列举下其中和备份比较相关的选项:

datadir 是所有数据库, 表, InnoDB, 数据, 服务器日志和二进制日志文件在文件系统上的存储目录.
basedir 是 MySQL 的安装目录在文件系统中的位置.
innodb_data_file_path 这个变量确定单个 InnoDB 数据文件的位置以及它们的大小和行为.
innodb_file_per_table 当 innodb_file_per_table 被激活时，所有使用 InnoDB 存储引擎创建的表都将创建自己的表空间.
innodb_flush_log_at_trx_commit 默认值是 1, 意味着每次提交后都把日志缓冲区的内容写到 InnoDB 日志文件中, 并在该日志文件上执行清除磁盘操作. 设置为 2, 则意味着将以每秒一次的松散时间隔将日志缓冲区中的内容写到 InnoDB 日志文件中，不推荐使用 0.
server_id 给 MySQL 拓扑中的每一台 slave 服务器设置一个唯一的 server_id 值将确保复制数据能被正确应用.
log_bin 激活二进制日志
log_bin_index 文件保留二进制日志名并且以之为索引
binlog_format 使用这个变量可以控制 MySQL 所使用的二进制日志类型
binlog_cache_size 这个缓存用于在事物过程中保存二进制日志的变化
binlog_stmt_cache_size 这个变量为二进制日志指定缓冲区大小
relay_log 在 SQL 线程把被复制的数据库变化应用到从服务器之前, MySQL 使用一个编了号的被称为中继日志的文件集合来保留它们.
relay_log_index 这个变量保留所有中继日志的名称以便快速查找.
slave_skip_errors 当指定了 slave_skip_errors 时, 复制错误代码可以被自动跳过.
slave_exec_mode 它有两个有效值: IDEMPOTENT 和 STRICT. 这个变量用于解析复制冲突和检查错误.
log_slave_updates 当把此变量设置为真并把二进制日志文件打开时, slave 服务器主机将把所有被复制的变化写到自己的二进制日志文件中.

备份的分类

1.物理备份和逻辑备份

物理备份 : 它是直接拷贝mysql的存储在磁盘上的数据文件。这种备份使用的空间大，特别是innodb存储数据使用的表空间，因为表空间分配大小是按一定的比例增长，所以存在没有使用的空间。但是恢复时使用的时间少。
逻辑备份 : 将数据以sql语句的形式导出。导出数据和恢复数据所用的时间多，但占用空间小。

2.热备、温备、冷备

热备 : 对线上环境不见任何锁，不影响线上任何数据库操作。
温备 : 在备份之间要获取锁，并施加锁。在加锁期间会影响某些操作。
冷备 : 备份之前停掉服务器，然后在备份。

备份工具的选择和使用

作为一名开发人员，最熟悉的备份工具可能就是 mysqldump 了, 在项目的初期, 我经常使用 mysqldump 同步一些 staging 环境的数据到本地。

mysqldump

1. 最简单的方式

$ mysqldump -uroot --all-databases > backup.sql  

mysqldump 命令产生了一个 ASCII 转储文件，保存为 backup.sql

2. 远程备份

$ mysqldump -u<user> -p<password> --host db-server --compress --all-database > backup.sql

3. 使用 SSH

使用带有密钥认证的标准 SSH, 可以使备份自动地通过网络进行传输. 在下面的例子中, SSH 连接已经被简化到了只需要使用别名即可进行备份:

$ cat $HOME/.ssh/config

Host backup
IdentityFile /home/ubuntu/.ssh/admin.pem
User ubuntu
HostName db_server.com


$ mysqldump --all-database \| ssh backup "cat -> backup.sql"

4. 使用 nc

通过利用 netcat(nc), 可以在一个给定端口上直接通过 TCP/UDP 来传递文件.

$ ssh backup "nc -l 9306" > /mysql/backup/nc/first.sql" & mysqldump -all-database | nc backup 9306

在项目开始的阶段，我们使用 mysqldump 来备份数据库是合适的，但是如果项目发展的很好，数据库越来越庞大时，我们就需要更加专业的工具来备份数据库。

使用 XtraBackup

官网: https://www.percona.com/software/mysql-database/percona-xtrabackup Xtrabackup是由percona提供的mysql数据库备份工具，据官方介绍其特点如下：

Fast and reliable database backups (e.g. hot backup, incremental backups, bacula backup, etc.)
Uninterrupted transaction processing during backups
Savings on disk space and network bandwidth with better compression
Automatic backup verification
Higher uptime due to faster restore time
Point-in-time recovery

Xtrabackup中主要包含两个工具：

xtrabackup：是用于热备份innodb, xtradb表中数据的工具，不能备份其他类型的表，也不能备份数据表结构；

innobackupex：是将xtrabackup进行封装的perl脚本，提供了备份myisam表的能力。

常用命令

默认情况下备份是在指定的 backup-dir 目录下创建一个时间戳目录，然后备份集写入到时间目录下:

$ innobackupex --host=127.0.0.1 --port=3306 --user=root --password=123456 --defaults-file=/etc/my.cnf backup-dir

也可以通过流方式输出到标准输出:

$ innobackupex --host=127.0.0.1 --port=3306 --user=root --password=123456 --defaults-file=/etc/my.cnf --stream=tar backup-dir | gzip > backup.tar.gz