mysql进阶至mysql备份基础及备份工具

一、mysql备份、恢复基础

备份:存储的数据副本

还原：把副本应用到线上系统，仅能恢复至备份操作时刻的数据状态。

硬件上的冗余并不能有效恢复逻辑操作上的错误。

备份：数据备份、服务配置文件备份、系统环境备份。程序的运行依赖于一定的系统环境，仅提供数据本身并不能确保恢复数据之后系统可用，数据备份的目的在于确保系统出现不可预料的灾难事故之后能够快速恢复运行，降低损失。

演练：良好的制度，灾难恢复演练

       测试备份的可用性

       增强恢复操作效果

时间点恢复：mysql需要借助于binary log

2、为什么备份

       灾难恢复：硬件故障、软件故障、自然灾害、黑客攻击、误操作

3、备份时应该注意事项

       能容忍最多丢失多少数据

       恢复数据需要在多上时间内完成

       需要恢复哪些数据

备份时考虑因素：

       锁定 资源多长时间

       备份过程时长

       备份时的服务器负载

       恢复过程时长

4、备份类型

1）根据备份的数据集范围划分：

       完全备份：整个数据库

       部分备份：数据集的一部分，比如数据表

2）根据备份方式划分：

       完全备份:

       增量备份:第一次的备份数据是从上一个完全备份的时间点至当前时间点之间的变化数据，第二次的备份数据是从上一个备份时间点至当前备份时间点之间变化的数据。其数据不会产生冗余，但恢复起来复杂度较大。

       差异备份:从上一次完全备份至下一次完全备份之间的数据，每次的备份数据是上一次完全备份到当前备份时间点的数据的变化。可能会产生数据冗余。 

在上一个备份时间点与下一个备份时间点之间，若服务器发生故障，此时，使用备份文件仅能恢复至上一个备份时间点备份的数据。在上一个备份时间点至故障时间点之间的数据恢复依赖于二进制日志来实现恢复。

3）根据数据服务是否上线

       热备：读写操作均可进行的状态下所做的备份

       温备：可读但不可写状态下进行的备份

       冷备：读写操作均不可进行的状态下所做的备份

4）物理备份、逻辑备份

       物理备份：复制数据文件

       逻辑备份：从数据库中导出数据另存在一个或多个文件中

5、备份策略

       完全+增量+二进制日志

       完全+差异+二进制日志

       备份手段：物理+逻辑

6、备份的内容

       1）数据

       2）二进制日志、innodb的事务日志

       3）代码（存储过程、存储函数、触发器、时间调度器）

       4）服务器的配置文件（定时任务、服务配置文件等）

       5）根复制相关的配置信息

7、备份工具

1）mysqldump：mysql服务自动的备份工具，逻辑备份工具。适用于数据集较小的场景，10G以下。

       完全备份、部分备份

       innodb引擎：支持热备

       myisam引擎：支持温备

       aira引擎：支持温备

缺点：备份和恢复过程较慢

2）lvm-snapshot结合cp/tar

       接近于热备的工具，因为要先请求全局锁，而后创建快照，滨海个在创建快照完成后释放全局锁。之后，使用cp、tar等工具进行物理备份。

       优点：备份、恢复速度较快

       缺点：很难实现增量备份，并且请求全局锁需要等待一段时间，在繁忙的服务器上由其如此。

3）xtrabackup：由percona提供，开源工具，支持对innodb做热备，物理备份工具，速度快。可靠，备份后会自动检查备份是否可用；还原速度快。

       完全备份、部分备份

       完全备份、增量备份

       完全备份、差异备份

       对innodb：支持热备、增量备份

       对myisam：支持温备、不支持增量 

二、mysql备份工具之mysqldump

mysqldump+二进制文件进行备份

客户端工具。需要连接至mysql服务器，可用于备份库，或库下面的某些表

注：导出的是单个库，在恢复时默认不会自动创建库，在恢复还原库时，库必须得事先存在。

导出多个库在恢复是，不需创建库。

用法：mysqldump [options ] [db_name [tbl_name]]

       常用选项：

              –all-databases：备份所有库

              –databases db1 db2…..：备份指定的多个库，即便导出单个库，也不需要事先创建

              –lock-tables：在备份开始前，请求锁定目标表，之后在备份，以实现数据一致

              –lock-all-tables：请求锁定表之后在备份，能实现对innodb、myisam、aria做温备，当存储引擎不是innodb时建议使用

              –single-transaction：对innodb引擎实现热备。与–lock-all-tables不同时使用。

       备份代码常用的选项：

              –events：备份时间调度器代码

              –routines：备份存储过程和存储函数

              –triggers：备份触发器

       备份时滚动二进制日志：

              –flush-logs：备份前，请求到锁之后，滚动日志

       复制时的同步位置标记

              –master-data=[0|1|2]

              0：表示不吉利

              1：表示记录为Change Master语句

              2：表示记录为注释的Change Master语句。

注：在备份前要对备份的目标库或表请求锁

2、使用mysqldump备份步骤

1）请求锁或使用热备：–lock-all-tables或–single-transaction

2）滚动日志

3）请求数据库

4）指定二进制日志文件及其位置：–master-data

~]# mysqldump -uroot -hlocalhost -p --databases hellodb --lock-all-tables --master-data=2 --flush-logs > /tmp/hellodb.new.bak.sql[object Object]

意为当前备份时间点之后的数据变化会记录在二进制日志master.000006文件中的第106位置之后。要恢复数据至进行备份之后的某事件点时，重放106位置之后的日志即可。 

2.1、手动请求全局锁并备份数据的的方式

1）mysql命令：flush tables with read lock；把表中的数据写入磁盘并为表加上读锁。若数据过大，该操作可能会被阻塞一段时间；

2）fulsh logs；

3）之后使用mysqldump进行备份

4）之后unlock tables；解锁。

2.2、直接使用mysqldump进行备份

~]# mysqldump -uroot -hlocalhost -p --databases hellodb --lock-all-tables --master-data=2 --flush-logs > /tmp/hellodb.new.bak.sql

或

~]# mysqldump -uroot -hlocalhost -p --databases hellodb --single-transaction  --master-data=2 --flush-logs > /tmp/hellodb.new.bak.sql

3、恢复方式

恢复过程没必要记录在二进制文件中。

因此，执行恢复操作时需要临时关闭二进制日志。

3.1建议关闭二进制日志记录，关闭其他用户对服务器的写操作（关闭其他用户连接）

mysql> set session sql_log_bin=0;
mysql> show global variables like "%log_bin";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin| ON    |
| sql_log_bin| OFF   |
+---------------+-------+

3.2 导出上一次备份时间点到故障时间点之间的二进制日志至某文件中

注：引起故障的语句要删除

]# mysqlbinlog --start-position=106 --stop-position=265 master.000006 >/tmp/hellodb.bin.sql

3.3 导入数据备份

mysql> source  /tmp/hellodb.bak;

3.4 导入二进制日志

mysql> source /tmp/hellodb.bin.sql;

3.5 检查数据恢复成功与否

3.6 启用二进制日志记录

mysql> set session sql_log_bin=1;

备份策略：

       每周一做一次完全备份：备份的同时滚动日志。

       周一至周六备份二进制日志。

三、mysql备份工具之lvm-snapshot

基于lvm快照的备份

要求：

       1）、事务日志跟数据文件必须在同一个卷上

       2）、创建快照卷之前，要请求mysql的全局锁；在快照创建完成之后，释放锁。

       3）、请求全局锁，完成之后，做一次日志滚动；日志滚动之后，记录事件位置。

备份步骤：

       1）、请求全局锁，并滚动日志

mysql> flush tables with read lock;
mysql> flush logs;

       2）、做二进制日志文件及位置标记（手动进行）

#mysql -e "show master status" >/path/to/file

       3）、创建快照卷

#lvcreate -L -s -n -p r /path/to/some_lv

       4）、释放全局锁

mysql> unlock tables;

       5）、挂载快照卷并备份

cp

       6）、备份完成之后，删除快照卷

恢复步骤：

       1）、提取备份之后的二进制文件至某sql脚本中

       2）、还原数据、修改权限及属主属组等，并启动mysql

       3）、做即时点还原

四、mysql备份工具之xtrabackup

备份的实现

1、完全备份

# innobackupex –user=DBUSER
–password=DBUSERPASS  /path/to/BACKUP-DIR/

如果要使用一个最小权限的用户进行备份，则可基于如下命令创建此类用户：

mysql> CREATE USER ’bkpuser’@’localhost’IDENTIFIED BY ’s3cret’;
mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM ’bkpuser’;
mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO ’bkpuser’@’localhost’;
mysql> FLUSH PRIVILEGES;

       使用innobakupex备份时，其会调用xtrabackup备份所有的InnoDB表，复制所有关于表结构定义的相关文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件，同时还会备份触发器和数据库配置信息相关的文件。这些文件会被保存至一个以时间命令的目录中。

在备份的同时，innobackupex还会在备份目录中创建如下文件：

(1)xtrabackup_checkpoints —— 备份类型（如完全或增量）、备份状态（如是否已经为prepared状态）和LSN(日志序列号)范围信息；

       每个InnoDB页(通常为16k大小)都会包含一个日志序列号，即LSN。LSN是整个数据库系统的系统版本号，每个页面相关的LSN能够表明此页面最近是如何发生改变的。

(2)xtrabackup_binlog_info —— mysql服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置。

(3)xtrabackup_binlog_pos_innodb —— 二进制日志文件及用于InnoDB或XtraDB表的二进制日志文件的当前position。

(4)xtrabackup_binary —— 备份中用到的xtrabackup的可执行文件；

(5)backup-my.cnf —— 备份命令用到的配置选项信息；

       在使用innobackupex进行备份时，还可以使用–no-timestamp选项来阻止命令自动创建一个以时间命名的目录；如此一来，innobackupex命令将会创建一个BACKUP-DIR目录来存储备份数据。

2、准备(prepare)一个完全备份

       一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。

innobakupex命令的–apply-log选项可用于实现上述功能。如下面的命令：

# innobackupex –apply-log  /path/to/BACKUP-DIR

如果执行正确，其最后输出的几行信息通常如下：

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
1204079:01:36  InnoDB: Starting shutdown...
1204079:01:40  InnoDB: Shutdown
completed; log sequence number 92036620
120407 09:01:40innobackupex: completed OK!

       在实现“准备”的过程中，innobackupex通常还可以使用–use-memory选项来指定其可以使用的内存的大小，默认通常为100M。如果有足够的内存可用，可以多划分一些内存给prepare的过程，以提高其完成速度。

3、从一个完全备份中恢复数据

注意：恢复不用启动MySQL

       innobackupex命令的–copy-back选项用于执行恢复操作，其通过复制所有数据相关的文件至mysql服务器DATADIR目录中来执行恢复过程。innobackupex通过backup-my.cnf来获取DATADIR目录的相关信息。

# innobackupex –copy-back  /path/to/BACKUP-DIR

如果执行正确，其输出信息的最后几行通常如下：

innobackupex: Starting to copy InnoDB log files

innobackupex: in '/backup/2012-04-07_08-17-03'

innobackupex: back to original InnoDB log
directory '/mydata/data'

innobackupex: Finished copying back files.

120407 09:36:10innobackupex: completed OK!

请确保如上信息的最行一行出现“innobackupex: completed OK!”。

       当数据恢复至DATADIR目录以后，还需要确保所有数据文件的属主和属组均为正确的用户，如mysql，否则，在启动mysqld之前还需要事先修改数据文件的属主和属组。如：

# chown -Rmysql:mysql  /mydata/data/

4、使用innobackupex进行增量备份

       每个InnoDB的页面都会包含一个LSN信息，每当相关的数据发生改变，相关的页面的LSN就会自动增长。这正是InnoDB表可以进行增量备份的基础，即innobackupex通过备份上次完全备份之后发生改变的页面来实现。

要实现第一次增量备份，可以使用下面的命令进行：

# innobackupex --incremental /backup --incremental-basedir=BASEDIR

       其中，BASEDIR指的是完全备份所在的目录，此命令执行结束后，innobackupex命令会在/backup目录中创建一个新的以时间命名的目录以存放所有的增量备份数据。另外，在执行过增量备份之后再一次进行增量备份时，其–incremental-basedir应该指向上一次的增量备份所在的目录。

       需要注意的是，增量备份仅能应用于InnoDB或XtraDB表，对于MyISAM表而言，执行增量备份时其实进行的是完全备份。

“准备”(prepare)增量备份与整理完全备份有着一些不同，尤其要注意的是：

(1)需要在每个备份(包括完全和各个增量备份)上，将已经提交的事务进行“重放”。“重放”之后，所有的备份数据将合并到完全备份上。

(2)基于所有的备份将未提交的事务进行“回滚”。

于是，操作就变成了：

# innobackupex –apply-log –redo-only BASE-DIR

接着执行：

# innobackupex –apply-log –redo-only BASE-DIR
–incremental-dir=INCREMENTAL-DIR-1

而后是第二个增量：

# innobackupex –apply-log –redo-only BASE-DIR
–incremental-dir=INCREMENTAL-DIR-2

       其中BASE-DIR指的是完全备份所在的目录，而INCREMENTAL-DIR-1指的是第一次增量备份的目录，INCREMENTAL-DIR-2指的是第二次增量备份的目录，其它依次类推，即如果有多次增量备份，每一次都要执行如上操作；

       最后一次增量备份不需要加上–redo-only选项

5、Xtrabackup的“流”及“备份压缩”功能

       Xtrabackup对备份的数据文件支持“流”功能，即可以将备份的数据通过STDOUT传输给tar程序进行归档，而不是默认的直接保存至某备份目录中。要使用此功能，仅需要使用–stream选项即可。如：

# innobackupex –stream=tar  /backup | gzip > /backup/`date +%F_%H-%M-%S`.tar.gz

甚至也可以使用类似如下命令将数据备份至其它服务器：

# innobackupex –stream=tar  /backup | ssh user@www.magedu.com  "cat -> /backups/`date +%F_%H-%M-%S`.tar"

       此外，在执行本地备份时，还可以使用–parallel选项对多个文件进行并行复制。此选项用于指定在复制时启动的线程数目。当然，在实际进行备份时要利用此功能的便利性，也需要启用innodb_file_per_table选项或共享的表空间通过innodb_data_file_path选项存储在多个ibdata文件中。对某一数据库的多个文件的复制无法利用到此功能。其简单使用方法如下：

# innobackupex –parallel  /path/to/backup

       同时，innobackupex备份的数据文件也可以存储至远程主机，这可以使用–remote-host选项来实现：

# innobackupex –remote-host=root@www.magedu.com
 /path/IN/REMOTE/HOST/to/backup  

6、导入或导出单张表

       默认情况下，InnoDB表不能通过直接复制表文件的方式在mysql服务器之间进行移植，即便使用了innodb_file_per_table选项。而使用Xtrabackup工具可以实现此种功能，不过，此时需要“导出”表的mysql服务器启用了innodb_file_per_table选项（严格来说，是要“导出”的表在其创建之前，mysql服务器就启用了innodb_file_per_table选项），并且“导入”表的服务器同时启用了innodb_file_per_table和innodb_expand_import选项。

(1)“导出”表

       导出表是在备份的prepare阶段进行的，因此，一旦完全备份完成，就可以在prepare过程中通过–export选项将某表导出了：

# innobackupex –apply-log –export
/path/to/backup

       此命令会为每个innodb表的表空间创建一个以.exp结尾的文件，这些以.exp结尾的文件则可以用于导入至其它服务器。

(2)“导入”表

       要在mysql服务器上导入来自于其它服务器的某innodb表，需要先在当前服务器上创建一个跟原表表结构一致的表，而后才能实现将表导入：

mysql> CREATE TABLE mytable (…)  ENGINE=InnoDB;

然后将此表的表空间删除：

mysql> ALTER TABLE mydatabase.mytable  DISCARD TABLESPACE;

       接下来，将来自于“导出”表的服务器的mytable表的mytable.ibd和mytable.exp文件复制到当前服务器的数据目录，然后使用如下命令将其“导入”：

mysql> ALTER TABLE mydatabase.mytable  IMPORT TABLESPACE;

7、使用Xtrabackup对数据库进行部分备份

       Xtrabackup也可以实现部分备份，即只备份某个或某些指定的数据库或某数据库中的某个或某些表。但要使用此功能，必须启用innodb_file_per_table选项，即每张表保存为一个独立的文件。同时，其也不支持–stream选项，即不支持将数据通过管道传输给其它程序进行处理。

       此外，还原部分备份跟还原全部数据的备份也有所不同，即你不能通过简单地将prepared的部分备份使用–copy-back选项直接复制回数据目录，而是要通过导入表的方向来实现还原。当然，有些情况下，部分备份也可以直接通过–copy-back进行还原，但这种方式还原而来的数据多数会产生数据不一致的问题，因此，无论如何不推荐使用这种方式。

(1)创建部分备份

       创建部分备份的方式有三种：正则表达式(–include), 枚举表文件(–tables-file)和列出要备份的数据库(–databases)。

       (a)使用–include

       使用–include时，要求为其指定要备份的表的完整名称，即形如databasename.tablename，如：

# innobackupex –include='^mageedu[.]tb1'  /path/to/backup

       (b)使用–tables-file

       此选项的参数需要是一个文件名，此文件中每行包含一个要备份的表的完整名称；如：

# echo -e 'mageedu.tb1\nmageedu.tb2' >
/tmp/tables.txt

# innobackupex –tables-file=/tmp/tables.txt  /path/to/backup

       (c)使用–databases

       此选项接受的参数为数据名，如果要指定多个数据库，彼此间需要以空格隔开；同时，在指定某数据库时，也可以只指定其中的某张表。此外，此选项也可以接受一个文件为参数，文件中每一行为一个要备份的对象。如：

# innobackupex –databases="mageedu
testdb"  /path/to/backup

(2)整理(preparing)部分备份

       prepare部分备份的过程类似于导出表的过程，要使用–export选项进行：

# innobackupex –apply-log –export  /pat/to/partial/backup

       此命令执行过程中，innobackupex会调用xtrabackup命令从数据字典中移除缺失的表，因此，会显示出许多关于“表不存在”类的警告信息。同时，也会显示出为备份文件中存在的表创建.exp文件的相关信息。

(3)还原部分备份

       还原部分备份的过程跟导入表的过程相同。当然，也可以通过直接复制prepared状态的备份直接至数据目录中实现还原，不要此时要求数据目录处于一致状态。

五、数据备份注意要点

1、将数据和备份放在不同的磁盘设备上：异机或异地备份较为理想

2、备份的数据应该周期性地进行还原测试

3、每次灾难恢复后都应该立即做一次完全备份

4、针对不同规模或级别的数据量，要定制好备份策略。

5、二进制日志应该跟数据文件在不同的磁盘上，并周期性地备份好二进制日志文件。

六、从备份中恢复应该遵循的步骤

1、停止MySQL服务器

2、记录服务器的配置和文件权限

3、将数据从备份移到MySQL数据目录，其执行方式依赖于工具

4、改变配置和文件权限

5、以限制方式访问模块重启服务器：mysqld
–skip-networking可跳过网络功能

       方法：编辑my.cnf文件，添加如下项

       skip-networking

       socket=/tmp/mysql-recovery.sock

6、（如果有）载入逻辑备份并检查和重放二进制日志

7、检查已经还原的数据，确保没有问题

8、重新以完全访问模式重启服务器

       注释前面在my.cnf中添加的选项并重启即可。

七、只备份库中的部分表

在恢复之前要创建表结构。

 

来自为知笔记(Wiz)