• 生活的道路一旦选定,就要勇敢地走到底,决不回头。——左拉
  • 坚强的信心,能使平凡的人做出惊人的事业。——马尔顿
  • 人不可有傲气,但不可无傲骨。 --徐悲鸿
  • 古之立大志者,不惟有超世之才,亦必有坚韧不拔之志。 --苏轼
  • 时间像海绵里的水,只要你愿意挤,总还是有的。 --鲁迅

MySQL数据备份与恢复

MySQL zkinogg 2年前 (2020-08-03) 185次浏览 0个评论

MySQL数据备份与恢复

一、mysql数据备份

#mysql客户端
mysql
mysqladmin
mysqldump

1.备份的原因

1.备份就是为了恢复。
2.尽量减少数据的丢失(公司的损失)

2.备份的类型

1.冷备:停库,停服务,备份
2.热备:不停库,不停服务,备份
3.温备:不停服务,锁表(阻止数据写入),备份

#冷备份:
这些备份在用户不能访问数据时进行,因此无法读取或修改数据。这些脱机备份会阻止执行任何使用数据的活动。这些类型的备份不会干扰正常运行的系统的性能。但是,对于某些应用程序,会无法接受必须在一段较长的时间里锁定或完全阻止用户访问数据。

#温备份:
这些备份在读取数据时进行,但在多数情况下,在进行备份时不能修改数据本身。这种中途备份类型的优点是不必完全锁定最终用户。但是,其不足之处在于无法在进行备份时修改数据集,这可能使这种类型的备份不适用于某些应用程序。在备份过程中无法修改数据可能产生性能问题。

#热备份:
这些动态备份在读取或修改数据的过程中进行,很少中断或者不中断传输或处理数据的功能。使用热备份时,系统仍可供读取和修改数据的操作访问。

3.备份的策略

1.全备:全部数据备份
2.增备:针对于上一次备份,将新数据备份
3.差异备份:基于上一次全备进行新数据的备份

4.备份方式

1)逻辑备份

#基于SQL语句的备份
1.binlog
2.into outfile
    [root@db03 data]# vim /etc/my.cnf
    [mysqld]
    secure-file-priv=/tmp
    mysql> select * from world.city into outfile '/tmp/world_city.data';

3.mysqldump
4.replication

2)物理备份

#备份底层的数据文件
1.备份整个data数据目录
2.xtrabackup

二、逻辑备份mysqldump客户端

1.常用参数

1.不加参数:用于备份单个表
    1)备份库
    [root@db02 ~]# mysqldump ku > /tmp/ku.sql
    2)备份表
    [root@db02 ~]# mysqldump ku test > /tmp/ku.sql
    3)备份多个表
    [root@db02 ~]# mysqldump ku test test2 test3 > /tmp/ku.sql
    #注意:当不加参数时命令后面跟的是库名,库的后面全都是必须是库下面的表名

2.连接服务端参数(基本参数):-u -p -h -P -S

3.-A, --all-databases:全库备份

4.-B:指定库备份
[root@db01 ~]# mysqldump -uroot -p123 -B db1 > /backup/db1.sql
[root@db01 ~]# mysqldump -uroot -p123 -B db1 db2 > /backup/db1_db2.sql

5.-F:flush logs在备份时自动刷新binlog(不怎么常用)
[root@db01 backup]# mysqldump -uroot -p123 -A -F > /backup/full_2.sql

6.--master-data=2:备份时加入change master语句0没有1不注释2注释
    1)等于2:记录binlog信息,并注释(日常备份)
    2)等于1:记录binlog信息,不注释(扩展从库)
    0)等于0:不记录binlog信息
    [root@db01 backup]# mysqldump -uroot -p123 --master-data=2 >/backup/full.sql

7.--single-transaction:快照备份

8.-d:仅表结构
9.-t:仅数据

10.-R, --routines:备份存储过程和函数数据
11.--triggers:备份触发器数据
12.gzip:压缩备份
    #备份成压缩包
    [root@db01 ~]# mysqldump -uroot -p123 -A | gzip > /backup/full.sql.gz
    #恢复压缩包中的数据
    [root@db03 ~]# zcat /tmp/full.sql.gz | mysql -uroot -p123

#完整的备份命令:
mysqldump -uroot -p123 -A -R --triggers --master-data=2 –-single-transaction > /tmp/full.sql

2.注意:

1)mysqldump在备份和恢复时都需要MySQL实例启动为前提
2)一般数据量级100G以内,大约15-30分钟可以恢复
3)mysqldump是以覆盖的形式恢复数据的

3.模拟数据丢失备份恢复

模拟数据误删除并恢复
思路:
操作步骤
1.停库,避免二次伤害 
2.创建新库 
3.倒入前一天的全备 
4.通过binlog找到前一天23:00到第二天10点之间的数据 
5.导入找到的新数据 
6.恢复业务 
a.直接使用临时库顶替原生产库,前端应用割接到新库(数据量特别大的时候) 
b.将误删除的表单独导出,然后导入到原生产环境(数据量小的时候) 
# 1.创建库linux9 
mysql> create database linux9; Query OK, 1 row affected (0.00 sec)
# 2.进到linux9库下 
mysql> use linux9; Database changed 
# 3.创建student表 
mysql> create table student(id int,name varchar(12) not null, gender enum('m','f') default 'm', age tinyint); 
Query OK, 0 rows affected (0.13 sec) 
# 4查看表结构 
mysql> desc student -> ;
+--------+---------------+------+-----+---------+-------+ 
| Field | Type | Null | Key | Default | Extra | 
+--------+---------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | | | name | varchar(12) | NO | | NULL | | | gender | enum('m','f') | YES | | m | | | age | tinyint(4) | YES | | NULL | |
+--------+---------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) 
# 5.插入数据 
mysql> insert into student(id,name,gender,age) values(1,'zx','m',18); 
Query OK, 1 row affected (0.05 sec) 
mysql> insert into student(id,name,gender,age) values(2,'nl','m',18);
Query OK, 1 row affected (0.00 sec)
mysql> insert into student(id,name,gender,age) values(3,'zjy','m',18); 
Query OK, 1 row affected (0.00 sec) 
mysql> insert into student(id,name,gender,age) values(4,'ljp','m',18); Query OK, 1 row affected (0.00 sec) 
# 6.查看插入的数据 
mysql> select * from student; 
+------+------+--------+------+ 
| id | name | gender | age |
+------+------+--------+------+ 
| 1 | zx | m | 18 | | 2 | nl | m | 18 | | 3 | zjy | m | 18 | | 4 | ljp | m | 18 |
+------+------+--------+------+ 4 rows in set (0.00 sec) 
# 7.查看当前所在库 
mysql> select database (); 
+-------------+ 
| database () |
+-------------+
| linux9 | 
+-------------+
1 row in set (0.00 sec) 
mysql> Ctrl-C -- exit! Aborted 
# 8.导出老数据 
[root@db01 ~]# mysqldump -uroot -p123 -B linux9 -R --triggers --master-data=2 -- single-transaction > /tmp/linux.sql 
Warning: Using a password on the command line interface can be insecure. 
# 9.将老数据发送到52的tmp下 
[root@db01 ~]# scp /tmp/linux.sql 172.16.1.52:/tmp/ 
# 10.db01登录mysql模拟全备后的新数据 
[root@db01 ~]# mysql -uroot -p
Enter password: 
# 11.进到linux9库下 
mysql> use linux9 
Database changed 
# 12.插入新数据 
mysql> insert student(id,name,age) values(5,'aaa',18),(6,'bbb',18),(7,'ccc',18);
Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0
# 13.删除linux9库 
mysql> drop database linux9 -> ; 
Query OK, 1 row affected (0.00 sec) 
# 14.查看现在的库 mysql> show databases;
+--------------------+ 
| Database |
+--------------------+ 
| information_schema |
| dump |
| mysql |
| performance_schema |
| test | 
+--------------------+ 5 rows in set (0.00 sec) 
# 15.停掉mysqld 
[root@db01 ~]# systemctl stop mysqld 
# 16.查看导出的旧数据binlog起始位置点
[root@db01 ~]# head -22 /tmp/linux.sql | tail -1 -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=1711; 
# 17.进到data目录下,将bin-log日志导进1.txt找到drop前的位置点 
[root@db01 ~]# cd /usr/local/mysql/data/ 
[root@db01 data]# mysqlbinlog mysql-bin.000004 > 1.txt 
[root@db01 data]# vim 1.txt 
# at 1975 #200722 23:00:58 server id 1 end_log_pos 2073 CRC32 0x777d37a3 Query thread_id=7 exec_time=0 error_code=0 SET TIMESTAMP=1595430058/*!*/; SET @@session.pseudo_thread_id=7/*!*/; drop database linux9

# 18。确认两个位置点后。通过log-bin导出新写入的数据文件 
[root@db01 data]# mysqlbinlog -d linux9 --start-position=1711 --stop- position=1975 mysql-bin.000004 > /tmp/new.sql 
# 19.将新数据传送到db02 
[root@db01 data]# scp /tmp/new.sql 172.16.1.52:/tmp/ root@172.16.1.52's 
password: new.sql 100% 1693 1.8MB/s 00:00 
# 20.db02查看传过来的两个数据并导入 
[root@db02 ~]# mysql -uroot -p123 < /tmp/linux.sql 
mysql> show databases; 
+--------------------+ 
| Database |
+--------------------+ 
| information_schema | 
| dump | 
| linux9 | 
| mysql | 
| performance_schema |
| test |
+--------------------+
mysql> use linux9 
mysql> select * from student  ;
+------+------+--------+------+ 
| id | name | gender | age | 
+------+------+--------+------+
| 1 | zx | m | 18 | | 2 | nl | m | 18 | | 3 | zjy | m | 18 | | 4 | ljp | m | 18 | 
+------+------+--------+------+ 4 rows in set (0.00 sec) 
[root@db02 ~]# mysql -uroot -p123 < /tmp/new.sql 
[root@db02 ~]# mysql -uroot -p123 
mysql> use linux9 
mysql> select * from student;
+------+------+--------+------+ 
| id | name | gender | age | 
+------+------+--------+------+ 
| 1 | zx | m | 18 | | 2 | nl | m | 18 | | 3 | zjy | m | 18 | | 4 | ljp | m | 18 | | 5 | aaa | m | 18 | | 6 | bbb | m | 18 | | 7 | ccc | m | 18 | 
+------+------+--------+------+ 
[root@db02 ~]# mysqldump -uroot -p123 linux9 > /tmp/linux9.sql 
[root@db02 ~]# scp /tmp/linux9.sql 172.16.1.51:/tmp/ 
[root@db01 ~]# systemctl start mysqld 
mysql> show databases;
+--------------------+ 
| Database | 
+--------------------+ 
| information_schema |
| dump |
| mysql |
| performance_schema | 
| test |
+--------------------+ 
mysql> create database linux9; 
Query OK, 1 row affected (0.00 sec) 
mysql> use linux9; 
Database changed 
mysql> source /tmp/linux9.sql 
Query OK, 0 rows affected (0.00 sec) 
Query OK, 0 rows affected (0.00 sec) 
Query OK, 0 rows affected (0.00 sec) 
Query OK, 0 rows affected (0.00 sec) 
Query OK, 0 rows affected (0.00 sec) 
Query OK, 0 rows affected (0.00 sec) 
Query OK, 0 rows affected (0.00 sec) 
Query OK, 0 rows affected (0.00 sec) 
Query OK, 0 rows affected (0.00 sec) 
Query OK, 0 rows affected (0.00 sec) 
Query OK, 0 rows affected (0.00 sec) 
Query OK, 0 rows affected (0.00 sec) 
Query OK, 0 rows affected (0.00 sec) 
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec) 
Query OK, 0 rows affected (0.00 sec) 
Query OK, 0 rows affected (0.00 sec) 
Query OK, 7 rows affected (0.00 sec) Records: 7 Duplicates: 0 Warnings: 0 
Query OK, 0 rows affected (0.00 sec) 
Query OK, 0 rows affected (0.00 sec) 
Query OK, 0 rows affected (0.00 sec) 
Query OK, 0 rows affected (0.00 sec) 
Query OK, 0 rows affected (0.00 sec) 
Query OK, 0 rows affected (0.00 sec) 
Query OK, 0 rows affected (0.00 sec) 
Query OK, 0 rows affected (0.00 sec) 
Query OK, 0 rows affected (0.00 sec) 
Query OK, 0 rows affected (0.00 sec) 
mysql> select * from student;
+------+------+--------+------+ 
| id | name | gender | age |
+------+------+--------+------+ 
| 1 | zx | m | 18 | | 2 | nl | m | 18 | | 3 | zjy | m | 18 | | 4 | ljp | m | 18 | | 5 | aaa | m | 18 | | 6 | bbb | m | 18 | | 7 | ccc | m | 18 | 
+------+------+--------+------+ 7 rows in set (0.00 sec) 
## 数据恢复~!

三、物理备份Xtrabackup

1.安装

#上传文件包
[root@db03 ~]# rz percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

#下载epel源
wget -O /etc/yum.repos.d/epel.repo  https://mirrors.aliyun.com/repo/epel-6.repo
#安装依赖
yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL
#下载Xtrabackup
wget httpss://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

#安装
[root@db03 ~]# yum localinstall -y percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm
#安装好后的命令
[root@db03 ~]# xtrabackup
[root@db03 ~]# innobackupex

2.Xtrabackup备份

1)对于非innodb表(比如myisam)是直接锁表cp数据文件,属于一种温备。
2)对于innodb的表(支持事务),不锁表,cp数据页最终以数据文件方式保存下来,并且把redo和undo一并备走,属于热备方式。
3)备份时读取配置文件/etc/my.cnf(如果使用Xtrabackup备份,必须要配置datadir)

3.Xtrabackup全量备份

1)准备备份目录

[root@db03 ~]# mkdir /backup

2)备份(全备)

[root@db03 ~]# innobackupex --user=root --password=123 /backup/full

#去掉时间戳进行备份
[root@db03 ~]# innobackupex --user=root --password=123 --no-timestamp /backup/full

3)查看全备内容

[root@db03 ~]# ll /backup/full/
总用量 129052
-rw-r----- 1 root root      434 7月  23 08:51 backup-my.cnf
drwxr-x--- 2 root root       68 7月  23 08:51 dump
-rw-r----- 1 root root 79691776 7月  23 08:51 ibdata1
-rw-r----- 1 root root 52428800 7月  23 08:51 ibdata2
drwxr-x--- 2 root root     4096 7月  23 08:51 mysql
drwxr-x--- 2 root root     4096 7月  23 08:51 performance_schema
drwxr-x--- 2 root root       68 7月  23 08:51 row
drwxr-x--- 2 root root       20 7月  23 08:51 test
-rw-r----- 1 root root       21 7月  23 08:51 xtrabackup_binlog_info         #记录binlog的信息
-rw-r----- 1 root root      113 7月  23 08:51 xtrabackup_checkpoints         
-rw-r----- 1 root root      483 7月  23 08:51 xtrabackup_info                #工具或数据的信息
-rw-r----- 1 root root     2560 7月  23 08:51 xtrabackup_logfile             #redo-log

[root@db03 full]# cat xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 8417759

4.Xtrabackup全备恢复数据

1)删除所有数据库

mysql> drop database dump;

mysql> drop database performance_schema;

mysql> drop database row;

mysql> drop database test;

2)停止数据库

[root@db03 ~]# systemctl stop mysqld.service

3)手动模拟CSR的过程

#将redo进行重做,已提交的写到数据文件,未提交的使用undo回滚,模拟CSR的过程
[root@db03 ~]# innobackupex --user=root --password=123 --apply-log /backup/full

4)恢复数据

1>方法一:
#移走原数据目录
[root@db03 mysql]# mv data data.back

#将全备的数据目录迁移回来
[root@db03 mysql]# cp -r /backup/full ./data

[root@db03 mysql]# chown -R mysql.mysql data
2>方法二:
#使用innobackupex恢复数据
[root@db03 mysql]# innobackupex --copy-back /backup/full/
[root@db03 mysql]# chown -R mysql.mysql data

5)启动数据库查看数据

#启动数据库
[root@db03 data]# systemctl start mysqld

[root@db03 data]# mysql -uroot -p123
mysql> show databases;

5.Xtrabackup增量备份

1.基于上一次备份进行增量
2.增量备份无法单独恢复,必须基于全备进行恢复
3.所有增量必须要按顺序合并到全备当中

1)先全备

[root@db03 ~]# innobackupex --user=root --password=123 --no-timestamp /backup/full_$(date +%F)

2)写入新数据

[root@db03 ~]# mysql -uroot -p123
mysql> use dump
mysql> insert dump values(10000),(20000),(30000);

3)第一次增备

[root@db01 ~]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full_2020-07-23 /backup/inc1

参数说明:
--incremental:开启增量备份功能
--incremental-basedir:上一次备份的路径

#验证
[root@db03 ~]# cat /backup/full/xtrabackup_checkpoints 
backup_type = full-prepared
from_lsn = 0
to_lsn = 8417759
[root@db03 ~]# cat /backup/inc1/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 8417759
to_lsn = 8419281

4)再次写入数据

[root@db03 ~]# mysql -uroot -p123
mysql> use dump
mysql> insert dump values(100000),(200000),(300000);

5)第二次增备

[root@db03 ~]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/inc1/ /backup/inc2

#验证
[root@db03 backup]# cat /backup/full_2020-07-23/xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 1636167

[root@db03 backup]# cat /backup/inc1/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 1636167
to_lsn = 1640828

[root@db03 backup]# cat /backup/inc2/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 1640828
to_lsn = 1645877

6)再次写入数据

7)第三次增量备份

[root@db03 backup]# innobackupex --user=root --no-timestamp --incremental --incremental-basedir=/backup/inc2 /backup/inc3

6.Xtrabackup增量恢复数据

1)将全备执行redo

[root@db03 backup]# innobackupex --apply-log --redo-only /backup/full_2020-07-23

2)将第一次增备只执行redo并合并到第一次全备

[root@db03 backup]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc1/ /backup/full_2020-07-23

#验证
[root@db03 backup]# cat /backup/full_2020-07-23/xtrabackup_checkpoints 
backup_type = log-applied
from_lsn = 0
to_lsn = 1640828            #该值本来是inc1的位置点

3)将第二次增备只执行redo并合并到第一次全备

[root@db03 backup]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc2/ /backup/full_2020-07-23

#验证
[root@db03 backup]# cat /backup/full_2020-07-23/xtrabackup_checkpoints 
backup_type = log-applied
from_lsn = 0
to_lsn = 1645877            #该值本来是inc2的位置点

4)将最后一次增备执行redo和undo并合并到第一次全备

[root@db03 backup]# innobackupex --apply-log --incremental-dir=/backup/inc3/ /backup/full_2020-07-23

#验证
[root@db03 backup]# 
[root@db03 backup]# cat /backup/full_2020-07-23/xtrabackup_checkpoints 
backup_type = full-prepared
from_lsn = 0
to_lsn = 1649869

5)将整体数据进行一次CSR

[root@db03 backup]# innobackupex --apply-log /backup/full_2020-07-23/

6)恢复数据

[root@db03 mysql]# mv data data.bak
[root@db03 mysql]# innobackupex --copy-back /backup/full_2020-07-23/
[root@db03 mysql]# chown -R mysql.mysql data
[root@db03 mysql]# systemctl start mysqld

7.总结

1.增备:
    优点:占用磁盘空间小,没有重复数据
    缺点:恢复麻烦
    
2.全备:
    优点:恢复只需一次
    缺点:占用磁盘空间,每次全备都有重复数据

思考

企业级增量恢复实战

背景:
某大型网站,mysql数据库,数据量500G,每日更新量100M-200M

备份策略:
xtrabackup,每周六0:00进行全备,周一到周五及周日00:00进行增量备份。

故障场景:
周三下午2点出现数据库意外删除表操作。

极客公园 , 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权
转载请注明原文链接:MySQL数据备份与恢复
喜欢 (0)
[17551054905]
分享 (0)

您必须 登录 才能发表评论!