mysql多种备份还原

太过爱你忘了你带给我的痛 2022-05-17 12:37 209阅读 0赞

1、为什么需要备份数据？

在生产环境中我们数据库可能会遭遇各种各样的不测从而导致数据丢失, 大概分为以下几种.

*  硬件故障
 *  软件故障
 *  莫名其妙情况
 *  误操作

所以, 为了在数据丢失之后能够恢复数据, 我们就需要定期的备份数据, 备份数据的策略要根据不同的应用场景进行定制, 大致有几个参考数值, 我们可以根据这些数值从而定制符合特定环境中的数据备份策略。

*  能够容忍丢失多少数据
 *  恢复数据需要多长时间
 *  需要恢复哪一些数据

2、`MySQL`中进行不同方式的备份还要考虑存储引擎是否支持

<table> 
 <tbody> 
  <tr> 
   <td>&nbsp;</td> 
   <td>MyISAM&nbsp;</td> 
   <td>InnoDB</td> 
  </tr> 
  <tr> 
   <td>热备</td> 
   <td>不支持</td> 
   <td>支持</td> 
  </tr> 
  <tr> 
   <td>温备</td> 
   <td>支持</td> 
   <td>支持</td> 
  </tr> 
  <tr> 
   <td>冷备</td> 
   <td>支持</td> 
   <td>支持</td> 
  </tr> 
 </tbody> 
</table>

3、备份方式

1）如果库的数据量不大，可以直接复制mysql数据的存放目录。

2）mysqldump工具备份

3）xtrabackup工具备份

mysqldump是MySQL的一个命令行工具，用于逻辑备份。可以将数据库和表的结构，以及表中的数据分别导出成：create database, create table, insert into的sql语句。当然也可以导出 存储过程，触发器，函数，调度事件(events)。

mysqldump的默认选项：

Variables (--variable-name=value)
    and boolean options {FALSE|TRUE}  Value (after reading options)
    --------------------------------- ----------------------------------------
    all-databases                     FALSE
    all-tablespaces                   FALSE
    no-tablespaces                    FALSE
    add-drop-database                 FALSE
    add-drop-table                    TRUE
    add-drop-trigger                  FALSE
    add-locks                         TRUE
    allow-keywords                    FALSE
    apply-slave-statements            FALSE
    bind-address                      (No default value)
    character-sets-dir                (No default value)
    comments                          TRUE
    compatible                        (No default value)
    compact                           FALSE
    complete-insert                   FALSE
    compress                          FALSE
    create-options                    TRUE
    databases                         FALSE
    debug-check                       FALSE
    debug-info                        FALSE
    default-character-set             utf8
    delayed-insert                    FALSE
    delete-master-logs                FALSE
    disable-keys                      TRUE
    dump-slave                        0
    events                            FALSE
    extended-insert                   TRUE
    fields-terminated-by              (No default value)
    fields-enclosed-by                (No default value)
    fields-optionally-enclosed-by     (No default value)
    fields-escaped-by                 (No default value)
    flush-logs                        FALSE
    flush-privileges                  FALSE
    force                             FALSE
    hex-blob                          FALSE
    host                              (No default value)
    include-master-host-port          FALSE
    insert-ignore                     FALSE
    lines-terminated-by               (No default value)
    lock-all-tables                   FALSE
    lock-tables                       TRUE
    log-error                         (No default value)
    master-data                       0
    max-allowed-packet                25165824
    net-buffer-length                 1046528
    no-autocommit                     FALSE
    no-create-db                      FALSE
    no-create-info                    FALSE
    no-data                           FALSE
    order-by-primary                  FALSE
    port                              0
    quick                             TRUE
    quote-names                       TRUE
    replace                           FALSE
    routines                          FALSE
    set-charset                       TRUE
    single-transaction                FALSE
    dump-date                         TRUE
    socket                            /var/lib/mysql/mysql.sock
    secure-auth                       TRUE
    ssl                               FALSE
    ssl-ca                            (No default value)
    ssl-capath                        (No default value)
    ssl-cert                          (No default value)
    ssl-cipher                        (No default value)
    ssl-key                           (No default value)
    ssl-crl                           (No default value)
    ssl-crlpath                       (No default value)
    ssl-verify-server-cert            FALSE
    tab                               (No default value)
    triggers                          TRUE
    tz-utc                            TRUE
    user                              (No default value)
    verbose                           FALSE
    where                             (No default value)
    plugin-dir                        (No default value)
    default-auth                      (No default value)

查看默认为ture的选项的含义：

add-drop-table                    TRUE     表示在生成表结构语句之前，生成对应的 DROP TABLE IF EXISTS `table_name`; 语句
    add-locks                         TRUE     表示在生成表中数据的 insert into `table_name` values(...) 之前生成 LOCK TABLES `tab` WRITE;语句
    comments                          TRUE     表示生成备注，就是所有 -- 开头的说明，比如：-- Dumping data for  for table `tab`. 最好还是启用；
    create-options                    TRUE     表示在生成表结构时会生成：ENGINE=InnoDB AUTO_INCREMENT=827 DEFAULT CHARSET=utf8; 附加建表选项
    default-character-set             utf8     指定语句：/*!40101 SET NAMES utf8 */;中的字符集；可能你需要改成 --default-character-set=utf8mb4
    disable-keys                      TRUE     表示生产 insert 语句之前，生成：/*!40000 ALTER TABLE `tbl` DISABLE KEYS */; 可以加快insert速度；
    extended-insert                   TRUE     表示生产的insert是insert into `tbl` values(...),(...),数据行按照net-buffer-length分割合并成多个batch insert
    lock-tables                       TRUE     表示在导出的过程中会锁定所有表；
    max-allowed-packet                25165824     最大支持 24M 的数据包；
    net-buffer-length                 1046528      1M大小的socket buffer
    quick                             TRUE     表示在导出语句时，不缓存，直接输出到控制台或者文件中；
    quote-names                       TRUE     表示对表名和列名使用 `` 符号包裹；防止它们是关键字时会出错;
    set-charset                       TRUE     default-character-set=utf8指定字符集，而--set-charset=1/0 表示是否生成/*!40101 SET NAMES utf8 */; 
    dump-date                         TRUE     表示是否在导出文件的末尾生成导出时间：-- Dump completed on 2015-09-15 11:15:10 
    secure-auth                       TRUE     表示登录判断密码时使用新的加密算法，拒绝就的加密算法
    triggers                          TRUE     表示生成触发器脚本；
    tz-utc                            TRUE     表示是否生成：/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */;

备份所有的数据库：

mysqldump -uroot -p1234  --all-databases>/opt/wyl/backup.sql

还原所有的数据库

mysql -uroot -p1234 </opt/wyl/backup.sql

备份多个数据库

mysqldump -uroot –p1234  --databases student zabbix  > student_zabbix.sql

用mysqldump不带有--databases选项备份指定的一个数据库

mysqldump -uzabbix  –pzabbix    zabbix > zabbix.sql

导出某个库多个表的结构和数据： 导出数据库 db1 中的 表 tb1、tb2 的表结构 和 表中数据；

mysqldump -uxxx -p db1 tb1 tb2 > tb1_tb2.sql

注意：当对一个数据库进行备份时省略- -databases后导致的是备份文件名.sql中没有CREATE DATABASE 和USE语句，那么恢复备份文件时，必须指定一个默认的数据库名，由此mysql才知道备份文件恢复到哪个数据库中；不然就会报错，不过你可以使用一个和原始数据库名称不同的数据库名。但是数据内容是一样的。

xtrabackup工具备份，[percona的xtrabackup几种备份机制][percona_xtrabackup]，如下图所示，全量备份，增量备份，压缩备份，和加密备份。

![70][]

在备份之前我们看到有个前要条件，点击去，官网上说需要我们创建一个用户去执行备份操作，并需要给出最小的具备权限。

An SQL example of creating a database user with the minimum privileges required to full backups would be:
    
    mysql> CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY '1234';
    mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO
           'bkpuser'@'localhost';
    mysql> FLUSH PRIVILEGES;

*   xtrabackup 全量备份恢复

一些常用参数：

*  \--defaults-file=\[MY.CNF\]    //指定配置文件：只能从给定的文件中读取默认选项。 且必须作为命令行上的第一个选项；必须是一个真实的文件，它不能是一个符号链接。
 *  \--databases=\#    //指定备份的数据库和表，格式为：--database="db1\[.tb1\] db2\[.tb2\]" 多个库之间以空格隔开，如果此选项不被指定，将会备份所有的数据库。
 *  \--compress    //此选项指示xtrabackup压缩备份的InnoDB数据文件，会生成 \*.qp 文件。
 *  \--decompress    //解压缩qp文件，为了解压缩，必须安装 qpress 工具。 Percona XtraBackup不会自动删除压缩文件，为了清理备份目录，用户应手动删除 \* .qp文件
 *  \--incremental    //这个选项告诉 xtrabackup 创建一个增量备份，而不是完全备份。它传递到 xtrabackup 子进程。当指定这个选项，可以设置 --incremental-lsn 或 --incremental-basedir。如果这2个选项都没有被指定，--incremental-basedir 传递给 xtrabackup 默认值，默认值为：基础备份目录的第一个时间戳备份目录。
 *  \--incremental-basedir=DIRECTORY    //该选项接受一个字符串参数，该参数指定作为增量备份的基本数据集的完整备份目录。它与 --incremental 一起使用。
 *  \--incremental-dir=DIRECTORY    //该选项接受一个字符串参数，该参数指定了增量备份将与完整备份相结合的目录，以便进行新的完整备份。它与 --incremental 选项一起使用。

innobackupex --defaults-file=/etc/my.cnf --user=bkpuser --password=1234 --socket=/tmp/mysql.sock  /data/backup/
    
    
    #执行结果如下：
    
    180811 05:29:46 [01] Copying ./test/db.opt to /data/backup/test/db.opt
    180811 05:29:46 [01]        ...done
    180811 05:29:46 [01] Copying ./student/db.opt to /data/backup/student/db.opt
    180811 05:29:46 [01]        ...done
    180811 05:29:46 Finished backing up non-InnoDB tables and files
    180811 05:29:46 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
    xtrabackup: The latest check point (for incremental): '32800334'
    xtrabackup: Stopping log copying thread.
    .180811 05:29:46 >> log scanned up to (32800334)
    
    180811 05:29:47 Executing UNLOCK TABLES
    180811 05:29:47 All tables unlocked
    180811 05:29:47 Backup created in directory '/data/backup/'
    180811 05:29:47 [00] Writing /data/backup/backup-my.cnf
    180811 05:29:47 [00]        ...done
    180811 05:29:47 [00] Writing /data/backup/xtrabackup_info
    180811 05:29:47 [00]        ...done
    xtrabackup: Transaction log of lsn (32800334) to (32800334) was copied.
    180811 05:29:47 completed OK!

我们查看备份后的数据如下，其中xtrabackup\_checkpoints中的backup\_type = full-backuped。

![70 1][]

整个备份过程可以在上面的打印的日志中可以看到：连接数据库，开始拷贝redo log，拷贝innodb表文件，锁表、拷贝非innodb表文件，停止拷贝redo log，解锁。

### 备份还原： ###

1）首先需要将mysql的data目录进行删除，当然测试的时候我们可以cp一份到其他地方。然后创建一下data这个目录。我们可以看到此时数据库中已经没有数据了。

[root@wyl01 mysql]# service mysql.server stop
    [root@wyl01 mysql]#  mv data  data.bak    #先备份一下
    [root@wyl01 mysql]# mkdir data
    [root@wyl01 data]# mysql -uroot -p1234
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    +--------------------+
    1 row in set (0.00 sec)

2）准备(prepare)一个完全备份，Preparing a backup，执行之后/data/backup/xtrabackup\_checkpoints 文件中的 backup\_type = full-prepared

innobackupex --defaults-file=/etc/my.cnf --user=bkpuser --password=1234 --apply-log /data/backup/2018-08-11_05-25-24

![70 2][]

还原数据库，我们看到数据库已经恢复了。

[root@wyl01 data]#  xtrabackup --copy-back --target-dir=/data/backups/2018-08-11_05-25-24 --datadir=/usr/local/mysql/data
    
    [root@wyl01 data]# innobackupex  --defaults-file=/etc/my.cnf --copy-back  /data/backup/2018-08-11_05-25-24/
    
    #上面2种方案都可以，还原后一定要记得修改一下权限
    [root@wyl01 mysql]# chown -R mysql.mysql data
    [root@wyl01 mysql]# service mysql.server start
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | student            |
    | test               |
    | wyl                |
    | zabbix             |
    +--------------------+
    7 rows in set (0.00 sec)

## ##

### innbackupex增量备份 ###

第一步：做一个完整的备份，注意在做备份钱手动创建ins1，ins2，ins3三个目录。完全备份放在ins1目录下：

innobackupex  -uroot  -p1234 -socket=/tmp/mysql.sock  /data/backup/ins1/

第二步：制作第一次增量备份，在第一步的基础上增量备份，将备份的数据放在ins2中，不过在做增量备份的前，我们需要去数据库创建一个testdb1的数据库。（过程自己做）

innobackupex  -uroot  -p1234 --socket=/tmp/mysql.sock  --incremental /data/backup/ins2/ --incremental-basedir=/data/backup/ins1/2018-08-11_15-42-28/

第三步：在第二步的基础上做增量备份，将备份的数据放在ins3中，不过在做增量备份的前，我们需要去数据库创建一个testdb2的数据库。（过程自己做）

innobackupex  -uroot  -p1234 --socket=/tmp/mysql.sock  --incremental /data/backup/ins3/ --incremental-basedir=/data/backup/ins2/2018-08-11_15-46-10/

备份完，我们看看目录结构：

![70 3][]

### 增量备份还原 ###

第一步：停mysql服务,恢复增量备份1，首先准备还原原始备份。

service mysql.server stop
    rm -rf /usr/local/mysql/data/*    #最好是先有了备份了或者mv到其他目录，测试就无所谓了。 
    innobackupex --apply-log --redo-only  /data/backup/ins1/2018-08-11_15-42-28/  --use-memory=500m --user=root  --password=1234

第二步：合并ins2增量备份到原始备份

innobackupex --apply-log --redo-only /data/backup/ins1/2018-08-11_15-42-28 --incremental-dir=/data/backup/ins2/2018-08-11_15-46-10  --user=root    --password=1234

第三步：合并ins3增量到原始备份

innobackupex --apply-log --redo-only /data/backup/ins1/2018-08-11_15-42-28 --incremental-dir=/data/backup/ins3/2018-08-11_15-48-26/  --user=root    --password=1234

第四步：恢复最终备份的方法与恢复完整备份的方法一样,授权，然后启动mysql服务。

innobackupex --apply-log  /data/backup/ins1/2018-08-11_15-42-28/  --user=root    --password=1234
    cd /usr/local/mysql
    chown -R mysql.myql data/*
    service mysql.server start

我们看到中间我们做的创建testdb1和testdb2的两个数据库：

![70 4][]

[percona_xtrabackup]: https://www.percona.com/doc/percona-xtrabackup/LATEST/index.html
[70]: /images/20220517/c9397b3d444b4f61a87c116d327efd2e.png
[70 1]: /images/20220517/22ddbcaf26094cfb95e968ffe6473b48.png
[70 2]: /images/20220517/127f21b2647e40f9a8971e49af4375a2.png
[70 3]: /images/20220517/258e20788ee04af08fa20faa4e6ef974.png
[70 4]: /images/20220517/bcd149d258944298b7cb4717a51e5da5.png