MySQL的备份与恢复理解与备份策略

422 0 0

作者:鱼夫

MySQL的备份主要分为逻辑备份和物理备份

逻辑备份

在MySQL中逻辑备份的最大优点是对各种存储引擎都可以用同样的方法来备份。而物理备份则不同，不同的存储引擎有着不同的备份方法。Mysql中的逻辑备份是将数据库中的数据备份为一个文本文件，备份的文件可以被查看和编辑，在mysql中常用mysqldump工具来完成逻辑备份。

mysqldump的使用方法：

查看测试数据库信息
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| suzhou             |
| sysdb              |
| test               |
+--------------------+
6 rows in set (0.01 sec)

1.备份指定的数据库： suzhou

[root@anan tmp]# mysqldump -u root -p suzhou  >  ./dbbak/suzhou.sql     --备份suzhou 数据库
Enter password: 
[root@anan tmp]# 
[root@anan tmp]# cd dbbak/ && ls
suzhou.sql            --备份生成的文件
[root@anan dbbak]#

2.备份指定的某个库中的某个表：tb1

[root@anan dbbak]# mysqldump -u root -p suzhou tb1 > ./tb1.sql
Enter password: 
[root@anan dbbak]# ls
suzhou.sql  tb1.sql

3.备份所有数据库

mysqldump -uroot -p --all-database > ./alldb.sql
Enter password: 

[root@anan dbbak]# ls
alldb.sql  suzhou.sql  tb1.sql

　　需要强调的是，为了保证数据备份的一致性，MyISAM存储引擎在备份时需要加上 -l （lock table）参数，表示将所有表加上读锁。在备份期间，所有表将引擎只能读不能进行更新数据。但是对于事物存储引擎（InnoDB和BDB）来说，可以使用--single-transaction，该选项将使InnoDB引擎得到一个快照，使得备份的数据能够保证一致性。

完全恢复

mysqldump的恢复也比较简单，将备份的数据文件作为输入导入。但是注意，备份恢复后数据并不完整，因为备份后数据库中又会产生新的数据记录，需要将备份后的日志进行重写。使用方法：mysqlbinlog binlog-file | mysql -u -p

下面进行一次完整的备份与恢复

新建一个数据库 data1，库中有tb1，信息如下：

mysql> select * from tb1;
+------+------+
| id   | name |
+------+------+
|    1 | A    |
|    2 | B    |
|    3 | C    |
+------+------+
3 rows in set (0.00 sec)

备份data1数据库

mysql> show master status;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000010 |     2935 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
备份前先查看一下当前使用的bin-log日志文件编号

[root@anan dbbak]# mysqldump -uroot -p -l -F data1 > ./data1.sql
Enter password: 
[root@anan dbbak]# ls
data1.sql
--备份指令中使用的-l :锁定所有表为只读状态;  -F ： 刷新日志文件
再看bin-log文件信息：

mysql> insert into tb1 values (4,'D'),(5,'E'),(6,'F');
Query OK, 3 rows affected (0.03 sec)

再看bin-log文件的position值

　删除数据库data1，模拟故障

mysql> drop database data1;
Query OK, 1 row affected (0.03 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| suzhou             |
| sysdb              |
| test               |
+--------------------+
6 rows in set (0.00 sec)

data1 库已经被删除了

　　恢复数据库 data1

[root@anan dbbak]# mysql -uroot -p  data1 < ./data1.sql
Enter password: 
......
......
mysqldump: Got error: 1049: Unknown database 'data1' when selecting the database
【恢复的数据库必须要在数据库中存在，恢复前先新建一个同名的数据库 data1】

　　【注意：mysqldump只用来备份数据，恢复数据使用 mysql指令，第一次恢复大意地使用mysqldump指令来恢复，怎么也没有成功，也没有报错，还是得细心才是】

[root@anan dbbak]# mysql -u root -p data1 < ./data1.sql --恢复备份
Enter password:
[root@anan dbbak]#

查看恢复后的数据如下，后写入的数据还没有

mysql> select * from tb1;
+------+------+
| id | name |
+------+------+
| 1 | A |
| 2 | B |
| 3 | C |
+------+------+
3 rows in set (0.00 sec)

　恢复备份后的数据，由于失误操作，日志文件中存在误操作的语句，所以可以可以使用基于时间点和基于位置的恢复。下面以位置恢复。

恢复前需要先看新写入的数据在bin-log日志文件中的position值： mysqlbinlog master-bin.000011

[root@anan mysqldata]# mysqlbinlog master-bin.000011 | cat -n | grep -C 5  'insert'
     1	/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
     2	/*!40019 SET @@session.max_insert_delayed_threads=0*/;
     3	/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
     4	DELIMITER /*!*/;
     5	# at 4
     6	#180308  2:28:18 server id 2  end_log_pos 107 	Start: binlog v 4, server v 5.5.32-log created 180308  2:28:18
     7	# Warning: this binlog is either in use or was not closed properly.
--
    24	/*!*/;
    25	# at 176
    26	#180308  2:33:31 server id 2  end_log_pos 286 	Query	thread_id=13	exec_time=0 error_code=0
    27	use `data1`/*!*/;
    28	SET TIMESTAMP=1520447611/*!*/;
    29	insert into tb1 values (4,'D'),(5,'E'),(6,'F')
    30	/*!*/;
    31	# at 286
    32	#180308  2:33:31 server id 2  end_log_pos 313 	Xid = 611
    33	COMMIT/*!*/;
    34	# at 313
--
   213	BEGIN
   214	/*!*/;
   215	# at 3393
   216	#180308  2:33:31 server id 2  end_log_pos 3503 	Query	thread_id=13	exec_time=4490	error_code=0
   217	SET TIMESTAMP=1520447611/*!*/;
   218	insert into tb1 values (4,'D'),(5,'E'),(6,'F')
   219	/*!*/;
   220	# at 3503
   221	#180308  2:33:31 server id 2  end_log_pos 3530 	Xid = 916
   222	COMMIT/*!*/;
   223	DELIMITER ;

使用日志文件恢复后写入的数据 ；使用mysqlbinlog 指令

[root@anan mysqldata]# mysqlbinlog --start-position='176' --stop-position='286' ./master-bin.000011 | mysql -u root -p
Enter password:
[root@anan mysqldata]#

再看数据库信息

mysql> select * from tb1;
+------+------+
| id | name |
+------+------+
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | D |
| 5 | E |
| 6 | F |
+------+------+
6 rows in set (0.01 sec)

物理备份和恢复

物理备份又分为冷备和热备两种，和逻辑备份相比，它最大的优点是备份和恢复的速度更快，因为物理备份的原来是基于文件的cp。

冷备份

冷备份其实就是停掉数据库服务，cp数据文件的方法，这种方法对MyISAM和InnoDB引擎都适合，但是一般很少用，因为很多应用是不允许停机的。

热备份

在MySQL中，对于不同的存储引擎热备的方法也有所不同，下面主要介绍MyISAM和InnoDB两种常用的存储引擎的热备方法。

MyISAM存储引擎

MyISAM存储引擎的热备方法本质其实就是将要备份的表加读锁，然后在cp数据文件到备份目录，常用的方法有以下两种：

方法1：使用mysqlhotcopy工具

方法2：使用手动锁表copy

首先将数据库中的所有表加读锁： flush tables for read ; 然后再cp数据文件到备份目录即可。

InnoDB存储引擎

InnoDB存储引擎数据库可以使用Xtrabackup工具来备份

下面仔细介绍xtarbackup热备工具的使用：

xtarbackup热备工具的使用

xtrabackup是percona公司参与开发的一款基于InnoDB的在线热备工具，具有开源，免费，支持在线热备，备份恢复速度快，占用磁盘空间小等特点。

xtrabackup包含两个主要的工具。即xtrabackup和innobackupex，两者区别如下：

xtrabackup：只能备份InnoDB存储引擎和XtraDB两种数据表，而不能备份MyISAM数据表。

Innobackupex：是一个封装了xtrabackup的perl脚本，支持同时备份InnoDB和MyISAM,但在对MyISAM备份时需要加一个全局的读锁

备份前查看数据库的存储引擎是什么：

mysql> show variables like '%engine';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
| storage_engine         | InnoDB |
+------------------------+--------+
2 rows in set (0.00 sec)

innobackupex的参数比较多，详细的参数可以参考学习下面一位博友的文章，写的很详细

https://www.cnblogs.com/zhoujinyi/p/5893333.html

进行全备

[root@anan dbbak]# innobackupex  --defaults-file=/etc/my.cnf  --user=root  --password=fsz...  ./

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona Ireland Ltd 2009-2012.  All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

180308 06:11:41  innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/etc/my.cnf;mysql_read_default_group=xtrabackup' as 'root'  (using password: YES).
180308 06:11:41  innobackupex: Connected to MySQL server
IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".

innobackupex: Using mysql server version 5.5.32-log

innobackupex: Created backup directory /tmp/dbbak/2018-03-08_06-11-41

180308 06:11:41  innobackupex: Starting ibbackup with command: xtrabackup_55  --defaults-file="/etc/my.cnf"  --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/tmp/dbbak/2018-03-08_06-11-41 --tmpdir=/tmp
innobackupex: Waiting for ibbackup (pid=3736) to suspend
innobackupex: Suspend file '/tmp/dbbak/2018-03-08_06-11-41/xtrabackup_suspended_2'

xtrabackup_55 version 2.1.2 for Percona Server 5.5.16 Linux (x86_64) (revision id: 611)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /usr/local/mysqldata
xtrabackup: Target instance is assumed as followings.
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 5242880
>> log scanned up to (1645580)
[01] Copying ./ibdata1 to /tmp/dbbak/2018-03-08_06-11-41/ibdata1
[01]        ...done
>> log scanned up to (1645580)

180308 06:11:43  innobackupex: Continuing after ibbackup has suspended
180308 06:11:43  innobackupex: Starting to lock all tables...
180308 06:11:43  innobackupex: All tables locked and flushed to disk

180308 06:11:43  innobackupex: Starting to backup non-InnoDB tables and files
innobackupex: in subdirectories of '/usr/local/mysqldata'
innobackupex: Backing up files '/usr/local/mysqldata/performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (18 files)
innobackupex: Backing up file '/usr/local/mysqldata/data1/db.opt'
innobackupex: Backing up file '/usr/local/mysqldata/data1/tb1.frm'
innobackupex: Backing up file '/usr/local/mysqldata/sysdb/db.opt'
innobackupex: Backing up file '/usr/local/mysqldata/sysdb/tb1.frm'
innobackupex: Backing up files '/usr/local/mysqldata/mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (72 files)
>> log scanned up to (1645580)
innobackupex: Backing up file '/usr/local/mysqldata/suzhou/db.opt'
innobackupex: Backing up file '/usr/local/mysqldata/suzhou/tb1.frm'
innobackupex: Backing up file '/usr/local/mysqldata/suzhou/fengsuzhou.frm'
180308 06:11:44  innobackupex: Finished backing up non-InnoDB tables and files

180308 06:11:44  innobackupex: Waiting for log copying to finish

xtrabackup: The latest check point (for incremental): '1645580'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (1645580)

xtrabackup: Transaction log of lsn (1645580) to (1645580) was copied.
180308 06:11:45  innobackupex: All tables unlocked

innobackupex: Backup created in directory '/tmp/dbbak/2018-03-08_06-11-41'
innobackupex: MySQL binlog position: filename 'master-bin.000011', position 3736
180308 06:11:45  innobackupex: Connection to database server closed
180308 06:11:45  innobackupex: completed OK!

完整的备份过程信息可以细细查看
备份后在备份目录会生成一个时间戳目录，备份的数据文件就放在该目录中

　　热备恢复

恢复前可以测试一下把原来的数据目录清空然后恢复看看效果

1.先关闭服务
service  mysqld  stop

2.重命名数据目录并创建原同名目录
mv mysqldata  mysqldatabak
mkdir  mysqldata

3.还原后再重新修改新的数据目录的权限
chown -R mysql:mysql  mysqldata/*

4.启动服务
service mysqld  start

还原数据

#提交或者回滚事务
 [root@anan dbbak]# innobackupex --apply-log ./2018-03-08_06-11-41/
#上面这指令是利用--apply-log的作用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态

#恢复数据
[root@anan dbbak]# innobackupex --defaults-file=/etc/my.cnf --copy-back ./2018-03-08_06-11-41/

还可以使用 【--database=“db1 db2 db3”】选项备份指定的数据库，多个数据库用空格隔开
还可以使用 【--databases="db1.tb1 db2.tb2 db3.tb3"】方式备份不同库的不同表，同样多库用空格隔开

　　最后检查数据和查看数据目录

mysql> select * from tb1;
+------+------+
| id   | name |
+------+------+
|    1 | A    |
|    2 | B    |
|    3 | C    |
|    4 | D    |
|    5 | E    |
|    6 | F    |
|    4 | D    |
|    5 | E    |
|    6 | F    |
+------+------+
9 rows in set (0.01 sec)

　　创建增量备

在第一次全备的基础上进行增量备份，接着再继续往数据库里写入一些数据

mysql> insert into tb1 values (7,'G'),(8,'H'),(9,'K');
Query OK, 3 rows affected (0.03 sec)

　　执行增量备份

[root@anan dbbak]# innobackupex --apply-log --redo-only ./2018-03-08_06-11-41/

---恢复内容结束---