专职DBA-mysqldump企业级备份恢复
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