Linux运维：MySQL备份，物理冷备份,热备,完备+二进制日志,mysqldump备份-A,-B

备份类型

完全备份、增量备份、差异备份

完全备份：整个数据集都备份

增量备份：仅备份最近一次完全备份或增量备份（如果存在增量）以来变化的数据，备份较快，还原复杂。

差异备份：对比前一次备份，备份多出来的或者前一次备份有差异的数据。仅备份最近一次完全备份以来变化的数据或者增长的数据，备份较慢，还原简单。直接还原最新备份。

备份数据

cp,tar等复制归档工具：物理备份工具，适用所有存储引擎；只支持冷备，完全和部分备份

LVM的快照：先加读锁，做快照后解锁，几乎热备；借助文件系统工具进行备份

mysqldump：逻辑备份工具，使用所有存储引擎，对MyISAM存储引擎进行温备；支持完全或部分备份；对innodb存储引擎支持热备，结合binlog的增量备份。

二进制日志文件不应该与数据文件放在同一磁盘冷、温、热备份。

冷备：读、写操作均不可进行，数据库停止服务

温备：读操作可执行，但写操作不可执行

热备：读、写操作均可执行，不影响事务

MyISAM：温备，不支持热备，不支持事务。Innodb：都支持

物理冷备份：备份方式：冷备份，一定要先关闭数据库。

先关闭数据库stop，再把/var/lib/mysql使用scp拷贝到其他主机上，再删库，rm -rf /var/lib/mysql。如果重启start相当于格式化，需要重新过滤密码grep password /var/log/mysqld.log复制密码，登录，但是数据库中都没有自建的数据库了，只有默认的数据库。关闭stop数据库，把新生成的数据删除rm -rf /var/lib/mysql，把拷贝过去的mysql再拷回来，指定属主和属组chown。再登录就可以了。

yum安装mysql这里不再实验演示

本地源：

vim /etc/yum.repo.d/mysql.repo

[mysql57-community]

name=MySQL 5.7 Community Server

baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/

enabled=1

gpgcheck=0

下载社区办

yum install -y mysql-community-server

#Node1主机上启动，主机Node2上安装好先不启动。

systemctl start mysqld

启动后就会有日志文件，通过password过滤密码。登录数据库

grep password /var/log/mysql.log #最后面的就是密码，各种特殊符号都是密码的一部分。

登录mysql

mysql -uroot -p'密码'

写入密码策略，改密码，添加两条。目的是不需要遵守密码复杂度要求

set global validate_password_policy=0;

set global validate_password_length=1;

将密码改成abc123

alter user root@'localhost' identified by 'abc123';

ctrl + D退出。

数据库安装成功！

登录：mysql -uroot -pabc123

退出：ctrl + D

有一个数据库hellodb。有以下表；

主机Node1和主机Node2上测试物理冷备份。把主机1的数据库文件复制到主机2上

删除主机1的数据库文件。

物理冷备份

现有的数据库：hellodb和test。

拷贝到主机2上；

[root@Node1 ~]#:scp -r /var/lib/mysql 192.168.114.20:/data/

加-r是将数据库文件整个打包

破坏数据：

[root@Node1 ~]#:rm -rf /var/lib/mysql

如果再重启数据库。那么就不再是原来的数据库文件了。并且密码也需要重新修改。

systemctl start mysqld

重新设置密码：

set global validate_password_policy=0;

set global validate_password_length=1;

set password = 'abc123';

查看数据库是否存在hellodb和test数据库。

那么我们的数据就不存在了。需要从备份的主机那里重新拷过来

关闭数据库：

[root@Node1 ~]#:systemctl stop mysqld
[root@Node1 ~]#:rm -rf /var/lib/mysql

在主机2上再拷贝到主机1上：

[root@Node2 ~]#:scp -r /data/mysql 192.168.114.10:/var/lib/
The authenticity of host '192.168.114.10 (192.168.114.10)' can't be established.
ECDSA key fingerprint is SHA256:lsBlbsjrUi2EgYzxOda1NX0GiAJiwLXtn76BetsdzNc.
......

修改属主和属组：

[root@Node1 ~]#:ll /var/lib/mysql -d
drwxr-x--x. 7 root root 4096 7月   3 19:11 /var/lib/mysql
[root@Node1 ~]#:
[root@Node1 ~]#:chown -R mysql:mysql /var/lib/mysql

迁移数据库完了先不要启动数据库，复制好文件后再启动

启动systemctl start mysqld

[root@Node1 ~]#:systemctl start mysqld
[root@Node1 ~]#:
[root@Node1 ~]#:mysql -uroot -pabc123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
......

登录进来了。查看数据库，发现是原来的文件的数据库。

mysqldump热备份

不需要关闭数据库：

[root@Node1 ~]#:mysqldump -uroot -pabc123 hellodb > /data/hellodb.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

提示警告是因为密码暴露在了外面，可以：mysql -uroot -p hellodb > /data/hellodb.sql

然后输入密码：(不显示密码)

这样把数据库hellodb备份在了/data/下取名为hellodb.sql文件。

进去把hellodb数据库删除掉。

退出来，在外面操作，由于存在的弊端，这样导出来的只有创建表的记录。没有创建数据库的记录。需要进去创建一个hellodb的数据库。

此时数据库hellodb被我们删除了。备份在/data/下名为hellodb.sql

在外面复制过来，先不创建数据库。测试：

[root@Node1 ~]#:mysql -uroot -pabc123 hellodb < /data/hellodb.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1049 (42000): Unknown database 'hellodb'

由于没有数据库hellodb。导入失败。需要进入数据库。先创建个数据库。

可以查看一下这个文件：过滤create:

grep -i "create" /data/hellodb.sql

发现只有创建表的。那我们进入数据创建一个数据库：

create database hellodb;

[root@Node1 ~]#:mysql -uroot -pabc123
......
mysql> create database hellodb;
Query OK, 1 row affected (0.00 sec)

再次执行导入：指定hellodb数据库。执行成功！

有一个命令不进入数据库就可以进行查询语句：加-e选项

这样数据库就还原回来了。

复刻Node1主机的数据库

那么我们Node2主机上有Node1的mysql文件。我们可以把这个文件复刻为自己的数据库。

[root@Node2 ~]#:ls /data
mysql

Node2上安装好之后，没有启动就没有/var/lib/mysql文件https://tool.lu/crontab/ crontab时间计算器，我们把Node1备份过来的拷贝过来。

[root@Node2 ~]#:chown -R mysql:mysql /var/lib/mysql #给个权限，属主和属组

然后重新启动：systemctl start mysqld

如果有错，就删了重新复制一遍。

[root@Node2 ~]#:mysql -uroot -pabc123
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

停止：删了重新复制

#关闭数据库
[root@Node2 ~]#:systemctl stop mysqld
#删除文件
[root@Node2 ~]#:rm -rf /var/lib/mysql/*                    #忽略这里*/
[root@Node2 ~]#:ls /var/lib/mysql
#重新复制一遍
[root@Node2 ~]#:cp /data/mysql/* /var/lib/mysql/ -r        #忽略这里*/
[root@Node2 ~]#:chown -R mysql:mysql /var/lib/mysql
#启动
[root@Node2 ~]#:systemctl start mysqld
登录成功
[root@Node2 ~]#:mysql -uroot -pabc123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
......

mysql>
mysql> show databases;            #与Node1的数据库一样
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.00 sec)

这样就实现了主机Node2复刻主机Node1的数据。

完备+二进制日志

实验：在一个时间轴上，如果在6点时完全备份了。在6点到8点的时候对数据进行了操作修改，没有备份，怎么达到6点到8点和6点之前的完备合在一起？

现在Node1的数据库中：

开启二进制日志：

[root@Node1 ~]#:vim /etc/my.cnf
......
server-id=10
log-bin=/data/mysql/mysql-bin

创建路径和修改权限并重启：

[root@Node1 ~]#:mkdir /data/mysql -p
[root@Node1 ~]#:chown -R mysql.mysql /data
[root@Node1 ~]#:systemctl restart mysqld

执行完备，相当于6点这个时间点进行完全备份：

mysqldump -A --master-date=2 > /data/all.sql

可以查看一下这个备份文件：有一句话，是二进制从mysql-bin.000001二进制文件，154这个节点开始的。这个就是6点这个时间节点。

#从完全备份中，找到二进制位置

[root@Node1 ~]#:vim /data/all.sql

......

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154;

......

这个时候向数据库中插入几条数据：

这时候的数据是在备份后的数据，也就是备份的文件里面是没有这三条数据的。

进入数据库删除hellodb数据库，别忘了前面6点的时候有个完备/data/all.sql。

这个时候备份，完全备份后的二进制日志，这时候就是从6点的时间点到现在备份成一个新的文件叫/data/inc.sql。这里面有刚刚删除数据库的语句记录。也就是drop记录，能过滤出来。

这里！梳理一下：

我们把hellodb数据库删除了，在删除前6点的时候做了个完备：/data/all.sql

然后插入了三条数据，没有做备份，但是通过6点做的完备的节点开始做了二进制日志：/data/inc.sql

但是在/data/inc.sql中有drop的语句，不能直接把/data/inc.sql导入数据，否则执行了drop语句就又把hellodb数据库删了。我们通过过滤出drop的语句行，将其注释或者删除掉，再导入数据！

过滤并删除，如果数据量很大是不建议打开文件删除，直接sed。

导入备份的文件：

把完备原封不动的导入数据库：

进入数据库并查看是否有hellodb数据库：

可见数据库已回复，但这也只是6点之前的，6点之后插入的三条数据是没有的：查看

select * from hellodb.teachers;

再把6点之后的二进制导入进来：

source /data/inc.sql;

有个报错不影响：

再次查看是否有插入的三条数据了？

select * from hellodb.teachers;

这时三条数据就还原过来了！实现了完全备份+二进制日志恢复数据！

mysqldump备份指定数据库-B选项

现有hellodb数据库：

通过mysqldump指定hellodb数据，备份到/data/hellodb.sql

[root@Node1 ~]#:mysqldump -uroot -pabc123 -B hellodb > /data/hellodb.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

进入数据库把hellodb删除。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> drop database hellodb;
Query OK, 7 rows affected (0.03 sec)

查看hellodb.sql文件中是否有创建库的语句。并导入数据库。与前面相比不用手动创建数据库了，直接导入即可。

这个文件中是有创建库的语句的。可以不用再创建库了，直接导入数据库中就有了hellodb数据库。

mysqldump备份指定所有数据库-A选项

现在有两个自创的数据库文件

先做个备份：备份所有的数据库：-A

[root@Node1 ~]#:mysqldump -uroot -pabc123 -A > /data/alldb.sql
[root@Node1 ~]#:grep -i "create" /data/alldb.sql
......
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `alldb` /*!40100 DEFAULT CHARACTER SET latin1 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `hellodb` /*!40100 DEFAULT CHARACTER SET utf8 */;
CREATE TABLE `classes` (
CREATE TABLE `coc` (
CREATE TABLE `courses` (
CREATE TABLE `scores` (
CREATE TABLE `students` (
CREATE TABLE `teachers` (
CREATE TABLE `toc` (

-A选项是所有的数据库，记录了创建所有的数据库的语句。

然后将数据库目录删除掉：关闭，重启：

[root@Node1 ~]#:rm -rf /var/lib/mysql
[root@Node1 ~]#:systemctl stop mysqld
[root@Node1 ~]#:systemctl start mysqld

这时登录数据库要重新查找一下密码

[root@Node1 ~]#:grep password /var/log/mysqld.log 
......
2024-07-06T06:30:26.108061Z 1 [Note] A temporary password is generated for root@localhost: 2O-9G3_yhf<>
[root@Node1 ~]#:mysql -uroot -p'2O-9G3_yhf<>'
......
mysql>

登录进来重新设置一下密码：先取消密码策略的复杂度

在set设置密码：

这个时候是没有我们的数据hellodb和alldb的。

需要把这个alldb.sql导入进来查看数据库：

[root@Node1 ~]#:mysql -uroot -pabc123 < /data/alldb.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@Node1 ~]#:mysql -uroot -pabc123 -e "show databases"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| alldb              |
| hellodb            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+