Sad story, when the online database was accidentally deleted

Related learning recommendations: mysql tutorial

Preface

Due to the recent school season, our company I also do business related to colleges and universities, so I am quite busy and don’t have much time to write articles. Once people are too busy, they are prone to making mistakes while busy. As for me, just a few days ago, no, a friend of mine almost performed a deletion of the database and ran away when he was operating an online database a few days ago.

Note in advance: He is really my friend, not me. It's really my friend, not me. It's really my friend, not me. However, for the convenience of narrative, "my friend" will be referred to as "I" below.

The thing is like this. When I compared some table structures, I found that the table structures of this library and other libraries were quite different, so I thought that this environment was not in use, so I directly compared these tables. An overwriting operation was performed, and these tables happened to be related to the student wallets. Then at around 10 o'clock in the evening, the company's front-line staff reported in the group why the money in the wallet became 0.0. As soon as I saw this I was so frightened that I peeed and wondered if I should run away.

Fortunately, I have seen some database recovery plans and I still know a little bit about them.

Data recovery

1. Turn on the binlog log

I use the binlog log to recover data. To use binlog, you must first ensure that the binlog log is turned on. You can use command to view.

show variables like 'log_%';复制代码

You can see that it is in the ON state, indicating that it is turned on. If it is in the OFF state, in my.cnf [mysqld]After adding the configuration, restart the mysql service to enable it.

# my.cnf文件
[mysqld]
log-bin=mysql-bin
server-id=1复制代码

2. Check the binlog log

Make sure that after opening the binlog day, you can check the binlog log status through the command.

# 查看binlog日志的目录show master status;复制代码

# 查看binlog日志内容show binlog events IN 'mysql-bin.000002';复制代码

• Log_name represents the binlog log name
• Pos represents the pos starting point
• Event_type represents the type of this operation
• Server_id represents the machine id, which is specified by the configuration in my.cnf
• End_log_pos represents the pos end point
• Info represents the specific statement

# 进入存储binlog日志的文件木了，可以通过这条命令查看详情
 mysqlbinlog --base64-output=decode-rows -v mysql-bin.000002;复制代码

# 将binlog日志转为txt导出
mysqlbinlog --base64-output=decode-rows -v mysql-bin.000002 > sql.txt复制代码

3. Restore data

If you want to restore data from binlog log, command line recovery is commonly used, or The method is to copy the binlog log, use a binlog reading tool to convert it into a .sql file, and then copy all the SQL that needs to be used and run it again. The main focus here is the command line recovery method.

Command line recovery is mainly done by checking the binlog log, confirming the starting point and end point to be restored, and then entering the command to restore. Or estimate the time when you made a mistake and recover the data within a period of time.

# 通过起始点和结束点恢复    
mysqlbinlog --start-position="582" --stop-position="9414" mysql-bin.000002 | mysql -uroot -proot;复制代码

# 通过起始时间和结束时间来恢复，传入的时间可以是一个yyyy-MM-dd HH:mm:ss 的时间格式，也可以是一个时间戳
mysqlbinlog --start-datetime="2020-9-1 8:25:04" --stop-datetime="2020-9-1 20:00:00" mysql-bin.000002 | mysql -uroot -proot复制代码

##As you can see, after entering the recovery command, I cleared Database data is back.

4. Problem

Although the binlog log can recover data, there are also problems. When the binlog log continues to increase, it will be automatically deleted, which will result in the deleted part of the log. The data cannot be recovered.

However, the size and storage time of the binlog log can be set in mysql. However, it is still recommended to make daily backups to ensure nothing goes wrong.

# 设置文件大小,单位是字节,下面换算是100Mset global max_binlog_size=104857600;

# 设置文件保存天数,下面是保存7天，默认值为0,表示"没有自动删除"set global expire_logs_days = 7;复制代码

每日备份

对于线上环境来说，做好每日备份和binlog一起用才是王道。线上一般都是部署在linux上的，所以这里就简单列一下linux的定时备份方法。

1.检查是否安装定时任务,安装crontab

yum install crontabs复制代码

2.设置定时任务

/var/spool/cron/root  此文件为crontab定时任务,可通过crontab -e或者直接修改此文件修改.

crontab -l查看定时任务.

3.创建一个shell脚本

touch xxx.sh

mysqldump -uroot -p"密码" 数据库名 > /mysql/person_`date +%Y%m%d`.sql复制代码

4.修改文件权限

chmod 777 xxx.sh

5.打开定时任务文件

crontab -e

每天凌晨2点执行
00 2 * * * /xxx.sh复制代码

完结！

想了解更多编程学习，敬请关注php培训栏目！

The above is the detailed content of Sad story, when the online database was accidentally deleted. For more information, please follow other related articles on the PHP Chinese website!