MySQL的两种备份策略
两种备份策略指的是逻辑备份：分别是全量备份和增量备份。除了逻辑备份外，我们还可以选择通过复制原始数据来备份数据，这种方式也称为物理备份。关于逻辑备份和物理备份，它们有如下的特点：
物理备份的特点
-
因为是直接复制文件系统上面的数据，因此备份文件和数据文件完全一致
-
物理备份比逻辑备份速度要快
-
数据还原后不保证table层面的一致性（不同存储引擎行为不一样）
-
除了数据外，物理备份也同时备份了一些其他文件，例如日志文件
-
物理备份最好在服务器停止的情况下备份，确保数据一致性，否则需要在锁表的前提下进行备份
-
无法备份内存表，因为内存表没有写入硬盘
-
物理备份可移植性较低，例如在Linux下备份的文件还原到windows下是就可能会有问题
逻辑备份的特点
- 逻辑备份只能在服务器online的情况下备份，因为需要运行sql语句
- 逻辑备份速度比物理备份慢
- 不管数据库是什么引擎，保证数据在database层面和table层面的一致性
- 只能备份数据，不能备份配置文件或者日志文件
- 备份文件可能会比物理备份的大
- 逻辑备份可以在不锁表的情况下进行备份（针对innoDB引擎）
- 逻辑备份可移植性比物理备份高
逻辑备份的两种方式
可以对数据进行全量备份和增量备份，它们各有长短，最好的方式是两者结合使用，以确保数据的安全性和一致性。
全量备份的好处是数据完整，还原方便，但坏处是备份时间长；而增量备份的好处是备份时间短，缺点是数据不完整，还原比全量备份麻烦。在实际情况中，我们应该定期为数据库做全量备份，然后分时段做增量备份
使用mysqldump程序备份数据
一般来说，我们都是使用mysqldump来进行数据备份；除非你使用的是企业版的数据库，那么，你有更好的选择（mysqlbackup）。
mysqldump虽然使用简单，但是还是有很多地方需要注意的，尤其有些参数可能会造成理解上的错误。
-
基本备份命令
例如要备份名称为wordpress的数据库，可以使用下面的命令
root@ubuntu-server:~# mysqldump -uUser -pPassword wordpress > wordpress.sqlmysqldump数据默认输出到标准输出，因此要把输出重定向到指定文件中。执行完命令后，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语句都注释掉。
网友评论