不小心把mysql root 给删除了

转载

bingfeng 2024-09-27 14:39:19

文章标签 不小心把mysql root 给删除了 big data mysql 数据库 sql 文章分类 MySQL 数据库

数据库运维

介绍

开启定时数据库备份
开启binlog日志
误删恢复
建议
binglog日志
mysqlbinlog命令

开启数据库日常备份

1、创建备份目录

mkdir -p /home/magic/mysql/sqlbackup
cd /home/magic/mysql/sqlbackup

2、编写 backMysql.sh

#!/bin/sh

CURRENT_DIR=$(
   cd "$(dirname "$0")"
   pwd
)

#///
#安装之前需要配置如下信息

# 数据库
# mysql 账号
username="root"
# mysql 密码
password="hancloud1234!"
# 备份数据库 空格分割
databases=(mcp_manager ApolloConfigDB ApolloPortalDB cattle)

#删除当前日期-七天前的备份
find ${backPath} -mtime +7 -name "*.sql.gz" -exec rm -Rf {} \;
echo "删除$oldDate的备份成功"

#创建备份根目录
backPath="/home/magic/mysql/sqlbackup"


#///
#创建根目录
mkdir -p $backPath
echo "开始备份数据库";
nowDate=$(date +%Y%m%d_%H%M%S)

#压缩
for database in ${databases[*]}
do
	mysqldump -u${username} -p${password} ${database} | gzip > $backPath/${nowDate}_${database}.sql.gz;
    if [ $? -eq 0 ]; then
       echo "${database} 备份成功"
    fi
done

echo "备份数据完成";

授权

chmod +x /home/magic/mysql/sqlbackup/backMysql.sh
sed -i 's/\r$//' /home/magic/mysql/sqlbackup/backMysql.sh

3、创建定时任务，使用Crontab命令

3.1 创建定时任务文件

每天晚上2点执行脚本，，Crontab使用

echo "0 2 * * * /bin/sh /home/magic/mysql/sqlbackup/backMysql.sh" >> /home/magic/mysql/sqlbackup/backsql.crontab

3.2 开启定时任务

开启定时任务

crontab /home/magic/mysql/sqlbackup/backsql.crontab

查看定时任务

crontab -l

开启binlog日志

1、查看binlog日志状态

登录后查看

show variables like '%log_bin%';

不小心把mysql root 给删除了_big data

需要开启

2、开启binlog日志

mkdir -p /home/magic/mysql/3306/binlog
chown -R mysql:mysql /home/magic/mysql/3306/binlog
 
 vi /etc/my.cnf
 # 增加
server-id = 1
log_bin = /home/magic/mysql/3306/binlog/binlog
expire_logs_days = 15
binlog_format = ROW

不小心把mysql root 给删除了_mysql_02

3、重启mysql

service mysqld restart

4、登录后查看

mysql -uroot -phancloud1234!
show variables like '%log_bin%';

不小心把mysql root 给删除了_big data_03

误删后恢复

例子（误删）

有一张sys_user

1、期望删除

SELECT * FROM sys_user where create_time < '2021-01-01 00:00:00' and department_id = 3

不小心把mysql root 给删除了_不小心把mysql root 给删除了_04

2、实际删除

需要删除的数据已经确定，通常情况下把 sql 中的select *替换为delete去执行，出错的机率会小一点。

但是，并没有去改原来的 sql，而是重新写了一个删除语句并且执行。

delete FROM sys_user where create_time < '2021-01-01 00:00:00'

问题就这样出现了，在新写的删除语句中缺少了department_id = 3的条件

重新查表后发现误删了数据。

3、数据找回

cd /home/magic/mysql/3306/binlog

不小心把mysql root 给删除了_mysql_05

经过确定，删库的时间大概是**「17:50」**，那我们就以这个时间点为参考，找前后 5 分钟的日志。

mysqlbinlog -v --start-datetime='2021-09-22 17:45:00' --stop-datetime='2021-09-22 17:55:00' binlog.000001 | grep sys_user

不小心把mysql root 给删除了_mysql_06

从图中可以看到，这个时间点的日志确实包含我们删除数据的日志。

接下来我们就需要把这些日志整理一下，然后想办法恢复到数据库就可以了。

首先，把我们需要的日志单独保存到 tmp.log 文件中，方便下载到本地。

mysqlbinlog --no-defaults --start-datetime='2021-09-22 17:45:00' --stop-datetime='2021-09-22 17:55:00' --base64-output=decode-rows -vv binlog.000001  > tmp.sql

把 tmp.sql 下载到本地，用文本编辑工具打开看一下，可以看到一堆伪 sql。

不小心把mysql root 给删除了_数据库_07

在上图的伪 sql 中 @1 表示第一个字段 @2 表示第二个字段其他的以此类推

日志中包含的 sql 是一些伪 sql，并不能直接在数据库执行，我们需要想办法把这些伪 sql 处理成可在数据库执行的真正的 sql。

我们使用命令替换

我有19个字段，所以下面命令中的是@19

cat tmp.sql | sed 's/.*TIMESTAMP.*/&)/g'| sed 's/.*TIMESTAMP.*/FROM_UNIXTIME(&/g'| sed -n '/###/p' | sed 's/### //g;s/\/\*.*\//,/g;s/,)/),/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;' |sed -r 's/(@19.*),/\1;/g' | sed 's/@[1-9][0-9]=//g' | sed 's/@[1-9]=//g' >binlogMysql.sql

最终处理好的 sql 就像是这样：

不小心把mysql root 给删除了_不小心把mysql root 给删除了_08

4、数据恢复

导入该sql即可

cat binlogMysql.sql | mysql -uroot -phancloud1234! -v test

建议

「1、研发人员不能直连生产库」

生产库一般由 DBA 或者运维来维护，研发人员很少有需要登录生产数据库查看数据的需求，就算数据真的有问题，一般情况下 DBA 或运维人员也能解决。

如果一个系统需要研发人员频繁的登录数据库去维护数据，这时就该考虑在系统中增加一个管理功能来使用，而不是频繁登录数据库。

所以，研发就不应该具有生产库的登录权限。如果偶尔的需要登录生产库查看数据，可以找 DBA 开一个临时账号。

「2、登录生产库使用只读账号」

大部分人使用数据库都会使用连接工具，比如 Navicat、SQLyog 等

每个人的电脑上，大概率也只有一个连接工具。开发库、测试库、生产库都在同一个连接工具中打开，有时只是想在开发库中修改一条数据，却不小心修改了生产库。

而 MySql 的事务是自动提交的，在连接工具中，正在修改的当前行失去光标后就会自动提交事务，极其容易操作失误。

所以，如果确实的需要登录生产库，尽量使用具有只读权限的账号登录。

「3、关闭 autocomit、多人复核」

如果确实需要在生产库进行数据的增加、修改或删除，在执行 sql 之前最好先关闭事务的自动提交。

在需要登录生产库修改数据的情况下，想必问题也比较复杂，一条 sql 语句应该是完成不了，可能需要写 N 多个 sql 才能完成数据的修改。

这么多的 sql，很有可能在执行的时候会选错。有时你只是想执行一个 select 语句，结果发现执行的是 delete。

更坑爹的是，大部分的数据库连接工具有执行当前选中内容的功能。有时候你只想执行当前选中的内容，结果发现执行的是全部内容。

如果关闭了自动提交，就算出现上面的情况，也还有机会挽回。比如下面这样：

-- 关闭事务自动提交set @@autocommit=0;-- 查看需要删除的数据，共65600条select * from t_user where age>18 and deptid=100;-- 删除delete from t_user where age>18;-- 发现有问题，回滚select * from t_user where age>18 and deptid=100;rollback ;-- 确认没问题，提交-- commit;

另外，在commit之前需要至少再找一个同事进行确认。所谓当局者迷，自己有时可能处于一个错误的思路上，就想当然的认为结果没问题，这时就需要一个旁观者来指点迷津。

两个人都确认没问题之后再提交，出错的机率也会小很多。

「4、修改数据之前先备份」

备份、备份、备份，重要的事情说三遍。

备份虽然会麻烦一点，但它是保证数据准确性最有效的手段。

况且，掌握一些技巧后，备份也不是很麻烦的事情。

比如，我们删除数据之前可以先这样备份。

-- 创建一个和原表一样的备份表（包含索引）create table t_user_bak like t_user;-- 拷贝数据到备份表INSERT into t_user_bak select * from t_user;-- 确认数据拷贝完成select * from t_user_bak;

这样备份的数据，就算原表数据误删了，甚至都不用恢复数据，只需要把备份表的名字改成原表的名字直接使用就可以了。

在生产库修改数据之前，一定要记得备份，一旦数据修改出错，这是成本最低并且最有效的恢复途径。

「5、设置数据库定期备份」

生产环境，运维人员一定要设置数据库定期备份。研发人员也有义务提醒运维同事编写自动备份脚本，因为生产库一旦出现问题需要恢复数据，没有定期备份的话，麻烦的不只是运维人员，研发人员也要跟着麻烦。

备份周期可以根据业务需要来决定。如果业务对数据要求的实时性比较高，备份周期相对短一点，恢复数据时可以最大程度的避免数据丢失；反之，备份周期可以长一点，节省磁盘空间。

如果有必要，可以定期把备份文件拷贝到异地服务器，避免由于一些不可抗力因素导致的当前服务器磁盘损坏，如地震、台风等。

binglog 日志

binlog 即 Binary Log，它是二进制文件，用来记录数据库写操作的日志。

数据库的 insert、delete、update、create、alter、drop 等写入操作都会被 binlog 记录。

因此，数据库的主从数据同步通常也是基于 binlog 完成的，本文只对 binlog 做一些简单介绍，后期会单独写一篇文章讲基于 binlog 的主从数据同步。

binlog 日志需要配置开启，可以通过脚本查看 binlog 是否开启。

SHOW VARIABLES LIKE 'LOG_BIN%';

不小心把mysql root 给删除了_mysql_09

如果log_bin参数显示的是OFF说明 binlog 是关闭状态，需要手动开启。

开启 binlog 需要修改数据库的my.cnf配置文件，my.cnf文件通常在服务器的/etc目录下。

打开/etc/my.cnf文件，配置 binlog 的相关参数，下文配置 binlog 的常用参数。

# 启用binlog并设置binlog日志的存储目录log_bin = /var/lib/mysql/bin-log# 设置binlog索引存储目录log_bin_index = /var/lib/mysql/mysql-bin.index# 30天之前的日志自动删除expire_logs_days = 30# 设置binlog日志模式，共有3种模式：STATMENT、ROW、MIXED binlog_format = row

binlog 的日志有三种格式，分别是 STATEMENT、ROW、MIXED。在 mysql5.7.7 版本之前默认使用的是 STATEMENT，之后的版本默认使用的是 ROW。

ROW 格式

ROW 格式下，binlog 记录的是每一条数据被修改的详细细节。

比如，执行 delete 语句，删除的数据有多少条，binlog 中就记录有多少条伪 sql。

delete from t_user where age>18;

不小心把mysql root 给删除了_不小心把mysql root 给删除了_10

那么 row 格式的日志的缺点就很明显，在发生批量操作时，日志文件中会记录大量的伪 sql，占用较多的磁盘空间。

尤其是当进行 alter 操作时，每条数据都发生变化，日志文件中就会有每一条的数据的日志。此时，如果表中的数据量很大的话，日志文件也会非常大。

在 mysql5.6 版本之后，针对 ROW 格式的日志，新增了binlog_row_image参数。

当binlog_row_image设置为minimal时，日志中只会记录发生改变的列，而不是全部的列，这在一定程度上能减少 binlog 日志的大小。

虽然记录每行数据的变化会造成日志文件过大，但这也是它的优点所在。

因为它记录了每条数据修改细节，所以在一些极端情况下也不会出现数据错乱的问题。在做数据恢复或主从同步时能很好的保证数据的真实性和一致性

STATEMENT 格式

不小心把mysql root 给删除了_big data_11

日志中的 sql 是直接可以拿到数据库运行的。

STATEMENT 格式的日志的优缺点和 ROW 格式的正好相反，它记录的是 sql 语句和执行语句时的上下文环境，而不是每一条数据。所以它的日志文件会比 ROW 格式的日志文件小一些。

由于记录的只是 sql 语句和上下文的环境，STATEMENT 格式的日志在进行主从数据同步时会有一些不可预估的情况出现，导致数据错乱。比如 sleep()、last_insert_id() 等函数会出现问题。

MIXED 格式

MIXED 格式是 STATEMENT 和 ROW 的结合，mysql 会根据具体执行的 sql 语句，来选择合适的日志格式进行记录。

MIXED 格式下，在执行普通的 sql 语句时会选 STATEMENT 来记录日志，在遇到复杂的语句或函数操作时会选择 ROW 来记录日志。

mysqlbinlog 命令

mysql 数据库的 binlog 文件是二进制的，基本看不懂，使用数据库自带的mysqlbinlog命令可以把二进制文件转换成能看懂的十进制文件。

由于数据库的 binlog 文件可能会很大，查看起来会很麻烦，所以mysqlbinlog命令也提供了一些参数可以用来筛选日志。

「mysqlbinlog 语法」

mysqlbinlog [options] log-files

options：可选参数 log-files：文件名称

「options 的常用值」

-d: 根据数据库的名称筛选日志 -o：跳过前N行日志 -r, --result-fil: 把日志输出到指定文件 --start-datetime: 读取指定时间之后的日志，时间格式：yyyy-MM-dd HH:mm:ss --stop-datetime: 读取指定时间之前的日志，时间格式：yyyy-MM-dd HH:mm:ss --start-position: 从指定位置开始读取日志 --stop-position: 读取到指定位置停止 --base64-output：在 row 格式下，显示伪 sql 语句 -v, --verbose：显示伪 sql 语句，-vv 可以为 sql 语句添加备注

「常用写法」
查看 fusion 数据库的日志

mysqlbinlog -d=fusion bin-log.000001

查看某个时间段内的日志

mysqlbinlog  --start-datetime='2021-06-09 19:30:00' --stop-datetime='2021-06-09 19:50:00' bin-log.000001

恢复数据，事件的开始位置是 4300，结束位置是 10345

mysqlbinlog --start-position 4300 --stop-position 10345 bin-log.000001 | mysql -uroot -p123456 fusion

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：python 矩阵标准化归一化

下一篇：如何撤销mysql的操作

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯