mysql备份和恢复

mysql备份和恢复

 

 数据丢失的原因：

程序错误

人为操作错误

运算错误

磁盘故障

灾难（火灾，地震）和盗窃

数据库备份分类

物理备份

数据库此操作系统的物理文件（数据文件，日志文件等）的备份

方法：冷备份（脱机备份）：实在数据库关闭时进行

热备份（联机备份）：数据库处于运行状态，依赖于数据库的日志文件

温备份：数据库在锁定表（不可写入可读）状态下进行备份

逻辑备份

对数据库逻辑组件（库，表等数据库对象以文本导出）的备份

数据库备份分类

从策略备份

 完全备份：每次对数据库进行完整的备份

差异备份：备份自上次完全备份后之后被修改过的文件

增量备份：只有在上次完全备份或者增量备份后被修改的文件才会被备份

备份策略通常是每周一完全备份后面几天增量备份

备份的方法

 物理冷备份

备份时关闭数据库，直接打包数据库文件data目录打包 备份速度快，恢复也最简单

tar -zcf

专用备份工具mysqldump或mysqlhotcopy

mysldump常用的逻辑备份工具

mysqlhotcopy仅拥有备份myisqm和archive表

启用而矜持日志增量备份

进行增量备份，需要刷新二进制日志

第三方工具备份

免费的mysql热备份软件percona Xtrabackup

阿里云dts工具

备份

mkdir /backup

tar zcf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data

恢复

迁移数据

冷备份迁移

systemctl stop mysql

tar zcf /opt/mysql_all-$(date +%Y%m%d).tar.gz /usr/local/mysql/data

.xz压缩包压缩时tar Jcvf

scp mysql_all-$(date +%Y%m%d).tar.gz 192.168.232.17:'pwd'

解压覆盖

mysqldump备份

 mysqldump -uroot -pabc123  --databases kgc mysql > /opt/ky27/kgc.sql

通过--databases指定多个库

mysqldump -uroot -pabc123  --all-databases kgc mysql > /opt/ky27/kgc.sql

--all-databases 指定所有的库

mysqldump -uroot -pabc123   kgc member account > /opt/ky27/kgc.sql

member account为表明

使.sql文件更直观

grep -v "^$" kgc_men_acc.sql |grep -v "^--" |grep   

-v "^/\*"

恢复

法一

mysql -uroot -pabc123 -e 'show databases;'-e 执行数据库内命令结束返回

mysql -uroot -pabc123 kgc< kgc_men_acc_t1.sql

 生产环境中可以使用shell脚本定时备份mysqldump命令要用绝对路径去执行或者crontab -e

0 0 * * 1 /usr/local/mysql/bin/myaqldump -u root -pabc123 --all-databases > /opt/ky27/all-databases-$(data +%Y%m%d).sql

法二：恢复

create database kgc #备份时有--databases时不需要

use kgc

source /opt/ky27/kgc.sql

mysql没有提供直接的曾量备份方法

可通过mysql提供的二进制日志间接实现增量备份

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

 一般恢复，将所有备份的二进制日志内容全部恢复

基于位置恢复数据库某一时间可能存在错误操作也有正确的操作，可以精确的位置跳过错误的操作

基于时间点的恢复：跳过某个发生错误的时间点实现数据恢复

 mysql日志默认保存位置 /usr/local/mysql/data

vim /etc/my.cnf

[mysqld]

##错误日志，用来记录当MySOL启动、停止或运行时发生的错误信息，默认已开启#指定日志的保存位置和文件名log-error=/usr/local/mysql/data/mysql error.log 指定日杂hi的文件路径和文件名

##通用查询日志，用来记录MySQL的所有连接和语句，默认是关闭的

general_log=ON

general_log_file=/usr/local/mysql/data/mysgl general.log

#二进制日志(binlog)，用来记录所有更新了数据或者已经潜在更新了数据的语句，记录了数据的更改，可用于数据恢复，默认已开启#也可以 log-bin=mysql-bin也可以用log_bin=mysql-bin

#慢查询日志，用来记录所有执行时间超过long guery time秒的语句，可以找到哪些查询语句执行时间长，以便于优化，默认是关闭的

slow_query_log=ON

slow_query_log file=/usr/local/mysql/data/mysql slow_query.log

long_query_time=5

#设置超过5秒执行的语句被记录，缺省时为10秒

systemctl restart mysqld

mysgl -u root -p

show variables like 'general%';#查看通用查询日志是否开启

show variables like 'log_bin%';#查看二进制日志是否开启

show variables like '%slow%';#查看慢查询日功能是否开启

show variables like 'long_query_time';

#查看慢查询时间设置

生成日志文件重启 mysqladmin -uroot -pabc23 flush-logs

sed -n '$p' mysql-bin.index看最新的日志

tail -1 mysql-bin.inde

 二进制日志（binlog）有三种不同的记录格式：statement（基于sql语句），row（基于行），mixed（混合模式），默认格式时statement

sql语句高并发情况下易出现错误row准确行高

update member set address='beijing';

查看二进制日志以row输出的方法mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000003

vim /etc/my.cnf

[mysql]

binlog_format=MINED

重启服务

增量备份

完全备份后增量备份前先刷新mysqladmin -uroot -pabc123 flush-logs

cat  mysql-bin.index | tail -2 | head -1 |awk -F/ '{print $2}' #获取昨天二进制的日志文件

 #!/bin/bash

/usr/local/

完全备份和增量备份备份脚本

/usr/local/mysql/bin/mysqladmin -uroot -pabc123

mv 命令 移动旧binlog 到备份目录，按照时间格式保存

data -d "-1 day" +%Y%m%d

完全恢复

mysql -uroot -pabc123 < ky27_ky227_20230508.sql

增量恢复

mysqlbinlog --no-defaults mysql-binlog-20230507 | mysql -uroot -pabc123

mysqlbinlog --no-defaults mysql-binlog-20230508 | mysql -uroot -pabc123

位置恢复（时间点恢复）

at 269 即为位置

mysqlbinlog --no-defaults --start-position'632' /root/ky27/ | mysql -uroot

 -pabc123

mysqlbinlog --no-defaults --start-position'269' --stop-position='748' /root/ky27/ | mysql -uroot

 -pabc123

时间点恢复

时间如图

 mysqlbinlog --no-defaults --start-datatime'2023-05-08 17：17：08'  /root/ky27/ | mysql -uroot

 -pabc123

 如果恢复某条sql语句之前的所有数据，就stop在这个语句的前的位置节点或时间点

如果恢复某条sql语句之前的所有数据，就start在这个语句位置之后的位置节点