MySQL备份与日志管理及iptables防护实践-CSDN博客

本文链接：https://blog.csdn.net/weixin_54225453/article/details/125819085

本文详细介绍了MySQL的重做日志(redo log)和二进制日志(binlog)在数据库安全性中的作用，以及如何仅使用xtrabackup工具进行全量和增量备份。同时，演示了如何开启并检查binlog日志状态，并通过iptables设置规则防止nmap扫描，提升服务器安全。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一.如果只有redo log或者只用bin log可以吗?

重做日志：redo log—物理日志
二进制日志：binlog—逻辑日志
binlog 日志只用于归档，只依靠 binlog 是没有 crash-safe 能力的。
只有 redo log 也不行，因为 redo log 是 InnoDB特有的，且日志上的记录落盘后会被覆盖掉。

二. xtrabackup实现mysql的增量备份和全量备份

1.增量备份

xtrabackup --user=bkpuser --password=123456 --backup --target-dir=/data/backups/mysql
 
#### 会看到输出
2207016 15:55:37 Executing UNLOCK TABLES
2207016 15:55:37 All tables unlocked
2207016 15:55:37 [00] Copying ib_buffer_pool to /data/backups/mysql/ib_buffer_pool
2207016 15:55:37 [00]        ...done
2207016 15:55:37 Backup created in directory '/data/backups/mysql/'
2207016 15:55:37 [00] Writing /data/backups/mysql/backup-my.cnf
2207016 15:55:37 [00]        ...done
2207016 15:55:37 [00] Writing /data/backups/mysql/xtrabackup_info
2207016 15:55:37 [00]        ...done
xtrabackup: Transaction log of lsn (837940114) to (837940123) was copied.
2207016 15:55:37 completed OK!

2.增量备份

创建备份
xtrabackup --user=bkpuser --password=123456 --backup --target-dir=/data/backups/base

xtrabackup --user=bkpuser --password=123456 --backup --target-dir=/data/backups/inc1 --incremental-basedir=/data/backups/base

查看备份类型确认是增量备份了

root@longing:/data/backups/inc1# cat xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 837943393
to_lsn = 837943393
last_lsn = 837943402
compact = 0
recover_binlog_info = 0
flushed_lsn = 837943402

三.binlog操作

1.开启binlog日志

[root@mysql1 ~]# vim /etc/my.cnf
[mysqld]
server_id = 11
log-bin
... ...

[root@mysql1 ~]# systemctl restart mysqld

（kali是：/etc/mysql/mariadb.conf.d/50-server.cnf）

重启mysqld服务

[root@mysql1 ~]# systemctl restart mysqld
[root@mysql1 ~]# ls /mybinlog/
mylog.000001  mylog.000002  mylog.index
mysql> show master status;
+--------------+----------+--------------+------------------+-------------------+
| File         | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------+----------+--------------+------------------+-------------------+
| mylog.000002 |      154 |              |                  |                   |
+--------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

查看binlog日志是否开启

mysql> show variables like 'log_%';
+---------------------------------+---------------------+
| Variable_name | Value |
+---------------------------------+---------------------+
| log_bin | ON |
| log_bin_trust_function_creators | OFF |
| log_bin_trust_routine_creators | OFF |
| log_error | /var/log/mysqld.log |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_warnings | 1 |
+---------------------------------+---------------------+
9 rows in set (0.00 sec)

flush指令(清除或者重新加载缓存)

mysql> flush logs;
Query OK, 0 rows affected (0.10 sec)

mysql> show master status;
+--------------+----------+--------------+------------------+-------------------+
| File         | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------+----------+--------------+------------------+-------------------+
| mylog.000003 |      154 |              |                  |                   |
+--------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
[root@mysql1 ~]# ls /mybinlog/
mylog.000001  mylog.000002  mylog.000003  mylog.index

四.iptables防止nmap扫描

  #iptables -F

  #iptables -A INPUT -p tcp --tcp-flags ALL FIN,URG,PSH -j Drop

  #iptables -A INPUT -p tcp --tcp-flags SYN,RST SYN,RST -j Drop

  #iptables -A INPUT -p tcp --tcp-flags SYN,FIN SYN,FIN -j Drop

  #iptables -A INPUT -p tcp --tcp-flags SYN,SYN --dport 80 -j Drop