创建schema
根据提供的脚本，要预先定义好 schema 和相应表结构
代码语言：javascript复制[root@h105 ~]# cd /usr/share/doc/rsyslog-mysql-5.8.10/
[root@h105 rsyslog-mysql-5.8.10]# ls
createDB.sql
[root@h105 rsyslog-mysql-5.8.10]# wc -l createDB.sql
37 createDB.sql
[root@h105 rsyslog-mysql-5.8.10]# cat createDB.sql
CREATE DATABASE Syslog;
USE Syslog;
CREATE TABLE 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
);
CREATE TABLE SystemEventsProperties
(
ID int unsigned not null auto_increment primary key,
SystemEventID int NULL ,
ParamName varchar(255) NULL ,
ParamValue text NULL
);
[root@h105 rsyslog-mysql-5.8.10]# mysql -u root -pmysql < createDB.sql
Warning: Using a password on the command line interface can be insecure.
[root@h105 rsyslog-mysql-5.8.10]# Tip: 注意到这里并没有索引，应该是和日志的 append only 属性相关
查看表结构
代码语言：javascript复制[root@h105 rsyslog-mysql-5.8.10]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 14
Server version: 5.6.27-76.0 Percona Server (GPL), Release 76.0, Revision 5498987
Copyright (c) 2009-2015 Percona LLC and/or its affiliates
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql> show databases;
--------------------
| Database |
--------------------
| information_schema |
| Syslog |
| mysql |
| performance_schema |
| test |
--------------------
5 rows in set (0.00 sec)
mysql> use Syslog;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
------------------------
| Tables_in_Syslog |
------------------------
| SystemEvents |
| SystemEventsProperties |
------------------------
2 rows in set (0.00 sec)
mysql> desc SystemEvents;
-------------------- ------------------ ------ ----- --------- ----------------
| Field | Type | Null | Key | Default | Extra |
-------------------- ------------------ ------ ----- --------- ----------------
| ID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| CustomerID | bigint(20) | YES | | NULL | |
| ReceivedAt | datetime | YES | | NULL | |
| DeviceReportedTime | datetime | YES | | NULL | |
| Facility | smallint(6) | YES | | NULL | |
| Priority | smallint(6) | YES | | NULL | |
| FromHost | varchar(60) | YES | | NULL | |
| Message | text | YES | | NULL | |
| NTSeverity | int(11) | YES | | NULL | |
| Importance | int(11) | YES | | NULL | |
| EventSource | varchar(60) | YES | | NULL | |
| EventUser | varchar(60) | YES | | NULL | |
| EventCategory | int(11) | YES | | NULL | |
| EventID | int(11) | YES | | NULL | |
| EventBinaryData | text | YES | | NULL | |
| MaxAvailable | int(11) | YES | | NULL | |
| CurrUsage | int(11) | YES | | NULL | |
| MinUsage | int(11) | YES | | NULL | |
| MaxUsage | int(11) | YES | | NULL | |
| InfoUnitID | int(11) | YES | | NULL | |
| SysLogTag | varchar(60) | YES | | NULL | |
| EventLogType | varchar(60) | YES | | NULL | |
| GenericFileName | varchar(60) | YES | | NULL | |
| SystemID | int(11) | YES | | NULL | |
-------------------- ------------------ ------ ----- --------- ----------------
24 rows in set (0.00 sec)
mysql> desc SystemEventsProperties;
--------------- ------------------ ------ ----- --------- ----------------
| Field | Type | Null | Key | Default | Extra |
--------------- ------------------ ------ ----- --------- ----------------
| ID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| SystemEventID | int(11) | YES | | NULL | |
| ParamName | varchar(255) | YES | | NULL | |
| ParamValue | text | YES | | NULL | |
--------------- ------------------ ------ ----- --------- ----------------
4 rows in set (0.02 sec)
mysql>
mysql> select * from SystemEventsProperties;
Empty set (0.00 sec)
mysql> select * from SystemEvents;
Empty set (0.01 sec)
mysql>
