所有备份数据都应放在非数据库本地，而且建议有多份副本。
测试环境中做日常恢复演练，恢复较备份更为重要。
备份： 能够防止由于机械故障以及人为误操作带来的数据丢失，例如将数据库文件保存在了其它地方。
冗余： 数据有多份冗余，但不等备份，只能防止机械故障还来的数据丢失，例如主备模式、数据库集群。
备份过程中必须考虑因素：
1. 数据的一致性
2. 服务的可用性
逻辑备份： 备份的是建表、建库、插入等操作所执行SQL语句（DDL DML DCL），适用于中小型数据库，效率相对较低。
mysqldump
binlog
mydumper
phpmyadmin
物理备份： 直接复制数据库文件，适用于大型数据库环境，不受存储引擎的限制，但不能恢复到不同的MySQL版本。
tar,cp
mysqlhotcopy 只能用于备份MyISAM。
xtrabackup
inbackup
lvm snapshot
一、物理备份的方式
1.完全备份-----完整备份：
每次都将所有数据（不管自第一次备份以来有没有修改过），进行一次完整的复制.
特点：占用空间大，备份速度慢，但恢复时一次恢复到位，恢复速度快。
2.增量备份: 每次备份上一次备份到现在产生的新数据
特点：因每次仅备份自上一次备份（注意是上一次，不是第一次）以来有变化的文件，所 以备份体积小，备份速度快，但是恢复的时候，需要按备份时间顺序，逐个备份版本进行恢 复，恢复时间长。
1566269843796.png
3.差异备份:只备份跟完整备份不一样的
在第一次完整备份之后，第二次开始每次都将所有文件与第一次完整备份的文件做比较，把自第一次完整备份以来所有修改过的文件进行备份，且以后每次备份都是和第一次完整备份进行比较（注意是第一次，不是上一次），备份自第一次完整备份以来所有的修改过的文件。
特点：占用空间比增量备份大，比完整备份小，恢复时仅需要恢复第一个完整版本和最后一次的差异版本，恢复速度介于完整备份和增量备份之间。
1566269915833.png
热备份
数据库启动同时给客户端提供服务的情况下
冷备份
数据库要关掉或者不能给客户端提供服务
二、percona-xtrabackup 物理备份
是开源免费的支持MySQL 数据库热备份的软件，它能对InnoDB和XtraDB存储引擎的数据库非阻塞地备份。它不暂停服务创建Innodb热备份；
为mysql做增量备份；在mysql服务器之间做在线表迁移；使创建replication更加容易；备份mysql而不增加服务器的负载。
percona是一家老牌的mysql技术咨询公司。它不仅提供mysql的技术支持、培训、咨询，还发布了mysql的分支版本--percona Server。并围绕percona Server还发布了一系列的mysql工具。
1、安装xtrabackup
安装xtrabackup
# wget http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm
# rpm -ivh percona-release-0.1-4.noarch.rpm
[root@mysql_server yum.repos.d]# vim percona-release.repo
1566272559277.png
[root@mysql_server yum.repos.d]# yum -y install percona-xtrabackup-24.x86_64
2.完全备份流程:
[root@mysql_server ~]# mkdir /backup/full -p
[root@mysql_server ~]# innobackupex --user=root --password='Duan@123' /backup/full/
image.png
查看:
[root@mysql_server ~]# ls -l /backup/full/
total 0
drwxr-x---. 6 root root 205 Jan 30 10:41 2020-01-30_10-41-07
完全备份恢复流程：
1. 停止数据库
2. 清理环境
3. 重演回滚
4. 恢复数据
5. 修改权限
6. 启动数据库
1、停止数据库
[root@mysql_server ~]# systemctl stop mysqld
2、清理环境
[root@mysql_server ~]# rm -rf /var/lib/mysql/*
[root@mysql_server ~]# rm -rf /var/log/mysqld.log
[root@mysql_server ~]# rm -rf /var/log/mysql-slow/slow.log
3、重演回滚
[root@mysql_server ~]# innobackupex --apply-log /backup/full/2020-01-30_10-41-07/
4、恢复数据
恢复之前需要确认配置文件内有数据库目录指定，不然xtrabackup不知道恢复到哪里
# cat /etc/my.cnf
datadir=/var/lib/mysql
[root@mysql_server ~]# innobackupex --copy-back /backup/full/2020-01-30_10-41-07/
5、修改权限
[root@mysql_server ~]# chown mysql.mysql /var/lib/mysql/ -R
6、启动数据库
[root@mysql_server ~]# systemctl start mysqld
3.增量备份流程
原理：每次备份上一次备份到现在产生的新数据
创建测试库：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
mysql> use test
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
+----------------+
mysql> select * from t1;
+------+
| id |
+------+
| 1 |
+------+
1.先创建一个完整备份
[root@mysql_server ~]# mkdir /backup/incremental
[root@mysql_server ~]# innobackupex --user=root --password='Duan@123' /backup/incremental/
[root@mysql_server ~]# cd /backup/incremental/
[root@mysql_server incremental]# ls
2020-01-30_11-26-13
[root@mysql_server incremental]# mv 2020-01-30_11-26-13/ 1/
[root@mysql_server incremental]# ls
1
2.模拟数据变化
数据第一次变化：
[root@mysql_server]# mysql -uroot -pDuan@123 -e"insert into test.t1 values(2);"
[root@mysql_server]# mysql -uroot -pDuan@123 -e"select * from test.t1;"
+------+
| id |
+------+
| 1 |
| 2 |
+------+
[root@mysql_server ~]# innobackupex --user=root --password='Duan@123' --incremental /backup/incremental/ --incremental-basedir=/backup/incremental/1/
--incremental-basedir:基于哪个增量
[root@mysql_server ~]# ls /backup/incremental/
1 2020-01-30_11-38-06 ---相当于第一次数据变化的增量备份
[root@mysql_server ~]# mv /backup/incremental/2020-01-30_11-38-06/ /backup/incremental/2
数据第二次变化：
[root@mysql_server ~]# mysql -uroot -pDuan@123 -e"insert into test.t1 values(3);"
[root@mysql_server ~]# innobackupex --user=root --password='Duan@123' --incremental /backup/incremental/ --incremental-basedir=/backup/incremental/2
#基于上一次的备份为目录
[root@mysql_server ~]# mv /backup/incremental/2020-01-30_11-42-15/ /backup/incremental/3
[root@mysql_server ~]# ls /backup/incremental/
1 2 3 --相当于第二次数据变化的增量备份
[root@mysql_server ~]# ls /backup/incremental/
假设：
1 2 3
全备周一 增备周二 增备周三
生产中套路也是这样，只不过操作都写在脚本里
增量备份恢复流程
1. 停止数据库
2. 清理环境
3. 依次重演回滚redo log #注意
4. 恢复数据
5. 修改权限
6. 启动数据库
1、停止数据库
[root@mysql_server ~]# systemctl stop mysqld
2、清理环境
[root@mysql_server ~]# rm -rf /var/lib/mysql/*
3、依次重演回滚
[root@mysql_server ~]# innobackupex --apply-log --redo-only /backup/incremental/1
[root@mysql_server ~]# innobackupex --apply-log --redo-only /backup/incremental/1 --incremental-dir=/backup/incremental/2
--incremental-dir：增量目录
#将想要恢复的数据节点按顺序回滚到第一次完备回滚的目录中
#如果想要回复到3这个节点，那么回滚顺序就是1，2，最后才是3
[root@mysql_server ~]# innobackupex --apply-log --redo-only /backup/incremental/1 --incremental-dir=/backup/incremental/3
4、恢复数据
[root@mysql_server ~]# innobackupex --copy-back /backup/incremental/1
5、修改权限
[root@mysql_server ~]# chown -R mysql.mysql /var/lib/mysql
6、启动数据库
[root@mysql_server ~]# systemctl start mysqld
[root@mysql_server ~]# mysql -uroot -pDuan@123 -e'select * from test.t1;'
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
4、差异备份流程
环境准备
[root@mysql_server ~]# mysql -uroot -pDuan@123
mysql> drop table test.t1;
mysql> create table test.t1(id int);
mysql> insert into test.t1 values(1);
mysql> select * from test.t1;
+------+
| id |
+------+
| 1 |
+------+
[root@mysql_server ~]# mkdir /backup/difference
第一次完备：
[root@mysql_server ~]# innobackupex --user=root --password='Duan@123' /backup/difference/
[root@mysql_server ~]# mv /backup/difference/2020-01-30_12-10-47/ /backup/difference/1
[root@mysql_server ~]# ls /backup/difference/
1
第一次差异备份环境准备
[root@mysql_server ~]# mysql -uroot -pDuan@123 -e"insert into test.t1 values(2);"
第一次差异备份
语法: # innobackupex --user=root --password=888 --incremental /xtrabackup --incremental-basedir=/xtrabackup/完全备份目录
[root@mysql_server ~]# innobackupex --user=root --password='Duan@123' --incremental /backup/difference/ --incremental-basedir=/backup/difference/1
#备份目录基于周一的备份
[root@mysql_server ~]# mv /backup/difference/2020-01-30_12-15-12/ /backup/difference/2
第二次差异备份环境准备
[root@mysql_server ~]# mysql -uroot -pDuan@123 -e"insert into test.t1 values(3);"
第二次差异备份
[root@mysql_server ~]# innobackupex --user=root --password='Duan@123' --incremental /backup/difference/ --incremental-basedir=/backup/difference/1
命令和第一次差异备份一模一样，都是基于第一次完备进行备份
[root@mysql_server ~]# mv /backup/difference/2020-01-30_12-18-25/ /backup/difference/3
第三次差异备份环境准备
[root@mysql_server ~]# mysql -uroot -pDuan@123 -e"insert into test.t1 values(4);"
第三次差异备份
[root@mysql_server ~]# innobackupex --user=root --password='Duan@123' --incremental /backup/difference/ --incremental-basedir=/backup/difference/1
命令也是一样
[root@mysql_server ~]# mv /backup/difference/2020-01-30_12-21-07/ /backup/difference/4
[root@mysql_server ~]# ls /backup/difference/
1 2 3 4
[root@mysql_server ~]# ls /backup/difference/
1 2 3 4
完备 基于完备的第一次差异备份
差异备份恢复流程
1. 停止数据库
2. 清理环境
3. 重演回滚redo log（周一，某次差异）#注意
4. 恢复数据
5. 修改权限
6. 启动数据库
1、停止数据库
[root@mysql_server ~]# systemctl stop mysqld
2、清理环境
[root@mysql_server ~]# rm -rf /var/lib/mysql/*
3、重演回滚
回滚只需要回第一次完备，和你想要恢复的那一个节点
[root@mysql_server ~]# innobackupex --apply-log --redo-only /backup/difference/1
恢复差异的redo log
语法:# innobackupex --apply-log --redo-only /xtrabackup/完全备份目录（周一）--incremental-dir=/xtrabacku/某个差异备份
[root@mysql_server ~]# innobackupex --apply-log --redo-only /backup/difference/1 --incremental-dir=/backup/difference/3
这里我们恢复第二次差异备份这个节点，表中数据应该是1，2，3
4、恢复数据
[root@mysql_server ~]# innobackupex --copy-back /backup/difference/1
5、修改权限
[root@mysql_server ~]# chown -R mysql.mysql /var/lib/mysql
6、启动数据库
[root@mysql_server ~]# systemctl start mysqld
[root@mysql_server ~]# mysql -uroot -pDuan@123 -e'select * from test.t1;'
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3.mysqldump逻辑备份 ---- 推荐优先使用
mysqldump可以保证 数据一致，服务可用。
如何保证数据一致?在备份的时候进行锁表会自动锁表。锁住之后在备份。
本身为客户端工具:
远程备份语法: # mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql
本地备份语法: # mysqldump -u用户名 -p密码 数据库名 > 备份文件.sql
常用备份选项
-A, --all-databases
备份所有库
-B, --databases bbs test mysql
备份多个数据库
-F, --flush-logs
备份之前刷新binlog日志
--default-character-set
指定导出数据时采用何种字符集，如果数据表不是采用默认的 latin1 字符集的话，那么导出时必须指定该选项，否则再次导入数据后将产生乱码问题。
--no-data，-d
不导出任何数据，只导出数据库表结构。
注意
使用 mysqldump 备份数据库时避免锁表
对一个正在运行的数据库进行备份请慎重！！ 如果一定要 在服务运行期间备份，可以选择添加 --single-transaction选项，
类似执行： mysqldump --single-transaction -u root -p123456 dbname > mysql.sql
环境准备
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
mysql> use test
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
+----------------+
mysql> select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
[root@mysql_server ~]# mkdir /home/backup
1、备份表
语法: # mysqldump -u root -p密码 库名 表名 > /db1.t1.bak
[root@mysql_server ~]# mysqldump -uroot -pDuan@123 test t1 > /home/backup/test.t1.bak
备份多个表：
语法: mysqldump -u root -p密码 库名 表名1 表名2 > /db1.t1_t2.bak
2、备份库
备份一个库：相当于将这个库里面的所有表全部备份。
语法: # mysqldump -u root -p1 db1 > /db1.bak
[root@mysql_server ~]# mysqldump -uroot -pDuan@123 test > /home/backup/test.bak
备份多个库：
语法：mysqldump -u root -p1 -B db1 db2 db3 > /db123.bak
备份所有的库：
语法：# mysqldump -u root -p1 -A > /alldb.bak
3、恢复数据库和表
为保证数据一致性，应在恢复数据之前停止数据库对外的服务,停止binlog日志 因为binlog使用binlog日志恢复数据时也会产生binlog日志。
清理环境
[root@mysql_server ~]# mysql -uroot -pDuan@123
mysql> drop database test;
恢复库
登陆mysql创建一个库
mysql> create database test;
恢复：
[root@mysql_server ~]# mysql -uroot -pDuan@123 test < /home/backup/test.bak
恢复表
登陆到刚才恢复的库中将t1表删除掉
mysql> use test
mysql> drop table t1;
开始恢复:
mysql> set sql_log_bin=0; #停止binlog日志
mysql> source /home/backup/test.t1.bak; --加路径和备份的文件
恢复方式二：
# mysql -u root -p1 db1 < db1.t1.bak
库名 备份的文件路径
4、备份及恢复表结构
1.备份表结构：
语法：mysqldump -uroot -p123456 -d database table > dump.sql
[root@mysql_server ~]# mysqldump -uroot -p'Duan@123' -d test t1 > /home/back/t1.bak
恢复表结构：
登陆数据库创建一个库
mysql> create database t1;
语法：# mysql -u root -p密码 -D 库名 < t1.bak
[root@mysql_server ~]# mysql -uroot -p'Duan@123' -D t1 < /home/back/t1.bak
5、数据的导入导出。没有表结构。
表的导出和导入只备份表内记录，不会备份表结构，需要通过mysqldump备份表结构，恢复时先恢复表结构，再导入数据。
mysql> show variables like "secure_file_priv"; ----查询导入导出的目录。（保证数据安全做共享）
+------------------+-----------------------+
| Variable_name | Value |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ | //可以修改
+------------------+-----------------------+
修改安全文件目录：
1.创建一个目录：mkdir 路径目录
[root@mysql_server ~]# mkdir /sql
2.修改权限
[root@mysql_server ~]# chown mysql.mysql /sql
3.编辑配置文件：
vim /etc/my.cnf
在[mysqld]里追加
secure_file_priv=/sql
4.重新启动mysql.
1.导出数据
登陆数据查看数据
mysql> show databases; #找到test库
mysql> use test #进入test库
mysql> show tables; #找到它t1表
mysql> select * from t1 into outfile '/sql/test.t1.bak';
添加修饰的：
mysql> select * from t1 into outfile '/sql/test.t1.bak1' fields terminated by ',' lines terminated by '\n';
注：
fields terminated by ',' ：字段以逗号分割
lines terminated by '\n'：结尾换行
2.数据的导入
先将原来表里面的数据清除掉，只保留表结构
mysql> delete from t1;
mysql> load data infile '/sql/test.t1.bak' into table t1;
如果将数据导入别的表，需要创建这个表并创建相应的表结构。
6、通过binlog恢复
开启binlog日志：
1566533393301.png
创建目录并修改权限
[root@mysql_server ~]# mkdir /var/log/sql-bin
[root@mysql_server ~]# chown mysql.mysql /var/log/sql-bin
[root@mysql_server ~]# systemctl restart mysqld
1566533482923.png
mysql> flush logs; #刷新binlog日志会截断产生新的日志文件
mysql> create database testdb;
mysql> create table testdb.t1(id int); #创建一个表
1566533650005.png
根据位置恢复
找到要恢复的sql语句的起始位置、结束位置
[root@mysql-server sql-bin]# mysqlbinlog mylog.000002
1566533960687.png
测试:
[root@mysql_server ~]# mysql -uroot -p'Duan@123'
mysql> drop table testdb.t1; #将这个表删除
Query OK, 0 rows affected (0.01 sec)
恢复：
[root@mysql_server ~]# cd /var/log/sql-bin/
[root@mysql_server sql-bin]# mysqlbinlog --start-position 219 --stop-position 321 mylog.000002 |mysql -uroot -p'Duan@123'
mysql> use testdb
mysql> show tables;
网友评论