mysql数据库基本操作语句,模拟误删数据恢复

vim /etc/my.cnf.d/mariadb-server.cnf
server-id   = 1
log-bin=mysql-bin
binlog_format=mixed
expire_logs_days = 10

-- 查看binlog 状态
use mysql 
show variables like '%log_bin%';

-- 查看所有binlog文件列表
show master logs;

-- 查看最后一个binlog日志的编号名称及其最后一个操作事件pos结束点的值
show master status; 

-- 刷新日志，此刻开始产生一个新编号的binlog文件
Flush logs;
-- 每当mysqld服务重启时，会自动执行刷新binlog日志命令，mysqldump备份数据时加-F选项也会刷新binlog日志

-- 清空所有binlog
reset master;
-- 查看binlog 内容
-- 方法1
mysqlbinlog  mysql-bin.000001

-- 方法2
show binlog events in 'mysql-bin.000001';

-- 指定查询
-- 从mysql-bin.000003 的pos 点328 开始
show binlog events in 'mysql-bin.000003' from 328;

-- 从pos点154开始查询，中间跳过1行，查询2条数据
show binlog events in 'mysql-bin.000004' from 256 limit 1,2;

#数据备份
mysqldump  --single-transaction  -uroot -proot@123 -S /opt/app/mysql/mysql.sock taskmonitor | /bin/gzip >/tmp/mysql_taskmonitor_`date +%m%d`.gz

#下载
scp root@ip:/tmp/mysql_taskmonitor_0913.gz .

#导入
gzip -d mysql_taskmonitor_0913.gz
mysql -uroot -p123456 taskmonitor < mysql_taskmonitor_0913

SELECT CONCAT('drop table ',table_name,';') FROM information_schema.`TABLES` WHERE table_schema='wpc';

use mysql 
show variables like '%log_bin%';

vim /opt/scripts/backup_db_mysql.sh
#!/bin/bash
MYSQL="/usr/bin/mysql"
MYSQLDUMP="/usr/bin/mysqldump"
BACKUP_DIR="/data/db_backup"
DB_SOCKET="/var/lib/mysql/mysql.sock"
#DB_hostname="127.0.0.1"
DBNAME="taskmonitor"
DB_USER="root"
DB_PASS="123456"
TIME=`date +%Y%m%d%H%M%S`
LOCK_FILE="${BACKUP_DIR}/lock_file.tmp"
BKUP_LOG="/data/db_backup/${TIME}_bkup.log"
DEL_BAK=`date -d '10 days ago' '+%Y%m%d'`
#mkdir db_backup
[ ! -d $BACKUP_DIR ] && mkdir -p  $BACKUP_DIR
##To judge lock_file
if [[ -f $LOCK_FILE ]];then
exit 255
else
echo $$ > $LOCK_FILE
fi

##dump databases##
echo ${TIME} >> ${BKUP_LOG}
echo "=======Start Bakup============" >>${BKUP_LOG}
${MYSQLDUMP} -S ${DB_SOCKET} -u${DB_USER} -p${DB_PASS}  --databases  ${DBNAME} | gzip -9 > ${BACKUP_DIR}/${TIME}.${DBNAME}.gz
echo "=======Finished Bakup============" >>${BKUP_LOG}
/bin/rm -f ${LOCK_FILE}
##del back 10 days before##
/bin/rm -f ${BACKUP_DIR}/${DEL_BAK}*.gz

10 4 * * 0   /bin/bash  /opt/scripts/backup_db_taskmonitor.sh >/dev/null 2>&1

/bin/bash  /opt/scripts/backup_db_taskmonitor.sh

drop database taskmonitor;

flush logs;

mkdir dbbak  && cp /data/mysql/mysql-bin.*  dbbak/

mysqlbinlog -v --base64-output=DECODE-ROWS  mysql-bin.000002 |grep -C 10  -i "drop database"

grep -i "change master"  /data/db_backup/2021-09-29-15-29-13.taskmonitor

mysql -uroot -p123456 < /data/db_backup/2021-09-29-15-29-13.taskmonitor

mysqlbinlog --start-position=371 --stop-position=604 mysql-bin.000002  |mysql -uroot -p123456