数据库备份概述

数据库备份概述

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

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

数据库备份的分类

• 物理备份

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

• 冷备份

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

• 热备份

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

• 逻辑备份

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

• 完全备份

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

• 差异备份

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

• 增量备份

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

数据备份以及恢复

• 物理备份（冷备份）

[root@host50 backup]# cp -rp /var/lib/mysql/dumptest1/   /opt/backup/dumptest1
[root@host50 backup]# ls
dumptest1
[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.opt
dumptest1/test.frm
dumptest1/test.ibd
[root@host50 backup]# ls
dumptest1  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 -p123456
mysql: [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 3
Server 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 its
affiliates. Other names may be trademarks of their respective
owners.
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.opt
dumptest1/test.frm
dumptest1/test.ibd

[root@host50 backup]# systemctl restart mysqld
[root@host50 backup]# mysql -uroot -p1234566
mysql: [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 -p123456
mysql: [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 4
Server 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 its
affiliates. Other names may be trademarks of their respective
owners.
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 names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> 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]# ls
alldb.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]# ls
alldb.sql  dumptest1.sql

[root@host50 backup]# mysqldump -uroot -p -B dumptest1  transtb> /opt/backup/dumptest1+transdb.sql    //备份指定多个库
Enter password: 
[root@host50 backup]# ls
alldb.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 -p123456
mysql: [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 14
Server 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 its
affiliates. Other names may be trademarks of their respective
owners.
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=1
binlog_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 changed
mysql> 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: 0
mysql> 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 '
Jt8gXQ8BAAAAdwAAAHsAAAABAAQANS43LjE3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
Absv2ZI=
'/*!*/;
# 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=1
SET @@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=0
SET 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=2
SET @@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=0
use `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=3
SET @@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=0
SET 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=0
SET 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 = 17
COMMIT/*!*/;
# at 834
#190707  1:54:55 server id 1  end_log_pos 899 CRC32 0xba024a34  Anonymous_GTID last_committed=3 sequence_number=4
SET @@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=0
SET 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=0
SET TIMESTAMP=1562435695/*!*/;
delete from tb1
/*!*/;
# at 1083
#190707  1:54:55 server id 1  end_log_pos 1114 CRC32 0xd72d7ba3  Xid = 19
COMMIT/*!*/;
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 -p123456

mysql> 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 值
  或者时间点进行恢复，确保万无一失；