MySQL备份和还原

4trgr

1.备份类型

根据备份时服务器是否能够向外继续提供服务分为：热备份、温备份、冷备份。
    热备份：在线，读、写不受影响；
    温备份：在线，但仅可以执行读操作；
    冷备份：离线，读、写操作均不能进行；
根据备份时是直接复制数据文件还是将数据导出进行备份分为：物理备份、逻辑备份。
    物理备份：直接复制数据文件；
    逻辑备份：将数据导出至文本文件当中；
根据备份内容不同分为：完全备份、增量备份、差异备份。
    完全备份：备份全部数据；
    增量备份：仅备份上次完全备份或增量备份以后变化的数据；
    差异备份：仅备份上次完全备份以来变化的数据；
2.使用mysqldump工具备份还原数据
    mysqldump是一个逻辑备份工具，对MyISAM引擎可以实现温备，对InnoDB引擎可以实现热备。速度比较慢，不适合对大数据库进行完全备份。数据还原时应该将二进制日志功能临时性关掉(sql_log_bin=OFF)，还原后在启用。

    mysqldump命令的用法及其常见选项：

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

mysqldump -uroot -p [db_name] [tb1] [tb2] > backfile    #备份单个数据库，或库中特定表     --master-data={0|1|2}         0: 不记录二进制日志文件及其位置；         1：以CHNAGE MASTER TO的方式记录位置，可用于恢复后直接启动从服务器；         2：以CHANGE MASTER TO的方式记录位置，但默认为被注释；     --lock-all-tables：锁定所有表     --flush-logs：执行日志滚动     --all-databases：备份所有库     --databases DB_NAME,DB_NAME,...：备份指定库     --events：备份事件     --routines：备份存储过程和存储函数     --triggers：备份触发器     --no-data：只备份表结构     如果指定库中的表类型均为InnoDB，可使用--single-transaction启动热备；

    mysqldump完全备份加二进制日志增量备份及还原实例：
备份：

1 2 3 4

mysqldump -uroot -p --master-data=2 --flush-logs --all-databases --lock-all-tables > /root/all.sql     #完全备份 mysqlbinlog mysql-bin.000011 > /root/first-incremental.sql     #第一次增量备份

删除数据文件模拟故障还原(假设故障时使用的二进制日志文件为mysql-bin.000012且二进制日志文件和数据文件分开存储)：

1 2 3 4 5 6 7 8 9 10 11

killall mysqld /usr/local/mysql/scripts/mysql_install_db --user=mysql --datadir=/mydata/data     #初始化数据库 service mysqld start mysql -uroot -p < /root/all.sql     #还原完全备份 mysql -uroot -p < /root/first-incremental.sql     #还原增量备份 mysqlbinlog mysql-bin.000012 > /tmp/tmp.sql mysql -uroot -p < /tmp/tmp.sql     #即时点恢复

3.通过select命令备份数据并还原
    select通常用来备份单个表，还原时表结构需要预先创建好。
备份：

1	mysql> SELECT * INTO OUTFILE '/path/to/somefile.txt' FROM tb_name [WHERE clause];

还原：

1	mysql> LOAD DATA INFILE '/path/to/somefile.txt' INTO TABLE tb_name;

4.使用LVM快照进行数据库备份
    LVM快照能够实现几乎热备，使用LVM快照备份需要注意以下几点：
        1、数据文件要在逻辑卷上；
        2、此逻辑卷所在卷组必须有足够空间使用快照卷；
        3、数据文件和事务日志要在同一个逻辑卷上；
备份：
    刷新表并施加读锁：

1 2	mysql> FLUSH TABLES WITH READ LOCK; mysql> FLUSH LOGS;

    通过另一个终端保存二进制日志文件及相关位置信息：

1	mysql -uroot -p -e 'SHOW MASTER STATUS\G' > /path/to/master.info

    创建快照卷：

1	lvcreate -L 1G -s -p r -n mydata-snap /dev/myvg/mydata

    释放锁：

1	mysql> UNLOCK TABLES;

    挂在快照卷并备份数据：

1 2	mount -o ro  /dev/myvg/mydata-snap /mnt cp -a /mnt/* /backup

    删除快照卷：

1 2	umount /mnt lvremove --force /dev/myvg/mydata-snap

还原：

1	cp -a /backup/* /mydata/data

6.使用xtrabackup进行数据库备份
1).xtrabackup进行完全备份与恢复

a. xtrabackup进行完全备份

1 2 3

innobackupex --user=DBUSER --password=DBUSERPASS  /path/to/BACKUP-DIR/     #/path/to/BACKUP-DIR/表示备份文件保存的位置     #innobackupex命令封装了支持各种不同MySQL版本的xtrabackup工具

    在备份的同时，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 —— 备份命令用到的配置选项信息；
b. 准备(prepare)一个完全备份
    一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处于不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。

1	innobackupex --apply-log  /path/to/BACKUP-DIR

c. 从一个完全备份中恢复数据

1	innobackupex --copy-back  /path/to/BACKUP-DIR

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

1	# chown -R  mysql:mysql  /mydata/data/

2).xtrabackup进行增量备份并还原

a. 进行增量备份

1 2 3

innobackupex --incremental /backup --incremental-basedir=BASEDIR     #/backup表示增量备份文件保存的位置     #--incremental-basedir指定完全备份所在的目录或上一次增量备份所在目录

注：增量备份仅能应用于InnoDB或XtraDB表，对于MyISAM表而言，执行增量备份时其实进行的是完全备份。
b. 准备(prepare)增量备份
    “准备”(prepare)增量备份与整理完全备份有着一些不同，尤其要注意的是：
    (1)需要在每个备份(包括完全和各个增量备份)上，将已经提交的事务进行“重放”。“重放”之后，所有的备份数据将合并到完全备份上。
    (2)基于所有的备份将未提交的事务进行“回滚”。

1 2 3 4

innobackupex --apply-log --redo-only BASE-DIR     #--redo-only表示跳过rollback阶段，只进行redo阶段 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指的是第二次增量备份的目录，其它依次类推，即如果有多次增量备份，每一次都要执行如上操作。
c. 还原数据
    因为准备阶段已经将所有数据全部合并到完全备份上，所有还原时只需还原完全备份即可。

1 2	innobackupex --copy-back  BASE-DIR chown -R  mysql:mysql  /mydata/data/