mysql的mysqldump,xtraback备份和还原

一 备份

1.1 为什么要备份

由于 硬件故障、软件故障、自然灾害、黑客攻击、误操作测试等导致数据丢失，所以需要进行数据的备份。

1.2 备份注意要点考虑

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

备份和恢复数据需要在多长时间内完成?

需要备份和恢复哪些数据 ?

在备份和恢复过程中对对运行的服务是否有影响？

这些需要我们在备份和还原的时候进行思考

1.3 我们备份什么？

相应的数据
使用的二进制日志、InnoDB的事务日志         
程序代码（存储过程、函数、触发器、事件调度器）         
服务器的配置文件等。 

1.4 备份类型：

1.4.1完全备份，部分备份

完全备份：整个数据集   

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

1.4.2 完全备份、增量备份、差异备份

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

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

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

1.4.3 冷、温、热备份

冷备：读写操作均不可进行 
温备：读操作可执行；但写操作不可执行 
热备：读写操作均可执行 

PS:
MyISAM：温备，不支持热备   
InnoDB：都支持 

1.4.4物理和逻辑备份

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

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

逻辑备份是备份 sql 语句，在恢复的时候执行备份的 sql 语句实现数据库数据的重现;
物理备份就是备份数据文件了，比较形象点就是 cp 下数据文件，但真正备份的时候自然不是 cp 这么简单。
这 2 种备份各有优劣.
一般来说，物理备份恢复速度比较快，占用空间比较大，逻辑备份速度比较慢，占用空间比较小

二 备份工具

2.1 工具

1） cp, tar等复制归档工具：物理备份工具，适用所有存储引擎；只支持冷备；完 全和部分备份 

2) LVM的快照：先加锁，做快照后解锁，几乎热备；借助文件系统工具进行备份 

3) mysqldump：逻辑备份工具，适用所有存储引擎，温备；支持完全或部分备 份；对InnoDB存储引擎支持热备，结合binlog的增量备份 

4) xtrabackup：由Percona提供支持对InnoDB做热备(物理备份)的工具，支持 完全备份、增量备份 

5) MariaDB Backup： 从MariaDB 10.1.26开始集成，基于Percona XtraBackup 2.3.8实现 

6) mysqlbackup：热备份， MySQL Enterprise Edition组件 

7) mysqlhotcopy：
           PERL 语言实现，几乎冷备，仅适用于MyISAM存储引擎， 使用LOCK TABLES、FLUSH TABLES和cp或scp来快速备份数据库 

2.2 基于LVM的mysql备份和还原

2.2.1 lvm说明

LVM：使用逻辑卷的快照功能备份，几乎热备
前提：
1.数据文件要在逻辑卷上；
2.此逻辑卷所在组必须有足够空间使用快照卷；
3.数据文件和事物日志要在同一个卷上。

2.2.2 lvm备份

**(1) 请求锁定所有表 mysql> FLUSH TABLES WITH READ LOCK;
(2) 记录二进制日志文件及事件位置 mysql> FLUSH LOGS; mysql> SHOW MASTER STATUS; mysql -e 'SHOW MASTER STATUS' > /PATH/TO/SOMEFILE
(3) 创建快照 lvcreate -L 50M -s -p r -n NAME /DEV/VG_NAME/LV_NAME
说明： -s|–snapshot：指定创建快照

(4) 释放锁 mysql> UNLOCK TABLES;
(5) 挂载快照卷，执行数据备份

mount  /path/to/LV_NAME /backup/fullbak
cp -a /mysql/*  /backup

(6) 备份完成后，删除快照卷
(7) 制定好策略，通过原卷备份二进制日志**

cat  /path/to/master.info;查看当前二进制日志位置
mysqlbinlog --start-position=103 mysql-bin.000003 >/backup/bak.sql
当一个事务跨越不同的二进制文件时，开启通过时间备份--start-datetime
$mysqlbinlog --start-datetime=’2020-02-16 14:55:34’ mysql-bin.00003 mysql-bin.000004 > /backup/bak-`date +%F-%H-%M-%S`.sql

 

2.2.3 lvm还原：

cp -a  /backup/fullback  /mysql 注意mysql目录下的文件属主是mysql

/etc/init.d/mysqld restart

mysql>set sql_log_bin=0;关闭二进制日志
mysql>source /backup/back-`date +%F-%H-%M-%S`.sql；还原数据
mysql>set sql_log_bin=1;

三 mysqldump

3.1 mysqldump介绍

mysqldump 是 MySQL 自带的逻辑备份工具。
它是通过协议连接到 MySQL 数据库，将需要备份的数据查询出来，将查询出的数据转换成对应的insert 语句。
当我们需要还原这些数据时，只要执行这些 insert 语句，即可将对应的数据还原。

3.2 命令格式

mysqldump [OPTIONS] database [tables]   >脚本名

mysqldump [OPTIONS] –B DB1 [DB2 DB3...]   > 脚本名

mysqldump [OPTIONS] –A [OPTIONS] > 脚本名

3.3 mysqldump常见选项：

-A --all-databases 备份所有数据库，含create database 

-B --databases db_name…  指定备份的数据库，包括create database语句

-E --events：备份相关的所有event scheduler

-R --routines：备份所有存储过程和自定义函数 

--triggers：备份表相关触发器ÿ