Billcentraliseslogs,bemorelikeBillbymysqlboy

AbouttheSpeakerAndrewMoore

WorkPercona

TechServicesManager,RDBAPythian

MySQLDBANokia

MySQLDBA,SQLServerDBA

Assumptions,AdmissionsandDisclaimers

Logs

Logmanagement

Loganalytics

WhatisaLog?Streamofmessagesstoredintimesquence.

timestamp+message

LogTypes

HumanReadable

&

MachineReadable

Whydowelog?

Toanswerquestions.

Loggingisbroken

Reasonswhylegacyloggingsucks

108.61.198.241--[12/Feb/2017:06:00:32+0000]"GET/HTTP/1.1"20027996

Apacheaccess.log

#Time:14032213:54:58#User@Host:root[root]@localhost[]#Query_time:0.000303Lock_time:0.000090Rows_sent:1Rows_examined:10useprofile_sampling;SETtimestamp=1395521698;SELECT*FROMusersWHEREname='Bill';

MySQLslowquerylog

Feb1203:27:17ip-172-31-31-171fail2ban.actions:WARNING[apache-xmlrpc]\Unban194.87.98.7

syslog

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:Communicationslinkfailure<==FirstlinewitherrordescriptionThelastpacketsentsuccessfullytotheserverwas0millisecondsago.Thedriverhasnotreceivedanypacketsfromtheserver.atsun.reflect.NativeConstructorAccessorImpl.newInstance0(NativeMethod)atsun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)atsun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)atjava.lang.reflect.Constructor.newInstance(Constructor.java:525)atcom.mysql.jdbc.Util.handleNewInstance(Util.java:411)atcom.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1116)atcom.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:344)atcom.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2333)atcom.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2370)atcom.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2154)atcom.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:792)atcom.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)atsun.reflect.NativeConstructorAccessorImpl.newInstance0(NativeMethod)...

[1446935.222375]sdd:sdd1sdd2

dmesg

2015-06-1301:00:03,985PID<11678>INFO::<prod_host>read_onlyisset.2015-06-1301:00:04,033PID<11678>INFO::<prod_host>DiskspaceOK:free23599Mb,needed4297Mb2015-06-1301:00:04,034PID<11678>INFO::<prod_host>Backupdata...

PerconaManagedServicesbackuplog

Expertise

LogStructure

{"audit_record":{"name":"Query"..."timestamp":"2014-08-27T10:44:19UTC"..."connection_id":"37""status":0"sqltext":"SELECT*FROMBILL;""user":"root[root]@localhost[]"...}}

Percona-ServerAuditLog

TimestampsAin'tnobodygottimestampsforthat

1491861039

2017-04-10T21:50:39Z

14032213:54:58

Feb1203:27:17

[12/Feb/2017:06:00:32+0000]

UseUNIXTimestamporaniso8601variation

VolumeofLogs

AccessingLogs

Managementoverhead

Tools

Weneedtogetsmarter

LogMaturityScale

0.Nothing

1.Collecting

2.Troubleshooting

3.SaveSearches

4.ShareSearches

5.Reporting

6.Alerting

7.Moreevents

8.Correlation

9.Visualization

http://raffy.ch/blog/2010/06/07/maturity-scale-for-log-management-and-analysis/

LogManagementThePathtoCentralisationandAnalysis

how?

OSS

ElasticStacksyslogrsysloggraylog2

Commercial

honeycomblogglyelasticcloud-

Depends

LogEventLifeSpan1. Create2. Ship3. Centralize4. Enrich5. Store6. Analyse7. Visualize8. Archive9. Delete

ELKStack

ELK+B

ElasticStack

Beats:Shipper

BeatsLightweightgobinary

PacketbeatMetricbeatFilebeatWinlogbeatHeartbeat

Installrepo,rpm,deb,tar.gz

FilebeatConfig(elasticsearch)filebeat.prospectors:-input_type:logpaths:-/var/log/*.log

FilebeatConfig(elasticsearch)filebeat.prospectors:-input_type:logpaths:-/var/log/*.log

output.elasticsearch:hosts:["192.168.1.99:9200"]

Config(logstash)filebeat.prospectors:-input_type:logpaths:-/var/log/*.log

output.logstash:hosts:["127.0.0.1:5044"]

Logstash:Shipper&Enrichment

LogstashOpenSourceJRubyInputFilters

EnrichRemove

Output

Logstashconfiguration(input)input{beats{port=>5044ssl=>truessl_certificate_authorities=>["/etc/ca.crt"]ssl_certificate=>"/etc/server.crt"ssl_key=>"/etc/server.key"ssl_verify_mode=>"force_peer"}}

Logstashconfiguration(filter)filter{if[type]=="syslog"{grok{match=>{"message"=>"%{SYSLOGTIMESTAMP:syslog_timestamp}\%{SYSLOGHOST:syslog_hostname}\%{GREEDYDATA:syslog_message}"}add_field=>["received_at","%{@timestamp}"]add_field=>["received_from","%{host}"]}date{match=>["syslog_timestamp","MMMdHH:mm:ss","MMMddHH:mm:ss"]}}}

*don'tcopythis,itwon'twork!

Logstashconfiguration(filter)cipher{algorithm=>...mode=>...}

Logstashconfiguration(output)output{if[type]=="foo"{if[status]=~/^5\d\d/{pagerduty{...}}elseif[status]=~/^4\d\d/{elasticsearch{...}}}}

Input

Filejdbchttp/sog4jSyslogRediss3,sqs...

Filter

DNSlookupgeoipDategrokjson_encodeYaml

Output

ElasticsearchHipchatNagiosInfluxLogglyPagerdutyStatsd

LogstashPlugins

LogstashTimestamps

grok{match=>{"message"=>"%{COMBINEDAPACHELOG}"}}

grok{match=>{"message"=>"%{SYSLOGTIMESTAMP:syslog_timestamp}"}}

LogstashTimestamps

{some>reallyhorrible|sedorawk&&commandhere;maybe%even%perl.fml}

Elasticsearch:store&analyse

ElasticsearchJava,JVMBuiltonApacheLuceneDistributedRESTfulAPIFaulttolerantScalable

ElasticsearchInvertedIndex(lucene)

ElasticsearchDataIndexesTypesDocumentsFields

ElasticsearchDataIndexes~TablesTypes~Meh!*Documents~RowsFields~Columns

*deprecatedsoon

ElasticsearchShards

ElasticsearchQueriesGET/bank/_search{"query":{"match_all":{}},"sort":{"balance":{"order":"desc"}}}

ElasticsearchQueriesPUT/playground/equipment/1/{"type":"slide","quantity":2}

ElasticsearchNodesDataMasterClientIngestTribe

Analyse

Visualize

KibanaJavascriptdrivenfront-endvisualizeyourElasticsearchdataQueryDashboards

$$$Nowyouknowyoucan,shouldyou?