7.MySQL备份与恢复

一、备份类型

1、根据备份产生的结果不同

• 逻辑备份
  将操作转换成sql语句保存到文件中
  文件xxxx.sql
  逻辑备份工具: mysqldump

• 物理备份
  直接拷贝数据目录下的所有文件
  物理备份工具: xtrabackup

2、根据备份的数据量大小

• 完全备份

• 增量备份
  和上一次完全备份比，备份变化的数据
  和上一次增量备份比，备份变化的数据

• 差异备份
  和上一次完全备份比，备份变化的数据

二、逻辑备份 mysqldump

1、mysqldump常用选项

• –all-databases
  备份所有数据库

• –database 数据库名称
  备份单个数据库

• –lock-all-tables
  锁表

• –master-data={1|2}
  在备份文件中会记录数据库当前正在使用的二进制日志、最后一个事件位置
  CHANGE MASTER TO

2、mysqldump备份案例 ， 完全 + 差异

2.1 准备存放备份数据的目录

[root@localhost ~]# df -hT | grep backup
/dev/sde                xfs        20G   33M   20G   1% /backup

2.2 进行周一的完全备份

[root@localhost ~]# mysqldump -uroot -pWWW.1.com --lock-all-tables --master-data=2 --all-databases > /backup/data_$(date +%Y_%m_%d_%H_%M_%S).sql

2.3 模拟数据变化操作，比如在论坛注册个账号

2.4 进行周二的差异备份

[root@localhost ~]# mysqlbinlog --start-position=9901 /mysql/log/master.000003 > /backup/data_$(date +%Y_%m_%d_%H_%M_%S).sql

关键点: 完全备份文件中找二进制日志的事件点

2.5 模拟数据变化操作，比如在论坛注册个账号

2.4 进行周三的差异备份

[root@localhost ~]# mysqlbinlog --start-position=9901 /mysql/log/master.000003 > /backup/data_$(date +%Y_%m_%d_%H_%M_%S).sql

2.5 模拟数据变化操作，比如在论坛注册个账号

3、模拟数据恢复

3.1 模拟周三操作完毕后，数据库故障

[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# rm -rf /mysql/data/*

3.2 恢复数据库正常运行

[root@localhost ~]# systemctl start mysqld 
[root@localhost ~]# ls /mysql/data/
auto.cnf    ca.pem           client-key.pem  ibdata1      ib_logfile1  mysql               private_key.pem  server-cert.pem  slow.log
ca-key.pem  client-cert.pem  ib_buffer_pool  ib_logfile0  ibtmp1       performance_schema  public_key.pem   server-

[root@localhost ~]# grep -i password /var/log/mysqld.log 

mysql> alter user 'root'@"localhost" identified by 'WWW.1.com';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

3.3 先恢复最近一次的完全备份

• 在进行数据恢复时，二进制日志会重新记录所有的写操作，为避免重复，建议临时关闭二进制日志功能

mysql> select @@sql_log_bin;
+---------------+
| @@sql_log_bin |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)

mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@sql_log_bin;
+---------------+
| @@sql_log_bin |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)

注意：修改仅在当前连接中生效!!!!!!!!!

恢复数据

mysql> source /backup/data_2024_05_12_15_16_47.sql; 

3.4 恢复最后一次的差异备份

mysql> source /backup/data_2024_05_12_15_27_50.sql; 

3.5 根据以前的旧的二进制日志，找回aa3用户

[root@localhost ~]# mysqlbinlog --start-position=57307 /mysql/log/master.000003  | mysql -uroot -pWWW.1.com 

根据二进制日志恢复未备份的数据