MySQL 数据库 之 温备份 & 热备份
- 前言
- 一、完全备份方案
- 二、增量备份方案
- 三、GTID 备份
- 四、mydumper 备份
- 五、LVM 快照备份
- 六、xtrabackup 备份
- 1、完全备份
- 2、增量备份
前言
本环境是基于 Centos 7.8 系统构建MySQL-5.7.14
具体构建，请参考 MySQL-5.7.14 环境构建
一、完全备份方案
mysqldump 是采用SQL级别的备份机制，它将数据表导成SQL脚本文件，在不同的MySQL版本之间升级时相对比较合适。接下来我们将详细介绍MySQL自带的mysqldump 工具的备份方案。
备份方式
# 备份时不创建数据库和切换数据库（需要管理员手工创建和切换）
[root@mysql-yum ~]# mkdir /backup/mysql -p
[root@mysql-yum ~]# mysqldump -uroot -p123456 db1 > /backup/mysql/db1_1.sql
# 备份时创建数据库和切换数据库
[root@mysql-yum ~]# mysqldump -uroot -p123456 -B db1 > /backup/mysql/db1_2.sql
# 备份时，压缩备份文件
[root@mysql-yum ~]# mysqldump -uroot -p123456 -B db1 | gzip > /backup/mysql/db1_3.sql.g
# 同时备份多个数据库
[root@mysql-yum ~]# mysqldump -uroot -p123456 -B db1 db2 | gzip > /backup/mysql/db1_2.sql.gz
# 查看备份文件
[root@mysql-yum ~]# ll /backup/mysql/
total 28
-rw-r--r-- 1 root root 2196 Jan 19 09:30 db1
-rw-r--r-- 1 root root 5301 Jan 19 09:18 db1_1.sql
-rw-r--r-- 1 root root 5435 Jan 19 09:19 db1_2.sql
-rw-r--r-- 1 root root 2195 Jan 19 09:32 db1_2.sql.gz
-rw-r--r-- 1 root root 1859 Jan 19 09:25 db1_3.sql.gz
恢复方式
# 删除数据库db1、db2
mysql> drop database db1;
Query OK, 3 rows affected (0.36 sec)
mysql> drop database db2;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
# 恢复数据
--- 方案一 直接恢复
[root@mysql-yum mysql]# zcat db1_2.sql.gz | mysql -uroot -p123456
# 查看数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| db2 |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
--- 方案二 先解压，在恢复
[root@mysql-yum mysql]# gzip -d db1_2.sql.gz
[root@mysql-yum mysql]# mysql -uroot -p123456 < db1_2.sql
# 查看数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| db2 |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
--- 方案三 直接在mysql 命令行恢复
[root@mysql-yum mysql]# gzip -d db1_2.sql.gz
mysql> source /backup/mysql/db1_2.sql
--- 方案四 备份所有的数据库
[root@mysql-yum mysql]# mysqldump -uroot -p123456 -A -B > /backup/mysql/all.sql
--- 方案五 备份表结构
[root@mysql-yum mysql]# mysqldump -uroot -p123456 -B -d db1 > db1.sql
--- 备份数据库中的指定表
[root@mysql-yum mysql]# mysqldump -uroot -p123456 db2 student > db2_student.sql
--- 在mysql-cli中恢复
mysql> source /backup/mysql/all_databases.sql
二、增量备份方案
创建数据库、表、插入数据
mysql> create database it;
mysql> CREATE TABLE `Student` (
`Sno` int(10) NOT NULL COMMENT '学号', `Sname` varchar(16) NOT NULL COMMENT
'姓名',
`Ssex` char(2) NOT NULL COMMENT '性别', `Sage` tinyint(2) NOT NULL DEFAULT '0'
COMMENT '学生年龄',
`Sdept` varchar(16) DEFAULT 'NULL' COMMENT '学生所在系别', PRIMARY KEY (`Sno`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
表中插入数据
mysql> INSERT INTO `Student` VALUES (1, '陆亚', '男', 24, '计算机网络'),(2, 'tom', '男',
26, '英语'),(3, '张阳', '男', 21, '物流管理'), (4, 'alex', '女', 22, '电子商务');
mysql> select * from it.Student;
+-----+----------+------+------+-----------------+
| Sno | Sname | Ssex | Sage | Sdept |
+-----+----------+------+------+-----------------+
| 1 | 陆亚 | 男 | 24 | 计算机网络 |
| 2 | tom | 男 | 26 | 英语 |
| 3 | 张阳 | 男 | 21 | 物流管理 |
| 4 | alex | 女 | 22 | 电子商务 |
| 5 | xumubin | 男 | 29 | 中文专业 |
| 6 | wangzhao | 男 | 21 | 导弹专业 |
+-----+----------+------+------+-----------------+
6 rows in set (0.00 sec)
完全备份
[root@mysql-yum mysql]# mysqldump -uroot -p123456 -F -B it | gzip > /backup/mysql/mysql_backup_`date +%F`.sql.gz
[root@mysql-yum mysql]# ll mysql_backup_2021-01-19.sql.gz
-rw-r--r-- 1 root root 973 Jan 19 12:01 mysql_backup_2021-01-19.sql.gz
插入数据
INSERT INTO Student values(0005,'xumubin','男',29,'中文专业'),
(0006,'wangzhao','男',21,'导弹专业');
删除it数据库
mysql> drop database it;
恢复完全备份
[root@mysql-yum mysql]# zcat mysql_backup_2021-01-19.sql.gz | mysql -uroot -p123456
恢复增量备份
[root@mysql-yum mysql]# cp /var/lib/mysql/mysql-bin.000004 .
[root@mysql-yum mysql]# mysqlbinlog mysql-bin.000004 > bin_log.sql
[root@mysql-yum mysql]# mysql -uroot -p123456 < bin_log.sql
备份完成
mysql> select * from it.Student;
+-----+----------+------+------+-----------------+
| Sno | Sname | Ssex | Sage | Sdept |
+-----+----------+------+------+-----------------+
| 1 | 陆亚 | 男 | 24 | 计算机网络 |
| 2 | tom | 男 | 26 | 英语 |
| 3 | 张阳 | 男 | 21 | 物流管理 |
| 4 | alex | 女 | 22 | 电子商务 |
| 5 | xumubin | 男 | 29 | 中文专业 |
| 6 | wangzhao | 男 | 21 | 导弹专业 |
+-----+----------+------+------+-----------------+
6 rows in set (0.00 sec)
时间点恢复
# 设置增量恢复的时间段
[root@mysql-yum mysql]# mysqlbinlog mysql-bin.000004 --start-datetime='2021-01-19 12:01:19' --stop-datetime='2021-01-19 12:07:19' -r time.sql
[root@mysql-yum mysql]# ll time.sql
-rw-r----- 1 root root 2162 Jan 19 15:08 time.sql
# 删除数据库
mysql> drop database it;
Query OK, 1 row affected (0.05 sec)
# 完全恢复
[root@mysql-yum mysql]# zcat mysql_backup_2021-01-19.sql.gz | mysql -uroot -p123456
# 增量恢复
[root@mysql-yum mysql]# mysql -uroot -p123456 < time.sql
位置点恢复
# 设置增量恢复的时间段
[root@mysql-yum mysql]# mysqlbinlog mysql-bin.000004 --start-position=4 --stop-position=476 -r post.sql
[root@mysql-yum mysql]# ll post.sql
-rw-r----- 1 root root 2162 Jan 19 15:26 post.sql
# 删除数据库
mysql> drop database it;
Query OK, 1 row affected (0.05 sec)
# 完全恢复
[root@mysql-yum mysql]# zcat mysql_backup_2021-01-19.sql.gz | mysql -uroot -p123456
# 增量恢复
[root@mysql-yum mysql]# mysql -uroot -p123456 < post.sql
三、GTID 备份
开启GTID
[root@mysql-yum mysql]# vim /etc/my.cnf
gtid-mode=on
enforce-gtid-consistency=true
[root@mysql-yum mysql]# systemctl restart mysqld
创建gtid数据库、表
mysql> create database gtid;
mysql> use gtid
mysql> create table t1(id int);
mysql> begin;
mysql> insert into t1 values(1);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
mysql> flush logs;
mysql> create table t2(id int);
mysql> begin;
mysql> insert into t2 values(1);
mysql> commit;
mysql> flush logs;
mysql> create table t3;
mysql> begin;
mysql> insert into t3 values(1);
mysql> commit;
mysql> flush logs;
查看gtid数据库的起始GTID
查看gtid数据库的末尾GTID
binlog 截取日志
# 停止bin_log日志记录
mysql> set sql_log_bin=0;
[root@mysql-yum mysql]# mysqlbinlog --include-gtids='bd01e960-562a-11eb-80d6-000c293e3782:6-12' mysql-bin.000008 mysql-bin.000009 mysql-bin.000010 --skip-gtids -r /backup/mysql/gtid.sql
[root@mysql-yum mysql]# ll /backup/mysql/gtid.sql
-rw-r----- 1 root root 5132 Jan 19 23:47 /backup/mysql/gtid.sql
恢复gtid数据库
[root@mysql-yum mysql]# mysql -u root -p123456 < /backup/mysql/gtid.sql
mysql> source /backup/mysql/gtid.sql
开启bin_log日志记录
mysql> set sql_log_bin=0;
查看备份效果
mysql> show tables from gtid;
+----------------+
| Tables_in_gtid |
+----------------+
| t1 |
| t2 |
| t3 |
+----------------+
3 rows in set (0.00 sec)
mysql> select * from gtid.t1;
+------+
| id |
+------+
| 1 |
| 1 |
+------+
2 rows in set (0.00 sec)
mysql> select * from gtid.t2;
+------+
| id |
+------+
| 1 |
| 1 |
+------+
2 rows in set (0.00 sec)
mysql> select * from gtid.t3;
+------+
| id |
+------+
| 1 |
| 1 |
+------+
2 rows in set (0.00 sec)
四、mydumper 备份
源码部署mydumper
# 上传mydumper源码包
[root@mysql-yum ~]# ll mydumper-0.9.1.tar.gz
-rw-r--r-- 1 root root 44463 Jan 18 11:05 mydumper-0.9.1.tar.gz
[root@mysql-yum mysql]# tar xf mydumper-0.9.1.tar.gz -C /usr/local/src/
# 安装编译环境、解决依赖关系
[root@mysql-yum mysql]# yum install gcc gcc-c++ make cmake -y
[root@mysql-yum mysql]# yum install glib2-devel mysql-devel zlib-devel pcre-devel -y
# cmake
[root@mysql-yum mysql]# cd /usr/local/src
[root@mysql-yum mydumper-0.9.1]# cmake .
# make & make install
[root@mysql-yum mysql]# make
[root@mysql-yum mysql]# make install
备份
# mydumper工具备份
[root@mysql-yum ~]# mydumper -u root -p 123456 -B it -o /mysql_back
[root@mysql-yum ~]# ll /mysql_back/
total 16
-rw-r--r-- 1 root root 61 Jan 19 17:14 it-schema-create.sql
-rw-r--r-- 1 root root 420 Jan 19 17:14 it.Student-schema.sql
-rw-r--r-- 1 root root 354 Jan 19 17:14 it.Student.sql
-rw-r--r-- 1 root root 137 Jan 19 17:14 metadata
# mydumper工具备份，同时压缩
[root@mysql-yum ~]# mydumper -u root -p 123456 -B it -c -o /mysql_back
[root@mysql-yum ~]# ll /mysql_back/
total 16
-rw-r--r-- 1 root root 81 Jan 19 17:19 it-schema-create.sql.gz
-rw-r--r-- 1 root root 299 Jan 19 17:19 it.Student-schema.sql.gz
-rw-r--r-- 1 root root 307 Jan 19 17:19 it.Student.sql.gz
-rw-r--r-- 1 root root 137 Jan 19 17:19 metadata
恢复文件
[root@mysql-yum ~]# myloader -u root -p 123456 -B it -o -d /mysql_back/
备份表
[root@mysql-yum ~]# mydumper -u root -p 123456 -B db2 -T student,course -o /backup/tables
恢复表
[root@mysql-yum ~]# myloader -u root -p 123456 -d /backup/tables/ -o -B db2
五、LVM 快照备份
添加新的磁盘，作为LVM存储
创建lvm、格式化
# 查看系统磁盘分区信息
[root@mysql-yum ~]# lsblk
NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
sda 8:0 0 20G 0 disk
├─sda1 8:1 0 1G 0 part /boot
└─sda2 8:2 0 19G 0 part
├─centos-root 253:0 0 17G 0 lvm /
└─centos-swap 253:1 0 2G 0 lvm [SWAP]
sdb 8:16 0 10G 0 disk
sr0 11:0 1 4.5G 0 rom /mnt
# 创建lvm
[root@mysql-yum ~]# vgcreate mysql /dev/sdb
[root@mysql-yum ~]# lvcreate -n lv_mysql -L 4G mysq
# 格式化lvm分区
[root@mysql-yum ~]# mkfs.xfs /dev/mysql/lv_mysql
# 查看分区
[root@mysql-yum ~]# lsblk -l
NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
sda 8:0 0 20G 0 disk
sda1 8:1 0 1G 0 part /boot
sda2 8:2 0 19G 0 part
centos-root 253:0 0 17G 0 lvm /
centos-swap 253:1 0 2G 0 lvm [SWAP]
sdb 8:16 0 10G 0 disk
mysql-lv_mysql 253:2 0 4G 0 lvm
sr0 11:0 1 4.5G 0 rom /mnt
# 备份数据
[root@mysql-yum ~]# cd /var/lib/mysql
[root@mysql-yum mysql]# tar czf /backup/mysql/mysql.tar.gz *
# 挂载mysql数据目录到lvm分区
[root@mysql-yum mysql]# mount /dev/mapper/mysql-lv_mysql /var/lib/mysq
# 解压数据到mysql目录
[root@mysql-yum mysql]# tar xf /backup/mysql/mysql.tar.gz -C /var/lib/mysql
# 查看磁盘挂载信息
[root@mysql-yum mysql]# df -h
Filesystem Size Used Avail Use% Mounted on
devtmpfs 898M 0 898M 0% /dev
tmpfs 910M 0 910M 0% /dev/shm
tmpfs 910M 9.5M 901M 2% /run
tmpfs 910M 0 910M 0% /sys/fs/cgroup
/dev/mapper/centos-root 17G 4.0G 14G 24% /
/dev/sr0 4.5G 4.5G 0 100% /mnt
/dev/sda1 1014M 151M 864M 15% /boot
tmpfs 182M 0 182M 0% /run/user/0
/dev/mapper/mysql-lv_mysql 4.0G 224M 3.8G 6% /var/lib/mysql
# 给数据库加读锁
mysql> flush table with read lock;
Query OK, 0 rows affected (0.00 sec)
# 创建快照
[root@mysql-yum mysql]# lvcreate -n lv_mysql_s -L 500M -s /dev/mysql/lv_mysql
# 解锁数据库
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
# 创建快照挂载点目录、挂载
[root@mysql-yum mysql]# mkdir /mnt/mysql/ -p
[root@mysql-yum mysql]# mount -o nouuid /dev/mysql/lv_mysql_s /mnt/mysql/
# 查看挂载情况
[root@mysql-yum mysql]# df -h
Filesystem Size Used Avail Use% Mounted on
devtmpfs 898M 0 898M 0% /dev
tmpfs 910M 0 910M 0% /dev/shm
tmpfs 910M 9.6M 901M 2% /run
tmpfs 910M 0 910M 0% /sys/fs/cgroup
/dev/mapper/centos-root 17G 4.0G 14G 24% /
/dev/sr0 4.5G 4.5G 0 100% /mnt
/dev/sda1 1014M 151M 864M 15% /boot
tmpfs 182M 0 182M 0% /run/user/0
/dev/mapper/mysql-lv_mysql 4.0G 231M 3.8G 6% /var/lib/mysql
/dev/mapper/mysql-lv_mysql_s 4.0G 231M 3.8G 6% /mnt/mysql/
查看备份效果
# 打包备份快照文件
[root@mysql-yum ~]# mkdir /back
[root@mysql-yum mysql]# cd
[root@mysql-yum ~]# cd /mnt/mysql/
[root@mysql-yum mysql]# tar czf /back/mysql.tar.gz *
# 停止数据库服务
[root@mysql-yum ~]# systemctl stop mysqld
# 删除数据库目录文件
[root@mysql-yum ~]# cd /var/lib/mysql
[root@mysql-yum mysql]# rm -rf *
# 恢复数据
[root@mysql-yum mysql]# tar xf /back/mysql.tar.gz
#启动数据库服务
[root@mysql-yum mysql]# systemctl start mysqld
# 查看数据库中的数据
mysql> select * from db2.student;
+--------------+-----------+---------+---------+-----------+--------------+
| stu_id | stu_name | stu_sex | stu_age | stu_major | stu_college |
+--------------+-----------+---------+---------+-----------+--------------+
| 201804550101 | 郭奎 | 男 | 22 | 计科 | 信工学院 |
| 201804550102 | 吕宇航 | 男 | 18 | 计科 | 信工学院 |
| 201804550103 | 张豪辉 | 女 | 19 | 计科 | 信工学院 |
| 201804550107 | 丁志杰 | 男 | 17 | 金融学 | 金贸学院 |
| 201804550109 | 范伟 | 男 | 19 | 金融学 | 金贸学院 |
| 201804550116 | 张依婷 | 女 | 17 | 大数据 | 信工学院 |
| 201804550120 | 张维 | 男 | 19 | 计科 | 信工学院 |
| 201804550121 | 朱柳阳 | 女 | 20 | 计科 | 信工学院 |
| 201804550144 | 谭兵炎 | 男 | 20 | 大数据 | 信工学院 |
| 201804550153 | 杨志强 | 男 | 17 | 大数据 | 信工学院 |
+--------------+-----------+---------+---------+-----------+--------------+
10 rows in set (0.01 sec)
六、xtrabackup 备份
xtrabackup 部署
[root@mysql-yum ~]# yum install percona-xtrabackup-24-2.4.8-1.el7.x86_64.rpm perl-DBD-MySQL-4.023-6.el7.x86_64.rpm -y
1、完全备份
备份数据
[root@mysql-yum ~]# innobackupex -u root -p 123456 --no-timestamp /backup/mysql/full_$(date +%F)
[root@mysql-yum ~]# ll /backup/mysql/
total 0
drwxr-x--- 7 root root 218 Jan 20 14:35 full_2021-01-20
恢复数据
# 应用日志文件的数据
[root@mysql-yum ~]# innobackupex -u root -p 123456 --apply-log /backup/mysql/full_2021-01-20/
# 停止mysql服务
[root@mysql-yum ~]# systemctl stop mysqld
# 删除mysql数据目录中的文件
[root@mysql-yum ~]# rm -rf /var/lib/mysql/*
# 恢复数据
[root@mysql-yum ~]# innobackupex --copy-back /backup/mysql/full_2021-01-20/
# 修改目录所属者、所属组
[root@mysql-yum ~]# chmod -R mysql.mysql /var/lib/mysql/*
# 启动mysqld服务
[root@mysql-yum ~]# systemctl start mysql
查看mysql数据库的数据
2、增量备份
备份数据
# 插入数据
mysql> insert into student
-> values('907','王老六','男','1999','会计','陕西省咸阳市');
# 完全备份
innobackupex -u root -p 123456 --no-timestamp /backup/mysql/full_$(date +%F)
# 增量备份
[root@mysql-yum ~]# innobackupex --incremental -u root -p 123456 --no-timestamp /backup/mysql/incr_1 --incremental-basedir=/backup/mysql/full_2021-01-20
# 插入数据
mysql> insert into student
-> values('908','找小花','女','1994','工商','陕西省延安市');
# 再次增量备份
[root@mysql-yum ~]# innobackupex --incremental -u root -p 123456 --no-timestamp /backup/mysql/incr_2 --incremental-basedir=/backup/mysql/incr_1
恢复数据
# 恢复全备
[root@mysql-yum ~]# innobackupex -u root -p 123456 --apply-log --redo-only /backup/mysql/full_2021-01-20/
# 恢复增量备份
[root@mysql-yum ~]# innobackupex -u root -p 123456 --apply-log --redo-only /backup/mysql/full_2021-01-20/ --incremental-dir=/backup/mysql/incr_1
# 再次恢复增量备份
[root@mysql-yum ~]# innobackupex -u root -p 123456 --apply-log --redo-only /backup/mysql/full_2021-01-20/ --incremental-dir=/backup/mysql/incr_2
# 停止数据库
[root@mysql-yum ~]# systemctl stop mysqld
# 删除数据库目录文件
[root@mysql-yum ~]# rm -rf /var/lib/mysql/*
# 还原数据到数据库数据目录中
innobackupex -u root -p 123456 --copy-back /backup/mysql/full_2021-01-20/
# 修改数据库文件所属主、所属组
[root@mysql-yum ~]# chown -R mysql.mysql /var/lib/mysql/*
查看mysql数据库的数据
