Mysql 数据备份与恢复

分手后的思念是犯贱 2022-01-31 09:04 388阅读 0赞

**一、相关概念**

**1.备份目的**

防止硬件故障、软件故障、自然灾害、黑客攻击、误操作、测试场景等导致的数据丢失；

**2.注意要点**

最多能容忍丢失多少数据；

恢复数据需要在多长时间内完成；

需要恢复哪些数据；

**3.还原要点**

做还原测试，用于测试备份的可用性；

**4.备份类型**

**1）完全备份，部分备份**

完全备份：备份整个数据集；

部分备份：只备份数据子集，如部分库或表；

**2）完全备份、增量备份、差异备份**

完全备份：备份整个数据集；

增量备份：仅备份最近一次完全备份或增量备份（如果存在增量）以来变化的数据，备份较快，还原复杂；

差异备份：仅备份最近一次完全备份以来变化的数据，备份较慢，还原简单；

\------注意：二进制日志文件不应该与数据文件放在同一磁盘；

**3）冷、温、热备份**

冷备：读写操作均不可进行；

温备：读操作可执行；但写操作不可执行；

热备：读写操作均可执行；

\------MyISAM：支持温备，不支持热备；

\------InnoDB：都支持；

**4）物理和逻辑备份**

物理备份：直接复制数据文件进行备份，与存储引擎有关，占用较多的空间，速度快；

逻辑备份：从数据库中 "导出" 数据另存而进行的备份，与存储引擎无关，占用空间少，速度慢，可能丢失精度；

**5.考虑因素**

温备的持锁多久；

备份产生的负载；

备份过程的时长；

恢复过程的时长；

**6.备份什么**

数据；

二进制日志、InnoDB的事务日志；

程序代码（存储过程、存储函数、触发器、事件调度器）；

服务器的配置文件；

**7.备份目标**

数据库数据，每个表空间单独存放；

二进制日志，需要和数据分开存储；

InnoDB的事务日志；

存储过程、存储函数、触发器或事件调度器等；

服务器的配置文件：/etc/my.cnf；

**8.备份工具**

基于 lvm 快照备份：几乎热备，需要在拍快照前锁表；

cp + tar == 物理冷备；

mysqldump + InnoDB + binlog == 完全热备 + 增量备份；

Xtrabackup + InnoDB + binlog == 完全热备 + 增量备份；

--------------------

**二、cp + tar**

cp + tar == 物理冷备；

将数据目录（/var/lib/mysql）打包压缩备份，备份和还原都需要停服务，不推荐；

0）初始数据

[root@mariadb ~]# mysql -uroot -p
    Enter password: 
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MariaDB connection id is 4
    Server version: 5.5.60-MariaDB MariaDB Server
    
    Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    MariaDB [(none)]> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | school             |
    +--------------------+
    4 rows in set (0.00 sec)

1）备份

[root@mariadb ~]# mkdir backup
    
    [root@mariadb ~]# cd backup/
    
    [root@mariadb backup]# systemctl stop mariadb
    
    [root@mariadb backup]# tar -zcvf mariadb_back_"$(date +%F)".tar.gz /var/lib/mysql

2）模拟误操作删除数据库

[root@mariadb backup]# mysql -uroot -p                          
    Enter password: 
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MariaDB connection id is 4
    Server version: 5.5.60-MariaDB MariaDB Server
    
    Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    MariaDB [(none)]> drop database school;
    Query OK, 1 row affected (0.01 sec)
    
    MariaDB [(none)]> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    +--------------------+
    3 rows in set (0.00 sec)

3）还原

[root@mariadb backup]# tar -zxvf mariadb_back_2019-05-14.tar.gz
    
    [root@mariadb backup]# systemctl stop mariadb
    
    [root@mariadb backup]# /bin/cp -af var/lib/mysql/ /var/lib/
    ## 上面的 cp 命令使用绝对路径，并使用 -f 选项忽略覆盖文件提醒；
    
    [root@mariadb backup]# systemctl start mariadb
    
    [root@mariadb backup]# mysql -uroot -p                          
    Enter password: 
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MariaDB connection id is 4
    Server version: 5.5.60-MariaDB MariaDB Server
    
    Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    MariaDB [(none)]> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | school             |
    +--------------------+
    4 rows in set (0.00 sec)

school 数据库已恢复；

--------------------

**三、mysqldump + InnoDB + binlog**

**1.mysqldump 选项**

语法： 
     mysqldump [OPTIONS] database [tables] 
     mysqldump [OPTIONS] –B DB1 [DB2 DB3...]
     mysqldump [OPTIONS] –A [OPTIONS]
    
    选项：
        -A：备份所有库；
        -B db_name1,[db_name2,...]：备份指定库；
        -E：备份相关的所有 event scheduler；
        -R：备份所有存储过程和存储函数；
        --triggers：备份表相关触发器，默认启用，用--skip-triggers，不备份触发器；
        --master-data={1|2}：
             1：所备份的数据之前加一条记录为CHANGE MASTER TO语句，非注释，不指定默认为1；
             2：记录为注释的CHANGE MASTER TO语句，注意：此选项会自动关闭--lock-tables功能，自动打开--lock-all-tables功能（除非开启--single-transaction）；
        -F：备份前滚动日志，锁定表完成后，执行flush logs命令,生成新的二进制日志文件，配合-A时，会导致刷新多次数据库，在同一时刻执行转储和日志刷新，则应同时使用--flush-logs和-x，--master-data或-single-transaction,此时只刷新一次；建议：和-x，--master-data或 --single-transaction一起使用
        --compact 去掉注释，适合调试，生产不使用；
        -d：只备份表结构；
        -t：只备份数据，不备份create table；
        -n：不备份create database，可被-A或-B覆盖；
        --flush-privileges：备份前刷新授权表，备份mysql库或相关时需要使用；
        -f：忽略SQL错误，继续执行；
        --hex-blob：使用十六进制符号转储二进制列（例如，“abc”变为0x616263），受影响的数据类型包括BINARY， VARBINARY，BLOB，BIT；
        -q：不缓存查询，直接输出，加快备份速度；

MyISAM备份选项

> 支持温备；不支持热备，所以必须先锁定要备份的库，而后启动备份操作；  
> **-x,--lock-all-tables**：加全局读锁，锁定所有库的所有表，同时加**--single-transaction**或**--lock-**tables选项会关闭此选项功能，注意：数据量大时，可能会导致长时间无法并发访问数据库；  
> **-l,--lock-tables**：对于需要备份的每个数据库，在启动备份之前分别锁定其所有表，默认为**on,--skip-lock-tables**选项可禁**用，对备份MyISAM的多个库，可能会造成数据不一致；**

InnoDB备份选项

> 支持热备，可用温备但不建议用；  
> **--single-transaction**：此选项 Innodb中 推荐使用，不适用 MyISAM，此选项会在开始备份前，先执行 START TRANSACTION 指令开启事务，通过在单个事务中转储所有表来创建一致的快照。仅适用于存储在支持多版本控制的存储引擎中的表（目前只有InnoDB可以）; 转储不保证与其他存储引擎保持一致。在进行单事务转储时，要确保有效的转储文件（正确的表内容和二进制日志位置），需要保证没有其他连接使用以下语句：ALTER TABLE，DROP TABLE，RENAME TABLE，TRUNCATE TABLE。此选项和 **--lock-tables**（此选项隐含提交挂起的事务）选项是相互排斥，备份大型表时，建议将 --**single-transaction** 选项和 **--quick** 合一起使用；

InnoDB建议备份策略：
        mysqldump –uroot –A –F –E –R  --single-transaction --master-data=1 --flush-privileges  --triggers --hex-blob > $BACKUP/fullbak_$BACKUP_TIME.sql
    
    MyISAM建议备份策略：
        mysqldump –uroot –A –F –E –R –x --master-data=1 --flush-privileges  --triggers --hex-blob > $BACKUP/fullbak_$BACKUP_TIME.sql

**2. mysqldump 备份示例**

1）初始环境

[root@mariadb ~]# mysql -uroot -p
    Enter password: 
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MariaDB connection id is 14
    Server version: 5.5.60-MariaDB MariaDB Server
    
    Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    MariaDB [(none)]> show variables like '%storage_engine%';
    +------------------------+--------+
    | Variable_name          | Value  |
    +------------------------+--------+
    | default_storage_engine | InnoDB |
    | storage_engine         | InnoDB |
    +------------------------+--------+
    2 rows in set (0.00 sec)
    
    MariaDB [(none)]> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | mytest             |
    | performance_schema |
    | school             |
    +--------------------+
    5 rows in set (0.00 sec)
    
    MariaDB [(none)]> use mytest;
    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
    MariaDB [mytest]> show tables;
    +------------------+
    | Tables_in_mytest |
    +------------------+
    | color            |
    +------------------+
    1 row in set (0.00 sec)
    
    MariaDB [mytest]> select * from color;
    +------+--------+
    | id   | type   |
    +------+--------+
    |    1 | red    |
    |    2 | green  |
    |    3 | blue |
    +------+--------+
    3 rows in set (0.00 sec)
    
    MariaDB [mytest]>

2）完全备份

[root@mariadb ~]# mysqldump -uroot -p -A -F -E -R --single-transaction --master-data=2 --flush-privileges > full_`date +%F-%T`.sql          
    Enter password: 
    [root@mariadb ~]# ls -l
    total 508
    -rw-r--r-- 1 root root 517114 May 20 05:38 full_2019-05-20-05:37:58.sql

3）模拟误操作

模拟更新某字段时，update 语句后面未跟 where 条件：

MariaDB [mytest]> insert into color values(5,'yellow');
    Query OK, 1 row affected (0.01 sec)
    
    MariaDB [mytest]> insert into color values(6,'block'); 
    Query OK, 1 row affected (0.00 sec)
    
    MariaDB [mytest]> update color set type='black';
    Query OK, 5 rows affected (0.01 sec)
    Rows matched: 5  Changed: 5  Warnings: 0
    
    MariaDB [mytest]> select * from color;
    +------+-------+
    | id   | type  |
    +------+-------+
    |    1 | black |
    |    2 | black |
    |    3 | black |
    |    5 | black |
    |    6 | black |
    +------+-------+
    5 rows in set (0.00 sec)

误操作之后，其他用户又向该表插入了数据，最终数据如下：

MariaDB [mytest]> select * from color;
    +------+--------+
    | id   | type   |
    +------+--------+
    |    1 | black  |
    |    2 | black  |
    |    3 | black  |
    |    5 | black  |
    |    6 | black  |
    |    7 | grey   |
    |    8 | pink   |
    |    9 | brown  |
    |   10 | purple |
    |   11 | orange |
    +------+--------+
    10 rows in set (0.00 sec)

4）数据恢复

数据恢复的过程，实际上是将所有事务性 sql 再次执行一遍的过程，所以为避免 sql 语句冲突，最好新建一个 mysql 实例，在新实例中进行数据恢复。思路：

a.首先使用 mysqldump 的最近一次全量备份文件进行恢复；

[root@mariadb2 ~]# ls
    full_2019-05-20-05:37:58.sql
    [root@mariadb2 ~]# mysql -uroot -p < full_2019-05-20-05\:37\:58.sql 
    Enter password:

b.查看全量备份在二进制文件中的位置：

[root@mariadb ~]# head -30 full_2019-05-20-05\:37\:58.sql | grep -i 'change master'
    -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000012', MASTER_LOG_POS=245;

c.具体的二进制文件如下：

[root@mariadb ~]# cd /var/lib/mysql
    [root@mariadb mysql]# ls -l mysql-bin*
    -rw-rw---- 1 mysql mysql  807 May 20 03:45 mysql-bin.000001
    -rw-rw---- 1 mysql mysql  487 May 20 03:46 mysql-bin.000002
    -rw-rw---- 1 mysql mysql 1003 May 20 03:51 mysql-bin.000003
    -rw-rw---- 1 mysql mysql  288 May 20 03:53 mysql-bin.000004
    -rw-rw---- 1 mysql mysql  487 May 20 03:56 mysql-bin.000005
    -rw-rw---- 1 mysql mysql 1278 May 20 04:13 mysql-bin.000006
    -rw-rw---- 1 mysql mysql  690 May 20 04:16 mysql-bin.000007
    -rw-rw---- 1 mysql mysql  288 May 20 04:29 mysql-bin.000008
    -rw-rw---- 1 mysql mysql 1073 May 20 05:13 mysql-bin.000009
    -rw-rw---- 1 mysql mysql 3220 May 20 05:32 mysql-bin.000010
    -rw-rw---- 1 mysql mysql 1055 May 20 05:38 mysql-bin.000011
    -rw-rw---- 1 mysql mysql 1070 May 20 05:41 mysql-bin.000012
    -rw-rw---- 1 mysql mysql  681 May 20 05:42 mysql-bin.000013
    -rw-rw---- 1 mysql mysql  686 May 20 05:43 mysql-bin.000014
    -rw-rw---- 1 mysql mysql  245 May 20 05:43 mysql-bin.000015
    -rw-rw---- 1 mysql mysql  285 May 20 05:43 mysql-bin.index

所以 mysql-bin.000012 之前的二进制的文件丢弃不管，只在这之后的二进制文件中找出误操作的 sql 语句；

d.将二进制文件导出为用于恢复 sql 语句文件：

[root@mariadb mysql]# mysqlbinlog mysql-bin.000012 --start-position=245 > errquery.sql
    [root@mariadb mysql]# mysqlbinlog mysql-bin.000013 >> errquery.sql                     
    [root@mariadb mysql]# mysqlbinlog mysql-bin.000014 >> errquery.sql 
    [root@mariadb mysql]# mysqlbinlog mysql-bin.000015 >> errquery.sql

f.在 errquery.sql 文件中找到 执行错误的 sql 语句，如下，然后删除这几行：

/*!*/;
    # at 710
    #190520  5:40:45 server id 28  end_log_pos 804  Query   thread_id=19    exec_time=0     error_code=0
    SET TIMESTAMP=1558345245/*!*/;
    update color set type='black'

由于这里是模拟测试，很容易就跟找到执行错误的 sql 语句。在实际的生产环境中，要从海量数据中找到执行错误的 sql 语句，可根据 二进制的文件的时间戳和误操作的大概时间点去判断记录的误操作的具体二进制文件，缩小范围，然后根据关键字去查询；

g.开始数据恢复：

[root@mariadb2 ~]# mysql -uroot -p < errquery.sql 
    Enter password:

h.查看恢复情况：

[root@mariadb2 ~]# mysql -uroot -p
    Enter password: 
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MariaDB connection id is 15
    Server version: 5.5.60-MariaDB MariaDB Server
    
    Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    MariaDB [(none)]> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | mytest             |
    | performance_schema |
    | school             |
    +--------------------+
    5 rows in set (0.00 sec)
    
    MariaDB [(none)]> use mytest
    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
    MariaDB [mytest]> show tables;
    +------------------+
    | Tables_in_mytest |
    +------------------+
    | color            |
    +------------------+
    1 row in set (0.00 sec)
    
    MariaDB [mytest]> select * from color; 
    +------+--------+
    | id   | type   |
    +------+--------+
    |    1 | red    |
    |    2 | green  |
    |    3 | blue   |
    |    5 | yellow |
    |    6 | block  |
    |    7 | grey   |
    |    8 | pink   |
    |    9 | brown  |
    |   10 | purple |
    |   11 | orange |
    +------+--------+
    10 rows in set (0.00 sec)

数据已恢复；

--------------------