目录
- 一. 造成数据库丢失的原因
- 二. MySQL 备份的分类
- 2.1 物理备份
- 2.2 逻辑备份
- 三. MySQL 的备份策略
- 3.1 完全备份
- 3.2 差异备份
- 3.3 增量备份
- 四. MySQL常用的备份方法
- 4.1 数据库完全备份分类
- 4.2 增量备份：二进制日志备份
- 4.3 第三方工具备份
- 五. MySQL具体的备份实现操作
- 5.1 MySQL数据完全备份操作
- 5.2 mysqldump备份与恢复
- 5.21 mysqldump的备份操作
- 5.22 mysql恢复操作
- 5.3 MySQL数据库增量备份恢复
- 5.31 MySQL增量备份的特点与意义
- 5.32 修改配置文件/etc/my.cnf 开启二进制日志备份功能
- 5.33 MySQL增量恢复的方法
- 5.331 一般恢复
- 5.332 基于位置的恢复
- 5.333 基于时间点的恢复
- 六. mysql企业备份案例
- 6.1 一般恢复（丢什么数据，找什么数据）
- 6.2 进行一次完全备份
- 6.3 继续录入新的内容并进行增量备份
- 6.4 模拟误操作删除 user_info表
- 6.5 恢复操作
- 6.6 基于位置的恢复
- 6.7 基于时间恢复
一. 造成数据库丢失的原因
- 程序错误
- 人为操作错误
- 运算错误
- 磁盘故障
- 灾难（如火灾、地震）和盗窃
二. MySQL 备份的分类
2.1 物理备份
冷备份：在数据关闭的状态下运行
热备份：在数据库运行的状态时进行备份操作，该备份方法依赖数据库日志文件。
温备份：数据局锁定表格（可以读不可以写）的状态年备份数据
2.2 逻辑备份
逻辑备份是对数据库逻辑组件（如表等数据库对象）的备份，表示为逻辑数据结构（create database、create tabale）
和内容（insert语句或分割文本文件）的信息
三. MySQL 的备份策略
3.1 完全备份
每次对数据库进行完整的备份，即对整个数据的备份，数据库结构和文件结构备份。保存的是完整时刻的数据库
3.2 差异备份
备份那些自从上次完整备份之后被修改过的所有文件，备份的时间节点是从上次完整备份起，备份的数据量会越来越大，
恢复数据只是恢复上次的完整备份和最近一次的差异备份。
3.3 增量备份
只有那些在上次完全备份或者增量备份后被修改的文件才会备份，以上次完成整备份，或者上次的增量备份的时间点，
仅备份这之间的数据变化，因而备份的数据量小，占用空间小，备份数据速度快，但恢复时候，需要从上次的完整备份
开始到最后一次的增量备份之间所有的增量依次恢复，如中间某次的恢复数据备份损坏，将导致数据的丢失。
四. MySQL常用的备份方法
4.1 数据库完全备份分类
4.2 增量备份：二进制日志备份
4.3 第三方工具备份
Percona XtraBackup
Xtrabackup、innobackupex、xbstream
五. MySQL具体的备份实现操作
5.1 MySQL数据完全备份操作
物理冷备份与恢复（tar命令直接打包数据库文件夹）
1） 备份数据库：
[root@localhost ~]# systemctl stop mysqld ###关闭数据库####
[root@localhost ~]# mkdir /backup ###在根下新建个backup目录
[root@localhost ~]# tar zcf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/ #####/usr/local/mysql/data备份压缩
[root@localhost ~]# cd /backup/ ###进入/backup/
[root@localhost backup]# ll ##查看备份
总用量 1328
-rw-r--r-- 1 root root 1359664 8月 12 22:39 mysql_all-2018-08-12.tar.gz2） 模拟故障
[root@localhost backup]# mkdir /bak ####在根下新建bak目录
[root@localhost backup]# mv /usr/local/mysql/data/ /bak/ ####将目录data数据移动到bak 里面去
[root@localhost backup]# cd /usr/local/mysql/ #####仅mysql目录查看data
[root@localhost mysql]# ll ####查看后data目录被移走 ，模拟故障成功3） 数据库恢复
[root@localhost mysql]# mkdir /restore ####根目录新建restore 目录
[root@localhost mysql]# tar xzvf /backup/mysql_all-2020-09-12.tar.gz -C /restore/ ####将备份数据库解压到 /restore目录下
usr/local/mysql/data/
usr/local/mysql/data/ibdata1
usr/local/mysql/data/ib_logfile1
usr/local/mysql/data/ib_logfile0
.....省略.....4） 启动数据库
[root@localhost mysql]# mv /restore/usr/local/mysql/data/ /usr/local/mysql/ ####将备份数据移动到到/usr/local/mysql中
[root@localhost mysql]# systemctl start mysqld ####启动mysql数据库
[root@localhost mysql]# systemctl status mysqld5.2 mysqldump备份与恢复
将指定的库、表、或全部的库导出为SQL脚本
5.21 mysqldump的备份操作
mysqldump备份需要和mysql进行数据交互，如果关闭mysql 则无法备份和恢复
mysqldump [选项] 库名 [表名1] [表名2] … > /备份路径/备份文件名
mysqldump [选项] --databases 库名1 [库名2] … > /备份路径/备份文件名
mysqldump [选项] --all-databases > /备份路径/备份文件名
1）创建实验环境
mysql> create databases auth; ####创建auth 这个数据库
mysql> use auth; ####进入auth 这个数据库
mysql> create table users (user_name CHAR(16) NOT NULL, user_passwd CHAR(48) DEFAULT '', PRIMARY KEY (user_name));
Database changed
mysql> insert into users(user_name,user_passwd) values('zhangsan', password('123456'));
mysql> insert into users values('lisi', password('123456'));2）mysqldump备份数据表
[root@localhost /]# mysqldump -u root -p mysql user >mysql-user.sql ###备份数据路mysql中的user表 ，文件保存在当前操作目录下面
Enter password: ###输入密码abc1233）mysqldump备份数据库
[root@localhost /]# mysqldump -u root -p --databases auth >auth.sql ####备份auth数据库 文件保存在当前操作目录下面
Enter password: ####输入密码abc1235.22 mysql恢复操作
mysql [选项] [库名] [表名] < /备份路径/备份文件名
1）恢复数据库表
[root@localhost /]# mysql -u root -p ###进入数据库###
Enter password: ###输入密码####
mysql> show databases; ###查看数据库####
mysql> create database text ; ####新建数据库text ,等下导表用###
mysql> exit ####退出#####
[root@localhost opt]# mysql -u root -p text < mysql-user.sql
Enter password:
[root@localhost /]# mysql -u root -p -e 'show tables from text' #######验证输出结果####
Enter password:2）恢复数据库
[root@localhost /]# mysql -u root -p -e 'drop database auth' ######模拟故障删除auth数据库
Enter password:
[root@localhost /]# mysql -u root -p -e 'show databases' ######查看databases所有数据库有没有auth，发现没有了
Enter password:
[root@localhost /]# mysql -u root -p < ./auth.sql ####导入数据库auth
Enter password: ####输入密码
[root@localhost /]# mysql -u root -p -e 'show databases' ######查看databases所有数据库有没有auth，发现有了
Enter password:5.3 MySQL数据库增量备份恢复
5.31 MySQL增量备份的特点与意义
1、增量备份的特点
与完全备份完全不同，增量备份没有重复数据，备份量不大，时间短；但其恢复麻烦，需要上次完成完全备份及备份之后的所有增量备份才能恢复。
2、MySQL数据库二进制日志对备份的意义
二进制日志保存了所有更新或者可能更新数据的操作。二进制日志在启动mysql服务器后开始记录，并在文件达到二进制日志所设置的最大值或者接受到flush logs命令后重新创建新的日志文件，生成二进制的文件序列，并及时把这些日志文件保存到安全的存储位置，即可完成一个时间段
的增量备份。
5.32 修改配置文件/etc/my.cnf 开启二进制日志备份功能
[root@localhost mysql]# vim /etc/my.cnf #####编辑my.cnf配置文件
[client]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock
[mysql]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock
[mysqld] #####找到这个模块
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
character_set_server=utf8
pid-file = /usr/local/mysql/mysqld.pid
socket = /usr/local/mysql/mysql.sock
server-id = 1
log-bin=/usr/local/mysql/data/mysql-bin ####这个地方开启二进制日志功能
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,ANSI_QUOTES
[root@localhost mysql]# systemctl restart mysqld生成二进制日志文件
[root@localhost ~]# ll /usr/local/mysql/data/
5.33 MySQL增量恢复的方法
5.331 一般恢复
mysqlbinlog --stop-position=’操作 id’ 二进制日志 |mysql -u 用户名 -p 密码
5.332 基于位置的恢复
1、恢复数据到指定位置
mysqlbinlog --stop-position=’操作 id’ 二进制日志 |mysql -u 用户名 -p 密码
2、从指定的位置开始恢复数据
mysqlbinlog --start-position=’操作 id’ 二进制日志 |mysql -u 用户名 -p 密码
5.333 基于时间点的恢复
1、从日志开头截止到某个时间点的恢复
mysqlbinlog [–no-defaults] --stop-datetime=’年-月-日 小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p 密码
2、从某个时间点到日志结尾的恢复
mysqlbinlog [–no-defaults] --start-datetime=’年-月-日 小时:分钟:秒’
3、从某个时间点到某个时间点的恢复
mysqlbinlog [–no-defaults] --start-datetime=’年-月-日 小时:分钟:秒’ --stop-datetime=’年-月-日小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p 密码
六. mysql企业备份案例
6.1 一般恢复（丢什么数据，找什么数据）
[root@localhost opt]# mysql -u root -p
Enter password:
mysql> create database client;
mysql> use client;
mysql> create table user_info(身份证 char(20) not null,姓名 char(20) not null,姓别 char(4),用户ID号 char(10) not null,资费 int(10));
mysql> insert into user_info values('000006','张三','男','016','10');
mysql> insert into user_info values('000007','李四','女','017','91');
mysql> insert into user_info values('000008','王五','女','018','23');
mysql> select * from user_info;
mysql> show master logs; ####查看当前数据库binlog文件
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 1492 |
+------------------+-----------+
1 row in set (0.00 sec)6.2 进行一次完全备份
mysql> flush tables with read lock; #####备份前需要将数据库加读锁，防止数据在备份时写入。
mysql> exit
[root@localhost ~]# mkdir /mysql_bak
[root@localhost opt]# mysqldump -u root -p client user_info >/mysql_bak/client_userinfo-$(date +%F).sql ####这边括号中%F 的F必须是大写的
Enter password:
[root@localhost ~]# ll /mysql_bak/
mysqldump -u root -p client user_info > /mysql_bak/client.user_info-$(date +%F).sql
Enter password:
[root@localhost mysql_bak]# ll /mysql_bak/
total 4
-rw-r--r-- 1 root root 2055 Sep 13 04:08 client.user_info-2020-09-13.sql
[root@localhost mysql_bak]# ll /usr/local/mysql/data/
-rw-r----- 1 mysql mysql 1539 Sep 13 04:11 mysql-bin.000001
-rw-r----- 1 mysql mysql 154 Sep 13 04:11 mysql-bin.000002
-rw-r----- 1 mysql mysql 78 Sep 13 04:11 mysql-bin.index6.3 继续录入新的内容并进行增量备份
[root@localhost mysql_bak]# mysql -u root -p
Enter password:
mysql> unlock tables; #######解除表锁。
Query OK, 0 rows affected (0.00 sec)
mysql> use client;
mysql> insert into user_info values('000009','赵六','男','019','37');
mysql> insert into user_info values('000010','孙七','男','020','36');
mysql> select * from user_info;
+-----------+--------+--------+-------------+--------+
| 身份证 | 姓名 | 姓别 | 用户ID号 | 资费 |
+-----------+--------+--------+-------------+--------+
| 000006 | 张三 | 男 | 016 | 10 |
| 000007 | 李四 | 女 | 017 | 91 |
| 000008 | 王五 | 女 | 018 | 23 |
| 000009 | 赵六 | 男 | 019 | 37 |
| 000010 | 孙七 | 男 | 020 | 36 |
+-----------+--------+--------+-------------+--------+
5 rows in set (0.00 sec)
mysql> exit
Bye
[root@localhost mysql_bak]# mysqladmin -u root -p flush-logs
Enter password:
[root@localhost ~]# ll /usr/local/mysql/data
-rw-r----- 1 mysql mysql 1539 Sep 13 04:11 mysql-bin.000001
-rw-r----- 1 mysql mysql 797 Sep 13 04:15 mysql-bin.000002
-rw-r----- 1 mysql mysql 154 Sep 13 04:15 mysql-bin.000003
-rw-r----- 1 mysql mysql 117 Sep 13 04:15 mysql-bin.index
[root@localhost ~]# cp /usr/local/mysql/data/mysql-bin.000002 /mysql_bak/6.4 模拟误操作删除 user_info表
[root@localhost mysql_bak]# mysql -u root -p -e 'drop table client.user_info'
Enter password:
[root@localhost mysql_bak]# mysql -u root -p -e 'select * from client.user_info'
Enter password:
ERROR 1146 (42S02) at line 1: Table 'client.user_info' doesn't exist6.5 恢复操作
完全备份的恢复
[root@localhost mysql_bak]# mysql -u root -p client < /mysql_bak/client_userinfo-2018-08-14.sql
Enter password:
[root@localhost mysql_bak]# mysql -u root -p -e'select * from client.user_info'
Enter password:
+-----------+--------+--------+-------------+--------+
| 身份证 | 姓名 | 姓别 | 用户ID号 | 资费 |
+-----------+--------+--------+-------------+--------+
| 000006 | 张三 | 男 | 016 | 10 |
| 000007 | 李四 | 女 | 017 | 91 |
| 000008 | 王五 | 女 | 018 | 23 |
+-----------+--------+--------+-------------+--------+增量备份的恢复
[root@localhost ~]# mysqlbinlog --no-defaults /mysql_bak/mysql-bin.000002 |mysql -u root -p
Enter password:
[root@localhost ~]# mysql -u root -p -e'select * from client.user_info'
Enter password:
+-----------+--------+--------+-------------+--------+
| 身份证 | 姓名 | 姓别 | 用户ID号 | 资费 |
+-----------+--------+--------+-------------+--------+
| 000006 | 张三 | 男 | 016 | 10 |
| 000007 | 李四 | 女 | 017 | 91 |
| 000008 | 王五 | 女 | 018 | 23 |
| 000009 | 赵六 | 男 | 019 | 37 |
| 000010 | 孙七 | 男 | 020 | 36 |
+-----------+--------+--------+-------------+--------+6.6 基于位置的恢复
[root@localhost mysql_bak]# mysql -uroot -p -e 'drop table client.user_info' ### 删除表user_info
Enter password:
[root@localhost mysql_bak]# mysql -uroot -p -e 'select * from client.user_info'
Enter password:
ERROR 1146 (42S02) at line 1: Table 'client.user_info' doesn't exist
[root@localhost ~]# mysql -u root -p -e'select * from client.user_info'
Enter password:
+-----------+--------+--------+-------------+--------+
| 身份证 | 姓名 | 姓别 | 用户ID号 | 资费 |
+-----------+--------+--------+-------------+--------+
| 000006 | 张三 | 男 | 016 | 10 |
| 000007 | 李四 | 女 | 017 | 91 |
| 000008 | 王五 | 女 | 018 | 23 |
+-----------+--------+--------+-------------+--------+[root@localhost ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /mysql_bak/mysql-bin.000002 ####--base64-output=decode-rows 64解码 ### -v 换行显示 查看二进制日志文件
[root@localhost ~]# mysqlbinlog --no-defaults --stop-position='517' /mysql_bak/mysql-bin.000002 |mysql -u root -p ####操作的节点附近即可
[root@localhost ~]# mysql -u root -p -e'select * from client.user_info'
Enter password:
###恢复赵六
[root@localhost ~]# mysqlbinlog --no-defaults --start-position='657' /mysql_bak/mysql-bin.000002 |mysql -u root -p ####操作的节点附近即可
Enter password:
[root@localhost ~]# mysql -u root -p -e'select * from client.user_info'
Enter password:
###恢复孙七
**基于位置恢复的操作，节点不要选择太靠近操作的节点，否则容易恢复失败。**
6.7 基于时间恢复
[root@localhost ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /mysql_bak/mysql-bin.000002 ####--base64-output=decode-rows 64解码 ### -v 换行显示 查看二进制日志文件
mysqlbinlog --no-defaults --stop-datetime='2020-9-13 4:14:09 ' /mysql_bak/mysql-bin.000002 |mysql -u root -p ##恢复赵六
mysqlbinlog --no-defaults --start-datetime='2020-9-13 4:13:56' /mysql_bak/mysql-bin.000002 |mysql -u root -p ##恢复孙七
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
