完全备份、增量备份、差异备份、binlog日志

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
  13. [root@host50 ~ ]# source /etc/bashrc
  14. //查看帮助信息
  15. [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)
  9. mysql> flush logs; //刷新日志
  10. Query OK, 0 rows affected (0.22 sec)
  11. mysql> flush logs; //刷新日志
  12. Query OK, 0 rows affected (0.16 sec)
  13. mysql> show master status; //刷新一次创建一个新日志
  14. +----------------+----------+--------------+------------------+-------------------+
  15. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
  16. +----------------+----------+--------------+------------------+-------------------+
  17. | mysql52.000003 | 156 | | | |
  18. +----------------+----------+--------------+------------------+-------------------+
  19. 1 row in set (0.00 sec)
  20. //只要服务重启就会创建新日志
  21. [root@mysql52 ~]# systemctl restart mysqld
  22. [root@mysql52 ~]# mysql 连接服务
  23. Mysql> show master status; 查看日志
  24. +----------------+----------+--------------+------------------+-------------------+
  25. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
  26. +----------------+----------+--------------+------------------+-------------------+
  27. | mysql52.000004 | 156 | | | |
  28. +----------------+----------+--------------+------------------+-------------------+
  29. [root@mysql52 ~]#
  30. //完全备份后创建新的日志文件,创建的日志个数和备份库的个数一致
  31. [root@mysql52 ~]# mysqldump --flush-logs mysql user > user.sql
  32. [root@mysql52 ~]# mysql -e 'show master status'
  33. +----------------+----------+--------------+------------------+-------------------+
  34. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
  35. +----------------+----------+--------------+------------------+-------------------+
  36. | mysql52.000005 | 156 | | | |
  37. +----------------+----------+--------------+------------------+-------------------+
  38. [root@mysql52 ~]# mysqldump --flush-logs -B mysql db1 > db_2.sql
  39. [root@mysql52 ~]# mysql -e 'show master status'
  40. +----------------+----------+--------------+------------------+-------------------+
  41. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
  42. +----------------+----------+--------------+------------------+-------------------+
  43. | mysql52.000007 | 156 | | | |
  44. +----------------+----------+--------------+------------------+-------------------+
  45. [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 ~]#

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/96116.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

考研408 | 【计算机网络】 传输层

导图 传输层的功能 传输层的两个协议 传输层的寻址与端口 UDP协议 UDP的主要特点 UDP首部格式&#xff1a; UDP校验&#xff1a; TCP协议 TCP协议的特点 TCP报文段首部格式 TCP连接管理 TCP的连接建立 SYN洪泛攻击 TCP的连接释放 TCP可靠传输 序号&#xff1a; 确认&#xff1…

三本书与三场发布会,和鲸社区重新定义编程类书籍从阅读到实践新体验

当 AI 开发者社区配备 AI 基础设施开发平台工具时&#xff0c;它还能做什么&#xff1f; 答案是&#xff1a;过去半年&#xff0c;和鲸社区凭借在气象、医学、社科等垂直领域的长期积累以及多方伙伴的支持&#xff0c;联合举办了三场新书发布会——从 Python 到 R 语言 、从气…

虚拟机远程连接出现 Connection refused: connect问题(已解决)

如果你也跟我一样出现了这样的问题&#xff0c;并且网上的方法试了都不行&#xff0c;不妨试试我的简单粗暴的方法&#xff0c; 那就是拔网线&#xff0c;我的就是拔网线&#xff0c;重新连接就行了&#xff0c;佛了

【从0开始学架构笔记】01 基础架构

文章目录 一、架构的定义1. 系统与子系统2. 模块与组件3. 框架与架构4. 重新定义架构 二、架构设计的目的三、复杂度来源&#xff1a;高性能1. 单机复杂度2. 集群复杂度2.1 任务分配2.2 任务分解&#xff08;微服务&#xff09; 四、复杂度来源&#xff1a;高可用1. 计算高可用…

【网络教程】如何创建/添加钉钉机器人以及如何获取机器人的Token/Secret

文章目录 创建钉钉机器人添加钉钉机器人获取机器人的Token/Secret相关网站创建钉钉机器人 这里以PC端的操作为例,按照如下操作进行 访问 钉钉开放平台选择机器人选项卡,点击右上角的创建应用,这里会有一个弹窗,我这里选择的是继续使用旧版,如图按照要求填写相关信息创建自…

Linux实用运维脚本分享

Linux实用运维脚本分享&#x1f343; MySQL备份 目录备份 PING查询 磁盘IO检查 性能相关 进程相关 javadump.sh 常用工具安装 常用lib库安装 系统检查脚本 sed进阶 MySQL备份 #!/bin/bashset -eUSER"backup" PASSWORD"backup" # 数据库数据目录…

2024浙大MBA/MEM/MPA四个月冲刺备考策略

近期收到很多考生的咨询&#xff1a;距离联考就仅剩四个多月的时间&#xff0c;这个管理类联考的难度如何&#xff1f;主要考些什么内容&#xff1f;现在才开始备考还有希望上岸浙大吗&#xff1f;是不是要等到明年在开始备考比较合适&#xff1f;那么今天在这里小立老师就跟大…

分布式锁有哪些应用场景和实现?

电商网站都会遇到秒杀、特价之类的活动&#xff0c;大促活动有一个共同特点就是访问量激增&#xff0c;在高并发下会出现成千上万人抢购一个商品的场景。虽然在系统设计时会通过限流、异步、排队等方式优化&#xff0c;但整体的并发还是平时的数倍以上&#xff0c;参加活动的商…

【100天精通python】Day39:GUI界面编程_PyQt 从入门到实战(下)_图形绘制和动画效果,数据可视化,刷新交互

目录 专栏导读 6 图形绘制与动画效果 6.1 绘制基本图形、文本和图片 6.2 实现动画效果和过渡效果 7 数据可视化 7.1 使用 Matplotlib绘制图表 7.2 使用PyQtGraph绘制图表 7.3 数据的实时刷新和交互操作 7.3.1 数据的实时刷新 7.3.2 交互操作 7.4 自定义数据可视化…

易服客工作室:Pixwell主题 – 现代杂志/WordPress新闻主题

PixWell主题概述 Pixwell主题是一个强大、多用途和现代的WordPress杂志主题&#xff0c;具有像素完美的设计、出色的功能、完全响应和移动友好。它非常灵活&#xff0c;非常适合食谱、时尚、旅行、技术、个人或任何其他很棒的杂志和博客网站。 该主题与 Elementor、Cooked&am…

【Maven教程】(一)入门介绍篇:Maven基础概念与其他构建工具:理解构建过程与Maven的多重作用,以及与敏捷开发的关系 ~

Maven入门介绍篇 1️⃣ 基础概念1.1 构建1.2 maven对构建的支持1.3 Maven的其他作用 2️⃣ 其他构建工具2.1 IDE2.2 Make2.3 Ant2.4 Jenkins 3️⃣ Maven与敏捷开发&#x1f33e; 总结 1️⃣ 基础概念 "Maven"可以翻译为 “知识的积累者” 或 “专家”。这个词源于波…

【解决】Kafka Exception thrown when sending a message with key=‘null‘ 异常

问题原因&#xff1a; 如下图&#xff0c;kafka 中配置的是监听域名的方式&#xff0c;但程序里使用的是 ip:port 的连接方式。 解决办法&#xff1a; kafka 中配置的是域名的方式&#xff0c;程序里也相应配置成 域名:port 的方式&#xff08;注意&#xff1a;本地h…

Excelize Go语言操作 Office Excel文档基础库

Excelize 是 Go 语言编写的用于操作 Office Excel 文档基础库&#xff0c;基于 ECMA-376&#xff0c;ISO/IEC 29500 国际标准。可以使用它来读取、写入由 Microsoft Excel™ 2007 及以上版本创建的电子表格文档。支持 XLAM / XLSM / XLSX / XLTM / XLTX 等多种文档格式&#xf…

【.net】本地调试运行只能用localhost的问题

【.net】本地调试运行只能用localhost的问题 解决方案 找到到项目目录下 隐藏文件夹 .vs /项目名称/config/applicationhost.config <bindings><binding protocol"http" bindingInformation"*:1738:localhost" /></bindings> 再加一条你…

大数据面试题:Spark的任务执行流程

面试题来源&#xff1a; 《大数据面试题 V4.0》 大数据面试题V3.0&#xff0c;523道题&#xff0c;679页&#xff0c;46w字 可回答&#xff1a;1&#xff09;Spark的工作流程&#xff1f;2&#xff09;Spark的调度流程&#xff1b;3&#xff09;Spark的任务调度原理&#xf…

国产之光:讯飞星火最新大模型V2.0

大家好&#xff0c;我是herosunly。985院校硕士毕业&#xff0c;现担任算法研究员一职&#xff0c;热衷于机器学习算法研究与应用。曾获得阿里云天池比赛第一名&#xff0c;CCF比赛第二名&#xff0c;科大讯飞比赛第三名。拥有多项发明专利。对机器学习和深度学习拥有自己独到的…

ICCV23 | Ada3D:利用动态推理挖掘3D感知任务中数据冗余性

​ 论文地址&#xff1a;https://arxiv.org/abs/2307.08209 项目主页&#xff1a;https://a-suozhang.xyz/ada3d.github.io/ 01. 背景与动因 3D检测(3D Detection)任务是自动驾驶任务中的重要任务。由于自动驾驶任务的安全性至关重要(safety-critic)&#xff0c;对感知算法的延…

python爬虫7:实战1

python爬虫7&#xff1a;实战1 前言 ​ python实现网络爬虫非常简单&#xff0c;只需要掌握一定的基础知识和一定的库使用技巧即可。本系列目标旨在梳理相关知识点&#xff0c;方便以后复习。 申明 ​ 本系列所涉及的代码仅用于个人研究与讨论&#xff0c;并不会对网站产生不好…

Java-运算符和控制语句(上)(基于c语言的补充)

算术运算符 关于求余 不管分子&#xff0c;分母是正还是负&#xff0c;对于分母&#xff0c;直接取正&#xff1b;对于分子若有负号&#xff0c;则先提取出来&#xff1b;剩下两个正的分子分母运算&#xff1b;最后&#xff0c;若刚才的分子有负号&#xff0c;对最后的结果添加…

C语言刷题训练DAY.8

1.计算单位阶跃函数 解题思路&#xff1a; 这个非常简单&#xff0c;只需要if else语句即可完成 解题代码&#xff1a; #include <stdio.h>int main() {int t 0;while(scanf("%d",&t)!EOF){if (t > 0)printf("1\n");else if (t < 0)pr…