MySQL-备份与恢复、binlog日志

it_zhenxiaobai

已于 2024-03-28 11:26:35 修改

阅读量785

点赞数 10

文章标签： oracle 数据库

于 2024-01-16 21:00:22 首次发布

本文链接：https://blog.csdn.net/it_zhenxiaobai/article/details/135626990

版权

备份概述

一、备份方式

按照数据库服务状态分为：

冷备份：在备份时暂停数据库运行和服务，将整个数据库复制到备份设备中

热备份：在备份时不停止数据库的运行和服务

按照备份的数据分为：

物理备份：备份数据库服务器上存储的原始数据和文件，可以直接拷贝和恢复

逻辑备份：备份的是SQL语句，可用于不同版本和不同类型的MySQL数据库之间的数据迁移

二、备份策略

1、完全备份

完全备份所有的数据
易于管理

2、增量备份

每周进行一次完全备份
其余每天备份和上次备份的差异部分

3、差异备份

每周进行一次完全备份
其余每天备份和完全备份的差异部分

三、备份系统三要素

1、BW 一个工作周期内留给备份系统进行备份的时间长度

2、RPO 客户可以承受的最大数据丢失量

3、RTO 客户可以承受的最长停机时间

完全备份与恢复

一、物理备份及恢复

适合线下备份，不适合线上备份

1、备份操作

systemctl stop mysqld
cp -rp /var/lib/mysql /opt/mysql.bak
或
tar -zcf /opt/mysql.tar.gz /var/lib/mysql/*

2、恢复操作

systemctl stop mysqld
rm -rf /var/lib/mysql/*
cp -r /opt/mysql.bak/* /var/lib/mysql/
或
tar -zxf /opt/mysql.tar.gz -C /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld

二、逻辑备份及恢复

1、完全备份

mysqldump -uroot -p密码库名 > /目录/xxx.sql

库名表示方式：

库名表名列表备份1张表或多张表所有数据

-B 库名列表备份1个库或多个库所有数据

--all-databases 或 -A 备份1台服务器所有数据

[root@mysql50 ~]# mkdir /bakdir
# 备份所有库 
[root@mysql50 ~]# mysqldump -uroot -p123456 -A > /bakdir/allbak.sql
# 备份1个库 
[root@mysql50 ~]# mysqldump -uroot -p123456 -B tarena > /bakdir/tarena.sql
# 备份多个库
root@mysql50 ~]# mysqldump -uroot -p123456  -B db3 db1 > /bakdir/db3_db1.sql 
# 备份多张表
[root@mysql50 ~]# mysqldump -uroot -p123456  tarena employees departments > /bakdir/tarena_employees_deparments.sql
# 备份1张表 
[root@mysql50 ~]# mysqldump -uroot -p123456 tarena salary > /bakdir/tarena_salary.sql

[root@mysql50 ~]# ls /bakdir/
allbak.sql  tarena_salary.sql  tarena.sql  db3_db1.sql   tarena_employees_deparments.sql

2、完全恢复

1）命令行恢复

mysql -uroot -p密码 [库名] < /目录/xxx.sql

[root@mysql51 ~]# mysql -p123456

mysql> select count(*) from tarena.salary;
mysql> delete from tarena.salary;
mysql> select count(*) from tarena.salary;

[root@mysql50 ~]# scp /bakdir/tarena_salary.sql 192.168.88.51:/root
[root@mysql51 ~]# ls
mysql.tar.gz  tarena_salary.sql
[root@mysql51 ~]# mysql -uroot -p123456 tarena < /root/tarena_salary.sql 
[root@mysql51 ~]# mysql -p123456

mysql> select count(*) from tarena.salary;

2）使用数据库里的 source 命令恢复数据

mysql> drop database tarena;
mysql> show databases;
mysql> source /bakdir/tarena.sql;
mysql> show databases;
mysql> drop table tarena.salary;
mysql> use tarena;
mysql> show tables;
mysql> source /bakdir/tarena_salary.sql;
mysql> show tables;

恢复库时库名可以省略

恢复表时需要库名

备份和恢复时，数据库服务必须是运行状态

Mysqldump 备份数据时会锁表，锁表期间无法对表做写访问，mysqldump适合备份数据量比较小的数据或在数据库服务器访问量少的时候备份。

三、备份缺点

1、物理备份缺点

跨平台性差
备份时间长、冗余备份、浪费存储空间

2、mysqldump备份缺点

效率较低，备份和还原速度慢、锁表
备份过程中，数据插入和更新操作被阻塞

增量备份与恢复

备份上次备份后产生的数据

软件介绍

一款强大的在线热备份工具

备份过程中不锁库表，适合生产环境

由专业组织Percona提供（改进MySQL分支）

一、软件安装

# 免安装解压即可使用

[root@mysql50 ~]# yum -y install perl-DBD-MySQL    # 安装依赖
[root@mysql50 ~]# tar -xf percona-xtrabackup-8.0.26-18-Linux-x86_64.glibc2.12-minimal.tar.gz
[root@mysql50 ~]# mv percona-xtrabackup-8.0.26-18-Linux-x86_64.glibc2.12-minimal /usr/local/percona
[root@mysql50 ~]# vim /etc/bashrc           # 配置初始配置文件 系统环境变量
export PATH=/usr/local/percona/bin:$PATH    # 添加此行
[root@mysql50 ~]# source /etc/bashrc        # 使配置生效
[root@mysql50 ~]# man xtrabackup

二、增量备份

1、首次完全备份

xtrabackup --host=127.0.0.1 --user=用户名 --password=密码 --backup --target-dir=备份目录 --datadir=数据库目录

[root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=123456 --backup --target-dir=/allbak --datadir=/var/lib/mysql

2、增量备份

xtrabackup --host=127.0.0.1 --user=用户名 --password=密码 --backup --target-dir=备份目录 --incremental-basedir=上次备份目录 --datadir=数据库目录

[root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=123456 --backup --target-dir=/new2 --incremental-basedir=/allbak --datadir=/var/lib/mysql
...连接数据库插入数据...
[root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=123456 --backup --target-dir=/new3 --incremental-basedir=/new2 --datadir=/var/lib/mysql
...连接数据库插入数据...
[root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=123456 --backup --target-dir=/new4 --incremental-basedir=/new3 --datadir=/var/lib/mysql
...连接数据库插入数据...
[root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=123456 --backup --target-dir=/new5 --incremental-basedir=/new4 --datadir=/var/lib/mysql
...连接数据库插入数据...
[root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=123456 --backup --target-dir=/new6 --incremental-basedir=/new5 --datadir=/var/lib/mysql
...连接数据库插入数据...
[root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=123456 --backup --target-dir=/new7 --incremental-basedir=/new6 --datadir=/var/lib/mysql

[root@mysql50 ~]# ls -d /allbak/
/allbak/
[root@mysql50 ~]# ls -d /new*
/new2  /new3  /new4  /new5  /new6  /new7

三、增量恢复

1、准备恢复数据

定义合并数据的位置

[root@mysql50 ~]# scp -r /allbak root@192.168.88.51:/opt
[root@mysql50 ~]# scp -r /new* root@192.168.88.51:/opt
[root@mysql51 ~]# ls /opt/
allbak  new2  new3  new4  new5  new6  new7

[root@mysql51 ~]# cat /opt/allbak/xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 23539361
last_lsn = 23539361
flushed_lsn = 23539361

[root@mysql51 ~]# xtrabackup --prepare --apply-log-only --target-dir=/opt/allbak

# 验证合并
[root@mysql51 ~]# cat /opt/allbak/xtrabackup_checkpoints 
backup_type = log-applied    # 此行改变 可以合并数据了
from_lsn = 0
to_lsn = 23539361
last_lsn = 23539361
flushed_lsn = 23539361

2、合并数据

合并的顺序要和备份的顺序一致

[root@mysql51 ~]# cat /opt/allbak/xtrabackup_checkpoints    # 合并前查看数据范围
backup_type = log-applied
from_lsn = 0
to_lsn = 23541261
last_lsn = 23541261
flushed_lsn = 23541261

[root@mysql51 ~]# xtrabackup --prepare --apply-log-only --target-dir=/opt/allbak --incremental-dir=/opt/new2

[root@mysql51 ~]# xtrabackup --prepare --apply-log-only --target-dir=/opt/allbak --incremental-dir=/opt/new3
[root@mysql51 ~]# xtrabackup --prepare --apply-log-only --target-dir=/opt/allbak --incremental-dir=/opt/new4
[root@mysql51 ~]# xtrabackup --prepare --apply-log-only --target-dir=/opt/allbak --incremental-dir=/opt/new5
[root@mysql51 ~]# xtrabackup --prepare --apply-log-only --target-dir=/opt/allbak --incremental-dir=/opt/new6
[root@mysql51 ~]# xtrabackup --prepare --apply-log-only --target-dir=/opt/allbak --incremental-dir=/opt/new7

[root@mysql51 ~]# cat /opt/new7/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 23543141
to_lsn = 23543161
last_lsn = 23543171
flushed_lsn = 23543161

[root@mysql51 ~]# cat /opt/allbak/xtrabackup_checkpoints    
backup_type = log-applied
from_lsn = 0
to_lsn = 23543161
last_lsn = 23543171
flushed_lsn = 23543161

3、拷贝数据

恢复数据

[root@mysql51 ~]# systemctl stop mysqld.service 
[root@mysql51 ~]# rm -rf /var/lib/mysql/*
[root@mysql51 ~]# xtrabackup --copy-back --target-dir=/opt/allbak
[root@mysql51 ~]# chown -R mysql:mysql /var/lib/mysql/
[root@mysql51 ~]# systemctl start mysqld.service 
[root@mysql51 ~]# mysql -uroot -p123456

四、恢复步骤

1、准备恢复数据

定义合并数据的位置

2、合并数据

合并的顺序要和备份的顺序一致

3、清空数据库目录

停止数据库服务并清空数据库目录

4、拷贝数据

恢复数据

5、修改数据库目录所有者/所属组用户为mysql

6、重启数据库服务

差异备份与恢复

备份完全备份后产生的新数据

一、差异备份

1、首次完全备份

xtrabackup --host=127.0.0.1 --user=用户名 --password=密码 --backup --target-dir=/备份目录 --datadir=/数据库目录

[root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=123456 --backup --target-dir=/fullbak --datadir=/var/lib/mysql
[root@mysql50 ~]# ls /fullbak/

2、差异备份

xtrabackup --host=127.0.0.1 --user=用户名 --password=密码 --backup --target-dir=/备份目录 --incremental-basedir=/完全备份目录 datadir=/数据库目录

[root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=123456 --backup --target-dir=/dir2 --incremental-basedir=/fullbak --datadir=/var/lib/mysql
[root@mysql50 ~]# ls /dir2/

...连接数据库插入数据...

[root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=123456 --backup --target-dir=/dir3 --incremental-basedir=/fullbak --datadir=/var/lib/mysql
[root@mysql50 ~]# ls /dir3/

......

二、差异恢复

1、准备恢复数据

[root@mysql50 ~]# scp -r /fullbak root@192.168.88.51:/root
[root@mysql50 ~]# scp -r /dir2/ root@192.168.88.51:/root
[root@mysql50 ~]# scp -r /dir3/ root@192.168.88.51:/root

[root@mysql51 ~]# cat /root/fullbak/xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 23544911
last_lsn = 23544911
flushed_lsn = 23544911

[root@mysql51 ~]# xtrabackup --prepare --apply-log-only --target-dir=/root/fullbak

[root@mysql51 ~]# cat /root/fullbak/xtrabackup_checkpoints 
backup_type = log-applied
from_lsn = 0
to_lsn = 23544911
last_lsn = 23544911
flushed_lsn = 23544911

2、合并数据

[root@mysql51 ~]# cat /root/dir3/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 23544911
to_lsn = 23548529
last_lsn = 23548539
flushed_lsn = 23548529

[root@mysql51 ~]# xtrabackup --prepare --apply-log-only --target-dir=/root/fullbak --incremental-dir=/root/dir3

[root@mysql51 ~]# cat /root/fullbak/xtrabackup_checkpoints 
backup_type = log-applied
from_lsn = 0
to_lsn = 23548529
last_lsn = 23548539
flushed_lsn = 23548529

3、拷贝数据

[root@mysql51 ~]# systemctl stop mysqld.service 
[root@mysql51 ~]# rm -rf /var/lib/mysql/*
[root@mysql51 ~]# xtrabackup --copy-back --target-dir=/root/fullbak
[root@mysql51 ~]# chown -R mysql:mysql /var/lib/mysql
[root@mysql51 ~]# systemctl start mysqld.service 
[root@mysql51 ~]# mysql -uroot -p123456

三、恢复步骤

1、准备恢复数据

2、合并数据

3、清空数据目录

4、拷贝数据

5、修改数据库目录所有者/组用户为mysql

6、重启mysql服务

完全+差异

数据量                 备份量
周一        完全备份 1G         1G

周二差异备份 1G+2G          2G

周三        差异备份 1G+2G+3G         5G

周四        差异备份 1G+2G+3G+4G 9G

周五        差异备份   1G+2G+3G+4G+5G 14G

周六        差异备份 1G+2G+3G+4G+5G+6G 20G

周日        差异备份 1G+2G+3G+4G+5G+6G+7G 27G

完全+增量

数据量                 备份量
周一        完全备份 1G         1G

周二增量备份 1G+2G          2G

周三        增量备份 1G+2G+3G         3G

周四        增量备份 1G+2G+3G+4G 4G

周五        增量备份   1G+2G+3G+4G+5G 5G

周六        增量备份 1G+2G+3G+4G+5G+6G 6G

周日        增量备份 1G+2G+3G+4G+5G+6G+7G 7G

binlog日志

一、日志管理

1、什么是binlog日志

也称作二进制日志
保存除查询之外的所有SQL命令
可用于数据的备份和恢复
配置mysql主从同步的必要条件

2、查看日志

日志默认是启用的

mysql> show master status; 查看日志文件
+----------------+----------+--------------+------------------+-------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| binlog.000001 |      156 |              |                  |                   |
+----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

日志存储位置

日志默认存储在 /var/lib/mysql 目录下

mysql> system ls /var/lib/mysql/binlog.*
/var/lib/mysql/binlog.000001  /var/lib/mysql/binlog.index

3、自定义日志

配置项	用途
server-id=数字	指定id值（1～255）
log-bin	启用binlog日志
max-binlog-size=数值m	指定日志文件容量，默认1G

[root@mysql52 ~]# vim /etc/my.cnf.d/mysql-server.cnf 
[mysqld]
log-bin=/mylog/mysql52   # 定义日志目录和日志文件名(手动添加)
:wq
[root@mysql52 ~]# mkdir /mylog  # 创建目录
[root@mysql52 ~]# chown  mysql  /mylog   # 修改目录所有者mysql用户
[root@mysql52 ~]# setenforce  0   # 关闭selinux
[root@mysql52 ~]# systemctl  restart mysqld   # 重启服务
[root@mysql52 ~]# ls /mylog/  # 查看日志目录
mysql52.000001  mysql52.index
[root@mysql52 ~]# mysql  # 登陆服务
Mysql> show master status ； # 查看日志信息
+----------------+----------+--------------+------------------+-------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| mysql52.000001 |      156 |              |                  |                   |
+----------------+----------+--------------+------------------+-------------------+

4、手动创建新日志文件

默认日志文件容量大于1G时会自动创建新的日志文件，在日志文件没写满时，执行的所有写命令都会保存到当前使用的日志文件里。

# 刷新前查看
mysql> show master status;
+----------------+----------+--------------+------------------+-------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| mysql52.000001 |      156 |              |                  |                   |
+----------------+----------+--------------+------------------+-------------------+

# 刷新日志
mysql> flush logs;
mysql> flush logs;
# 刷新一次创建一个新日志
mysql> show master status;
+----------------+----------+--------------+------------------+-------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| mysql52.000003 |      156 |              |                  |                   |
+----------------+----------+--------------+------------------+-------------------+

# 只要服务重启就会创建新日志
[root@mysql52 ~]# systemctl  restart mysqld 
[root@mysql52 ~]# mysql
Mysql> show master status;
+----------------+----------+--------------+------------------+-------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| mysql52.000004 |      156 |              |                  |                   |
+----------------+----------+--------------+------------------+-------------------+

5、相关命令

mysql> show master  status;             # 查看正在使用的日志
mysql> show binary logs;                # 查看已有的日志文件
mysql> purge master logs to "日志名";    # 删除日志文件名之前的所有日志文件
mysql> reset master;                    # 删除所有日志，重新创建日志
mysql> show binlog events in ""日志名;    # 查看日志文件内容
Log_name：    日志文件名
Pos：         命令在日志文件中的起始位置
Event_type：  事件类型，例如 Query、Table_map、Write_rows 等
Server_id：   服务器 ID
End_log_pos： 命令在文件中的结束位置，以字节为单位
Info：        执行命令信息

二、使用日志恢复数据

把查看到的文件内容管道给连接mysql服务的命令执行

查看文件全部内容，适用于恢复所有数据

mysqlbinlog /目录/日志文件名 | mysql –uroot -p密码

在mysql52主机执行如下操作

# 重置日志
mysql> reset master;
# 查看日志
mysql> show master status;
+----------------+----------+--------------+------------------+-------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| mysql52.000001 |      156 |              |                  |                   |
+----------------+----------+--------------+------------------+-------------------+
# 建库
mysql> create database gamedb;
# 建表
mysql> create table gamedb.t1(name char(10),class char(3));
# 插入记录
mysql> insert into gamedb.t1 values ("yaya","aaa");
Query OK, 1 row affected (0.08 sec)
mysql> insert into gamedb.t1 values ("yaya","aaa");
Query OK, 1 row affected (0.04 sec)
mysql> insert into gamedb.t1 values ("yaya","aaa");
Query OK, 1 row affected (0.08 sec)
# 查看表记录
mysql> select  * from gamedb.t1;
+------+-------+
| name | class |
+------+-------+
| yaya | aaa   |
| yaya | aaa   |
| yaya | aaa   |
+------+-------+
mysql> exit
# 把日志文件拷贝给恢复数据的服务器,比如 mysql50
[root@mysql52 ~]# scp  /mylog/mysql52.000001 root@192.168.88.50:/root/

在MySQL50 使用日志恢复数据

# 查看日志
[root@mysql50 ~]# ls /root/mysql52.000001 
/root/mysql52.000001
# 执行日志恢复数据
[root@mysql50 ~]# mysqlbinlog /root/mysql52.000001 | mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
# 连接服务查看数据
[root@mysql50 ~]# mysql -uroot -p123456 -e 'select * from gamedb.t1'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+-------+
| name | class |
+------+-------+
| yaya | aaa   |
| yaya | aaa   |
| yaya | aaa   |
+------+-------+