devbox
小舞很执着
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

MySQL备份与恢复

作者：小舞很执着 | 2024-08-02 08:02:48

赞

踩

MySQL备份与恢复

MySQL备份与恢复

一、MySQL 数据库备份概述

1、数据库备份的重要性

  • 在生产环境中，数据的安全性至关重要
  • 任何数据的丢失都可能产生严重的后果
  • 造成数据丢失的原因
    • 程序错误
    • 人为操作错误
    • 运算错误
    • 磁盘故障
    • 灾难（如火灾、地震）和盗窃

2、数据库备份类型

  • 从物理与逻辑的角度，备份可分为
    • 物理备份：对数据库操作系统的物理文件（如数据文件、日志文件等）的备份
      • 物理备份方法
        • 冷备份（脱机备份）：是在关闭数据库的时候进行的，啥也不行
        • 热备份（联机备份）：数据库处于运行状态，依赖于数据库的日志文件，能读能写
        • 温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作，只能读
      • 逻辑备份：对数据库逻辑组件（如：表等数据库对象）的备份
  • 从数据库的备份策略角度，备份可分为
    • 完全备份：每次对数据库进行完整的备份
    • 差异备份：备份自从上次完全备份之后被修改过的文件
    • 增量备份：只有在上次完全备份或者增重备份后被修改的文件才会被备份

3、常见的备份方法

  • 物理冷备
    • 备份时数据库处于关闭状态，直接打包数据库文件
    • 备份速度快，恢复时也是最简单的
  • 专用备份工具 mydump 或 mysqlhoctopy
    • mydump 常用的逻辑备份工具
    • mysqlhoctopy 仅拥有备份 MyISAM 和 ARCHIVE 表
  • 启用二进制日志进行增量备份
    • 进行增量备份，需要刷新二进制日志
  • 第三方工具备份
    • 免费的MySQL热备份软件Percoona XtraBackup

二、数据库完全备份操作

1、物理冷备份与恢复

1.备份数据库

mysql> create database aa;
Query OK, 1 row affected (0.00 sec)

mysql> create database bb;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aa                 |
| bb                 |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql> exit
Bye

[root@bogon mysql]# cat /etc/my.cnf
[client]
socket=/usr/local/mysql/data/mysql.sock
[mysqld]
socket=/usr/local/mysql/data/mysql.sock
#绑定监听地址 0.0.0.0
bind-address = 0.0.0.0
# 跳过域名解析，如果mysql服务器设置了dns服务器，并且客户端ip在dns上并没有相应的hostname，那么这个过程很慢，导致连接等待
skip-name-resolve
#设置 3306 端口
port = 3306
# 设置 mysql 的安装目录
basedir=/usr/local/mysql
# 设置 mysql 数据库的数据的存放目录
datadir=/usr/local/mysql/data
# 允许最大连接数
max_connections=2048
# 服务端使用的字符集默认为 utf8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 表名存储在磁盘是小写的，但是比较的时候是不区分大小写
lower_case_table_names=1
# 接收数据包的大小
max_allowed_packet=16M

[root@bogon ~]# grep datadir /etc/my.cnf
datadir=/usr/local/mysql/data
[root@bogon ~]# systemctl stop mysqld
[root@bogon ~]# mkdir /backup
[root@bogon ~]# cd /usr/local/mysql/
[root@bogon mysql]# ls
bin  data  docs  include  lib  LICENSE  man  README  share  support-files
[root@bogon mysql]# tar zcf /backup/mysql-all-$(date +%F).tar.gz data/
[root@bogon mysql]# ls /backup/
mysql-all-2024-07-29.tar.gz  mysql-all.tar-2024-07-29.gz
[root@bogon mysql]# systemctl start mysqld
[root@bogon mysql]# scp /backup/mysql-all-2024-07-29.tar.gz root@192.168.72.145:/root/
root@192.168.72.145's password: 
mysql-all-2024-07-29.tar.gz                                                100% 1319KB  82.8MB/s   00:00    

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62

2.恢复数据库

[root@bogon ~]# ls
anaconda-ks.cfg  auto-install-mysql57-glibc  auto-install-mysql57-glibc.tar.gz  mysql-all-2024-07-29.tar.gz
[root@bogon ~]# systemctl stop mysqld
[root@bogon ~]# ls /usr/local/mysql/
bin  data  docs  include  lib  LICENSE  man  README  share  support-files
[root@bogon ~]# mv /usr/local/mysql/data/ /usr/local/mysql/data.old
[root@bogon ~]# tar zxf mysql-all-2024-07-29.tar.gz 
[root@bogon ~]# ls
anaconda-ks.cfg             auto-install-mysql57-glibc.tar.gz  mysql-all-2024-07-29.tar.gz
auto-install-mysql57-glibc  data
[root@bogon ~]# mv data/ /usr/local/mysql/
[root@bogon ~]# systemctl start mysqld

# 测试是否备份恢复成功
[root@bogon ~]# mysql -uroot -p123

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aa                 |
| bb                 |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql> exit
Bye

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31

2、mysqldump 备份与恢复

1.备份数据库

# 备份指定库中的部分表
# mysqldump [选项] 库名 [表名 1] [表名 2] … > /备份路径/备份文件名
mysqldump -uroot -p mysql user > mysql_user.sql


# 备份一个或多个完整的库（包括其中所有的表）
# mysqldump [选项] --databases 库名 1 [库名 2] … > /备份路径/备份文件名
mysqldump -uroot -p --databases mysql > mysql.sql


# 备份 MySQL 服务器中所有的库
# mysqldump [选项] --all-databases > /备份路径/备份文件名
# -A 是 --all-databases的简写
mysqldump -uroot -p --all-databases > all-data.sql
mysqldump -uroot -p -A > all-data.sql

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

2.恢复数据库

# 删除数据库 aa 和 bb
mysql> drop database aa;
Query OK, 0 rows affected (0.00 sec)
mysql> drop database bb;
Query OK, 0 rows affected (0.00 sec)
# 查看数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)


# mysql [选项] [库名] [表名] < /备份路径/备份文件名
# 恢复所有数据库
[root@bogon ~]# mysql -uroot -p < all-data.sql 
Enter password: 
# 查看数据库
[root@bogon ~]# mysql -uroot -p -e 'show databases;'
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aa                 |
| bb                 |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35

三、MySQL 增量备份与恢复

1、MySQL 增量备份概述

1.增量备份的特点

2.MySQL 二进制日志对备份的意义

# 开启二进制日志功能
[root@bogon ~]# vi /etc/my.cnf
[mysqld]
log-bin=mysql-bin
binlog_format = MIXED
server-id=1
# 重启服务
[root@bogon ~]# systemctl restart mysqld
# 有 mysql-bin.000001 就代表成功
[root@bogon ~]# ls /usr/local/mysql/data/mysql-bin.*
/usr/local/mysql/data/mysql-bin.000001  /usr/local/mysql/data/mysql-bin.index

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

2、MySQL 增量恢复

  • 常用的增量恢复的方法有三种：一般恢复、基于位置的恢复、基于时间点的恢复。
  • 一般恢复：将所有备份的二进制日志内容全部恢复，命令格式如下所示。
mysqlbinlog [--no-defaults] 增量备份文件 | mysql -u 用户名 -p 密码

  • 1
  • 基于位置的恢复：数据库管理员在操作数据库时可能在同一时间点既有错误的操作也有 正确的操作，通过基于位置进行恢复可以更加精准，命令格式如下所示。
# 格式 1：恢复数据到指定位置。
mysqlbinlog --stop-position='操作 id' 二进制日志 |mysql -u 用户名 -p 密码
# 格式 2：从指定的位置开始恢复数据。
mysqlbinlog --start-position='操作 id' 二进制日志 |mysql -u 用户名 -p 密码

  • 1
  • 2
  • 3
  • 4
  • 基于时间点的恢复：跳过某个发生错误的时间点实现数据恢复，而基于时间点的恢复可 以分成三种情况。
# 格式 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='年-月-日小时:分钟:秒' 二进制日志 

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

3、MySQL 企业备份案例

1.一般恢复

  • 添加数据库、表，录入信息
[root@bogon ~]# mysql -uroot -p123
CREATE DATABASE client;
USE client;

CREATE TABLE user_info(
IdentityCard CHAR(20) NOT NULL,
name CHAR(20) NOT NULL, 
gender CHAR(4),
UserID CHAR(10) NOT NULL,
expenses INT(10));

INSERT INTO user_info VALUES('000006','张三','男','016','10');
INSERT INTO user_info VALUES('000007','李四','女','017','91');
INSERT INTO user_info VALUES('000008','王五','女','018','23');

SELECT * FROM user_info;

mysql> exit
Bye

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 先进行一次完全备份
[root@bogon ~]# mkdir /mysql_bak
[root@bogon ~]# mysqldump -uroot -p -A > /mysql_bak/mysql-all.sql
Enter password: 
# 刷新日志
[root@bogon ~]# mysqladmin -uroot -p flush-logs
[root@bogon ~]# ls /usr/local/mysql/data/mysql-bin.*
/usr/local/mysql/data/mysql-bin.000001  /usr/local/mysql/data/mysql-bin.index
/usr/local/mysql/data/mysql-bin.000002

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 继续录入新的数据并进行增量备份
USE client;

INSERT INTO user_info VALUES('000009','赵六','男','019','37');
INSERT INTO user_info VALUES('000010','孙七','男','020','36');
# 停顿几秒之后再插入
INSERT INTO user_info VALUES('000011','张三','男','021','35');
INSERT INTO user_info VALUES('000012','李四','男','022','37');

# 刷新日志
[root@bogon ~]# mysqladmin -uroot -p flush-logs
Enter password: 
[root@bogon ~]# ls /usr/local/mysql/data/mysql-bin.*
/usr/local/mysql/data/mysql-bin.000001  /usr/local/mysql/data/mysql-bin.000003
/usr/local/mysql/data/mysql-bin.000002  /usr/local/mysql/data/mysql-bin.index

# 备份
[root@bogon ~]# cp /usr/local/mysql/data/mysql-bin.000002 /mysql_bak/
[root@bogon ~]# ls /mysql_bak/
client_userinfo-2024-07-29.sql  mysql-all.sql  mysql-bin.000002

[root@bogon ~]# ls /mysql_bak/
client_userinfo-2024-07-29.sql  mysql-all.sql  mysql-bin.000002
# 查看
[root@bogon ~]# mysqlbinlog --no-defaults /mysql_bak/mysql-bin.000002

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 模拟误操作
[root@bogon ~]# mysql -uroot -p -e 'DROP database client;'
Enter password: 
[root@bogon ~]# mysql -uroot -p -e 'show databases;'
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 恢复操作
[root@bogon ~]# mysql -uroot -p < /mysql_bak/mysql-all.sql
Enter password: 
[root@bogon ~]# mysql -uroot -p -e 'show databases;'
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| client             |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13

2.基于位置恢复

# 把mysql-bin.000002的内容全部恢复
mysqlbinlog --no-defaults /mysql_bak/mysql-bin.000002 | mysql -uroot -p
--start-position
--stop-position

[root@bogon ~]# mysql -uroot -p -e 'select * from client.user_info;'
Enter password: 
+--------------+--------+--------+--------+----------+
| IdentityCard | name   | gender | UserID | expenses |
+--------------+--------+--------+--------+----------+
| 000006       | 张三   | 男     | 016    |       10 |
| 000007       | 李四   | 女     | 017    |       91 |
| 000008       | 王五   | 女     | 018    |       23 |
+--------------+--------+--------+--------+----------+
# 恢复 孙七 之前的内容 基于位置 623
[root@bogon ~]# mysqlbinlog --no-defaults --stop-position='623' /mysql_bak/mysql-bin.000002 | mysql -uroot -p
Enter password: 
[root@bogon ~]# mysql -uroot -p -e 'select * from client.user_info;'
Enter password: 
+--------------+--------+--------+--------+----------+
| IdentityCard | name   | gender | UserID | expenses |
+--------------+--------+--------+--------+----------+
| 000006       | 张三   | 男     | 016    |       10 |
| 000007       | 李四   | 女     | 017    |       91 |
| 000008       | 王五   | 女     | 018    |       23 |
| 000009       | 赵六   | 男     | 019    |       37 |
+--------------+--------+--------+--------+----------+
# 恢复 孙七 之后的内容 基于位置 623
[root@bogon ~]# mysqlbinlog --no-defaults --start-position='623' /mysql_bak/mysql-bin.000002 | mysql -uroot -p
Enter password: 
[root@bogon ~]# mysql -uroot -p -e 'select * from client.user_info;'
Enter password: 
+--------------+--------+--------+--------+----------+
| IdentityCard | name   | gender | UserID | expenses |
+--------------+--------+--------+--------+----------+
| 000006       | 张三   | 男     | 016    |       10 |
| 000007       | 李四   | 女     | 017    |       91 |
| 000008       | 王五   | 女     | 018    |       23 |
| 000009       | 赵六   | 男     | 019    |       37 |
| 000010       | 孙七   | 男     | 020    |       36 |
| 000011       | 张三   | 男     | 021    |       35 |
| 000012       | 李四   | 男     | 022    |       37 |
+--------------+--------+--------+--------+----------+

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43

3.基于时间点恢复

--start-datetime
--stop-datetime

mysqlbinlog --no-defaults --start-datetime='2024-07-28 22:13:33' /mysql_bak/mysql-bin.000002 | mysql -uroot -p
mysqlbinlog --no-defaults --stop-datetime='2024-07-28 22:13:33' /mysql_bak/mysql-bin.000002 | mysql -uroot -p

[root@bogon ~]# mysql -uroot -p -e 'select * from client.user_info;'
Enter password: 
+--------------+--------+--------+--------+----------+
| IdentityCard | name   | gender | UserID | expenses |
+--------------+--------+--------+--------+----------+
| 000006       | 张三   | 男     | 016    |       10 |
| 000007       | 李四   | 女     | 017    |       91 |
| 000008       | 王五   | 女     | 018    |       23 |
+--------------+--------+--------+--------+----------+
# 恢复 孙七 之前的内容 基于时间 2024-07-29 10:13:20
[root@bogon ~]# mysqlbinlog --no-defaults --stop-datetime='2024-07-29 10:13:20' /mysql_bak/mysql-bin.000002 | mysql -uroot -p
Enter password: 
[root@bogon ~]# mysql -uroot -p -e 'select * from client.user_info;'
Enter password: 
+--------------+--------+--------+--------+----------+
| IdentityCard | name   | gender | UserID | expenses |
+--------------+--------+--------+--------+----------+
| 000006       | 张三   | 男     | 016    |       10 |
| 000007       | 李四   | 女     | 017    |       91 |
| 000008       | 王五   | 女     | 018    |       23 |
| 000009       | 赵六   | 男     | 019    |       37 |
+--------------+--------+--------+--------+----------+
# 恢复 孙七 之后的内容 基于时间 2024-07-29 10:13:20
[root@bogon ~]# mysqlbinlog --no-defaults --start-datetime='2024-07-29 10:13:20' /mysql_bak/mysql-bin.000002 | mysql -uroot -p
Enter password: 
[root@bogon ~]# mysql -uroot -p -e 'select * from client.user_info;'
Enter password: 
+--------------+--------+--------+--------+----------+
| IdentityCard | name   | gender | UserID | expenses |
+--------------+--------+--------+--------+----------+
| 000006       | 张三   | 男     | 016    |       10 |
| 000007       | 李四   | 女     | 017    |       91 |
| 000008       | 王五   | 女     | 018    |       23 |
| 000009       | 赵六   | 男     | 019    |       37 |
| 000010       | 孙七   | 男     | 020    |       36 |
| 000011       | 张三   | 男     | 021    |       35 |
| 000012       | 李四   | 男     | 022    |       37 |
+--------------+--------+--------+--------+----------+

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44

在这里插入图片描述

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/小舞很执着/article/detail/917855
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号