MySQL（五）

笔落_惊风雨

已于 2024-08-13 00:55:41 修改

阅读量248

点赞数

分类专栏：运维工具 # MySQL 文章标签： mysql 数据库 linux 运维

于 2023-09-10 05:05:50 首次发布

本文链接：https://blog.csdn.net/qq_40875048/article/details/132768715

版权

运维工具同时被 2 个专栏收录

45 篇文章 2 订阅

订阅专栏

MySQL

7 篇文章 0 订阅

订阅专栏

备份

使用数据库的过程中，有多种原因造成数据的丢失：

程序错误：指对数据库操作的程序难免有些错误，造成数据丢失
人为错误：指由于使用人员的误操作造成的数据被破坏，还有可能是黑客对系统攻击造成的数据丢失等
计算机故障：指运行数据库的服务器操作系统或软件损坏，有可能造成数据的损坏
磁盘故障：指硬盘等存储数据的硬件设备，长时间运行后可能损坏，造成数据丢失
灾难（如火灾、地震）和偷窃：指自然灾害等的发生，有可能造成数据丢失

一个合理的数据库备份方案，能够在数据丢失时，有效地恢复数据，实现数据的一致性，服务可用性，而且也需要考虑技术实现难度和有效地利用资源。

备份技术

物理备份/冷备份

直接复制数据库文件，适用于大型数据库环境，不受存储引擎的限制，但不能恢复到不同的MySQL版本；拷贝数据，速度快，但是需要关闭MySQL服务

逻辑备份/热备份

备份的内容是建库、建表、更新数据等操作所执行的SQL语句（DDL，DML，DCL）,适用于中小型数据库；效率相对较低，MySQL服务可正常运行。

温备

读操作可执行；写操作不可执行

备份种类

完全备份

完全备份是对整个数据库的备份、数据库结构和文件结构的备份，保存的是备份完成时刻的数据库，是增量备份的基础。

优点：备份与恢复操作简单方便，缺点是数据存在大量重复，占用大量的备份空间，备份的时间长

增量备份（incremental backup）

针对于上一次备份（无论是哪种备份）：备份上一次备份后，所有发生变化的文件。

差异备份（DifferentialBackup）

记录自上次完全数据库备份之后对数据库的更改的数据库备份。

备份目标

数据
二进制日志、InnoDB的事务日志
用户账号，权限设置，程序代码（存储过程、函数、触发器、事件调度器）
服务器配置文件

备份注意

能容忍最多丢失多少数据
备份产生的负载
备份过程的时长
温备的持锁多久
恢复数据需要在多长时间内完成
需要备份和恢复哪些数据

还原要点

备份后要做还原测试，用于测试备份的可用性
还原演练，写成规范的技术文档

备份工具

cp, tar等复制归档工具：物理备份工具，适用所有存储引擎；只支持冷备；完全和部分备份
LVM的快照：先加读锁，做快照后解锁，几乎热备；借助文件系统工具进行备份
mysqldump：逻辑备份工具，适用所有存储引擎，对MyISAM存储引擎进行温备；支持完全或部分备份；对InnoDB存储引擎支持热备，结合binlog的增量备份
xtrabackup：由Percona提供支持对InnoDB做热备(物理备份)的工具，支持完全备份、增量备份
MariaDB Backup：从MariaDB 10.1.26开始集成，基于Percona XtraBackup 2.3.8实现
mysqlbackup：热备份， MySQL Enterprise Edition 组件
mysqlhotcopy：PERL 语言实现，几乎冷备，仅适用于MyISAM存储引擎，使用LOCK TABLES、FLUSH TABLES和cp或scp来快速备份数据库

mysqldump备份工具

mysqldump是MySQL的客户端命令，通过mysql协议连接至mysql服务器进行备份

常见选项

-u：指定用户名

-p：指定密码

-A：备份所有数据库，包括create database语句

-B：指定要备份的数据库，包括create database语句

-E：备份相关所有的event scheduler 事件调度器

-R：备份所有存储过程和自定义函数

--triggers：备份表相关触发器，默认启用；用--skip-triggers不备份触发器

--default-character-set=utf8：指定字符集

--master-data= ：此项在MySQL8.0.26版后，变为--source-data。使用此选项要启用二进制日志。

为1（默认）时，所备份的数据之前加一条 CHANGE MASTER TO语句；适合主从复制多机使用。

为2时，增加的 CHANGE MASTER TO语句被注释；适合单机使用、备份还原。

-F：备份前滚动日志，生成新的二进制日志；配合 -A 或 -B 时会导致多次滚动日志，建议和--single-transaction、--master-data一起使用，可实现只刷新一次二进制日志。

--hex-blob：使用十六进制符号转储二进制列，当有包括BINARY,VARBINARY,BLOB，
BIT的数据类型的列时使用，避免乱码。

--flush-privileges：此选项会刷新MySQL的权限系统，确保新的权限设置立即生效

关于MyISAM存储引擎相关的备份选项

MyISAM不支持事务，只能支持温备；不支持热备，所以必须先锁定要备份的库，再备份。

-x，--lock-all-tables：加全局读锁，锁定所有库的所有表，同时加--single-transaction或--
lock-tables选项会关闭此选项功能。注意：数据量大时，可能会导致长时间无法并发访问数据库

-l，--lock-tables：对于需要备份的每个数据库，在启动备份之前分别锁定其所有表，默认为on，--
skip-lock-tables选项可禁用，对备份MyISAM的多个库，可能会造成数据不一致

这两项对InnoDB表一样生效，实现温备，但不推荐使用。

关于InnoDB存储引擎相关的备份选项

--single-transaction：推荐使用，不适用MyISAM。此项会在开始备份前开启事务。

生产环境实战备份策略

InnoDB

mysqldump -uroot -p密码 -A -F -E -R --triggers --single-transaction --master-data=1 --flush-privileges --default-character-set=utf8 --hex-blob > ${BACKUP}/fullbak_${BACKUP_TIME}.sql

MyISAM

mysqldump -uroot -p -A -F -E -R -x --master-data=1 --flush-privileges --triggers --default-character-set=utf8 --hex-blob > ${BACKUP}/fullbak_${BACKUP_TIME}.sql

percona-xtrabackup

地址：Software Downloads - Percona

它是开源免费的支持MySQL 数据库热备份的软件，它能对InnoDB和XtraDB存储引擎的数据库非阻塞地备份。它能不暂停服务创建Innodb热备份；

percona-xtrabackup-2x 版本支持MySQL5.x版本；percona-xtrabackup-8x 版本支持MySQL8.x版本。

由于Oracle在2020年4月底发布的MySQL 8.0.20的变化，Percona XtraBackup 8.0，最高版本为8.0.11，与MySQL 8.0.20或更高版本，或基于它的Percona产品不兼容： Percona Server for MySQL 和 Percona XtraDB Cluster。

常见选项

--target-dir：备份文件保存的目录

完全备份

创建

xtrabackup --backup --target-dir=/data/backups/

准备

xtrabackup --prepare --target-dir=/data/backups/

恢复

恢复前数据库服务必须是停止状态；数据目录必须为空；

xtrabackup --copy-back --target-dir=/data/backups/

增量备份

--incremental-basedir：指定本次增量备份的基础备份

--incremental-dir：指定增量备份存放的目录

创建一个完全备份

xtrabackup --backup --target-dir=/data/backups/base

创建第一个增量备份

xtrabackup --backup --target-dir=/data/backups/inc1 --incremental-basedir=/data/backups/base

在第一个增量备份基础上创建第二个增量备份

xtrabackup --backup --target-dir=/data/backups/inc2 --incremental-basedir=/data/backups/inc1

准备基本备份

现在应该有 /data/backups/base、/data/backups/inc1、/data/backups/inc2 这三个文件

xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base

将第一次增量备份应用于完全备份

xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base --incremental-dir=/data/backups/inc1

将第二次增量备份应用于完全备份

最后一次应用增量备份不要加 --apply-log-only

xtrabackup --prepare --target-dir=/data/backups/base --incremental-dir=/data/backups/inc2

恢复

恢复前数据库服务必须是停止状态；数据目录必须为空；

xtrabackup --copy-back --target-dir=/data/backups/

记录一（2023.09）

环境

mysql 8.0.19 yum方式安装

Percona XtraBackup 8.0.10 yum方式安装

安装mysql

[root@wenzi ~]# ls
anaconda-ks.cfg  mysql-8.0.19-1.el7.x86_64.rpm-bundle.tar  original-ks.cfg  percona-xtrabackup-80-8.0.10-1.el7.x86_64.rpm
[root@wenzi ~]# tar -x -f mysql-8.0.19-1.el7.x86_64.rpm-bundle.tar
[root@wenzi ~]# ls
anaconda-ks.cfg                                 mysql-community-devel-8.0.19-1.el7.x86_64.rpm            mysql-community-server-8.0.19-1.el7.x86_64.rpm
mysql-8.0.19-1.el7.x86_64.rpm-bundle.tar        mysql-community-embedded-compat-8.0.19-1.el7.x86_64.rpm  mysql-community-test-8.0.19-1.el7.x86_64.rpm
mysql-community-client-8.0.19-1.el7.x86_64.rpm  mysql-community-libs-8.0.19-1.el7.x86_64.rpm             original-ks.cfg
mysql-community-common-8.0.19-1.el7.x86_64.rpm  mysql-community-libs-compat-8.0.19-1.el7.x86_64.rpm      percona-xtrabackup-80-8.0.10-1.el7.x86_64.rpm2

#提前卸载mariadb的lib包，不然会和mysql-community-libs冲突
[root@wenzi ~]# yum -y remove mariadb-libs.x86_64 1:5.5.68-1.el7
#提前安装，避免出错提示
[root@wenzi ~]# yum -y install libaio net-tools
#按照以下安装顺序
[root@wenzi ~]# rpm -ivh mysql-community-common-8.0.19-1.el7.x86_64.rpm && rpm -ivh mysql-community-libs-8.0.19-1.el7.x86_64.rpm && rpm -ivh mysql-community-client-8.0.19-1.el7.x86_64.rpm && rpm -ivh mysql-community-server-8.0.19-1.el7.x86_64.rpm && rpm -ivh mysql-community-libs-compat-8.0.19-1.el7.x86_64.rpm && yum -y install  percona-xtrabackup-80-8.0.10-1.el7.x86_64.rpm

[root@wenzi ~]# systemctl enable --now mysqld
[root@wenzi ~]# grep "password" /var/log/mysqld.log
2023-09-08T17:44:30.120757Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: OYph_Xz4H:zK
mysql> alter user 'root'@'localhost' identified by 'Admin.123';
Query OK, 0 rows affected (0.01 sec)
mysql> quit
Bye

[root@wenzi ~]# echo "log_bin" >> /etc/my.cnf
[root@wenzi ~]# systemctl restart mysqld

[root@wenzi ~]# mysql -uroot -p'Admin.123'
mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed
mysql> create table student (id int,name varchar(20));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into student values (1,'liubei');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test.student;
+------+--------+
| id   | name   |
+------+--------+
|    1 | liubei |
+------+--------+
1 row in set (0.00 sec)

完整备份

创建备份

[root@wenzi ~]# ls /var/lib/mysql
auto.cnf       ca-key.pem       client-key.pem  ib_logfile0  #innodb_temp  mysql.sock          private_key.pem  server-key.pem  undo_001          wenzi-bin.000002
binlog.000001  ca.pem           ib_buffer_pool  ib_logfile1  mysql         mysql.sock.lock     public_key.pem   sys             undo_002          wenzi-bin.index
binlog.index   client-cert.pem  ibdata1         ibtmp1       mysql.ibd     performance_schema  server-cert.pem  test            wenzi-bin.000001

[root@wenzi ~]# mkdir -p /data/backups
[root@wenzi ~]# xtrabackup --backup --user=root --password='Admin.123' --target-dir=/data/backups/`date +'%F-%H-%M-%S'`

[root@wenzi ~]# ls /data/backups/2023-09-09-21-05-16/
backup-my.cnf   ibdata1  mysql.ibd           sys   undo_001  wenzi-bin.000002  xtrabackup_binlog_info  xtrabackup_info     xtrabackup_tablespaces
ib_buffer_pool  mysql    performance_schema  test  undo_002  wenzi-bin.index   xtrabackup_checkpoints  xtrabackup_logfile
#查看二进制日志文件位置
[root@wenzi ~]# cat /data/backups/2023-09-09-21-05-16/xtrabackup_binlog_info
wenzi-bin.000002        155

--target-dir=备份存储目录

--databases=要备份的数据库

--tables=要备份的实际表名称

模拟数据库损坏

[root@wenzi ~]# systemctl stop mysqld
[root@wenzi ~]# rm -rf /var/lib/mysql/*
[root@wenzi ~]# rm -f /var/log/mysqld.log

恢复数据

#还原前先进行一致性检查
[root@wenzi ~]# xtrabackup --prepare --target-dir=/data/backups/2023-09-09-21-05-16/
#恢复数据
[root@wenzi ~]# xtrabackup --copy-back --target-dir=/data/backups/2023-09-09-21-05-16/
#恢复权限
[root@wenzi ~]# chown -R mysql:mysql /var/lib/mysql
[root@wenzi ~]# systemctl start mysqld

检查数据

[root@wenzi ~]# mysql -uroot -p'Admin.123'
mysql> select * from test.student;
+------+--------+
| id   | name   |
+------+--------+
|    1 | liubei |
+------+--------+
1 row in set (0.01 sec)

增量备份

数据库新增数据

mysql> insert into test.student values (2,'zhaoyun');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test.student;
+------+---------+
| id   | name    |
+------+---------+
|    1 | liubei  |
|    2 | zhaoyun |
+------+---------+
2 rows in set (0.00 sec)

第一次增量备份

第一次增量备份是基于<完整备份实验>的完整备份

[root@wenzi ~]# xtrabackup --backup -uroot -p'Admin.123' --target-dir=/data/backups/inc01 --incremental-basedir=/data/backups/2023-09-09-21-05-16/
[root@wenzi ~]# ls /data/backups/inc01/
backup-my.cnf   ibdata1.meta     mysql.ibd.meta      test            undo_002.delta    wenzi-bin.index         xtrabackup_info
ib_buffer_pool  mysql            performance_schema  undo_001.delta  undo_002.meta     xtrabackup_binlog_info  xtrabackup_logfile
ibdata1.delta   mysql.ibd.delta  sys                 undo_001.meta   wenzi-bin.000004  xtrabackup_checkpoints  xtrabackup_tablespaces

数据库新增数据

mysql> insert into test.student values (3,'guanyu');
Query OK, 1 row affected (0.01 sec)

mysql> select * from test.student;
+------+---------+
| id   | name    |
+------+---------+
|    1 | liubei  |
|    2 | zhaoyun |
|    3 | guanyu  |
+------+---------+
3 rows in set (0.00 sec)

第二次增量备份

第二次增量备份是基于第一次增量备份

多次增量备份时指定的增量备份存储目录应该不同

[root@wenzi ~]# xtrabackup --backup -uroot -p'Admin.123' --target-dir=/data/backups/inc02 --incremental-basedir=/data/backups/inc01/
[root@wenzi ~]# ls /data/backups/inc02
backup-my.cnf   ibdata1.meta     mysql.ibd.meta      test            undo_002.delta    wenzi-bin.index         xtrabackup_info
ib_buffer_pool  mysql            performance_schema  undo_001.delta  undo_002.meta     xtrabackup_binlog_info  xtrabackup_logfile
ibdata1.delta   mysql.ibd.delta  sys                 undo_001.meta   wenzi-bin.000005  xtrabackup_checkpoints  xtrabackup_tablespaces

模拟数据库损坏

[root@wenzi ~]# systemctl stop mysqld
[root@wenzi ~]# rm -rf /var/lib/mysql/*
[root@wenzi ~]# rm -f /var/log/mysqld.log

恢复数据

#检查一致性
#先回滚 完整备份
[root@wenzi ~]# xtrabackup --prepare --apply-log-only  --target-dir=/data/backups/2023-09-09-21-05-16/
#接着回滚第一次增量备份
[root@wenzi ~]# xtrabackup --prepare --apply-log-only  --target-dir=/data/backups/2023-09-09-21-05-16/ --incremental-dir=/data/backups/inc01/
#再回滚第二次增量备份
[root@wenzi ~]# xtrabackup --prepare --apply-log-only  --target-dir=/data/backups/2023-09-09-21-05-16/ --incremental-dir=/data/backups/inc02/

#此时所有备份文件已合并在完整备份文件中
#开始恢复
[root@wenzi ~]# xtrabackup --copy-back --target-dir=/data/backups/2023-09-09-21-05-16/
[root@wenzi ~]# chown -R mysql:mysql /var/lib/mysql*
[root@wenzi ~]# systemctl start mysqld

检查数据

[root@wenzi ~]# mysql -uroot -p'Admin.123'
mysql> select * from test.student;
+------+---------+
| id   | name    |
+------+---------+
|    1 | liubei  |
|    2 | zhaoyun |
|    3 | guanyu  |
+------+---------+
3 rows in set (0.02 sec)

差异备份

数据库改变数据

mysql> update test.student set name='zhangfei' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test.student;
+------+----------+
| id   | name     |
+------+----------+
|    1 | liubei   |
|    2 | zhangfei |
|    3 | guanyu   |
+------+----------+
3 rows in set (0.00 sec)

第一次差异备份

第一次差异备份是基于<完整备份实验>的完整备份

[root@wenzi ~]# xtrabackup --backup -uroot -p'Admin.123' --target-dir=/data/backups/cy01/ --incremental-basedir=/data/backups/2023-09-09-21-05-16/
[root@wenzi ~]# ls /data/backups/cy01/
backup-my.cnf   ibdata1.meta     mysql.ibd.meta      test            undo_002.delta    wenzi-bin.index         xtrabackup_info
ib_buffer_pool  mysql            performance_schema  undo_001.delta  undo_002.meta     xtrabackup_binlog_info  xtrabackup_logfile
ibdata1.delta   mysql.ibd.delta  sys                 undo_001.meta   wenzi-bin.000007  xtrabackup_checkpoints  xtrabackup_tablespaces

数据库改变数据

mysql> update test.student set name='huangzhong' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test.student;
+------+------------+
| id   | name       |
+------+------------+
|    1 | huangzhong |
|    2 | zhangfei   |
|    3 | guanyu     |
+------+------------+
3 rows in set (0.00 sec)

第二次差异备份

第二次差异备份是基于<完整备份实验>的完整备份

[root@wenzi ~]# xtrabackup --backup -uroot -p'Admin.123' --target-dir=/data/backups/cy02/ --incremental-basedir=/data/backups/2023-09-09-21-05-16/
[root@wenzi ~]# ls /data/backups/cy02/
backup-my.cnf   ibdata1.meta     mysql.ibd.meta      test            undo_002.delta    wenzi-bin.index         xtrabackup_info
ib_buffer_pool  mysql            performance_schema  undo_001.delta  undo_002.meta     xtrabackup_binlog_info  xtrabackup_logfile
ibdata1.delta   mysql.ibd.delta  sys                 undo_001.meta   wenzi-bin.000008  xtrabackup_checkpoints  xtrabackup_tablespaces

模拟数据库损坏

[root@wenzi ~]# systemctl stop mysqld
[root@wenzi ~]# rm -rf /var/lib/mysql/*
[root@wenzi ~]# rm -f /var/log/mysqld.log

恢复数据

[root@wenzi ~]# xtrabackup --prepare --apply-log-only --target-dir=/data/backups/2023-09-09-21-05-16/
[root@wenzi ~]# xtrabackup --prepare --apply-log-only --target-dir=/data/backups/2023-09-09-21-05-16/ --incremental-dir=/data/backups/cy02/
[root@wenzi ~]# xtrabackup --copy-back --target-dir=/data/backups/2023-09-09-21-05-16/
[root@wenzi ~]# chown -R mysql:mysql /var/lib/mysql*
[root@wenzi ~]# systemctl start mysqld

检查数据

[root@wenzi ~]# mysql -uroot -p'Admin.123'
mysql> select * from test.student;
+------+------------+
| id   | name       |
+------+------------+
|    1 | huangzhong |
|    2 | zhangfei   |
|    3 | guanyu     |
+------+------------+
3 rows in set (0.00 sec)

mysqldump + binlog

mysqldump是MySQL数据库中用于备份和导出数据库的命令行工具。它允许你导出整个数据库或仅导出特定的表。通过使用mysqldump，你可以生成一个SQL脚本文件，该文件可以用于还原已导出的数据库

优势：自动记录日志position位置；可用性，一致性；备份时的锁表机制；

常见用法：

导出所有数据库 mysqldump -uroot -p‘密码’ -A > all.sql

导出单个数据库 mysqldump -uroot -p’密码’ test1 > test1.sql

导出多个数据库 mysqldump -uroot -p’密码’ --databases test1 test2 > 2.sql

导出单张表 mysqldump -uroot -p’密码’ mysql user > mysql.user.sql;

导出多张表 mysqldump -uroot -p’密码’ mysql user db > mysql.userdb.sql;

只导出表结构 mysqldump -uroot -p’密码’ --all-databases --no-data > all.d.sql

--single-transaction 保持数据一致性，仅限innodb引擎

--master-data=1或2 将记录binlog的日志位置与文件名并追加到文件中，2是添加注释

--flush-logs 截断日志。备份之后产生新binlog。

-R 导出存储过程和自定义函数

--triggers 导出触发器，默认启用，可用--skip-triggers禁用

–opt 等同于--add-drop-table, --add-locks, --create-options, --quick, --extended-insert, --lock-tables, --set-charset, --disable-keys 该选项默认开启,可以用--skip-opt禁用

环境

mysql 8.0.19 yum方式安装

此实验需要两套密码，密码一：Admin.123 密码二：aDMIN.123

安装mysql

[root@wenzi ~]# ls
anaconda-ks.cfg  mysql-8.0.19-1.el7.x86_64.rpm-bundle.tar  original-ks.cfg
[root@wenzi ~]# tar -x -f mysql-8.0.19-1.el7.x86_64.rpm-bundle.tar && yum -y remove mariadb-libs.x86_64 1:5.5.68-1.el7 && yum -y install libaio net-tools && rpm -ivh mysql-community-common-8.0.19-1.el7.x86_64.rpm && rpm -ivh mysql-community-libs-8.0.19-1.el7.x86_64.rpm && rpm -ivh mysql-community-client-8.0.19-1.el7.x86_64.rpm && rpm -ivh mysql-community-server-8.0.19-1.el7.x86_64.rpm && rpm -ivh mysql-community-libs-compat-8.0.19-1.el7.x86_64.rpm  && echo "log_bin" >> /etc/my.cnf && systemctl enable --now mysqld && grep "password" /var/log/mysqld.log


[root@wenzi ~]# mysql -uroot -p'an+1PuHi6g92'
mysql> alter user 'root'@'localhost' identified by 'Admin.123';
Query OK, 0 rows affected (0.00 sec)
mysql> quit
[root@wenzi ~]# mysql -uroot -p'Admin.123'
mysql> create database one;
Query OK, 1 row affected (0.00 sec)

mysql> use one;
Database changed
mysql> create table emp (id int,name varchar(20));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into emp values (1,'a');
Query OK, 1 row affected (0.01 sec)

mysql> select * from one.emp;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
+------+------+
1 row in set (0.00 sec)

mysql> quit
Bye

[root@wenzi ~]# mkdir -p  /data/backups

备份数据

mysqldump语句会切割一次二进制日志，产生了wenzi-bin.000003

[root@wenzi ~]# mysqldump -h localhost -uroot -p'Admin.123' -A --single-transaction --master-data=2 --flush-logs  > /data/backups/`date +'%F-%H-%M-%S'`_all.sql
[root@wenzi ~]# ls /data/backups/
2023-09-10-01-46-50_all.sql

查看备份详细

[root@wenzi ~]# vim /data/backups/2023-09-10-03-15-51_all.sql

在此次备份时产生的新日志名称为wenzi-bin.000003，新日志中POS155之前是已经备份过的，从155往后是未备份过的内容

[root@wenzi mysql]# mysqlbinlog -v wenzi-bin.000003
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#230910 4:44:19 server id 1 end_log_pos 124 CRC32 0x1fa24712 Start: binlog v 4, server v 8.0.19 created 230910 4:44:19
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
I9n8ZA8BAAAAeAAAAHwAAAABAAQAOC4wLjE5AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEwANAAgAAAAABAAEAAAAYAAEGggAAAAICAgCAAAACgoKKioAEjQA
CgESR6If
'/*!*/;
# at 124 从at155开始往后都是新产生的变化，未备份过的
#230910 4:44:19 server id 1 end_log_pos 155 CRC32 0xf4f29efe Previous-GTIDs
# [empty]
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

在备份时锁表，可读不可写

备份后数据变动

mysql> insert into one.emp values (2,'b');
Query OK, 1 row affected (0.00 sec)

mysql> select * from one.emp;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
+------+------+
2 rows in set (0.00 sec)

备份二进制日志

[root@wenzi ~]# mkdir -p /data/backups/binlog/ && cp /var/lib/mysql/*bin* /data/backups/binlog/
[root@wenzi ~]# ls /data/backups/binlog/
wenzi-bin.000001  wenzi-bin.000002  wenzi-bin.000003  wenzi-bin.index

此时查看二进制日志，变动的数据存在于 binlog.000003 中

[root@wenzi ~]# mysqlbinlog -v /data/backups/binlog/wenzi-bin.000003

...

# at 361
#230910 3:31:30 server id 1 end_log_pos 403 CRC32 0x48bd867c Write_rows: table id 174 flags: STMT_END_F

BINLOG '
Esj8ZBMBAAAAOAAAAGkBAAAAAK4AAAAAAAEAA29uZQADZW1wAAIDDwJQAAMBAQACA/z/ANFoIYk=
Esj8ZB4BAAAAKgAAAJMBAAAAAK4AAAAAAAEAAgAC/wACAAAAAWJ8hr1I
'/*!*/;
### INSERT INTO `one`.`emp`
### SET
### @1=2
### @2='b'

...

模拟数据损坏

[root@wenzi ~]# systemctl stop mysqld && rm -rf /var/lib/mysql/* && rm -f /var/log/mysqld.log

mysql恢复数据

启动数据库，使用默认密码登录mysql，将密码修改为准备的第二套密码 aDMIN.123

[root@wenzi ~]# systemctl start mysqld
[root@wenzi ~]# grep "password" /var/log/mysqld.log
2023-09-09T18:27:22.298328Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: !5GHJT*dlh5e
#修改为预先准备的第二个密码
[root@wenzi ~]# mysql -uroot -p'!5GHJT*dlh5e'
mysql> alter user 'root'@'localhost' identified by 'aDMIN.123';
Query OK, 0 rows affected (0.00 sec)

将备份数据导入数据库，刷新权限，此时使用的密码为 aDMIN.123

[root@wenzi ~]# mysql -uroot -p'aDMIN.123' < /data/backups/2023-09-10-01-46-50_all.sql
[root@wenzi ~]# mysql -uroot -p'aDMIN.123' -e 'flush privileges'

登录数据库，发现密码 aDMIN.123 失效，因为原先的数据库已被恢复，应使用原密码 Admin.123。同时发现数据只恢复了备份前的数据，备份后的数据变动没有恢复成功

[root@wenzi ~]# mysql -uroot -p'aDMIN.123'
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

[root@wenzi ~]# mysql -uroot -p'Admin.123'
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| one                |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> select * from one.emp;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
+------+------+
1 row in set (0.00 sec)

二进制日志恢复数据

二进制日志恢复的起始即 wenzi-bin.000003的POS155。

若后续还有新产生的日志 wenzi-bin.000004、wenzi-bin.000005......，应在全写入在命令中

mysqlbinlog /data/backups/binlog/wenzi-bin.000003 /data/backups/binlog/wenzi-bin.000004 /data/backups/binlog/wenzi-bin.000005 --start-position=155 | mysql -uroot -p'Admin.123'

[root@wenzi ~]# mysqlbinlog /data/backups/binlog/wenzi-bin.000003 --start-position=155 | mysql -uroot -p'Admin.123'
[root@wenzi ~]# mysql -uroot -p'Admin.123'
mysql> select * from one.emp;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
+------+------+
2 rows in set (0.00 sec)

备份后产生的数据变化已恢复

记录二（2024.01）

冷备及还原

将CentOS8（192.168.28.30）中yum安装的mysql8.0.26的所有数据备份至Rocky8（192.168.28.40）上，并还原

192.168.28.30
登录数据库，查看数据
[root@wenzi ~]$mysql -uroot -pAdmin.
mysql> use cs;
mysql> select * from student;
+----+-------+
| id | name  |
+----+-------+
|  1 | zhao  |
|  2 | qian  |
|  3 | sun   |
|  4 | li    |
|  5 | zhou  |
|  6 | wu    |
|  7 | zheng |
|  8 | wang  |
+----+-------+
8 rows in set (0.00 sec)

停止mysqld，备份数据及配置文件至192.168.28.40
[root@wenzi ~]$systemctl stop mysqld
[root@wenzi ~]$rsync -a /var/lib/mysql root@192.168.28.40:/data/
[root@wenzi ~]$rsync -a /etc/{my.cnf,my.cnf.d} root@192.168.28.40:/data/etc/

-------------------------------------------------------------------------------------------

192.168.28.40
yum安装mysql，不启动
[root@wenzi ~]$yum -y install mysql-server

将备份的数据复制到指定位置
[root@wenzi ~]#rsync -a /data/etc/* /etc
[root@wenzi ~]#rsync -a /data/mysql/* /var/lib/mysql/

启动mysqld，登录数据库验证
[root@wenzi ~]#systemctl enable --now mysqld
[root@wenzi ~]#mysql -uroot -pAdmin.
mysql> select * from cs.student;
+----+-------+
| id | name  |
+----+-------+
|  1 | zhao  |
|  2 | qian  |
|  3 | sun   |
|  4 | li    |
|  5 | zhou  |
|  6 | wu    |
|  7 | zheng |
|  8 | wang  |
+----+-------+
8 rows in set (0.01 sec)

mysqldump实现分库备份，定时执行

CentOS8 yum安装 mysql8.0.26

其中mysql：[Warning]是警告用户使用了明文密码；此条告警是个错误输出。information_schema表、performance_schema表是虚拟表，不用备份。

[root@wenzi ~]$vim backup_db.sh
#!/bin/bash
#********************************************************************
#FileName:     backup_db.sh
#Version:      1.0
#Date:         2024-01-21
#Author:       wenzi
#Description： 分库备份
#********************************************************************
time=`date +%F_%H-%M-%S`
bakdir=/backup
passwd=Admin.

[ -d ${bakdir} ] || mkdir ${bakdir}

for i in `mysql -uroot -p${passwd} -e 'show databases;' 2>/dev/null | grep -Ev "^Database|.*schema$"`;do
    mysqldump -uroot -p${passwd} -B ${i} -F -E -R --triggers --single-transaction --master-data=1 --flush-privileges --default-character-set=utf8 --hex-blob > ${bakdir}/${i}_${time}.sql
    if [ $? -eq 0 ];then
        echo "数据库$i备份完毕!"
    else
        echo "备份出错!"
        exit 1
    fi
done

设置定时任务
[root@wenzi ~]$crontab -e
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
30 2 * * * /root/backup_mysql.sh
修改时间
[root@wenzi ~]$date -s '2024-01-21 02:29:30'
观察定时任务日志
[root@wenzi ~]$tail /var/log/cron
Jan 21 02:30:25 wenzi CROND[29078]: (root) CMD (/root/backup_mysql.sh)

mysqldump实现完全备份及还原

将CentOS8（192.168.28.30）中yum安装的mysql8.0.26的所有数据备份至Rocky8（192.168.28.40）上，并还原

192.168.28.30
[root@wenzi ~]$mysql -uroot -pAdmin. -e 'select * from cs.student;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-------+
| id | name  |
+----+-------+
|  1 | zhao  |
|  2 | qian  |
|  3 | sun   |
|  4 | li    |
|  5 | zhou  |
|  6 | wu    |
|  7 | zheng |
|  8 | wang  |
+----+-------+

[root@wenzi ~]$mysqldump -uroot -pAdmin. -A -F --triggers --single-transaction --master-data=2 > all_`date +%F`.sql
[root@wenzi ~]$ls
all_2024-01-21.sql
[root@wenzi ~]$rsync -a all_2024-01-21.sql root@192.168.28.40:/root/

-------------------------------------------------------------------------------------------

192.168.28.40
[root@wenzi ~]#mysql
mysql> set sql_log_bin=0;
mysql> source all_2024-01-21.sql;
mysql> select * from cs.student;
+----+-------+
| id | name  |
+----+-------+
|  1 | zhao  |
|  2 | qian  |
|  3 | sun   |
|  4 | li    |
|  5 | zhou  |
|  6 | wu    |
|  7 | zheng |
|  8 | wang  |
+----+-------+
8 rows in set (0.00 sec)

mysql> set sql_log_bin=1;

mysqldump实现完全备份+通过binlog还原

1、记录二进制日志POS位置，并完全备份
[root@wenzi ~]$mysql -uroot -pAdmin.
mysql> select * from cs.student;
+----+-------+
| id | name  |
+----+-------+
|  1 | zhao  |
|  2 | qian  |
|  3 | sun   |
|  4 | li    |
|  5 | zhou  |
|  6 | wu    |
|  7 | zheng |
|  8 | wang  |
+----+-------+
8 rows in set (0.00 sec)

mysql> show master logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |      3369 | No        |
+---------------+-----------+-----------+


[root@wenzi ~]$mysqldump -uroot -pAdmin. -A -E -R -F --triggers --single-transaction --master-data=2 --flush-privileges --default-character-set=utf8 --hex-blob > /data/all.sql
[root@wenzi ~]$ls /data/
all.sql

2、修改数据库数据
mysql> insert into student(name) values ('ABCD');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+-------+
| id | name  |
+----+-------+
|  1 | zhao  |
|  2 | qian  |
|  3 | sun   |
|  4 | li    |
|  5 | zhou  |
|  6 | wu    |
|  7 | zheng |
|  8 | wang  |
|  9 | ABCD  |
+----+-------+
9 rows in set (0.00 sec)

3、备份二进制日志
[root@wenzi ~]$mysqlbinlog /var/lib/mysql/binlog.000001 /var/lib/mysql/binlog.000002 --start-position=3369 > /data/inc.sql

4、模拟数据库损坏
[root@wenzi ~]$systemctl stop mysqld
[root@wenzi ~]$rm -rf /var/lib/mysql/*

5、先还原完全备份，再还原二进制备份
[root@wenzi ~]$systemctl start mysqld
[root@wenzi ~]$mysql
mysql> set sql_log_bin=0;
mysql> source /data/all.sql
mysql> source /data/inc.sql
mysql> set sql_log_bin=1;
mysql> use cs;
Database changed
mysql> select * from student;
+----+-------+
| id | name  |
+----+-------+
|  1 | zhao  |
|  2 | qian  |
|  3 | sun   |
|  4 | li    |
|  5 | zhou  |
|  6 | wu    |
|  7 | zheng |
|  8 | wang  |
|  9 | ABCD  |
+----+-------+