MySQL物理备份实战
2.1 xtrabackup原理及安装
1. 备份的过程
(1) 非InnoDB表,进行短暂的锁表,然后Copy数据文件
(2) 对于InnoDB表,立即出发checkpoint,会立即记录一个LSN,COPY数据文件.
(3) 将备份过程中产生的redo进行截取和保存,并记录此时最新的LSN
2. 恢复过程
模拟了CSR的全过程,在恢复之前,将数据的LSN号和redo LSN号追平
恢复方法就是直接cp回去即可。
Xtrabackup包含如下三个步骤：
备份（--backup）->应用日志（--apply-log）->恢复（--copy-back）
https://www.percona.com/downloads/Percona-XtraBackup-2.4/LATEST/
1. 安装xtrabackup
[root@test ~]# wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
[root@test ~]# yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev
[root@test ~]# wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.22/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.22-1.el7.x86_64.rpm
[root@test ~]# yum -y install percona-xtrabackup-24-2.4.22-1.el7.x86_64.rpm
2.2 xtrabackup全备及恢复
在某MySQL5.7线上系统，如果某员工删库跑路，把MySQL数据目录误删，请使用xtrabackup进行恢复
1. xtrabackup全备
[root@test ~]# innobackupex --user=root --password=test --no-timestamp /opt/full
[root@test ~]# ll /opt/full
total 77880
-rw-r----- 1 root root 487 May 17 10:22 backup-my.cnf
drwxr-x--- 2 root root 52 May 17 10:22 cool
-rw-r----- 1 root root 868 May 17 10:22 ib_buffer_pool
-rw-r----- 1 root root 79691776 May 17 10:22 ibdata1
drwxr-x--- 2 root root 4096 May 17 10:22 mysql
drwxr-x--- 2 root root 8192 May 17 10:22 performance_schema
drwxr-x--- 2 root root 8192 May 17 10:22 sys
drwxr-x--- 2 root root 54 May 17 10:22 test
drwxr-x--- 2 root root 4096 May 17 10:22 wordpress
-rw-r----- 1 root root 108 May 17 10:22 xtrabackup_binlog_info
-rw-r----- 1 root root 135 May 17 10:22 xtrabackup_checkpoints
-rw-r----- 1 root root 596 May 17 10:22 xtrabackup_info
-rw-r----- 1 root root 2560 May 17 10:22 xtrabackup_logfile
2. 模拟数据库故障
[root@test ~]# pkill mysqld
[root@test ~]# ls /data/mysql/data/
auto.cnf cool ib_logfile1 public_key.pem slave-binlog.000002 test-relay-bin.000003
ca-key.pem error.log master.info relay-log.info slave-binlog.index test-relay-bin.000004
ca.pem ib_buffer_pool mysql server-cert.pem sys test-relay-bin.index
client-cert.pem ibdata1 performance_schema server-key.pem test wordpress
client-key.pem ib_logfile0 private_key.pem slave-binlog.000001 test-relay-bin.000002
[root@test ~]# rm -rf /data/mysql/data/*
[root@test ~]# ls /data/mysql/data/
[root@test ~]#
3. 恢复完全备份的数据
[root@test ~]# innobackupex --apply-log /opt/full/
4. 拷贝数据到/data/mysql/data下及修改权限
[root@test ~]# cp -a /opt/full/* /data/mysql/data/
[root@test ~]# chown -R mysql.mysql /data/mysql/data/*
5. 启动数据库查看数据
[root@test ~]# service mysql start
Starting MySQL.Logging to '/data/mysql/data/error.log'.
. SUCCESS!
[root@test ~]# mysql -uroot -ptest
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.32-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cool |
| mysql |
| performance_schema |
| sys |
| test |
| wordpress |
+--------------------+
7 rows in set (0.00 sec)
2.3 xtrabackup全备+增量+binlog恢复演练
周日晚上23:00全备，周一周二晚上23:00增备，周三早上10:00数据库奔溃。
恢复：周日全备+周一增备+周二增备+（周二晚上十一点至周三早上十点binlog恢复）
先修改my.cnf，指定binlog目录（生产环境上一般binlog需要单独一个目录）
[root@localhost ~]# mkdir /data/mysql/binlog
[root@localhost ~]# chown -R mysql.mysql /data/mysql/binlog/
[root@localhost ~]# vi /etc/my.cnf
[root@localhost ~]# cat /etc/my.cnf
[client]
port = 3306
socket = /tmp/mysql.sock
[mysql]
no-auto-rehash
[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /data/mysql/data
port = 3306
socket = /tmp/mysql.sock
log-error = error.log
slow_query_log_file = slow.log
character-set-server = utf8
open_files_limit = 65535
max_connections = 100
max_connect_errors = 100000
lower_case_table_names =1
server_id=1
gtid_mode=on
enforce_gtid_consistency=on
#binlog
log_bin=/data/mysql/binlog/binlog
log-slave-updates=1
binlog_format=row
#relay log
skip-slave-start=1
relay_log_purge=0
重启数据库
[root@localhost ~]# service mysql restart
1. 模拟数据
[root@test ~]# mysql -uroot -ptest
mysql> create database t1 charset utf8mb4;
mysql> use t1;
mysql> create table t1(id int);
mysql> insert into t1 value(1),(2),(3);
mysql> commit;
2. 进行周日的全备
[root@test ~]# innobackupex --user=root --password=test --no-timestamp /opt/full_$(date +%F)
[root@test ~]# ls /opt/full_2021-05-19
backup-my.cnf ib_buffer_pool performance_schema test xtrabackup_checkpoints
ceshi ibdata1 sys wordpress xtrabackup_info
cool mysql t1 xtrabackup_binlog_info xtrabackup_logfile
[root@test ~]# cat /opt/full_2021-05-19/xtrabackup_checkpoints
backup_type = full-backuped #类型全备
from_lsn = 0
to_lsn = 7251541
last_lsn = 7251550
compact = 0
recover_binlog_info = 0
flushed_lsn = 7251550
3. 模拟周一数据变化
mysql> create database inc1 charset utf8mb4;
mysql> use inc1;
mysql> create table t1(id int);
mysql> insert into t1 values(1),(2),(3);
mysql> commit;
4. 进行周一的增量备份
周一的增量基于周日的全备
[root@test ~]# innobackupex --user=root --password=test --no-timestamp --incremental-basedir=/opt/full_2021-05-19 --incremental /opt/inc1
[root@test ~]# ls /opt
full_2021-05-19 inc1
5. 检查备份的LSN
[root@test ~]# cat /opt/full_2021-05-19/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 7251541
last_lsn = 7251550
compact = 0
recover_binlog_info = 0
flushed_lsn = 7251550
[root@test ~]# cat /opt/inc1/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 7251541
to_lsn = 7282961
last_lsn = 7282970
compact = 0
recover_binlog_info = 0
flushed_lsn = 7282970
6. 模拟周二数据变化
mysql> create database inc2 charset utf8mb4;
mysql> use inc2;
mysql> create table t1(id int);
mysql> insert into t1 values(1),(2),(3);
mysql> commit;
7. 进行周二的增量备份
周二的增量基于周一的增量进行备份，余下类推
[root@test ~]# innobackupex --user=root --password=test --no-timestamp --incremental-basedir=/opt/inc1 --incremental /opt/inc2
[root@test ~]# ls /opt
full_2021-05-19 inc1 inc2
8. 检查备份的LSN
[root@test ~]# cat /opt/full_2021-05-19/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 7251541
last_lsn = 7251550
compact = 0
recover_binlog_info = 0
flushed_lsn = 7251550
[root@test ~]# cat /opt/inc1/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 7251541
to_lsn = 7282961
last_lsn = 7282970
compact = 0
recover_binlog_info = 0
flushed_lsn = 7282970
[root@test ~]# cat /opt/inc2/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 7282961
to_lsn = 7288594
last_lsn = 7288603
compact = 0
recover_binlog_info = 0
flushed_lsn = 7288603
9. 模拟周三数据变化
create database inc3 charset utf8mb4;
use inc3;
create table t1(id int);
insert into t1 values(1),(2),(3);
commit;
10. 模拟上午10点数据库奔溃
pkill mysqld
\rm -rf /data/mysql/data/*
思路：查找可用备份full_2021-05-19+inc1+inc2，周三的增量备份是晚上23:00，那周三早上10:00数据库崩溃了，时间还没到来不及做周三的增量。除了全备，增量1，增量2到故障时间点的binlog。
如何恢复备份？
恢复全备+增量+binlog
11. 恢复过程
（1）整理full
[root@test ~]# innobackupex --apply-log --redo-only /opt/full_2021-05-19
#--redo-only的意思 这个选项应该在整理全备和所有增量合并时，除了最后一次增量。
（2）合并inc1到full_2021-05-19，并整理备份
[root@test ~]# innobackupex --apply-log --redo-only --incremental-dir=/opt/inc1 /opt/full_2021-05-19
#对比xtrabackup_checkpoints
[root@test ~]# cat /opt/full_2021-05-19/xtrabackup_checkpoints
backup_type = log-applied
from_lsn = 0
to_lsn = 7282961
last_lsn = 7282970
compact = 0
recover_binlog_info = 0
flushed_lsn = 7282970
[root@test ~]# cat /opt/inc1/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 7251541
to_lsn = 7282961
last_lsn = 7282970
compact = 0
recover_binlog_info = 0
flushed_lsn = 7282970
（3）合并inc2到full_2021-05-19，并整理备份
[root@test ~]# innobackupex --apply-log --incremental-dir=/opt/inc2 /opt/full_2021-05-19
#对比inc2与全备的
[root@test ~]# cat /opt/full_2021-05-19/xtrabackup_checkpoints
backup_type = full-prepared
from_lsn = 0
to_lsn = 7288594
last_lsn = 7288603
compact = 0
recover_binlog_info = 0
flushed_lsn = 7288603
[root@test ~]# cat /opt/inc2/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 7282961
to_lsn = 7288594
last_lsn = 7288603
compact = 0
recover_binlog_info = 0
flushed_lsn = 7288603
（4）最后一次整理full_2021-05-19
[root@test ~]# innobackupex --apply-log /opt/full_2021-05-19
至此，已经恢复到周二晚上23:00的数据了。
12. 截取二进制日志
起点
[root@test ~]# cat /opt/inc2/xtrabackup_binlog_info
binlog.000001 1940 18d11475-b5fd-11eb-9b94-000c29c40ee0:1-9
终点：从gtid为10的开始找到12
SET @@SESSION.GTID_NEXT= '18d11475-b5fd-11eb-9b94-000c29c40ee0:12'/*!*/;
[root@localhost ~]# mysqlbinlog /data/mysql/binlog/binlog.000001 |grep 'SET'
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
SET @@SESSION.GTID_NEXT= '18d11475-b5fd-11eb-9b94-000c29c40ee0:1'/*!*/;
SET TIMESTAMP=1623131800/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
SET @@SESSION.GTID_NEXT= '18d11475-b5fd-11eb-9b94-000c29c40ee0:2'/*!*/;
SET TIMESTAMP=1623131813/*!*/;
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '18d11475-b5fd-11eb-9b94-000c29c40ee0:3'/*!*/;
SET TIMESTAMP=1623131822/*!*/;
SET @@SESSION.GTID_NEXT= '18d11475-b5fd-11eb-9b94-000c29c40ee0:4'/*!*/;
SET TIMESTAMP=1623131969/*!*/;
SET @@SESSION.GTID_NEXT= '18d11475-b5fd-11eb-9b94-000c29c40ee0:5'/*!*/;
SET TIMESTAMP=1623131981/*!*/;
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '18d11475-b5fd-11eb-9b94-000c29c40ee0:6'/*!*/;
SET TIMESTAMP=1623131988/*!*/;
SET @@SESSION.GTID_NEXT= '18d11475-b5fd-11eb-9b94-000c29c40ee0:7'/*!*/;
SET TIMESTAMP=1623132021/*!*/;
SET @@SESSION.GTID_NEXT= '18d11475-b5fd-11eb-9b94-000c29c40ee0:8'/*!*/;
SET TIMESTAMP=1623132032/*!*/;
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '18d11475-b5fd-11eb-9b94-000c29c40ee0:9'/*!*/;
SET TIMESTAMP=1623132039/*!*/;
SET @@SESSION.GTID_NEXT= '18d11475-b5fd-11eb-9b94-000c29c40ee0:10'/*!*/;
SET TIMESTAMP=1623132070/*!*/;
SET @@SESSION.GTID_NEXT= '18d11475-b5fd-11eb-9b94-000c29c40ee0:11'/*!*/;
SET TIMESTAMP=1623132070/*!*/;
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '18d11475-b5fd-11eb-9b94-000c29c40ee0:12'/*!*/;
SET TIMESTAMP=1623132070/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
截取日志
[root@localhost ~]# mysqlbinlog --skip-gtids --include-gtids='18d11475-b5fd-11eb-9b94-000c29c40ee0:10-12' /data/mysql/binlog/binlog.000001>/opt/binlog.sql
13. 恢复备份数据
[root@localhost ~]# cp -a /opt/full_2021-06-08/* /data/mysql/data/
[root@localhost ~]# chown -R mysql.mysql /data/mysql/
[root@localhost ~]# service mysql start
[root@localhost ~]# mysql -uroot -ptest
mysql> set sql_log_bin=0;
mysql> source /opt/binlog.sql;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| inc1 |
| inc2 |
| inc3 |
| mysql |
| performance_schema |
| sys |
| t1 |
| wordpress |
+--------------------+
9 rows in set (0.01 sec)
mysql> use inc3
Database changed
mysql> show tables;
+----------------+
| Tables_in_inc3 |
+----------------+
| t1 |
+----------------+
1 row in set (0.00 sec)
mysql> select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
