备份的目的
1、做灾难恢复：对损坏的数据进行恢复和还原
2、需求改变：因需求改变而需要把数据还原到改变以前
3、测试：测试新功能是否可用
备份需要考虑的问题
1、可以容忍丢失多长时间的数据丢失；
2、恢复数据要在多长时间内完；
3、恢复的时候是否需要持续提供服务；
4、恢复的对象，是整个库，多个表，还是单个库，单个表。
MySQL备份的类型
按照备份时对数据库的影响范围分为
Hot backup(热备) Cold Backup（冷备）Warm Backup（温备）
- Hot backup：指在数据库运行中直接备份，对正在运行的数据库没有任何影响。（Online Backup），官方手册称为在线备份。（备份的同时，业务不受影响）
- Cold Backup：指在数据库停止的情况下进行备份(Offline Backup) ，官方手册称为离线备份。（需要关mysql服务，读写请求均不允许状态下进行）
- Warm Backup：备份同样在数据库运行时进行，但仅支持读请求，不允许写请求；例如，加一个读锁以保证备份数据的一致性。（服务在线，但仅支持读请求，不允许写请求）按照备份后文件内容
逻辑备份
指备份后的文件内容是可读的，通常为文本文件，内容一般是SQL语句，或者是表内的实际数据，如mysqldump和SELECT * INTO OUTFILE的方法，一般适用于数据库的升级和迁移，恢复时间较长
物理文件备份
对数据库物理文件（如数据文件、日志文件等）的备份，数据库既可以处于运行状态（mysqlhotcopy 、ibbackup、xtrabackup这类工具），也可以处于停止状态（如cp、tar等），恢复时间较短。
按照备份数据库的内容来分
完全备份：每次对数据进行完整的备份。可以备份整个数据库，包含用户表、系统表、索引、视图和存储过程等所有数据库对象。但它需要花费更多的时间和存储空间，所以，做一次完全备份的周期要长些。完全是其他类型的基础。
差异备份：在上一次完全备份基础上，对更新的数据进行备份。因为只备份数据库部分的内容。它比完全备份小，因为只包含自上次完全备份以来所改变的数据。它的优点是存储和恢复速度快。
增量备份：在上次备份的基础上，对更新的数据进行备份
日志备份：二进制日志备份。
建议的备份策略
完全+增量+二进制日志
完全+差异+二进制日志
逻辑备份工具mysqldump
mysqldump是MySQL数据库自带的一款命令行工具，mysqldump属于单线程，功能是非常强大的，不仅常被用于执行数据备份任务，甚至还可以用于数据迁移。
Mysqldump是mysql自带的逻辑备份工具，它的备份原理是，通过协议连接到mysql数据库，将数据转换成标准SQL语句（一堆 CREATE , DROP ，INSERT等语句）；但我们需要还原时，只要执行这些语句即可将对应的数据还原。
mysqldump优缺点
优点
- 备份粒度相当灵活，既可以针对整个MySQL服务，也可以只备份某个或者某几个DB，或者还可以指定只备份某个或者某几个表对象，甚至可以实现只备份表中某些符合条件的记录（-w, --where: 只导出符合条件的记录）。缺点
- 当数据是浮点数时，会出现精度丢失。
- Mysqldump的备份过程属于逻辑备份，备份速度、恢复速度与物理备份工具相比较慢，而且mysqldump备份的过程是串行化的，不会并行的进行备份，当数据量较大时，一般不会使用mysqldump进行备份，因为效率较低。
- Mysqldump对innodb存储引擎支持热备，innodb支持事务，我们可以基于事务通过mysqldump对数据库进行热备（--single-transaction选项）。
- Mysqldump对myisam存储引擎只支持温备，通过mysqldump对使用myisam存储引擎的表进行备份时，最多只能实现温备，因为在备份时会对备份的表请求一个读锁，当备份完成后，锁会被释放。导出数据
语法： mysqldump [options] [db_name [tbl_name ...]]>导出的文件名.sql-?, --help: 显示帮助信息，英文的；
-u, --user: 指定连接的用户名；
-p, --password: 指定用户的密码，可以交互输入密码；
-S , --socket: 指定socket文件连接，本地登录才会使用。
-h, --host: 指定连接的服务器名称或者IP。
-P, --port=: 连接数据库监听的端口。
--default-character-set: 设置字符集，默认是UTF8。
-A,--all-databases: 导出所有数据库。不过默认情况下是不会导出information_schema库。
-B, --databases: 导出指定的某个/或者某几个数据库，参数后面所有名字都被看作数据库名，用空格隔开，包含CREATE DATABASE创建库的语句。
--tables**: 导出指定表对象，参数格式为“库名 表名”，默认该参数将覆盖-B参数。
-w, --where**: 只导出符合条件的记录。
-l, --lock-tables: 默认参数，锁定读取的表对象，想导出一致性备份的话最好使用该参数，但会导致无法对表执行写入操作。
--single-transaction: 该选项在导出数据之前提交一个BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于innoDB存储引擎。
在InnoDB导出时会建立一致性快照，在保证导出数据的一致性前提下，又不会堵塞其他会话的读写操作。指定这个参数后，其他连接不能执行ALTER TABLE、DROP TABLE 、RENAME TABLE、TRUNCATE TABLE这类语句，事务的隔离级别无法控制DDL语句。本选项和--lock-tables 选项是互斥的，使用参数--single-transaction会自动关闭该选项。
-d, --no-data: 只导出表结构，不导出表数据。
-t, --no-create-info: 只导出数据，而不添加CREATE TABLE 语句。
-f, --force: 即使遇到SQL错误，也继续执行。
-F, --flush-logs: 在执行导出前先刷新二进制日志文件，一般来说，如果是全库导出，建议先刷新日志文件，否则就不用了。
-x, --lock-all-tables: 在导出任务执行期间锁定所有数据库中的所有表，以保证数据的一致性。这是一个全局锁定，并且自动关闭--single-transaction 和--lock-tables 选项。这个参数副作用比较大，这是全库锁定，备份执行过程中，该库无法进行读写操作，不是所有业务场景都能接受的。请慎用。
-n, --no-create-db: 不生成建库的语句CREATE DATABASE … IF EXISTS，即使指定---all-databases或--databases这类参数。
--triggers: 导出表的触发器脚本，默认就是启用状态。使用--skip-triggers禁用它。
-R, --outines: 导出存储过程以及自定义函数。导入数据
mysql -uroot -p123456 < books.sql
source 数据路径二进制日志binlog
二进制日志概述
mysql的二进制日志记录着数据库的所有增、删、改等操作日志(前提是要在自己的服务器上开启binlog)，还包括了这些操作的执行时间。为了显示这些二进制内容，我们可以使用mysqlbinlog命令来查看
Binlog的用途
主从同步
恢复数据库
查看binlog是否开启
show variables like 'log_bin%';开启二进制日志
log-bin=/data/mysql/log/mysql_bin
log-bin-index=/data/mysql/log/mysql_bin.index
server-id=1
binlog其他参数
1、“max-binlog-size”设置binlog 的最大存储上限，一般设置为512M或1G,一般不能超过1G。当日志达到该上限时，MySQL 会重新创建一个日志开始继续记录。不过偶尔也有超出该设置的binlog 产生，一般都是因为在即将达到上限时，产生了一个较大的事务，为了保证事务安全，MySQL 不会将同一个事务分开记录到两个binlog 中。
2、“binlog-do-db=db_name”参数明确告诉MySQL，需要对某个（db_name）数据库记录binlog，如果有了“binlog-do-db=db_name”参数的显式指定，MySQL 会忽略针对其他数据库执行的sql query，而仅仅记录针对指定数据库执行的sql query。
3、“binlog-ignore-db=db_name”与“binlog-do-db=db_name”完全相反，它显式指定忽略某个（db_name）数据库的binlog 记录，当指定了这个参数之后，MySQL 会记录指定数据库以外所有的数据库的binlog。
4、binlog-cache-size ：一个事务，在没有提交（uncommitted）的时候，产生的日志，记录到Cache中；等到事务提交（committed）的时候，则把日志持久化到磁盘。一般来说，如果我们的数据库中没有什么大事务，写入也不是特别频繁，2MB～4MB是一个合适的选择。但是如果我们的数据库大事务较多，写入量比较大，可与适当调高binlog_cache_size，默认值32768。 查看binlog-cache-size的默认值：show variables like 'binlog_cache_size';
5、binlog-format= {ROW|STATEMENT|MIXED}参数指定二进制日志的类型 mysql复制主要有三种方式：基于SQL语句的复制(statement-based replication, SBR)，基于行的复制(row-based replication, RBR)，混合模式复制(mixed-based replication, MBR)。对应的，binlog的格式也有三种：STATEMENT，ROW，MIXED。
STATEMENT模式（SBR）
每一条修改数据的sql语句会记录到binlog中。优点是并不需要记录每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如LOAD_FILE()、UUID()、USER()、FOUND_ROWS()等函数的语句无法被复制)
ROW模式（RBR）
不记录每条sql语句的信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，让日志暴涨。
MIXED模式（MBR）
以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。即交替使用行和语句、由mysql服务器自行判断。
其中基于行的定义格式数据量会大一些，但是可以保证数据的精确性。
binlog常用命令
#查看binlog日志列表
show binary logs;
#查看当前使用的二进制文件及日志文件中事件当前位置
show master status;
#刷新日志文件
flush logs;
#清空所有的二进制日志文件
reset master;
查看binlog日志文件的内容
二进制日志的定义方式为二进制格式；使用此格式可以存储更多的信息，并且可以使写入二进制日志的效率更高。但是不能直接使用文件内容查看命令打开并查看二进制日志。
查看到二进制日志文件使用mysqlbinlog
语法格式： mysqlbinlog [options] log_file ...-v：查看执行SQL语句的详细信息，该选项会将事件重构成被注释掉的伪SQL语句，如果想看到更详细的信息可以使用-vv
实战：
mysqldump全库备份、binlog的增量备份与还原
mysqldump全备+binlog日志增备
mysqldump 全备我们需要备份的数据库
mysqldump -uroot -p123456 --single-transaction --flush-logs -B backup > /opt/backup/all_backup_$(date +%Y-%m-%d-%H:%M:%S)
插入测试数据增备binlog日志(拷贝全备刷新后的binlog日志)
mysql -uroot -p123456 -e 'flush logs;'
cp mysql_bin.000003 /opt/backup/模拟故障还原
#全备还原
mysql> source all_backup_$(date +%Y%m%d%H%M%S)
#增备还原
mysqlbinlog -v mysql_bin.000003 > mysql_bin.000003.sql
mysql> source mysql_bin.000003.sql
利用二进制日志实现基于时间点或位置的恢复
利用二进制日志可实现基于时间点和位置的回复。例如，由于误操作删除了一行数据，这时完全恢复是没有用的，因为日志里面还存在误操作的语句，我们需要的是恢复到误操作前的状态，然后跳过误操作的语句，再恢复后面操作的语句。
基于时间点的恢复：
--stop-datetime：表示从二进制日志中读取指定时间之前的日志事件，后面误操作的语句不执行
--start-datetime：表示从二进制日志中读取指定时间之后的日志事件。
注意：使用基于时间点的恢复，可能会出现在一个时间点里同时存在正确的操作和存在错误操作。所以基于位置是一种更为精确的恢复方式
mysqlbinlog -v --start-datetime='2024-12-19 20:44:14' mysql_bin.000003 > mysql_bin.000004.sql
基于位置的恢复
--start-position 从二进制日志中读取指定position 事件位置作为开始。
--stop-position 从二进制日志中读取指定position 事件位置作为事件截至。
mysqlbinlog -v --stop-position=595 mysql_bin.000003 > mysql_bin.000003.sql
mysqlbinlog -v --start-position=713 mysql_bin.000003 >> mysql_bin.000003.sql
写个自动备份MySQL数据库shell脚本
生产环境中Mysql数据库的备份是周期性重复的操作，所以通常是要编写脚本实现，通过crond计划任务周期性执行备份脚本
制定企业备份策略的思路
制定企业备份策略要根据企业数据库的实际读写的频繁性与数据的重要性进行
数据更新频繁，则应该进行较为频繁的备份
数据较为重要，则在有适当更新时进行备份
在数据库压力小的时间段进行备份。
mysqldump备份方案
周日凌晨1点全库备份
周一到周六凌晨4点增量备份一次
设置crontab任务，每天执行备份脚本
#!/bin/bash
#2024年12月17日 15:11:31
#mysql backup
#######################
binlogdir=/data/mysql/log
mysqlbackup=/data/mysql/backup/
date1=$(date +%F)
database="test"
backup_log="/data/mysql/backup/backup.log"
backup_user="root"
user_password="123456"
#全备
qb(){
if [ ! -d $mysqlbackup ];then
mkdir $mysqlbackup
fi
tar -zcf /opt/mysql_all_$date1.tar $mysqlbackup --remove-files &> /dev/null
if [ $? -eq 0 ];then
if [ ! -d $mysqlbackup ];then
mkdir $mysqlbackup
fi
echo -e "\033[40;32m mysql_all_$date1.tar 备份到/opt目录成功\033[0m" &>>$backup_log
else
echo -e "\033[40;31m mysql_all_$date1.tar 备份失败，请检查\033[0m" &>>$backup_log
exit
fi
mysqldump -u${backup_user} -p${user_password} --single-transaction --flush-logs -B ${database} > ${mysqlbackup}/${database}_${date1}.sql
if [ $? -eq 0 ];then
echo -e "\033[40;32m全备成功\033[0m" &>>$backup_log
else
echo -e "\033[40;31m全备失败\033[0m" &>>$backup_log
exit
fi
num=$[$(cat $binlogdir/mysql_bin.index | wc -l) - 1]
if [ ! -f $binlogdir/num.txt ];then
touch $binlogdir/num.txt
fi
echo "$num" > $binlogdir/num.txt
}
#增量
zl(){
if [ ! -d $mysqlbackup/binlog ];then
mkdir -p $mysqlbackup/binlog
fi
num=$(cat $binlogdir/num.txt)
mysql -u${backup_user} -p${user_password} -e "flush logs" &> /dev/null
aa=$(cat $binlogdir/mysql_bin.index | wc -l)
bb=${num}+1
#for i in $(cat $binlogdir/mysql_bin.index)
#do
# binlogname=$(basename $i)
#done
while [ $bb -lt $aa ]
do
if [ $bb -gt $num ];then
binlogname=$(sed -n "${bb}p" $binlogdir/mysql_bin.index)
cp $binlogname ${mysqlbackup}/binlog
if [ $? -eq 0 ];then
echo -e "\033[40;32m${binlogname}增量备份成功\033[0m" &>>$backup_log
else
echo -e "\033[40;31m${binlogname}增量备份失败请检查\033[0m" &>>$backup_log
exit
fi
fi
bb=$((bb+1))
done
num=$[$(cat $binlogdir/mysql_bin.index | wc -l) - 1]
if [ ! -f $binlogdir/num.txt ];then
touch $binlogdir/num.txt
fi
echo "$num" > $binlogdir/num.txt
}
case "$1" in
all)
qb
;;
add)
zl
;;
*)
echo $"Usage: $0 {all(全备)|add(增量)}"
;;
esac要求每周日凌晨2：30做一次全备，并在3：30的时候将全备份发送到远程ftp服务器，每隔两小时做一次差异备份
设定任务计划：
1 */2 * * * /backup/mysql_backup_full.sh all &
30 1 * * 7 /backup/mysql_backup_full.sh add &
30 3 * * 7 /backup/ftp.sh &