Top

NSD DBA DAY06

1. 案例1：完全備份與恢復
2. 案例2：增量備份與恢復
3. 案例3：差異備份與恢復
4. 案例4：binlog日志

1 案例1：完全備份與恢復

1.1 問題

1. 練習物理備份與恢復
2. 練習mysqldump備份與恢復

1.2 方案

在數據庫服務器192.168.88.50 練習數據的備份與恢復

1.3 步驟

實現此案例需要按照如下步驟進行。

步驟一：練習物理備份與恢復

冷備份，需停止數據庫服務 適合線下服務器。

備份數據

1. [root@mysql50 ~]# systemctl stop mysqld
2. [root@mysql50 ~]# mkdir /bakdir 創建備份目錄
3. [root@mysql50 ~]# cp -r /var/lib/mysql /bakdir/mysql.bak 拷貝數據源文件
4. [root@mysql50 ~]# cd /var/lib/mysql
5. [root@mysql50 mysql]# tar -zcvf /bakdir/mysql.tar.gz ./* 打包壓縮數據源文件
6. [root@mysql50 mysql]# ls /bakdir/ 查看備份文件
7. mysql.bak mysql.tar.gz

刪除數據

1. [root@mysql50 ~]# rm -rf /var/lib/mysql/*

恢復數據

1. [root@mysql50 ~]# tar -xf /bakdir/mysql.tar.gz -C /var/lib/mysql/
2. [root@mysql50 ~]# systemctl start mysqld
3. [root@mysql50 ~]# mysql -uroot -pNSD2023...a
4. mysql> show databases;
5. +--------------------+
6. | Database |
7. +--------------------+
8. | GAMEDB |
9. | db1 |
10. | home |
11. | information_schema |
12. | mysql |
13. | performance_schema |
14. | studb |
15. | sys |
16. | tarena |
17. | 學生庫 |
18. +--------------------+
19. 10 rows in set (0.00 sec)

也可使用cp拷貝的備份文件恢復數據

1. [root@mysql50 ~]# systemctl stop mysqld
2. [root@mysql50 ~]# rm -rf /var/lib/mysql/*
3. [root@mysql50 ~]# cp -r /bakdir/mysql.bak/* /var/lib/mysql/
4. [root@mysql50 ~]# chown -R mysql:mysql /var/lib/mysql
5. [root@mysql50 ~]# systemctl start mysqld
6. [root@mysql50 ~]# mysql -uroot -pNSD2023...a

步驟二：練習mysqldump備份與恢復

熱備份，備份和恢復數據庫服務必須是運行的

1. //備份1張表
2. [root@mysql50 ~]# mysqldump -uroot -pNSD2023...a tarena salary > /bakdir/tarena_salary.sql
3. mysqldump: [Warning] Using a password on the command line interface can be insecure.
4. //備份多張表
5. [root@mysql50 ~]# mysqldump -uroot -pNSD2023...a tarena employees departments > /bakdir/tarena_employees_deparments.sql
6. mysqldump: [Warning] Using a password on the command line interface can be insecure.
7. //備份1個庫
8. [root@mysql50 ~]# mysqldump -uroot -pNSD2023...a -B tarena > /bakdir/tarena.sql
9. mysqldump: [Warning] Using a password on the command line interface can be insecure.
10. //備份多個庫
11. [root@mysql50 ~]# mysqldump -uroot -pNSD2023...a -B studb db1 > /bakdir/studb_db1.sql
12. mysqldump: [Warning] Using a password on the command line interface can be insecure.
13. //備份所有庫
14. [root@mysql50 ~]# mysqldump -uroot -pNSD2023...a -A > /bakdir/allbak.sql
15. mysqldump: [Warning] Using a password on the command line interface can be insecure.
16. [root@mysql50 ~]#

恢復數據（覆蓋恢復數據）

1. [root@mysql50 ~]# mysql -uroot -pNSD2023...a
2. mysql> drop database tarena; //刪除庫
3. Query OK, 6 rows affected (0.57 sec)
4. mysql> exit
5. Bye
6. [root@mysql50 ~]# mysql -uroot -pNSD2023...a < /bakdir/tarena.sql //恢復數據
7. mysql: [Warning] Using a password on the command line interface can be insecure.
8. [root@mysql50 ~]# mysql -uroot -pNSD2023...a //登陸
9. mysql> use tarena; //進庫
10. mysql> show tables; //看表
11. +------------------+
12. | Tables_in_tarena |
13. +------------------+
14. | departments |
15. | employees |
16. | salary |
17. | stu4 |
18. | user |
19. | wage_grade |
20. +------------------+
21. 6 rows in set (0.00 sec)
22. mysql> delete from salary; //刪除表記錄
23. Query OK, 8055 rows affected (0.11 sec)
24. mysql> exit
25. Bye
26. [root@mysql50 ~]#
27. //使用備份文件恢復數據
28. [root@mysql50 ~]# mysql -uroot -pNSD2023...a tarena < /bakdir/tarena_salary.sql
29. mysql: [Warning] Using a password on the command line interface can be insecure.
30. [root@mysql50 ~]# mysql -uroot -pNSD2023...a //登陸服務
31. mysql> select count(*) from tarena.salary; //查看行數
32. +----------+
33. | count(*) |
34. +----------+
35. | 8055 |
36. +----------+
37. 1 row in set (0.00 sec)

分析：

Mysqldump 備份和恢復數據時會鎖表，鎖表期間無法對表做寫訪問，mysqldump適合備份數據量比較小的數據或在數據庫服務器訪問量少的時候備份。

2 案例2：增量備份與恢復

2.1 問題

1. 練習數據增量備份
2. 練習數據增量恢復

2.2 方案

2.3 準備2臺數據庫服務器，如表-1所示

?

增量備份：備份上次備份后，新產生的數據。

PERCONA Xtrabackup是一款強大的在線熱備份工具，備份過程中不鎖庫表，適合生產環境。支持完全備份與恢復、增量備份與恢復、差異備份與恢復。

在192.168.88.50主機完成備份與恢復的練習 。

2.4 步驟

實現此案例需要按照如下步驟進行。

步驟一：練習數據增量備份

安裝軟件（在mysql50 、MySQL51 兩臺主機都要安裝）

1. //把軟件拷貝到虛擬機里
2. [openeuler@server1 ~]$ scp /linux-soft/s3/percona-xtrabackup-8.0.26-18-Linux-x86_64.glibc2.12-minimal.tar.gz root@192.168.88.50:/root/
3. //安裝依賴
4. [root@host50 ~]# yum -y install perl-DBD-MySQL
5. //解壓源碼
6. [root@host50 ~ ]# tar -xf percona-xtrabackup-8.0.26-18-Linux-x86_64.glibc2.12-minimal.tar.gz
7. //移動并改名
8. [root@host50 ~ ]# mv percona-xtrabackup-8.0.26-18-Linux-x86_64.glibc2.12-minimal /usr/local/percona
9. //把命令添加到系統環境變量
10. [root@host50 ~ ]# vim /etc/bashrc
11. export PATH=/usr/local/percona/bin:$PATH 添加在文件末尾
12. :wq
14. [root@host50 ~ ]# source /etc/bashrc
15. //查看幫助信息
16. [root@host50 ~ ]# man xtrabackup (按q 退出)

增量備份（在mysql50主機 完成增量備份練習）

對數據做增量備份前，必須先有一次備份，也就是首次備份，通常是備份所有數據；比如每周周一完全備份，周二到周日增量備份。

周一完全備份（備份所有數據）

1. [root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=NSD2023...a --backup --target-dir=/fullbak --datadir=/var/lib/mysql
2. ……
3. ……
4. 230530 18:18:48 [00] ...done
5. xtrabackup: Transaction log of lsn (24822878) to (24822898) was copied.
6. 230530 18:18:50 completed OK!
7. [root@mysql50 ~]#
8. //插入新數據 （可以插入多行）
9. mysql> insert into tarena.salary(date,employee_id,basic,bonus)values("20230610",18,25000,8000);

周二增量備份（備份周一備份后新產生的數據）

1. [root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=NSD2023...a --backup --target-dir=/new2 --incremental-basedir=/fullbak --datadir=/var/lib/mysql
2. ……
3. ……
4. 230530 18:33:52 [00] ...done
5. xtrabackup: Transaction log of lsn (24827173) to (24827183) was copied.
6. 230530 18:33:53 completed OK!
7. [root@mysql50 ~]#
8. //插入新數據 （可以插入多行）
9. mysql> insert into tarena.salary(date,employee_id,basic,bonus)values("20230710",18,25000,8000);

周三增量備份（備份周二備份后新產生的數據）

1. [root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=NSD2023...a --backup --target-dir=/new3 --incremental-basedir=/new2 --datadir=/var/lib/mysql
2. ……
3. ……
4. 230530 18:46:17 [00] ...done
5. xtrabackup: Transaction log of lsn (24832526) to (24832536) was copied.
6. 230530 18:46:18 completed OK!
7. [root@mysql50 ~]#
8. //插入新數據 （可以插入多行）
9. mysql> insert into tarena.salary(date,employee_id,basic,bonus)values("20230710",18,25000,8000);

周四增量備份（備份周三備份后新產生的數據）

1. [root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=NSD2023...a --backup --target-dir=/new4 --incremental-basedir=/new3 --datadir=/var/lib/mysql
2. ……
3. ……
4. 230530 18:53:41 [00] ...done
5. xtrabackup: Transaction log of lsn (24837561) to (24837571) was copied.
6. 230530 18:53:42 completed OK!
7. [root@mysql50 ~]#
8. //插入新數據 （可以插入多行）
9. mysql> insert into tarena.salary(date,employee_id,basic,bonus)values("20230710",18,25000,8000);

周五增量備份（備份周四備份后新產生的數據）

1. [root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=NSD2023...a --backup --target-dir=/new5 --incremental-basedir=/new4 --datadir=/var/lib/mysql
2. ……
3. ……
4. 230530 18:58:50 [00] ...done
5. xtrabackup: Transaction log of lsn (24841645) to (24841655) was copied.
6. 230530 18:58:51 completed OK!
7. [root@mysql50 ~]#
8. //插入新數據 （可以插入多行）
9. mysql> insert into tarena.salary(date,employee_id,basic,bonus)values("20230710",18,25000,8000);

周六增量備份（備份周五備份后新產生的數據）

1. [root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=NSD2023...a --backup --target-dir=/new6 --incremental-basedir=/new5 --datadir=/var/lib/mysql
2. ……
3. ……
4. 230530 19:00:55 [00] ...done
5. xtrabackup: Transaction log of lsn (24848404) to (24848414) was copied.
6. 230530 19:00:56 completed OK!
7. [root@mysql50 ~]#
8. //插入新數據 （可以插入多行）
9. mysql> insert into tarena.salary(date,employee_id,basic,bonus)values("20230710",18,25000,8000);

周日增量備份（備份周六備份后新產生的數據）

1. [root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=NSD2023...a --backup --target-dir=/new7 --incremental-basedir=/new6 --datadir=/var/lib/mysql
2. ……
3. ……
4. 230530 19:00:55 [00] ...done
5. xtrabackup: Transaction log of lsn (24848404) to (24848414) was copied.
6. 230530 19:00:56 completed OK!
7. [root@mysql50 ~]#

步驟二：練習數據增量恢復

增量恢復數據步驟：

1. 準備恢復數據
2. 合并數據
3. 清空數據庫目錄
4. 拷貝數據
5. 修改數據庫目錄所有者/組用戶為mysql
6. 重啟數據庫服務

具體操作如下：

MySQL51 拷貝 MySQL50 的備份文件到 本機的根目錄下

1. [root@mysql50 ~]# scp –r root@192.168.88.50:/fullbak /
2. [root@mysql50 ~]# scp –r root@192.168.88.50:/new2 /
3. [root@mysql50 ~]# scp –r root@192.168.88.50:/new3 /
4. [root@mysql50 ~]# scp –r root@192.168.88.50:/new4 /
5. [root@mysql50 ~]# scp –r root@192.168.88.50:/new5 /
6. [root@mysql50 ~]# scp –r root@192.168.88.50:/new6 /
7. [root@mysql50 ~]# scp –r root@192.168.88.50:/new7 /

在MySQL51主機使用備份文件恢復數據

1)、準備恢復數據

1. [root@mysql51 ~]# xtrabackup --prepare --apply-log-only --target-dir=/fullbak
2. ……
3. ……
4. Log background threads are being closed...
5. Shutdown completed; log sequence number 24822898
6. Number of pools: 1
7. 230531 14:32:14 completed OK!
8. [root@mysql51 ~]#

2）、合并數據

1. //將周二的增量數據拷貝到周一備份目錄里，合并后周一的目錄里存放的是周一 + 周二 的數據
2. [root@mysql51 ~]# xtrabackup --prepare --apply-log-only --target-dir=/fullbak --incremental-dir=/new2
3. ……
4. ……
5. 230531 14:40:05 [00] Copying /new2/binlog.index to ./binlog.index
6. 230531 14:40:05 [00] ...done
7. 230531 14:40:05 completed OK!
8. [root@mysql51 ~]#
9. //將周三的增量數據拷貝到周一備份目錄里，合并后周一的目錄里存放的是周一 + 周二 + 周三 的數據
10. [root@mysql51 ~]# xtrabackup --prepare --apply-log-only --target-dir=/fullbak --incremental-dir=/new3
11. ……
12. ……
13. 230531 15:00:37 [00] Copying /new3/binlog.index to ./binlog.index
14. 230531 15:00:37 [00] ...done
15. 230531 15:00:37 completed OK!
16. [root@mysql51 ~]#
17. //將周四的增量數據拷貝到周一備份目錄里，合并后周一的目錄里存放的是周一 + 周二 + 周三 +周四的數據
18. [root@mysql51 ~]# xtrabackup --prepare --apply-log-only --target-dir=/fullbak --incremental-dir=/new4
19. ……
20. ……
21. 230531 15:00:37 [00] Copying /new4/binlog.index to ./binlog.index
22. 230531 15:00:37 [00] ...done
23. 230531 15:00:37 completed OK!
24. [root@mysql51 ~]#
25. //將周五的增量數據拷貝到周一備份目錄里，合并后周一的目錄里存放的是周一 + 周二 + 周三 +周四+周五的數據
26. [root@mysql51 ~]# xtrabackup --prepare --apply-log-only --target-dir=/fullbak --incremental-dir=/new5
27. ……
28. ……
29. 230531 15:00:37 [00] Copying /new5/binlog.index to ./binlog.index
30. 230531 15:00:37 [00] ...done
31. 230531 15:00:37 completed OK!
32. [root@mysql51 ~]#
33. //將周六的增量數據拷貝到周一備份目錄里，合并后周一的目錄里存放的是周一 + 周二 + 周三 +周四+周五+周六的數據
34. [root@mysql51 ~]# xtrabackup --prepare --apply-log-only --target-dir=/fullbak --incremental-dir=/new6
35. ……
36. ……
37. 230531 15:00:37 [00] Copying /new6/binlog.index to ./binlog.index
38. 230531 15:00:37 [00] ...done
39. 230531 15:00:37 completed OK!
40. [root@mysql51 ~]#
41. //將周日的增量數據拷貝到周一備份目錄里，合并后周一的目錄里存放的是周一 + 周二 + 周三 +周四+周五+周六+周日的數據
42. [root@mysql51 ~]# xtrabackup --prepare --apply-log-only --target-dir=/fullbak --incremental-dir=/new7
43. ……
44. ……
45. 230531 15:00:37 [00] Copying /new7/binlog.index to ./binlog.index
46. 230531 15:00:37 [00] ...done
47. 230531 15:00:37 completed OK!
48. [root@mysql51 ~]#
49. [root@mysql51 ~]# rm -rf /var/lib/mysql/*
50. [root@mysql51 ~]# xtrabackup --copy-back --target-dir=/fullbak
51. [root@mysql51 ~]# chown -R mysql:mysql /var/lib/mysql

6）重啟數據庫服務

1. [root@mysql51 ~]# systemctl restart mysqld

7）連接服務查看數據

1. [root@mysql51 ~]# mysql -uroot -pNSD2023...a
2. mysql> select count(*) from tarena.salary where date=20230710;
3. +----------+
4. | count(*) |
5. +----------+
6. | 75 |
7. +----------+
8. 1 row in set (0.01 sec)
9. mysql> select count(*) from tarena.salary where not date=20230710;
10. +----------+
11. | count(*) |
12. +----------+
13. | 8067 |
14. +----------+
15. 1 row in set (0.00 sec)

3 案例3：差異備份與恢復

3.1 問題

1. 練習差異備份
2. 練習差異恢復

3.2 方案

差異備份：備份完全備份后，新產生的數據。

PERCONA Xtrabackup是一款強大的在線熱備份工具，備份過程中不鎖庫表，適合生產環境。支持差異備份與恢復。

在192.168.88.50主機完成差異備份

3.3 步驟

實現此案例需要按照如下步驟進行。

步驟一：練習差異備份

差異備份

對數據做差異備份前，必須先有一次備份，也就是首次備份，通常是備份所有數據；比如每周周一完全備份，周二到周日差異備份。

1. //周一完全備份
2. [root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=NSD2023...a --backup --target-dir=/allbak --datadir=/var/lib/mysql
3. ……
4. ……
5. 230531 17:10:02 [00] Writing /allbak/xtrabackup_info
6. 230531 17:10:02 [00] ...done
7. xtrabackup: Transaction log of lsn (24881353) to (24881373) was copied.
8. 230531 17:10:03 completed OK!
9. [root@mysql50 ~]#
10. //插入新數據 （可以插入多行）
11. mysql> insert into tarena.salary(date,employee_id,basic,bonus)values("20230810",18,25000,8000);

周二差異備份，備份周一備份后新產生的數據

1. [root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=NSD2023...a --backup --target-dir=/dir2 --incremental-basedir=/allbak --datadir=/var/lib/mysql
2. ……
3. ……
4. 230531 17:23:56 [00] Writing /dir2/xtrabackup_info
5. 230531 17:23:56 [00] ...done
6. xtrabackup: Transaction log of lsn (24886741) to (24886751) was copied.
7. 230531 17:23:58 completed OK!
8. [root@mysql50 ~]#
9. //插入新數據 （可以插入多行）
10. mysql> insert into tarena.salary(date,employee_id,basic,bonus)values("20230810",18,25000,8000);

周三差異備份，備份周一備份后新產生的數據

1. [root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=NSD2023...a --backup --target-dir=/dir3 --incremental-basedir=/allbak --datadir=/var/lib/mysql
2. ……
3. ……
4. 230531 17:27:10 [00] Writing /dir3/xtrabackup_info
5. 230531 17:27:10 [00] ...done
6. xtrabackup: Transaction log of lsn (24892043) to (24892063) was copied.
7. 230531 17:27:11 completed OK!
8. [root@mysql50 ~]#
9. //插入新數據 （可以插入多行）
10. mysql> insert into tarena.salary(date,employee_id,basic,bonus)values("20230810",18,25000,8000);

周四差異備份，備份周一備份后新產生的數據

1. [root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=NSD2023...a --backup --target-dir=/dir4 --incremental-basedir=/allbak --datadir=/var/lib/mysql
2. ……
3. ……
4. 230531 17:31:00 [00] Writing /dir4/xtrabackup_info
5. 230531 17:31:00 [00] ...done
6. xtrabackup: Transaction log of lsn (24900560) to (24900580) was copied.
7. 230531 17:31:01 completed OK!
8. [root@mysql50 ~]#
9. //插入新數據 （可以插入多行）
10. mysql> insert into tarena.salary(date,employee_id,basic,bonus)values("20230810",18,25000,8000);

周五差異備份，備份周一備份后新產生的數據

1. [root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=NSD2023...a --backup --target-dir=/dir5 --incremental-basedir=/allbak --datadir=/var/lib/mysql
2. ……
3. ……
4. 230531 17:32:38 [00] Writing /dir5/xtrabackup_info
5. 230531 17:32:38 [00] ...done
6. xtrabackup: Transaction log of lsn (24906902) to (24906912) was copied.
7. 230531 17:32:39 completed OK!
8. [root@mysql50 ~]#
9. //插入新數據 （可以插入多行）
10. mysql> insert into tarena.salary(date,employee_id,basic,bonus)values("20230810",18,25000,8000);

周六差異備份，備份周一備份后新產生的數據

1. [root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=NSD2023...a --backup --target-dir=/dir6 --incremental-basedir=/allbak --datadir=/var/lib/mysql
2. ……
3. ……
4. 230531 17:41:01 [00] Writing /dir6/xtrabackup_info
5. 230531 17:41:01 [00] ...done
6. xtrabackup: Transaction log of lsn (24914729) to (24914739) was copied.
7. 230531 17:41:02 completed OK!
8. [root@mysql50 ~]#
9. //插入新數據 （可以插入多行）
10. mysql> insert into tarena.salary(date,employee_id,basic,bonus)values("20230810",18,25000,8000);

周日差異，備份備份周一備份后新產生的數據

1. [root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=NSD2023...a --backup --target-dir=/dir7 --incremental-basedir=/allbak --datadir=/var/lib/mysql
2. ……
3. ……
4. 230531 17:43:16 [00] Writing /dir7/xtrabackup_info
5. 230531 17:43:16 [00] ...done
6. xtrabackup: Transaction log of lsn (24920772) to (24920782) was copied.
7. 230531 17:43:17 completed OK!
8. [root@mysql50 ~]#

步驟二：練習差異恢復

差異恢復數據步驟：

1. 準備恢復數據
2. 合并數據
3. 清空數據庫目錄
4. 拷貝數據
5. 修改數據庫目錄所有者/組用戶為mysql
6. 重啟數據庫服務

具體操作如下：

MySQL51 拷貝 MySQL50 的備份文件到 本機的根目錄下

1. [root@mysql51 ~]# scp –r root@192.168.88.50:/allbak /
2. [root@mysql51 ~]# scp –r root@192.168.88.50:/dir7 /

在MySQL51主機使用備份文件恢復數據

1)、準備恢復數據

1. [root@mysql51 ~]# xtrabackup --prepare --apply-log-only --target-dir=/allbak
2. ……
3. ……
4. Log background threads are being closed...
5. Shutdown completed; log sequence number 24881373
6. Number of pools: 1
7. 230531 17:59:06 completed OK!
8. [root@mysql51 ~]#

2）、合并數據

1. //將周日的差異備份與周一的完全備份合并，因為周日的差異備份包擴周二+周日的所有數據
2. [root@mysql51 ~]# xtrabackup --prepare --apply-log-only --target-dir=/allbak --incremental-dir=/dir7
3. ……
4. ……
5. 230531 18:05:08 [00] Copying /dir7/binlog.000029 to ./binlog.000029
6. 230531 18:05:08 [00] ...done
7. 230531 18:05:08 [00] Copying /dir7/binlog.index to ./binlog.index
8. 230531 18:05:08 [00] ...done
9. 230531 18:05:08 completed OK!
10. [root@mysql51 ~]#
11. [root@mysql51 ~]# rm -rf /var/lib/mysql/*
12. [root@mysql51 ~]# xtrabackup --copy-back --target-dir=/allbak
13. [root@mysql51 ~]# chown -R mysql:mysql /var/lib/mysql

6）重啟數據庫服務

1. [root@mysql51 ~]# systemctl restart mysqld

7）連接服務查看數據

1. [root@mysql51 ~]# mysql -uroot -pNSD2023...a
2. mysql> select count(*) from tarena.salary where date=20230810;
3. +----------+
4. | count(*) |
5. +----------+
6. | 75 |
7. +----------+
8. 1 row in set (0.01 sec)
9. mysql> select count(*) from tarena.salary where not date=20230810;
10. +----------+
11. | count(*) |
12. +----------+
13. | 8067 |
14. +----------+
15. 1 row in set (0.00 sec)

4 案例4：binlog日志

4.1 問題

1. 查看正在使用的binlog日志文件
2. 自定義日志目錄和日志名
3. 手動創建新的日志文件
4. 練習日志相關命令的使用
5. 使用日志恢復數據

4.2 方案

binlog日志介紹：

1. 也稱做 二進制日志
2. MySQL服務日志文件的一種
3. 保存除查詢之外的所有SQL命令
4. 可用于數據的備份和恢復
5. 配置mysql主從同步的必要條件
6. 準備新的數據庫服務器如表-1，做binlog日志的練習

?

4.3 步驟

實現此案例需要按照如下步驟進行。

步驟一：查看正在使用的binlog日志文件

在新創建的數據庫服務器做如下操作:

1. [root@mysql52 ~]# yum -y install mysql-server mysql 安裝軟件
2. [root@mysql52 ~]# systemctl start mysqld 啟動服務
3. [root@mysql52 ~]# mysql 連接服務
4. mysql> show master status; 查看日志文件
5. +----------------+----------+--------------+------------------+-------------------+
6. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
7. +----------------+----------+--------------+------------------+-------------------+
8. | binlog.000001 | 156 | | | |
9. +----------------+----------+--------------+------------------+-------------------+
10. 1 row in set (0.00 sec)
11. 執行查詢命令
12. mysql> select count(*) from mysql.user;
13. +----------+
14. | count(*) |
15. +----------+
16. | 4 |
17. +----------+
18. 1 row in set (0.00 sec)
19. mysql> show master status; 執行查詢命令 日志偏移量不變
20. +----------------+----------+--------------+------------------+-------------------+
21. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
22. +----------------+----------+--------------+------------------+-------------------+
23. | binlog.000001 | 156 | | | |
24. +----------------+----------+--------------+------------------+-------------------+
25. 1 row in set (0.00 sec)
26. 執行建庫、建表命令
27. mysql> create database db1;
28. Query OK, 1 row affected (0.07 sec)
29. mysql> create table db1.user(name char(10));
30. Query OK, 0 rows affected (0.52 sec)
31. mysql> show master status; 執行寫命令 日志偏移量改變
32. +----------------+----------+--------------+------------------+-------------------+
33. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
34. +----------------+----------+--------------+------------------+-------------------+
35. | binlog.000001 | 535 | | | |
36. +----------------+----------+--------------+------------------+-------------------+
37. 1 row in set (0.00 sec)
38. mysql> insert into db1.user values("jim"); 插入記錄
39. Query OK, 1 row affected (0.10 sec)
40. mysql> show master status; 執行寫命令 日志偏移量改變
41. +----------------+----------+--------------+------------------+-------------------+
42. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
43. +----------------+----------+--------------+------------------+-------------------+
44. | binlog.000001 | 809 | | | |
45. +----------------+----------+--------------+------------------+-------------------+
46. 1 row in set (0.00 sec)
47. mysql>

步驟二：自定義日志目錄和日志名

日志文件默認保存在/var/lib/mysql目錄下，默認日志名binlog

1. [root@mysql52 ~]# vim /etc/my.cnf.d/mysql-server.cnf
2. [mysqld]
3. log-bin=/mylog/mysql52 //定義日志目錄和日志文件名(手動添加)
4. :wq
5. [root@mysql52 ~]# mkdir /mylog 創建目錄
6. [root@mysql52 ~]# chown mysql /mylog 修改目錄所有者mysql用戶
7. [root@mysql52 ~]# setenforce 0 關閉selinux
8. [root@mysql52 ~]# systemctl restart mysqld 重啟服務
9. [root@mysql52 ~]# ls /mylog/ 查看日志目錄
10. mysql52.000001 mysql52.index
11. [root@mysql52 ~]# mysql 登陸服務
12. Mysql> show master status ； 查看日志信息
13. +----------------+----------+--------------+------------------+-------------------+
14. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
15. +----------------+----------+--------------+------------------+-------------------+
16. | mysql52.000001 | 156 | | | |
17. +----------------+----------+--------------+------------------+-------------------+

步驟三：手動創建新的日志文件

默認日志文件容量大于1G時會自動創建新的日志文件，在日志文件沒寫滿時，執行的所有寫命令都會保存到當前使用的日志文件里。

1. //刷新前查看
2. mysql> show master status;
3. +----------------+----------+--------------+------------------+-------------------+
4. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
5. +----------------+----------+--------------+------------------+-------------------+
6. | mysql52.000001 | 156 | | | |
7. +----------------+----------+--------------+------------------+-------------------+
8. 1 row in set (0.00 sec)
10. mysql> flush logs; //刷新日志
11. Query OK, 0 rows affected (0.22 sec)
13. mysql> flush logs; //刷新日志
14. Query OK, 0 rows affected (0.16 sec)
16. mysql> show master status; //刷新一次創建一個新日志
17. +----------------+----------+--------------+------------------+-------------------+
18. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
19. +----------------+----------+--------------+------------------+-------------------+
20. | mysql52.000003 | 156 | | | |
21. +----------------+----------+--------------+------------------+-------------------+
22. 1 row in set (0.00 sec)
24. //只要服務重啟就會創建新日志
25. [root@mysql52 ~]# systemctl restart mysqld
26. [root@mysql52 ~]# mysql 連接服務
27. Mysql> show master status; 查看日志
28. +----------------+----------+--------------+------------------+-------------------+
29. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
30. +----------------+----------+--------------+------------------+-------------------+
31. | mysql52.000004 | 156 | | | |
32. +----------------+----------+--------------+------------------+-------------------+
33. [root@mysql52 ~]#
34. //完全備份后創建新的日志文件，創建的日志個數和備份庫的個數一致
35. [root@mysql52 ~]# mysqldump --flush-logs mysql user > user.sql
36. [root@mysql52 ~]# mysql -e 'show master status'
37. +----------------+----------+--------------+------------------+-------------------+
38. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
39. +----------------+----------+--------------+------------------+-------------------+
40. | mysql52.000005 | 156 | | | |
41. +----------------+----------+--------------+------------------+-------------------+
42. [root@mysql52 ~]# mysqldump --flush-logs -B mysql db1 > db_2.sql
43. [root@mysql52 ~]# mysql -e 'show master status'
44. +----------------+----------+--------------+------------------+-------------------+
45. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
46. +----------------+----------+--------------+------------------+-------------------+
47. | mysql52.000007 | 156 | | | |
48. +----------------+----------+--------------+------------------+-------------------+
49. [root@mysql52 ~]#

步驟四：練習日志相關命令的使用

MySQL服務提供了管理日志的專屬命令，具體練習如下:

1. //查看已有的日志文件
2. mysql> show binary logs;
3. 日志文件名 日志大小(字節) 加密（no/yes）
4. +----------------+-----------+-----------+
5. | Log_name | File_size | Encrypted |
6. +----------------+-----------+-----------+
7. | mysql52.000001 | 201 | No |
8. | mysql52.000002 | 201 | No |
9. | mysql52.000003 | 179 | No |
10. | mysql52.000004 | 201 | No |
11. | mysql52.000005 | 201 | No |
12. | mysql52.000006 | 201 | No |
13. | mysql52.000007 | 156 | No |
14. +----------------+-----------+-----------+
15. 7 rows in set (0.00 sec)
16. //查看正在使用的日志
17. mysql> show master status;
18. +----------------+----------+--------------+------------------+-------------------+
19. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
20. +----------------+----------+--------------+------------------+-------------------+
21. | mysql52.000007 | 156 | | | |
22. +----------------+----------+--------------+------------------+-------------------+
23. 1 row in set (0.00 sec)
24. //插入記錄
25. mysql> insert into db1.user values("yaya");
26. Query OK, 1 row affected (0.04 sec)
27. //查看日志文件內容
28. mysql> show binlog events in "mysql52.000007";
29. Log_name： 日志文件名。
30. Pos： 命令在日志文件中的起始位置。
31. Event_type： 事件類型，例如 Query、Table_map、Write_rows 等。
32. Server_id： 服務器 ID。
33. End_log_pos：命令在文件中的結束位置，以字節為單位。
34. Info：執行命令信息。
35. +----------------+-----+----------------+-----------+-------------+--------------------------------------+
36. | Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
37. +----------------+-----+----------------+-----------+-------------+--------------------------------------+
38. | mysql52.000007 | 4 | Format_desc | 1 | 125 | Server ver: 8.0.26, Binlog ver: 4 |
39. | mysql52.000007 | 125 | Previous_gtids | 1 | 156 | |
40. | mysql52.000007 | 156 | Anonymous_Gtid | 1 | 235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
41. | mysql52.000007 | 235 | Query | 1 | 306 | BEGIN |
42. | mysql52.000007 | 306 | Table_map | 1 | 359 | table_id: 108 (db1.user) |
43. | mysql52.000007 | 359 | Write_rows | 1 | 400 | table_id: 108 flags: STMT_END_F |
44. | mysql52.000007 | 400 | Xid | 1 | 431 | COMMIT /* xid=649 */ |
45. +----------------+-----+----------------+-----------+-------------+--------------------------------------+
46. 7 rows in set (0.00 sec)
47. //刪除日志文件名之前的所有日志文件
48. mysql> purge master logs to "mysql52.000004";
49. Query OK, 0 rows affected (0.10 sec)
50. //查看已有的日志文件
51. mysql> show binary logs;
52. +----------------+-----------+-----------+
53. | Log_name | File_size | Encrypted |
54. +----------------+-----------+-----------+
55. | mysql52.000004 | 201 | No |
56. | mysql52.000005 | 201 | No |
57. | mysql52.000006 | 201 | No |
58. | mysql52.000007 | 431 | No |
59. +----------------+-----------+-----------+
60. 4 rows in set (0.00 sec)
61. //刪除所有日志文件，并重新創建日志文件
62. mysql> reset master;
63. Query OK, 0 rows affected (0.14 sec)
64. //查看已有的日志文件 ，僅有第1個文件了
65. mysql> show binary logs;
66. +----------------+-----------+-----------+
67. | Log_name | File_size | Encrypted |
68. +----------------+-----------+-----------+
69. | mysql52.000001 | 156 | No |
70. +----------------+-----------+-----------+
71. 1 row in set (0.00 sec)

步驟五：使用日志恢復數據

把查看到的文件內容管道給連接mysql服務的命令執行

恢復數據命令：

mysqlbinlog /目錄/文件名 | mysql –uroot -p密碼

1）在mysql52主機執行如下操：

1. //重置日志
2. mysql> reset master;
3. Query OK, 0 rows affected (0.09 sec)
4. //查看日志
5. mysql> show master status;
6. +----------------+----------+--------------+------------------+-------------------+
7. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
8. +----------------+----------+--------------+------------------+-------------------+
9. | mysql52.000001 | 156 | | | |
10. +----------------+----------+--------------+------------------+-------------------+
11. 1 row in set (0.00 sec)
12. //建庫、
13. mysql> create database gamedb;
14. Query OK, 1 row affected (0.07 sec)
15. //建表
16. mysql> create table gamedb.t1(name char(10),class char(3));
17. Query OK, 0 rows affected (0.55 sec)
18. //插入記錄
19. mysql> insert into gamedb.t1 values ("yaya","nsd");
20. Query OK, 1 row affected (0.08 sec)
21. mysql> insert into gamedb.t1 values ("yaya","nsd");
22. Query OK, 1 row affected (0.04 sec)
23. mysql> insert into gamedb.t1 values ("yaya","nsd");
24. Query OK, 1 row affected (0.08 sec)
25. //查看表記錄
26. mysql> select * from gamedb.t1;
27. +------+-------+
28. | name | class |
29. +------+-------+
30. | yaya | nsd |
31. | yaya | nsd |
32. | yaya | nsd |
33. +------+-------+
34. 3 rows in set (0.00 sec)
35. mysql> exit
36. //把日志文件拷貝給恢復數據的服務器,比如 mysql50
37. [root@mysql52 ~]# scp /mylog/mysql52.000001 root@192.168.88.50:/root/
38. The authenticity of host '192.168.88.50 (192.168.88.50)' can't be established.
39. ECDSA key fingerprint is SHA256:t7J3okFd0o+9zTmFCIetvDl6mxGCmc43VoD6C65zico.
40. Are you sure you want to continue connecting (yes/no/[fingerprint])? Yes 同意
41. Warning: Permanently added '192.168.88.50' (ECDSA) to the list of known hosts.
42. root@192.168.88.50's password: mysql50的密碼
43. mysql52.000001 100% 1410 1.6MB/s 00:00
44. [root@mysql52 ~]#

2）在MySQL50 使用日志恢復數據

1. //查看日志
2. [root@mysql50 ~]# ls /root/mysql52.000001
3. /root/mysql52.000001
4. //執行日志恢復數據
5. [root@mysql50 ~]# mysqlbinlog /root/mysql52.000001 | mysql -uroot -pNSD2023...a
6. mysql: [Warning] Using a password on the command line interface can be insecure.
7. //連接服務查看數據
8. [root@mysql50 ~]# mysql -uroot -pNSD2023...a -e 'select * from gamedb.t1'
9. mysql: [Warning] Using a password on the command line interface can be insecure.
10. +------+-------+
11. | name | class |
12. +------+-------+
13. | yaya | nsd |
14. | yaya | nsd |
15. | yaya | nsd |
16. +------+-------+
17. [root@mysql50 ~]#