MySQL备份与恢复、日志管理

MySQL备份与恢复、日志管理

前言：

学习备份的目的是为了灾难的恢复，备份还可以测试应用，回滚数据修改，查询历史数据、审计等。而在备份、恢复当中，日志起到了重要作用

一、日志的应用

MySQL的日志默认保存在/usr/local/mysql/data当中

1.1、如何开启日志

vim /etc/my.cnf进入MySQL的配置文件中在【mysqld】中加入下方配置.错误日志,用来记录当MySQL启动、停止或运行时发生的错误信息,默认已开启log-error=/usr/local/mysql/data/mysql_error.log 指定日志的保存位置和文件名.通用查询日志,用来记录MySQU的所有连接和语句,默认是关闭的general_log=ONgeneral_log_file=/usr/local/mysql/data/mysql_general.log.二进制日志(binlog),用来记录所有更新了数据或者已经潜在更新了数据的语句,记录了数据的更改,可用数据恢复,默认已开启log-bin-mysql-bin或log_bin-mysql-bin.中继日志般情况下它在Mysal主从同步(复制)、读写分离集群的从节点开启。主节点一般不需要这个日志。.慢查询日志,用来记录所有执行时间超过long_query_time秒的语句,可以找到哪些查询语句执行时提醒优化,默认是关闭的slow_query_log=ONslow_query_log_file=/usr/local/mysql/data/mysql_slow_query.loglong_query_time=5 #设置超过5秒执行的语句被记录,缺省时为10秒

1.2、查询日志

配置文件后,先重启数据库，在进入MySQLsystemctl restart mysql,servicemysql -uroot -p

show variables like 'general8'查看通用查询日志是否开启show variables like 'log_bint查看二进制日志是否开启show variables like 'slow%';查看慢查询日功能是否开启show variables like 'long_query_time': 查看慢查询时间设置set global slow_query_log=ON在数据库中设置开启慢查询的方法当中的变量like表示模糊查询%。。。代表以。。。结尾的文件。。。%以。。。开头的文件%。。。%中间只有出现。。。字段会显示出来。。。则为精确查找

1.3、二进制文件

开启二进制文件后，重启MySQL，会在目录中生产一个二进制文件cd /usr/local/mysql/datals

二、数据备份

2.1、数据备份的重要性

备份的主要自的是灾难恢复

在生产环境中,数据的安全性至关重要

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

造成数据丢失的原因

程序的错误人为的错误操作运算错误磁盘故障灾难（火灾、地震等）和磁盘盗窃

2.2、数据备份的类型

数据库的备份分为两种物理备份和逻辑备份

1、物理备份

分为冷备份（脱机备份）、热备份（链接备份）和温备份

冷备份：实在数据库关闭的时候进行的

热备份：数据库处于运行状态，依赖于数据库的日志文件（mysqlhotcopy  mysqlbackup）

温备份：数据库锁定表格（不可以写入但可以读取）的状态进行备份操作（mysqldump）

2、逻辑备份

逻辑备份是对数据库逻辑组件的备份，表示为逻辑数据库结构分为完全备份、差异备份和增量备份。

完全备份： 又叫完整备份，对整个数据库进行备份，保存的是备份完成时刻的数据库（是差异备份和增量备份的前提，每次增量备份都是备份在上一次完全备份或者增量备份之后的数据，不会出现备份重复数据的情况，也不会占用额外的磁盘空间）

特点： 备份恢复简单，占用磁盘空间大，备份时间较长（恢复数据时，需要安装次序恢复完全备份和增量备份的数据）

增量备份： 以上一次完全备份或者上一次增量备份的时间为节点，只备份时间节点数据发生变化的部分（通过日志文件备份）（每次都进行完全备份，会导致文件占用空间巨大，并且有大量重复数据）

特点： 备份数据量小，占用空间小，备份速度快,恢复时需要一次完整备份 + 所有增量备份依次恢复（恢复时，使用完全备份的文件即可)

差异备份： 备份上一次完全备份后被修改过的所有文件（每次差异备份，都会备份上一次完全备份之后的数据，可能出现备份重复数据，导致占用额外的磁盘空间）

特点： 备份数据量大，恢复时需要恢复上次的完全备份 + 最近一次的差异备份（恢复时，先恢复完全备份的数据，在恢复差异备份的数据）

三、MySQL备份的实战

实验环境：

mysql -u root -pcreate database ys;use ys;create table if not exists yr ( #如果没有CLASS1这个表则创建,有则不创建id int(4) not null auto_increment, #字段一: id整型 最大显示长度4位不能为空 自动递增从1开始name varchar(10) not null, #字段二: name可变长度字段 最多10字符 不能为空sex char(10) not null, #字段三: sex固定度字段10字节不能为空hobby varchar(50), #字段四: bobby可变长度字段最多50字符primary key (id)); #主键(id)insert into yr values(1,'lip','male','running');insert into yr values(2,'lzz','myp','holle');

3.1、MySQL完全备份

InnoDB存储引擎的数据库在磁盘上存储成三个文件：db.opt(表属性文件)、表名.frm(表结构文件)、表名.ibd(表数据文件)。

3.1.1、物理冷备份与恢复

systemctl stop mysqldyum -y install xztar Jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data/cd /usr/local/mysqltar Jxvf /opt/mysql_all_.tar.xz -C ./systemctl restart mysql

让我们mv移走data，在tar解压备份的数据，模拟数据丢失后如何恢复的场景。

需要重启MySQL服务：systemctl restart mysql

3.1.2、mysqldump备份与恢复（温备份）

3.1.2.1、完全备份一个或多个完整的库（包括其中所有的表）

mysqldump -u root -p[密码] --databases 库名1 [库名2] … > /备份路径/备份文件名.sql #导出的就是数据库脚本文件mysqldump -uroot -p123456 --databases ys > /opt/ys.sqlmysqldump -uroot -p123456 --databases mysql ys > /opt/mysql-ys.sql

3.1.2.2、完全备份mysql服务器中所有的库

mysqldump -u root -p[密码] --all-databases > /备份路径/备份文件名.sqlmysqldump -u root -p123456 --all-databases > /opt/all.sql

3.1.2.3、完全备份指定库中的部分表并查看备份文件

mysqldump -u root -p[密码] 库名 [表名1] [表名2] … > /备份路径/备份文件名.sqlmysqldump -uroot -p123456 ys yr > /opt/ys_yr.sql#使用“-d”选项，说明只保存数据库的表结构#不使用“-d”选项，说明表数据也进行备份查看备份文件cat ys_yr.sql grep -v "^--" /opt/ys_yr.sql | grep -v "^/" | grep -v "^$"

完全备份指定ys库里的yr表，到opt目录下，名为ys_yr.sql

3.2、完全备份

3.2.1、恢复数据

mysql -uroot -p123456 -e 'drop database ys;'#“-e”选项，用于指定连接 MySQL 后执行的命令，命令执行完后自动退出mysql -uroot -p123456 -e 'SHOW DATABASES;'mysql -uroot -p123456 < /opt/ys.sqlmysql -uroot -p123456 -e 'SHOW DATABASES;'

3.2.2、恢复数据表

当备份文件中只包含表的备份，而不包含创建的库的语句时，执行导入操作时必须指定库名，且目标库必须存在mysql -uroot -p123123 -e 'drop table ys.yr;'mysql -uroot -p123123 -e 'show tables from ys;'mysql -uroot -p123123 SCHOOL < /opt/ys_yr.sqlmysql -uroot -p123123 -e 'show tables from ys;'

3.3、MySQL增量备份

3.3.1、开启二进制日志功能

vim /etc/my.cnf[mysqld]log-bin=mysql-binbinlog_format = MIXED server-id = 1二进制日志(binlog)有3种不同的记录格式：STATEMENT(基于SQL语句)、ROW(基于行)、MIXED(混合模式)，默认格式是STATEMENTsystemctl restart mysqldcd /usr/local/mysql/data/ls

3.3.2、可每周对数据库或表进行完全备份

mysqldump -uroot -p123123 ys yr > /opt/ys_yr_$(date +%F).sqlmysqldump -uroot -p123123 --all-databases ys > /opt/ys_$(date +%F).sql

3.3.3、每天可以进行增量备份，生产二进制日志文件

cd /usr/local/mysql/data/lsmysqladmin -uroot -p123456 flush-logsls

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

mysql -uroot -puse ys;insert into yr values(3,'lzx','look','reading');insert into yr values(4,'long','good','running');select * from yr;

3.3.5、再次生产二进制文件，并且查看二进制文件内容

mysqladmin -uroot -p123123 flush-logslscp /usr/local/musql/data/mysql-bin.000007 /opt/cd /opt/mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/musql-bin.000007

3.4、MySQL增量备份恢复

3.4.1、一般恢复

mysql -uroot -puse ys;delete from yr where id=4;delete from yr where id=3;select * from yr;quitmysqlbinlog --no-defaults /usr/local/mysql/data/musql-bin.000006 | mysql -uroot -p123456mysql -uroot -p123456 -e "select * from ys.yr;"

3.4.2、模拟丢失所有数据的恢复步骤

mysql -uroot -puse ysdrop table yr;exit;mysql -uroot -p123456 ys < /opt/ys_yr.sql mysql -uroot -p123456 -e "select * from ys.yr;"mysqlbinlog --no-defaults /usr/local/mysql/data/musql-bin.000006 |mysql -uroot -p123456mysql -uroot -p123456 -e "select * from ys.yr;"

3.4.3．断点恢复

mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/musql-bin.000006需要解读二进制日志文件，方便查看详细的内容，以便进行下一步操作

这是解读后的musql-bin.000006文件/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER /*!*/;# at 4#220621 6:55:52 server id 1 end_log_pos 123 CRC32 0xfbf5ad73 Start: binlog v 4, server v 5.7.17-log created 220621 6:55:52# at 123#220621 6:55:52 server id 1 end_log_pos 154 CRC32 0xdd9532f2 Previous-GTIDs# [empty]# at 154#220621 7:01:32 server id 1 end_log_pos 219 CRC32 0x08e73150 Anonymous_GTID last_committed=0 sequence_number=1SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;# at 219#220621 7:01:32 server id 1 end_log_pos 289 CRC32 0xc5396418 Query thread_id=4 exec_time=error_code=0SET TIMESTAMP=1655820092/*!*/;SET @@session.pseudo_thread_id=4/*!*/;SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;SET @@session.sql_mode=1437073414/*!*/;SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;/*!\C utf8 *//*!*/;SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;SET @@session.lc_time_names=0/*!*/;SET @@session.collation_database=DEFAULT/*!*/;BEGIN/*!*/;# at 289#220621 7:01:32 server id 1 end_log_pos 341 CRC32 0x66b7fefb Table_map: `ys`.`yr` mapped to number 219# at 341#220621 7:01:32 server id 1 end_log_pos 398 CRC32 0xbcf43dc4 Write_rows: table id 219 flags: STMT_END_F### INSERT INTO `ys`.`yr`### SET### @1=3### @2='lzx'### @3='look'### @4='reading'# at 398#220621 7:01:32 server id 1 end_log_pos 429 CRC32 0x1c2adaa5 Xid = 12COMMIT/*!*/;# at 429#220621 7:02:10 server id 1 end_log_pos 494 CRC32 0xab9c9673 Anonymous_GTID last_committed=1 sequence_number=2SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;# at 494#220621 7:02:10 server id 1 end_log_pos 564 CRC32 0x640c53c8 Query thread_id=4 exec_time=error_code=0SET TIMESTAMP=1655820130/*!*/;BEGIN/*!*/;# at 564#220621 7:02:10 server id 1 end_log_pos 616 CRC32 0xec5aec71 Table_map: `ys`.`yr` mapped to number 219# at 616#220621 7:02:10 server id 1 end_log_pos 674 CRC32 0x10c06ff9 Write_rows: table id 219 flags: STMT_END_F### INSERT INTO `ys`.`yr`### SET### @1=4### @2='long'### @3='good'### @4='running'# at 674#220621 7:02:10 server id 1 end_log_pos 705 CRC32 0x496e23fc Xid = 13COMMIT/*!*/;# at 705#220621 7:09:11 server id 1 end_log_pos 752 CRC32 0x33dea980 Rotate to musql-bin.000007 pos: 4SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;DELIMITER ;# End of log file/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

3.4.3.1、基于位置恢复

mysql -uroot -p123456 -e "select * from ys.yr;"mysql -uroot -p123456 -e "truncate table ys.yr;"mysql -uroot -p123456 -e "select * from ys.yr;"mysqlbinlog --no-defaults --stop-position='564' /opt/musql-bin.000006|mysql -uroot -p123456mysql -uroot -p123456 -e "select * from ys.yr;"意思是恢复操作564之前的数据，不会恢复id=4的数据

方法二：

仅恢复id=4的数据，跳过id=3的数据恢复，564只后，仅有第四条记录

mysql -uroot -p123456 -e "select * from ys.yr;"mysqlbinlog --no-defaults --start-position='564' /opt/musql-bin.000006|mysql -uroot -p123456mysql -uroot -p123456 -e "select * from ys.yr;"

3.4.3.2、基于时间点恢复

仅恢复到7:02:10 之前的数据，即不恢复“user4”的数据

mysql -uroot -p123456 -e "truncate table ys.yr;"mysql -uroot -p123456 -e "select * from ys.yr;"mysqlbinlog --no-defaults --stop-datetime='2022-6-21 7:02:10' /opt/musql-bin.000006|mysql -uroot -p123456mysql -uroot -p123456 -e "select * from ys.yr;"

方法二：​

恢复id=4的数据，跳过id=3的数据恢复

mysqlbinlog --no-defaults --start-datetime='2022-6-21 7:02:10' /opt/musql-bin.000006|mysql -uroot -p123456mysql -uroot -p123456 -e "select * from ys.yr;"

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。