mysql数据库备份的好处_MySQL数据备份

MySQL的两种备份策略

两种备份策略指的是逻辑备份：分别是全量备份和增量备份。除了逻辑备份外，我们还可以选择通过复制原始数据来备份数据，这种方式也称为物理备份。关于逻辑备份和物理备份，它们有如下的特点：

物理备份的特点

因为是直接复制文件系统上面的数据，因此备份文件和数据文件完全一致

物理备份比逻辑备份速度要快

数据还原后不保证table层面的一致性(不同存储引擎行为不一样)

除了数据外，物理备份也同时备份了一些其他文件，例如日志文件

物理备份最好在服务器停止的情况下备份，确保数据一致性，否则需要在锁表的前提下进行备份

无法备份内存表，因为内存表没有写入硬盘

物理备份可移植性较低，例如在Linux下备份的文件还原到windows下是就可能会有问题

逻辑备份的特点

逻辑备份只能在服务器online的情况下备份，因为需要运行sql语句

逻辑备份速度比物理备份慢

不管数据库是什么引擎，保证数据在database层面和table层面的一致性

只能备份数据，不能备份配置文件或者日志文件

备份文件可能会比物理备份的大

逻辑备份可以在不锁表的情况下进行备份(针对innoDB引擎)

逻辑备份可移植性比物理备份高

逻辑备份的两种方式

可以对数据进行全量备份和增量备份，它们各有长短，最好的方式是两者结合使用，以确保数据的安全性和一致性。

全量备份的好处是数据完整，还原方便，但坏处是备份时间长；而增量备份的好处是备份时间短，缺点是数据不完整，还原比全量备份麻烦。在实际情况中，我们应该定期为数据库做全量备份，然后分时段做增量备份

使用mysqldump程序备份数据

一般来说，我们都是使用mysqldump来进行数据备份；除非你使用的是企业版的数据库，那么，你有更好的选择(mysqlbackup)。

mysqldump虽然使用简单，但是还是有很多地方需要注意的，尤其有些参数可能会造成理解上的错误。

基本备份命令

例如要备份名称为wordpress的数据库，可以使用下面的命令

root@ubuntu-server:~# mysqldump -uUser -pPassword wordpress > wordpress.sql

mysqldump数据默认输出到标准输出，因此要把输出重定向到指定文件中。执行完命令后，wordpress数据库的内容就会备份到wordpress.sql文件中。

备份某个指定表

mysqldump还可以单独备份某个指定的表，例如要备份wordpress下的article表

root@ubuntu-server:~# mysqldump -uUser -pPassword wordpress article > article.sql

除了用户名和密码外，mysqldump会把第一个给出的参数作为数据库，其他的则作为表来备份，因此，上面语句的意思是：备份wordpress数据库中的article表到article.sql文件。如果需要备份多个表(article、user、comment)，只需要把表的名称添加上即可，例如：

root@ubuntu-server:~# mysqldump -uUser -pPassword wordpress article user comment > backup.sql

备份多个数据库

通过--databases选项(短形式为-B)，可以同时备份多个数据库，例如备份wordpress数据库和mysql数据库

root@ubuntu-server:~# mysqldump -uUser -pPassword --databases wordpress mysql > backup.sql

--databases后面跟着的参数都被当做数据库来处理，除此之外，这个选项还会在备份文件中添加上create database和use database命令。因此，在还原数据库的时候就不需要手动处理这些工作。

备份所有数据库

使用的选项是--all-databases(短形式为-A)

root@ubuntu-server:~# mysqldump -uUser -pPassword --all-databases > all.sql

这个选项和--databases一样，也会在备份文件中添加create database和use database命令。

备份innoDB数据库

innoDB数据库的特点是支持事务，通过--single-transaction选项，可以在不锁表的情况下备份innoDB数据库，确保数据一致性。另外需要注意的是，在备份过程中，任何ALTER TABLE、CREATE TABLE、DROP TABLE、RENAME TABLE、TRUNCATE TABLE命令都不允许被运行。--single-transaction选项会通过start transaction新建一个事务，然后把数据库隔离级别设置为REPEATABLE READ。

root@ubuntu-server:~# mysqldump --single-transaction -uUser -pPassword --databases wordpress > wordpress.sql

备份myisam数据库

--single-transaction选项不支持myisam数据库，因此，如果要确保myisam数据库备份时候的一致性，需要对数据库进行锁表操作。锁表选项是--lock-tables。

root@ubuntu-server:~# mysqldump --lock-tables -uUser -pPassword --databases tempdb > tempdb.sql

备份数据库以建立slave服务器

建立主从服务器的一个关键点是binary log，因此，如果希望通过备份数据库来建立主从关系，那么如何处理binary log是关键。

通过--flush-logs(-F)选项，可以在备份的时候同时刷新bniary log。另外，--flush-logs选项通常会结合-lock-all-tables、--master-data或--single-transaction选项使用，针对不同的数据库引擎，使用不同的选项。

--master-data选项的作用是在备份文件中添加change master to命令，当该选项的值为2，则change master to命令被注释，1则没有注释。

root@ubuntu-server:~# mysqldump --all-databases --lock-all-tables --master-data=1 --flush-logs -uUser -pPassword > all.sql

备份好后，需要到从服务器中还原all.sql，以及flush-logs选项生成的binary log文件。以确保数据的一致性。

通过mysqladmin增量备份数据

增量备份数据需要mysql开启binary log模式，增量数据都被储存到binary log中。做增量备份之前请确保已经做好全量备份。具体可以通过上面介绍的方法。

通过mysqladmin生成增量备份其实非常简单，通过flush-logs命令就可以创建增量备份，假设在全量备份之前，mysql数据目录内容如下：

root@ubuntu-server:/var/lib/mysql# ls -l

total 244736

......

drwxr-x--- 2 mysql mysql 4096 4月 26 06:06 mysql

-rw-r----- 1 mysql mysql 91332 6月 20 11:03 mysql-bin.000001

-rw-r----- 1 mysql mysql 6233676 6月 20 13:39 mysql-bin.000002

-rw-r----- 1 mysql mysql 1843408 6月 20 14:35 mysql-bin.000003

-rw-r----- 1 mysql mysql 39375931 6月 21 07:35 mysql-bin.000004

-rw-r----- 1 mysql mysql 10037135 6月 21 11:44 mysql-bin.000005

-rw-r----- 1 mysql mysql 95 6月 21 07:35 mysql-bin.index

-rw-r--r-- 1 root root 6 4月 26 06:06 mysql_upgrade_info

drwxr-x--- 2 mysql mysql 4096 4月 26 06:06 performance_schema

drwxr-x--- 2 mysql mysql 12288 3月 26 10:53 sys

drwxr-x--- 2 mysql mysql 4096 6月 21 10:50 wordpress

drwxr-x--- 2 mysql mysql 12288 5月 28 13:44 zabbix

一共有5个binary log文件，那么，在使用下面的备份命令之后，结果会怎样

root@ubuntu-server:~# mysqldump --all-databases --lock-all-tables --master-data=1 --flush-logs -uUser -pPassword > all.sql

然后再ls一下该目录

root@nas-share:/var/lib/mysql# ls -l

total 322452

......

drwxr-x--- 2 mysql mysql 4096 4月 26 06:06 mysql

-rw-r----- 1 mysql mysql 91332 6月 20 11:03 mysql-bin.000001

-rw-r----- 1 mysql mysql 6233676 6月 20 13:39 mysql-bin.000002

-rw-r----- 1 mysql mysql 1843408 6月 20 14:35 mysql-bin.000003

-rw-r----- 1 mysql mysql 39375931 6月 21 07:35 mysql-bin.000004

-rw-r----- 1 mysql mysql 10095733 6月 21 11:45 mysql-bin.000005

-rw-r----- 1 mysql mysql 6013 6月 21 11:46 mysql-bin.000006

-rw-r----- 1 mysql mysql 114 6月 21 11:45 mysql-bin.index

-rw-r--r-- 1 root root 6 4月 26 06:06 mysql_upgrade_info

drwxr-x--- 2 mysql mysql 4096 4月 26 06:06 performance_schema

drwxr-x--- 2 mysql mysql 12288 3月 26 10:53 sys

drwxr-x--- 2 mysql mysql 4096 6月 21 10:50 wordpress

drwxr-x--- 2 mysql mysql 12288 5月 28 13:44 zabbix

发现多了一个日志mysql-bin.000006，这个日志其实就是第一个增量备份，mysqladmin的flush-logs命令和mysqldump的差不多，也就是说，如果你希望在特定时候创建增量备份，就可以使用下面的命令：

root@ubuntu-server:~# mysqladmin -uUser -pPassword flush-logs

结果是目录下面增加了mysql-bin.000007日志，这也是最新的增量备份数据。

通过mysqlbinlog还原增量备份

假如前面的完全备份(all.sql)是在mysql-bin.000006之前创建的，如果有一天数据意外丢失，那么你可以先把完全备份还原，然后再分别还原两个增量备份，分别是mysql-bin.000006和mysql-bin.000007。具体步骤如下：

还原完全备份

root@ubuntu-server:~# mysql -uUser -pPassword < all.sql

还原增量备份

先查看一下增量备份有哪些，6和7就是我们还原的目标

mysqlbinlog有一个选项比较重要，它就是--disable-log-bin，它可以防止还原过程产生额外的日志记录，从而避免了数据循环写入。

root@ubuntu-server:/var/lib/mysql# ls

auto.cnf ibdata1 mysql mysql-bin.000004 mysql-bin.index restore.sh zabbix

backall.sql ib_logfile0 mysql-bin.000001 mysql-bin.000005 mysql_upgrade_info restore.sql

debian-5.7.flag ib_logfile1 mysql-bin.000002 mysql-bin.000006 performance_schema sys

ib_buffer_pool ibtmp1 mysql-bin.000003 mysql-bin.000007 restore2.sql wordpress

接下来使用mysqlbinlog进行还原，当需要还原被意外删除的数据之前，需要先确保日志中的drop语句被移除，可以先把日志文件中的内容导出到普通的文件当中，然后把相关的drop语句删除，这样才能保证数据可以正确还原。

root@ubuntu-server:/var/lib/mysql# mysqlbinlog mysql-bin.000006 >> bak.sql #导出后删除drop语句(如果有的话)

root@ubuntu-server:/var/lib/mysql# mysqlbinlog mysql-bin.000007 >> bak.sql #导出后删除drop语句(如果有的话)

root@ubuntu-server:/var/lib/mysql# cat bak.sql | mysql -u root -p #最后进行数据还原

其他选项

mysqlbinlog有些选项在还原数据的时候非常有用，分别是

--start-position和--stop-position

--start-datetime和--stop-datetime

第一对选项用于指定开始还原的位置和结束还原的位置。如果只指定start-position，则数据从start-position一直还原到日志文件的末尾，相反如果只指定stop-position，那么数据从日志开头一直还原到stop-position位置。

第二对选项则用来指定还原的起始时间，其作用和start-position类似。

使用mysqlbinlog查看日志内容

由于binary log日志是二进制形式的，我们无法像普通文件一样查看它里面的内容，不过MySQL提供了mysqlbinlog工具来操作binary log。

root@ubuntu-server:/var/lib/mysql# mysqlbinlog mysql-bin.000007

BINLOG '

QSIrWxMBAAAAPAAAAP0BAAAAAAQCAAAAAAEABnphYmJpeAAMaGlzdG9yeV91aW50AAQIAwgDAAAD

ESAf

QSIrWx4BAAAAVQAAAFICAAAAAAQCAAAAAAEAAgAE//B8XQAAAAAAAEAiK1sAAAAAAAAAAGNL/hvw

6FoAAAAAAABAIitbAAAAAAAAAABZegEcDUyQpA==

'/*!*/;

# at 594

#180621 11:57:53 server id 1 end_log_pos 625 CRC32 0x7a2c64d6 Xid = 539263

COMMIT/*!*/;

默认情况下，日志会输出到标准输出，且数据以base64方式加密，如果希望查看具体内容，可以使用--base64-output和-v选项解密。--base64-output选项可以抑制加密信息的输出，而-v选项可重构日志中的sql语句

root@ubuntu-server:/var/lib/mysql# mysqlbinlog --base64-output=decode-rows -v mysql-bin.000007 > decode.sql

解密后的内容部分显示如下，已经可以看到具体的sql语句操作：

# at 389

#180621 7:35:22 server id 1 end_log_pos 449 CRC32 0x43f149b1 Write_rows: table id 272 flags: STMT_END_F

### INSERT INTO `zabbix`.`history`

### SET

### @1=23301

### @2=1529537721

### @3=26.818599999999999994

### @4=995065421

当然，通过-v选项输出的文件不能用来进行数据的还原，因为该选项会把任何insert、update语句都注释掉。