Mysql备份与恢复
一、MySQL常用日志类型及开启
(一)MySQL的主配置文件的修改
vim /etc/my.cnf
[mysqld]
......
MySQL日志管理
MySQL的默认日志保存位置为/usr/local/mysql/data
日志开启方式有两种:通过配置文件或者是通过命令
通过命令修改开启的日志是临时的,关闭或重启服务后就会关闭
(二)常用日志
1.错误日志
用来记录当MySQL启动、停止或运行时发生的错误信息,默认已开启
指定日志的保存位置和文件名
log-error=/usr/local/mysql/data/mysql_error.log
2.二进制日志
二进制日志(binlog),用来记录所有更新的数据或者已经潜在更新了数据的语句,记录了数据的更改,可用于数据恢复,默认已开启 ,mysql中并没有开启
潜在更新:指数据存在不一致的状态情况。比如转账过程中,数据可能存在不一致状态
开启方式:
log-bin=mysql-bin
或
log_bin=mysql-bin
每重启一次mysqld服务或刷新二进制文件 flush-logs 都会生成一个二进制文件mysql-bin.000001
3.中继日志
一般情况下它在MySQL主从同步(复制)、读写分离集群的节点才开启。主节点一般不需要这个日志
4.慢日志查询(索引的优化)
用来记录所有执行时间超过long_query_time秒的语句,可以找到哪些查询语句执行时间长,以便于优化,默认是关闭的
vim /etc/my.cnf
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
5.通用(一般)查询日志
用来记录MySQL的所有连接和语句,默认是关闭的
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log
(三)实操
vim /etc/my.cnf
[mysqld]
......
server-id = 1
log-error=/usr/local/mysql/data/mysql_error.log
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log
log-bin=mysql-bin
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5
systemctl restart mysqld
mysql -uroot -p123456
show variables like 'general%';
show variables like 'log_bin%';
show variables like '%slow%';
二、查看日志状态
1.查看通用查询日志是否开启
mysql -u root -p
show variables like 'general%'; #variables 表示变量 like表示模糊查询
2.查看二进制日志是否开启
show variables like 'log_bin%';
3.查看慢查询日功能是否开启
//慢日志状态
show variables like '%slow%';
//查看慢查询时间设置
show variables like 'long_query_time';
4.在数据库中设置开启慢查询的方法
set global slow_query_log=ON;
该方法重启服务失效,属于临时开启
5.补充
variables:变量
like:模糊查询
#xxx(字段)
xxx% 以xxx开头的字段
%xxx 以xxx结尾的字段
%xxx% 只要出现xxx字段的都会显示出来
xxx 精确查询
三、MySQL备份与恢复
(一)数据备份的重要性
备份的主要目的是灾难恢复
在生产环境中,数据的安全性至关重要
任何数据的丢失都可能产生严重的后果
1.造成数据丢失的原因
1.1程序错误
1.2人为操作错误
1.3运算错误
1.4磁盘故障
1.5灾难(火灾、地震、盗窃等)
(二)数据库备份的分类
1.物理备份:对数据库操作系统的物理文件(如数据文件、日志文件等)的备份
数据库备份可以分为物理备份和逻辑备份,物理备份是对数据库操作系统的物理文件(如数据文件、日志文件等)的备份。这种类型的备份适用于在出现问题时需要快速恢复的大型重要数据库。
物理备份方法:
-冷备份(脱机备份):是在关闭数据库的时候进行的
如果数据库没有关闭的时候数据库正在写入,这样打包压缩的时候可能出错,数据不完整等情况
-热备份(联机备份):数据库处于运行状态,依赖于数据库的日志文件(多使用mysqldump工具)
-温备份:数据库锁定表格(不可写入但可读)的状态下进行备份操作
2.逻辑备份:对数据库逻辑组件(如:表等数据库对象)的备份
逻辑备份是对数据库逻辑组件(如表等数据库对象)的备份,表示为逻辑数据库的结构(create database,createtable语句)和内容(insert 语句或分隔文本文件)的信息。这种类型的备份适用于可以编辑数据值或表结构较小的数据量,或者在不同的机器体系结构上重建数据
从数据库的备份策略角度,备份可分为
-完全备份:
每次对数据进行完整的备份,即对整个数据库、数据库结构和文件结构的备份,保存的是备份完成时刻的数据库,是差异备份与增量备份的基础。完全备份的备份与恢复操作都非常简单方便,但是数据存在大量的重复,并且会占用大量的磁盘空间,备份的时间也很长。
每次都进行完全备份,会导致备份文件占用空间巨大,并且有大量的重复数据。恢复时,直接使用完全备份的文件即可。
-差异备份:
备份那些自从上次完全备份之后被修改过的所有文件,备份的时间节点是从上次完整备份起,备份数据量会越来越大。恢复数据时,只需恢复上次的完全备份与最近的一次差异备份。(优点:数据恢复快安全性高,相比完全备份数据量小相比增量备份易恢复)
每次差异备份,都会备份上一次完全备份之后的数据,可能会出现重复数据。恢复时,先恢复完全备份的数据,再恢复差异备份的数据。
-增量备份:
只有那些在上次完全备份或者增量备份后被修改的文件才会被备份。以上次完整备份或上次增量备份的时间为时间点,仅备份这之间的数据变化,因而备份的数据量小,占用空间小,备份速度快。但恢复时,需要从上一次的完整备份开始到最后一次增量备份之的所有增量依次恢复,如中间某次的备份数据损坏,将导致数据的丢失。(增量备份值对比上次备份时间点与当前点表数据是否一致,不一致的备份,两个时间点期间该动了数据又还原了数据,则不会备份该数据)
每次增量备份都是备份在上一次完全备份或者增量备份之后的数据,不会出现重复数据的情况,也不会占用额外的磁盘空间。恢复数据,需要按照次序恢复完全备份和增量备份的数据。
备份方式 | 完全备份 | 差异备份 | 增量备份 |
---|---|---|---|
完全备份时的状态 | 表1、表2 | 表1、表2 | 表1、表2 |
第1次添加内容 | 创建表3 | 创建表3 | 创建表3 |
备份内容 | 表1、表2、表3 | 表3 | 表3 |
第2次添加内容 | 创建表4 | 创建表4 | 创建表4 |
备份内容 | 表1、表2、表3、表4 | 表3、表4 | 表4 |
四、常见的备份方法
1.物理冷备
物理冷备份时需要在数据库处于关闭状态下,能够较好地保证数据库的完整性。物理冷备份一般用于非核心业务,这类业务一般都允许中断,物理冷备份的特点就是速度快,恢复时也是最为简单的。通常通过直接打包数据库文件夹(/usr/local/mysql/ data)来实现备份
2.专用备份工具 myscldump或mysclhotcopy
mysqldump 程序和mysqlhotcopy 都可以做备份
mysqldump是客户端常用逻辑备份程序,能够产生一组被执行以后再现原始数据库对象定义和表数据的SQL语句。它可以转储一个到多个MySQL数据库,对其进行备份或传输到远程SQL服务器。mysqldump更为企业通用,因为它可以备份各种表。
mysqlhotcopy仅拥有备份myisam和archive表仅适用于某些存储引擎
3.通过启动二进制日志进行增量备份MySQL
支持增量备份,进行增量备份时必须启用二进制日志。二进制日志文件为用户提供复制,对执行备份点后进行的数据库更改所需的信息进行恢复。如果进行增量备份(包含自上次完全备份或增量备份以来发生的数据修改),需要刷新二进制日志。
4.通过第三方工具备份Percona XLraBackup是一个免费的MySQL热备份软件,支持在线热备份Innodb和XLraDB,也可以支持MysQT,表备份,不过 MyTSAM表的备份要在表锁的情况下进行。
五、MySQL完全备份与恢复
1.MySQL完全备份概述
概念:每次对数据进行完整的备份,即对整个数据库、数据库结构和文件结构的备份,保存的是备份完成时刻的数据库,是差异备份与增量备份的基础。完全备份的备份与恢复操作都非常简单方便,但是数据存在大量的重复,并且会占用大量的磁盘空间,备份的时间也很长
优缺点:
优点:
备份与恢复操作简单方便
缺点:
①数据存在大量的重复
②占用大量的备份空间
③备份与恢复时间长
数据库完全备份分类:
1.物理冷备份与恢复
-关闭MySQL数据库
-使用tar命令直接打包数据库文件夹
-直接替换现有Mysql目录即可
2.mysqldump备份与恢复
-MySQL自带的备份工具,可方便实现对MySQL的备份
-可以将指定的库、表导出为SQL脚本
-使用命令mysql导入备份的数据
2.创建一个实验表
mysql -u root -p
create database kgc;
use kgc;
create table if not exists info1 (
id int(4) not null auto_increment,
name varchar(10) not null,
age char(10) not null,
hobby varchar(50),
primary key (id));
insert into info1 values (1,'z',11,'lanqiu');
insert into info1 values (2,'zz',12,'taiqiu');
exit
(一)MySQL完全备份
Innodb 存储引擎的数据库在磁盘上储存成三个文件:db.opt(表属性文件)、表名.frm(表结构文件)、表名.ibd(表数据文件)
1.物理冷备份与恢复
systemctl stop mysqld
yum -y install xz #xz是一个压缩工具
#压缩备份
tar Jcvf /opt/mysql_all_`date +%F`.tar.xz /usr/local/mysql/data/
或者tar jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data/
#解压恢复
tar Jxvf /opt/mysql_all_2022-08-10.tar.xz -C ./
cd /usr/local/mysql/data
mv /usr/local/mysql/data/* ./
systemctl start mysqld
2.mysqldump 备份
(1)完全备份一个或多个完整的库(包括其中所有的表)
mysqldump -u root -p[密码] --databases 库名1 [库名2] … > /备份路径/备份文件名.sql //导出的就是数据库脚本文件
例:
mysqldump -uroot -p123456 --databases kgc > /opt/kgc.sql
mysqldump -uroot -p123456 --databases kgc njzb > /opt/kgc-njzb.sql
(2)完全备份 MySQL 服务器中所有的库
mysqldump -u root -p[密码] --all-databases > /备份路径/备份文件名.sql
例:
mysqldump -u root -p123456 --all-databases > /opt/all_databases.sql
(3)完全备份指定库中的部分表
mysqldump -u root -p[密码] 库名 [表名1] [表名2] … > /备份路径/备份文件名.sql
mysqldump -uroot -p123456 kgc info1 info2 > /opt/kgc-info1_info2.sql
#使用“-d”选项,说明只保存数据库的表结构
#不使用“-d”选项,说明表数据也进行备份
(4)查看备份文件
grep -v "^--" /opt/kgc_info1.sql | grep -v "^/" | grep -v "^$"
3.完全备份恢复
(1)恢复数据库
mysql -uroot -p123456 -e 'drop database kgc;'
#“-e”选项,用于指定连接 MySQL 后执行的命令,命令执行完后自动退出
mysql -uroot -p123123 -e 'SHOW DATABASES;'
mysql -uroot -p123123 < /opt/kgc.sql
mysql -uroot -p123123 -e 'SHOW DATABASES;'
(2)恢复数据表
当备份文件中只包含表的备份,而不包含创建的库的语句时,执行导入操作时必须指定库名,且目标库必须存在。
mysql -uroot -p123456 -e 'drop table info.ky20;'
mysql -uroot -p123456 -e 'show tables from info;'
mysql -uroot -p123456 info < /opt/info_ky20.sql
mysql -uroot -p123456 -e 'show tables from info;'
六、MySQL 增量备份与恢复
(一)MySQL 增量备份
1.开启二进制日志功能
vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin //开启二进制日志
binlog_format = MIXED //添加二进制日志格式,默认statement
server-id = 1 //服务器id
//二进制日志(binlog)有3种不同的记录格式:STATEMENT(基于SQL语句)、ROW(基于行)、MIXED(混合模式),默认格式是STATEMENT
systemctl restart mysqld
ls /usr/local/mysql/data/
2.可每周对数据库或表进行完全备份
mysqldump -uroot -p123456 kgc info1 > /opt/kgc_info1-$(date +%F).sql
mysqldump -uroot -p123456 --all-databases kgc > /opt/kgc-$(date +%F).sql
3.可每天进行增量备份操作,生成新的二进制日志文件(例如 mysql-bin.000005)
mysqladmin -uroot -p123456 flush-logs
//可以结合crontab -e 设置计划任务
4.插入新数据,以模拟数据的增加或变更
mysql -uroot -p123456
use kgc;
insert into info1 values(5,'zzzzz',15,'qiu');
5.再次生成新的二进制日志文件(例如 mysql-bin.000003)
mysqladmin -uroot -p123456 flush-logs
//之前插入的数据库操作会保存到mysql-bin.000002文件中,之后数据库数据再发生变化则保存在mysql-bin.000003文件中
6.查看二进制日志文件的内容
cp /usr/local/mysql/data/mysql-bin.000002 /opt/
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002
//--base64-output=decode-rows:使用64位编码机制去解码并按行读取,直接看是看不了的
//-v:显示详细内容
(二)MySQL 增量恢复
1.一般恢复
(1)模拟丢失更改的数据的恢复步骤
mysql -uroot -p123456
use kgc;
delete from info1 where id=5;
select * from info1;
quit
mysqlbinlog --no-defaults /opt/mysql_bin.000002 | mysql -uroot -p123456
(2)模拟丢失所有数据的恢复步骤
mysql -u root -p
use kgc
drop table info1;
mysql -uroot -p123456 kgc < /opt/kgc_info1-2022-08-11.sql
2.断点恢复
查看二进制日志文件
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002
(1)基于位置恢复
模拟数据丢失
use kgc;
delete from info1 where id=4;
delete from info1 where id=5;
断点恢复id=5之前的数据
cd /usr/local/mysql/data
mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002
mysqlbinlog --no-defaults --stop-position='1865' mysql-bin.000002 | mysql -uroot -p123456
mysql -uroot -p123 -e "select * from kgc_info1;"
(2)基于时间点恢复
mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000003
mysqlbinlog --no-defaults --stop-datatime='2022-08-11 17:07:30' mysql-bin.000003 | mysql -uroot -p123456