mysql数据备份
#mysql客户端
mysql
mysqladmin
mysqldump
备份的原因
1.备份就是为了恢复。
2.尽量减少数据的丢失（公司的损失）
备份的类型
1.冷备：停库，停服务，备份
2.热备：不停库，不停服务，备份
3.温备：不停服务，锁表（阻止数据写入），备份
#冷备份:
这些备份在用户不能访问数据时进行，因此无法读取或修改数据。这些脱机备份会阻止执行任何使用数据的活动。这些类型的备份不会干扰正常运行的系统的性能。但是，对于某些应用程序，会无法接受必须在一段较长的时间里锁定或完全阻止用户访问数据。
#温备份:
这些备份在读取数据时进行，但在多数情况下，在进行备份时不能修改数据本身。这种中途备份类型的优点是不必完全锁定最终用户。但是，其不足之处在于无法在进行备份时修改数据集，这可能使这种类型的备份不适用于某些应用程序。在备份过程中无法修改数据可能产生性能问题。
#热备份:
这些动态备份在读取或修改数据的过程中进行，很少中断或者不中断传输或处理数据的功能。使用热备份时，系统仍可供读取和修改数据的操作访问。
备份的策略
1.全备：全部数据备份
2.增备：针对于上一次备份，将新数据备份
3.差异备份：基于上一次全备进行新数据的备份
备份方式
- 逻辑备份
#基于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
- 物理备份
#备份底层的数据文件
1.备份整个data数据目录
2.xtrabackup
mysqldump客户端
- 常用参数
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
- 注意：
1）mysqldump在备份和恢复时都需要MySQL实例启动为前提
2）一般数据量级100G以内，大约15-30分钟可以恢复
3）mysqldump是以覆盖的形式恢复数据的
- 思考
数据库或表被误删除的是很久之前创建的（一年前，100个binlog）
如果基于binlog全量恢复，成本很高
1.可以用备份恢复+短时间内二进制日志，恢复到故障之前
2.非官方方法，binlog2sql，binlog取反，类似于Oracle的flushback
3.延时从库
如果同一时间内和故障库无关的数据库都有操作，在截取binlog时都会被截取到
其他过滤方案？
1.-d 参数接库名
企业案例
- 背景
1.正在运行的网站系统，MySQL数据库，数据量25G，日业务增量10-15M。
2.备份策略：每天23：00，计划任务调用mysqldump执行全备脚本
3.故障时间点：上午10点开发人员误删除一个核心业务表，如何恢复？
- 思路
1.停库，避免二次伤害
2.创建新库
3.倒入前一天的全备
4.通过binlog找到前一天23：00到第二天10点之间的数据
5.导入找到的新数据
6.恢复业务
a.直接使用临时库顶替原生产库，前端应用割接到新库（数据量特别大的时候）
b.将误删除的表单独导出，然后导入到原生产环境（数据量小的时候）
- 模拟案例
模拟生产数据
mysql> create database del;
Query OK, 1 row affected (0.00 sec)
{1}
mysql> use del
Database changed
mysql> create table del select * from world.city;
Query OK, 4079 rows affected (0.04 sec)
Records: 4079 Duplicates: 0 Warnings: 0
{1}
mysql> select count(*) from del;
+----------+
| count(*) |
+----------+
| 4079 |
+----------+
1 row in set (0.00 sec)
{1}
mysql> insert del select * from del;
Query OK, 4079 rows affected (0.06 sec)
Records: 4079 Duplicates: 0 Warnings: 0
{1}
mysql> insert del select * from del;
Query OK, 8158 rows affected (0.05 sec)
Records: 8158 Duplicates: 0 Warnings: 0
{1}
mysql> insert del select * from del;
Query OK, 16316 rows affected (0.07 sec)
Records: 16316 Duplicates: 0 Warnings: 0
{1}
mysql> insert del select * from del;
Query OK, 32632 rows affected (0.12 sec)
Records: 32632 Duplicates: 0 Warnings: 0
{1}
mysql> select count(*) from del;
+----------+
| count(*) |
+----------+
| 65264 |
+----------+
1 row in set (0.01 sec)
- 模拟23:00全备
mysqldump -uroot -p1 -A -R --triggers --master-data=2 --single-transaction > /root/full.sql
- 模拟23：00到10:00的数据操作
mysql> use del
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> insert del select * from del;
Query OK, 65264 rows affected (0.33 sec)
Records: 65264 Duplicates: 0 Warnings: 0
mysql>
mysql> insert del select * from del;
Query OK, 130528 rows affected (0.91 sec)
Records: 130528 Duplicates: 0 Warnings: 0
mysql> select count(*) from del;
+----------+
| count(*) |
+----------+
| 261056 |
+----------+
1 row in set (0.06 sec)
- 模拟删库
mysql> drop database del;
Query OK, 1 row affected (0.05 sec)
恢复数据
方案一
1）停库，避免二次伤害
systemctl stop mysqld.service
2）通过binlog找到前一天23：00到第二天10点之间的数据
# 起始位置：
head -22 full.sql | tail -1
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000015', MASTER_LOG_POS=1763;
#结束位置：
mysqlbinlog /usr/local/mysql/data/mysql-bin.000015 > 1.txt
tail 1.txt
# at 2179
#200722 23:24:50 server id 9 end_log_pos 2268 CRC32 0x3e598d51 Query thread_id=8 exec_time=0 error_code=0
SET TIMESTAMP=1595431490/*!*/;
SET @@session.pseudo_thread_id=8/*!*/;
drop database del
mysqlbinlog -d del --start-position=4268431 --stop-position=17069047 /usr/local/mysql/data/mysql-bin.000005 > /root/new.sql
3）创建新数据库
4）将binlog与全备发往新数据库
scp /root/*.sql 172.16.1.53:/root/
root@172.16.1.53\'s password:
full.sql 100% 3694KB 47.4MB/s 00:00
new.sql 100% 2093 789.9KB/s 00:00
5）将数据导入新库
mysql> source /root/full.sql;
mysql> source /root/new.sql;
6）确认数据
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| del |
| dsb |
| mysql |
| performance_schema |
| sss |
| test |
| world |
+--------------------+
8 rows in set (0.00 sec)
mysql> use del
Database changed
mysql> select count(*) from del;
+----------+
| count(*) |
+----------+
| 261056 |
+----------+
1 row in set (0.06 sec)
7）恢复业务
1.直接使用临时库顶替原生产库，前端应用割接到新库（数据量特别大的时候）
2.将误删除的表单独导出，然后导入到原生产环境（数据量小的时候）
1）新库导出指定业务库
[root@db02 ~]# mysqldump dump > /tmp/dump.sql
2）新库将数据推送回老库
[root@db02 ~]# scp /tmp/dump.sql 172.16.1.53:/tmp
3）将恢复的数据导入老库
mysql> create database dump;
mysql> use dump;
mysql> source /tmp/dump.sql
方案二：
1）停库，避免二次伤害
systemctl stop mysqld.service
2）通过binlog找到前一天23：00到第二天10点之间的数据
# 起始位置：
head -22 full.sql | tail -1
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000015', MASTER_LOG_POS=1763;
#结束位置：
mysqlbinlog /usr/local/mysql/data/mysql-bin.000015 > 1.txt
tail 1.txt
# at 2179
#200722 23:24:50 server id 9 end_log_pos 2268 CRC32 0x3e598d51 Query thread_id=8 exec_time=0 error_code=0
SET TIMESTAMP=1595431490/*!*/;
SET @@session.pseudo_thread_id=8/*!*/;
drop database del
mysqlbinlog -d del --start-position=4268431 --stop-position=17069047 /usr/local/mysql/data/mysql-bin.000005 > /root/new.sql
3）创建新数据库
4）将binlog与全备发往新数据库
scp /root/*.sql 172.16.1.53:/root/
root@172.16.1.53\'s password:
full.sql 100% 3694KB 47.4MB/s 00:00
new.sql 100% 2093 789.9KB/s 00:00
5）将数据导入新库
mysql> source /root/full.sql;
mysql> source /root/new.sql;
6）确认数据
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| del |
| dsb |
| mysql |
| performance_schema |
| sss |
| test |
| world |
+--------------------+
8 rows in set (0.00 sec)
mysql> use del
Database changed
mysql> select count(*) from del;
+----------+
| count(*) |
+----------+
| 261056 |
+----------+
1 row in set (0.06 sec)
7）导出del库并发回原数据库
mysqldump -R --triggers --master-data=2 --single-transaction -B del > del.sql
scp del.sql 172.16.1.54:/root/
root@172.16.1.54's password:
del.sql 100% 11MB 76.6MB/s 00:00
8）导入原数据库
mysql> source /root/del.sql;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| del |
| dsb |
| mysql |
| performance_schema |
| sss |
| test |
| world |
+--------------------+
8 rows in set (0.00 sec)
方案三：
1）停库，避免二次伤害
systemctl stop mysqld.service
2）通过binlog找到前一天23：00到第二天10点之间的数据
# 起始位置：
head -22 full.sql | tail -1
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000015', MASTER_LOG_POS=1763;
#结束位置：
mysqlbinlog /usr/local/mysql/data/mysql-bin.000015 > 1.txt
tail 1.txt
# at 2179
#200722 23:24:50 server id 9 end_log_pos 2268 CRC32 0x3e598d51 Query thread_id=8 exec_time=0 error_code=0
SET TIMESTAMP=1595431490/*!*/;
SET @@session.pseudo_thread_id=8/*!*/;
drop database del
mysqlbinlog -d del --start-position=4268431 --stop-position=17069047 /usr/local/mysql/data/mysql-bin.000005 > /root/new.sql
3）干掉data目录并重新生成
cd /usr/local/mysql/data/
rm -rf *
cd ../scripts/
./mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
4）导入数据并查看
mysql> source /root/full.sql;
mysql> source /root/new.sql;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| del |
| dsb |
| mysql |
| performance_schema |
| sss |
| test |
| world |
+--------------------+
8 rows in set (0.00 sec)
mysql> use del
Database changed
mysql> select count(*) from del;
+----------+
| count(*) |
+----------+
| 261056 |
+----------+
1 row in set (0.05 sec)
物理备份Xtrabackup
安装
#上传文件包
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
#安装
yum localinstall -y percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm
#安装好后的命令
xtrabackup
innobackupex
Xtrabackup备份
1）对于非innodb表（比如myisam）是直接锁表cp数据文件，属于一种温备。
2）对于innodb的表（支持事务），不锁表，cp数据页最终以数据文件方式保存下来，并且把redo和undo一并备走，属于热备方式。
3）备份时读取配置文件/etc/my.cnf（如果使用Xtrabackup备份，必须要配置datadir）
Xtrabackup全量备份
1）准备备份目录
mkdir /backup
2）备份(全备)
innobackupex --user=root --password=123 /backup/full
#去掉时间戳进行备份
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
- Xtrabackup全备恢复数据
1）删除所有数据库
drop database dump;
drop database performance_schema;
drop database row;
drop database test;
2）停止数据库
systemctl stop mysqld.service
3）手动模拟CSR的过程
#将redo进行重做，已提交的写到数据文件，未提交的使用undo回滚，模拟CSR的过程
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;
Xtrabackup增量备份
1.基于上一次备份进行增量
2.增量备份无法单独恢复，必须基于全备进行恢复
3.所有增量必须要按顺序合并到全备当中
1）先全备
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）第二次增备
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）第三次增量备份
innobackupex --user=root --no-timestamp --incremental --incremental-basedir=/backup/inc2 /backup/inc3
Xtrabackup增量恢复数据
1）将全备执行redo
innobackupex --apply-log --redo-only /backup/full_2020-07-23
2）将第一次增备只执行redo并合并到第一次全备
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并合并到第一次全备
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并合并到第一次全备
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（Control system recovery？）
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
总结
1.增备：
优点：占用磁盘空间小，没有重复数据
缺点：恢复麻烦
2.全备：
优点：恢复只需一次
缺点：占用磁盘空间，每次全备都有重复数据
思考
企业级增量恢复实战
背景：
某大型网站，mysql数据库，数据量500G，每日更新量100M-200M
备份策略：
xtrabackup，每周六0:00进行全备，周一到周五及周日00:00进行增量备份。
故障场景：
周三下午2点出现数据库意外删除表操作。
1. xtrabackup恢复
2. binlog恢复