mysql数据备份总结

最新推荐文章于 2021-02-04 13:57:30 发布
最新推荐文章于 2021-02-04 13:57:30 发布
阅读量212 收藏 1
点赞数 2
分类专栏： mysql 文章标签： mysql备份
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_34193883/article/details/83348291
版权

mysql数据备份（重要）

​ 对于一个网站来说，数据是最重要的，也是无可替代的，服务器宕机可以更换硬件，项目丢失都可以根据之前的需求重写，但是数据丢失对于一个公司来说，是灾难性的，所以下面，主要介绍几种常用的mysql备份方式

数据的备份类型

数据的备份主要分为以下两类

  1. 完全备份：备份整个数据库
  2. 部分备份：备份部分数据集，又分为以下两种
    1. 增量备份：备份上一次备份以后的增量数据，优点：省空间；缺点：还原复杂
    2. 差异备份：本分上一次完全备份以后变化的数据，缺点：浪费空间；有点：还原简单

mysql的数据备份有以下几种

  1. 热备份：数据库进行备份时，读写不受影响
  2. 温备份：数据库进行备份时，可读不可写
  3. 冷备份：数据库进行备份时，不可读写，数据库也不可访问

在考虑备份方式时，还要考虑到数据库本身的存储引擎

引擎热备温备冷备
MyISAM×√√
InnoDB√√√

数据备份与恢复

一、复制文件进行数据恢复
  1. 定时复制mysql目录下，data文件夹下相关数据库的文件夹
  2. 数据丢失后，直接将文件夹还原（不可重装数据库或在新数据库上进行直接复制，可能导致数据无法使用）
二、mysqldump+binlog进行数据恢复
  1. 定时利用mysqldump备份全表数据
  2. 定期备份binlog作为增量文件备份
  3. 数据丢失后，首先利用MySQLdump导出的数据进行恢复，然后将增量数据逐步恢复（恢复时间很长，尤其是表格数据较多时）
三、使用lvm2快照备份（未测试）

  1. 部署lvm环境

    #添加硬盘; 这里我们直接实现SCSI硬盘的热插拔, 首先在虚拟机中添加一块硬盘, 不重启
[root@node1 ~]# ls /dev/sd*   #只有以下几块硬盘, 但是我们不重启可以让系统识别新添加的硬盘
/dev/sda  /dev/sda1  /dev/sda2

[root@node1 ~]# echo '- - -' > /sys/class/scsi_host/host0/scan 
[root@node1 ~]# echo '- - -' > /sys/class/scsi_host/host1/scan 
[root@node1 ~]# echo '- - -' > /sys/class/scsi_host/host2/scan 

[root@node1 ~]# ls /dev/sd*    #看！sdb识别出来了
/dev/sda  /dev/sda1  /dev/sda2  /dev/sdb


[root@node1 ~]# fdisk /dev/sdb   #分区
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel with disk identifier 0xd353d192.
Changes will remain in memory only, until you decide to write them.
After that, of course, the previous content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
         switch off the mode (command 'c') and change display units to
         sectors (command 'u').

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-2610, default 1): 
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-2610, default 2610): +15G

Command (m for help): t
Selected partition 1
Hex code (type L to list codes): 8e
Changed system type of partition 1 to 8e (Linux LVM)

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.
You have new mail in /var/spool/mail/root
[root@node1 ~]# partx -a /dev/sdb
BLKPG: Device or resource busy
error adding partition 1

##创建逻辑卷
[root@node1 ~]# pvcreate /dev/sdb1
  Physical volume "/dev/sdb1" successfully created
[root@node1 ~]# vgcreate myvg /dev/sdb1 
  Volume group "myvg" successfully created
[root@node1 ~]# lvcreate -n mydata -L 5G myvg 
  Logical volume "mydata" created.

[root@node1 ~]# mkfs.ext4 /dev/mapper/myvg-mydata   #格式化
[root@node1 ~]# mkdir /lvm_data
[root@node1 ~]# mount /dev/mapper/myvg-mydata /lvm_data  #挂载到/lvm_data


[root@node1 ~]# vim /etc/my.cnf    #修改mysql配置文件的datadir如下

datadir=/lvm_data

[root@node1 ~]# service mysqld restart  #重启MySQL

####重新导入employees数据库########略过####

  2. 创建快照卷并备份

    mysql> FLUSH TABLES WITH READ LOCK;     #锁定所有表
Query OK, 0 rows affected (0.00 sec)

[root@node1 lvm_data]# lvcreate -L 1G -n mydata-snap -p r -s /dev/mapper/myvg-mydata   #创建快照卷
  Logical volume "mydata-snap" created.

mysql> UNLOCK TABLES;  #解锁所有表
Query OK, 0 rows affected (0.00 sec)

[root@node1 lvm_data]# mkdir /lvm_snap  #创建文件夹
[root@node1 lvm_data]# mount /dev/myvg/mydata-snap /lvm_snap/  #挂载snap
mount: block device /dev/mapper/myvg-mydata--snap is write-protected, mounting read-only

[root@node1 lvm_data]# cd /lvm_snap/
[root@node1 lvm_snap]# ls
employees  ibdata1  ib_logfile0  ib_logfile1  mysql  mysql-bin.000001  mysql-bin.000002  mysql-bin.000003  mysql-bin.index  test
[root@node1 lvm_snap]# tar cf /tmp/mysqlback.tar *  #打包文件到/tmp/mysqlback.tar

[root@node1 ~]# umount /lvm_snap/  #卸载snap
[root@node1 ~]# lvremove myvg mydata-snap  #删除snap

  3. 恢复数据

    [root@node1 lvm_snap]# rm -rf /lvm_data/*
[root@node1 ~]# service mysqld start    #启动MySQL, 如果是编译安装的应该不能启动(需重新初始化), 如果rpm安装则会重新初始化数据库


mysql> SHOW DATABASES;   #查看数据库, 数据丢失!
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

[root@node1 ~]# cd /lvm_data/
[root@node1 lvm_data]# rm -rf * #删除所有文件
[root@node1 lvm_data]# tar xf /tmp/mysqlback.tar     #解压备份数据库到此文件夹 
[root@node1 lvm_data]# ls  #查看当前的文件
employees  ibdata1  ib_logfile0  ib_logfile1  mysql  mysql-bin.000001  mysql-bin.000002  mysql-bin.000003  mysql-bin.index  test
    三、xtrabackup数据恢复

    1. 备份数据

      [root@node1 ~]# mkdir /extrabackup  #创建备份目录
[root@node1 ~]# innobackupex --user=root /extrabackup/ #备份数据
###################提示complete表示成功*********************

[root@node1 ~]# ls /extrabackup/  #看到备份目录
2016-04-27_07-30-48 
[root@node1 ~]# innobackupex --apply-log /extrabackup/2016-04-27_07-30-48/  #指定备份文件的目录

#一般情况下下面三行结尾代表成功*****************
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 369661462
160427 07:40:11 completed OK!

[root@node1 ~]# cd /extrabackup/2016-04-27_07-30-48/
[root@node1 2016-04-27_07-30-48]# ls -hl  #查看备份文件
total 31M
-rw-r----- 1 root root  386 Apr 27 07:30 backup-my.cnf
drwx------ 2 root root 4.0K Apr 27 07:30 employees
-rw-r----- 1 root root  18M Apr 27 07:40 ibdata1
-rw-r--r-- 1 root root 5.0M Apr 27 07:40 ib_logfile0
-rw-r--r-- 1 root root 5.0M Apr 27 07:40 ib_logfile1
drwx------ 2 root root 4.0K Apr 27 07:30 mysql
drwx------ 2 root root 4.0K Apr 27 07:30 performance_schema
drwx------ 2 root root 4.0K Apr 27 07:30 test
-rw-r----- 1 root root   27 Apr 27 07:30 xtrabackup_binlog_info
-rw-r--r-- 1 root root   29 Apr 27 07:40 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root  117 Apr 27 07:40 xtrabackup_checkpoints
-rw-r----- 1 root root  470 Apr 27 07:30 xtrabackup_info
-rw-r----- 1 root root 2.0M Apr 27 07:40 xtrabackup_logfile

    2. 恢复数据

      [root@node1 ~]# rm -rf /data/*   #删除数据文件

***不用启动数据库也可以还原*************

[root@node1 ~]# innobackupex --copy-back /extrabackup/2016-04-27_07-30-48/   #恢复数据, 记清使用方法

#########我们这里是编译安装的mariadb所以需要做一些操作##########
[root@node1 data]# killall mysqld

[root@node1 ~]# chown -R mysql:mysql ./* 
[root@node1 ~]# ll /data/      #数据恢复
total 28704
-rw-rw---- 1 mysql mysql    16384 Apr 27 07:43 aria_log.00000001
-rw-rw---- 1 mysql mysql       52 Apr 27 07:43 aria_log_control
-rw-rw---- 1 mysql mysql 18874368 Apr 27 07:43 ibdata1
-rw-rw---- 1 mysql mysql  5242880 Apr 27 07:43 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 Apr 27 07:43 ib_logfile1
-rw-rw---- 1 mysql mysql      264 Apr 27 07:43 mysql-bin.000001
-rw-rw---- 1 mysql mysql       19 Apr 27 07:43 mysql-bin.index
-rw-r----- 1 mysql mysql     2166 Apr 27 07:43 node1.anyisalin.com.err


[root@node1 data]# service mysqld restart
MySQL server PID file could not be found!                  [FAILED]
Starting MySQL..                                           [  OK  ]

MariaDB [(none)]> SHOW DATABASES;  #查看数据库, 已经恢复
+--------------------+
| Database           |
+--------------------+
| information_schema |
| employees          |
| mysql              |
| performance_schema |
| test               |
+--------------------+

    3. 增量备份

      #########创建连两个数据库以供测试#####################
MariaDB [(none)]> CREATE DATABASE TEST1;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> CREATE DATABASE TEST2;
Query OK, 1 row affected (0.00 sec)

[root@node1 ~]# innobackupex --incremental /extrabackup/ --incremental-basedir=/extrabackup/2016-04-27_07-30-48/ 

[root@node1 ~]# ls /extrabackup/2016-04-27_07-57-22/ #查看备份文件
total 96
-rw-r----- 1 root root   386 Apr 27 07:57 backup-my.cnf
drwx------ 2 root root  4096 Apr 27 07:57 employees
-rw-r----- 1 root root 49152 Apr 27 07:57 ibdata1.delta
-rw-r----- 1 root root    44 Apr 27 07:57 ibdata1.meta
drwx------ 2 root root  4096 Apr 27 07:57 mysql
drwx------ 2 root root  4096 Apr 27 07:57 performance_schema
drwx------ 2 root root  4096 Apr 27 07:57 test
drwx------ 2 root root  4096 Apr 27 07:57 TEST1
drwx------ 2 root root  4096 Apr 27 07:57 TEST2
-rw-r----- 1 root root    21 Apr 27 07:57 xtrabackup_binlog_info
-rw-r----- 1 root root   123 Apr 27 07:57 xtrabackup_checkpoints
-rw-r----- 1 root root   530 Apr 27 07:57 xtrabackup_info
-rw-r----- 1 root root  2560 Apr 27 07:57 xtrabackup_logfile

[root@node1 ~]# innobackupex --apply-log --redo-only /extrabackup/2016-04-27_07-30-48/
[root@node1 ~]# innobackupex --apply-log --redo-only /extrabackup/2016-04-27_07-30-48/ --incremental-dir=/extrabackup/2016-04-27_07-5
7-22/

    4. 恢复数据

      [root@node1 ~]# rm -rf /data/*   #删除数据

[root@node1 ~]# innobackupex --copy-back /extrabackup/2016-04-27_07-30-48/     #整理增量备份之后可以直接通过全量备份还原

[root@node1 ~]# chown -R mysql.mysql /data/

总结

备份方法备份速度恢复速度便捷性功能一般用于
cp快快一般、灵活性低很弱少量数据备份
mysqldump慢慢一般、可无视存储引擎的差异一般中小型数据量的备份
lvm2快照快快一般、支持几乎热备、速度快一般中小型数据量的备份
xtrabackup较快较快实现innodb热备、对存储引擎有要求强大较大规模的备份
Michael·Ren
关注
  • 2
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
用shell写一个mysql数据备份脚本
09-09
1. **MySQL数据备份脚本**： 使用`mysqldump`工具进行数据库备份是最常见的方法。`mysqldump`提供了多种选项来控制备份过程，例如`--opt`、`--single-transaction`和`--master-data`。在shell脚本中，你需要指定...
mysql备份表时注意事项
qq_33635666的博客
03-08 104
备份表： mysqldump -h(ip) -uroot -p(password) databasename tablename > tablename.sql 注：mysqldump命令是在没有登录MySQL之前使用 负责会出现![在这里插入图片描述](https://img-blog.csdnimg.cn/20190308152908193.PNG) 指定目录 mysqldump -...
第八章 mysql 数据物理备份percona-xtrabackup
weixin_44990801的博客
09-24 455
一.MySQL数据备份 所有备份数据都应放在非数据库本地，而且建议有多份副本。 测试环境中做日常恢复演练，恢复较备份更为重要。 备份： 能够防止由于机械故障以及人为误操作带来的数据丢失，例如将数据库文件保存在了其它地方。 冗余： 数据有多份冗余，但不等备份，只能防止机械故障还来的数据丢失，例如主备模式、数据库集群。 备份过程中必须考虑因素： 1. 数据的一致性 2. 服务的可用性 逻辑备份： 备份的是建表、建库、插入等操作所执行SQL语句（DDL DML DCL），适用于中小型数据库.
mysql数据库数据备份与恢复
热门推荐
dayi_123的博客
11-15 1万+
1、通过物理拷贝备份与恢复          对MyISAM引擎的表来说：如果当前此表在拷贝过程中没有任何写操作时，可以直接拷贝，对单张表拷贝时只需把把表相关的三个以.frm,.MYD,.MYI结尾的文件拷贝到另外的数据库实例对应的数据库目录下即可。          对InnoDB表来说：即使是innodb_file_per_table=on的情况下直接拷贝与表相关的两个文件到目标实例下不行...
学会用各种姿势备份MySQL数据库
weixin_34038652的博客
04-27 339
学会用各种姿势备份MySQL数据库大纲前言为什么需要备份数据？数据的备份类型MySQL备份数据的方式备份需要考虑的问题设计合适的备份策略使用cp进行备份使用mysqldump+复制BINARY LOG备份使用lvm2快照备份数据使用Xtrabackup备份前言我们试着想一想, 在生产环境中什么最重要？如果我们服务器的硬件坏了可以维修或者换新, 软件问题可以修复或重新安装,...
mysql备份要注意哪些_备份MySQL数据库时需要注意哪些事项
weixin_42662605的博客
01-20 509
备份MySQL数据库时需要注意哪些事项发布时间：2020-11-07 15:54:25来源：亿速云阅读：86作者：Leah这篇文章将为大家详细讲解有关备份MySQL数据库时需要注意哪些事项，文章内容质量较高，因此小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。对于MySQL备份，每个DBA的理解可能都不一样，备份可以分为下面几个维度：文件种类划分：1、物理备份，以xtrab...
备份mysql数据结构.docx
05-28
MySQL 数据库备份是数据库管理中非常重要的一步，备份可以保障数据的安全性和可靠性。本文将详细介绍两种 MySQL 数据库备份方法：通过 MySQL Workbench 备份数据库和通过 cmd 中 mysql 命令备份数据库。 方法一：...
mysql 数据备份与恢复
10-23
### MySQL 数据备份与恢复 #### 一、MySQL 数据备份的重要性 在现代企业的信息化管理中，数据库作为信息存储的核心，其安全性和稳定性至关重要。一旦发生硬件故障、人为误操作或者恶意攻击等情况，如果没有有效的...
使用PHP备份MYSQL数据的多种方法
10-26
总结来说，使用PHP进行MySQL数据备份是一个灵活性和自定义程度都很高的选择。它允许开发者根据需求编写备份脚本，将数据备份到服务器上或者外部存储设备中。然而，考虑到性能和安全性，开发者在实际应用中应仔细规划...
数据备份申请表.docx
最新发布
04-29
根据给定的“数据备份申请表.docx”文档信息，我们可以从中提炼出多个与数据备份相关的知识点，包括但不限于数据备份的定义、不同类型的备份方法、备份频率与保留周期的设定等。 ### 数据备份概述 数据备份是信息...
mysql dump 影响业务_mysqldump对mysql数据库的影响
weixin_33410492的博客
02-04 1145
对于想入门或者初级，中级mysql数据库运维人员，了解mysqldump对mysql数据库的影响，是非常必要的，当执行mysqldump命令之后，mysql后台执行了什么，下面就带大家看看，在这里使用general_log进行分析1.首先的开启数据库的general_log，如下所示[root@localhost] 17:30:41 [(none)]>show variables like ...
mysql进行字符串处理
也无风雨也无晴
03-08 1万+
mysql进行字符串的处理 MySQL 字符串截取函数：left(), right(), substring(), substring_index()。还有 mid(), substr()。其中，mid(), substr() 等价于 substring() 函数，substring() 的功能非常强大和灵活。 字符串截取：left(str, length) select left(...
MySQL Too many open files
也无风雨也无晴
04-25 3347
MySQL Too many open files背景修改 背景 最近公司运维对路由进行了修改，导致开发数据库查不到部分路由信息，对开发数据库进行路由维护后，导致多人访问时会出现无法连库和读表的情况，具体报错如下 Error Code: 1018 Can't read dir of '.' (errno: 24 - Too many open files) 修改 首先重启数据库，使数据库可以访...
MySQL Update批量更新死锁问题排查
也无风雨也无晴
12-12 3012
MySQL Update批量更新死锁问题排查背景锁分析可能性分析结果处理 背景 业务中有两个不同的业务，更新同一批数据时出现死锁问题，sql语句如下 update test set a= 1 where name = 'test'; update test set b = 2 where name = 'test' and time >0; -- 这里的name是一个普通的索引 频繁出现死...
and和or的优先级
也无风雨也无晴
08-28 1488
MySQL中and和or的优先级MySQLphp MySQL MySQL中关于and和or的运算，类似与*和+的预算，举例如下 -- 这个返回返回全部数据 select * from table_a where 1 or 0 and 0; --这个返回空集 select * from table_a where 0 or 1 and 0; php if (1 || 0 && 0) { //会执行 } if (0 || 1 && 0) { //不会执行 } ...
mysql删库后操作指南
也无风雨也无晴
10-24 640
数据库删除后要做的事情 首先确定是否有备份 有备份，恢复 没备份，辞职 确定上一次备份距离现在的时间 很久了 准备长时间恢复数据，顺便写一下简历 不久前 找一下binlog日志是否有足够长的数据 有，利用binlog日志，恢复备份后到删库前的增量数据 没有，数据只能恢复到备份时的数据，公司能接受，继续上班，不能接受，辞职（和钱相关的平台，肯定不能接受） 删库之前有备份 ...
phpstudy更新mysql
也无风雨也无晴
02-26 421
phpstudy更新mysql下载安装新版本的mysql安装mysql 下载安装新版本的mysql 下载地址https://dev.mysql.com/downloads/mysql/，我这里下载的最新的8.0.15的 安装mysql 将phpstudy服务停止，退出 打开MySQL的安装目录，将整个目录备份一下，如果安装失败，直接还原即可。 将MySQL目录清空 将下载的MySQL安装包解压到...
MySQL日期 字符串 时间戳互转
也无风雨也无晴
12-04 303
MySQL日期 字符串 时间戳互转 平时比较常用的时间、字符串、时间戳之间的互相转换，虽然常用但是几乎每次使用时候都喜欢去搜索一下用法；本文将作为一个笔记，整理一下三者之间的 转换（即：date转字符串、date转时间戳、字符串转date、字符串转时间戳、时间戳转date，时间戳转字符串）用法，方便日后查看； 涉及的函数 1. date_format(date, format) 函数，MyS
MySQL主从架构数据备份测试与配置
"这篇博客文章主要讨论了在MySQL环境中如何设置单个主库与多个从库的数据备份测试，特别强调了从库的只读性质。作者通过使用Docker来搭建MySQL主从架构，并且详细介绍了测试过程，包括系统更新、Docker安装、端口...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Michael·Ren

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值