专职DBA-mysqldump企业级备份恢复
时间：2021-07-01 10:21:17
帮助过：5人阅读
mysqldump企业级备份恢复
数据库备份最高层次思想
---------------------------------------
数据库备份最牛的层次，就是永远都用不上备份。
这就像我们日常购买大病保险一样，任何人购买大病保险都肯定不是希望得大病，我们做数据库备份也是一样，备份策略无论做得多么完备，我们还是不希望故障发生。
因此，除了具备高超的备份策略和精湛的恢复能力之外，还要在未雨绸缪上多下功夫以达到防患于未然的目的。
对数据一致性要求很严格的业务可以使用MGR/PXC
/MySQL InnoDB Cluster
/RAC架构
DBA两大工作核心：
保护数据的安全：防止数据丢失、脱库、泄密、宕机、数据混乱等。
能7x24小时提供服务。
[root@db01 ~]# mysqld
--defaults-file=/data/mysql/3306/my.cnf &
[1] 6798
[root@db01 ~]# ps
-aux
| grep mysql
mysql 6798 9.1 17.5 1118576 176700 pts
/2 Sl
17:
15 0:
01 mysqld
--defaults-file=/data/mysql/3306/my.cnf
root
6829 0.0 0.0 112708 976 pts
/2 R
+ 17:
15 0:
00 grep
--color=auto mysql
1.全量备份
全备：把数据库中所有的数据进行备份。
备份数据库中所有库的所有数据命令：
[root@db01 ~]# mysqldump
-S
/data
/mysql
/3306/mysql.sock
-p
-B
--master-data=2 --single-transaction -A --set-gtid-purged=OFF |gzip >/backup/all.sql.gz
备份一个库中所有的数据命令：
[root@db01 ~]# mysqldump
-S
/data
/mysql
/3306/mysql.sock
-p
-B
--master-data=2 --single-transaction app --set-gtid-purged=OFF |gzip >/backup/app.sql.gz
2.增量备份
增量数据：就是上次全备之后到下次全备之前数据库所更新的数据。
在使用mysqldump命令做全备时，增量数据就是mysql的binlog日志，对binlog日志进行备份，就是增量备份了。
3.按天全备
优点：恢复数据时需要的数据文件数量少，恢复时间短，维护成本低。
缺点：每天一个全备，占用空间多，占用系统资源多，经常备份会影响用户体验。
中小型企业用得最多的策略就是按天全备，然后根据空间情况保留全备天数。
企业数据很重要可以买磁带机等设备长期保存备份的数据。
binlog增量的清理可以在my.cnf中配置过期清理天数expire_logs_days = 7保留七天内的binlog日志。
理论上如果每天进行全备，那么binlog只要保留1天的就够了。
4.按周全备
优点：每周仅有一个完整备份，占用磁盘总空间小，占用系统资源少，备份次数少，用户体验好一些。
缺点：恢复时数据文件多，导致恢复麻烦，维护成本高，恢复时间长。
大型企业由于数据量特别大，每天全备时间太长，用周备。
5.MySQL常用的备份方式
(1).逻辑备份（数据库总数据量不超过30GB）
原理：以SQL语句的形式存储。
备份命令：mysqldump, mydumper
恢复命令：mysql, source
增量恢复命令：mysqlbinlog（把增量的binlog日志转换成SQL文件）
优点：操作简单、方便、可靠，备份的数据可以跨平台、跨版本、跨软件、跨操作系统，分库分表备份。
缺点：备份速度比物理备份慢，恢复效率也不高。
备份要求：需要锁表但不需要停库，锁表会影响数据库更新，InnoDB引擎可以不锁表，而采用事务备份方案。
[root@db01 ~]# mysqldump
-S
/data
/mysql
/3306/mysql.sock
-p
-A
-B
--master-data=2 --single-transaction --set-gtid-purged=OFF |gzip >/backup/all.sql.gz
[root@db01 ~]# zcat
/backup/all.sql.gz
|mysql
-S
/data
/mysql
/3306/mysql.sock
-p
[root@db01 /data/mysql/3306/logs]# mysqlbinlog mysql
-bin.
000001 mysql
-bin.
000002 > bin.sql
简单的参考备份脚本：（此脚本需要修改，并且有待完善）
[root@db01 /backup/sh]# vim mysqldump_v1.sh
#!/bin
/bash
bak_path=/backup/mysql
[ ! -d $bak_path ] && mkdir
-p $bak_path
/usr
/local
/mysql
/bin
/mysqldump
-S
/data
/mysql
/3306/mysql.sock
-p123
-A
-B
--master-data=2 --single-transaction --set-gtid-purg
ed
=OFF |gzip
>$bak_path
/3306-full-$(date
+%F).sql.gz
/usr
/bin
/scp
-rp $bak_path
/ root
@backup:
/backup/mysql
/
/usr
/bin
/rsync
-az $bak_path
/ rsync_backup
@backup::mysql
/ --password-file=/etc/rsync.password
/usr
/bin
/find $bak_path
/ -type
-f
-name "
*.sql.gz"
-mtime
+7 | /usr
/bin
/xargs rm
-rf
[root@db01 /backup/sh]# vim mysqldump_v2.sh
#!/bin
/bash
bak_path=/backup/mysql
[ ! -d $bak_path ] && mkdir
-p $bak_path
if [ $(date +%w) -eq 6 ]
then
file_name=bak_$(date
+%w
-%F)
else
file_name=bak_$(date
+%F)
fi
/usr
/local
/mysql
/bin
/mysqldump
-S
/data
/mysql
/3306/mysql.sock
-p123
-A
-B
--master-data=2 --single-transaction --set-gtid-purg
ed
=OFF |gzip
>$bak_path
/3306-full-${
file_name}.sql.gz
/usr
/bin
/md5sum $bak_path
/3306-full-${
file_name}.sql.gz
>$bak_path
/3306-full-${
file_name}.flag
/usr
/bin
/scp
-rp $bak_path
/ root
@backup:
/backup/mysql
/
/usr
/bin
/rsync
-az $bak_path
/ rsync_backup
@backup::mysql
/ --password-file=/etc/rsync.password
/usr
/bin
/find $bak_path
/ -type
-f
-name "
*.sql.gz"
-mtime
+7 | /usr
/bin
/xargs rm
-rf
[root@db01 /backup/sh]# crontab
-e
# backup mysql data.
00 00 * * * /bin
/sh
/backup/sh
/mysqldump_v1.sh
&>/dev
/null
[root@backup /backup/mysql]# md5sum
-c
3306-full-2019-05-05.flag
/backup/mysql
/3306-full-2019-05-05.sql.gz: OK
一般备份服务器上保留最近7天的所有备份，同时保留每周六的全部备份命令：
[root@backup ~]# find
/backup/mysql
/ -type f
-name "bak_
*"
-mtime
+7 !
-name "bak_6
*"
| xargs rm
-rf
(2).物理备份（数据库总数据量超过30GB）
命令：cp、rsync、tar、scp、xtrabackup
原理：直接复制磁盘物理文件。
特点：速度快，效率高
缺点：不容易跨平台、跨版本、跨软件、跨操作系统。分库分表备份恢复时麻烦，软件的使用较为复杂。
冷备：
使用cp、rsync、tar、scp等复制工具把mysql数据文件复制成多份，
由于在备份期间数据仍然有写入操作，所以直接复制的备份方式会引起数据丢失。
另外在恢复数据库时，对新数据库的路径、配置也有要求，一般要和原数据库的配置保持一致（版本、路径、配置尽可能一样）。
为了确保备份期间数据的一致性，可以人工停库、锁库后再进行物理复制，而生产环境是不允许这样做的。（这些命令做冷备需要锁表或者停库以确保数据的一致性。）
可以根据mysql主从复制利用从库进行冷备份的策略。
一般在进行大规模数据库迁移时，先停库，然后物理迁移，这样做是很有效率的方案。
热备：
用第三方工具来实现物理热备，Xtrabackup
热备不需要锁表（仅事务引擎，例如InnoDB）或停机
物理备份企业场景：
1、数据库总数据量超过30GB，用Xtrabackup热备工具进行备份。
2、在从库上备份，备份时停止SQL线程应用数据到数据库，然后cp、tar打包备份，这也是一种不错的冷备方案，不会影响数据库的服务。
大多数中小型企业的数据库环境为一主多从，可以在一个从库服务器上专门做全量以及增量备份，需要开启从库记录binlog日志功能，备份工具可用mysqldump/Xtrabackup。
6.中小企业MySQL增量恢复案例
具备什么条件才能完整恢复数据库数据呢？？？
1.全备 mysqldump
2.全备之后的 所有binlog增量日志
假设当前数据库内的数据如下：
[root@db01 ~]# mysql
-S
/data
/mysql
/3306/mysql.sock
-p
-e "
select count(
*)
from app.t1;"
Enter password:
+----------+
| count(
*)
|
+----------+
| 28 |
+----------+
模拟0点对数据库app数据进行全备
[root@db01 ~]# date
-s "
2017/03/19"
Sun Mar 19 00:
00:
00 CST
2017
[root@db01 ~]# mysqldump
-S
/data
/mysql
/3306/mysql.sock
-p
-B
--master-data=2 --single-transaction --set-gtid-purged=OFF app |gzip >/backup/3306-app_$(date +%F).sql.gz
[root@db01 ~]# ls
-l
/backup/3306-app_2017
-03-19.sql.gz
-rw
-r
--r-- 1 root root 943 Mar 19 00:00 /backup/3306-app_2017-03-19.sql.gz
模拟0点全备后用户继续写入数据
[root@db01 ~]# mysql
-S
/data
/mysql
/3306/mysql.sock
-p
-e "
insert into app.t1(id,name)
values(
29,
‘zhouwanchun‘);"
[root@db01 ~]# mysql
-S
/data
/mysql
/3306/mysql.sock
-p
-e "
insert into app.t1(id,name)
values(
30,
‘zhouwanchun‘);"
[root@db01 ~]# mysql
-S
/data
/mysql
/3306/mysql.sock
-p
-e "
insert into app.t1(id,name)
values(
31,
‘zhouwanchun‘);"
[root@db01 ~]# mysql
-S
/data
/mysql
/3306/mysql.sock
-p
-e "
insert into app.t1(id,name)
values(
32,
‘zhouwanchun‘);"
[root@db01 ~]# mysql
-S
/data
/mysql
/3306/mysql.sock
-p
-e "
select count(
*)
from app.t1;" Enter password:
+----------+
| count(
*)
|
+----------+
| 32 |
+----------+
模拟上午10:00点管理员删除oldboy数据库
[root@db01 ~]# date
-s "
2017/03/19 10:
00"
Sun Mar 19 10:
00:
00 CST
2017
[root@db01 ~]# mysql
-S
/data
/mysql
/3306/mysql.sock
-p
-e "
drop database app;show databases;"
Enter password:
+--------------------+
| Database |
+--------------------+
| information_schema
|
| app01
|
| mysql
|
| performance_schema
|
| sys
|
+--------------------+
备份所有的binlog增量文件，防止二次破坏。
[root@db01 ~]# cp
-a
/data
/mysql
/3306/logs
/ /backup/
[root@db01 ~]# ls
-l
/backup/logs
/
total 784
-rw
-r
--r-- 1 root root 7641 Aug 29 2019 bin.sql
-rw
-r
----- 1 mysql mysql 1797 Aug 29 2019 mysql-bin.000001
-rw
-r
----- 1 mysql mysql 241 Aug 29 2019 mysql-bin.000002
-rw
-r
----- 1 mysql mysql 217 Aug 29 2019 mysql-bin.000003
-rw
-r
----- 1 mysql mysql 778113 Mar 19 00:04 mysql-bin.000004
-rw
-r
----- 1 mysql mysql 156 Aug 29 2019 mysql-bin.index
开始恢复，停止数据库对外访问
[root@linux-node1 ~]# iptables
-I INPUT
-p tcp
--dport 3306 ! -s 10.0.0.11 -j DROP
非10.
0.0.11禁止访问数据库3306端口
解压全备的数据
[root@db01 ~]# gzip
-cd
/backup/3306-app_2017
-03-19.sql.gz
> app.sql
[root@db01 ~]# ls
-l app.sql
-rw
-r
--r-- 1 root root 2507 Mar 19 10:09 app.sql
解析binlog文件增量数据
[root@db01 ~]# sed
-n
‘22p‘ app.sql
-- CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000004‘, MASTER_LOG_POS=776907;
[root@db01 ~]# mysqlbinlog
-d app
/backup/logs
/mysql
-bin.
000004 --start-position=776907 -r bin.sql
[root@db01 ~]# ls
-l
total 12
-rw
-r
--r-- 1 root root 2507 Mar 19 10:09 app.sql
-rw
-r
----- 1 root root 5518 Mar 19 10:12 bin.sql
可能会不只是一个binlog文件，你可以继续恢复后面的所有binlog文件。
# mysqlbinlog -d app mysql
-bin.
000005 mysql
-bin.
000006 -r bin1.sql
踢出误删数据库的drop语句
[root@db01 ~]# grep
-w
drop bin.sql
drop database app
[root@db01 ~]# sed
-i
‘/drop database app/d‘ bin.sql
[root@db01 ~]# grep
-w
insert bin.sql
[root@db01 ~]#
先恢复0点以前的全备数据
[root@db01 ~]# mysql
-S
/data
/mysql
/3306/mysql.sock
-p
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema
|
| app01
|
| mysql
|
| performance_schema
|
| sys
|
+--------------------+
5 rows
in set (
0.00 sec)
[root@db01 ~]# mysql
-S
/data
/mysql
/3306/mysql.sock
-p
< app.sql
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema
|
| app
|
| app01
|
| mysql
|
| performance_schema
|
| sys
|
+--------------------+
6 rows
in set (
0.00 sec)
mysql> select count(
*)
from app.t1;
+----------+
| count(
*)
|
+----------+
| 28 |
+----------+
1 row
in set (
0.01 sec)
0点以前的数据已恢复，再来恢复0点以后的增量数据
[root@db01 ~]# mysql
-S
/data
/mysql
/3306/mysql.sock
-p app
< bin.sql
Enter password:
mysql> select count(
*)
from app.t1;
+----------+
| count(
*)
|
+----------+
| 32 |
+----------+
1 row
in set (
0.01 sec)
增量的数据回来了
增量的数据回来了
7.分库分表备份
(1).分库备份
[root@db01 /backup/sh]# cat fenku.sh
#!/bin
/bash
bak_path=/backup/$(date
+%F)
[ ! -d $bak_path ] && mkdir
-p $bak_path
for dbname
in `
/usr
/local
/mysql
/bin
/mysql
-S
/data
/mysql
/3306/mysql.sock
-p123
-e "show databases"
|sed
‘1,2d‘|grep
-v _
schema`
do
/usr
/local
/mysql
/bin
/mysqldump
-S
/data
/mysql
/3306/mysql.sock
-B
--master-data=2 --single-transaction --set-gtid-purged=OFF |gzip >$bak_path/${dbname}_$(date +%F).sql.gz
done
[root@db01 /backup/sh]# sh fenku.sh
[root@db01 /backup/sh]# ls
-l
/backup/2017-03-19/
total 16
-rw
-r
--r-- 1 root root 130 Mar 19 10:36 app01_2017-03-19.sql.gz
-rw
-r
--r-- 1 root root 130 Mar 19 10:36 app_2017-03-19.sql.gz
-rw
-r
--r-- 1 root root 130 Mar 19 10:36 mysql_2017-03-19.sql.gz
-rw
-r
--r-- 1 root root 130 Mar 19 10:36 sys_2017-03-19.sql.gz
(2).分表备份
[root@db01 /backup/sh]# cat fenbiao.sh
#!/bin
/bash
bak_path=/backup/$(date
+%F)
[ ! -d $bak_path ] && mkdir
-p $bak_path
for dbname
in `
/usr
/local
/mysql
/bin
/mysql
-S
/data
/mysql
/3306/mysql.sock
-p123
-e "show databases"
|sed
‘1,2d‘|grep
-v _
schema`
do
for tablename
in `
/usr
/local
/mysql
/bin
/mysql
-S
/data
/mysql
/3306/mysql.sock
-p123
-e "show tables
from $dbname;"
|sed
‘1d‘`
do
/usr
/local
/mysql
/bin
/mysqldump
-S
/data
/mysql
/3306/mysql.sock
-p123
-B
--master-data=2 --single-transaction --set-gtid-purged=OFF |gzip > $bak_path/${dbname}_${tablename}_$(date +%F).sql.gz
done
done
[root@db01 /backup/sh]# sh fenbiao.sh
[root@db01 /backup/sh]# ls
-l
/backup/2017-03-19/
total 548
-rw
-r
--r-- 1 root root 130 Mar 19 10:36 app01_2017-03-19.sql.gz
-rw
-r
--r-- 1 root root 130 Mar 19 10:36 app_2017-03-19.sql.gz
-rw
-r
--r-- 1 root root 130 Mar 19 10:41 app_t1_2017-03-19.sql.gz
-rw
-r
--r-- 1 root root 130 Mar 19 10:36 mysql_2017-03-19.sql.gz
-rw
-r
--r-- 1 root root 130 Mar 19 10:41 mysql_columns_priv_2017-03-19.sql.gz
-rw
-r
--r-- 1 root root 130 Mar 19 10:41 mysql_db_2017-03-19.sql.gz
-rw
-r
--r-- 1 root root 130 Mar 19 10:41 mysql_engine_cost_2017-03-19.sql.gz
-rw
-r
--r-- 1 root root 130 Mar 19 10:41 mysql_event_2017-03-19.sql.gz
-rw
-r
--r-- 1 root root 130 Mar 19 10:41 mysql_func_2017-03-19.sql.gz
-rw
-r
--r-- 1 root root 130 Mar 19 10:41 mysql_general_log_2017-03-19.sql.gz
-rw
-r
--r-- 1 root root 130 Mar 19 10:41 mysql_gtid_executed_2017-03-19.sql.gz
-rw
-r
--r-- 1 root root 130 Mar 19 10:41 mysql_help_category_2017-03-19.sql.gz
-rw
-r
--r-- 1 root root 130 Mar 19 10:41 mysql_help_keyword_2017-03-19.sql.gz
-rw
-r
--r-- 1 root root 130 Mar 19 10:41 mysql_help_relation_2017-03-19.sql.gz
-rw
-r
--r-- 1 root root 130 Mar 19 10:41 mysql_help_topic_2017-03-19.sql.gz
-rw
-r
--r-- 1 root root 130 Mar 19 10:41 mysql_innodb_index_stats_2017-03-19.sql.gz
-rw
-r
--r-- 1 root root 130 Mar 19 10:41 mysql_innodb_table_stats_2017-03-19.sql.gz
-rw
-r
--r-- 1 root root 130 Mar 19 10:41 mysql_ndb_binlog_index_2017-03-19.sql.gz
-rw
-r
--r-- 1 root root 130 Mar 19 10:41 mysql_plugin_2017-03-19.sql.gz
-rw
-r
--r-- 1 root root 130 Mar 19 10:41 mysql_proc_2017-03-19.sql.gz
-rw
-r
--r-- 1 root root 130 Mar 19 10:41 mysql_procs_priv_2017-03-19.sql.gz
-rw
-r
--r-- 1 root root 130 Mar 19 10:41 mysql_proxies_priv_2017-03-19.sql.gz
-rw
-r
--r-- 1 root root 130 Mar 19 10:41 mysql_server_cost_2017-03-19.sql.gz
-rw
-r
--r-- 1 root root 130 Mar 19 10:41 mysql_servers_2017-03-19.sql.gz
-rw
-r
--r-- 1 root root 130 Mar 19 10:41 mysql_slave_master_info_2017-03-19.sql.gz
-rw
-r
--r-- 1 root root 130 Mar 19 10:41 mysql_slave_relay_log_info_2017-03-19.sql.gz
-rw
-r
--r-- 1 root root 130 Mar 19 10:41 mysql_slave_worker_info_2017-03-19.sql.gz
-rw
-r
--r-- 1 root root 130 Mar 19 10:41 mysql_slow_log_2017-03-19.sql.gz
-rw
-r
--r-- 1 root root 130 Mar 19 10:41 mysql_tables_priv_2017-03-19.sql.gz
-rw
-r
--r-- 1 root root 130 Mar 19 10:41 mysql_time_zone_2017-03-19.sql.gz
-rw
-r
--r-- 1 root root 130 Mar 19 10:41 mysql_time_zone_leap_second_2017-03-19.sql.gz
-rw
-r
--r-- 1 root root 130 Mar 19 10:41 mysql_time_zone_name_2017-03-19.sql.gz
-rw
