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)
-> );
![](https://i-blog.csdnimg.cn/blog_migrate/91d9e43fa2cdba5f50c57c9db239048e.png)
为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);
备份数据库test到/backup目录
[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