Configuring syslog-ng to store logs into a MySQL database

/ Felipe Alfaro Solana

This short article describes how to configure syslog-ng in order to store the logs into a MySQL backend. This adds more flexibility when performing log analysis, log searching and correlation.

Installing MySQL

MySQL can be compiled from source and installed using the FreeBSD ports collection:

# cd /usr/ports/databases/mysql41-server/
# make install distclean

Configuring MySQL

Use the following commands to create the MySQL database directory and install a configuration file:

# mkdir /var/db/mysql
# cp /usr/local/share/mysql/my-small.cnf /var/db/mysql/my.cnf

Optionally, edit /var/db/mysql/my.cnf to adjust some parameters like:

  • Disabling networking support.

    This can be done by uncommenting the skip-networking option from the configuration file.

    This will prevent MySQL from listening on port 3306/tcp. Since we are using MySQL locally, we can use UNIX sockets instead of true networking.

  • Reducing memory usage.

    By adjusting innodb_buffer_pool_size and innodb_additional_mem_pool_size to values suited to the amount of RAM available to FreeBSD

Next, add the following lines into /etc/rc.conf so that MySQL will get launched during system startup:

# MySQL
mysql_enable="YES"
mysql_limits="YES"
mysql_dbdir="/var/db/mysql"
mysql_args=""

Starting MySQL

Use the following command to start MySQL:

# /usr/local/etc/rc.d/mysql-server.sh start

Creating the MySQL database

The logs will get stored into a table named logs on database syslog.
To create the database and table, create a file named syslog.sql with the following SQL commands:

CREATE DATABASE syslog;
USE syslog;
CREATE TABLE logs (
	host varchar(32) default NULL,
	facility varchar(10) default NULL,
	priority varchar(10) default NULL,
	level varchar(10) default NULL,
	tag varchar(10) default NULL,
	timestamp datetime default NULL,
	program varchar(15) default NULL,
	msg text,
	seq int(10) unsigned NOT NULL auto_increment,
	PRIMARY KEY (seq),
	KEY host (host),
	KEY seq (seq),
	KEY program (program),
	KEY timestamp (timestamp),
	KEY priority (priority),
	KEY facility (facility)
);

Then, process those SQL commands using MySQL client:

# mysql -u root -p <syslog .sql

Setting up the communication channel

syslog-ng will issue INSERT INTO SQL commands into a UNIX pipe for every log received and processed. Those SQL commands will be retrieved from the UNIX pipe and will be injected into MySQL.

This UNIX pipe will act as the communication channel between syslog-ng and MySQL. To create the UNIX pipe:

# mkfifo /tmp/mysql.pipe

Also, we will create a startup script used to keep feeding SQL commands sent to the UNIX pipe to MySQL called /usr/local/etc/rc.d/040.mysql-syslog.sh:

( while [ -e /tmp/mysql.pipe ]
  do
        /usr/local/bin/mysql -u root --password= syslog

This script will get invoked at startup and will keep feeding the SQL commands generated by the mysql syslog-ng destination into the MySQL database.

However, we must make sure this startup script is invoked after MySQL has been started. Thus, in FreeBSD I recommend renaming the MySQL startup script:

# mv /usr/local/etc/rc.d/mysql-server.sh 
     /usr/local/etc/rc.d/030.mysql-server.sh

Setting up syslog-ng

Modify /usr/local/etc/syslog-ng/syslog-ng.conf to add a new source called net used to retrieve logs via the network:

source net { udp(); };

Next, add a new destination for MySQL:

destination mysql { pipe("/tmp/mysql.pipe"
template("INSERT INTO logs (host, facility, priority, level, tag,
timestamp, program, msg) VALUES ( '$HOST', '$FACILITY', '$PRIORITY',
'$LEVEL', '$TAG', '$YEAR-$MONTH-$DAY $HOUR:$MIN:$SEC', '$PRORAM',
'$MSG' );n") template-escape(yes) ); };

Finally, configure syslog-ng so all logging is sent to the MySQL destination. Since syslog-ng allows multiple destinations, this makes perfectly possible to keep locally-generated log events stored in text files alongside the MySQL database.


log { source(net); destination(mysql); };
log { source(src); destination(mysql); };

Finally, we send the SIGHUP signal to syslog-ng to instruct it to re-read its configuration file and reconfigure accordingly:

# pkill -HUP syslog-ng

73 thoughts on “Configuring syslog-ng to store logs into a MySQL database”

  1. Pingback: مشکل در ذخیره log ها در دیتابیس (syslog-ng)

  2. I would like to thnkx for the efforts you have put in writing this blog. I’m hoping the same high-grade site post from you in the upcoming as well. Actually your creative writing abilities has encouraged me to get my own site now. Actually the blogging is spreading its wings fast. Your write up is a great example of it.

  5. With havin so much content do you ever run into any problems of plagorism or copyright infringement?
    My website has a lot of completely unique content I’ve either authored myself or outsourced but it looks like a lot of it is popping it up all over the internet
    without my agreement. Do you know any ways to help protect against content from being stolen? I’d certainly appreciate it.

  7. I am not certain the place you’re getting your information, but great topic.
    I needs to spend some time finding out much more or understanding more.
    Thanks for fantastic info I was looking for this information for my
    mission.

  8. Thanks on your marvelous posting! I seriously enjoyed reading it,
    you’re a great author.I will always bookmark your blog and will come back in the future.

    I want to encourage yourself to continue your great work, have a nice holiday weekend!

  10. Being known in the industry will help you become a tester.
    The uncertain mind of a child gives the danger of not determining what’s good
    and what’s bad, what’s right and what’s wrong. Di – RT 2 takes the players
    into competitive multi-car and passionate solo racing events in a most challenging and diverse
    real world environment.

  11. It is a sourcebook for remodeling ideas
    and products. Not to mention, their evening aerobatic sessions are a whole lot of
    fun to watch. Well, they are easy to use, they can use very light line, it is easy to adjust the drag and they are generally less expensive than other kinds of reels.

  12. Using the online communities expanding rapidly the base for
    these games to survive has become better and the opportunity of
    reaching some sort of billion users in addition has work as a possibility.
    From video game titles being targeted intended for
    platforms like Laptops, Controllers, and androids today games are being developed exclusively for communities.
    These types of communities include millions of consumers
    including the The facebook and there are focused Facebook game programmers active in the procedure for designing as well as developing games according to
    the limitations and boundaries of the program.

  13. I’m not sure where you’re getting your information, but great topic.
    I needs to spend some time learning more or understanding more.
    Thanks for great information I was looking for this information for my mission.

  14. It is the best time to make some plans for the future and it’s time to be happy.
    I’ve read this post and if I could I desire to suggest
    you few interesting things or suggestions. Maybe you could write next articles referring to
    this article. I want to read even more things about it!

  15. Admiring the hard work you put into your website and detailed information you offer.

    It’s nice to come across a blog every once in a
    while that isn’t the same old rehashed information. Wonderful read!
    I’ve saved your site and I’m adding your RSS feeds to my Google account.

  16. Rule #3 ‘ Talk about the one subject that is most interesting and important to your date.

    By taking advantage of the latest computer technology this product
    has compiled enough projects to fill many bookshelves. Hannah and Elijah match one another so much
    because they feed off of one another’s toxic narcissism.

  17. I’m really loving the theme/design of your website.

    Do you ever run into any internet browser compatibility
    problems? A handful of my blog visitors have complained about my website not working correctly in Explorer but looks great in Safari.
    Do you have any suggestions to help fix this problem?

  21. I’m not that much of a online reader to be honest but your blogs really nice, keep it up!

    I’ll go ahead and bookmark your website to come back down the road.
    All the best

  22. Just wish to say your article is as surprising.
    The clearness in your post is simply nice and i can assume you are
    an expert on this subject. Fine with your permission let me
    to grab your RSS feed to keep up to date with forthcoming post.
    Thanks a million and please carry on the rewarding work.

  23. Howdy! I know this is kind of off topic but I was wondering if you knew where I could find a
    captcha plugin for my comment form? I’m using the same blog platform as yours
    and I’m having difficulty finding one? Thanks a lot!

  24. I drop a leave a response when I especially enjoy a article on a blog or I have something to valuable to
    contribute to the conversation. It’s a result of the
    sincerness displayed in the article I read. And on this post Configuring syslog-ng to store logs into a MySQL
    database | felipe-alfaro.com. I was actually excited enough to drop
    a leave a responsea response 🙂 I do have 2 questions for you
    if you don’t mind. Could it be just me or does it look as if
    like a few of these remarks appear like coming from brain dead visitors?
    😛 And, if you are writing on other places, I would
    like to keep up with anything fresh you have to post.

    Could you make a list every one of all your shared sites
    like your Facebook page, twitter feed, or linkedin profile?

  25. Every weekend i used to go to see this site,
    for the reason that i want enjoyment, for the reason that this this web page conations in fact pleasant funny data too.

  27. Howdy I am so grateful I found your website, I really found you by mistake, while I
    was researching on Google for something else, Anyhow I am here now and would just like to say many thanks for a tremendous post and a all round enjoyable blog (I
    also love the theme/design), I don’t have time to go through it all at
    the minute but I have book-marked it and also included your RSS feeds, so when I have time I will be back to read more, Please
    do keep up the great b.

  28. Attractive component of content. I just stumbled upon your blog and in accession capital to assert that I get in fact loved account your
    weblog posts. Any way I will be subscribing for your feeds or even I fulfillment you get
    right of entry to persistently fast.

  30. I love what you guys are up too. This sort of clever work and exposure!
    Keep up the superb works guys I’ve incorporated you guys to my own blogroll.

  32. I believe what you typed was actually very reasonable. However,
    what about this? what if you were to write a killer post title?
    I am not saying your content is not solid., but
    suppose you added a post title that makes people want more?

    I mean Configuring syslog-ng to store logs into a
    MySQL database | felipe-alfaro.com is a little plain. You ought to look at Yahoo’s home page and see how they create post titles to grab people to open the links.
    You might try adding a video or a related picture or two to grab
    people interested about everything’ve written. In my
    opinion, it could bring your posts a little bit more interesting.

  33. I know this if off topic but I’m looking into
    starting my own weblog and was wondering what all is required to get sset up?
    I’m assuming having a blog like yours would cost a pretty penny?

    I’m not very internet smart so I’m not 100% positive. Any tips
    or advice would be greatly appreciated. Many thanks

  36. Hi to all, the contents existing at this web page are really remarkable for people experience, well,
    kee uup the nice workk fellows.

  37. Attractive section of content. I just stumbled upon your weblog
    and in accession capital to assert that I acquire in fact enjoyed account your blog
    posts. Anyway I will be subscribing to your augment and
    even I achievement you access consistently quickly.

  41. You really make it seem so easy along with your presentation however I in finding this topic to be really something that
    I feel I might never understand. It sort of feels too complex and very large for me.
    I am having a look forward to your subsequent post, I will attempt to get the grasp
    of it!

  44. One bullet per target and stick to the pistol for the
    fast reloads. 50COMIC BOOK COVER PORTFOLIO #2 JUSTICE COLLECTION $49.

    Estimation is allowed to a certain extent within the architectural drafting field.

  45. Have you ever thought about adding a little bit more than just your
    articles? I mean, what you say is fundamental
    and everything. However think of if you added some great graphics or videos to give your posts more,
    “pop”! Your content is excellent but with pics and video clips, this
    blog could certainly be one of the most beneficial in its niche.
    Excellent blog!

  47. Thanks on your marvelous posting! I seriously enjoyed reading
    it, you’re a great author.I will make certain to bookmark
    your blog and may come back from now on. I want to encourage one to continue your great work, have a nice evening!

  48. It’s hard to come by experienced people in this particular subject, however, you seem like you know what you’re talking
    about! Thanks

  50. I’d like to thank you for the efforts you’ve put in penning this site.
    I really hope to see the same high-grade blog posts from you later on as well.
    In truth, your creative writing abilities has inspired me to get my own, personal website now
    😉

  53. I’m amazed, I have to admit. Seldom do I encounter a blog
    that’s equally educative and interesting, and let me
    tell you, you’ve hit the nail on the head. The problem is an issue that too few men and women are speaking intelligently about.
    I’m very happy that I found this in my hunt for something relating to this.

  57. Hello I am so thrilled I found your web site, I really found you by
    error, while I was searching on Aol for something else, Anyways I
    am here now and would just like to say thanks a lot for a fantastic
    post and a all round entertaining blog (I also love the theme/design), I don’t
    have time to read it all at the minute but I have book-marked it and
    also included your RSS feeds, so when I have time I will be back to read a lot more, Please do keep up the excellent work.

  60. Her first signature perfume is generally considered to be
    light, girly and easily wearable on a daily basis.
    Some sites require you to pay the fee when you try to contact others.
    The ultimate source of this renewable biomass is the inexhaustible solar energy which is captured by plants through photosynthesis.

  61. Most West Rim excursions have you back by early evening.
    Current advancement and research state that these quadcopterswould be capable of advanced autonomous mission, which any other UAV cannot perform.
    Batteries keep running out after a certain period and someone needs to know exactly how to charge them and the equipment that will be used to charge.

  68. Manufacturers of seedbox servers took under consideration that different users have different systems on their native machines and provided users with
    servers that allowed them to share files throughout the Internet inspite of the os running natively on their own machines.
    It is just not designed for secure or large-scale application. Easy when you are aware what
    you are looking for in the product and difficult
    when you don’t have a system for choosing the very best Clickbank products.

  70. The backend part of your company supports these profit centers.
    In addition, many of the words used started to take
    on slightly different meanings, depending on the context in which they are used.
    Efficiency: Many carriers provide multiple receive emails, so your fax messages are automatically delivered to whomever you intend.

  71. This is a very good tip particularly to those fresh to
    the blogosphere. Short but very accurate info… Many thanks forr sharing
    this one. A must read article!

  72. I purchased the hair over a Thursday and received it the following Monday.
    The shipping was rapidly. I bought 3 packages of 8 inches and and I received 10 inch packages instead since they were out of 8
    inches. They quit an email apologizing for your difficulty.

    Sophia, the vendor was very form and beneficial.
    I am not unhappy . I’ve had it mounted for
    over a week now and the hair does work to duration and they will
    be definitely purchased from by me again.

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out /  Change )

Cancel

Connecting to %s