Linux实战之MySQL数据库——数据库备份

备份介绍

数据丢失原因大概分为以下几种：
硬件故障、软件故障、自然灾害、黑客攻击、误操作 (占比最大)

定制数据备份策略

要根据不同的应用场景定制符合特定环境中的数据备份策略

能够容忍丢失多少数据
恢复数据需要多长时间
需要恢复哪一些数据

备份类型

数据库备份方式	备份时的影响
热备份	数据库的读写操作均不受影响
温备份	数据库的读操作可以执行，但是不能执行写操作
冷备份	数据库不能进行读写操作，即数据库要下线

MySQL中进行不同方式的备份还要考虑存储引擎是否支持

存储引擎	支持备份方式
MyISAM	热备× 温备√ 冷备√
InnoDB	热备√ 温备√ 冷备√

数据库备份方式	备份时的影响
物理备份	一般就是通过tar、cp等命令直接打包复制数据库的数据文件达到备份的效果
逻辑备份	一般就是通过特定工具从数据库中导出数据并另存备份(逻辑备份会丢失数据精度)

物理备份： 直接复制数据库文件，适用于大型数据库环境，不受存储引擎的限制，但不能恢复到异构系统中如Windows。
逻辑备份：备份的是建表、建库、插入等操作所执行SQL语句，适用于中小型数据库，效率相对较低

逻辑备份的优点

1. 在备份速度上两种备份要取决于不同的存储引擎，物理备份的还原速度非常快，但是物理备份的最小粒度只能做到表，逻辑备份保存的结构通常都是纯ASCII的，所以可以使用文本处理工具来处理
2. 逻辑备份有非常强的兼容性，而物理备份则对版本要求非常高
3. 逻辑备份对保持数据的安全性有保证

逻辑备份的缺点

1. 逻辑备份要对RDBMS产生额外的压力，而物理备份无压力
2. 逻辑备份的结果可能要比源文件更大，所以很多人都对备份的内容进行压缩，逻辑备份可能会丢失浮点数的精度信息

备份步骤

1.确定备份的内容

数据
二进制日志
InnoDB事务日志
代码(存储过程、存储函数、触发器、事件调度器)
服务器配置文件

2.明确备份工具

工具	介绍
mysqldump	逻辑备份工具，适用于所有的存储引擎，支持温备、完全备份、部分备份、对于InnoDB存储引擎支持热备
cp, tar 等归档复制工具	物理备份工具，适用于所有的存储引擎，冷备、完全备份、部分备份
lvm2 snapshot	几乎热备，借助文件系统管理工具进行备份
mysqlhotcopy	名不副实的的一个工具，几乎冷备，仅支持MyISAM存储引擎
xtrabackup	一款非常强大的InnoDB/XtraDB热备工具，支持完全备份、增量备份

3.设计备份策略

不同场景制定不同的备份策略对数据库进行备份，一般情况下，备份策略一般为以下几种：
（1）直接cp，tar复制数据库文件 适合数据量较小的场景
（2）mysqldump+复制BIN LOGS 适合数据量适中的场景，增量备份
（3）lvm2快照+复制BIN LOGS 适合数据量一般、而又不过分影响业务运行的场景，增量备份
（4）xtrabackup 适合数据量很大、而又不过分影响业务运行的场景， 增量备份或差异备份

实战：模拟全量与二进制增量备份数据恢复

恢复sql文件的方法

方法一：基于位置点的增量恢复

指定开始位置到结束位置
--start-position=4 --stop-position=510
指定开始位置到文件结束
--start-position=4
从文件开始位置到指定结束位置
--stop-position=510

方法二：基于时间点的增量恢复

指定开始时间到结束时间
--start-datetime='2014-10-45 01:46:23' --stop-datetime='2014-10-45 03:46:23'
指定开始时间到文件结束
--start-datetime='2014-10-45 01:46:23'
从文件开头到指定结束时间
--stop-datetime='2014-10-45 03:46:23'

可以在转化成sql语句后，修改.sql，删除DROP语句，再全部导入

增量备份前提

（1）my.cnf开启MySQL log-bin日志功能，若没有开启则修改配置文件log-bin = /usr/local/mysql/logs/mysql-bin.log，重启MySQL
查看数据库是否开启了二进制日志打印功能（log_bin为ON表示开启）

mysql> show variables like 'log_%';
+----------------------------------------+----------------------------------------+
| Variable_name                          | Value                                  |
+----------------------------------------+----------------------------------------+
| log_bin                                | ON                                     |
| log_bin_basename                       | /usr/local/mysql/data/binlog           |
+----------------------------------------+----------------------------------------+

（2）存在一个完全备份，生产环境一般凌晨某个时刻进行全备
mysqldump -uroot -p --default-character-set=gbk --single-transaction -F -B school |gzip >/server/backup/school_$(date +%F).sql.gz
InnoDB表在备份时，通常启用选项 --single-transaction(热备) 来保证备份的一致性

模拟凌晨12:00数据库增量备份

mysqldump数据库备份

mysql> create database xingyun DEFAULT CHARACTER SET utf8;
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> use xingyun
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
COLLATE=utf8_general_ci;

mysql> INSERT INTO `Student` VALUES 
(1, '陆亚', '男', 24, '计算机网络'),
(2, 'tom', '男', 26, '英语'),
(3, '张阳', '男', 21, '物流管理'),
(4, 'alex', '女', 22, '电子商务');
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0

[root@mysql ~]# mkdir -p /server/backup
[root@mysql ~]# date -s "20200229"
Sat Feb 29 00:00:00 CST 2020

模拟半夜零点手工全备份
[root@mysql ~]# mysqldump -uroot -pZhao123@com --default-character-set=utf8 --single-transaction -F -B xingyun -e | gzip >/server/backup/mysql_backup_`date +%F`.sql.gz

备份后插入数据
mysql> INSERT INTO xingyun.Student values (0005,'xumubin','男',29,'中文专业'), (0006,'wangzhao','男',21,'导弹专业');

模拟用户破坏数据
mysql> drop database xingyun;

（1）检查凌晨备份
（2）检查全备份后的所有binlog

[root@mysql ~]# ls -lrt /usr/local/mysql/data/binlog.*

（3）立即刷新并备份出binlog

[root@mysql ~]# mysqladmin -uroot -pZhao123@com flush-logs
[root@mysql ~]# cp /usr/local/mysql/data/binlog.* /server/backup/

（4）恢复binlog生成sql语句

[root@mysql backup]# mysqlbinlog binlog.000006 >bin.log
查看出问题的节点
[root@mysql backup]# vim bin.log
恢复sql文件
[root@mysql backup]# mysqlbinlog binlog.000006 --start-position=4 --stop-position=510 -r backup.sql

（5）恢复凌晨备份

[root@mysql backup]# gunzip mysql_backup_2020-02-29.sql.gz
[root@mysql backup]# mysql -uroot -pZhao123@com -e 'create database xingyun'
[root@mysql backup]# mysql -uroot -pZhao123@com xingyun< mysql_backup_2020-02-29.sql

（6）恢复增量备份

[root@mysql backup]# mysql -uroot -pZhao123@com xingyun < time.sql

实现自动化备份（数据库小）

备份计划：

1. 什么时间 2:00
2. 对哪些数据库备份
3. 备份文件放的位置

[root@mysql ~]# cat /mysql_back.sql
back_dir=/backup
back_file=`date +%F`_all.sql
user=root
pass=123
if [ ! -d /backup ];then
mkdir -p /backup
fi
# 备份并截断日志
mysqldump -u${user} -p${pass} --lock-all-tables --routines --events --triggers --master-data=2 --flush-logs --all-databases > /$back_dir/$back_file
# 只保留最近一周的备份
cd $back_dir
find . -mtime +7 -exec rm -rf {} \;

[root@mysql ~]# crontab -e
#数据库备份
0 2 * * * root source /etc/profile;/usr/bin/sh /mysql_back.sql

数据库备份实例

（1）备份所有数据库
-A --all-databases
mysqldump -uroot -p -A > sqlfile.sql
（2）备份除系统自带库的所有数据库
mysql -uroot -pZhao123@com -e "show databases;"|tail -n +2|grep -Ev "sys|mysql|information_schema|performance_schema"|xargs mysqldump -uroot -ppassword -B > mysql_dump.sql

复制数据文件 cp或tar

（1）停止服务

systemctl stop mysqld
netstat -lnupt | grep 3306

（2）备份数据文件

cd /usr/local/mysql/
mkdir -p /server/backup
tar czf /server/backup/all. date +%F .tar.gz *

（3）将备份文件拷贝到目标服务器 scp /server/backup/all. date +%F .tar.gz 192.168.95.12:/tmp

（4）目标服务器停止服务 systemctl stop mysqld
（5）解压文件至目标服务器数据文件夹，修改权限

tar xf /tmp/all.tar.gz -C /usr/local/mysql/data
chown -R mysql.mysql /usr/local/mysql/data

（6）目标服务器启动服务测试 systemctl start mysqld