MySQL备份的分类

从物理与逻辑的角度，备份可分为

• 物理备份：对数据库操作系统的物理文件（如数据文件、日志文件等）的备份

  • 物理备份又可以分为脱机备份（冷备份）和联机备份（热备份）
    • 冷备份：是在关闭数据库的时候进行的
    • 热备份：数据库处于运行状态，这种备份方法依赖于数据库的日志文件

• 逻辑备份：对数据库逻辑组件（如表等数据库对象）的备份

从数据库的备份策略角度，备份可分为

• 完全备份：每次对数据进行完整的备份
• 差异备份：备份那些自从上次完全备份之后被修改过的文件
• 增量备份：只有那些在上次完全备份或者增量备份后被修改的文件才会被备份

MySQL完全备份

• 完全备份是对整个数据库的备份、数据库结构和文件结构的备份
• 完全备份保存的是备份完成时刻的数据库
• 完全备份是增量备份的基础

完全备份的优点

• 备份与恢复操作简单方便

完全备份的缺点

• 数据存在大量的重复
• 占用大量的备份空间
• 备份与恢复时间长

在生产环境中，完全备份和增量备份都会使用，需要制定合理高效的方案达到备份数据的目的，避免数据丢失造成的严重的后果

MySQL数据库的完全备份可以采用用多种方式

1. 直接打包数据库文件夹，如/usr/local/mysql/data

tar -zcvf data.tar.gz data

[root@maomao mysql]# ls
bin  COPYING  data  data.tar.gz  docs  include  lib  man  README  share  support-files

2. 使用专用备份工具 mysqldump
   MySQL自带的备份工具，相当方便对MySQL进行备份
   通过该命令工具可以将指定的库、表或全部的库导出为SQL脚本，在需要恢复时可进行数据恢复

mysqldump备份库

mysqldump命令对单个库进行完全备份

语法：
mysqldump -u 用户名 -p [密码] [选项] [数据库名]  > /备份路径/备份文件名

mysqldump -uroot -p school > /home/school.sql
Enter password: 

[root@maomao home]# ls
maomao  school.sql

mysqldump命令对多个库进行完全备份

语法：
mysqldump -u 用户名 -p [密码] [选项]  --databases  库名 1  [库名2] …  >  /备份路径/备份文件名

mysqldump -uroot -p --databases school maomao > /home/school-maomao.sql
Enter password: 

[root@maomao home]# ls
maomao  school-maomao.sql

对所有库进行完全备份

语法：
mysqldump -u 用户名 -p [密码] [选项]  --all-databases  >  /备份路径/备份文件名

mysqldump -uroot -p --all-databases > /home/all-data.sql

[root@maomao home]# ls
all-data.sql  maomao  school-maomao.sql

对某个特定的表进行备份

语法：
mysqldump -u 用户名 -p [密码] [选项] 数据库名  表名 > /备份路径/备份文件名 

mysqldump -uroot -p school student >/home/school_student.sql
Enter password: 

[root@maomao home]# ls
all-data.sql  maomao  school-maomao.sql  school_student.sql

恢复数据库

使用mysqldump命令导出的SQL备份脚本，在进行数据恢复时可使用以下方法导入

• source命令
• mysql命令

使用source恢复数据库

• 登录到MySQL数据库
• 执行source 备份sql脚本的路径

因为之前已经备份过了 模拟数据丢失 然后恢复
直接把库删了
mysql [school]>drop database school;
Query OK, 6 rows affected (0.01 sec)

进入sql脚本的目录 登录mysql之后进行恢复
mysql [school]>source school.sql

mysql [school]>show tables;
+------------------+
| Tables_in_school |
+------------------+
| category         |
| grade            |
| result           |
| student          |
| subject          |
| t_student        |
+------------------+
6 rows in set (0.00 sec)

使用mysql命令恢复库数据

语法：
mysql -u 用户名 -p [密码] 库名 < 库备份脚本的路径

先把maomao库删了
mysql [(none)]>drop database maomao;
Query OK, 1 row affected (0.01 sec)

然后创建新的maomao库
create database maomao;

恢复
mysql -uroot -p maomao </home/maomao.sql

使用mysql命令恢复表数据
恢复表数据同样可以使用source或者mysql命令
source恢复表的操作与恢复库的操作相同

当备份文件中只包含表的备份，而不包括创建库的语句时，必须指定库名，且目标库必须存在

语法：
mysql -u 用户名 -p [密码] 库名 < 表备份脚本的路径

mysql -uroot -p school </home/school_student.sql

MySQL备份思路

• 定期实施备份，制定备份计划或者策略，并严格遵守
• 除了进行完全备份，开启MySQL服务器的日志功能是很重要的
• 完全备份加上日志，可以对MySQL进行最大化还原
• 使用统一的和易理解的备份文件名称
  不要使用backup1、backup2等这样没有意义的名字
  推荐使用库名或者表名加上时间的命名规则

全量备份的shell脚本

首先先创建一个专门备份的用户并且授权

create user backup identified by '123';

grant all on school.* to backup@'localhost' identified by "123";

#!/bin/bash
#auto backup mysql db
#before you back ,please grant to the user backup
#grant all on discuz.* to backup@'localhost' identified by "123456"
#flush privileges

BAK_DIR=/back
BAK_FILE=/back/full_`date +%Y%m%d`
MYSQLDB=school
MYSQLUSR=backup
MYSQLPWD=123
MYSQLCMD=/usr/bin/mysqldump

if [ $UID -ne 0 ];then
	echo "You must to be use root"
	exit 1
fi

if [ ! -d $BAK_DIR ];then
	mkdir -p $BAK_DIR && echo -e "\033[32mcreate the backdir success\033[0m"
else
	echo "THIS $BAK_DIR IS EXSITS..."
fi

mysqldump -u$MYSQLUSR -p$MYSQLPWD $MYSQLDB > $BAK_FILE.$MYSQLDB.sql

if [ $? -eq 0 ];then
	echo -e "\033[32mTHE mysql backup $MYSQLDB successful!\033[0m"
else
	echo -e "\033[32mTHE mysql backup $MYSQLDB faild!\033[0m"
fi

# 删除超过日期的备份
find /back -mtime +30 |xargs rm -rf 

执行脚本
[root@maomao home]# bash mysql.sh 
create the backdir success
mysqldump: [Warning] Using a password on the command line interface can be insecure.
THE mysql backup school successful!

备份成功
[root@maomao back]# ls
full_20210413.school.sql

MySQL增量备份

• 增量备份就是备份自上一次备份之后增加或变化的文件或者内容

增量备份的特点

• 没有重复数据，备份量不大，时间短
• 恢复麻烦：需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且要对所有增量备份进行逐个反推恢复

MySQL没有提供直接的增量备份方法
可以通过MySQL提供的==二进制日志（binary logs）==间接实现增量备份

MySQL二进制日志对备份的意义

• 二进制日志保存了所有更新或者可能更新数据库的操作
• 二进制日志在启动MySQL服务器后开始记录，并在文件达到max_binlog_size所设置的大小或者接收到flush logs命令后重新创建新的日志文件
• 只需定时执行flush logs方法重新创建新的日志，生成二进制文件序列，并及时把这些旧的日志保存到安全的地方就完成了一个时间段的增量备份

开启增量备份

实现增量备份之前需要开启二进制日志功能，开启而仅仅只日志功能有两种方式：

mysql [(none)]>show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

在没开启之前 log_bin的value是OFF

1. 在MySQL配置文件的 [mysqld] 项中加入log-bin=filepath

   [mysqld]
   log_bin=mysql-bin
   log_bin_index=mysql-bin.index
   
   重启mysql
   service mysqld restart
   再次查看
   mysql [(none)]>show variables like 'log_bin';
   +---------------+-------+
   | Variable_name | Value |
   +---------------+-------+
   | log_bin       | ON    |
   +---------------+-------+
   1 row in set (0.00 sec)
   
   

2. 使用mysqld --log-bin=filepath

   /etc/init.d/mysqld restart --log-bin=mysql-bin
   

开启二进制日志功能后 在mysql目录的data里面会出现日志文件

确定字符集使用的是utf-8的形式，避免出现乱码问题

mysql [(none)]>show variables like '%char%';
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | utf8                             |
| character_set_connection | utf8                             |
| character_set_database   | utf8                            |
| character_set_filesystem | binary                           |
| character_set_results    | utf8                             |
| character_set_server     | utf8                            |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+

如果不是utf8	编辑my.cnf文件
添加
[mysqld]
character-set-server=utf8 
collation-server=utf8_general_ci 

然后使用mysqldump命令，对数据库或者表进行全量备份
每天进行增量备份操作，使用mysqladmin的选项flush-logs生成新的二进制文件

生成新的二进制文件
mysqladmin -uroot -p123 flush-logs

添加新的数据

mysql [school]>insert into student (studentno,loginpwd,studentname,gender,gradeid,phone,address,borndate,email,identitycard) 
values (1008,'123456','猪猪','男',1,'13800002009','广东深圳','1999-1-30','text250@qq.com','123456199001011999');
Query OK, 1 row affected (0.00 sec)

insert into student (studentno,loginpwd,studentname,gender,gradeid,phone,address,borndate,email,identitycard) 
values (1009,'123456','小宇','男',1,'13800002549','山西太原','1998-8-3','text387@qq.com','123456199001011387');

查看二进制日志
注意：如果遇到乱码可以添加参数进行解码

mysqlbinlog mysql-bin.000002 

解码
mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000002

解码后：

把二进制文件复制到保存备份的目录，完成增量备份

cp mysql-bin.000002 /back

ls /back
full_20210413.school.sql  mysql-bin.000002

增量恢复三种方式

1. 一般恢复
2. 基于位置恢复
   就是将某个起始时间的二进制日志导入数据库中，从而跳过某个发生错误的时间点实现数据的恢复
3. 基于时间点恢复
   使用基于时间点的恢复，可能会出现在一个时间点里既同时存在正确的操作又存在错误的操作，所以我们需要一种更为精确的恢复方式

一般恢复

mysqlbinlog [--no-defaults] 增量备份文件 | mysql -u 用户名 -p

增量恢复之前需要全量恢复
mysql -uroot -p school </back/full_20210413.school.sql 

mysqlbinlog /back/mysql-bin.000002 |mysql -uroot -p

恢复成功

基于位置的恢复

恢复数据到指定位置
mysqlbinlog --stop-position=’操作 id’ 二进制日志 |mysql -u 用户名 -p 密码

从指定的位置开始恢复数据
mysqlbinlog --start-position=’操作 id’ 二进制日志 |mysql -u 用户名 -p 密码

恢复数据到指定位置

使用delete删除插入的两条数据
delete from student where identitycard=123456199001011999;

delete from student where identitycard=123456199001011387;

需要去查看一下备份日志里的位置点

找到位置点为499

mysqlbinlog --stop-position='499' /back/mysql-bin.000002 |mysql -uroot -p123

验证：
只恢复了499位置点上面的sql

从指定的位置开始恢复数据

先删除数据
delete from student where identitycard=123456199001011999;

跳过第一条数据的499，只恢复后面那条数据

mysqlbinlog --start-position='574' /back/mysql-bin.000002 |mysql -uroot -p123

增量备份的shell脚本

#!/bin/bash

BAK_DIR=/back
MYSQLUSR=root
MYSQLPWD=123

# 刷新二进制日志
mysqladmin -u$MYSQLUSR -p$MYSQLPWD flush-logs

# 获取最近一次的增量备份文件
newlog=`ls -lt /usr/local/mysql/data |grep mysql-bin.0  |awk 'NR==2{print $9}'`
newlog="/usr/local/mysql/data/"$newlog

# 将备份日志保存到相应的目录
cp $newlog $BAK_DIR

# 删除超过日期的备份
find /back -mtime +30 |xargs rm -rf

制定企业备份策略的思路

制定企业备份策略要根据企业数据库的实际读写的频繁性与数据的重要性进行

• 数据更新频繁，则应该进行较为频繁的备份。
• 数据较为重要，则在有适当更新时进行备份
• 在数据库压力小的时候进行全量备份，如一周一次，然后每天进行增量备份，并且实现主从复制架构