个人整理，感谢观看
- 一，为什么要做数据备份？
- 二，备份的分类
- 三，常见的备份方法
- 四，mysql完全备份操作
-
- 1.物理备份与恢复
- 2.mysqldump备份和恢复
- 五，增量备份和恢复
-
- 1、增量备份的特点
- 2、MySQL数据库二进制日志对备份的意义
- 方法:
-
- 一般恢复：
- 基于位置恢复：
- 基于时间戳恢复
一，为什么要做数据备份？
- 在生产环境中，数据的安全性非常重要，任何数据的丢失都可能会产生严重的后果，
- 造成数据丢失的原因：
程序错误
人为操作失误
运算错误
磁盘故障
灾难
等等… - 所以说备份数据库是非常重要的，对咱们的数据安全性也就能大大的提高
二，备份的分类
- 物理备份
对数据库操作系统的物理文件（数据文件，日志文件等…）备份
物理备份方法：
冷备份（脱机备份）：需要关闭数据库，才能进行
热备份（联机备份）：数据库处于运行状态，依赖数据库的日志文件
温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作
-
逻辑备份：对数据库逻辑组件（数据库中的表…）的备份
-
从数据库的备份策略角度，备份可分为
完全备份：每次对数据库进行完整的备份
差异备份：备份自从上次完全备份之后被修改过的文件
增量备份：只有在上次完全备份或增量备份后被修改的文件才会被备份
三，常见的备份方法
1.物理冷备份
tar命令
2.专用备份工具
mysqldump
mysqlhotcopy
3.二进制日志
4.第三方工具备份
Percona XtraBackup
Xtrabackup、innobackupex、xbstream
四，mysql完全备份操作
1.物理备份与恢复
备份：
systemctl stop mysqld #先关闭数据库服务
mkdir /bakup #创建备份目录
tar zcvf /bakup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/ #压缩目录备份
cd /backup/ #查看备份
模拟故障：
mkdir /bak #创建目录模拟故障
mv /usr/local/mysql/data/ /bak #移动数据文件目录
cd /usr/local/mysql/ #查看模拟故障是否成功
ll
恢复：
mkdir /restore #创建恢复存放目录
tar zxvf /bakup/mysql_all-2020-09-13.tar.gz -C /restore/ #将之前压缩备份的目录，解压到指定的新建恢复存放目录
mv /restore/usr/local/mysql/data/ /usr/local/mysql/ #将解压的数据文件目录data，移动到mysql下
systemctl start mysqld #启动服务
systemctl status mysqld #查看数据库开启正常
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since Sun 2020-09-13 06:03:42 EDT; 3h 56min ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 9819 ExecStart=/usr/local/mysql/bin/mysqld --daemonize --pid-file=/usr/local/mysql/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Process: 9801 ExecStartPre=/usr/local/mysql/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 9822 (mysqld)
2.mysqldump备份和恢复
mysqldump 可以将指定的库，表，或全部的库导出为sql脚本；
备份的几种操作：
- mysqldump备份需要和mysql进行数据交互，如果关闭mysql 则无法备份和恢复
- mysqldump -u 用户 -p [选项] 库名 [表名1] [表名2] … > /备份路径/备份文件名
mysqldump -u 用户 -p [选项] –databases 库名1 [库名2] … > /备份路径/备份文件名 - mysqldump -u 用户 -p [选项] –all-databases > /备份路径/备份文件名
示例：
mysqldump -u root -p mysql user >mysql-user.sql #备份数据路mysql中的user表 ，文件保存在当前操作目录下面
Enter password: ###输入密码abc123
mysqldump -u root -p –databases auth >auth.sql #备份auth数据库 文件保存在当前操作目录下面
Enter password: ####输入密码abc123
操作：
准备环境：
创建数据库—创建表—表内增加点数据
1.create database wow;
2.use wow;
3.create talbe lol(name CHAR(16) NOT NULL,password CHAR(48)
DEFAULT'',primary key (name));
4.mysql> insert into lol(name,password)
-> values
-> ('xiaohong',password('123456'));
5.mysql> insert into lol values ('lisi',password('123456'));
Query OK, 1 row affected, 1 warning (0.01 sec)
对数据库的表备份：
mysqldump -u root -p wow lol >lol.sql #对数据库中的lol表做备份
Enter password: #输入密码
恢复：
mysql -u root -p uou < lol.sql #uou是之前存在的空库，将备份表lol导入到uou库中；
Enter password:
mysql -u root -p -e 'show tables from uou' #查看数据表lol是否导入成功
Enter password:
+---------------+
| Tables_in_uou |
+---------------+
| lol |
| user |
+---------------+
对数据库做备份：
mysqldump -u root -p --databases wow >wow.sql
Enter password:
模拟数据库故障：
[root@localhost ~]# mysql -u root -p -e 'drop database wow' #将库wow删除，模拟故障（高危操作哦！！！！）
Enter password:
[root@localhost ~]# mysql -u root -p -e 'show databases' #查看下是否被删除
Enter password:
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| uou |
+--------------------+
恢复：
[root@localhost ~]# mysql -u root -p < wow.sql #将库wow导入数据库中
Enter password:
[root@localhost ~]# mysql -u root -p -e 'show databases' #查看下数据库中的库
Enter password:
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| uou |
| wow |
+--------------------+
五，增量备份和恢复
1、增量备份的特点
与完全备份完全不同，增量备份没有重复数据，备份量不大，时间短；但其恢复麻烦，需要上次完成完全备份及备份之后的所有增量备份才能恢复。
2、MySQL数据库二进制日志对备份的意义
二进制日志保存了所有更新或者可能更新数据的操作。二进制日志在启动mysql服务器后开始记录，并在文件达到二进制日志所设置的最大值
或者接受到flush logs命令后重新创建新的日志文件，生成二进制的文件序列，并及时把这些日志文件保存到安全的存储位置，即可完成一个时间段
的增量备份
方法:
-
一般恢复： （一般丢什么数据，找什么数据）
mysqlbinlog [–no-defaults] 增量备份文件 | mysql -u 用户名 -p -
基于位置恢复：
1、恢复数据到指定位置
mysqlbinlog –stop-position=’操作 id’ 二进制日志 |mysql -u 用户名 -p 密码
2、从指定的位置开始恢复数据
mysqlbinlog –start-position=’操作 id’ 二进制日志 |mysql -u 用户名 -p 密码
- 基于时间恢复：
1、从日志开头截止到某个时间点的恢复
mysqlbinlog [–no-defaults] –stop-datetime=’年-月-日 小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p 密码
2、从某个时间点到日志结尾的恢复
mysqlbinlog [–no-defaults] –start-datetime=’年-月-日 小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p 密码
3、从某个时间点到某个时间点的恢复
mysqlbinlog [–no-defaults] –start-datetime=’年-月-日 小时:分钟:秒’ –stop-datetime=’年-月-日小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p 密码
一般恢复：
准备环境：
生成二进制文件--常见库--表
1.vi /etc/my.cnf 在[mysqld]配置内，添加
log-bin=/usr/local/mysql/data/mysql-bin
2.systemctl restart mysqld #重启服务
3.ll /usr/local/mysql/data #查看时候有二进制文件日志生成
....省略部分...
-rw-r-----. 1 mysql mysql 154 Sep 13 11:50 mysql-bin.000001
-rw-r-----. 1 mysql mysql 39 Sep 13 11:50 mysql-bin.index
....省略部分...
在这里插入代码片创建测试环境：
mysql> create database cl;
Query OK, 1 row affected (0.01 sec)
mysql> use cl;
Database changed
create table cls(工号 char(20) not null,姓名 char(10) not null,工资 char(6) not null,部门 char(5));
Query OK, 0 rows affected (0.01 sec)
insert into cls values ('001','熏无空','18888',1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into cls values ('002','沙屋静','16666',2);
Query OK, 1 row affected (0.00 sec)
mysql> insert into cls values ('003','竹巴结','14444',3);
Query OK, 1 row affected (0.01 sec)
mysql> select * from cls;
+--------+-----------+--------+--------+
| 工号 | 姓名 | 工资 | 部门 |
+--------+-----------+--------+--------+
| 001 | 熏无空 | 18888 | 1 |
| 002 | 沙屋静 | 16666 | 2 |
| 003 | 竹巴结 | 14444 | 3 |
+--------+-----------+--------+--------+
3 rows in set (0.00 sec)
mysql> show master logs; #查看是否生成二进制日志文件，记录着操作
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 1802 |
+------------------+-----------+
1 row in set (0.00 sec)
进行一次完全备份：
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
mysqldump -u root -p cl cls >/mysql-bak/cls-$(date +%F).sql
Enter password:
ll /mysql-bak/
total 4
-rw-r--r--. 1 root root 1952 Sep 13 12:22 cls-2020-09-13.sql
mysqladmin -u root -p flush-logs
Enter password:
ll /usr/local/mysql/data/
...省略部分...
-rw-r-----. 1 mysql mysql 1849 Sep 13 12:24 mysql-bin.000001
-rw-r-----. 1 mysql mysql 154 Sep 13 12:24 mysql-bin.000002
-rw-r-----. 1 mysql mysql 78 Sep 13 12:24 mysql-bin.index
...省略部分...
录入新的内容并进行一次增量备份：
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql> use cl;
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
mysql> insert into cls values ('004','滕森','10000',4);
Query OK, 1 row affected (0.00 sec)
mysql> insert into cls values ('005','金娇','10000',5);
Query OK, 1 row affected (0.00 sec)
mysql> select * from cls;
+--------+-----------+--------+--------+
| 工号 | 姓名 | 工资 | 部门 |
+--------+-----------+--------+--------+
| 001 | 熏无空 | 18888 | 1 |
| 002 | 沙屋静 | 16666 | 2 |
| 003 | 竹巴结 | 14444 | 3 |
| 004 | 滕森 | 10000 | 4 |
| 005 | 金娇 | 10000 | 5 |
+--------+-----------+--------+--------+
5 rows in set (0.00 sec)
mysqladmin -u root -p flush-logs; #再次查看二进制日志文件，看操作是否被记录生成文件
Enter password:
ll /usr/local/mysql/data/
...省略部分...
-rw-r-----. 1 mysql mysql 1849 Sep 13 12:24 mysql-bin.000001
-rw-r-----. 1 mysql mysql 753 Sep 13 12:30 mysql-bin.000002
-rw-r-----. 1 mysql mysql 154 Sep 13 12:30 mysql-bin.000003
-rw-r-----. 1 mysql mysql 117 Sep 13 12:30 mysql-bin.index
...省略部分...
cp /usr/local/mysql/data/mysql-bin.000002 /mysql-bak/ #将二进制日志文件复制到咱们创建的备份目录下
模拟失误操作删除cls表；
mysql -u root -p -e 'drop table cl.cls'
Enter password:
mysql -u root -p -e 'select * from cl.cls'
Enter password:
ERROR 1146 (42S02) at line 1: Table 'cl.cls' doesn't exist 恢复操作：（完全备份恢复） mysql -u root -p cl < /mysql-bak/cls-2020-09-13.sql Enter password: [root@localhost ~]# mysql -u root -p -e 'select * from cl.cls' Enter password: +--------+-----------+--------+--------+ | 工号 | 姓名 | 工资 | 部门 | +--------+-----------+--------+--------+ | 001 | 熏无空 | 18888 | 1 | | 002 | 沙屋静 | 16666 | 2 | | 003 | 竹巴结 | 14444 | 3 | +--------+-----------+--------+--------+ 增量备份恢复： mysqlbinlog --no-defaults /mysql-bak/mysql-bin.000002 |mysql -u root -p Enter password: mysql -u root -p -e 'select * from cl.cls'
Enter password:
+--------+-----------+--------+--------+
| 工号 | 姓名 | 工资 | 部门 |
+--------+-----------+--------+--------+
| 001 | 熏无空 | 18888 | 1 |
| 002 | 沙屋静 | 16666 | 2 |
| 003 | 竹巴结 | 14444 | 3 |
| 004 | 滕森 | 10000 | 4 |
| 005 | 金娇 | 10000 | 5 |
+--------+-----------+--------+--------+
基于位置恢复：
模拟删除表cls：
删除表cls---恢复下完全备份cl.2020.9.13.sql--查看二进制日志文件内想要恢复的位置
mysqlbinlog --no-defaults --base64-output=decode-rows -v /mysql_bak/mysql-bin.000002 ####--base64-output=decode-rows 64解码 ### -v 换行显示
.........省略部分...........
#at 289
#200913 12:27:59 server id 1 end_log_pos 344 CRC32 0x25b12913 Table_map: `cl`.`cls` mapped to number 225
#at 344
#200913 12:27:59 server id 1 end_log_pos 399 CRC32 0xbd609658 Write_rows: table id 225 flags: STMT_END_F
### INSERT INTO `cl`.`cls`
### SET
### @1='004'
### @2='滕森'
### @3='10000'
### @4='4'
# at 399
#200913 12:27:59 server id 1 end_log_pos 430 CRC32 0x3561ca6a Xid = 74
COMMIT/*!*/;
# at 565
#200913 12:28:26 server id 1 end_log_pos 620 CRC32 0x5e09ca65 Table_map: `cl`.`cls` mapped to number 225
# at 620
#200913 12:28:26 server id 1 end_log_pos 675 CRC32 0x9a65af81 Write_rows: table id 225 flags: STMT_END_F
### INSERT INTO `cl`.`cls`
### SET
### @1='005'
### @2='金娇'
### @3='10000'
### @4='5'
# at 675
#200913 12:28:26 server id 1 end_log_pos 706 CRC32 0x510d9e90 Xid = 75
COMMIT/*!*/;
.........省略部分...........
咱们只想恢复 滕森的数据！操作如下：
从文件可以得知，at289- at565 这位置范围是滕森
mysqlbinlog --no-defaults --stop-position='430' /mysql-bak/mysql-bin.000002 |mysql -u root -p #位置在操作节点附近即可
Enter password:
[root@localhost ~]# mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@localhost ~]# mysql -u root -p -e 'select * from cl.cls'
Enter password:
+--------+-----------+--------+--------+
| 工号 | 姓名 | 工资 | 部门 |
+--------+-----------+--------+--------+
| 001 | 熏无空 | 18888 | 1 |
| 002 | 沙屋静 | 16666 | 2 |
| 003 | 竹巴结 | 14444 | 3 |
| 004 | 滕森 | 10000 | 4 |
+--------+-----------+--------+--------+
另一种操作方法：start 跟上面差不多操作
注意： 基于位置恢复的操作，节点不要选择太靠近操作的节点，否则容易恢复失败。
基于时间戳恢复
比如咱们通过时间戳恢复金娇的数据
20200913 12:28:26 # 日志文件中金娇所在的时间戳
mysqlbinlog --no-defaults --start-datetime='2020-09-13 12:28:26' /mysql-bak/mysql-bin.000002 |mysql -u root -p
Enter password:
[root@localhost ~]# mysql -u root -p -e 'select * from cl.cls'
Enter password:
+--------+-----------+--------+--------+
| 工号 | 姓名 | 工资 | 部门 |
+--------+-----------+--------+--------+
| 001 | 熏无空 | 18888 | 1 |
| 002 | 沙屋静 | 16666 | 2 |
| 003 | 竹巴结 | 14444 | 3 |
| 004 | 滕森 | 10000 | 4 |
| 005 | 金娇 | 10000 | 5 |
+--------+-----------+--------+--------+
本文地址：https://blog.csdn.net/weixin_47320286/article/details/108562220