MySQL备份

.懒懒奶羊.

已于 2023-03-24 23:33:41 修改

阅读量125

点赞数

分类专栏：云原生运维高级文章标签： mysql 数据库 Powered by 金山文档

于 2023-03-24 23:25:38 首次发布

本文链接：https://blog.csdn.net/weixin_56600233/article/details/129760087

版权

云原生运维高级专栏收录该内容

5 篇文章 0 订阅

订阅专栏

1.备份类型

热备份、温备份、冷备份 （根据服务器状态）：
    热备份：读、写不受影响；
    温备份：仅可以执行读操作；
    冷备份：离线备份；读、写操作均中止；
物理备份与逻辑备份 （从对象来分）
    物理备份：复制数据文件；
    逻辑备份：将数据导出至文本文件中；
完全备份、增量备份、差异备份 （从数据收集来分）
    完全备份：备份全部数据；
    增量备份：仅备份上次完全备份或增量备份以后变化的数据；
    差异备份：仅备份上次完全备份以来变化的数据；

逻辑备份的优缺点

逻辑备份的优点：
  在备份速度上两种备份要取决于不同的存储引擎；
  物理备份的还原速度非常快。但是物理备份的最小粒度只能做到表；
  逻辑备份保存的结构通常都是纯ASCII的，所以我们可以使用文本处理工具来处理；
  逻辑备份有非常强的兼容性，而物理备份则对版本要求非常高；
  逻辑备份也对保持数据的安全性有保证。
逻辑备份的缺点：
  逻辑备份要对RDBMS产生额外的压力，而裸备份无压力；
  逻辑备份的结果可能要比源文件更大，所以很多人都对备份的内容进行压缩；
  逻辑备份可能会丢失浮点数的精度信息。

2.MySQL的备份工具

MySQL备份一般采用自带的备份工具:

mysqldump 逻辑备份工具: 支持所有引擎，MyISAM引擎是温备，InnoDB引擎是热备，备份速度中

速，还原速度非常非常慢，但是在实现还原的时候，具有很大的操作余地。具有很好的弹性。

mysqlhotcopy 物理备份工具: 但只支持MyISAM引擎，基本上属于冷备的范畴，物理备份，速度比较

快。

3.备份策略

策略一：直接拷贝数据库文件

文件系统备份工具 cp（适合小型数据库，是最可靠的）：

停止MySQL服务器。

直接复制整个数据库目录。（注意：使用这种方法最好还原到相同版本服务器中，不同版本可能不兼容。）

目标服务器上还原。

目标服务器数据库目录授权，重启服务器测试。

策略二：mysqldump备份数据库

（完全备份+增加备份，速度相对较慢，适合中小型数据库）<MyISAM是温备份，InnoDB是热备份>

mysqldump 是采用SQL级别的备份机制，它将数据表导成 SQL 脚本文件，在不同的 MySQL 版本之间

升级时相对比较合适，这也是最常用的备份方法。

示例一：备份恢复单个数据库

备份：mydqldump -u 用户 –p’密码’ –default-character-set=Iatin1 数据库名 [|gzip] > 备
     份文件名

示例：mysqldump -uroot -p'123456' --default-character-set=utf8 -B school >
/tmp/school.$(date +%F).sql

注意：-B 作用：创建数据库和切换到数据库，恢复时不用创建数据库和删表。备份多个库，-B 数据库1 数
     据库2 ...

恢复：
1）MySQL中用source命令
2）mysql命令恢复

示例二：备份恢复单个表

备份：mysqldump -u 用户名 -p 数据库名 表名 > 备份的文件名
备份多个表：mysqldump -u 用户名 -p 数据库名 表名1 表名2 > 备份的文件名

示例：mysqldump -uroot -p school Books > /tmp/Books.$(date +%F).sql

示例三：备份数据结构

-d只备份库结构，不包含数据内容

策略三：mydumper备份数据库

Mydumper是一个针对MySQL和Drizzle的高性能多线程备份和恢复工具。

Mydumper主要特性：

轻量级C语言写的

执行速度比mysqldump快10倍

事务性和非事务性表一致的快照(适用于0.2.2以上版本)

快速的文件压缩

支持导出binlog

多线程恢复(适用于0.2.1以上版本)

以守护进程的工作方式，定时快照和连续二进制日志(适用于0.5.0以上版本)

开源 (GNU GPLv3)

Mydumper安装

# yum -y install glib2-devel mysql-devel zlib-devel pcre-devel
# tar xvf mydumper-0.5.1.tar.gz
# cd mydumper-0.5.1/
# cmake .
# make
# make install

mydumper输出文件
metadata:元数据 记录备份开始和结束时间，以及binlog日志文件位置。
table data:每个表一个文件
table schemas:表结构文件
binary logs: 启用--binlogs选项后，二进制文件存放在binlog_snapshot目录下
daemon mode:在这个模式下，有五个目录0，1，binlogs，binlog_snapshot，last_dump。
备份目录是0和1，间隔备份，如果mydumper因某种原因失败而仍然有一个好的快照，
当快照完成后，last_dump指向该备份。

mydumper备份和恢复示例：

备份：
[root@localhost ~]# mydumper -h localhost -u root -p 888 -t 6 -S
/tmp/mysql.sock -B school -o /mysqlbackup/
恢复：
[root@localhost ~]# myloader -h localhost -u root -p 888 -S /tmp/mysql.sock
-d /mysqlbackup/ -o -B school

策略四：lvm快照

lvm快照从物理角度实现几乎热备的完全备份，配合二进制日志备份实现增量备份，速度快适合比较烦忙

的数据库。

前提：
   数据文件要在逻辑卷上；
   此逻辑卷所在卷组必须有足够空间使用快照卷；
   数据文件和事务日志要在同一个逻辑卷上；

前提：MySQL数据lv和将要创建的快照要在同一vg，vg要有足够的空间存储

优点：
   几乎是热备（创建快照前把表上锁，创建完毕后立即释放）
   支持所有的存储引擎
   备份速度快
   无需使用昂贵的商业软件（操作系统级别的）
缺点：
   可能需要部门协调（使用操作系统级别的命令，DBA一般没有权限）
   无法预计服务停止时间
   数据如果分布在多个卷上比较麻烦

操作流程：
1> 锁表 flush table with read lock
2> 查看position号并记录，便于后期恢复 show master status
3> 创建snapshot快照 create snapshop
4> 解表 unlock tables
5> 挂载snapshot
6> 拷贝snapshot数据，进行备份。备份整个数据库之前，要关闭mysql服务（保护ibdata1文件）
7> 卸载
8> 移除快照

策略五：xtrabackup 备份数据库

xtrabackup 备份数据库，实现完全热备份与增量热备份（MyISAM是温备份，InnoDB是热备份）

Xtrabackup是一个对InnoDB做数据备份的工具，支持在线热备份（备份时不影响数据读写），是商业备份工具InnoDB

Hotbackup的一个很好的替代品。

Xtrabackup有两个主要的工具：xtrabackup、innobackupex

xtrabackup 只能备份InnoDB和XtraDB两种数据表，而不能备份MyISAM数据表。

4.备份练习

创建student和score表

#创建数据库
mysql> create database test;
mysql> use test;
Database changed

#创建student和score表
mysql> create table student( 
    -> id int(10) not null unique primary key,
    -> name varchar(20) not null,
    -> sex varchar(4),
    -> birth year,
    -> department varchar(20),
    -> address varchar(50)
    -> );
mysql> create table score(
    -> id int(10) not null unique primary key auto_increment,
    -> stu_id int(10) not null,
    -> c_name varchar(20),
    -> grade int(10)
    -> );

为student表和score表增加记录

#为student表中添加数据
mysql> insert into student(id,name,sex,birth,department,address) values (901,'张老大','男',1985,'计算机系','北京市海淀区');
mysql> insert into student(id,name,sex,birth,department,address) values (902,'张老二', '男',1986,'中文系', '北京市昌平区');
mysql> insert into student(id,name,sex,birth,department,address) values (903,'张三', '女',1990,'中文系', '湖南省永州市');
mysql> insert into student(id,name,sex,birth,department,address) values (904,'李四', '男',1990,'英语系', '辽宁省阜新市');
mysql> insert into student(id,name,sex,birth,department,address) values (905,'王五', '女',1991,'英语系', '福建省厦门市');
mysql> insert into student(id,name,sex,birth,department,address) values (906,'王六', '男',1988,'计算机系', '湖南省衡阳市');

#为score表中添加数据
mysql> insert into score(id,stu_id,c_name,grade) values (NULL,901, '计算机',98)；
mysql> insert into score(id,stu_id,c_name,grade) values (NULL,901, '英语', 80);
mysql> insert into score(id,stu_id,c_name,grade) values (NULL,902, '计算机',65);
mysql> insert into score(id,stu_id,c_name,grade) values (NULL,902, '中文',88);
mysql> insert into score(id,stu_id,c_name,grade) values (NULL,903, '中文',95);
mysql> insert into score(id,stu_id,c_name,grade) values (NULL,904, '计算机',70);
mysql> insert into score(id,stu_id,c_name,grade) values (NULL,904, '英语',92);
mysql> insert into score(id,stu_id,c_name,grade) values (NULL,905, '英语',94);
mysql> insert into score(id,stu_id,c_name,grade) values (NULL,906, '计算机',90);

[root@localhost mysql]# mysqldump -uroot -p'Chen1110!' test > backup/test.sql

备份MySQL数据库为带删除表的格式，能够让该备份覆盖已有数据库而不需要手动删除原有数据库

[root@localhost mysql]# mysqldump -uroot -p'Chen1110!' -B test > backup/test.sql

直接将MySQL数据库压缩备份

[root@localhost mysql]# mysqldump -uroot -p'Chen1110!' test > backup/test.sql

备份MySQL数据库某个(些)表。此例备份student表

[root@localhost mysql]# mysqldump -uroot -p'Chen1110!' test student > backup/student.sql

同时备份多个MySQL数据库（其他数据库素材自行准备）

[root@localhost mysql]# mysqldump -uroot -p'Chen1110!' test sys > backup/test.sql sys.sql

仅仅备份数据库结构

[root@localhost mysql]# mysqldump -uroot -p'Chen1110!' -d test > /backup/test1.sql

备份服务器上所有数据库

[root@localhost mysql]# mysqldump -uroot -p'Chen1110!' --all-database > /backup/all_test.sql

还原MySQL数据库

mysql> source /mysql/backup/all_test.sql

还原压缩的MySQL数据库

[root@localhost backup]# zcat school.sql.gz | mysql -uroot -p'Chen1110!'

使用mydumper备份数据库

[root@localhost backup]# mydumper -uroot -p'Chen1110!!' -B test -S /tmp/mysql.sock -o /mysql/backup/mysql_mydumper.sql

使用mydumper恢复数据库

[root@localhost backup]# myloader -uroot -p'Chen1110!!' -B test -S /tmp/mysql.sock -d /mysql/backup/mysql_mydumper.sql

.懒懒奶羊.

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL备份

MySQL备份学习
复制链接

扫一扫

专栏目录