数据备份与恢复

备份概述

备份方式

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

冷备份：在备份时停止数据库服务，将整个数据库复制到备份设备中。

热备份：在备份时数据库是运行的。

· 按照备份的数据分为：

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

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

物理备份

备份方法

• 拷贝/var/lib/mysql目录到目标即可

备份步骤

源主机

停止服务

[root@node10 ~]# systemctl stop mysqld

拷贝/var/lib/mysql到目标主机

[root@node10 ~]# scp -r /var/lib/mysql root@192.168.4.20:/var/lib/mysql

目标主机

修改属主属组

[root@node20 ~]# chown -R mysql:mysql /var/lib/mysql

启动服务

[root@node20 ~]# systemctl start mysqld

连接测试

[root@node20 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| nsd2021            |
+--------------------+
5 rows in set (0.00 sec)

使用mysqldump进行逻辑备份

备份策略

1. 完全备份：备份所有数据（单一或多个库、单一或多张表、整个数据库）
2. 只备份更新数据：

差异备份：备份自完全备份后产生的数据

增量备份：备份自上次备份之后产生的数据

备份系统三要素

灾备系统建设的国际标准

· 根据SHARE 78国际组织提出的标准，可以将系统容灾的级别划分为如下7级。

完全备份与恢复

物理备份及恢复

· 备份操作

cp   -r   /var/lib/mysql   /备份目录/mysql.bak 
tar   -zcf   /root/mysql.tar.gz   /var/lib/mysql/*

· 恢复操作

cp   -r  备份目录/mysql.bak       /var/lib/mysql/ 
tar  -zxf  /root/mysql.tar.gz  -C  /var/lib/mysql/ 
chown   -R   mysql:mysql   /var/lib/mysql

逻辑备份及恢复

完全备份

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

· 库名表示方式

库名表名列表	//备份1张表或多张表所有数据
-B库名列表	//备份1个库或多个库所有数据
--all  -databases或-A	//备份1台服务器所有数据

[root@mysql50~]#mysqldump -uroot -proot -A  >  /bakdir/allbak.sgl  

· 完全恢复

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

注意：恢复库时库名可以省略

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

mysqldump 备份缺点

物理备份缺点

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

mysqldump备份缺点

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

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

增量备份与恢复

软件介绍

1. 一款强大的在线热备份工具
2. 备份过程中不锁库表，适合生产环境
3. 由专业组织Percona提供(改进MySQL分支)

增量备份

·首次完全备份

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

·增量备份

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

增量恢复

·准备恢复数据

Xtrabackup  --prepare   --apply-log-only  --target-dir=/完全备份目录

· 合并数据

Xtrabackup  --prepare  --apply-log-only  --target-dir=/完全备份目录   --incremental-dir=/新数据目录名

· 拷贝数据

Xtrabackup  -copy-back  -target-dir=/ 完全备份目录

Xtrabackup  -copy-back  -target-dir=/ 完全备份目录

恢复步骤

· 具体如下：

1. 准备恢复数据
2. 合并数据(合并的顺序要和增量备份的顺序一致)
3. 清空数据库目录
4. 拷贝数据
5. 修改数据库目录所有者/组用户为mysql
6. 重启数据库服务

差异备份与恢复

差异备份

· 首次完全备份

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

· 差异备份

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

差异恢复

· 准备恢复数据

Xtrabackup --prepare --apply-log-only --target-dir=完全备份目录

· 合并数据

xtrabackup --prepare --apply-log-only  --target-dir=/完全备份目 --incremental-dir=/最后一次差异备份

·拷贝数据

Xtrabackup --copy-back  --target-dir=完全备份目录

恢复步骤

· 具体如下：

准备恢复数据

合并数据最后一次差异备份

清空数据库目录

拷贝数据

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

重启mysql服务

数据实时备份与恢复

binlog日志管理

日志介绍

·什么是binlog日志

① 也称做二进制日志

② MySQL 服务日志文件的一种

③ 保存除查询之外的所有SQL命令

④ 可用于数据的备份和恢复

⑤ 配置mysql主从同步的必要条件

查看日志

· 日志默认是启用的

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

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

/var/lib/mysql/binlog.000001 /var/lib/mysql/binlog.index

自定义日志

配 置 项                                 用 途

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=/  目录名/日志名  //指定存放目录和文件名

:wq

[root@mysql11]#mkdir  /mylog;chown  mysql  /mylog

[root@mysql11~]#systemctl  restart  mysqld

创建新日志文件

· Binlog日志文件默认1G容量，超出后会自动创建新日志

mysql> flush logs ;

]#systemctl restart mysqld

]#mysqldump -uroot -p密码 --flush-logs -B tarena > /t.sql

]#mysqldump -uroot -p密码 --flush-logs -A > /all.sql

恢复数据

相关命令

· 日志文件常用管理命令

mysql>show master status;

命令格式

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

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

]#mysqlbinlog       /目录/文件名 | mysql-uroot       -p密码

]#mysqlbinlog  /var/lib/mysql/binlog.000001   |mysql-uroot- proot