mysql备份和恢复

[root@localhost ~]# systemctl stop mysqld.service

[root@localhost ~]# mkdir /backup

[root@localhost ~]# tar Jcvf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/

4.2 数据库被误删除恢复操作

[root@localhost ~]# cd /usr/local/mysql/

[root@localhost mysql]# mv data data.bak

[root@localhost mysql]# tar Jxf /backup/mysql_all-2022-10-26.tar.gz -C /opt/ [root@localhost ~]# mv /opt/usr/local/mysql/data/ /usr/local/mysql/

[root@localhost ~]# systemctl start mysqld

[root@localhost ~]# mysql -uroot -pabc123

show databases;

5 mysqldump备份数据库

[root@localhost backup]# mysql -uroot -pabc123 -e "show databases;"

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

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

单库备份的实例

mysqldump -u root -pabc123 can > /backup/can.sql

mysqldump -u root -pabc123 mysql > /bakcup/mysql.sql

mysqldump 在备份库的时候加 --databases 与不加的区别

//加--databases

[root@localhost backup]# mysqldump -u root -pabc123 --databases can > /backup/can01.sql

[root@localhost backup]# cat can01.sql | egrep -v "(^--|^/|^$)"

//不加

mysqldump -u root -pabc123 can > /backup/can02.sql

[root@localhost backup]# cat can02.sql | egrep -v "(^--|^/|^$)"

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

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

多库备份的示例

mysqldump -u root -pabc123 --databases can mysql > /backup/databases_can_mysql.sql

5.3 对`所有库`进行完全备份

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

所有库备份的示例

mysqldump -u root -pabc123 --all-databases > /backup/all-data.sql

5.4 mysqldump可针对库内`特定的表`进行备份

使用mysqldump备份表的操作

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

备份表的示例

mysqldump -u root -pabc123 mysql user > /backup/mysql-user.sql

-d 只保存表结构

6 mysqldump完全恢复

6.1 恢复数据库

先删除数据库，之后进行恢复

##删除数据库sly##

mysql -u root -p -e 'drop database sly;'

#"-e"选项，用于指定连接MySQL后执行的命令，命令执行完后自动退出

mysql -u root -p -e 'show databases;' #查看当前有哪些数据库

##恢复数据库sly##

mysql -u root -p < /opt/backup/bbc.sql #重定向输入库文件

mysql -u root -p -e 'show databases;' #查看当前有哪些数据库

6.2 恢复数据表

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

##备份sly库中的account表##

mysqldump -u root -p sly account > /opt/backup/sly_account.sql

##删除sly库中的account表##

mysql -u root -p -e 'drop table can.account;'

mysql -u root -p -e 'show tables from sly;' #查看sly库中的表，已无account表

##恢复sly库中的account表##

mysql -u root -p sly < /opt/backup/can_account.sql #重定向导入备份文件，必须指定库名，且目标库必须存在

mysql -u root -p -e 'show tables from sly;'

三. MySQL日志管理

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

vim /etc/my.cnf

[mysqld]

##错误日志，用来记录当MySQL启动、停止或运行时发生的错误信息，默认已开启

log-error=/usr/local/mysql/data/mysql_error.log #指定日志的保存位置和文件名

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

general_log=ON

general_log_file=/usr/local/mysql/data/mysql_general.log

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

log-bin=mysql-bin #也可以log_bin=mysql-bin

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

slow_query_log=ON

slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log

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

[mysqld]

log-bin=mysql-bin

binlog_format=STATEMENT|ROW|MIXED

STATEMENT(基于SQL语句记录二进制日志，写入和恢复速度较快，在高并发的情况可能会出SQL语句顺序记录偏差导致恢复时数据丢失) 老版本的默认模式

ROW（基于行记录二进制日志，写入和恢复速度较STATEMENT慢些，日志文件占用空间也较大，但是准确性较高）5.7的默认模式

MIXED（混合模式，平时负载压力较小时使用SQL语句记录二进制日志，在高并发的情况会切换成基于行记录二进制日志）

systemctl restart mysqld

mysql -uroot -p

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

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

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

show variables like 'long_query_time'; //查看慢查询时间设置 set global slow_query_log=ON; //在数据库中设置开启慢查询的方法

四. MySQL增量备份与恢复

1 增量备份

1.1 开启二进制日志功能

vim /etc/my.cnf

[mysqld] log-bin=mysql-bin #开启二进制日志。如果使用相对路径，则保存在/usr/local/mysql/data/目录下

binlog_format = MIXED #可选，指定二进制日志(binlog)的记录格式为MIXED

1.2 可每周对数据库或表进行`完全备份`

mysqldump -u root -p can test01 > /opt/backup/can_test01_$(date +%F).sql

mysqldump -u root -p --databases can > /opt/backup/can_$(date +%F).sql

1.3 可每天进行增量备份操作，`刷新二进制日志文件`

mysqladmin -u root -p flush-logs

1.4 插入新数据，以模拟数据的增加和变更

user can;

insert into test01 values(4,'zhangan',25,'男');

insert into test01 values(5,'lisi',18,'女');

1.5 再次生成新的二进制日志文件

mysqladmin -u root -p flush-logs

#之前的步骤4的数据库操作会保存到mysql-bin.000002文件中，之后数据库数据再发生变化则保存在mysql-bin.00003文件中

1.6 备份二进制日志文件并查看内容

mv /usr/local/mysql/data/mysql-bin.000002 /opt/backup

mysqlbinlog --no-defaults --base64-output=decode-rows -v /bak/mysql-bin.000002

#--base64-output=decode-rows：使用64位编码机制去解码并按行读取

#-v：显示详细内容

2 增量恢复

一般恢复

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

基于位置恢复

数据库在某一时间点可能既有错误的操作也有正确的操作

可以基于精准的位置跳过错误的操作

基于时间点恢复

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

2.1 一般恢复

模拟丢失更改数据的恢复

user can;

delete from class where id=4; ##删除新增数据

delete from class where id=5;

##增量恢复

mysqlbinlog --no-defaults /opt/backup/mysql-bin.000002 | mysql -u root -p

mysqlbinlog --no-defaults /opt/backup/mysql-bin.000002 | mysql -u root -p

模拟丢失表中的所有的数据的恢复步骤

mysql -u root -p -e 'drop table can.test01;' ##删除整个test01表

mysql -u root -p -e 'show tables from can;' #查看can库中的表

##先完全恢复历史数据

mysql -u root -p can < /opt/backup/can_2022.10.27.sql

##再进行增量恢复今天新增的两条数据

mysqlbinlog --no-defaults /opt/backup/mysql-bin.000002 | mysql -u root -p

mysql -u root -p -e 'select * from can.test01;' ##查看表中数据

2.2 断点恢复

use can;

insert into class values(6,'哈哈哈',29,'男');

insert into class values(7,'嘿嘿嘿',19,'男');

insert into class values(8,'吼吼吼',49,'男');

mysqladmin -u root -p flush-logs #刷新日志，生成新的二进制日志

mv /usr/local/mysql/data/mysql-bin.000003 /opt/backup/ #将前一个日志移动到/opt/backup/目录下

mysqlbinlog --no-defaults --base64-output=decode-rows -v /bak/mysql-bin.000003 > /opt/backup/binlog.txt #将二进制文件内容重定向到binlog.txt文件中

基于位置的断点恢复

mysqlbinlog --no-defaults --start-position='位置点' 文件名 | mysql -u root -p

#从某一个位置点开始恢复，一直到日志结尾

mysqlbinlog --no-defaults --stop-position='位置点' 文件名 | mysql -u root -p

#从日志开头，一直恢复到某一个位置点前结束

mysqlbinlog --no-defaults --start-position='位置点' --stop-position='位置点' 文件名 | mysql -u root -p

#从某一个位置点开始恢复，一直到某一个位置点前结束

示例1：

use can;

delete from class where id=6;

delete from class where id=7; ##删除id为6-8的数据记录

delete from class where id=8;

cat /opt/backup/binlog.txt ##查看二进制文件内容，找到id=6数据的位置点

##从6的位置点恢复数据

mysqlbinlog --no-defaults --start-position='位置点' /opt/backup/mysql-bin.000003 | mysql -u root -p

mysql -u root -p -e 'select * from can.test01;' ##查看表中数据

示例2

只恢复到id=7的数据

mysqlbinlog --no-defaults --stop-position='位置点' 文件名 | mysql -u root -p

mysql -u root -p -e 'select * from can.test01;' ##查看表中数据

示例3

恢复6-7行数据

mysqlbinlog --no-defults --start-position='位置点' --stop-position='位置点' 文件名 | mysql -u root -p

mysql -u root -p -e 'select * from can.test01;' ##查看表中数据

2.3 基于时间点的断点恢复

注意：日期格式为：yyyy-mm-dd hh:mm:ss

mysqlbinlog --no-defults --start-datetime='时间点' 文件名 | mysql -u root -p

##从某一个时间点开始恢复，一直到日志结尾

mysqlbinlog --no-defults --stop-datetime='时间点' 文件名 | mysql -u root -p

##从日志开头，一直恢复到某一个时间点前结束

mysqlbinlog --no-defults --start-datetime='开始时间点' --stop-datetime='结束时间点' 文件名 | mysql -u root -p

#从某一个时间点开始恢复，一直到某一个位置点前结束

示例1

use bbc;

delete from class where id=6;

delete from class where id=7; ##删除id为6-8的数据记录

delete from class where id=8;

cat /opt/backup/binlog.txt ##查看二进制文件内容，找到id=6数据的时间点

##从6的时间点恢复数据

mysqlbinlog --no-defaults --start-datetime='位置点' 文件名 | mysql -u root -p

mysql -u root -p -e 'select * from bbc.class;' ##查看表中数据

示例2：

只恢复到id=7的数据

mysqlbinlog --no-defults --stop-datetime='时间点' 文件名 | mysql -u root -p

mysql -u root -p -e 'select * from can.test01;' ##查看表中数据

示例3：

mysqlbinlog --no-defults --start-datetime='开始时间点' --stop-datetime='结束时间点' 文件名 | mysql -u root -p

mysql -u root -p -e 'select * from bbc.class;' ##查看表中数据

3 使用脚本备份

使用脚本每周三进行完全备份，之后每天进行增量备份

3.1 获取最新的二进制文件名称

#查看索引文件获取最新的二进制文件

tail -1 /usr/local/mysql/data/mysql-bin.index

或者

sed -n '$p' /usr/local/mysql/data/mysql-bin.index

3.2 完全备份（每周三完全备份）

vim a.sh

#!/bin/bash

mysqldump -u root -p --all-databases > /bak/all_data_$(date +%F).sql

chmod +x wanquan.sh

crontab -e

0 3 * * 3 /root/a.sh ##每周三凌晨三点进行完全备份

3.3 增量备份文件（每天凌晨3点进行备份）

vim b.sh

#!/bin/bash

##使用tail命令打印出日志文件的最后一行内容作为name变量的值

name=$(tail -1 /usr/local/mysql/data/mysql-bin.index | awk -F'/' '{print $2}')

##生成新的二进制文件

mysqladmin -uroot -pabc123 flush-logs

##将二进制文件移动到备份目录下并加上日期

mv /usr/local/mysql/data/$name /opt/backup/bin_$(data_ +%F)

chmod +x b.sh

crontab -e

0 3 * * 3 /root/a.sh ##每周三凌晨三点进行完全备份

0 3 * * * /root/b.sh ##每天凌晨三点进行增量备份