MySQL数据库备份和恢复

数据库备份

  • 数据库复制不能取代备份的作用
  • 备份分类:
    • 全量备份:整个数据库的完整备份
    • 增量备份:在上一次备份基础上,对更改数据进行备份。mysqldump不支持这种
    • 逻辑备份:结果为SQL语句,适用于所有存储引擎
    • 物理备份:对数据库目录的靠背,对于内存表只备份结构
    • 备份内容:
    • 备份方式:

mysqldump全备介绍

  • mysqldump备份
mysqldump database [tables]
mysqldump --database DB1 [DB2]
mysqldump --all-databases
  • 常用参数
    • 账户具备的权限SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT, SHOW VIEW, PROCESS
    • -u
    • -p
    • --single-transaction 开启事务来操作,innodb推荐用这个
    • -l 依次锁表,一般myisam用。备份时锁住一个数据库下的表。如果混合引擎的表也用这个参数,和 --single-transaction互斥。只能保持一个db下的一致
    • -x,锁所有db的所有表
    • --master-data=[1或2] 时间恢复和新的slave实例用。1把change master to被记录,2则是把1放在注释里,配合 --single-transaction一起指定
    • -R 备份存储过程
    • --triggers 触发器
    • -E 备份调度事件
    • --hex-blob 把数据文本变成hex格式
    • --tab=path 指定路径下,生成表结构和表数据两个文件
    • -w='过滤条件',单表导出用

mysqldump恢复

  • bash: mysql-u-p dbname<backup.sql
  • mysql client: mysql>source/tmp/backup.sql

指定时间点的恢复

先决条件

  • 具有指定时间点前的一个全备
  • 具备自上次全备后到指定时间点的所有“二进制日志”(相当于重复操作从备份时间点的到现在的数据库操作)

步骤:

  • 还原某个时间点的全备 mysql-uroot-p mc_orderdb<mc_order_backup.sql
  • 查找全备时开始的mysql-bin的log操作的日志点(change master那行)
  • 查看最近的误操作mysql-bin的log的日志点
  • mysqlbinlog--start-position=84882--stop-position=169348--database=mc_orderdb>mc_order_diff.sql
  • mysql-uroot-p mc_orderdb<mc_order_diff.sql

实时备份binlog

  • GRANT REPLICATION SLAVE ON*.*TO'repl'@'ip'IDENTIFIED BY'xxxxxx'
  • mkdir-p binlog_backup
  • mysqlbinlog--raw--read-from-remote-server--stop-never--host localhost--port3306-u repl-p xxxxxx二进制日志名

xtrabackup备份和恢复

  • xtrabackup用于在线备份innodb存储引擎的表
    • 只会备份数据文件,不会备份表结构
  • innobackupex是对extrabackup的封装并提供MyISAM表的备份功能
    • innobackupex是Xtrabackup的插件支持MyISAM备份,但也会锁表
全备和恢复
  • 全备 innobackupex--user=root--password=pwd--parallel=2/home/db_backup/
  • 备份恢复 innobackupex--apply-log/path/to/BACKUP-DIR
增量备份和恢复
  • 增量备份 innobackupex--user=root--password=pwd--incremental/home/db_backup/--incremental-basedir=/home/db_backup/back-dir 参数 --incremental-basedir上一次全备的文件夹
  • 增量备份恢复
    1. innobackupex--apply-log--redo-only全备目录
    2. innobackupex--apply-log--redo-only全备目录--incremental-dir=第一次增量目录
    3. innobackupex--apply-log全备目录
    4. mv/path/to/BACKUP-DIR/home/mysql/data 恢复后的数据直接替换原本的
    5. 记得改变属于的用户chmod

制定备份计划

  • 每天凌晨对数据库进行一次全备
  • 实时对二进制日志进行远程备份
    • 使用linux定时任务:crontab

参考

  1. 高性能可扩展MySQL数据库设计及架构优化 电商项目,sqlercn,https://coding.imooc.com/class/79.html

原文发布于微信公众号 - 林欣哲(gh_aba6caba3ac7)

原文发表时间:2018-05-11

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏数据和云

经典故障分析 -用好UTL_FILE包其实并不是太容易

作者介绍 ? 崔华 网名 dbsnake Oracle ACE Director,ACOUG 核心专家 UTL_FILE包可以用来读写操作系统上的文本文件...

3499
来自专栏醉梦轩

Ubuntu 16.04 部署MySQL服务

MySQL是一款开源的关系型数据库管理系统,大量公司都在使用它,或是在它的基础上做二次开发。有时,我们会需要在Linux系统上部署MySQL服务用于测试。

1071
来自专栏Python、Flask、Django

Ajax与php进行数据交互

1552
来自专栏张善友的专栏

SQL Server : Browser服务

SQL Server : Browser服务是SQL Server 2005新增的,简单的说,如果一个物理服务器上面有多个SQL Server实例,那么为了确保...

2195
来自专栏上善若水

013 windows10 mysql的安装

Access denied for user 'root'@'localhost' (using password:YES) 解决方案 问题的解决

1403
来自专栏python3

解决centos7 /etc/rc.local 不能执行

最近发现centos7 的/etc/rc.local不会开机执行,于是认真看了下/etc/rc.local文件内容的就发现了问题的原因了

1372
来自专栏猿天地

solr or es 结合弥补mongodb的全文检索功能

最近将公司的solr集群升级到了最新的6.5版本。 之前用了N年的是3点多的版本,那个时候solr还不支持自带的集群,集群方式都是自己实现的。 公司里用的最多的...

46214
来自专栏酷玩时刻

Centos7.3 安装Mysql5.7并修改初始密码

http://dev.mysql.com/doc/mysql-yum-repo-quick-guide/en/

2124
来自专栏逸鹏说道

SQL SERVER几种数据迁移/导出导入的实践

SQLServer提供了多种数据导出导入的工具和方法,在此,分享我实践的经验(只涉及数据库与Excel、数据库与文本文件、数据库与数据库之间的导出导入)。 (一...

6789
来自专栏C/C++基础

MySQL设置远程访问

在MySQL Server端,执行mysql 命令进入mysql 命令模式。使用grant命令用来建立新用户,指定用户口令并增加用户权限。命令格式如下:

1591

扫码关注云+社区

领取腾讯云代金券