{"id":145,"date":"2022-05-13T17:21:04","date_gmt":"2022-05-13T09:21:04","guid":{"rendered":"https:\/\/frogvps.com\/?p=145"},"modified":"2022-05-18T12:34:28","modified_gmt":"2022-05-18T04:34:28","slug":"rsyslog%e6%95%99%e7%a8%8b%ef%bc%9a%e5%88%a9%e7%94%a8-mysql-%e5%ad%98%e5%82%a8%e6%97%a5%e5%bf%97%e4%bf%a1%e6%81%af","status":"publish","type":"post","link":"https:\/\/frogvps.com\/?p=145","title":{"rendered":"rsyslog\u6559\u7a0b\uff1a\u5229\u7528 MySQL \u5b58\u50a8\u65e5\u5fd7\u4fe1\u606f"},"content":{"rendered":"\n

\u672c\u6587\u5c06\u4ecb\u7ecd\u5982\u4f55\u628a\u672c\u5730\u670d\u52a1\u5668\u7684\u65e5\u5fd7\uff0c\u53d1\u9001\u5230\u8fdc\u7a0b\u4e3b\u673a\u7684MYSQL\u6570\u636e\u5e93\u4e2d\u3002<\/p>\n\n\n\n

\u73af\u5883\u51c6\u5907<\/h2>\n\n\n\n

\u4e24\u53f0\u4e3b\u673a<\/p>\n\n\n\n

\u4e00\u53f0rsyslog\u65e5\u5fd7\u670d\u52a1\u5668\uff0c IP\uff1a154.17.6.123<\/p>\n\n\n\n

\u4e00\u53f0MYSQL\u6570\u636e\u5e93\u670d\u52a1\u5668\uff0cIP\uff1a154.3.32.85<\/p>\n\n\n\n

\u7cfb\u7edf\uff1aUbuntu 20.04<\/p>\n\n\n\n

\u5b9e\u73b0\u6b65\u9aa4<\/h2>\n\n\n\n

\u65e5\u5fd7\u670d\u52a1\u5668\uff1a<\/strong><\/p>\n\n\n\n

\u5728rsyslog\u65e5\u5fd7\u670d\u52a1\u5668\u4e0a\u5b89\u88c5\u8fde\u63a5mysql\u6a21\u5757\u7684\u7a0b\u5e8f\u5305<\/p>\n\n\n\n

apt -y install rsyslog-mysql<\/code><\/pre>\n\n\n\n
\u5b89\u88c5\u7684\u65f6\u5019\u4f1a\u5f39\u51fa\u8fd9\u4e2a\u754c\u9762\uff0c\u56e0\u4e3a\u6211\u4eec\u7684MYSQL\u5728\u8fdc\u7a0b\u4e3b\u673a\u9700\u8981\u81ea\u5df1\u914d\u7f6e\uff0c\u6240\u4ee5\u8fd9\u91cc\u9009\u62e9NO\u3002<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
\u5b58\u653e\u65e5\u5fd7\u9700\u8981\u521b\u5efa\u6570\u636e\u5e93\u548c\u8868\u683c\uff0c\u7a0b\u5e8f\u81ea\u5e26\u4e86SQL\u8bed\u53e5\uff0c\u53ea\u9700\u8981\u627e\u5230\u8fd9\u4e2a\u6587\u4ef6\u5c31\u53ef\u4ee5<\/p>\n\n\n\n
#\u5982\u679c\u6ca1\u6709apt-file\u547d\u4ee4\u6267\u884c\u4ee5\u4e0b\u64cd\u4f5c
apt -y install apt-file
#\u67e5\u770brsyslog-mysql\u7a0b\u5e8f\u5305\u91cc\u7684\u6587\u4ef6
apt-file list rsyslog-mysql
#\u627e\u5230\u81ea\u5e26\u7684SQL\u8bed\u53e5
cat \/usr\/share\/dbconfig-common\/data\/rsyslog-mysql\/install\/mysql
#\u53ef\u4ee5\u770b\u5230\u8bed\u53e5\u5982\u4e0b
CREATE TABLE IF NOT EXISTS SystemEvents
(
        ID int unsigned not null auto_increment primary key,
        CustomerID bigint,
        ReceivedAt datetime NULL,
        DeviceReportedTime datetime NULL,
        Facility smallint NULL,
        Priority smallint NULL,
        FromHost varchar(60) NULL,
        Message text,
        NTSeverity int NULL,
        Importance int NULL,
        EventSource varchar(60),
        EventUser varchar(60) NULL,
        EventCategory int NULL,
        EventID int NULL,
        EventBinaryData text NULL,
        MaxAvailable int NULL,
        CurrUsage int NULL,
        MinUsage int NULL,
        MaxUsage int NULL,
        InfoUnitID int NULL ,
        SysLogTag varchar(60),
        EventLogType varchar(60),
        GenericFileName VarChar(60),
        SystemID int NULL
);
\u200b
CREATE TABLE IF NOT EXISTS SystemEventsProperties
(
        ID int unsigned not null auto_increment primary key,
        SystemEventID int NULL ,
        ParamName varchar(255) NULL ,
        ParamValue text NULL
);<\/code><\/pre>\n\n\n\n
\u6570\u636e\u5e93\u670d\u52a1\u5668\uff1a<\/strong><\/p>\n\n\n\n
\u7531\u4e8e\u8981\u5b58\u653e\u5728\u8fdc\u7a0b\u7684MYSQL\u6570\u636e\u5e93\uff0c\u6240\u4ee5\u6211\u4eec\u5728\u6570\u636e\u5e93\u670d\u52a1\u5668\u521b\u5efa\u4e00\u4e2a\u6587\u4ef6\uff0c\u7136\u540e\u628aSQL\u8bed\u53e5\u590d\u5236\u8fdb\u53bb\u6267\u884c\u3002<\/p>\n\n\n\n
\u9996\u5148\u5728\u8fdc\u7a0b\u4e3b\u673a\u5b89\u88c5\u6570\u636e\u5e93<\/p>\n\n\n\n
apt -y install mysql-server<\/code><\/pre>\n\n\n\n
\u7136\u540e\u6267\u884c\u4ee5\u4e0b\u547d\u4ee4<\/p>\n\n\n\n
vim rsyslog.sql<\/code><\/pre>\n\n\n\n
\u628a\u521a\u624d\u770b\u5230\u7684SQL\u8bed\u53e5\u590d\u5236\u8fdb\u53bb\uff0c\u6211\u53d1\u73b0\u5b83\u6ca1\u6709\u521b\u5efa\u6570\u636e\u5e93\u7684\u547d\u4ee4\uff0c\u6240\u4ee5\u6211\u81ea\u5df1\u6dfb\u52a0\u4e86\u4e24\u53e5\u547d\u4ee4\u3002<\/p>\n\n\n\n
CREATE DATABASE Syslog;
USE Syslog;
CREATE TABLE IF NOT EXISTS SystemEvents
(
        ID int unsigned not null auto_increment primary key,
        CustomerID bigint,
        ReceivedAt datetime NULL,
        DeviceReportedTime datetime NULL,
        Facility smallint NULL,
        Priority smallint NULL,
        FromHost varchar(60) NULL,
        Message text,
        NTSeverity int NULL,
        Importance int NULL,
        EventSource varchar(60),
        EventUser varchar(60) NULL,
        EventCategory int NULL,
        EventID int NULL,
        EventBinaryData text NULL,
        MaxAvailable int NULL,
        CurrUsage int NULL,
        MinUsage int NULL,
        MaxUsage int NULL,
        InfoUnitID int NULL ,
        SysLogTag varchar(60),
        EventLogType varchar(60),
        GenericFileName VarChar(60),
        SystemID int NULL
);
\u200b
CREATE TABLE IF NOT EXISTS SystemEventsProperties
(
        ID int unsigned not null auto_increment primary key,
        SystemEventID int NULL ,
        ParamName varchar(255) NULL ,
        ParamValue text NULL
);<\/code><\/pre>\n\n\n\n
:wq \u4fee\u6539\u5e76\u4fdd\u5b58\u9000\u51fa\u4ee5\u540e\uff0c\u6267\u884c\u4ee5\u4e0b\u547d\u4ee4<\/p>\n\n\n\n
mysql < rsyslog.sql<\/code><\/pre>\n\n\n\n
\u6267\u884c\u5b8c\u4ee5\u540e\u5b58\u653e\u65e5\u5fd7\u7684\u6570\u636e\u5e93\u5c31\u81ea\u52a8\u521b\u5efa\u5b8c\u6210\u4e86<\/p>\n\n\n\n
\u6211\u4eec\u73b0\u5728\u53ef\u4ee5\u767b\u5f55\u6570\u636e\u5e93\uff08\u521a\u5b89\u88c5\u662f\u6ca1\u6709root\u5bc6\u7801\u7684\uff09<\/p>\n\n\n\n
mysql -uroot<\/code><\/pre>\n\n\n\n
\u521b\u5efa\u8fdc\u7a0b\u767b\u5f55\u6570\u636e\u5e93\u7684\u7528\u6237\uff0c\u8fd9\u91cc\u7684IP\u5730\u5740\u662f\u65e5\u5fd7\u670d\u52a1\u5668\u7684\u5730\u5740<\/p>\n\n\n\n
CREATE USER rsyslog@154.17.6.123 IDENTIFIED BY \"123456\";<\/code><\/pre>\n\n\n\n
\u628a\u7ba1\u7406\u65e5\u5fd7\u6570\u636e\u5e93\u7684\u6743\u9650\u6388\u4e88\u7528\u6237<\/p>\n\n\n\n
GRANT ALL ON Syslog.* TO rsyslog@154.17.6.123;<\/code><\/pre>\n\n\n\n
\u4fee\u6539\u6570\u636e\u5e93\u7684\u914d\u7f6e\u6587\u4ef6\u8ba9MYSQL\u53ef\u4ee5\u88ab\u8fdc\u7a0b\u8fde\u63a5<\/p>\n\n\n\n
vim \/etc\/mysql\/mysql.conf.d\/mysqld.cnf <\/code><\/pre>\n\n\n\n
\u627e\u5230bind address\u8fd9\u4e00\u884c\u4fee\u6539\u6210<\/p>\n\n\n\n
bind-address            = 0.0.0.0<\/code><\/pre>\n\n\n\n
\u4fee\u6539\u5b8c\u6210\u4ee5\u540e\u91cd\u542f\u6570\u636e\u5e93\u670d\u52a1<\/p>\n\n\n\n
systemctl restart mysql<\/code><\/pre>\n\n\n\n
\u65e5\u5fd7\u670d\u52a1\u5668\uff1a<\/strong><\/p>\n\n\n\n
\u4fee\u6539rsyslog\u914d\u7f6e\u6587\u4ef6<\/p>\n\n\n\n
\u5728\u6a21\u5757\u914d\u7f6e\u91cc\u52a0\u8f7d\u521a\u624d\u5b89\u88c5\u7684mysql\u6a21\u5757<\/p>\n\n\n\n
vim \/etc\/rsyslog.conf
module(load=\"ommysql\")<\/code><\/pre>\n\n\n\n
\u4fee\u6539\u5b50\u914d\u7f6e\u6587\u4ef6<\/p>\n\n\n\n
\u683c\u5f0f\uff1a<\/p>\n\n\n\n
facility.priority :ommysql:\u6570\u636e\u5e93\u4e3b\u673aIP:\u6570\u636e\u5e93\u540d:\u6570\u636e\u5e93\u7528\u6237\u540d:\u6570\u636e\u5e93\u7528\u6237\u5bc6\u7801<\/p>\n\n\n\n
vim \/etc\/rsyslog.d\/50-default.conf 
*.info :ommysql:154.3.32.85,Syslog,rsyslog,123456<\/code><\/pre>\n\n\n\n
\u4fee\u6539\u5b8c\u914d\u7f6e\u540e\u91cd\u542f\u65e5\u5fd7\u670d\u52a1<\/p>\n\n\n\n
systemctl restart rsyslog<\/code><\/pre>\n\n\n\n
\u6d4b\u8bd5<\/h2>\n\n\n\n
\u5728\u65e5\u5fd7\u670d\u52a1\u5668\u4e0a\u751f\u6210\u4e00\u6761\u65e5\u5fd7<\/p>\n\n\n\n
logger \"guaguaguaguagua\"<\/code><\/pre>\n\n\n\n
\u767b\u5f55\u8fdc\u7a0b\u670d\u52a1\u5668\u7684\u6570\u636e\u5e93\u6267\u884cSELECT\u67e5\u8be2<\/p>\n\n\n\n
SELECT * FROM  Syslog.SystemEvents\\G;<\/code><\/pre>\n\n\n\n
\u53ef\u4ee5\u770b\u5230\u6211\u4eec\u521a\u624d\u53d1\u9001\u7684\u65e5\u5fd7\u4e86<\/p>\n\n\n\n
\"\"<\/figure>\n","protected":false},"excerpt":{"rendered":"
\u672c\u6587\u5c06\u4ecb\u7ecd\u5982\u4f55\u628a\u672c\u5730\u670d\u52a1\u5668\u7684\u65e5\u5fd7\uff0c\u53d1\u9001\u5230\u8fdc\u7a0b\u4e3b\u673a\u7684MYSQL\u6570\u636e\u5e93\u4e2d\u3002 \u73af\u5883\u51c6\u5907 \u4e24\u53f0\u4e3b\u673a \u4e00\u53f0rsyslog\u65e5\u5fd7\u670d\u52a1\u5668\uff0c IP\uff1a154.17.6.123 \u4e00\u53f0MYSQL\u6570\u636e\u5e93\u670d\u52a1\u5668\uff0cIP\uff1a154.3.32.85 \u7cfb\u7edf\uff1aUbuntu 20….<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7,35],"tags":[15,36,34],"topic":[],"class_list":["post-145","post","type-post","status-publish","format-standard","hentry","category-linux","category-35","tag-linux","tag-mysql","tag-34"],"_links":{"self":[{"href":"https:\/\/frogvps.com\/index.php?rest_route=\/wp\/v2\/posts\/145","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/frogvps.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/frogvps.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/frogvps.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/frogvps.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=145"}],"version-history":[{"count":2,"href":"https:\/\/frogvps.com\/index.php?rest_route=\/wp\/v2\/posts\/145\/revisions"}],"predecessor-version":[{"id":190,"href":"https:\/\/frogvps.com\/index.php?rest_route=\/wp\/v2\/posts\/145\/revisions\/190"}],"wp:attachment":[{"href":"https:\/\/frogvps.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=145"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/frogvps.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=145"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/frogvps.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=145"},{"taxonomy":"topic","embeddable":true,"href":"https:\/\/frogvps.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftopic&post=145"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}