目录
一、mysql的日志
1、日志的分类
2、日志的查询
二、mysql的备份与恢复
1、数据备份的重要性
2、数据丢失的原因
3、mysql备份的分类
4、mysql数据库的备份策略
5、mysql完全备份
一、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;"