备份

lg
220 阅读11分钟

备份

物理备份

备份方法

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

备份步骤

源主机
  1. 停止服务
[root@node10 ~]# systemctl stop mysqld
  1. 拷贝/var/lib/mysql到目标主机
[root@node10 ~]# scp -r /var/lib/mysql root@192.168.4.20:/var/lib/mysql
目标主机
  1. 修改属主属组
[root@node20 ~]# chown -R mysql:mysql /var/lib/mysql
  1. 启动服务
[root@node20 ~]# systemctl start mysqld
  1. 连接测试
[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进行逻辑备份

备份策略

  • 完全备份：备份所有数据（单一或多个库、单一或多张表、整个数据库）
  • 只备份更新数据：
    • 差异备份：备份自完全备份后产生的数据
    • 增量备份：备份自上次备份之后产生的数据

完全备份

命令格式

  • 备份命令
mysqldump -u用户名 -p密码 > 路径/文件名.sql
  • 恢复命令
mysql -u用户名 -p密码 < 路径/备份文件名.sql

备份示例

备份所有库
  • 源数据库
[root@node10 ~]# mysqldump -uroot -pNSD2021@tedu.cn --all-databases > dbbackup/alldb.sql
或
[root@node10 ~]# mysqldump -uroot -pNSD2021@tedu.cn -A > dbbackup/alldb.sql

# 拷贝文件到目标服务器
[root@node10 ~]# rsync -r dbbackup root@192.168.4.20:/root/
  • 目标数据库
[root@node20 ~]# mysql -uroot -pNSD2021@tedu.cn < dbbackup/alldb.sql
备份某一个库
  • 源数据库
[root@node10 ~]# mysqldump -uroot -pNSD2021@tedu.cn nsd2021 > dbbackup/tedu_db.sql

# 拷贝文件到目标服务器
[root@node10 ~]# rsync -r dbbackup root@192.168.4.20:/root/
  • 目标数据库
# 删除库，以便测试结果
[root@node20 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> drop database nsd2021;

mysql> CREATE DATABASE nsd2021 DEFAULT CHARSET utf8mb4;
[root@node20 ~]# mysql -uroot -pNSD2021@tedu.cn nsd2021 < dbbackup/tedu_db.sql
备份某一张表
  • 源数据库
[root@node10 ~]# mysqldump -uroot -pNSD2021@tedu.cn nsd2021 salary > dbbackup/nsd2021_salary.sql

# 拷贝文件到目标服务器
[root@node10 ~]# rsync -r dbbackup root@192.168.4.20:/root/
  • 目标数据库
# 清空表，以便测试结果
mysql> use nsd2021;
mysql> truncate salary;

[root@node20 ~]# mysql -uroot -pNSD2021@tedu.cn nsd2021 < dbbackup/nsd2021_salary.sql
备份某多个库
  • 源数据库
[root@node10 ~]# mysqldump -uroot -pNSD2021@tedu.cn -B nsd2021 mysql > dbbackup/twodb.sql

# 拷贝文件到目标服务器
[root@node10 ~]# rsync -r dbbackup root@192.168.4.20:/root/
  • 目标数据库
[root@node20 ~]# mysql -uroot -pNSD2021@tedu.cn < dbbackup/twodb.sql
备份多张表
  • 源数据库
[root@node10 ~]# mysqldump -uroot -pNSD2021@tedu.cn nsd2021 employees salary > dbbackup/nsd2021_employees_salary.sql

# 拷贝文件到目标服务器
[root@node10 ~]# rsync -r dbbackup root@192.168.4.20:/root/
  • 目标数据库
# 清空表，以便测试结果
mysql> truncate salary;
mysql> delete from employees;

[root@node20 ~]# mysql -uroot -pNSD2021@tedu.cn nsd2021 < dbbackup/nsd2021_employees_salary.sql

增量备份

binlog日志

binlog日志概述
  • 也叫做二进制日志
  • 它是MySQL服务日志文件的一种
  • 默认没有启用
  • 记录除查询之外的所有SQL命令
  • 可用于数据的备份和恢复
  • 它是MySQL主从同步的必要条件
启用binlog日志
  • 修改/etc/my.cnf启用日志
配置项用途
server_id=数字指定服务器id值（1-255）
log-bin/log_bin=目录/文件名启用binlog日志
max_binlog_size=数值m日志文件容量，默认1GB
  • 启用 binlog
[root@mysql1 ~]# vim /etc/my.cnf
[mysqld]
server_id = 11
log-bin
... ...

[root@mysql1 ~]# systemctl restart mysqld

# 查看结果
[root@mysql1 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| mysql1-bin.000001 |      154 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
[root@mysql1 ~]# ls /var/lib/mysql/mysql1-bin.*
/var/lib/mysql/mysql1-bin.000001  /var/lib/mysql/mysql1-bin.index
手动创建binlog日志文件
  • binlog文件默认存在/var/lib/mysql目录下
  • 也可以手工进行修改
# 创建用于保存日志文件的目录
[root@mysql1 ~]# mkdir /mybinlog
[root@mysql1 ~]# chown mysql:mysql /mybinlog/

# 修改配置文件
[root@node10 ~]# vim /etc/my.cnf
[mysqld]
server_id = 11
log-bin = /mybinlog/mylog
... ...

# 验证
[root@mysql1 ~]# systemctl restart mysqld
[root@mysql1 ~]# ls /mybinlog/
mylog.000001  mylog.index
mysql> show master status;
+--------------+----------+--------------+------------------+-------------------+
| File         | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------+----------+--------------+------------------+-------------------+
| mylog.000001 |      154 |              |                  |                   |
+--------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
  • 新建binlog日志文件
# 方法一：重启mysqld服务
[root@mysql1 ~]# systemctl restart mysqld
[root@mysql1 ~]# ls /mybinlog/
mylog.000001  mylog.000002  mylog.index
mysql> show master status;
+--------------+----------+--------------+------------------+-------------------+
| File         | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------+----------+--------------+------------------+-------------------+
| mylog.000002 |      154 |              |                  |                   |
+--------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

# 方法二：flush指令
mysql> flush logs;
Query OK, 0 rows affected (0.10 sec)

mysql> show master status;
+--------------+----------+--------------+------------------+-------------------+
| File         | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------+----------+--------------+------------------+-------------------+
| mylog.000003 |      154 |              |                  |                   |
+--------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
[root@node10 ~]# ls /mybinlog/
mylog.000001  mylog.000002  mylog.000003  mylog.index

# 方法三：备份时刷新日志
[root@mysql1 ~]# mysqldump -uroot -pNSD2021@tedu.cn --flush-logs nsd2021 > dbbackup/nsd2021_full.sql
[root@mysql1 ~]# ls /mybinlog/
mylog.000001  mylog.000002  mylog.000003  mylog.000004  mylog.index
mysql> show master status;
+--------------+----------+--------------+------------------+-------------------+
| File         | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------+----------+--------------+------------------+-------------------+
| mylog.000004 |      154 |              |                  |                   |
+--------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
删除已有binlog日志
  • 删除指定编号之前的日志文件
# 删除mylog.000003（不包含）之前的日志
mysql> purge master logs to "mylog.000003";
Query OK, 0 rows affected (0.05 sec)
[root@node10 ~]# ls /mybinlog/
mylog.000003  mylog.000004  mylog.index

# 删除所有日志，重新新日志
mysql> reset master;
Query OK, 0 rows affected (0.13 sec)

mysql> show master status;
+--------------+----------+--------------+------------------+-------------------+
| File         | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------+----------+--------------+------------------+-------------------+
| mylog.000001 |      154 |              |                  |                   |
+--------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
[root@node10 ~]# ls /mybinlog/
mylog.000001  mylog.index
binlog日志内容
通过binlog日志修改数据库
  • 向departments表中插入数据
mysql> use nsd2021;
mysql> INSERT INTO departments(dept_name) VALUES ('sales1');
Query OK, 1 row affected (0.04 sec)

mysql> INSERT INTO departments(dept_name) VALUES ('sales2');
Query OK, 1 row affected (0.04 sec)

mysql> INSERT INTO departments(dept_name) VALUES ('sales3');
Query OK, 1 row affected (0.04 sec)

mysql> INSERT INTO departments(dept_name) VALUES ('sales4');
Query OK, 1 row affected (0.05 sec)

mysql> show master status;
+--------------+----------+--------------+------------------+-------------------+
| File         | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------+----------+--------------+------------------+-------------------+
| mylog.000001 |     1274 |              |                  |                   |
+--------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)

[root@mysql1 ~]# mysqlbinlog /mybinlog/mylog.000001
  • 在目标主机上通过binlog同步源主机上的数据
# 将binlog日志拷贝到目标主机
[root@mysql1 ~]# rsync -r /mybinlog 192.168.1.12:/root

# 在目标主机上执行一遍binlog日志
[root@mysql2 ~]# mysqlbinlog mybinlog/mylog.000001 | mysql -uroot -pNSD2021@tedu.cn
修改binlog记录格式
  • binlog日记记录方式
    • row：行模式
    • statement：陈述模式
    • mixed：混合模式
mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)
  • 修改日志记录格式
[root@mysql1 ~]# vim /etc/my.cnf
[mysqld]
server_id = 11
log-bin = /mybinlog/mylog
binlog_format = "mixed"
... ...

[root@mysql1 ~]# systemctl restart mysqld

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.01 sec)
  • 检查日志
# 修改数据
mysql> use nsd2021;
mysql> INSERT INTO departments(dept_name) VALUES('sales5');
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO departments(dept_name) VALUES('sales6');
Query OK, 1 row affected (0.03 sec)

# 查看日志
[root@mysql1 ~]# mysqlbinlog /mybinlog/mylog.000002
通过binlog日志修改指定范围内的数据
  • 执行删除操作
mysql> delete from departments where dept_name like 'sales_';
  • 拷贝日志文件到目标服务器
[root@mysql1 ~]# rsync -r /mybinlog 192.168.4.20:/root
  • 在目标主机上查看日志，找到要恢复数据的起始和结束偏移量
# 读取日志，找到插入sales5日志上面的at偏移量作为起始值，找到插入sales6日志下面的COMMIT之后的at偏移量作为结束值
[root@mysql2 ~]# mysqlbinlog mybinlog/mylog.000002 

[root@mysql2 ~]# mysqlbinlog --start-position=328 --stop-position=810 mybinlog/mylog.000002 | mysql -uroot -pNSD2021@tedu.cn

Percona

Percona概述

Percona解决的问题

  • 物理备份缺点

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

  • mysqldump备份缺点

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

Percona特点

  • Percona提供的xtrabackup是一款强大的在线热备份工具
  • 备份过程中不锁库表，适合生产环境
  • 由专业组织Percona提供（改进MySQL分支）
  • 主要包含两个组件：
    • xtrabackup：C程序，支持InnoDB/XtraDB
    • innobackupex：以Perl脚本封装xtrabackup，还支持MyISAM

安装

  • 在yum服务器上生成yum源
[root@room8pc16 mysql_soft]# cp percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm libev-4.15-1.el6.rf.x86_64.rpm /var/www/html/
[root@room8pc16 mysql_soft]# cd /var/www/html/
[root@room8pc16 mysql]# createrepo -v .
  • 在数据库服务器上安装percona
[root@mysql1 ~]# yum install -y percona-xtrabackup-24

Percona完全备份数据库

语法

  • 备份语法
innobackupex -u 用户名 -p 密码 备份目录名 [--no-timestamp]
# 备份目录名不用提前创建
  • 恢复语法
innobackupex --apply-log 备份目录名   # 准备恢复数据 
innobackupex --copy-back 备份目录名   # 复制数据
  • 选项说明
常用选项含义
-u用户名
-p密码
--no-timestamp不用日期命名备份文件存储的子目录名
--apply-log准备恢复数据
--copy-back拷贝数据
--databases--databases="库名" # 1个库
--databases="库1 库2" # 多个库
--databases="库1.表" # 1张表

示例

  • 源服务器完全备份数据到/pbackup/目录
# 方法一：在备份目录下生成日期时间子目录
[root@mysql1 ~]# innobackupex -uroot -pNSD2021@tedu.cn /pbackup/fullbackup
[root@mysql1 ~]# ls /pbackup/fullbackup/
2021-04-07_11-42-08

# 方法二：不生成子目录
[root@mysql1 ~]# innobackupex -uroot -pNSD2021@tedu.cn /pbackup/full20210407 --no-timestamp
  • 目标服务器恢复数据
# 将完全备份目录拷贝到目标服务器
[root@mysql1 ~]# rsync -r /pbackup root@192.168.1.12:/root

# 查看备份目录状态（非必须步骤）
[root@mysql2 ~]# cat pbackup/full20210407/xtrabackup_checkpoints
backup_type = full-backuped   # 已完全备份
from_lsn = 0                  # 起始日志序列号
to_lsn = 3639394              # 结束日志序列号
last_lsn = 3639403
compact = 0
recover_binlog_info = 0

# 准备恢复数据
[root@mysql2 ~]# innobackupex --apply-log /root/pbackup/full20210407/

# 查看备份目录状态（非必须步骤）
[root@mysql2 ~]# cat pbackup/full20210407/xtrabackup_checkpoints
backup_type = full-prepared
from_lsn = 0
to_lsn = 3639394
last_lsn = 3639403
compact = 0
recover_binlog_info = 0

# 停止mysqld服务
[root@mysql2 ~]# systemctl stop mysqld

# 恢复时要求mysql工作目录为空
[root@mysql2 ~]# rm -rf /var/lib/mysql/*

# 恢复数据
[root@mysql2 ~]# innobackupex --copy-back /root/pbackup/full20210407/

# 修改工作目录的属主和属组
[root@mysql2 ~]# chown -R mysql:mysql /var/lib/mysql

# 启动服务
[root@mysql2 ~]# systemctl start mysqld

使用完全备份恢复单张表

表空间

  • 数据驻留在磁盘上的位置，称为数据目录，如/var/lib/mysql
  • 从MySQL 5.6.6版开始，用户可以创建并指定他们想要存储数据的表空间，从而实现数据操作和恢复过程的吞吐量。
  • InnoDB的每表文件功能为每个表提供了单独的.ibd数据和索引文件，代表一个单独的通用表空间。这样数据库中的每个表都可以指向数据目录的不同位置。

恢复单表步骤

  1. 删除待恢复表的表空间 (表名.ibd)
  2. 导出备份目录中的表信息
  3. 拷贝表信息文件到数据库目录下
  4. 修改表信息文件的所有者及组用户为mysql
  5. 从备份目录导入表空间
  6. 删除数据库目录下的表信息文件
  7. 验证：查看表记录

恢复示例

  • 误删除表内容
[root@mysql2 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> use nsd2021;
mysql> truncate salary;
  • 恢复准备：删除表空间
mysql> alter table salary discard tablespace;
[root@mysql2 ~]# ls /var/lib/mysql/nsd2021/   # salary.ibd消失
  • 导出表信息
[root@mysql2 ~]# innobackupex --apply-log --export /root/pbackup/fullbackup/2021-04-07_11-42-08/
  • 拷贝表信息文件到数据库目录下
[root@mysql2 ~]# cp /root/pbackup/fullbackup/2021-04-07_11-42-08/nsd2021/salary.{cfg,exp,ibd} /var/lib/mysql/nsd2021/
  • 修改表信息文件的所有者及组用户为mysql
[root@mysql2 ~]# chown mysql:mysql /var/lib/mysql/nsd2021/salary.*
  • 导入表空间
mysql> alter table salary import tablespace;
  • 删除数据库目录下的表信息文件
[root@mysql2 ~]# rm -f /var/lib/mysql/nsd2021/salary.{cfg,exp}
  • 验证：查看表记录
mysql> select count(*) from salary;
+----------+
| count(*) |
+----------+
|     8590 |
+----------+
1 row in set (0.00 sec)

Percona增量备份

增量备份基础

  • 备份自上次备份以来的数据

  • 一般来说，需要有一个完全备份

  • 备份语法

innobackupex -u 用户名 -p 密码 --incremental 备份目录 --incremental-basedir=备份目录名   --no-timestamp
  • 恢复语法
# 准备恢复数据
innobackupex --apply-log --redo-only 首次备份目录名
# 合并数据
innobackupex --apply-log --redo-only 首次备份目录名 --incremental-dir=备份目录名
# 恢复数据
innobackupex --copy-back 首次备份目录名
常用选项含义
--apply-log准备恢复数据
--redo-only日志合并
--incremental 目录名新数据存放目录
--incremental-basedir=目录名备份新数据参考目录
--incremental-dir=目录名合并数据存放目录

增量备份示例

  • 在源服务器上添加数据
mysql> use nsd2021;
mysql> insert into departments(dept_name) values('sales10');
Query OK, 1 row affected (0.15 sec)

mysql> insert into departments(dept_name) values('sales11');
Query OK, 1 row affected (0.13 sec)

mysql> insert into departments(dept_name) values('sales12');
Query OK, 1 row affected (0.15 sec)
  • 在源服务器上进行增量备份
[root@mysql1 ~]# innobackupex -uroot -pNSD2021@tedu.cn --incremental /pbackup/incr20210407 --incremental-basedir=/pbackup/full20210407/ --no-timestamp

[root@mysql1 ~]# cat /pbackup/incr20210407/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 3639394
to_lsn = 3643003
last_lsn = 3643012
compact = 0
recover_binlog_info = 0
  • 在目标服务上进行恢复数据
# 在目标服务器上删除原有日志
[root@mysql2 ~]# rm -rf /root/pbackup/

# 将日志拷贝到目标服务器
[root@mysql1 ~]# rsync -r /pbackup 192.168.4.12:/root/

# 准备恢复数据
[root@mysql2 ~]# innobackupex --apply-log --redo-only /root/pbackup/full20210407/
[root@mysql2 ~]# cat /root/pbackup/full20210407/xtrabackup_checkpoints
backup_type = log-applied
from_lsn = 0
to_lsn = 3639394
last_lsn = 3639403
compact = 0
recover_binlog_info = 0

# 合并日志
[root@mysql2 ~]# innobackupex --apply-log --redo-only /root/pbackup/full20210407/ --incremental-dir=/root/pbackup/incr20210407/
[root@mysql2 ~]# cat /root/pbackup/full20210407/xtrabackup_checkpoints
backup_type = log-applied
from_lsn = 0
to_lsn = 3643003
last_lsn = 3643012
compact = 0
recover_binlog_info = 0

# 停止目标服务器mysqld服务
[root@mysql2 ~]# systemctl stop mysqld

# 删除工作目录
[root@mysql2 ~]# rm -rf /var/lib/mysql/*

# 恢复数据
[root@mysql2 ~]# innobackupex --copy-back /root/pbackup/full20210407/

# 修改属主属组
[root@mysql2 ~]# chown -R mysql:mysql /var/lib/mysql

# 启动目标服务器mysqld服务
[root@mysql2 ~]# systemctl start mysqld

# 验证
[root@mysql2 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> use nsd2021;
mysql> select * from departments;
  • 重复增量备份
# 源服务器增加数据
[root@mysql1 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> use nsd2021;
mysql> insert into departments(dept_name) values('sales13');
Query OK, 1 row affected (0.06 sec)

mysql> insert into departments(dept_name) values('sales14');
Query OK, 1 row affected (0.06 sec)

mysql> insert into departments(dept_name) values('sales15');
Query OK, 1 row affected (0.06 sec)

# 源服务器增量备份
[root@mysql1 ~]# innobackupex -uroot -pNSD2021@tedu.cn --incremental /pbackup/incr20210407-2 --incremental-basedir=/pbackup/incr20210407/ --no-timestamp

# 将日志拷贝到目标服务器
[root@mysql1 ~]# scp -r /pbackup/incr20210407-2/ 192.168.4.12:/root/pbackup/

# 准备恢复数据
[root@mysql2 ~]# innobackupex --apply-log --redo-only /root/pbackup/full20210407/

# 合并日志
[root@mysql2 ~]# innobackupex --apply-log --redo-only /root/pbackup/full20210407/ --incremental-dir=/root/pbackup/incr20210407-2/

# 停止目标服务器mysqld服务
[root@mysql2 ~]# systemctl stop mysqld

# 删除工作目录
[root@mysql2 ~]# rm -rf /var/lib/mysql/*

# 恢复数据
[root@mysql2 ~]# innobackupex --copy-back /root/pbackup/full20210407/

# 修改属主属组
[root@mysql2 ~]# chown -R mysql:mysql /var/lib/mysql

# 启动目标服务器mysqld服务
[root@mysql2 ~]# systemctl start mysqld

# 验证
[root@mysql2 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> use tedu_db;
mysql> select * from departments;
avatar
文章
阅读
粉丝