防止由于机械故障以及人为误操作带来的数据丢失,例如将数据库文件保存在了其它地方。
备份方式
从生成备份的内容区分
-
物理备份
直接复制数据库文件,适用于大型数据库环境,不受存储引擎的限制,但不能恢复到不同的 MySQL版本。
物理备份的备份方式
-
完全备份
每次对数据进行完整的备份,保存的是当前数据库中所有的数据,是差异备份与增量备份的基础。 优点:备份与恢复操作简单方便,恢复时一次恢复到位,恢复速度快 缺点:占用空间大,备份速度慢
-
增量备份
每次备份上一次备份到现在产生的新数据 特点:因而备份的数据量小,占用空间小,备份速度快。但恢复时,需要从上一次的完整备份起按备份时间顺序,逐个备份版本进行恢复,恢复时间长,如中间某次的备份数据损坏,将导致数据的丢失。
-
差异备份
只备份跟完整备份不一样的 特点:差异备份其实就是增量备份的中特殊操作,它体现在差异备份只能以一个完整备份为基础,占用空间比增量备份大,比完整备份小,恢复时仅需要恢复第一个完整版本和最后一次的差异版本,恢复速度介于完整备份和增量备份之间。
-
逻辑备份
备份的是建表、建库、插入等操作所执行SQL语句(DDL DML DCL),适用于中小型数据库,效率相对较低。
总结
物理备份 | 逻辑备份 | |
---|---|---|
备份方式 | 备份数据库数据(表) | 备份数据库建表、建库、插入sql语句 |
优点 | 恢复速度比较快 | 备份文件相对较小,只备份表中的数据与结构 |
缺点 | 备份文件相对较大(备份表空间,包含数据与索引) | 恢复速度较慢(需要重建索引,存储过程等) |
对业务影响 | I/O负载加大 | I/O负载加大 |
代表工具 | ibbackup、xtrabackup开源,mysqlbackup | mysqldump |
工作中使用需要使用哪种需要根据业务和数据量来决定,一般情况下,数据量特别巨大时会采用物理备份,数据量中小时采取逻辑备份
从恢复数据操作区分
冷备份 | 恢复备份时,其他人禁止访问数据库 |
温备份 | 恢复备份时,其他人可对数据库进行查询 |
热备份 | 恢复数据时,其他人可对数据库进行所有操作 |
总结 | 工作中使用什么方式恢复数据是由业务决定的,当恢复的业务非常重要需要使用冷备份,当恢复的业务相对不重要选择温备份或热备份 比如:银行ATM程序恢复选择冷备份 图书馆资料管理程序选择热备份 |
第一个备份——select into 少量数据备份
sql中的一个基础命令,可以完成数据备份,但是由于十分简陋,只能适用于临时的数据备份
1.准备数据
mysql> create database back; #创建back数据库
mysql> use back; #切换到back
mysql> create table t_user(id int primary key,name varchar(20)); #创建t_user表
mysql> insert into t_user values(1,'zhangsan'); #添加数据
mysql> insert into t_user values(2,'lisi');
mysql> insert into t_user values(3,'wangwu');
2.进行备份
#语法 select 语句 into outfile '目标文件'
mysql> select * from t_user into outfile '/tmp/user.txt'
#将select的查询结果数据储存到/tmp/user.txt
3.赋予权限
当报错如上时,表示msyql没有使用select into 备份权限
1.查看当前权限
mysql> show variables like '%secure%';
2.进入/etc/my.cnf
secure-file-priv=/tmp
3.然后重启mysql,查看信息
4.重新执行命令,并查看备份文件
5.删除数据
6.恢复数据
load data infile '/tmp/user.txt' into table t_user;
物理备份工具-Xtrabackup 大量数据备份
Xtrabackup是开源免费的支持MySQL 数据库热备份的软件,在 Xtrabackup 包中主要有 Xtrabackup 和 innobackupex 两个工具。其中 Xtrabackup 只能备份 InnoDB 和 XtraDB 两种引擎; innobackupex则是封装了Xtrabackup,同时增加了备份MyISAM引擎的功能。
安装Xtrabackup
下载并解压
[root@localhost tmp]# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.9/binary/redhat/6/x86_64/Percona-XtraBackup-2.4.9-ra467167cdd4-el6-x86_64-bundle.tar
[root@localhost tmp]# tar xvf Percona-XtraBackup-2.4.9-ra467167cdd4-el6-x86_64-bundle.tar
安装
[root@localhost tmp]# yum install -y percona-xtrabackup-24-2.4.9-1.el6.x86_64.rpm
完整备份
创建备份
创建备份目录
[root@mysql-server ~]# mkdir /xtrabackup/full -p
执行备份命令,将当前mysql所有数据进行备份
[root@mysql-server ~]# innobackupex --user=root --password='111111' -S /tmp/mysql.sock /xtrabackup/full--user: 数据库登陆用户名
--password: 密码
-S :数据库套接文件地址,在/etc/my.cnf的socket中获取
恢复备份
1.关闭数据库
2.删除数据库所有数据,在/etc/my.cnf中的datadir获取储存位置
#注意一旦删除所有数据,mysql数据库就理论上被损坏了,无法再启动,必须恢复数据后才能使用
[root@mysql-server ~]# rm -rf /opt/liuyh/data/mysql/*3.重演数据,也就是在恢复数据之前先检查一下备份文件中是否有问题
[root@mysql-server ~]# innobackupex --apply-log /xtrabackup/full/2021-11-17_00-37-48
时间改成现在
4.恢复数据
[root@mysql-server ~]# innobackupex --copy-back /xtrabackup/full/2021-11-17_00-37-485.数据恢复后,到mysql指定的数据储存位置查看是否有数据文件
6.设置权限,注意恢复后的文件需要将权限设置为mysql数据库的拥有者可执行权限
[root@mysql-server ~]#chown -R mysql:mysql /opt/liuyh/data/mysql/*7.启动数据库,查看数据
出错是因为没有权限,需要重新改回(将该文件用户权限改了)
增量备份
创建备份
1.先创建完整备份
2.修改数据库数据
3.创建增量备份
[root@mysql-server ~]# innobackupex --user=root --password=111111 -S /tmp/mysql.sock --incremental /xtrabackup/full --incremental-basedir=/xtrabackup/full/2023-11-17_15-57-12--incremental:指定增量备份生成位置
--incremental-basedir:指定以哪个备份为基础做增量备份,注意:所选备份应为一个完整备份或增量备份
恢复备份
1.关闭数据库
2.删除数据库所有数据,在/etc/my.cnf中的datadir获取储存位置
#注意一旦删除所有数据,mysql数据库就理论上被损坏了,无法再启动,必须恢复数据后才能使用
[root@mysql-server ~]# rm -rf /opt/liuyh/data/mysql/*3.重演数据,并整合数据
#重演完整备份
[root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/full/2021-11-17_15-57-12
#按生成顺序将第一个增量备份整合到完整备份中
[root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/full/2021-11-17_15-57-12 --incremental-dir=/xtrabackup/full/2021-11-17_16-01-25
#按生成顺序将第二个增量备份整合到完整备份中
[root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/full/2021-11-17_15-57-12 --incremental-dir=/xtrabackup/full/2021-11-17_16-02-014.恢复数据,此时所有数据都已经保存在完整备份中,只需恢复完整备份即可
[root@mysql-server ~]# innobackupex --copy-back /xtrabackup/full/2021-11-17_15-57-125.设置权限,注意恢复后的文件需要将权限设置为mysql数据库的拥有者可执行权限
[root@mysql-server ~]#chown -R mysql:mysql /opt/liuyh/data/mysql/*
ll查看
6.启动数据库,查看数据
差异备份
在上面我们已经知道差异备份其实就是增量备份的一种,它的操作与增量备份一致,区别在于生成差异备份时只能以一个完整备份为基础
逻辑备份工具-mysqldump
mysqldump 是 MySQL 自带的逻辑备份工具。可以保证数据的一致性和服务的可用性。
本身为客户端工具:
远程备份语法: # mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql
本地备份语法: # mysqldump -u用户名 -p密码 数据库名 > 备份文件.sql
常用关键字
-A, --all-databases #备份所有库
-B, --databases #备份多个数据库
-F, --flush-logs #备份之前刷新binlog日志
--default-character-set #指定导出数据时采用何种字符集,如果数据表不是采用默认的latin1字符集的话,那么导出时必须指定该选项,否则再次导入数据后将产生乱码问题。
--no-data,-d #不导出任何数据,只导出数据库表结构。
--lock-tables #备份前,锁定所有数据库表
--single-transaction #保证数据的一致性和服务的可用性
-f, --force #即使在一个表导出期间得到一个SQL错误,继续。
着重强调:
使用 mysqldump 备份数据库时避免锁表(只能一个人用锁表):
对一个正在运行的数据库进行备份请慎重,尽量不要在数据库开放服务时备份,如果一定要在服务运行期间备份,可以选择添加 --single-transaction选项,类似执行: mysqldump --single-transaction -u root -p123456 dbname > mysql.sql
备份所有数据
1.创建备份目录
[root@mysql-server ~]# mkdir /tmp/mysqldumpdata2.备份当前数据库所有数据
# cd /opt/chengpf/mysql/bin/
[root@localhost bin]# ./mysqldump -uroot -p111111 -A > /tmp/mysqldumpdata/all.sql3.查看是否生成文件
恢复所有数据
[root@localhost mysqldumpdata]# mysql -uroot -p111111 < /tmp/mysqldumpdata/all.sql
注意mysqldump只能恢复表级内容,不能恢复库,所以当数据库失踪时需要先手动创建数据库再执行恢复命令
备份和恢复指定库内容
备份指定库
[root@mysql-server ~]# mysqldump -uroot -p111111 -B back > /tmp/mysqldumpdata/back.sql-B:指定数据库,多个库之间用空格区分
恢复指定库
[root@mysql-server ~]# mysql -uroot -p111111 -B mysql </tmp/mysqldumpdata/mysql.sql注意:恢复时只能指定一个库操作,若有多个则多次执行
备份和恢复指定表内容
备份指定表
[root@mysql-server ~]# mysqldump -uroot -p111111 -B back --tables t_user > /tmp/mysqldumpdata/back.sql--tables:指定表
恢复指定表
[root@mysql-server ~]# mysql -uroot -p111111 -B back </tmp/mysqldumpdata/user.sql