常用数据库备份策略

  1. mysqldump工具备份（数据量小）
    mysqldump由于是mysql自带的备份工具，所以也是最常用的mysql数据库的备份工具。支持基于InnoDB的热备份。但由于是逻辑备份，所以速度不是很快，适合备份数据量比较小的场景。

    mysqldump完全备份+二进制日志 —>实现时间点恢复

  2. 基于LVM快照备份（冷备份）

    在物理备份中 ，有基于文件系统的物理备份（LVM的快照），也可以直接用tar之类的命令打包。但这些只能进行冷备份

    不同的存储引擎能备份的级别也不一样，MyISAM能备份到表级别，而InnoDB不开启每表一文件的话就只能备份整个数据库。

  3. 使用percona提供的xtrabackup（推荐）

    支持InnoDB的物理热备份，支持完全备份，增量备份，而且速度非常快，而且支持InnoDB引擎的数据在不同数据库迁移。
    优点如下：

    1. 无需停止数据库进行InnoDB热备份，在50G以上的数据量备份时候，应该是首选工具。

    2. 支持增量备份MySQL并通过流传输到其他的服务器上。

    3. 备份MySQL的时候不会增加服务器的负载。

在这里我们不演示如何备份的工作，而只针对于如何将数据恢复到数据库中，并且关闭密码校验。

恢复数据库

这里以xtrabackup备份后的数据为例。将从一台全新的ubuntu服务器上完成数据库的恢复

首先我们会拥有一个backup.tar.gz的一个压缩包。他实际上就是我们数据库的data文件夹。因此我们只需要将这个压缩包解压到对应的data目录下，然后通过修改密码。重新启动这个数据库即可完成数据的再次访问。

第一步:解压备份文件

首先还是先把我们的数据库文件解压出来
我们这里选择在/root/back/102目录下解压

1
tar -zxvf backup.tar.gz -C /root/back/102

表示会把backup.tar.gz解压到/root/back/102目录下

然后我们创建一个data目录,用来把解压后的文件移动到这个目录下.

1
2
3
cd /root/back/102 #移动到指定目录下
mkdir data # 创建目录
mv /root/back/102/tmp/backup/2024-03-23/full/* /root/back/102/data # 移动文件到指定目录下

数据库备份及恢复-2024-05-25-10-53-36
此时我们可以看到在你对应的目录下,有一个data文件夹,这个文件夹下面就是数据库的所有文件.

第二步：安装mysql服务

  1. 安装docker
    这里我们使用docker来安装mysql服务，这样子可以简化很多环境问题。

    1
    2
    curl -fsSL https://test.docker.com -o test-docker.sh
    sudo sh test-docker.sh

  2. 通过docker拉取mysql镜像

    1
    docker pull mysql:8.0.19

  3. 启动mysql服务
    这里要考虑两个情况,一个是我们正常的使用.一个是跳过过校验过程进入安全模式实现对密码的修改.

    1
    2
    3
    4
    5
    6
    7
    8
    9

    docker run -p 3306:3306 --name 102 \
    -v /root/back/102/mysql-files:/var/lib/mysql-files \
    -v /root/back/102/conf:/etc/mysql \
    -v /root/back/102/logs:/var/log/mysql \
    -v /root/back/102/data:/var/lib/mysql \
    -e MYSQL_ROOT_PASSWORD=yourpassword \
    -d mysql:8.0.19 \
    --skip-grant-tables # 跳过密码校验.带上这个参数表示进入安全模式

    这里他会把你的数据库文件挂载到/var/lib/mysql目录下.

第三步：修改密码

这里我们需要修改密码并允许远程连接

1
2
docker exec -it 102 bash # 进入docker服务内部
mysql -u root -p # 进入mysql(此时是安全模式,不会有密码校验)

进入mysql后我们查看数据后.需要修改密码.

1
2
3
4
5
6
# 进入mysql数据库
use mysql; 
# 查看用户信息
select host, user, authentication_string, plugin from user; 
# 修改密码
update user set authentication_string='' where user='root';

按ctrl+d退出mysql,再按一次退出docker
然后我们退出docker服务,重新启动mysql(不进入安全模式)

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
docker rm -f 102 # 删除容器

# 重新启动,并且不适用安全模式
 docker run -p 3306:3306 --name 102 \
    -v /root/back/102/mysql-files:/var/lib/mysql-files \
    -v /root/back/102/conf:/etc/mysql \
    -v /root/back/102/logs:/var/log/mysql \
    -v /root/back/102/data:/var/lib/mysql \
    -e MYSQL_ROOT_PASSWORD=yourpassword \
    -d mysql:8.0.19 

# 进入docker容器
docker exec -it 102 bash
# 进入mysql
mysql -u root 
# 可能需要的步骤(有时候,如果被提示root用户没有权限就执行这个命令)
# grant system_user on *.* to 'root'@'localhost';

# 添加root@%用户
CREATE USER 'root'@'%' IDENTIFIED BY '';

# 为root设置允许所有权限
GRANT ALL ON *.* TO 'root'@'%';

# 设置加密方式
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'yourpassword';
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'yourpassword';

# 刷新权限
flush privileges;

至此我们就完成了数据库的恢复工作.
如果是在云服务器,我们就需要放开3306端口,本地就直接连接即可.
这样子我们就可以通过navicat等工具连接到数据库了.

补充

在经过长达3-4个月的实际使用中。经常会经历包括服务器奔溃，数据库奔溃的情况。曾经我不会解决，每次都要花个10到20分钟。从0开始重新部署一遍数据库。总会显得有些麻烦。现在有一个解决方案：
mysql8.0参考文档

在这个文档中，解释到了一些关于强制重启的参数。而这正是我所刚需的。我并不需要对数据库进行写服务，只需要把他设置为readonly模式。这样子，就算服务器因为内存太小奔溃了。它也可以重新自启动。

解决措施：在my.cnf中设置如下参数

1
2
[mysqld]
innodb_force_recovery = 1

1 （SRV_FORCE_IGNORE_CORRUPT）

即使检测到损坏的页面， 也让服务器运行 。尝试 跳过损坏的索引记录和页面，这有助于转储表。 SELECT * FROM tbl_name

2 （SRV_FORCE_NO_BACKGROUND）

阻止主线程和任何清除线程运行。如果在清除操作期间发生意外退出，此恢复值将阻止它。

3 （SRV_FORCE_NO_TRX_UNDO）

崩溃恢复后 不运行事务 回滚。

4 （SRV_FORCE_NO_IBUF_MERGE）

防止插入缓冲区合并操作。如果这些操作会导致崩溃，则不执行这些操作。不计算表统计 信息。此值可能会永久损坏数据文件。使用此值后，请准备删除并重新创建所有二级索引。设置 InnoDB为只读。

5 （SRV_FORCE_NO_UNDO_LOG_SCAN）

启动数据库时 不查看撤消日志InnoDB：即使未完成的事务也视为已提交。此值可能会永久损坏数据文件。设置InnoDB为只读。

6 （SRV_FORCE_NO_LOG_REDO）

不执行与恢复相关的重做日志 前滚。此值可能会永久损坏数据文件。使数据库页面处于过时状态，这反过来可能会导致 B 树和其他数据库结构进一步损坏。设置 InnoDB为只读。

一般设置为4以上时，就可以解决我的需要了。
以上