数据库备份与恢复

update_now1

已于 2023-09-20 20:24:38 修改

阅读量51

点赞数

文章标签：数据库

于 2023-09-15 16:24:03 首次发布

本文链接：https://blog.csdn.net/m0_57740456/article/details/132900142

版权

本文详细介绍了MySQL数据库的各种备份方法，包括完全备份、增量备份、差异备份，以及使用物理备份、逻辑备份、binlog日志恢复数据，以及PerconaXtraBackup工具（innobackupex）的应用。

摘要由CSDN通过智能技术生成

备份策略

-完全备份：备份所有数据

-增量备份：备份上次备份后，所有新产生的数据

-差异备份：备份完全备份后，所有新产生的数据

一、完全备份与恢复

1、物理备份

适用于线下服务器，需要关闭服务

//物理备份
[root@host51 ~]# cp -r /var/lib/mysql  ~/data/mysql.bak
[root@host51 ~]# systemctl stop mysqld
[root@host51 ~]# mv /var/lib/mysql /var/lib/mysql-old
//恢复
[root@host51 ~]# cp -r ~/data/mysql.bak /var/lib/mysql
[root@host51 ~]# chown -R mysql:mysql /var/lib/mysql
[root@host51 ~]# systemctl start mysqld

2、逻辑备份

不需要关服务但是会锁表

备份
[root@host51 ~]# mysqldump -uroot -p123qqq...A db2 t2 >data/db2t2.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@host51 ~]# mysql -uroot -p123qqq...A -e"delete from db2.t2";
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@host51 ~]# mysql -uroot -p123qqq...A -e"select count(*) from db2.t2";
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------+
| count(*) |
+----------+
|        0 |
+----------+
//恢复
[root@host51 ~]# mysql -uroot -p123qqq...A db2 < ~/data/db2t2.sql

二、binlog日志

1、启用binlog日志

[root@host51 ~]# vim /etc/my.cnf
[mysqld]
server_id=51        //唯一
log_bin=master51    //默认路径为/var/lib/mysql下

查看日志

[root@host51 ~]# mysql -uroot -p123qqq...A
//查看日志信息
mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| master51.000002 |     1852 |              |                  |                   |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
//刷新日志
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| master51.000003 |      154 |              |                  |                   |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
//查看日志文件
mysql> system ls /var/lib/mysql
auto.cnf    client-cert.pem  db2             ib_logfile0  master51.000001  master51.index  mysql.sock.lock     public_key.pem   sys
ca-key.pem  client-key.pem   ib_buffer_pool  ib_logfile1  master51.000002  mysql           performance_schema  server-cert.pem
ca.pem      db1              ibdata1         ibtmp1       master51.000003  mysql.sock      private_key.pem     server-key.pem

删除日志

mysql> purge master logs to "master51.000002";
Query OK, 0 rows affected (0.02 sec)

mysql> system ls /var/lib/mysql
auto.cnf    client-cert.pem  db2             ib_logfile0  master51.000002  mysql            performance_schema  server-cert.pem
ca-key.pem  client-key.pem   ib_buffer_pool  ib_logfile1  master51.000003  mysql.sock       private_key.pem     server-key.pem
ca.pem      db1              ibdata1         ibtmp1       master51.index   mysql.sock.lock  public_key.pem      sys

mysql> system cat /var/lib/mysql/master51.index
./master51.000002
./master51.000003

2、启用binlog日志恢复数据

binlog日志格式：报表模式statement，行模式row，混合模式mixed

修改日志格式

//查看日志格式
mysql> show variables like "binlog_format";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.01 sec)

//修改日志格式
[root@host51 ~]# vim /etc/my.cnf
binlog_format="mixed"
//查看日志信息
mysql> show master status\G
*************************** 1. row ***************************
             File: master51.000004
         Position: 154
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

恢复记录

mysql> show binlog events in "master51.000004";
+-----------------+------+----------------+-----------+-------------+----------------------------------------------+
| Log_name        | Pos  | Event_type     | Server_id | End_log_pos | Info                                         |
+-----------------+------+----------------+-----------+-------------+----------------------------------------------+
| master51.000004 |    4 | Format_desc    |        51 |         123 | Server ver: 5.7.17-log, Binlog ver: 4        |
| master51.000004 |  123 | Previous_gtids |        51 |         154 |                                              |
| master51.000004 |  154 | Anonymous_Gtid |        51 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'         |
| master51.000004 |  219 | Query          |        51 |         323 | create table  db2.t3(name char(10))          |
| master51.000004 |  323 | Anonymous_Gtid |        51 |         388 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'         |
| master51.000004 |  388 | Query          |        51 |         465 | BEGIN                                        |
| master51.000004 |  465 | Query          |        51 |         569 | use `db2`; insert into db2.t3 values("tom")  |
| master51.000004 |  569 | Xid            |        51 |         600 | COMMIT /* xid=14 */                          |
| master51.000004 |  600 | Anonymous_Gtid |        51 |         665 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'         |
| master51.000004 |  665 | Query          |        51 |         742 | BEGIN                                        |
| master51.000004 |  742 | Query          |        51 |         847 | use `db2`; insert into db2.t3 values("lucy") |
| master51.000004 |  847 | Xid            |        51 |         878 | COMMIT /* xid=15 */                          |
| master51.000004 |  878 | Anonymous_Gtid |        51 |         943 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'         |
| master51.000004 |  943 | Query          |        51 |        1020 | BEGIN                                        |
| master51.000004 | 1020 | Query          |        51 |        1110 | use `db2`; delete from db2.t3                |
| master51.000004 | 1110 | Xid            |        51 |        1141 | COMMIT /* xid=18 */                          |
+-----------------+------+----------------+-----------+-------------+----------------------------------------------+

恢复数据

[root@host51 ~]# mysqlbinlog --start-position=465  --stop-position=943 /var/lib/mysql/master51.000004 | mysql -uroot -p123qqq...A
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@host51 ~]# mysql -uroot -p123qqq...A -e"select * from db2.t3";
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+
| name |
+------+
| tom  |
| lucy |
+------+

三、innobackupex备份工具

1、安装innobackupex

[root@host51 ~]# rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm    //安装依赖
[root@host51 ~]# yum -y install percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm

2、备份所有数据到/allbak下

[root@host51 ~]# innobackupex -uroot -p123qqq...A /allbak --no-timestamp
//查看备份文件
[root@host51 ~]# ls /allbak/
backup-my.cnf  db2             ibdata1  performance_schema  xtrabackup_binlog_info  xtrabackup_info
db1            ib_buffer_pool  mysql    sys                 xtrabackup_checkpoints  xtrabackup_logfile
[root@host51 ~]# cat /allbak/xtrabackup_checkpoints
backup_type = full-backuped        //备份类型
from_lsn = 0
to_lsn = 2603676
last_lsn = 2603685
compact = 0
recover_binlog_info = 0

3、恢复完全备份数据

[root@host51 ~]# systemctl stop mysqld
[root@host51 ~]# rm -rf /var/lib/mysql/*
//准备恢复数据
[root@host51 ~]# innobackupex --apply-log /allbak/
//查看
[root@host51 ~]# cat /allbak/xtrabackup_checkpoints
backup_type = full-prepared
from_lsn = 0
to_lsn = 2603676
last_lsn = 2603685
compact = 0
recover_binlog_info = 0
//拷贝数据
[root@host51 ~]# innobackupex --copy-back /allbak
[root@host51 ~]# chown -R  mysql:mysql /var/lib/mysql
[root@host51 ~]# systemctl restart mysqld

4、恢复某一张表

mysql> delete from db2.t3;    //删除标记录
mysql> alter table db2.t3 discard tablespace;       //删除表空间
//导出表信息
[root@host51 ~]# ls /allbak/db2/t3.
t3.frm  t3.ibd
[root@host51 ~]# innobackupex --apply-log --export /allbak       
[root@host51 ~]# ls /allbak/db2/t3.
t3.cfg  t3.exp  t3.frm  t3.ibd
//.ibd为表记录文件  .frm为表头文件


[root@host51 ~]# cp /allbak/db2/t3.{cfg,exp,ibd} /var/lib/mysql/db2/
[root@host51 ~]# chown -R mysql:mysql /var/lib/mysql/db2/t3.*
[root@host51 ~]# mysql -uroot -p123qqq...A
mysql> select count(*) from db2.t3;
ERROR 1814 (HY000): Tablespace has been discarded for table 't3'
//导入表信息
mysql> alter table db2.t3 import tablespace;

//删除表信息文件
[root@host51 ~]# ls /var/lib/mysql/db2/t3.
t3.cfg  t3.exp  t3.frm  t3.ibd
[root@host51 ~]# rm -rf /var/lib/mysql/db2/t3.cfg
[root@host51 ~]# rm -rf /var/lib/mysql/db2/t3.exp
[root@host51 ~]# ls /var/lib/mysql/db2/t3.
t3.frm  t3.ibd

5、增量备份与恢复

//增量备份存储目录
[root@host51 ~]# innobackupex -uroot -p123qqq...A --incremental /new1dir --incremental-basedir=/allbak --no-timestamp
//查看备份信息
[root@host51 ~]# cat /new1dir/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 2603676
to_lsn = 2611266
last_lsn = 2611275
compact = 0
recover_binlog_info = 0

//准备恢复数据
[root@host51 ~]# systemctl stop mysqld
[root@host51 ~]# rm -rf /var/lib/mysql/*
[root@host51 ~]# innobackupex --apply-log --redo-only /allbak
[root@host51 ~]# cat /allbak/xtrabackup_checkpoints
backup_type = log-applied
from_lsn = 0
to_lsn = 2603676
last_lsn = 2603685
compact = 0
recover_binlog_info = 0

//合并数据
[root@host51 ~]# innobackupex --apply-log --redo-only /allbak  --incremental-dir=/new1dir
[root@host51 ~]# cat /allbak/xtrabackup_checkpoints
backup_type = log-applied
from_lsn = 0
to_lsn = 2611266
last_lsn = 2611275
compact = 0
recover_binlog_info = 0

恢复数据

[root@host51 ~]# innobackupex --copy-back /allbak/
root@host51 ~]# chown -R mysql:mysql /var/lib/mysql
[root@host51 ~]# ls /var/lib/mysql
db1  ib_buffer_pool  ib_logfile0  ibtmp1  performance_schema  xtrabackup_binlog_pos_innodb
db2  ibdata1         ib_logfile1  mysql   sys                 xtrabackup_info
[root@host51 ~]# systemctl start mysqld