mysql多种备份与恢复方式一

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了mysql多种备份与恢复方式一，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4613字，纯文字阅读大概需要7分钟。

内容图文

基于mysql社区版5.7,严重声明：本文中图方便直接写入了-p数据库密码，在生产环境中如果指定了一定要使用history -C清空历史命令记录哦，所有博客作者原创纯手打，转载一定要注明本博客链接，感谢！

通过mysqldump方式进行备份和恢复

物理备份：拷贝mysql下的数据目录将其备份即为物理备份。
逻辑备份：使用mysqldump进行备份可以进行逻辑备份。
完全备份：备份所有数据。
增量备份：备份上次备份后，所有新产生的数据。
差异备份：备份完全备份后，所有产生的数据。

         mysqldump -uroot -ppassword 库名 > /目录/备份文件名.sql
 #使用mysqldump逻辑备份时会自动锁表，导致数据无法写入。正好可以使用这一特性，组合--flush-logs刷新binlog日志，这样
 #可以使得备份完成后刷新日志，对日志进行断点。可以在出现故障的第一时间使用备份后开始的binlog进行恢复，可以提升一部分效率。

备份时库名表示方式：

        * --all-databases 或 -A               //备份所有库
* 数据库名                             //备份单个库
* 数据库名 表名                        //单张表
* -B 数据库1 数据库2                   //多个库

        mysql -uroot -ppassword [库名] < 目录/xxx.sql

通过binlog日志进行恢复

? binlog日志也称作二进制日志，记录除查询外的所有的SQL命令，可以用于数据备份和恢复，配置mysql主从同步的必要条件。

要使用binlog日志必须要要先打开binlog日志记录功能，在/etc/my.cnf下打开#log_bin注释

                log_bin=/binlog存放目录/命名前缀
# 可以单独只打开log_bin，而不指定路径和名字，将默认存放到mysql主文件夹中，在rpm包安装中默认路径是/var/lib/mysql目录，名称
#前缀也可以不指定默认使用主机名作为前缀，设定了存放目录后要给mysql写入权限才能使用，如果不给写入权限会导致服务无法启动。
server_id=1
# 指定服务器ID值范围1-255,开启binlog一定要设置服务器ID不设定也会无法启动服务。
max_binlog_size=数值m
# 指定日志文件容量，默认1G
binlog_format="Mixed"
# 指定binlog日志记录模式，修改为mixed模式。三种日志记录模式分别是：row（行模式）、statement（报表模式）、mixed（混合模式）

在修改配置文件后需要将mysql重启服务才能生效。

                show master status ;

File	Position	Binlog_Do_DB	Binlog_Ignore_DB	Executed_Gtid_Set
当前日志文件	偏移量	只记录指定数据库的二进制日志	不记录指定数据库的二进制日志	已执行的事务编号

看到File和Positiion有值后即为打开成功。
在数据库主从同步的时候也是必须要打开binlog日志的。
删除指定编号之前的binlog日志文件：purge master logs to "binlog文件名"。
删除所有的binlog日志，重建日志：reset master。

通过在数据库命令行中查询binlog日志查看日志记录模式是否改成混合模式。

                show variables like "%binlog_format%";
#如果values是MIXED则说明开启了混合模式。

说到这里又要扯到mysql binlog三种模式。

模式	优点	缺点
row（行模式）	非常清楚记录下每一行数据被修改的细节，可以很好支持存储过程和function以及trigger调用和触发器。	主从同步占用大量磁盘IO，日志量巨大。
statement（报表模式）	记录每一条修改数据的SQL语句事件，并非每条更改记录。减少了binlog日志量，节约磁盘IO，提升性能。	函数、存储过程的复制不能很好的支持，导致有时主从同步出现问题。
Mixed（混合模式）	MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志格式，也就是在Statement和Row之间选择一种，缓和了磁盘IO，和日志文件大小。	兼顾解决磁盘IO问题，但未测试函数、存储过程等。(看下方)

                # at 285168
#191014 23:35:10 server id 1  end_log_pos 285199 CRC32 0x0cb3a5df     Xid = 3048
COMMIT/*!*/;
# at 285199
#191014 23:36:23 server id 1  end_log_pos 285264 CRC32 0x9d6c36f7     Anonymous_GTID  last_committed=1012 sequence_number=1013
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 285264
#191014 23:36:23 server id 1  end_log_pos 285342 CRC32 0xa7d96de8     Query   thread_id=1016  exec_time=0 error_code=0
SET TIMESTAMP=1571067383/*!*/;
BEGIN
/*!*/;
# at 285342
#191014 23:36:23 server id 1  end_log_pos 285439 CRC32 0x379d2612     Query   thread_id=1016  exec_time=0 error_code=0
SET TIMESTAMP=1571067383/*!*/;
delete  from userdb2.tb1
/*!*/;

通过binlog时间区间/偏移量进行恢复

                   mysqlbinlog [选项] binlog日志文件名 | mysql -uroot -p

选项
--start-datetime="yyyy-mm-dd hh:mm:ss"
--stop-datetime="yyyy-mm-dd hh:mm:ss"
--strat-position=偏移量
--stop-position=偏移量

恢复思路：

第一步首先通过上次的完整备份恢复到完整备份之前的状态。
第二步通过binlog定位到完整备份之后到当前删库之前的偏移量得到起始偏移量和结束偏移量（或者时间）。
第三步准确恢复校对数据。
思路校验：
先创建测试库测试表然后通过循环进行插入数据。

                mysql -uroot -p12345 -e "create database testdb charset=utf8;"
mysql -uroot -p12345 -e "create table testdb.testtb1(id int);"
for i in {1..1000};do mysql -uroot -p12345 -e "insert into testdb.testtb1(id) values($i);" ;done

然后通过mysqldump对数据库testdb进行完整备份。

                mysqldump -uroot -p12345 --flush-logs -B testdb > /tmp/testdb.sql
#参数-B和不加有何区别？ 
#1.区别就在于加参数B后可以指定备份数据库列表。
#2.生成的数据库备份文件中含有创建数据库和选择数据库的指令。
#CREATE DATABASE /*!32312 IF NOT EXISTS*/ `testdb` /*!40100 DEFAULT CHARACTER SET utf8 */;
#USE `testdb`;

模拟用户写入数据1001条。

                for i in {3000..4000};do mysql -uroot -p12345 -e "insert into testdb.testtb1(id) values($i);" ;done && mysql -uroot -p12345 -e "insert into testdb.testtb1(id) values(666);" ;

然后测试删库

                mysql -uroot -p12345 -e "drop database testdb;"

然后使用完整备份记录进行恢复。

                mysql -uroot -p12345 < testdb.sql

使用上次完整备份的记录恢复后，中间还有用户写入的1000条数据，可以通过binlog偏移量（时间差）进行恢复，先定位上次备份结束的位置

                [root@db-master tmp]# mysql -uroot -p12345 -e "select count(*) from testdb.testtb1"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------+
| count(*) |
+----------+
|     1000 |
+----------+

# at 869709
#191015  0:19:55 server id 1  end_log_pos 875708 CRC32 0x9b4fd429     Query   thread_id=3032  exec_time=0 error_code=0
SET TIMESTAMP=1571069995/*!*/;
******************************************************************************************
内容过多省略
******************************************************************************************
# at 875708
#191015  0:19:56 server id 1  end_log_pos 296078 CRC32 0x84203c10     Query   thread_id=4040  exec_time=0 error_code=0
SET TIMESTAMP=1571071195/*!*/;
insert into testdb.testtb1(id) values(666)
/*!*/;

通过偏移量进行恢复（也可以指定binlog文件和结束偏移量恢复到删库前一条，不带开始偏移量。）

                mysqlbinlog --strat-position=869709 --stop-position=875708 /logfile/lqh.000001 | mysql -uroot -p12345

原文：https://www.cnblogs.com/lqinghua/p/11675093.html

内容总结

以上是互联网集市为您收集整理的mysql多种备份与恢复方式一全部内容，希望文章能够帮你解决mysql多种备份与恢复方式一所遇到的程序开发问题。如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/1203900.html

来源：【匿名】

【上一篇】mysql修改root密码【下一篇】用IE远程创建Mysql数据库的简易程序

更多 ►

【mysql多种备份与恢复方式一】教程文章相关的互联网学习教程文章

基于mysql社区版5.7,严重声明：本文中图方便直接写入了-p数据库密码，在生产环境中如果指定了一定要使用history -C清空历史命令记录哦，所有博客作者原创纯手打，转载一定要注明本博客链接，感谢！通过mysqldump方式进行备份和恢复物理备份：拷贝mysql下的数据目录将其备份即为物理备份。逻辑备份：使用mysqldump进行备份可以进行逻辑备份。完全备份：备份所有数据。增量备份：备份上次备份后，所有新产生的数据。差异备份：备份完...

MYSQL数据导出备份、导入的几种方式

第一种 mysqldump:这是mysql自带的备份命令,提供了很多扩展参数可供选择,工作中常用此方式常用语法: mysqldump[options] database [tables,多个表用空格隔开] --databases [options] BD1 [DB2..多个库用空格隔开] --all-databases [options]导出(备份): 导出库 > SQL文件(本地导入导出不需要-P3306 -h 192.168.1.25)(-q –e写法可以合并)mysqldump -P 3306 -h 192.168.1.25...

mysql备份的三种方式详解

一、备份的目的做灾难恢复：对损坏的数据进行恢复和还原需求改变：因需求改变而需要把数据还原到改变以前测试：测试新功能是否可用二、备份需要考虑的问题可以容忍丢失多长时间的数据；恢复数据要在多长时间内完；恢复的时候是否需要持续提供服务；恢复的对象，是整个库，多个表，还是单个库，单个表。三、备份的类型 1、根据是否需要数据库离线冷备（cold backup）：需要关mysql服务，读写请求均不允许状态下进行；温备（warm ba...

mysql恢复备份数据时，部分表数据丢失的问题

原因：是由于默认的通信缓冲区的最大长度为50M（max_allowed_packed）。处理办法：//查看当前max_allowed_packet的大小show global variables like ‘max_allowed_packet‘;//修改max_allowed_packet的大小 set global max_allowed_packet=1024*1024*50;原文：https://www.cnblogs.com/GaoAnLee/p/10683422.html

mysql主从热备份【代码】

master主服务器：ip:192.168.5.112 sudo vi /etc/mysql/my.cnf[mysqld] server-id=1 log-bin=mysql-bin #一定要把下面的一句话注销，否则无法远程登录服务器 #bind-address = 127.0.0.1 #这里可以设置哪些库需要备份，哪些不需要，我在这里没有设置 #binlog_do_db = include_database_name #binlog_ignore_db = include_database_name修改完成之后，运行mysql -uroot -proot新建查询用户：mysql> CREATE USER...

linux下自动备份MySQL数据并上传到FTP上的shell脚本

复制代码代码如下:#!/bin/bash #Mysql autobackup shell # # #----------------set the mysql login parameters dbuser=root dbpasswd= dbserver=localhost dbname=fwserver2008 dbopt=--opt backupdir=/dbbackup/ #-----------------set the FTP paramters , 0 no send to a ftp server,1 send to a ftp server copytoftp=1 ftpserver=172.16.25.2 ftpuser=linux ftppasswd=123456 #----------------set the backpfile paramters...

Linux命令：MySQL系列之十二--MySQL备份与还原（重要章节）【代码】【图】

一、数据备份备份类型：热备份、温备份和冷备份热备份：读、写不受影响；温备份：仅可以执行读操作；冷备份：离线备份，读、写操作均中止；备份方式：物理备份和逻辑备份：物理备份：复制数据文件，速度快，保存数据库的所有数据结构，如果数据跟数据库关联性强，导致物理备份可移植性能力不强；逻辑备份：将数据导出至文本文件中，速度慢、丢失浮点数精度；方便使用文本处理工具直接对其处理，...

mysql 数据库的主从复制及备份、恢复【代码】

##远程登录MySql mysql -h62.234.124.229 -P31306 -ucrab -p123456mysql --host=62.234.124.229 --user=yq --password=123456##给远程登录的用户授权: grant all on *.* to crab@‘114.248.166.196‘ identified by ‘123456‘; grant select,update,delete on *.* to crab@‘114.248.166.196‘ identified by ‘123456‘;crab@‘114.248.166.196‘:权限一定是用户名+ip地址; crab@‘114.248.166.%‘ crab@‘114.248.%‘ crab@‘%‘...

mysql 备份还原数据库

备份和还原都在bin目录下操作1.备份mysqldump -u 用户名 -p 密码 --default-character-set=utf8 数据库名称 >d:/temp.sql2.还原先建立mysql空数据库mysql -h host -u 用户名 -p 密码 --default-character-set=utf8 空数据库名称 <d:/temp.sql 原文：http://www.cnblogs.com/daviddong/p/6264708.html

mysql-数据备份与还原【代码】【图】

一、数据备份1、使用mysqldump命令备份　　mysqldump命令将数据库中的数据备份成一个文本文件。表的结构和表中的数据将存储在生成的文本文件中。　　mysqldump命令的工作原理很简单。它先查出需要备份的表的结构，再在文本文件中生成一个CREATE语句。然后，将表中的所有记录转换成一条INSERT语句。然后通过这些语句，就能够创建表并插入数据。　　1、备份一个数据库　　mysqldump基本语法：mysqldump -u username -p dbname table1...

自动删除Mysql备份（数组+for）【代码】

#!/bin/bash #author:V #Dispaly:auto delete mysql backup.BACKDIR=(/home/11/mysqlbackup/ /home/full/) DAYS=2 FILENAME="full*" LOGNAME=/var/log/autodel.log DATA=`date +%Y%m%d` echo ${BACKDIR[*]} for i in ${BACKDIR[*]} dofind $i -name "$FILENAME" -type f -mtime +$DAYS -exec rm {} \; >> $LOGNAME 2>&1 if [ $? -eq 0 ];thenecho "$DATA $i OK">>$LOGNAME elseecho "$DATA $i FAIL">>$LOGNAMEexit 0 fidone 原文：...

（4.11）mysql备份还原——mysql闪回技术（基于binlog）

0、闪回技术与工具简介mysql闪回工具比较流行三大类：【0.1】官方的mysqlbinlog：支持数据库在线/离线，用脚本处理binlog的输出，转化成对应SQL再执行。通用性不好，对正则、sed等需要较高熟练度。【0.2】第三方的mysqlbinlog工具，比官方的工具多了一个闪回功能 -B【0.3】开源的binlog2sql：兼容性比较好，使用难度小，使用简单，前提是mysql必须开启。最佳实践：线上使用binlog2sql，线下用mysqlbinlog以及mysqlbinlog_flashbac...

MySQL备份锁

无论逻辑备份还是物理备份，为了获取一致性位点，都强依赖于FTWRL(Flush Table With Read Lock)。这个锁杀伤力非常大，因为持有锁的这段时间，整个数据库实质上不能对外提供写服务的。此外，由于FTWRL需要关闭表，如有大查询，会导致FTWRL等待，进而导致DML堵塞的时间变长。即使是备库，也有SQL线程在复制来源于主库的更新，上全局锁时，会导致主备库延迟。FTWRL这把锁持有的时间主要与非innodb表的数据量有关，如果非innodb表数据...

利用shell脚本实现对mysql数据库的备份

1234567891011121314151617181920212223242526272829303132333435#!/bin/bash #保存备份个数 number=3 #备份保存路径 backup_dir=/root/mysqlbackup #日期 dd=`date +%Y%m%d` #备份工具 tool=mysqldump #用户名 username=mracale #密码 password=456852 #将要备份的数据库 database_name=test #简单写法 mysqldump -u root -p123456 users > /root/mysqlbackup/users-$filename.dump$tool -u $username -p$password $databa...

Mysql备份与恢复【代码】【图】

通常数据库备份数据文件、binlog 日志文件和 my.cnf 配置文件都应在其他地方保存一份甚至多份仅备份是没有任何意义, 需要在测试环境中做日常恢复演练, 测试备份的可用性, 恢复较比备份更加的重要备份: 能够有效防止设备故障以及人为误操作带来的数据丢失, 例如:将数据文件保存在远端。冗余: 数据有多分冗余, 但不等于备份, 只能防止机械故障丢失的数据, 例如: 主备模式、数据库集群。Mysql逻辑备份与恢复完整备份与恢复增量备份...

首页 / MYSQL / mysql多种备份与恢复方式一