linux的mysql数据库备份和恢复与日志还原

最新推荐文章于 2024-04-22 09:12:15 发布

2301_78602537

最新推荐文章于 2024-04-22 09:12:15 发布

阅读量1.2k

点赞数 16

文章标签：数据库 linux mysql

本文链接：https://blog.csdn.net/2301_78602537/article/details/134871968

版权

一、完整备份

打包数据库文件备份

源码包的位置/usr/local/mysql/data/
安装包的位置/var/lib/mysql
在数据库中创建数据表，并写入数据，停止数据库服务，创建备份目录
按照特定格式对数据库目录进行备份
1、把/var/lib/mysql/下的文件进行打包

tar  czf  mysql_all-$(date  +%F).tar.gz  /var/lib/mysql/*

先关闭数据库，在备份数据。

systemctl stop mysqld.service 
tar -czf mysql_all-$(date +%F).tar.gz /var/lib/mysql/*

2、把/var/lib/mysql/*下的文件删除

rm -rf /var/lib/mysql/*

3、解压刚打包的mysql_all.tar.gz包到/var/lib/mysql/目录下

tar xvf mysql_all-2023-12-08.tar.gz -C /var/lib/mysql/

4、mysql重启一下mysql登录进入mysql

systemctl restart mysqld.service
systemctl status mysqld.service
#显示正常：
Active: active (running) since Fri 2023-12-08 10:12:14 CST; 9s ago
mysql -uroot -p123456@#

二、备份工具备份

工具 mysqldump
对单个库进行完全备份
mysqldump -u用户名 -p[密码] [选项] --databases [数据库名] > /备份路径/备份文件名

备份数据库：完全备份powerjob-daily到powerjob-daily目录下：

#备份数据库
mysqldump -uroot -p123456@# --databases powerjob-daily > /var/lib/mysql/backup/powerjob-daily-backup.sql
#还原
mysql -uroot -p123456@# < /var/lib/mysql/backup/powerjob-daily-backup.sql

查看备份文件：

[root@204db backup]# pwd
/var/lib/mysql/backup
[root@204db backup]# ls
powerjob-daily-backup.sql

进入数据库，把原来的库删除在恢复测试一下

#进入数据库
 [root@204db ~]# mysql -uroot -p123456@#
 #查看数据库
 mysql> show databases;
 #删除数据库
 mysql> drop databases powerjob-daily;
 #退出登录
 exit;
 #还原恢复数据库
 [root@204db backup]# mysql -uroot -p123456@# < /var/lib/mysql/backup/powerjob-daily-backup.sql
 #然后在进去查看
 [root@204db ~]# mysql -uroot -p123456@#
  #查看数据库
 mysql> show databases;

三、对库表进行完全备份

对多个库进行完全备份
mysqldump -u用户名 -p[密码] [选项] --databases 库名1 [库名2]…… > /备份路径/备份文件名

mysqldump -uroot -p123456@# --databases sys test-db > /var/lib/mysql/backup/sys-backup.sql test-db.sql

对所有库进行完全备份
mysqldump -u用户名 -p[密码] [选项] –opt --all-databases > /备份路径/备份文件名

#进行mysql所有库备份：
mysqldump -uroot -p123456@# --opt --all-databases > /var/lib/mysql/backup/dataAll.sql

对表进行完全备份
mysqldump -u用户名 -p[密码] [选项] 数据库名表名 > /备份路径/备份文件名
对 dataease数据库的表sys_log进行备份

mysqldump -uroot -p123456@#  dataease sys_log > /var/lib/mysql/backup/sys_log.sql

对表的结构进行备份
mysqldump -u用户名 -p[密码] -d 数据库名表名 > /备份路径/备份文件名

#对 dataease数据库的表sys_log进行备份 
mysqldump -uroot -p123456@# -d dataease sys_log > /var/lib/mysql/backup/sys_log.sql

说明：
这个命令是用来导出 MySQL 数据库中的指定表的数据结构（仅包含表结构，不包含数据）到指定路径下的备份文件中。
-u [用户名]：指定数据库的用户名。
-p[密码]：指定数据库的密码。请注意在-p后面没有空格。
-d：表示只导出数据表的结构，不包含数据。
[数据库名]：指定要备份的数据库的名称。
[表名]：指定要备份的表的名称。
> /备份路径/备份文件名：表示将备份的结果输出到指定路径和文件名中。

还原工具有两种方式
1、source 登录mysql 数据库执行source 备份sql脚本路径

 [root@204db ~]# mysql -uroot -p123456@#
 mysql> use dataease;
 mysql> show tables;
# 恢复还原表语句
 source  -uroot -p123456@# < /var/lib/mysql/backup/sys_log.sql

2、mysql -u用户名 -p[密码] < 库备份脚本的路径
mysql -u用户名 -p[密码] 库名 < 表备份脚本的路径

# 恢复还原库语句
mysql -uroot -p123456@# < /var/lib/mysql/backup/powerjob-daily-backup.sql
[root@204db ~]# mysql -uroot -p123456@#
 mysql> use dataease;
 mysql> show tables;

四、增量备份

使用完全备份时，备份与恢复的时间过长
增量备份就是备份自上一次备份之后增加的或改变的文件内容
特点没有重复数据，备份量不大，时间短
恢复麻烦，需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且要对所有增量备份进行逐个反推恢复
MySQL没有提供直接的增量备份办法，可以通过MySQL提供的二进制日志（binary logsbinlog））间接实现增量备份

MySQL二进制日志对备份的意义：二进制日志保存了所有更新或者可能更新数据库的操作。

二进制日志在启动MySQL服务器后开始记录，并在文件达到max_binlog_size所设置的大小或者接收到flush logs命令后重新创建新的日志文件。

开启二进制日志备份功能
MySQL的配置文件的[mysqld]项中加入 log-bin=文件存放路径/文件前缀，如 log-bin=mysql-bin，然后重启mysqld服务。默认此配置存在。

vi /etc/my.cnf
systemctl restart mysqld.service
ll /var/lib/mysql

在这里插入图片描述
生成了一个二进制日志mysql-bin.000001

mysql回滚了一次生成另一个记录

mysqladmin -uroot -p flush-logs
 ll /var/lib/mysql

使用mysqld --log-bin=文件存放路径/文件前缀重新启动mysqld服务每周选择服务器负载较轻的时间段，或者用户访问较少的时间段进行备份

增量恢复
应用场景:
人为的SQL语句破坏了数据库
在进行下一次全备之前发生系统故障导致数据库丢失
在主从架构中，主库数据发生了故障，保证从库数据一致性

一般恢复:
备份的二进制日志内容全部恢复
格式： mysqldbinlog [–no-defaults] 增量备份文件 | mysql -u用户名 -p密码

基于时间点的恢复:
便于跳过某个发生错误的时间点实现数据恢复
格式：从日志开头截止到某个时间点的恢复：
mysqlbinlog [–no-defaults] --stop-datetime=’年-月-日小时：分钟：秒‘ 二进制日志 | mysql -u用户名 -p密码

从某个时间点到日志结尾的恢复:
mysqlbinlog [–no-defaults] --start-datetime=’年-月-日小时：分钟：秒‘ 二进制日志 | mysql -u用户名 -p密码

从某个时间点到某个时间点的恢复:
mysqlbinlog [–no-defaults] --start-datetime=’年-月-日小时：分钟：秒‘ --stop-datetime=’年-月-日小时：分钟：秒‘ 二进制日志 | mysql -u用户名 -p密码

基于位置的恢复:
可能在同一时间点既有错误的操作也有正确的操作，基于位置进行恢复更加准确
mysqlbinlog --stop-position=‘操作 id‘ 二进制日志 | mysql -u用户名 -p密码
mysqlbinlog --start-position=‘操作 id‘ 二进制日志 | mysql -u用户名 -p密码

演示：

[root@204db ~]# mysql -uroot -p123456@#
mysql> create database client;
mysql> use client;
mysql> create table user_info(身份证 char(20) not null,姓名 char(20) not null,性别 char(4),用户ID号 char(10) not null,资费 int(10)) DEFAULT CHARSET=utf8;
mysql> insert into user_info values('000000006','张三','男','016','10');
mysql> insert into user_info values('000000007','李四','女','017','91');
mysql> insert into user_info values('000000008','王五','女','018','23');
mysql> select * from user_info;
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 性别   | 用户ID号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 000000006 | 张三   | 男     | 016         |     10 |
| 000000007 | 李四   | 女     | 017         |     91 |
| 000000008 | 王五   | 女     | 018         |     23 |
+-----------+--------+--------+-------------+--------+

备份：

#备份表：
[root@204db backup]# mysqldump -uroot -p123456@# client user_info > /var/lib/mysql/backup/client_userinfo-$(date +%F).sql
#备份数据库：
[root@204db backup]# mysqldump -uroot -p123456@# --databases client > /var/lib/mysql/backup/client-$(date +%F).sql

备份文件如下：

[root@204db backup]# pwd
/var/lib/mysql/backup
[root@204db backup]# ls
1  client  client-2023-12-08.sql  client_userinfo-2023-12-08.sql

/etc/my.cnf文件添加两条内容，重启一下服务

 vi /etc/my.cnf
# 配置文件的[mysqld]项中加入
server-id=1
log-bin=mysql-bin
#重启服务
systemctl restart mysqld.service

**进行增量备份：**进行一次日志回滚（生成新的二进制日志）

mysqladmin -uroot -p123456@# flush-logs

查看/var/lib/mysql文件目录：新增日志如下
在这里插入图片描述
进行增量备份：多出文件

mysqladmin -uroot -p123456@# flush-logs
ll /var/lib/mysql

在这里插入图片描述

继续录入新的数据

 [root@204db]# mysql -uroot -p123456@#
  mysql> use client;
  mysql> insert into user_info values('000000009','赵六','男','019','37');
  mysql> insert into user_info values('000000010','孙七','男','020','36');
  mysql> select * from user_info;
 mysql>  select * from user_info;
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 性别   | 用户ID号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 000000006 | 张三   | 男     | 016         |     10 |
| 000000007 | 李四   | 女     | 017         |     91 |
| 000000008 | 王五   | 女     | 018         |     23 |
| 000000009 | 赵六   | 男     | 019         |     37 |
| 000000010 | 孙七   | 男     | 020         |     36 |
+-----------+--------+--------+-------------+--------+

删除数据库：模拟误操作删除user_info表

drop table client.user_info; 
select * from client.user_info;

mysql> drop table client.user_info;
Query OK, 0 rows affected (0.04 sec)
mysql> select * from client.user_info;
#提示不存在
ERROR 1146 (42S02): Table 'client.user_info' doesn't exist

恢复完全备份

[root@204db backup]# mysql -uroot -p123456@# client < /var/lib/mysql/backup/client_userinfo-2023-12-08.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

查询看看恢复回来了

 [root@204db]# mysql -uroot -p123456@#
  mysql> use client;
mysql> select * from client.user_info;
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 性别   | 用户ID号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 000000006 | 张三   | 男     | 016         |     10 |
| 000000007 | 李四   | 女     | 017         |     91 |
| 000000008 | 王五   | 女     | 018         |     23 |
+-----------+--------+--------+-------------+--------+
3 rows in set (0.00 sec)

恢复增量备份

[root@204db mysql]# mysqlbinlog --no-defaults /var/lib/mysql/mysql-bin.000003 | mysql -uroot -p123456@#
mysql: [Warning] Using a password on the command line interface can be insecure.

查询看看恢复回来了

 [root@204db]# mysql -uroot -p123456@#
  mysql> use client;
mysql> select * from client.user_info;
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 性别   | 用户ID号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 000000006 | 张三   | 男     | 016         |     10 |
| 000000007 | 李四   | 女     | 017         |     91 |
| 000000008 | 王五   | 女     | 018         |     23 |
| 000000009 | 赵六   | 男     | 019         |     37 |
| 000000010 | 孙七   | 男     | 020         |     36 |
+-----------+--------+--------+-------------+--------+

参考文章：https://blog.csdn.net/l187567/article/details/131964069

2301_78602537

关注

16
点赞
踩
17

收藏

觉得还不错? 一键收藏
0
评论
linux的mysql数据库备份和恢复与日志还原

mysqlbinlog [–no-defaults] --start-datetime=’年-月-日小时：分钟：秒‘ --stop-datetime=’年-月-日小时：分钟：秒‘ 二进制日志 | mysql -u用户名 -p密码。mysqlbinlog [–no-defaults] --stop-datetime=’年-月-日小时：分钟：秒‘ 二进制日志 | mysql -u用户名 -p密码。mysqldump -u用户名 -p[密码] [选项] --databases 库名1 [库名2]……
复制链接

扫一扫