https://blog.csdn.net/richardlygo/article/details/81638237

一、数据库备份的分类;

物理备份:指对数据库操作系统的物理文件(数据文件、日志文件)等的备份;


冷备份:必须在数据库关闭的状态下进行备份,能够更好的保证数据库的完整性;

热备份:能够在数据库处于正常运行的情况下备份,能够更高的保证服务的可用性;

逻辑备份:指对数据库的逻辑组件(数据库、表、数据对象)进行备份;

完全备份:将数据进行完整的备份,包含完整的库、表、索引、视图等,需要花费更长的时间

差异备份:备份自上次完全备份到现在发生改变的数据库内容,备份的文件比完整备份的文件小,备份的速度更快;

增量备份:备份至上次完全备份或增量备份后被修改的数据库内容;

二、数据库全量备份及其恢复;

方式一:物理备份(冷备份)

关掉mysql服务

/etc/init.d/mysql stop 或 systemctl stop mysqld


#查看mysql数据地址 
grep -ri 'datadir' /etc/mysql
#显示 
/etc/mysql/mysql.conf.d/mysqld.cnf:datadir        = /var/lib/mysql

#打包备份整个服务器内mysql
tar zcvf /home/dml/mysql-backup-$(date +%F).tar.gz /var/lib/mysql/
方式二:Mysqldump命令(热备份)

三、数据库增量备份及其恢复;

方式一:二进制日志文件增量备份

1.开启二进制日志文件;

mysql 配置文件中去掉log_bin前的注释

vim /etc/mysql/mysql.conf.d/mysqld.cnf
server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log

或者

echo -e "log-bin = /var/log/mysql/mysql_bin \nserver_id = 1\n max_binlog_size = 100M " >>/etc/mysql/my.cnf

重启mysql服务

  1. 查看mysql的二进制日志情况
mysql> show master status;  ##查看mysql的二进制日志情况
mysql> show binary logs; #获取所有binlog文件列表
mysql> flush logs; ##重新建立二进制文件日志,自此刻开始产生一个新编号的binlog日志文件
mysql> reset master; ##清空所有二进制文件日志
mysql> show binlog events in 'mysql-bin.000001'\G; #查看指定binlog文件的内容

#指定查询 mysql-bin.000001这个文件,从pos点:624开始查起,偏移2行(即中间跳过2个),查询10条
mysql> show binlog events in 'mysql-bin.000001' from 624 limit 2,10\G;
  1. 恢复数据库增量备份

从binlog日志恢复数据,恢复命令的语法格式:

mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 数据库名

常用参数选项解释:

--start-position=875 起始pos点
--stop-position=954 结束pos点
--start-datetime="2020-11-10 22:01:08" 起始时间点
--stop-datetime="2020-11-11 22:09:46" 结束时间点
--database=zyyshop 指定只恢复zyyshop数据库(一台主机上往往有多个数据库,只限本地log日志)

不常用选项:

-u --user=name 连接到远程主机的用户名
-p --password[=name] 连接到远程主机的密码
-h --host=name 从远程主机上获取binlog日志
--read-from-remote-server 从某个MySQL服务器上读取binlog日志

#根据position值位置进行恢复;
mysqlbinlog --start-position=1 --stop-position=795 /var/log/mysql/mysql-bin.000001 |mysql -uroot -p123456

#查看二进制文件的内容
sudo mysqlbinlog --no-defaults --base64-output=decode-rows -v /var/log/mysql/mysql-bin.000001

#根据时间点位置进行恢复;-d指定数据库,选项-h指定主机
mysqlbinlog --start-datetime="2020-11-08 18:00:00" --stop-datetime="2020-11-08 10:00:00" -d 数据库名 -h 127.0.0.1 /var/log/mysql/mysql-bin.000001 |mysql -uroot -p123456

#重新建立二进制日志文件
mysqladmin -uroot -p123456 flush-logs

四、生产环境Mysql数据库备份策略;

策略设计思路:

1.数据更新频繁,则应该进行较为频繁的备份;

2.数据较为重要,则在有适当更新时进行备份;

3.在数据库压力小的时段进行全量备份;

备份方案:

1.在每周末的某个时段使用mysqldump进行重要数据库的全量备份(最好备份单个数据库,而不是all);

2.在平时每天的晚上时段将二进制日志文件终止(前提需要根据数据记录的生成数量合理指定单个二进制文件的大小),这样每隔24小时会生成一个二进制日志文件;

3.每周的全量备份文件和每天的二进制日志文件相加,就是这一周内数据库的整体内容;

4.出现故障时,首先恢复全量备份,可以执行mysqlbinlog命令结合实际情况根据position值或者时间点进行恢复,确保万无一失;

全量备份脚本

vim /home/dml/mysql_quan_backup.sh

#!/bin/bash

#############全量备份`cms_database`##########

mkdir /home/dml/quanliang

mkdir /home/dml/quanliang/backup-$(date +%F)/

/usr/bin/mysqldump -uroot -p123456 cms_database >/home/dml/quanliang/backup-$(date +%F)/cms_database.sql

if [ -f /home/dml/quanliang/backup-$(date +%F)/cms_database.sql ];then

  echo "mysql is quan backup success on time-$(date +%F)" >>/var/log/mysql/mysql_quan_backup.log

else

  echo "mysql is quan backup fail on time-$(date +%F)" >>/var/log/mysql/mysql_quan_backup.log

fi

运行全量备份

chmod +x /home/dml/mysql_quan_backup.sh
echo "0 0 * * 7 /home/dml/mysql_quan_backup.sh" >>/var/spool/cron/root
/etc/init.d/cron restart 或 systemctl restart cron
增量备份脚本

==mysqladmin -uroot -p123456 flush-logs 是重新建一个二进制文件==

vim /home/dml/mysql_zeng_backup.sh

#!/bin/bash

#############增量备份cms_database ##########

mkdir /var/log/mysql/log-$(date + %F)

mysqladmin -uroot -p123456 flush-logs >/dev/null

sleep 5

find /var/log/mysql/ -mmin 1 -exec cp {} /var/log/mysql/log-$(date + %F)/  \;

if [ -f /var/log/mysql/log-$(date + %F)/mysql_bin.* ];then

  echo "mysql is zeng backup success on time-$(date +%F)" >>/var/log/mysql/mysql_zeng_backup.log

else

  echo "mysql is zeng backup fail on time-$(date +%F)" >>/var/log/mysql/mysql_zeng_backup.log

fi

运行增量备份

chmod +x /home/dml/mysql_zeng_backup.sh
echo "0 23 * * * /home/dml/mysql_zeng_backup.sh" >>/var/spool/cron/root
/etc/init.d/cron restart 或 systemctl restart cron

恢复

恢复前先关闭binlog并重启
sudo mysqladmin -uroot -p123456 flush-logs && sudo mysqlbinlog /var/log/mysql/mysql-bin.000001 | mysql -uroot -p -h192.168.99.99 

或者
sudo mysqladmin -uroot -p123456 flush-logs && sudo mysqlbinlog --no-defaults /var/log/mysql/mysql-bin.000001 >bin.sql
sed -i 's/SET @@SESSION.GTID_NEXT/#SET @@SESSION.GTID_NEXT/g' bin.sql
mysql -uroot -p123456 -h192.168.99.99 < bin.sql

恢复错误解决

GTID(GlobalTransaction ID)是对于一个已提交事务的编号,并且是一个全局唯一的编号。GTID实际上是由UUID+TID组成的。其中UUID是一个MySQL实例的唯一标识。TID代表了该实例上已经提交的事务数量,并且随着事务提交单调递增。

从开启GTID的库中导出数据到未开启GTID的库中,需要注意,在导出的文件中去掉相应的gtid内容,否则导入时会报错如下:

ERROR 1193 (HY000) at line 4: Unknown system variable 'GTID_NEXT'

方法:

mysql> show global variables like 'gtid_mode';
mysql> set @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
或者
#导出的增量日志需要去掉GTID
sed -i 's/SET @@SESSION.GTID_NEXT/#SET @@SESSION.GTID_NEXT/g' mysql-bin.000547_binlog.sql