案例1：MySQL8.0實作資料庫冷備份和還原
10.0.0.10 -- MySQL8.0
#停止資料庫
[root@CentOS8 my.cnf.d]# systemctl stop mysqld.service
#備份資料
[root@CentOS8 ~]# scp -pr /var/lib/mysql 10.0.0.13:/mysql-bak
#備份組態檔
[root@CentOS8 ~]# scp -rp /etc/my.cnf /etc/my.cnf.d/
10.0.0.13 -- MySQL8.0
#安裝MySQL8.0
[root@CentOS8 config]# yum insatll mysql-server -y
#替換拷貝過來的資料
[root@CentOS8 mysql]# cp -a mysql /var/lib/mysql
#替換組態檔
[root@CentOS8 config]# cp my.cnf /etc/
[root@CentOS8 config]# cp -a my.cnf.d /etc/
#更改檔案權限
[root@centos8 ~]#chown -R mysql.mysql /var/lib/mysql/
#啟動資料庫測驗
[root@CentOS8 config]# systemctl start mysqld.service
注意：scp -p：保留檔案的訪問和修改時間，檔案的所有者和所有組會發生變化（建議使用rsync -a進行遠程拷貝）
案例2：使用mysqldump進行完全備份并模擬資料庫損壞：
1.使用musqldump的格式三備份資料庫所有資料
[root@CentOS8 ~]# mysqldump -A >hello.sql
#查看備份的資料庫
[root@CentOS8 ~]# grep -i '^create database' hello.sql;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `hellodb` /*!40100 DEFAULT CHARACTER SET utf8 */;
2.模擬資料庫損壞，清空資料庫的所有檔案，會自動初始化MySQL為重裝的狀態
[root@CentOS8 ~]# rm -rf /var/lib/mysql/* #yum安裝默認的資料存放路徑為/var/lib/mysql/
3.啟動資料庫
[root@CentOS8 ~]# systemctl start mysqld.service
4.匯入資料
[root@CentOS8 ~]# mysql < hello.sql
5.測驗是否恢復正常
mysql> show databases ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
案例3: 利用二進制日志，還原資料庫最新狀態
利用--master-data選項在備份的文本檔案中從二進制日志中標記當前位置，
前提：需要開啟二進制日志
步驟：
1.使用mysqldump進行完全備份
[root@CentOS8 ~]# mysqldump -A --master-data=https://www.cnblogs.com/heyongshen/p/2 > hellodb.sql
2.更改資料庫中的資料資訊
mysql> insert into teachers values(null,'xiao ming',20,'M');
Query OK, 1 row affected (0.00 sec)
mysql> insert into teachers values(null,'xiao hong',18,'F');
Query OK, 1 row affected (0.00 sec)
mysql> insert into teachers values(null,'xiao he',19,'M');
Query OK, 1 row affected (0.00 sec)
3.損壞資料庫
[root@CentOS8 ~]# rm -rf /var/lib/mysql/*
4.執行還原操作
[root@CentOS8 ~]# ll /data/logbin/*
-rw-r----- 1 mysql mysql 1024 Sep 19 18:26 /data/logbin/mysql-bin.000001
-rw-r----- 1 mysql mysql 154 Sep 19 18:26 /data/logbin/mysql-bin.000002
-rw-r----- 1 mysql mysql 60 Sep 19 18:26 /data/logbin/mysql-bin.index
[root@CentOS8 ~]# mysqlbinlog --start-position=154 /data/logbin/mysql-bin.000001 > binlog.sql
[root@CentOS8 ~]# mysqlbinlog /data/logbin/mysql-bin.000002 >> binlog.sql
5.關閉二進制日志 -- 資料恢復期間不記錄二進制日志
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@sql_log_bin;
+---------------+
| @@sql_log_bin |
+---------------+
| 0 |
+---------------+
1 row in set (0.00 sec)
6.恢復資料庫
root@CentOS8 ~]# mysql < hellodb.sql
[root@CentOS8 ~]# mysql < binlog.sql
7.檢查
案例4：恢復誤洗掉的表
案例說明：每天凌晨3點定時做完全備份，早上10：00誤洗掉了表students，10：10才發現故障，現需要將數
據庫還原到10：10的狀態，且恢復被洗掉的students表
完全備份（3）點--------->洗掉了表students(10：00)----->發現故障（10：10）
思路：
1.將資料庫恢復到3點時候的狀態
2.通過二進制日志將三點到十一點的資料進行還原（需要洗掉二進制日志中洗掉students這條陳述句）
實作：
前提：mysql需要開啟二進制日志
1.凌晨三點進行完全備份：
[root@CentOS8 ~]# mysqldump -uroot -p -A -F --single-transaction --master-data=https://www.cnblogs.com/heyongshen/p/2 > /data/back/all_back_`date +"%F:%T"`.sql
2.模擬三點到十點之間有資料更新
mysql> insert students (name,age,gender) values('rose',20,'f');
Query OK, 1 row affected (0.01 sec)
mysql> insert students (name,age,gender) values('jack',22,'M');
Query OK, 1 row affected (0.00 sec)
3.模擬誤洗掉了students
mysql> drop table students;
Query OK, 0 rows affected (0.01 sec)
4.其他表仍然更新資料
mysql> insert teachers (name,age,gender)values('xiao wang ',30,'M');
Query OK, 1 row affected (0.00 sec)
mysql> insert teachers (name,age,gender)values('xiao li',28,'M');
Query OK, 1 row affected (0.00 sec)
5.發現表洗掉，進行還原（此時需要停止資料庫的訪問）
（1）#從完全備份中，找到二進制位置
[root@CentOS8 back]# cat all_back_2022-09-20\:00\:32\:05.sql | grep -i "^-- change master"
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=156;
（2）查看對應的二進制檔案并生成sql陳述句
[root@CentOS8 back]# mysqlbinlog /data/logbin/mysql-bin.000002 > binlog.sql
#mysqlbinlog默認將資訊輸出到螢屏
（3）洗掉binlog.sql中關于洗掉表的陳述句
[root@CentOS8 back]# grep -i '^DROP TABLE' binlog.sql
DROP TABLE `students` /* generated by server */
[root@CentOS8 back]# sed -i.bak '/^DROP TABLE/d' binlog.sql
[root@CentOS8 back]# grep -i '^DROP TABLE' binlog.sql
（4）執行資料恢復腳本（需要進入到mysql里面使用source命令執行）
[root@centos8 ~]#mysql -uroot -p
#不將資料恢復的資訊寫入到二進制日志里面
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
mysql> source /data/back/all_back_2022-09-20:00:32:05.sql；
#執行這個腳本的時候有一個報錯資訊，不用管他，資料依然能正常恢復
mysql> source /data/back/binlog.sql;
mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/509159.html
標籤：MySQL