Mysql-12-mysql的备份和恢复

1.数据库的备份

  备份=拷贝加管理,防止数据的丢失同时记录用户的操作记录。其中最有效的备份是对IT架构进行备份。

原则:

(1)数据库要定期备份,备份的周期应当根据应用数据系统可承受的恢复时间,而且定期备份的时间应当在系统负荷最低的时候进行。对于重要的数据,要保证在极端情况下的损失都可以正常恢复。

(2)定期备份后,同样需要定期做恢复测试,了解备份的正确可靠性,确保备份的有意义的、可恢复的。

(3)根据系统需要来确定是否采用增量备份,增量备份只需要备份每天的增量数据,备份花费的时间少,对系统负载的压力也小。缺点是恢复的时候需要加载之前所有的备份数据。恢复时间较长。

(4)确保mysql打开了log-bin选项,mysql在做完整恢复或者基于时间点恢复的时候都需要binlog。

(5)可以考虑异地备份。

2.逻辑备份和恢复

(1)逻辑备份:也可以成为文件级备份,是将数据库中的数据备份为一个文本文件,而备份大小取决于文件大小。并且该文本文件是可以移植到其他机器上的,甚至是不同硬件结构的机器。

  • 使用mysqldump命令生成insert语句备份
语法:mysqldump [arguments] >file_name.sql

使用帮助如下:

[root@cai ~]# mysqldump
Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump –help
例:备份所有数据库,语句如下:
[root@cai dumpback]# mysqldump -uroot -p123456 --all-databases >/tmp/dumpback/alldb.sql
备份某些数据库,语法如下:
[root@cai dumpback]# mysqldump -uroot -p123456 --databases aa >/tmp/dumpback/data_aa.sql
备份某数据库中的表,语句如下:
[root@cai dumpback]# mysqldump -uroot -p123456 aa cairui >/tmp/dumpback/aa_cairui.sql
查看备份内容,语句如下:
[root@cai dumpback]# more aa_cairui.sql

如何保证数据备份的一致性?有以下两种方法:

①同一时刻取出所有数据

对于事务支持的存储引擎,如innodb或者bdb等,可以通过控制将整个备份过程在同一事物中,使用 -single-transaction test>test_backup.sql选项

例:[root@cai dumpback]# mysqldump -uroot -p123456 --single-transaction aa >/tmp/dumpback/aa_backup.sql

②数据库中的数据处于静止状态

通过锁表参数未完成

LOCK-TABLES 每次锁定一个数据库的表,此参数默认为true

LOCK-ALL-TABLES 一次锁定所有的表,适用于dump的表分别处于各个不同的数据库中的情况。

l 生成特定格式的纯文本文件备份

①通过select … to outfile from … 命令

参数如下:

fields escaped by [‘name’]:在sql语句中需要转义的字符

fields terminated by:设定每两个字段之间的分隔符

fields [optionally] enclosed by ‘name’:包装,有optionally数字类型不被包装,否则全包装。

lines terminated by ‘name’:行分隔符,即每记录结束时添加的字符。

例:mysql> select * into outfile '/tmp/tb1.txt'
    -> fields terminated by ','
    -> optionally enclosed by '"'
    -> lines terminated by '\n'
-> from cai limit 50;

②通过mysqldump工具命令导出文本。

用此方法可以生成一个文本数据和一个对应的数据库结构创建脚本,主要参数:

-T,--tab=name

例:导出sqoop库的tb1表(http://blog.itpub.net/31386161/viewspace-2130313/错误处理方法)

[root@cai ~]# mysqldump -uroot -p123456 -T /tmp sqoop tb1 --fields-enclosed-by=\" --fields-terminated-by=,

(2)逻辑备份的恢复

[root@cai dumpback]# mysql -uroot -p123456 -D cai </tmp/dumpback/aa_cairui.sql

将aa底下的cairui表恢复到cai database下

②纯文本文件的恢复

使用mysqllimport工具恢复,此工具可以用于恢复生成txt和sql两文件,所以保证txt文件对应的数据库的表存在。

首先恢复表结构,语句如下:
[root@cai tmp]# mysql -uroot -p123456 -D aa </tmp/tb1.sql
恢复数据,语句如下:
[root@cai tmp]# mysqlimport -uroot -p123456 aa --fields-enclosed-by=\" --fields-terminated-by=, /tmp/tb1.txt

3.物理备份和恢复

物理备份比逻辑备份速度要快,分为以下两种:

a。冷备份:这种方式是最直接的备份方式,就是首先停掉数据库服务,然后cp数据文件,恢复时停止mysql,先进行操作系统级别恢复文件,然后重启mysql服务,使用mysqlbinlog工具恢复自备份以来的所有binlog。这种方式虽然简单,而且对所有引擎支持,但是有一个非常大的弊端就是需要关闭数据库服务。在当前的大多数信息系统都是不允许长期时间停机的。

b。热备份:对于不同的存储引擎方法也不同

4.各种备份与恢复方法的具体实现

(1)利用select into outfile实现数据备份与还原

①把需要备份的数据备份出来

mysql> select * from students;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | li   |   36 |
|    2 | wang |   26 |
|    3 | cai  |   12 |

mysql> select * from students where age>30 into outfile '/tmp/students.txt';//将年龄大于30的同学的信息备份出来

发现students。txt是文本文件,不能直接导入数据库,需要使用load data infile恢复到mysql服务器端,删除年龄大于30的用户,模拟数据被破坏。

mysql> delete from students where age>30;
mysql> load data infile '/tmp/students.txt' into table students;

(2)备份策略:完全备份+增量备份+二进制日志

①先给数据库做完全备份

http://www.cnblogs.com/kerrycode/p/4565669.html(错误解决办法)

[root@cai tmp]# mysqldump -uroot -p123456 --single-transaction --master-data=2 --databases hellodb >/backup/hellodb_'data+%F'.sql

②回到mysql服务器端更新数据

mysql> use hellodb;
Database changed
mysql> create table tb1(id int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into tb1 values(1),(2),(3);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

③先查看完全备份文件里面记录的位置

[root@cai backup]# cat hellodb_data+%F.sql | less
-- CHANGE MASTER TO MASTER_LOG_FILE='cai-bin.000001', MASTER_LOG_POS=107;(记录了二进制日志的位置)

④回到服务器端

mysql> show master status;显示此时的二进制日志的位置,从备份文件里边记录的位置到我们此时的位置即为增量的部分。
+----------------+----------+--------------+------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------+----------+--------------+------------------+
| cai-bin.000001 |      394 |              |                  |
+----------------+----------+--------------+------------------+
1 row in set (0.00 sec)

⑤做增量备份

[root@cai backup]# mysqlbinlog --start-position=107 --stop-position=394 /application/mysql/data/cai-bin.000001 >/backup/hellodb_'data+$F_%H'.sql

⑥再回到服务器

mysql> insert into tb1 values(4),(5);
mysql> drop database hellodb;

⑦导出这次二进制日志

[root@cai backup]# mysqlbinlog --start-position=394 /application/mysql/data/cai-bin.000001 查看删除操作时二进制日志的位置
[root@cai backup]# mysqlbinlog --start-position=394 --stop-position=587 /application/mysql/data/cai-bin.000001 >/tmp/hellodb.sql(导出二进制日志)

⑧先让mysql离线

mysql>set sql_log_bin=0;关闭二进制日志
mysql>flush logs;滚动下日志

⑨模拟数据库破坏

mysql>drop database hellodb;

⑩开始恢复数据

#mysql </backup/hellodb_2013-09-08.sql导入完全备份文件
#mysql </backup/hellodb_2013-09-08_05.sql导入增量备份文件
#mysql <hellodb.sql//导入二进制文件

(1真正生产环境中,应该导出的是整个mysql服务器中的数据,而不是单个数据库,因此应该使用-all-databases。

(2在导出二进制日志的时候,可以直接复制文件即可,但是要注意的是,备份之前滚动下日志。

(3利用LVM快照实现几乎热备的数据备份与恢复。

重要:使用xtrabackup做备份恢复

1.优势

(1)快速可靠的进行完全备份

(2)在备份的过程中不影响事务

(3)支持数据流、网络传输、压缩,所以它可以有效地节约磁盘资源和网络带宽。

(4)可以自动备份检验数据的可用性。

a。安装rabackup

[root@cai tools]# tar zxf percona-xtrabackup-2.4.2-Linux-x86_64.tar.gz 
[root@cai tools]# mv percona-xtrabackup-2.4.2-Linux-x86_64 /application/percona-xtrabackup2.4.2
[root@cai tools]# ln -s /application/percona-xtrabackup2.4.2/ /application/xtrabackup
[root@cai tools]#  echo "export PATH=\$PATH:/application/xtrabackup/bin" >> /etc/profile
[root@cai tools]# . /etc/profile

b。全量备份

mysql> create user 'backup'@'%' identified by '123456';创建备份用户
mysql> grant all on *.* to 'backup'@'%';授权
mysql> flush privileges;使生效
mysql> select * from cairui;
+------+------+
| id   | name |
+------+------+
|    1 | li   |
|    2 | wang |
测试的表为cairui 在cai底下

xtrabackup_checkpoints:备份类型、备份状态和LSN(日志序列号)范围信息。

xtrabackup_binlog_info:mysql服务器当前正在使用的二进制文件及备份这一刻为止的二进制日志事件的位置。

xtrabackup_logfile:非文本文件,xtrabackup自己的日志文件。

backup-my.cnf:备份时数据文件中关于mysql的配置。

mysql> delete from bb where age >30;
xtrabackup的增量备份代码如下:
[root@cai backup]# innobackupex --user=root --password=123456 --incremental /data/backup/--incremental-basedir=/data/backup/2017-05-11_16-57-24

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

MySQL集群:高可用性DBMS

如果你在寻找一个不会发生单点故障的数据库管理系统,那么水平拓展的MySQL集群分布式多主架构将是您的最佳选择。MySQL集群可以通过MySQL和...

2897
来自专栏张戈的专栏

zabbix agentd客户端插件Shell一键自动安装脚本

这次生产环境上线了多台 Linux 服务器,需要全部纳入 Zabbix 监控范畴,一台一台的去装 Zabbix Agentd 插件那就太苦逼了,所幸 Zabbi...

3435
来自专栏蓝天

MySQL-5.7.10主主同步的安装和配置

这里安装的是最新的MySQL 5.7.10(5.7.12、5.7.13(后续更新版本可能类似)有些区别,安装过程中遇到错误可搜索下本文,也许可以找到解决办法...

1544
来自专栏乐沙弥的世界

myloader恢复mysql数据库示例

    mydumper是针对mysql数据库备份的一个轻量级第三方的开源工具,备份方式为逻辑备份。它支持多线程,备份速度远高于原生态的mysqldump以及众...

1232
来自专栏Python

mysql数据备份与恢复

MySQL数据备份与恢复 #1. 物理备份: 直接复制数据库文件,适用于大型数据库环境。但不能恢复到异构系统中如Windows。 #2. 逻辑备份: 备份的是建...

3856
来自专栏破晓之歌

Restful API实战 原

举例:需要请求获取服务器允许我对服务器进行哪些操作,可以用OPTIONS请求。还有类似访问频率参数

1682
来自专栏LanceToBigData

MySQL(十五)之数据备份中mysqldump详解

前言   其实前面一篇数据备份已经是非常的详细了,这里我想单独的讲解一下mysqldump,相信很多程序员都是用过这个命令的! 一、MySQL数据库的备份与还原...

3258
来自专栏python学习之旅

Python笔记(十二):操作数据库

(一)      前言     本文说明如何连接Oracle、MySQL、sqlserver,以及执行sql、获取查询结果等。 (二)      DB-API ...

3899
来自专栏沈唁志

如何在CentOS 7上安装PostgreSQL关系数据库

PostgreSQL关系数据库系统是一个功能强大的,可扩展的,并符合标准的开源数据库平台。本指南将帮助您在CentOS 7 Linode上安装和配置Postgr...

1692
来自专栏小狼的世界

重新认识wget

Wget是平时经常会用到的一个工具,这个工具其实有着非常丰富的参数和配置,能够实现很强大的功能。比较常用的一个就是抓站或者为网站做镜像。

1011

扫码关注云+社区

领取腾讯云代金券