IT数码 购物 网址 头条 软件 日历 阅读 图书馆
TxT小说阅读器
↓语音阅读,小说下载,古典文学↓
图片批量下载器
↓批量下载图片,美女图库↓
图片自动播放器
↓图片自动播放器↓
一键清除垃圾
↓轻轻一点,清除系统垃圾↓
开发: C++知识库 Java知识库 JavaScript Python PHP知识库 人工智能 区块链 大数据 移动开发 嵌入式 开发工具 数据结构与算法 开发测试 游戏开发 网络协议 系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑 笔记本 显卡 显示器 固态硬盘 硬盘 耳机 手机 iphone vivo oppo 小米 华为 单反 装机 图拉丁
 
   -> 大数据 -> 002MySQL学习013备份和还原 -> 正文阅读

[大数据]002MySQL学习013备份和还原

部分内容来自以下博客:

https://www.cnblogs.com/chenmh/p/5300370.html

1 概述

1.1 为什么需要数据备份

在生产环境中数据库可能会遭遇各种各样的不测从而导致数据丢失,大概分为以下几种:硬件故障、软件故障、自然灾害、黑客攻击、误操作(占比最大)。

所以,为了在数据丢失之后能够恢复数据,就需要定期的备份数据。

1.2 数据备份需要考虑的因素

备份数据的策略要根据不同的应用场景进行定制,大致有几个参考数值,可以根据这些数值从而定制符合特定环境中的数据备份策略:

能够容忍丢失多少数据。

恢复数据需要多长时间。

需要恢复哪一些数据。

1.3 数据备份类型

数据的备份类型根据其自身的特性主要分为以下几组:完全备份、部分备份。

1.3.1 完全备份

完全备份指的是备份整个数据集(即整个数据库)、部分备份指的是备份部分数据集(例如只备份一个表)

1.3.2 部分快递

部分备份分为以下两种:增量备份、差异备份。

增量备份指的是备份自上一次增量备份或完全备份以来变化的数据。其特点是节约空间,但是还原麻烦。

差异备份指的是备份自上一次完全备份以来变化的数据。其特点是浪费空间,但是还原比增量备份简单。

1.4 数据备份方式

1.4.1 备份方式

在MySQL中备份数据一般有几种方式:热备份、温备份、冷备份。

热备份指的是当数据库进行备份时,数据库的读写操作均不是受影响。

温备份指的是当数据库进行备份时,数据库的读操作可以执行,但是不能执行写操作。

冷备份指的是当数据库进行备份时,数据库不能进行读写操作,即数据库要下线。

1.4.2 引擎支持

MySQL中进行不同方式的备份还要考虑存储引擎是否支持:

MyISAM:不支持热备份,仅支持温备份和冷备份。

InnoDB:支持热备份,也支持温备份和冷备份。

1.5 数据备份策略

针对不同的场景,应该制定不同的备份策略,一般情况下,备份策略有以下三种:

使用cp命令或者tar命令复制数据库文件,适用于数据量较小的环境。

使用mysqldump命令和二进制日志文件备份,适用于数据量一般的环境。

使用lvm2快照和二进制日志文件备份,适用于数据量较大的环境。

使用xtrabackup工具进行备份,适用于数据量很大的环境。

2 常用命令

2.1 导出数据

导出2019年1月的数据,数据库是ide,表是user,要求不需要导出建库语句,要求使用完成的插入语句,并且合并插入:

[root@localhost ~]# mysqldump -h 127.0.0.1 -P 3306 -uroot -p'123456' --no-create-info --databases ide --tables user \
> --where=" date >= '2019-01-01' and date < '2019-02-01' " --complete-insert --extended-insert > '/data/dump/201901.txt'

2.2 导入数据

[root@localhost ~]# mysql -uroot -p123456 database < db_back.sql

3 导出数据

导出数据一般使用mysqldump命令在Linux系统执行。

3.1 语法

导出全部数据库:

mysqldump [OPTIONS] --all-databases [OPTIONS]

导出指定数据库:

mysqldump [OPTIONS] --databases DB1 [DB2 DB3...] [OPTIONS]

导出指定数据库的指定表:

mysqldump [OPTIONS] --database DB --tables TB [TB2 TB3...] [OPTIONS]

3.2 说明

--user, -u:用户名。登录数据库的用户名,当前用户可省略该配置。
--password, -p:密码。登录数据库的密码。
--host, -h:主机地址。设置数据库所在的主机地址,默认是localhost。
--port, -P:端口号。设置用于连接的端口号。
--protocol:连接协议。设置使用的连接协议,取值有TCP、SOCKET、PIPE、MEMORY四种。
--socket, -S:套接字文件。指定当连接localhost时的套接字文件位置,默认路径是/tmp/mysql.sock。
--all-databases, -A:导出全部数据库。不指定数据库,导出所有数据库。
--databases, -B:导出指定数据库。导出指定的数据库。
--tables:导出指定表。只能针对一个数据库进行导出,并且导出的内容中没有创建数据库的判断语句。
--ignore-table:不导出指定表。指定忽略多个表时,需要重复多次,每个表都要指定数据库和表名。
--all-tablespaces, -Y:导出全部表空间。
--no-tablespaces, -y:不导出表空间信息。
--no-create-db, -n:不添加创建数据库语句。默认添加。
--add-drop-database:添加删除数据库语句。默认不添加。
--no-create-info, -t:不添加创建表语句。默认添加。
--add-drop-table:添加删除表语句。默认添加。
--skip-add-drop-table:取消添加删除表语句。
--no-data, -d:不导出数据只导出表结构。
--routines, -R:导出存储过程以及自定义函数。
--events, -E:导出事件表。
--triggers:导出触发器。
--add-locks:在每个表导出前锁表并在导出后解锁。默认为打开状态。
--skip-add-locks:取消在每个表导出前锁表并在导出后解锁。
--default-character-set:设置默认的字符集。默认是utf8。
--compress, -C:在客户端和服务器之间启用压缩传递所有信息。
--result-file, -r:处理换行。直接输出到指定文件中。该选项应该用在使用回车换行对(\\r\\n)换行的系统上(例如:DOS,Windows)。该选项确保只有一行被使用。
--dump-date:添加导出时间。将导出时间添加到输出文件中。默认为打开状态。
--skip-dump-date:取消添加导出时间。
--where, -w:只导出符合指定条件的记录。如果条件包含命令解释符专用空格或字符,将条件引用起来。
--extended-insert:合并多个插入语句。默认开启。
--skip-extended-insert:取消合并多个插入语句。
--complete-insert, -c:使用完整的插入语句。使用包含列名称的完整插入语句。这么做能提高插入效率,但是可能会受到max_allowed_packet参数的影响而导致插入失败。
--insert-ignore:忽略插入错误。在插入语句中添加忽略错误语句,默认不添加。
--max_allowed_packet:服务器发送和接受的最大包长度。客户端/服务器之间通信的缓存区最大为1GB。
--force, -f:强制插入。在表转储过程中,即使出现SQL错误也继续。
--lock-all-tables, -x:对所有数据备份时,对所有库的所有表加读锁,实现温备。
--lock-tables, -l:对指定的数据库进行备份时,对指定库的所有表加锁,实现温备。
--single-transaction:在备份InnoDB存储引擎的表时,让备份操作基于独立的事务进行,实现热备。该选项的优先级高于“--lock-all-tables”和“--lock-tables”,并且仅能用于InnoDB。

3.3 实例

导出全部数据库:

[root@localhost ~]# mysqldump -h127.0.0.1 -uroot -p --all-databases > db_back.sql

导出指定数据库:

[root@localhost ~]# mysqldump -h127.0.0.1 -uroot -p --databases test demo > db_back.sql

导出指定表:

[root@localhost ~]# mysqldump -h127.0.0.1 -uroot -p --database test --tables demo user > db_back.sql

不导出指定表:

[root@localhost ~]# mysqldump -h127.0.0.1 -uroot -p --all-databases \
> --ignore-table=database.table1 --ignore-table=database.table2 > db_back.sql

设置默认字符集:

[root@localhost ~]# mysqldump -h127.0.0.1 -uroot -p --all-databases --default-character-set=latin1 > db_back.sql

处理换行:

[root@localhost ~]# mysqldump -h127.0.0.1 -uroot -p --all-databases --result-file=/tmp/mysqldump_result_file.txt

只导出符合指定条件的记录:

[root@localhost ~]# mysqldump -h127.0.0.1 -uroot -p --all-databases --where=" id=1 limit 10"

使用完整的插入语句:

[root@localhost ~]# mysqldump -h127.0.0.1 -uroot -p --all-databases --complete-insert

4 导入数据

导入数据有两种方式,一种是在Linux系统使用<命令导入,另一种是在MySQL数据库使用source命令导入。

4.1 语法

在Linux系统使用<命令:

mysql -u用户名 -p密码 数据库名 < 文件路径

在MySQL数据库使用source命令:

source 文件路径

4.2 实例

在Linux系统使用<命令:

[root@localhost ~]# mysql -uroot -p123456 database < db_back.sql

在MySQL数据库使用source命令:

mysql> source /home/user/data/db_back.sql

4.3 效率比较

在文件较小的情况下,source速度比mysql高。在导入大的文件时,建议使用mysql命令。

4.4 优化导入速度

对于百M级以上文件,根据MySQL官方建议,有几个措施可以极大提高导入的速度。

4.4.1 修改配置文件

配置如下:

[mysqld]
bulk_insert_buffer_size=2G;
innodb_log_buffer_size=2G;
innodb_autoinc_lock_mode=2;

说明:

对于MyISAM,调整系统参数:bulk_insert_buffer_size。改为至少单个文件大小的2倍以上。

对于InnoDB,调整系统参数:innodb_log_buffer_size。改为至少单个文件大小的2倍以上,导入完成后可以改回默认的8M,注意不是innodb_buffer_pool_size。

有自增列的,设置:innodb_autoinc_lock_mode的值为2。

4.4.2 删除索引

除主键外,删除其他索引,导入完成后重建索引。

4.4.3 执行批量脚本

bash脚本内容:

for SQL in *.sql;
do
? ? echo $SQL;
? ? sed -i '1i\SET autocommit=0;\nSET unique_checks=0;\nSET foreign_key_checks=0;' $SQL
? ? sed -i '$a\COMMIT;\nSET autocommit=1;\nSET unique_checks=1;\nSET foreign_key_checks=1;' $SQL
done

说明:

autocommit=0:关闭自动提交。
unique_checks=0:关闭唯一索引检查。
foreign_key_checks=0:关闭外键检查。

  大数据 最新文章
实现Kafka至少消费一次
亚马逊云科技:还在苦于ETL?Zero ETL的时代
初探MapReduce
【SpringBoot框架篇】32.基于注解+redis实现
Elasticsearch:如何减少 Elasticsearch 集
Go redis操作
Redis面试题
专题五 Redis高并发场景
基于GBase8s和Calcite的多数据源查询
Redis——底层数据结构原理
上一篇文章      下一篇文章      查看所有文章
加:2021-12-18 16:03:48  更:2021-12-18 16:05:56 
 
开发: C++知识库 Java知识库 JavaScript Python PHP知识库 人工智能 区块链 大数据 移动开发 嵌入式 开发工具 数据结构与算法 开发测试 游戏开发 网络协议 系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑 笔记本 显卡 显示器 固态硬盘 硬盘 耳机 手机 iphone vivo oppo 小米 华为 单反 装机 图拉丁

360图书馆 购物 三丰科技 阅读网 日历 万年历 2024年11日历 -2024/11/24 12:43:05-

图片自动播放器
↓图片自动播放器↓
TxT小说阅读器
↓语音阅读,小说下载,古典文学↓
一键清除垃圾
↓轻轻一点,清除系统垃圾↓
图片批量下载器
↓批量下载图片,美女图库↓
  网站联系: qq:121756557 email:121756557@qq.com  IT数码