一、备份
1. mysqldump
常用参数
- --master-data=2 : 将备份时的二进制文件名以及位置信息写入至导出的文件中，会锁表，需要与single-transaction结合使用
- --single-transaction ： 备份时不锁表(Innodb)
- --hex-blob : 使用十六进制格式导出二进制字符串字段,如果有二进制数据就必须使用该选项，否则会显示乱码。
- --events(-E) : 导出事件。
- --routines(-R) : 导出存储过程及函数。
- --triggers : 导出触发器。
实例：
root@cd0fb7617eb9:/# mysqldump -uroot -p123456 --master-data=2 --single-transaction --hex-blob -E -R --triggers world > /tmp/world.sql注意：对于100G以内的数据量级，可以使用mysqldump，数据量过大，导致备份恢复数据比较慢。
2. Percona Xtrabackup
Percona Xtrabackup 属于数据库的物理备份，需要部署在mysql服务器，不能进行远程备份。
2.1 安装
- 安装依赖包
[root@ipa1 ~]# yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev- 下载软件包安装
[root@ipa1 tools]# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.12/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
[root@ipa1 tools]# yum -y install percona-xtrabackup-*.rpm- 命令介绍
[root@ipa1 ~]# innobackupex --help2.2 全备
- 备份
[root@ipa1 ~]# innobackupex --user=root --password=123456 /data/backup
.................................
200323 17:10:49 [00] ...done
xtrabackup: Transaction log of lsn (3559212) to (3559221) was copied.
200323 17:10:50 completed OK!注意：innobackupex 备份时，默认会读取/var/lib/mysql/mysql.sock文件，如果mysql的sock文件不在此位置，需要修改配置文件
添加以下内容：
[client]
socket=/tmp/mysql.sock
2）备份检查
文件夹会自动生成
[root@ipa1 ~]# ll /data/backup/2020-03-23_17-10-48/
total 12336
-rw-r----- 1 root root 487 Mar 23 17:10 backup-my.cnf
-rw-r----- 1 root root 425 Mar 23 17:10 ib_buffer_pool
-rw-r----- 1 root root 12582912 Mar 23 17:10 ibdata1
drwxr-x--- 2 root root 4096 Mar 23 17:10 mysql
drwxr-x--- 2 root root 8192 Mar 23 17:10 performance_schema
drwxr-x--- 2 root root 8192 Mar 23 17:10 sys
drwxr-x--- 2 root root 144 Mar 23 17:10 world
-rw-r----- 1 root root 113 Mar 23 17:10 xtrabackup_checkpoints
-rw-r----- 1 root root 423 Mar 23 17:10 xtrabackup_info
-rw-r----- 1 root root 2560 Mar 23 17:10 xtrabackup_logfile- 自定义时间戳文件备份
添加--no-timestamp参数 可以自定义备份路径
[root@ipa1 ~]# innobackupex --user=root --password=123456 --no-timestamp /data/backup/full_`date +%F`4）备份集文件说明
-rw-r----- 1 root root 21 Mar 23 17:27 xtrabackup_binlog_info
-rw-r----- 1 root root 113 Mar 23 17:27 xtrabackup_checkpoints
-rw-r----- 1 root root 501 Mar 23 17:27 xtrabackup_info
-rw-r----- 1 root root 2560 Mar 23 17:27 xtrabackup_logfile
xtrabackup_binlog_info ：（备份时刻的binlog位置）
[root@ipa1 full_2020-03-23]# cat xtrabackup_binlog_info
mysql-bin.000001 154
记录的是备份时刻，binlog的文件名字和当时的结束的position，可以用来作为截取binlog时的起点。
xtrabackup_checkpoints ：
backup_type = full-backuped
from_lsn = 0 上次所到达的LSN号(对于全备就是从0开始,对于增量有别的显示方法)
to_lsn = 160683027 备份开始时间(ckpt)点数据页的LSN
last_lsn = 160683036 备份结束后，redo日志最终的LSN
compact = 0
recover_binlog_info = 0
（1）备份时刻，立即将已经commit过的，内存中的数据页刷新到磁盘(CKPT).开始备份数据，数据文件的LSN会停留在to_lsn位置。
（2）备份时刻有可能会有其他的数据写入，已备走的数据文件就不会再发生变化了。
（3）在备份过程中，备份软件会一直监控着redo的undo，如果一旦有变化会将日志也一并备走，并记录LSN到last_lsn。
从to_lsn ----》last_lsn 就是，备份过程中产生的数据变化.2.3 全量恢复
将redo进行重做，已提交的写到数据文件，未提交的使用undo回滚掉。模拟了CSR的过程
apply-log参数是备份预整理
[root@ipa1 ~]# rm -rf /data/mysql/*
[root@ipa1 ~]# innobackupex --apply-log /data/backup/full_2020-03-23
[root@ipa1 ~]# cp -r /data/backup/full_2020-03-23/* /data/mysql/
[root@ipa1 ~]# chown -R mysql.mysql /data/mysql
[root@ipa1 ~]# systemctl restart mysqld2.4 增量备份
（1）增量备份的方式，是基于上一次备份进行增量，首次增量备份必须是基于全备。
（2）增量备份无法单独恢复。必须基于全备进行恢复。
（3）所有增量必须要按顺序合并到全备中。
# 首次全备
[root@ipa1 ~]# innobackupex --user=root --password=123456 --no-timestamp /data/backup/full
#第一次修改数据
mysql> create database tchua charset utf8mb4;
mysql> use tchua
mysql> create table t1(id int);
mysql> insert into t1 values(1),(2),(3);
mysql> select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
# 第一次增量备份
[root@ipa1 ~]# innobackupex --user=root --password=123456 --no-timestamp --incremental --incremental-basedir=/data/backup/full /data/backup/inc1
#第二次修改数据
mysql> use tchua
mysql> create table t2(id int);
mysql> insert into t2 values(1),(2),(3);
mysql> select * from t2;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
# 第二次增量备份
[root@ipa1 ~]# innobackupex --user=root --password=123456 --no-timestamp --incremental --incremental-basedir=/data/backup/inc1 /data/backup/inc2
# 删除数据
[root@ipa1 ~]# rm -rf /data/mysql/*
[root@ipa1 ~]# pkill mysqld
# 恢复
## 全备检查
[root@ipa1 ~]# innobackupex --user=root --password=123456 --apply-log --redo-only /data/backup/full
## 合并第一次增量
[root@ipa1 ~]# innobackupex --user=root --password=123456 --apply-log --redo-only --incremental-dir=/data/backup/inc1 /data/backup/full
## 合并第二次增量
[root@ipa1 ~]# innobackupex --user=root --password=123456 --apply-log --redo-only --incremental-dir=/data/backup/inc2 /data/backup/full
## 复制合并完的数据至mysql数据目录
[root@ipa1 ~]# cp -a /data/backup/full/* /data/mysql/
[root@ipa1 ~]# chown -R mysql.mysql /data/mysql
## 启动
[root@ipa1 ~]# systemctl status mysqld
## 验证数据
mysql> use tchua
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-----------------+
| Tables_in_tchua |
+-----------------+
| t1 |
| t2 |
+-----------------+二、恢复
2.1 binlog
1）binlog配置
注意：MySQL默认是没有开启二进制日志的。
基础参数查看:
开关:
mysql>select @@log_bin;
日志路径及名字
mysql>select @@log_bin_basename;
服务ID号:
mysql>select @@server_id;
二进制日志格式:
mysql>select @@binlog_format;
双一标准之二:
mysql>select @@sync_binlog;2.2.2 配置参数
server_id=6 # 服务器标识
log-bin=/data/mysqlbinlog/mysql-bin #日志路径及前缀
binlog_format=row # 日志记录格式
sync_binlog=1 # binlog刷到磁盘的策略，1 每次事务提交，MySQL都会把binlog刷到磁盘2.2.3 binlog恢复实战
# 模拟新增数据
mysql> use tchua
mysql> show tables;
+-----------------+
| Tables_in_tchua |
+-----------------+
| t1 |
| t2 |
+-----------------+
mysql> create table t3(id int);
mysql> insert into t3 values(1),(2),(3);
mysql> select * from t3;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
# 模拟删除操作
mysql> drop table t3;
mysql> show tables;
+-----------------+
| Tables_in_tchua |
+-----------------+
| t1 |
| t2 |
+-----------------+
# 恢复
1）获取position起始点终止点
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 | 765 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> show binlog events in 'mysql-bin.000005';
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------+
| mysql-bin.000005 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.29-log, Binlog ver: 4 |
| mysql-bin.000005 | 123 | Previous_gtids | 6 | 154 | |
| mysql-bin.000005 | 154 | Anonymous_Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000005 | 219 | Query | 6 | 318 | use `tchua`; create table t3(id int) |
| mysql-bin.000005 | 318 | Anonymous_Gtid | 6 | 383 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000005 | 383 | Query | 6 | 456 | BEGIN |
| mysql-bin.000005 | 456 | Table_map | 6 | 502 | table_id: 113 (tchua.t3) |
| mysql-bin.000005 | 502 | Write_rows | 6 | 552 | table_id: 113 flags: STMT_END_F |
| mysql-bin.000005 | 552 | Xid | 6 | 583 | COMMIT /* xid=32 */ |
| mysql-bin.000005 | 583 | Anonymous_Gtid | 6 | 648 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000005 | 648 | Query | 6 | 765 | use `tchua`; DROP TABLE `t3` /* generated by server */ |
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------+
[root@ipa1 ~]# mysqlbinlog --base64-output=decode-rows -vvv /data/mysqlbinlog/mysql-bin.000005
可以看出：起点为219，终点为648
[root@ipa1 ~]# mysqlbinlog --start-position=219 --stop-position=648 /data/mysqlbinlog/mysql-bin.000005 >> /tmp/21.sql
mysql> use tchua
mysql> set sql_log_bin=0; # 以下操作不写入日志
mysql> source /tmp/21.sql;
mysql> set sql_log_bin=1;
mysql> show tables;
+-----------------+
| Tables_in_tchua |
+-----------------+
| t1 |
| t2 |
| t3 |
+-----------------+
mysql> select * from t3;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+2.2.4 GTID恢复介绍
- 开启gtid
[root@ipa1 ~]# vim /etc/my.cnf
[mysqld]
gtid-mode=on
enforce-gtid-consistency=true
[root@ipa1 ~]# systemctl restart mysqld
mysql> create database tchua_db charset utf8mb4;
mysql> show master status;
+------------------+----------+--------------+------------------+----------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000007 | 341 | | | 1cc2116b-6ced-11ea-8eec-000c29c7c262:1 |
+------------------+----------+--------------+------------------+----------------------------------------+
Executed_Gtid_Set: 生成的gtid号，前半部分为固定的，后面只要有变化就会改变- 基于GTID进行查看binlog
mysql> use tchua_db
mysql> create table t1(id int);
mysql> insert into t1 values(1),(2);
mysql> insert into t1 values(4),(5);
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000007 | 1043 | | | 1cc2116b-6ced-11ea-8eec-000c29c7c262:1-4 |
+------------------+----------+--------------+------------------+------------------------------------------+
[root@ipa1 ~]# mysqlbinlog --include-gtids='1cc2116b-6ced-11ea-8eec-000c29c7c262:1-4' /data/mysqlbinlog/mysql-bin.000007- 模拟数据恢复
注意：开启GTID后,MySQL恢复Binlog时,重复GTID的事务不会再执行了
# 删除t1表
mysql> drop database tchua_db;
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000008 | 643 | | | 1cc2116b-6ced-11ea-8eec-000c29c7c262:1-6 |
+------------------+----------+--------------+------------------+------------------------------------------+
# 选择5是因为gitd等于6 是drop操作，所以需要
[root@ipa1 ~]# mysqlbinlog --skip-gtids --include-gtids='1cc2116b-6ced-11ea-8eec-000c29c7c262:1-5' /data/mysqlbinlog/mysql-bin.000007 /data/mysqlbinlog/mysql-bin.000008 > /tmp/22.sql
mysql> set sql_log_bin=0;
mysql> source /tmp/22.sql;
mysql> set sql_log_bin=1;2.2.5 物理表恢复
待恢复t1表文件：
-rw-r----- 1 mysql mysql 8556 Mar 23 17:58 t1.frm
-rw-r----- 1 mysql mysql 98304 Mar 23 17:58 t1.ibd
1） 在新的数据库创建t1表，表结构与t1表需要一样
mysql> create database t1_db charset utf8mb4;
mysql> use t1_db
mysql> create table t1(id int);
2）删除新的t1表ibd文件
mysql> alter table t1 discard tablespace;
[root@ipa1 ~]# cd /data/mysql/t1_db/
[root@ipa1 t1_db]# ll
-rw-r----- 1 mysql mysql 67 Mar 24 14:27 db.opt
-rw-r----- 1 mysql mysql 8556 Mar 24 14:27 t1.frm
3）复制t1表至新的库下
[root@ipa1 t1_db]# cp /data/mysql/tchua/t1.ibd /data/mysql/t1_db/
[root@ipa1 t1_db]# chown -R mysql.mysql /data/mysql/t1_db/
4）导入数据
mysql> alter table t1 import tablespace;
mysql> select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
