场景需求

每一款数据库都有自己逻辑备份工具，比如Oracle的expdp/impdp，Mysql的mysqldump，KingBase的sys_dump/sys_restore等，如何给导出日志的每一行加上具体时间戳，实现如下的效果，本文将介绍2种方法。

以Oracle为例，原日志导出信息如下：

Export: Release 11.2.0.4.0 - Production on Tue Oct 29 09:54:23 2024

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_26":  "/******** AS SYSDBA" directory=BZBF_PROD dumpfile=full_bak.dmp schemas=V7_PRO 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 288 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "V7_PRO"."TT50_PART_BYDAY_GVBGT":"TT50_PART_BYDAY_GVBGT_P240818"  4.747 MB     177 rows
. . exported "V7_PRO"."TT50_PART_BYDAY_GVBGT":"TT50_PART_BYDAY_GVBGT_P240819"  5.309 MB     198 rows
. . exported "V7_PRO"."TT50_PART_BYDAY_GVBGT":"TT50_PART_BYDAY_GVBGT_P240820"  5.336 MB     199 rows
. . exported "V7_PRO"."TT50_PART_BYDAY_GVBGT":"TT50_PART_BYDAY_GVBGT_P240821"  5.255 MB     196 rows
. . exported "V7_PRO"."TT50_PART_BYDAY_GVBGT":"TT50_PART_BYDAY_GVBGT_P240822"  5.603 MB     209 rows
. . exported "V7_PRO"."TT50_PART_BYDAY_GVBGT":"TT50_PART_BYDAY_GVBGT_P240823"  5.443 MB     203 rows
. . exported "V7_PRO"."TT50_PART_BYDAY_GVBGT":"TT50_PART_BYDAY_GVBGT_P240824"  5.710 MB     213 rows
..........

期望实现效果如下：

2024-10-29 09:54:23 
2024-10-29 09:54:23 Export: Release 11.2.0.4.0 - Production on Tue Oct 29 09:54:23 2024
2024-10-29 09:54:23 
2024-10-29 09:54:23 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
2024-10-29 09:54:23 
2024-10-29 09:54:23 Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
2024-10-29 09:54:23 With the Partitioning, OLAP, Data Mining and Real Application Testing options
2024-10-29 09:54:26 Starting "SYS"."SYS_EXPORT_SCHEMA_26":  "/******** AS SYSDBA" directory=BZBF_PROD dumpfile=full_bak.dmp schemas=V7_PRO
2024-10-29 09:54:26 Estimate in progress using BLOCKS method...
2024-10-29 09:54:27 Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
2024-10-29 09:54:29 Total estimation using BLOCKS method: 288 MB
2024-10-29 09:54:29 Processing object type SCHEMA_EXPORT/USER
2024-10-29 09:54:29 Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
2024-10-29 09:54:29 Processing object type SCHEMA_EXPORT/ROLE_GRANT
2024-10-29 09:54:29 Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
2024-10-29 09:54:29 Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
2024-10-29 09:54:34 Processing object type SCHEMA_EXPORT/TABLE/TABLE
2024-10-29 09:54:36 Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
2024-10-29 09:54:36 Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
2024-10-29 09:54:37 Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
2024-10-29 09:54:37 Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
2024-10-29 09:54:38 Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
2024-10-29 09:54:39 . . exported "V7_PRO"."TT50_PART_BYDAY_GVBGT":"TT50_PART_BYDAY_GVBGT_P240818"  4.747 MB     177 rows
2024-10-29 09:54:39 . . exported "V7_PRO"."TT50_PART_BYDAY_GVBGT":"TT50_PART_BYDAY_GVBGT_P240819"  5.309 MB     198 rows
2024-10-29 09:54:39 . . exported "V7_PRO"."TT50_PART_BYDAY_GVBGT":"TT50_PART_BYDAY_GVBGT_P240820"  5.336 MB     199 rows
2024-10-29 09:54:40 . . exported "V7_PRO"."TT50_PART_BYDAY_GVBGT":"TT50_PART_BYDAY_GVBGT_P240821"  5.255 MB     196 rows
2024-10-29 09:54:40 . . exported "V7_PRO"."TT50_PART_BYDAY_GVBGT":"TT50_PART_BYDAY_GVBGT_P240822"  5.603 MB     209 rows
2024-10-29 09:54:40 . . exported "V7_PRO"."TT50_PART_BYDAY_GVBGT":"TT50_PART_BYDAY_GVBGT_P240823"  5.443 MB     203 rows
2024-10-29 09:54:40 . . exported "V7_PRO"."TT50_PART_BYDAY_GVBGT":"TT50_PART_BYDAY_GVBGT_P240824"  5.710 MB     213 rows
.........  

实践步骤

方法一 基于终端工具

已SecureCRT为例，利用CRT 工具本身的日志记录功能，只需设置即可，实际上与数据库本身无关，只要操作都可以记录。

执行导出测试

[oracle@zsdb first]$ expdp \'/ as sysdba\' directory=BZBF_PROD dumpfile=full_bak2024.dmp schemas=V7_PRO logfile=expdp20241029.log 

## 找到对应的本地日志文件，即可查看带有时间戳的日志
注：导出命令不能使用nohup 放后台执行。
[10:14:31][oracle@zsdb first]$ expdp \'/ as sysdba\' directory=BZBF_PROD dumpfile=full_bak2024.dmp schemas=V7_PRO logfile=expdp20241029.log  
[10:14:31]
[10:14:31]Export: Release 11.2.0.4.0 - Production on Tue Oct 29 10:10:03 2024
[10:14:31]
[10:14:31]Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
[10:14:31]
[10:14:31]Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
[10:14:31]With the Partitioning, OLAP, Data Mining and Real Application Testing options
[10:14:33]Starting "SYS"."SYS_EXPORT_SCHEMA_26":  "/******** AS SYSDBA" directory=BZBF_PROD dumpfile=full_bak2024.dmp schemas=V7_PRO logfile=expdp20241029.log 
[10:14:33]Estimate in progress using BLOCKS method...
[10:14:34]Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
[10:14:35]Total estimation using BLOCKS method: 288 MB
[10:14:35]Processing object type SCHEMA_EXPORT/USER
[10:14:35]Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
[10:14:35]Processing object type SCHEMA_EXPORT/ROLE_GRANT
[10:14:35]Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
[10:14:35]Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
[10:14:39]Processing object type SCHEMA_EXPORT/TABLE/TABLE
[10:14:41]Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
[10:14:41]Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
[10:14:42]Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
[10:14:43]Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
[10:14:43]Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
[10:14:44]. . exported "V7_PRO"."TT50_PART_BYDAY_GVBGT":"TT50_PART_BYDAY_GVBGT_P240818"  4.747 MB     177 rows
[10:14:44]. . exported "V7_PRO"."TT50_PART_BYDAY_GVBGT":"TT50_PART_BYDAY_GVBGT_P240819"  5.309 MB     198 rows
[10:14:44]. . exported "V7_PRO"."TT50_PART_BYDAY_GVBGT":"TT50_PART_BYDAY_GVBGT_P240820"  5.336 MB     199 rows
[10:14:44]. . exported "V7_PRO"."TT50_PART_BYDAY_GVBGT":"TT50_PART_BYDAY_GVBGT_P240821"  5.255 MB     196 rows
[10:14:45]. . exported "V7_PRO"."TT50_PART_BYDAY_GVBGT":"TT50_PART_BYDAY_GVBGT_P240822"  5.603 MB     209 rows

方法二 基于shell命令实现

运维人员大多习惯导出备份命令放后台执行，一是防止终端出问题，二是主机可能设置的有超时策略。
通过使用nohup + tee + while read line + 通道符实现。

[oracle@zsdb first]$ nohup expdp \'/ as sysdba\' directory=BZBF_PROD dumpfile=full_bak.dmp schemas=V7_PRO | tee -a log.txt | while read line; do echo "$(date +'%Y-%m-%d %H:%M:%S') $line"; done >> log_time.txt &
[1] 17026
[oracle@zsdb first]$ nohup: 忽略输入重定向错误到标准输出端
[oracle@zsdb first]$ ll
总用量 201380
-rw-r--r-- 1 oracle oinstall     66002 10月 29 10:25 export.log
-rw-r----- 1 oracle oinstall 205991936 10月 29 10:25 full_bak.dmp
-rw-r--r-- 1 oracle oinstall     79532 10月 29 10:25 log_time.txt
-rw-r--r-- 1 oracle oinstall     65995 10月 29 10:25 log.txt
[oracle@zsdb first]$ more log_time.txt
2024-10-29 10:25:04 
2024-10-29 10:25:04 Export: Release 11.2.0.4.0 - Production on Tue Oct 29 10:25:04 2024
2024-10-29 10:25:04 
2024-10-29 10:25:04 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
2024-10-29 10:25:04 
2024-10-29 10:25:04 Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
2024-10-29 10:25:04 With the Partitioning, OLAP, Data Mining and Real Application Testing options
2024-10-29 10:25:05 Starting "SYS"."SYS_EXPORT_SCHEMA_26":  "/******** AS SYSDBA" directory=BZBF_PROD dumpfile=full_bak.dmp schemas=V7_PRO
2024-10-29 10:25:05 Estimate in progress using BLOCKS method...
2024-10-29 10:25:06 Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
2024-10-29 10:25:07 Total estimation using BLOCKS method: 288 MB
2024-10-29 10:25:07 Processing object type SCHEMA_EXPORT/USER
2024-10-29 10:25:07 Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
2024-10-29 10:25:07 Processing object type SCHEMA_EXPORT/ROLE_GRANT
2024-10-29 10:25:07 Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
2024-10-29 10:25:07 Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
2024-10-29 10:25:10 Processing object type SCHEMA_EXPORT/TABLE/TABLE
2024-10-29 10:25:12 Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
2024-10-29 10:25:12 Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
2024-10-29 10:25:13 Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
2024-10-29 10:25:14 Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
2024-10-29 10:25:14 Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
2024-10-29 10:25:15 . . exported "V7_PRO"."TT50_PART_BYDAY_GVBGT":"TT50_PART_BYDAY_GVBGT_P240818"  4.747 MB     177 rows
2024-10-29 10:25:15 . . exported "V7_PRO"."TT50_PART_BYDAY_GVBGT":"TT50_PART_BYDAY_GVBGT_P240819"  5.309 MB     198 rows
2024-10-29 10:25:15 . . exported "V7_PRO"."TT50_PART_BYDAY_GVBGT":"TT50_PART_BYDAY_GVBGT_P240820"  5.336 MB     199 rows
2024-10-29 10:25:15 . . exported "V7_PRO"."TT50_PART_BYDAY_GVBGT":"TT50_PART_BYDAY_GVBGT_P240821"  5.255 MB     196 rows

注：Oracle 12C及以上版本可以直接使用expdp的参数LOGTIME = ALL为日志添加时间戳，无需以上操作，更加便捷，多谢群友提示。

总结

通过以上2种方法，可以实现为逻辑导出日志每行添加时间戳的需求，另外，方法一中的基于CRT终端工具记录的方法，场景不仅仅在此，建议运维人员在日常操作时，都设置打开，作用懂的都懂>_<