数据库备份概述

数据库备份概述

概述：将数据库中存在的现有数据，进行存放成为副本数据，可解决数据容灾；
提高系统的高可用性和灾难恢复性，数据崩溃时，以最小代价重新恢复数据；

数据丢失的原因：程序错误、人为错误、磁盘错误、天灾人祸

数据库备份的分类

• 物理备份

指对数据库操作系统的物理文件（数据文件、日志文件）等备份

• 冷备份

  必须在数据库关闭状态下进行备份，能够更好保证数据库的完整性

• 热备份

  能够在数据库处于正常运行的情况下备份，能够更高的保证服务的可用性

• 逻辑备份

  指对数据库的逻辑组件（数据库、表、数据对象）进行备份

• 完全备份

  将数据进行完整的备份，包含完整的库、表、索引、视图等，需要花费更长的时间

• 差异备份

  备份自上次完全备份到现在发生改变的数据库内容，备份的文件比完整备份的文件小，备份速度更快

• 增量备份

备份至上次完全备份或增量备份后被修改的数据库内容

数据备份以及恢复

• 物理备份（冷备份）

[root@host50 backup]# cp -rp /var/lib/mysql/dumptest1/   /opt/backup/dumptest1[root@host50 backup]# lsdumptest1[root@host50 backup]# ls dumptest1/db.opt  test.frm  test.ibd[root@host50 backup]# ls /var/lib/mysql/dumptest1/db.opt  test.frm  test.ibd[root@host50 backup]# tar -zcvf mysql-backup-$(date +%F).tar.gz dumptest1/dumptest1/dumptest1/db.optdumptest1/test.frmdumptest1/test.ibd[root@host50 backup]# lsdumptest1  mysql-backup-2019-07-07.tar.gz                                          //dumptest1库备份并且压缩完成[root@host50 backup]# rm -dfr /var/lib/mysql/dumptest1/                            //删除mysql数据库dumptest1库[root@host50 backup]# systemctl restart mysqld[root@host50 backup]# mysql -uroot -p123456mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 3Server version: 5.7.17 MySQL Community Server (GPL)Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show databases;                                                               //查看所删库已经不在+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || sys                || transtb            |+--------------------+5 rows in set (0.00 sec)[root@host50 backup]# tar -zxvf mysql-backup-2019-07-07.tar.gz -C /var/lib/mysql/            //恢复数据库dumptest1/dumptest1/db.optdumptest1/test.frmdumptest1/test.ibd[root@host50 backup]# systemctl restart mysqld[root@host50 backup]# mysql -uroot -p1234566mysql: [Warning] Using a password on the command line interface can be insecure.ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)[root@host50 backup]# mysql -uroot -p123456mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 4Server version: 5.7.17 MySQL Community Server (GPL)Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || dumptest1          || mysql              || performance_schema || sys                || transtb            |+--------------------+6 rows in set (0.00 sec)mysql> use dumptest1;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select * from test;                                                              //库恢复成功+-----+| job |+-----+| aa  || bb  || c   || dd  |+-----+4 rows in set (0.00 sec)

• 逻辑备份（热备份）

  
  

  库名表示方式

  - --all-databases 或 -A 所有库

  - 数据库名 单个库

- 数据库名 表名 单张表

- -B 数据库1 数据库2 多个库

• 注意事项

  - 无论备份还是恢复，都要验证用户权限

[root@host50 backup]# mysqldump -uroot -p --all-databases > /opt/backup/alldb.sql           //备份所有库Enter password: [root@host50 backup]# lsalldb.sql[root@host50 backup]# grep -vE '^/|^-|^$' alldb.sql | head -15                             //查看库中部分内容CREATE DATABASE /*!32312 IF NOT EXISTS*/ `dumptest1` /*!40100 DEFAULT CHARACTER SET latin1 */;USE `dumptest1`;DROP TABLE IF EXISTS `test`;CREATE TABLE `test` (  `job` varchar(30) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;LOCK TABLES `test` WRITE;INSERT INTO `test` VALUES ('aa'),('bb'),('c'),('dd');UNLOCK TABLES;CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */;USE `mysql`;DROP TABLE IF EXISTS `columns_priv`;CREATE TABLE `columns_priv` (  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',  `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',  [root@host50 backup]# mysqldump -uroot -p dumptest1 > /opt/backup/dumptest1.sql              //只备份一个库Enter password: [root@host50 backup]# lsalldb.sql  dumptest1.sql[root@host50 backup]# mysqldump -uroot -p -B dumptest1  transtb> /opt/backup/dumptest1+transdb.sql    //备份指定多个库Enter password: [root@host50 backup]# lsalldb.sql  dumptest1.sql  dumptest1+transdb.sql

• 使用MYSQL命令从备份中恢复

  通常不建议直接覆盖旧库，而是采用建立新库并导入逻辑备份的方式执行恢复，待新库正常后即可废弃或删除旧库。
  

[root@host50 backup]# mysql -uroot -p dumptest2 < /opt/backup/dumptest1.sql Enter password: [root@host50 backup]# mysql -uroot -p123456mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 14Server version: 5.7.17 MySQL Community Server (GPL)Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> select * from dumptest2.test;+-----+| job |+-----+| aa  || bb  || c   || dd  |+-----+4 rows in set (0.00 sec)

binlog日志概述

• 二进制日志用途及配置方式

• 采用binlog日志的好处

-记录除查询之外的所有SQL命令

-可用于数据恢复

-配置MySQL主从同步的必要条件

• binlog相关文件

  - 主机名-bin.index 记录已有日志文件名

  - 主机名-bin.000001 第一个二进制日志

  - 主机名-bin.000002 第二个二进制日志

例：

• 配置/etc/my.cnf，并重启服务

[root@host50 backup]# vim /etc/my.cnf[mysqld].. ..log-bin-index=mysql-bin                              //启用二进制日志，并指定前缀server_id=1binlog_format=STATEMENT                             //在Mysql5.7中，binlog日志格式默认为ROW，但它不记录sql语句上下文相关信息。需要将binlog日志格式修改为STATEMENT[root@host50 backup]# systemctl restart mysqld[root@host50 backup]# ls /var/lib/mysql/mysql-bin.*  //新启用binlog后，每次启动MySQl服务都会新生成一份日志文件/var/lib/mysql/mysql-bin.000001  /var/lib/mysql/mysql-bin.index[root@host50 backup]# ls /var/lib/mysql/mysql-bin.*    //重启MySQL服务程序，或者执行SQL操作"FLUSH LOGS;"，会生成一份新的日志：/var/lib/mysql/mysql-bin.000001  /var/lib/mysql/mysql-bin.index/var/lib/mysql/mysql-bin.000002[root@host50 backup]# cat /var/lib/mysql/mysql-bin.index   //mysql-bin.index文件记录了当前保持的二进制文件列表./mysql-bin.000001./mysql-bin.000002

• 使用binlog日志恢复表记录

mysql> create database binlogdb;           //创建库 表，并删除后恢复Query OK, 1 row affected (0.01 sec)mysql> use binlogdb;Database changedmysql> create table tb1(    -> id int(4),    -> name varchar(20));Query OK, 0 rows affected (0.00 sec)mysql> insert into tb1 values    -> (1,'aa'),    -> (2,'bb'),    -> (3,'cc');Query OK, 3 rows affected (0.21 sec)Records: 3  Duplicates: 0  Warnings: 0mysql> select * from tb1;+------+------+| id   | name |+------+------+|    1 | aa   ||    2 | bb   ||    3 | cc   |+------+------+3 rows in set (0.00 sec)mysql> delete from tb1;Query OK, 3 rows affected (0.00 sec)mysql> select * from tb1;Empty set (0.00 sec)[root@host50 backup]# mysqlbinlog /var/lib/mysql/host50-bin.000003     //查看binlog日志确认恢复时间/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER /*!*/;# at 4#190707  1:49:26 server id 1  end_log_pos 123 CRC32 0x92d92fbb  Start: binlog v 4, server v 5.7.17-log created 190707  1:49:26# Warning: this binlog is either in use or was not closed properly.BINLOG 'Jt8gXQ8BAAAAdwAAAHsAAAABAAQANS43LjE3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQAAbsv2ZI='/*!*/;# at 123#190707  1:49:26 server id 1  end_log_pos 154 CRC32 0x12dd5e8b  Previous-GTIDs# [empty]# at 154#190707  1:53:03 server id 1  end_log_pos 219 CRC32 0xba04792a  Anonymous_GTID last_committed=0 sequence_number=1SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;# at 219#190707  1:53:03 server id 1  end_log_pos 325 CRC32 0x651624b7  Query thread_id=6 exec_time=0 error_code=0SET TIMESTAMP=1562435583/*!*/;SET @@session.pseudo_thread_id=6/*!*/;SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;SET @@session.sql_mode=1436549152/*!*/;SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;/*!\C utf8 *//*!*/;SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;SET @@session.lc_time_names=0/*!*/;SET @@session.collation_database=DEFAULT/*!*/;create database binlogdb/*!*/;# at 325#190707  1:53:43 server id 1  end_log_pos 390 CRC32 0x13116bdf  Anonymous_GTID last_committed=1 sequence_number=2SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;# at 390#190707  1:53:43 server id 1  end_log_pos 518 CRC32 0x431ff6ab  Query thread_id=6 exec_time=0 error_code=0use `binlogdb`/*!*/;SET TIMESTAMP=1562435623/*!*/;create table tb1(id int(4),name varchar(20))/*!*/;# at 518#190707  1:54:23 server id 1  end_log_pos 583 CRC32 0x8dc7fcdb  Anonymous_GTID last_committed=2 sequence_number=3SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;# at 583#190707  1:54:23 server id 1  end_log_pos 670 CRC32 0x58cc7317  Query thread_id=6 exec_time=0 error_code=0SET TIMESTAMP=1562435663/*!*/;BEGIN/*!*/;# at 670#190707  1:54:23 server id 1  end_log_pos 803 CRC32 0xf0848df1  Query thread_id=6 exec_time=0 error_code=0SET TIMESTAMP=1562435663/*!*/;insert into tb1 values(1,'aa'),(2,'bb'),(3,'cc')/*!*/;# at 803#190707  1:54:23 server id 1  end_log_pos 834 CRC32 0x01790e76  Xid = 17COMMIT/*!*/;# at 834#190707  1:54:55 server id 1  end_log_pos 899 CRC32 0xba024a34  Anonymous_GTID last_committed=3 sequence_number=4SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;# at 899#190707  1:54:55 server id 1  end_log_pos 986 CRC32 0xc99b2859  Query thread_id=6 exec_time=0 error_code=0SET TIMESTAMP=1562435695/*!*/;BEGIN/*!*/;# at 986#190707  1:54:55 server id 1  end_log_pos 1083 CRC32 0xda0eb644  Query thread_id=6 exec_time=0 error_code=0SET TIMESTAMP=1562435695/*!*/;delete from tb1/*!*/;# at 1083#190707  1:54:55 server id 1  end_log_pos 1114 CRC32 0xd72d7ba3  Xid = 19COMMIT/*!*/;SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;DELIMITER ;# End of log file/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;[root@host50 backup]# mysqlbinlog --start-datetime="2019-07-07 1:54:23" --stop-datetime="2019-07-07 1:54:55" /var/lib/mysql/host50-bin.000003 | mysql -uroot -p123456mysql> select * from binlogdb.tb1;+------+------+| id   | name |+------+------+|    1 | aa   ||    2 | bb   ||    3 | cc   |+------+------+3 rows in set (0.00 sec)

生产环境 Mysql 数据库备份策略

• 策略设计思路：

  1.数据更新频繁，则应该进行较为频繁的备份；
  2.数据较为重要，则在有适当更新时进行备份；
  3.在数据库压力小的时段进行全量备份；

• 备份方案：

  1.在每周末的某个时段使用 mysqldump 进行重要数据库的全量备份（最好备份单个数据库，
  而不是 all）；

  
  2.在平时每天的晚上时段将二进制日志文件终止（前提需要根据数据记录的生成数量合理指
  定单个二进制文件的大小），这样每隔 24 小时会生成一个二进制日志文件；

  
  3.每周的全量备份文件和每天的二进制日志文件相加，就是这一周内数据库的整体内容；

  
  4.出现故障时， 首先恢复全量备份， 可以执行 mysqlbinlog 命令结合实际情况根据 position 值
  或者时间点进行恢复，确保万无一失；