mysql 创建备份文件怎么打开_MySQL数据备份及恢复(1)

前言

MySQL备份一般采用全库备份加日志备份的方式,根据业务的需要,可以采用每周日凌晨1点进行完全备份以及每小时进行一次增量备份,这样在MySQL故障后可以使用完全备份和日志备份尽可能的去恢复最完整的数据。

一、binlog日志恢复

MySQL的二进制日志记录着该数据库所有增删改的操作日志(前提是需要自己开启binlog),还包括了这些操作的执行时间,binlog的使用场景无外乎就是主从同步以及恢复数据库。开启binlog功能,需要编辑MySQL的主配置文件,如下:

1、查看二进制功能是否开启(如下,值为OFF,则表示未开启):

6ef73a0f80917dd7b0925af6c9ead39f.png

2、开启二进制日志功能:

[root@mysql ~]# vim /etc/my.cnf #在mysqld字段下写入下面配置,以便开启二进制日志并指定二进制文件名

#开启二进制日志,需要指定server-id,否则服务将会启动失败

log-bin=/usr/local/mysql/data/bin_log

server-id=1

[root@mysql ~]# systemctl restart mysqld

#重启后,将在指定的目录下生成两个文件,如下:

[root@mysql data]# pwd

/usr/local/mysql/data

[root@mysql data]# ls | grep bin_log

bin_log.000001 #每次重启mysql服务或执行flush logs命令,都会生成一个新的这样的文件,依次为000001、000002......

bin_log.index #这个文件存储所有二进制文件的索引

开启二进制日志功能后,所有增删改的操作都会记录到二进制日志文件当中,注意,是增删改的操作,不包括查操作。

3、确定二进制日志功能已开启

3fd41986c25164af1925a1cffa9bc1ed.png

4、执行增删改以便测试bin_log是否有记录

mysql> reset master; #清空所有的二进制文件,从00001开始

mysql> create database test_db1;

mysql> use test_db1;

mysql> create table tb1(id int primary key auto_increment,name varchar(20));

mysql> insert into tb1(name) values('zhangsan');

mysql> insert into tb1(name) values('lisi');

#重新开始一个新的日志文件再执行操作。注意,此时上面所有的操作写入的是第一个二进制日志文件

mysql> flush logs;

mysql> delete from tb1 where name='lisi';

mysql> insert into tb1(name) values('tom');

5、MySQL中查看二进制日志文件及文件内容

查看二进制日志文件:

1622df745196f0349cca513bf7ac3c88.png

查看二进制日志文件内容:

完整的命令格式如下:

SHOW BINLOG EVENTS[IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

# in:指定要查看的二进制文件;

# from:指定从哪个“pos”位置开始查看

# limit:限制返回的行数,offset是指跳过多少行再显示

注:如果不指定二进制文件名,那么默认显示第一个二进制日志文件中的事件,文件内容中包含了日志文件名、事件的开始位置、事件类型、结束位置、信息等内容。

e19e82a85e71a433355ed9fbd53f56f5.png

其他命令:

show master logs:也是查看二进制日志文件;

PURGE BINARY LOGS:用于删除二进制文件;

例子:

PURGE BINARY LOGS TO 'mysql-bin.00010'; #把这个文件之前的其他文件都删除掉

PURGE BINARY LOGS BEFORE '2016-08-28 22:46:26'; #把指定时间之前的二进制文件删除了

6、使用mysqlbinlog本地查看二进制日志内容

[root@mysql data]# pwd

/usr/local/mysql/data

[root@mysql data]# mysqlbinlog bin_log.000001

#使用-v选项可以查看出日志文件中的详细信息,两个v可以查看出更详细的信息,但是三个vvv也不会有什么作用了

[root@mysql data]# mysqlbinlog -v bin_log.000001

[root@mysql data]# mysqlbinlog -vv bin_log.000001

7、通过二进制日志恢复数据

假设在开始删除lisi记录的那条sql语句是误操作,现在要通过二进制日志来恢复数据。

1)首先需要找到删除lisi记录的sql语句在二进制日志中的位置,每条sql语句都是一个事务,所以需要从其begin到commit,才算是完整的sql语句。如下:

68a38565a548106899a40907ed166529.png

从上面可以看出,delete事件发生position是219,事件结束是393。

2)事件恢复流程:直接用bin-log日志将数据库恢复到删除位置219前,然后跳过故障点,再进行恢复下面所有的操作,具体恢复流程如下:

导出相关binlog文件(将二进制文件转换为sql语句生成新的文件):

[root@mysql data]# mysqlbinlog bin_log.000001 > /tmp/01.sql

[root@mysql data]# mysqlbinlog --stop-position=219 bin_log.000002 > /tmp/219.sql

[root@mysql data]# mysqlbinlog --start-position=393 bin_log.000002 > /tmp/393.sql

上述指令中,第一条比较好理解,无非就是使用msyqlbinlog查看第一个二进制文件,并生成新文件,后面两条指令呢,--stop-postition意思是查看时到219这个位置不查看,一直到393才又开始接着查看。最后的结果就是新生成的文件中不会包含删除lisi记录的sql语句。

3)删除数据库

mysql> drop database test_db1;

4)利用binlog恢复数据

[root@mysql data]# mysql -uroot -p123.com < /tmp/01.sql #恢复第一个日志文件

[root@mysql data]# mysql -uroot -p123.com < /tmp/219.sql #恢复第二个

[root@mysql data]# mysql -uroot -p123.com < /tmp/393.sql #恢复第三个

5)确定数据已恢复

f446b7b4ef329bcf065219d02906d491.png

二、mysqldump备份工具

mysqldump是mysql用于备份和数据转移的一个工具。主要产生一系列的SQL语句,可以封装到文件,该文件包含有所有重建数据库所需要的 SQL命令,如CREATE DATABASE,CREATE TABLE,INSERT等等。可以用来实现轻量级的快速迁移或恢复数据库。 mysqldump 是将数据表导成 SQL 脚本文件,在不同的 MySQL 版本之间升级时相对比较合适,这也是最常用的备份方法。 mysqldump一般在数据量很小的时候(几个G)可以用于备份。当数据量比较大的情况下,就不建议用mysqldump工具进行备份了。

mysqldump可以针对单个表、多个表、单个数据库、多个数据库、所有数据库进行导出的操作。

mysqldump使用示例:

1、备份某一个表

[root@mysql backup]# mysqldump -u root -p123.com mysql user > mysql-user.sql #备份mysql库中的user表

[root@mysql backup]# ls #查看备份文件

mysql-user.sql

2、恢复mysql数据库中的user表

[root@mysql backup]# mysql -u root -p123.com mysql < mysql-user.sql

3、备份mysql库

[root@mysql backup]# mysqldump -u root -p123.com --databases mysql > mysql.sql #备份mysql库

[root@mysql backup]# ls #查看备份文件

mysql.sql

4、恢复mysql库

[root@mysql backup]# mysql -u root -p123.com < mysql.sql

5、备份所有的库(当导出的数据量较大时,可以添加“--opt”选项以优化执行速度)

[root@mysql backup]# mysqldump -u root -p123.com --opt --all-databases > all-data.sql #备份所有库

[root@mysql backup]# ls #查看备份文件

all-data.sql

已标记关键词 清除标记
表情包
插入表情
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
相关推荐
<p style="text-align:left;"> <span> </span> </p> <p class="ql-long-24357476" style="font-size:11pt;color:#494949;"> <span style="font-family:"color:#E53333;font-size:14px;background-color:#FFFFFF;line-height:24px;"><span style="line-height:24px;">限时福利1:</span></span><span style="font-family:"color:#3A4151;font-size:14px;background-color:#FFFFFF;">购课进答疑群专享柳峰(刘运强)老师答疑服务。</span> </p> <p> <br /> </p> <p class="ql-long-24357476"> <strong><span style="color:#337FE5;font-size:14px;">为什么说每一个程序员都应该学习MySQL?</span></strong> </p> <p class="ql-long-24357476"> <span style="font-size:14px;">根据《2019-2020年中国开发者调查报告》显示,超83%的开发者都在使用MySQL数据库。</span> </p> <p class="ql-long-24357476"> <img src="https://img-bss.csdn.net/202003301212574051.png" alt="" /> </p> <p class="ql-long-24357476"> <span style="font-size:14px;">使用量大同时,掌握MySQL早已是运维、DBA的必备技能,甚至部分IT开发岗位也要求对数据库使用和原理有深入的了解和掌握。</span><br /> <br /> <span style="font-size:14px;">学习编程,你可能会犹豫选择 C++ 还是 Java;入门数据科学,你可能会纠结于选择 Python 还是 R;但无论如何, MySQL 都是 IT 从业人员不可或缺的技能!</span> </p> <span></span> <p> <br /> </p> <p> <span> </span> </p> <h3 class="ql-long-26664262"> <p style="font-size:12pt;"> <strong class="ql-author-26664262 ql-size-14"><span style="font-size:14px;color:#337FE5;">【课程设计】</span></strong> </p> <p style="font-size:12pt;"> <span style="color:#494949;font-weight:normal;"><br /> </span> </p> <p style="font-size:12pt;"> <span style="color:#494949;font-weight:normal;font-size:14px;">在本课程中,刘运强老师会结合自己十多年来对MySQL的心得体会,通过课程给你分享一条高效的MySQL入门捷径,让学员少走弯路,彻底搞懂MySQL。</span> </p> <p style="font-size:12pt;"> <span style="color:#494949;font-weight:normal;"><br /> </span> </p> <p style="font-size:12pt;"> <span style="font-weight:normal;font-size:14px;">本课程包含3大模块:</span><span style="font-weight:normal;font-size:14px;"> </span> </p> </h3> <p class="ql-long-26664262" style="font-size:11pt;color:#494949;"> <strong class="ql-author-26664262"><span style="font-size:14px;">一、基础篇:</span></strong> </p> <p class="ql-long-26664262" style="font-size:11pt;color:#494949;"> <span class="ql-author-26664262" style="font-size:14px;">主要以最新的MySQL8.0安装为例帮助学员解决安装与配置MySQL的问题,并对MySQL8.0的新特性做一定介绍,为后续的课程展开做好环境部署。</span> </p> <p class="ql-long-26664262" style="font-size:11pt;color:#494949;"> <span class="ql-author-26664262" style="font-size:14px;"><br /> </span> </p> <p class="ql-long-26664262" style="font-size:11pt;color:#494949;"> <strong class="ql-author-26664262"><span style="font-size:14px;">二、SQL语言篇</span></strong><span class="ql-author-26664262" style="font-size:14px;">:</span> </p> <p class="ql-long-26664262" style="font-size:11pt;color:#494949;"> <span class="ql-author-26664262" style="font-size:14px;">本篇主要讲解SQL语言的四大部分数据查询语言DQL,数据操纵语言DML,数据定义语言DDL,数据控制语言DCL,</span><span style="font-size:14px;">学会熟练对库表进行增删改查等必备技能。</span> </p> <p class="ql-long-26664262" style="font-size:11pt;color:#494949;"> <span style="font-size:14px;"><br /> </span> </p> <p class="ql-long-26664262" style="font-size:11pt;color:#494949;"> <strong class="ql-author-26664262"><span style="font-size:14px;">三、MySQL进阶篇</span></strong><span style="font-size:14px;">:</span> </p> <p class="ql-long-26664262" style="font-size:11pt;color:#494949;"> <span style="font-size:14px;">本篇可以帮助学员更加高效的管理线上的MySQL数据库;具备MySQL的日常运维能力,语句调优、备份恢复等思路。</span> </p> <span><span> <p style="font-size:11pt;color:#494949;"> <span style="font-size:14px;"> </span><img src="https://img-bss.csdn.net/202004220208351273.png" alt="" /> </p> </span></span>
©️2020 CSDN 皮肤主题: 数字20 设计师:CSDN官方博客 返回首页