MySQL备份恢复
登录
标签搜索
侧边栏壁纸
博主昵称
小黑

  • 累计撰写 156 篇文章
  • 累计收到 891 条评论
  •   博主 4月6日 在线
Linux Mysql

MySQL备份恢复

罗小黑
罗小黑
2021-08-25 / 1 评论 / 436 阅读 / 正在检测是否收录...
广告
温馨提示：
本文最后更新于2021年08月30日，已超过963天没有更新，若内容或图片失效，请留言反馈。

mysqldump命令

连接参数：

mysqldump -u -p -S -h -P

备份参数：

-A：全备份

[root@cs ~]# mysqldump -uroot -p123 -A >/data/backup/full.sql

-B：单库或者多库备份

[root@cs ~]# mysqldump -uroot -p123 -B test cs >/data/backup/db.sql

单表或者多表备份

[root@cs ~]# mysqldump -uroot -p123 -B world city country >/data/backup/biao.sql

高级功能参数

参数一： --master-data=2

  1. 自动记录binlog位置点 b. 自动加GRL锁(FTWRL ,flush tables with read lock) c. 配合--single-transaction ,减少锁的时间。

参数二： single-transaction

  1. 对于InnoDB表，利用MVCC中一致性快照进行备份。备份数据时,不加锁 b. 备份期间如果出现DDL操作，导致备份数据不一致 问题： mysqldump是严格意义上的热备吗？ 8.0 之后 master-data和single-transaction,对于InnoDB数据备份时是快照备份的. 备份表结构等数据时,还是FTWRL过程备份. --single-transaction 只是针对InnoDB表数据进行一致性快照备份。 问题： mysqldump备份需要锁表吗？ 是有的。global read lock

参数三： -R -E --triggers 备份特殊对象
存储过程 函数 触发器 事件

参数四： --max_allowed_packet=64M
最大允许数据包的大小。

标准化备份

[root@cs backup]# mysqldump -uroot -p -A --master-data=2 --single-transaction -R -E --triggers --max_allowed_packet=64M >/data/backup/full_`date +%F`.sql
Enter password:

实验：通过mysqldump全备+binlog实现PIT数据恢复


环境搭建

mysql> create database mdb;
Query OK, 1 row affected (0.00 sec)

mysql> use mdb;
Database changed
mysql> create table t1 (id int);
Query OK, 0 rows affected (0.03 sec)

mysql> create table t2 (id int);
Query OK, 0 rows affected (0.03 sec)

mysql> create table t3 (id int);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t1 values(1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into t2 values(1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into t3 values(1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

[root@cs ~]# mysqldump -uroot -p -A --master-data=2 --single-transaction -R -E --triggers --max_allowed_packet=64M >/tmp/full_`date +%F`.sql
Enter password:

mysql> create table t4 (id int);
Query OK, 0 rows affected (0.09 sec)

mysql> insert into t4 values (1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> drop database mdb;
Query OK, 4 rows affected (0.06 sec)

恢复过程

查看备份获取二进制位置点

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
| mysql-bin.000002 |       201 |
| mysql-bin.000003 |       201 |
| mysql-bin.000004 |      3513 |
+------------------+-----------+
4 rows in set (0.00 sec)

mysql> show binlog events in 'mysql-bin.000004';
+------------------+------+----------------+-----------+-------------+----------------------------------
| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                             
+------------------+------+----------------+-----------+-------------+----------------------------------
|........................................................................................................
| mysql-bin.000004 | 1580 | Query          |         1 |        1671 | create database mdb                   |
| mysql-bin.000004 | 1671 | Anonymous_Gtid |         1 |        1736 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000004 | 1736 | Query          |         1 |        1832 | use `mdb`; create table t1 (id int)   |
| mysql-bin.000004 | 1832 | Anonymous_Gtid |         1 |        1897 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000004 | 1897 | Query          |         1 |        1993 | use `mdb`; create table t2 (id int)   |
| mysql-bin.000004 | 1993 | Anonymous_Gtid |         1 |        2058 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000004 | 2058 | Query          |         1 |        2154 | use `mdb`; create table t3 (id int)   |
| mysql-bin.000004 | 2154 | Anonymous_Gtid |         1 |        2219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000004 | 2219 | Query          |         1 |        2290 | BEGIN                                 |
| mysql-bin.000004 | 2290 | Table_map      |         1 |        2334 | table_id: 131 (mdb.t1)                |
| mysql-bin.000004 | 2334 | Write_rows     |         1 |        2384 | table_id: 131 flags: STMT_END_F       |
| mysql-bin.000004 | 2384 | Xid            |         1 |        2415 | COMMIT /* xid=1405 */                 |
| mysql-bin.000004 | 2415 | Anonymous_Gtid |         1 |        2480 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000004 | 2480 | Query          |         1 |        2551 | BEGIN                                 |
| mysql-bin.000004 | 2551 | Table_map      |         1 |        2595 | table_id: 132 (mdb.t2)                |
| mysql-bin.000004 | 2595 | Write_rows     |         1 |        2645 | table_id: 132 flags: STMT_END_F       |
| mysql-bin.000004 | 2645 | Xid            |         1 |        2676 | COMMIT /* xid=1406 */                 |
| mysql-bin.000004 | 2676 | Anonymous_Gtid |         1 |        2741 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000004 | 2741 | Query          |         1 |        2812 | BEGIN                                 |
| mysql-bin.000004 | 2812 | Table_map      |         1 |        2856 | table_id: 133 (mdb.t3)                |
| mysql-bin.000004 | 2856 | Write_rows     |         1 |        2906 | table_id: 133 flags: STMT_END_F       |
| mysql-bin.000004 | 2906 | Xid            |         1 |        2937 | COMMIT /* xid=1407 */                 |
| mysql-bin.000004 | 2937 | Anonymous_Gtid |         1 |        3002 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000004 | 3002 | Query          |         1 |        3098 | use `mdb`; create table t4 (id int)   |
| mysql-bin.000004 | 3098 | Anonymous_Gtid |         1 |        3163 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000004 | 3163 | Query          |         1 |        3234 | BEGIN                                 |
| mysql-bin.000004 | 3234 | Table_map      |         1 |        3278 | table_id: 366 (mdb.t4)                |
| mysql-bin.000004 | 3278 | Write_rows     |         1 |        3328 | table_id: 366 flags: STMT_END_F       |
| mysql-bin.000004 | 3328 | Xid            |         1 |        3359 | COMMIT /* xid=4749 */                 |
| mysql-bin.000004 | 3359 | Anonymous_Gtid |         1 |        3424 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000004 | 3424 | Query          |         1 |        3513 | drop database mdb                     |
+------------------+------+----------------+-----------+-------------+---------------------------------------+
恢复备份
mysql> source /tmp/full_2021-08-25.sql;
二进制恢复
[root@cs tmp]# mysqlbinlog --skip-gtids --start-position=1580 --stop-position=3424 /var/lib/mysql/mysql-bin.000004 >/tmp/bin1.sql
mysql> set sql_log_bin=0;
mysql> source /tmp/bin.sql
mysql> set sql_log_bin=1;

物理备份工具使用-Percona Xtrabackup（PXB）


安装yum 源

[root@cs ~]# yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm -y

安装XtraBackup 2.4版本的

[root@cs ~]# yum install -y percona-xtrabackup-24.x86_64

1.全量备份

[root@cs backup]# innobackupex --user=root --password=123 /tmp/backup &>/tmp/xbk.log

自主定制备份路径名

[root@cs backup]# innobackupex --user=root --password=123 --no-timestamp /data/backup/full &>/tmp/xbk.log

全备的恢复
准备备份（Prepared）
将redo进行重做，已提交的写到数据文件，未提交的使用undo回滚掉。模拟了CSR的过程

[root@cs lib]# innobackupex --apply-log /tmp/backup/full/

恢复备份
前提：

1、被恢复的目录是空
2、被恢复的数据库的实例是关闭
systemctl stop mysqld

创建新目录

[root@cs backup]# mkdir /var/lib/mysql

数据拷贝

[root@cs lib]# innobackupex --copy-back /tmp/backup/full/

启动数据库

systemctl start mysqld

innobackupex 增量备份(incremental)

（1）增量备份的方式，是基于上一次备份进行增量。
（2）增量备份无法单独恢复。必须基于全备进行恢复。
（3）所有增量必须要按顺序合并到全备中。

前期准备

mysql> create database test charset utf8;
Query OK, 1 row affected (0.01 sec)
mysql> use test;
Database changed
mysql> create table t1 (id int);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t1 values(1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

增量备份命令

# 全备 （周日）
[root@cs tmp]# innobackupex --user=root --password=123 --no-timestamp /tmp/backup/full &> /tmp/xbk.log
# 模拟数据变化 （周一）
mysql> create database cs;
Query OK, 1 row affected (0.00 sec)
mysql> use cs;
Database changed
mysql> create table t1 (id int);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t1 values (1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
#周一备份
[root@cs tmp]# innobackupex  --user=root --password=123 --no-timestamp --incremental /tmp/backup/inc1 --incremental-basedir=/tmp/backup/full &>/tmp/inc1.log
#模拟周二数据（cs）
mysql> create table t2 (id int);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t2 values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

#周二备份
[root@cs backup]# innobackupex --user=root --password=123 --no-timestamp --incremental /tmp/backup/inc2 --incremental-basedir=/tmp/backup/inc1 &>/tmp/inc2.log
# 模拟周三数据变化
 mysql> create table t3 (id int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t3 values(1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> drop database cs;
Query OK, 3 rows affected (0.04 sec)

恢复到周三误drop之前的数据状态

恢复思路：
1.  挂出维护页，停止当天的自动备份脚本
2.  检查备份：周日full+周一inc1+周二inc2，周三的完整二进制日志
3. 进行备份整理（细节），截取关键的二进制日志（从备份——误删除之前）
4. 测试库进行备份恢复及日志恢复
5. 应用进行测试无误，开启业务
6. 此次工作的总结

开始恢复

(1) 全备的整理
[root@cs backup]# innobackupex --apply-log --redo-only /tmp/backup/full/
(2) 合并inc1到full中
[root@cs backup]# innobackupex --apply-log --redo-only --incremental-dir=/tmp/backup/inc1  /tmp/backup/full/
(3) 合并inc2到full中
[root@cs backup]# innobackupex --apply-log --incremental-dir=/tmp/backup/inc2  /tmp/backup/full/
(4) 最后一次整理全备
[root@cs backup]# innobackupex --apply-log  /tmp/backup/full
(5) 二进制截取
判断起点
[root@cs backup]# cat inc2/xtrabackup_binlog_info 
mysql-bin.000001    2463
判断终点
mysql> show binlog  events in 'mysql-bin.000001';
+------------------+------+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+------+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 |    4 | Format_desc    |         1 |         123 | Server ver: 5.7.34-log, Binlog ver: 4 |
| mysql-bin.000001 |  123 | Previous_gtids |         1 |         154 |                                       |
| mysql-bin.000001 |  154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000001 |  219 | Query          |         1 |         326 | create database test charset utf8     |
| mysql-bin.000001 |  326 | Anonymous_Gtid |         1 |         391 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000001 |  391 | Query          |         1 |         489 | use `test`; create table t1 (id int)  |
| mysql-bin.000001 |  489 | Anonymous_Gtid |         1 |         554 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000001 |  554 | Query          |         1 |         626 | BEGIN                                 |
| mysql-bin.000001 |  626 | Table_map      |         1 |         671 | table_id: 122 (test.t1)               |
| mysql-bin.000001 |  671 | Write_rows     |         1 |         721 | table_id: 122 flags: STMT_END_F       |
| mysql-bin.000001 |  721 | Xid            |         1 |         752 | COMMIT /* xid=68 */                   |
| mysql-bin.000001 |  752 | Anonymous_Gtid |         1 |         817 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000001 |  817 | Query          |         1 |         905 | create database cs                    |
| mysql-bin.000001 |  905 | Anonymous_Gtid |         1 |         970 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000001 |  970 | Query          |         1 |        1064 | use `cs`; create table t1 (id int)    |
| mysql-bin.000001 | 1064 | Anonymous_Gtid |         1 |        1129 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000001 | 1129 | Query          |         1 |        1199 | BEGIN                                 |
| mysql-bin.000001 | 1199 | Table_map      |         1 |        1242 | table_id: 123 (cs.t1)                 |
| mysql-bin.000001 | 1242 | Write_rows     |         1 |        1292 | table_id: 123 flags: STMT_END_F       |
| mysql-bin.000001 | 1292 | Xid            |         1 |        1323 | COMMIT /* xid=81 */                   |
| mysql-bin.000001 | 1323 | Anonymous_Gtid |         1 |        1388 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000001 | 1388 | Query          |         1 |        1474 | drop database cs                      |
| mysql-bin.000001 | 1474 | Anonymous_Gtid |         1 |        1539 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000001 | 1539 | Query          |         1 |        1627 | create database cs                    |
| mysql-bin.000001 | 1627 | Anonymous_Gtid |         1 |        1692 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000001 | 1692 | Query          |         1 |        1786 | use `cs`; create table t1 (id int)    |
| mysql-bin.000001 | 1786 | Anonymous_Gtid |         1 |        1851 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000001 | 1851 | Query          |         1 |        1921 | BEGIN                                 |
| mysql-bin.000001 | 1921 | Table_map      |         1 |        1964 | table_id: 423 (cs.t1)                 |
| mysql-bin.000001 | 1964 | Write_rows     |         1 |        2014 | table_id: 423 flags: STMT_END_F       |
| mysql-bin.000001 | 2014 | Xid            |         1 |        2045 | COMMIT /* xid=113 */                  |
| mysql-bin.000001 | 2045 | Anonymous_Gtid |         1 |        2110 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000001 | 2110 | Query          |         1 |        2204 | use `cs`; create table t2 (id int)    |
| mysql-bin.000001 | 2204 | Anonymous_Gtid |         1 |        2269 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000001 | 2269 | Query          |         1 |        2339 | BEGIN                                 |
| mysql-bin.000001 | 2339 | Table_map      |         1 |        2382 | table_id: 728 (cs.t2)                 |
| mysql-bin.000001 | 2382 | Write_rows     |         1 |        2432 | table_id: 728 flags: STMT_END_F       |
| mysql-bin.000001 | 2432 | Xid            |         1 |        2463 | COMMIT /* xid=136 */                  |
| mysql-bin.000001 | 2463 | Anonymous_Gtid |         1 |        2528 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000001 | 2528 | Query          |         1 |        2622 | use `cs`; create table t3 (id int)    |
| mysql-bin.000001 | 2622 | Anonymous_Gtid |         1 |        2687 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000001 | 2687 | Query          |         1 |        2757 | BEGIN                                 |
| mysql-bin.000001 | 2757 | Table_map      |         1 |        2800 | table_id: 1034 (cs.t3)                |
| mysql-bin.000001 | 2800 | Write_rows     |         1 |        2850 | table_id: 1034 flags: STMT_END_F      |
| mysql-bin.000001 | 2850 | Xid            |         1 |        2881 | COMMIT /* xid=159 */                  |
| mysql-bin.000001 | 2881 | Anonymous_Gtid |         1 |        2946 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000001 | 2946 | Query          |         1 |        3032 | drop database cs          |
+-----------------+------+----------------+-----------+-------------+---------------------------------------+
恢复3. 截取周二 23:00 到drop 之前的 binlog 
[root@cs backup]# mysqlbinlog --start-position=2463 --stop-position=2946 /var/lib/mysql/mysql-bin.000001 >/tmp/backup/bin.sql
4. 进行恢复
[root@cs lib]# innobackupex --copy-back /tmp/backup/full/
[root@cs lib]# chown -R mysql.mysql mysql
[root@cs lib]# systemctl start mysqld
二进制恢复
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

mysql> source /tmp/backup/bin.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Database changed
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Charset changed
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+--------------+
| Tables_in_cs |
+--------------+
| t1           |
| t2           |
| t3           |
+--------------+
3 rows in set (0.00 sec)

mysql> set sql_log_bin=1;
1

打赏

LinuxMysql备份

注意：本站资源多为网络收集，如涉及版权问题请及时与站长联系，我们会在第一时间内删除资源。
您购买的只是资源，不提供解答疑问和安装服务。
本站用户发帖仅代表本站用户个人观点，并不代表本站赞同其观点和对其真实性负责。
本站资源大多存储在云盘，如发现链接失效，请及时与站长联系，我们会第一时间更新。
转载本网站任何内容，请按则和转载方式正确书写本站原文地址
版权属于： 罗小黑
本文链接： https://www.aiyo99.com/archives/598.html
作品采用： 本文版权内容属于《 罗小黑博客 》转载请标明出处
相关推荐
K8S部署grafana
K8S部署grafana
 K8S部署nexus
K8S部署nexus
 部署 KubeSphere
部署 KubeSphere
 SSH连接服务器失败
SSH连接服务器失败

评论 (1)

取消
  1. 头像
    罗小黑 作者
    Windows 10 · Google Chrome
    沙发

    mysqld --initialize-insecure --user=mysql --datadir=/var/lib/mysql --basedir=/usr/bin mysql初始化

    回复 删除 垃圾

罗小黑

罗小黑【白茶清欢无别事，我在等风也等你】

风风雨雨 00 天 00 时 00 分，你是第97,817位相遇的小伙伴

Ctrl+D收藏本站

绿水本无忧，因风皱面

青山原不老，为雪白头