mysql的日志管理及备份恢复

最新推荐文章于 2022-12-05 00:45:15 发布

qq_52825616

最新推荐文章于 2022-12-05 00:45:15 发布

阅读量155

点赞数

文章标签：数据库 dba database

本文链接：https://blog.csdn.net/qq_52825616/article/details/125036353

版权

一、mysql的日志

MySQL的默认日志保存位置为/usr/local/mysql/data

1、日志的分类

（1）错误日志

（2）通用查询日志

（3）二进制日志

（4）慢查询日志

2、mysql的日志查询

（1）查询通用日志是否开启

show variables like 'general%';

（2）查询二进制日志是否开启

show variables like 'log_bin%';

（3）查看慢查询功能是否开启

show variables like '%slow%';

（4）查看慢查询时间设置

show variables like 'long_query_time';

二、mysql的备份与恢复

1、数据备份的重要性

（1）备份的主要目的是灾难恢复

（2）在生产环境中，数据的安全性至关重要

（3）任何数据的丢失都可能产生严重的后果

2、数据丢失的原因

（1）程序错误

（2）人为操作错误.

（3）运算错误

（4）磁盘故障

（5）灾难(如火灾、地震)和盗窃

3、mysql备份的分类

按照物理和逻辑进行分类

（1）物理备份

对数据库操作系统的物理文件(如数据文件、日志文件等)的备份

物理备份的方法

（1）冷备份（脱机备份）

是在关闭数据库的时候进行的

（2）热备份（联机备份）

:数据库处于运行状态，依赖于数据库的日志文件

（3）温备份

数据库锁定表格(不可写入但可读)的状态下进行备份操作

（2）逻辑备份

对数据库逻辑组件(如:表等数据库对象)的备份

4、mysql数据库的备份策略

（1）完整备份

每次对数据库进行完整的备份

是对整个数据库、数据库结构和文件结构的备份

保存的是备份完成时刻的数据库

是差异备份与增量备份的基础

（2）差异备份

备份自从上次完全备份之后被修改过的文件

（3）增量备份

只有在上次完全备份或者增量备份后被修改的文件才会被备份

5、mysql的完全备份

（1）物理冷备份和恢复

1、关闭mysql，对日志进行备份
systemctl stop mysqld
tar Jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data/

2、开启mysql，删除库文件
systemctl start mysqld.service 
drop datebase year；

3、恢复文件
tar Jxvf /opt/mysql_all_2022-05-31.tar.xz -C /usr/local/mysql/
systemctl restart mysql

（2）mysqldump备份与恢复

mysqldump -u root -pAbc123 --databases sys mysql > /opt/sys-mysql.sql

（3）完全备份mysql中所有的库

mysqldump -u root -pAbc123 --all-databases >/opt/all.sql

（3）备份数据库中的表

mysqldump -u root -pAbc123 year xin> /opt/year_xin.sql

2、恢复数据库

1、免交互式删除year库
mysql -u root -pAbc123 -e 'drop database year;'

2、查看是否成功删除
mysql -u root -pAbc123 -e 'show databases;'

3、将之前的备份好的数据库数据导入mysql数据库，并查看是否恢复误删的数据库
mysql -u root -pAbc123 < /opt/year.sql
mysql -u root -pAbc123 -e 'show databases;'

3、恢复删除的数据表

1、免交互式删除year库中的xin表
mysql -u root -pAbc123 -e 'drop table year.xin;'

2、查看是否成功删除
mysql -u root -pAbc123 -e 'show tables from year;'

3、将之前的备份好的数据库数据导入mysql数据库中的year库中，并查看是否恢复误删的数据库
mysql -u root -pAbc123 year < /opt/year_xin.sql
mysql -u root -pAbc123 -e 'show tables from year;'

三、mysql的增量备份与恢复

1、开启二进制日志功能

vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
binlog_format = MIXED

2、查看二进制日志文件是否开启

systemctl restart mysqld
ls -l /usr/local/mysql/data/mysql-bin.*

3、设置每周备份任务

crontab -e

15 2 * * 6 mysqldump -u root -pAbc123 --all-databases year > /opt/year_$(date +%F).sql
15 2 * * 6 mysqldump -u root -pAbc123 year xin > /opt/year_xin_$(date +%F).sql

4、设置增量备份

 mysqladmin -u root -pAbc123 flush-logs

5、插入新数据，以模拟数据的增加或变更

mysql -u root -pAbc123
use year;
insert into xin values(2,'dj',20,'男',30);
insert into xin values(3,'dai',30,'男',30);

6、再次生成新的二进制日志文件

mysqladmin -u root -pAbc123 flush-logs

7、查看二进制日志文件

cp /usr/local/mysql/data/mysql-bin.000003 /opt/
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000003

mysql的一般恢复

(1)模拟丢失更改的数据的恢复步骤

mysql -u root -p
use school;
delete from class where id=3;
delete from class where id=4;
select * from year;
quit

mysqlbinlog --no-defaults /opt/mysql-bin.000003 | mysql -u root -p
mysql -u root -p -e "select * from year.xin;"

（2）模拟丢失所有数据的恢复步骤

mysql -u root -pAbc123
use year;
drop table class;
show tables;
quit

mysql -uroot -p year < /opt/year_xin_2021-05-31.sql
mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -uroot -p
mysql -u root -p -e "select * from year.xin;"

（3）断点恢复

mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002

#部分二进制文件的内容
......
BEGIN
/*!*/;
##-------------解释：at xxx 表示位置点------------------------------------------------
# at 302
##--------------解释：开头210531 15:45:53表示时间，其他的现在用不到-----------------------------------
#210531 21:45:53 server id 1  end_log_pos 449 CRC32 0xe972def7 	Query	thread_id=6	exec_time=0	error_code=0
##--------------解释：这里是执行的操作语句---------------------
use `school`/*!*/;        <-------------use school;使用数据库
SET TIMESTAMP=1612597553/*!*/; <------------建立时间戳
insert into class values ('6','qqq','223366','666666','nanjing') <-------向表中插入数据
/*!*/;
##---------------------------------------------------------------
# at 449
#210531 21:45:53 server id 1  end_log_pos 480 CRC32 0x5efde826 	Xid = 446
COMMIT/*!*/;
# at 480
#210531 21:45:54 server id 1  end_log_pos 545 CRC32 0x11768895 	Anonymous_GTID	last_committed=1	sequence_number=2	rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 545
#210224 21:45:54 server id 1  end_log_pos 628 CRC32 0x778ea5fa 	Query	thread_id=6	exec_time=0	error_code=0
SET TIMESTAMP=1612597554/*!*/;
##-------------------------------插入第二个数据--------------------------
BEGIN
/*!*/;
# at 628
#210206 21:45:54 server id 1  end_log_pos 775 CRC32 0x66e3bb53 	Query	thread_id=6	exec_time=0	error_code=0
SET TIMESTAMP=1612597554/*!*/;
insert into class values ('7','www','666555','777777','changzhou')
/*!*/;
# at 775
#210224 21:45:54 server id 1  end_log_pos 806 CRC32 0x7b972395 	Xid = 447
COMMIT/*!*/;
# at 806
#210224 21:48:52 server id 1  end_log_pos 853 CRC32 0x0d77c456 	Rotate to mysql-bin.000003  pos: 4
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
.......

（4）仅恢复到位置点为“628”之前的数据，即不恢复“id=7”的数据

#模拟数据丢失
mysql -uroot -p123456 school < /opt/school_class_2021-05-31.sql
mysql -uroot -p123456 -e "select * from year.xin;"
#到位置点628停止恢复数据
mysqlbinlog --no-defaults --stop-position='628' /opt/mysql-bin.000002 | mysql -uroot -pAbc123
#查看class表的数据
mysql -uroot -p123456 -e "select * from year.xin;"

基于时间点恢复

#模拟数据丢失
mysql -uroot -pAbc123 year < /opt/year_xin_2022-05-31.sql
mysql -uroot -pAbc123 -e "select * from year.xin;"
#到2022-05-31 21:45:54截止恢复数据
mysqlbinlog --no-defaults --stop-datetime='2022-05-31 21:45:54' /opt/mysql-bin.000002 | mysql -uroot -pAbc123
#查看class表的数据
mysql -uroot -pAbc123 -e "select * from year.xin;"