015—备份、还原和数据迁移

备份：

按是否备份全部数据可以将备份划分为：

l 完全备份

l 增量备份：指的是备份自上一次备份以来(增量或完全)以来变化的数据; 特点: 节约空间、还原麻烦。

l 差异备份：指的是备份自上一次完全备份以来变化的数据 特点: 浪费空间、还原比增量备份简单。

一般情况下，根据备份策略组合使用：完全+增量；完全+差异。

常用备份工具：

1. mysqldump : 逻辑备份工具, 适用于所有的存储引擎, 支持温备、完全备份、部分备份、对于InnoDB存储引擎支持热备

2. cp, tar 等归档复制工具: 物理备份工具, 适用于所有的存储引擎, 冷备、完全备份、部分备份

3. lvm2 snapshot: 几乎热备, 借助文件系统管理工具进行备份

4. mysqlhotcopy: 名不副实的的一个工具, 几乎冷备, 仅支持MyISAM存储引擎

5. xtrabackup: 一款非常强大的InnoDB/XtraDB热备工具, 支持完全备份、增量备份, 由percona提供

备份策略：

针对不同的场景下, 我们应该制定不同的备份策略对数据库进行备份, 一般情况下, 备份策略一般为以下4种：

a. 直接cp,tar复制数据库文件

b. mysqldump+复制BIN LOGS

c. lvm2快照+复制BIN LOGS

d. xtrabackup

以上的几种解决方案分别针对于不同的场景：

Ø 如果数据量较小, 可以使用第一种方式, 直接复制数据库文件。【如果是将复制的文件拷贝到新的数据库时，需要新数据的版本号高于旧数据库，仅有MYISAM引擎支持，INNODB不支持】

Ø 如果数据量还行, 可以使用第二种方式, 先使用mysqldump对数据库进行完全备份, 然后定期备份BINARY LOG达到增量备份的效果。

Ø 如果数据量一般, 而又不过分影响业务运行, 可以使用第三种方式, 使用lvm2的快照对数据文件进行备份, 而后定期备份BINARY LOG达到增量备份的效果。

Ø

Ø 如果数据量很大, 而又不过分影响业务运行, 可以使用第四种方式, 使用xtrabackup进行完全备份后, 定期使用xtrabackup进行增量备份或差异备份。

1、Mysqldump实现备份恢复

【windows环境下】

1) 备份单个数据库或单个数据库中的指定表：

mysqldump -h主机名  -P端口 -u用户名 -p密码 -database 数据库名 表名> 文件名.sql

2) 备份多个数据库：

mysqldump -h主机名  -P端口 -u用户名 -p密码 -database db1 db2 db3 > 文件名.sql

3) 备份所有数据库：

mysqldump --all -databases > backupname.sql

4) 压缩备份：

mysqldump -h hostname -u username -p password -database databasename | gzip > backupfile.sql.gz

5) 还原MySQL数据库的命令：

mysql -hhostname -uusername -ppassword databasename < backupfile.sql

6) 还原压缩的MySQL数据库

gunzip < backupfile.sql.gz | mysql -u username -p password dbname

数据迁移：

可以分为：同版本MYSQL迁移；不同版本MYSQL迁移；不同数据库迁移

1、直接迁移（备份还原同时进行）【同版本】

mysqldump -h hostname1-u root -p password=password1 -all-databases | mysql -h hostname2 -u root -password=password2

2、【不同版本】低版本可以向高版本迁移，高版本很难向低版本迁移。

3、不同数据库迁移，可以实现，但比较复杂。

===========================================提高==============================================================

增量备份

小量的数据库可以每天进行完整备份，因为这也用不了多少时间，但当数据库很大时，就不太可能每天进行一次完整备份了，这时候就可以使用增量备份。增量备份的原理就是使用了mysql的binlog志。

1、首先做一次完整备份：

mysqldump -h20.6.208.183 -utest2 -p123 -P3310 --single-transaction  --master-data=2  test>test.sql

这时候就会得到一个全备文件test.sql

在sql文件中我们会看到：

-- CHANGE MASTER TO MASTER_LOG_FILE='bin-log.000002', MASTER_LOG_POS=107;是指备份后所有的更改将会保存到bin-log.000002二进制文件中。

2、在test库的t_student表中增加两条记录，然后执行flush logs命令。这时将会产生一个新的二进制日志文件bin-log.000003，bin-log.000002则保存了全备过后的所有更改，既增加记录的操作也保存在了bin-log.00002中。

3、再在test库中的a表中增加两条记录，然后误删除t_student表和a表。a中增加记录的操作和删除表a和t_student的操作都记录在bin-log.000003中。

恢复

1、首先导入全备数据

mysql -h20.6.208.183 -utest2 -p123  -P3310 < test.sql，

也可以直接在mysql命令行下面用source导入

2、恢复bin-log.000002

mysqlbinlog bin-log.000002 |mysql -h20.6.208.183 -utest2 -p123  -P3310

3、恢复部分 bin-log.000003

在general_log中找到误删除的时间点，然后更加对应的时间点到bin-log.000003中找到相应的position点，需要恢复到误删除的前面一个position点。

可以用如下参数来控制binlog的区间

--start-position 开始点 --stop-position 结束点

--start-date 开始时间 --stop-date 结束时间

找到恢复点后，既可以开始恢复。

 mysqlbinlog mysql-bin.000003 --stop-position=208 |mysql -h20.6.208.183 -utest2 -p123  -P3310

参考：

1、学会4种MYSQL备份方法 2、常用MSQL备份还原命令