一.登录数据库 创建一个数据库,并插入一些数据
[root@db1 ~]# mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 5.7.29-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> create database backup charset utf8mb4;
Query OK, 1 row affected (0.02 sec)
mysql> use backup
Database changed
mysql> create table t1(id int);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t1 values(1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+
5 rows in set (0.00 sec)
二.开始备份
[root@db1 ~]# mysqldump -uroot -predhat -A -R -E --triggers --set-gtid-purged=AUTO --master-data=2 --single-transaction |gzip > /backup/full_$(date +%F).sql.gz
-A 全备份
-R 存储过程和函数
--triggers触发器
--master-data=2 #以注释的形式记录二进制信息 (2)自动开启锁表功能 一般配合--single-transaction使用来做到热备份
--single-transaction 针对innodb引擎进行快照备份
--set-gtid-purged=OFF --让恢复文件不再记录gtid信息 仅是做普通的本机备份恢复时,可以添加
--set-gtid-purged=ON--让恢复文件记录gtid信息 默认是auto 构建主从时使用 但默认是auto 所以可以不用添加
三.模拟生产环境还未来得及备份的数据变化
mysql> use backup
Database changed
mysql> create table t2(id int);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t2 values(1),(2),(3),(4);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from t2;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.00 sec)
四.模拟生产环境不小心数据丢失
mysql> drop database backup;
Query OK, 2 rows affected (0.03 sec)
五.截取日志.恢复没有备份的数据
[root@db1 backup]# gunzip full_2020-02-26.sql.gz
[root@db1 ~]# cat /backup/full_2020-02-26.sql | head -30
-- MySQL dump 10.13 Distrib 5.7.29, for linux-glibc2.12 (x86_64)
--
-- Host: localhost Database:
-- ------------------------------------------------------
-- Server version 5.7.29-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Position to start replication or point-in-time recovery from
--
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=8983;
--
-- Current Database: `backup`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `backup` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `backup`;
找到 -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=8983; 这行数据 这行最后备份时的日志号
登录数据库查看日志号
mysql> show master status; ##查看当前使用的记录日志文件
mysql> show binlog events in 'mysql-bin.000002'; ##找到8983的post号所对应的gtid事件号还有删库之前的gtid号
| mysql-bin.000002 | 8983 | Gtid | 6 | 9048 | SET @@SESSION.GTID_NEXT= 'eee7e0b6-5466-11ea-9f97-005056bf8ab0:41'
| mysql-bin.000002 | 9149 | Gtid | 6 | 9214 | SET @@SESSION.GTID_NEXT= 'eee7e0b6-5466-11ea-9f97-005056bf8ab0:42'
开始截取日志
[root@db1 ~]# mysqlbinlog --skip-gtids --include-gtids='eee7e0b6-5466-11ea-9f97-005056bf8ab0:41-42' /data/binlog/mysql-bin.000002 > /backup/binlog.sql
五.切换到测试环境把备份的数据导入
[root@db1 ~]# scp /backup/full_2020-02-26.sql czq@172.16.210.250:~
[root@db1 ~]# scp /backup/binlog.sql czq@172.16.210.250:~
czq@sunlog:~$ sudo mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.7.29-0ubuntu0.18.04.1 (Ubuntu)
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> set sql_log_bin=0
mysql> source /home/czq/full_2020-02-26.sql
mysql> source /home/czq/binlog.sql
mysql> set sql_log_bin=1
六.将被删除的库导出来并恢复到生产环境
czq@sunlog:~$ sudo mysqldump -uroot -predhat -B backup > backup.sql 导出一个库
### czq@sunlog:~$ sudo mysqldump -uroot -predhat backup t1 > t1.sql 如果导出指定表可以使用这条
czq@sunlog:~$ sudo scp backup.sql 172.16.210.36:~
回到生产环境
mysql> source /root/backup.sql
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| backup |
| mysql |
| performance_schema |
| sys |
+--------------------+
12 rows in set (0.01 sec)
mysql> use backup;
Database changed
mysql> show tables;
+------------------+
| Tables_in_backup |
+------------------+
| t1 |
| t2 |
+------------------+
2 rows in set (0.00 sec)
mysql> select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+
5 rows in set (0.00 sec)
mysql> select * from t2;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.00 sec)
网友评论