1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

HELP! Connecting rflow collector to mySQL

Discussion in 'DD-WRT Firmware' started by craigbolland, Feb 10, 2007.

  1. craigbolland

    craigbolland LI Guru Member

    Hey all!

    After bricking my WRT54GL a couple of times trying to switch from Tomato to DD-WRT, I've finally got everything ticking along nicely. Great firmware! I've donated.

    I'm finding rflow connector really useful, and would like to set it up to dump the data onto a mysql database so I can retain the info in between reboots/power cycling/etc.

    My problem is, I'm not having ANY luck getting the two to talk. rflow connector just keeps on returning the error "no mysql connection".

    Any info on what exactly I have to do in mySQL to get the connection happening would be REALLY appreciated. I've spent hours looking for a dummies guide to this somewhere but can't find one.

    I'm mostly clueless when it comes to mysql, I'm afraid. I can create a schema called rflow using mySQL administrator - but what should the tables be? Do I even need to create the schema or will rflow collector magically do this itself? Do I need to create the schema and then do some tricky things to tell it WHAT of the rflow data I want captured? Argh! I promise I'll write this up properly (I'm a tech writer/journalist) and give it back to the world if someone can help me through this.

    So far, I've:

    Opened port 3306 on my firewall.

    Made sure other gui tools can connect to the instance fine (mysql administrator etc).

    I have rflow set up as:

    Server Host or IP: localhost
    mySQL user: root
    mySQL password: password (ie: the correct password)
    mySQL database: rflow
    mySQL port: 3306

    Help please!

    Craig.
     
  2. craigbolland

    craigbolland LI Guru Member

    I'm getting partway there. Just found this:


    # Server Version: 4.00.21
    # PHP-Version: 4.0.4pl1
    # Datenbank: `rflow`
    # --------------------------------------------------------

    #
    # Tabellenstruktur für Tabelle `akteth`
    #

    DROP TABLE IF EXISTS akteth;
    CREATE TABLE akteth (
    ID int(11) NOT NULL auto_increment,
    ip varchar(17) NOT NULL default '',
    mac varchar(17) NOT NULL default '',
    status char(1) NOT NULL default '',
    lasttraffic int(11) NOT NULL default '0',
    name varchar(100) NOT NULL default '',
    device varchar(10) NOT NULL default '',
    PRIMARY KEY (ID)
    ) TYPE=MyISAM;
    # --------------------------------------------------------

    #
    # Tabellenstruktur für Tabelle `aktrouter`
    #

    DROP TABLE IF EXISTS aktrouter;
    CREATE TABLE aktrouter (
    ID int(11) NOT NULL auto_increment,
    ip varchar(17) NOT NULL default '',
    flowsequenz int(11) NOT NULL default '0',
    lastflow varchar(7) NOT NULL default '0',
    ploss int(11) NOT NULL default '0',
    PRIMARY KEY (ID)
    ) TYPE=MyISAM;
    # --------------------------------------------------------

    #
    # Tabellenstruktur für Tabelle `globals`
    #

    DROP TABLE IF EXISTS globals;
    CREATE TABLE globals (
    id int(11) NOT NULL auto_increment,
    name varchar(20) NOT NULL default '',
    value varchar(50) NOT NULL default '',
    PRIMARY KEY (id)
    ) TYPE=MyISAM;
    # --------------------------------------------------------

    #
    # Tabellenstruktur für Tabelle `traffic`
    #

    DROP TABLE IF EXISTS traffic;
    CREATE TABLE traffic (
    ID int(11) NOT NULL auto_increment,
    zeit varchar(10) NOT NULL default '',
    datum varchar(10) NOT NULL default '',
    name varchar(100) NOT NULL default '',
    uloktets int(11) NOT NULL default '0',
    dloktets int(11) NOT NULL default '0',
    porttraffic text NOT NULL,
    mac varchar(17) NOT NULL default '',
    ip varchar(15) NOT NULL default '',
    device varchar(10) NOT NULL default '',
    oktets int(11) NOT NULL default '0',
    srcip varchar(15) NOT NULL default '',
    dstip varchar(15) NOT NULL default '',
    srcport varchar(11) NOT NULL default '',
    dstport varchar(11) NOT NULL default '',
    PRIMARY KEY (ID)
    ) TYPE=MyISAM;
    # --------------------------------------------------------

    #
    # Tabellenstruktur für Tabelle `wlanclients`
    #

    DROP TABLE IF EXISTS wlanclients;
    CREATE TABLE wlanclients (
    id int(11) NOT NULL default '0',
    mac varchar(17) NOT NULL default '',
    rssi varchar(5) NOT NULL default '',
    ip varchar(15) NOT NULL default '',
    status char(1) NOT NULL default '',
    location varchar(5) NOT NULL default ''
    ) TYPE=MyISAM;

    Easy to build the tables using that code. Just connect to the database using the shell (connect rflow) and then copy and paste that in to the shell. I pasted in the commands individually, not using the DROP TABLE IF EXISTS lines, because there were no tables - it was a fresh database. But, the whole lot could just be copied and pasted in for timeliness' sake.
     
  3. craigbolland

    craigbolland LI Guru Member

    erm. . . just documenting as I go in case no-body can help me and someone else has this problem sometime. Would love to hear from anyone who HAS got this talking to mySQL properly!
     
  4. craigbolland

    craigbolland LI Guru Member

    and now I've ground to a halt. rflow connector keeps returning the "no mysql Connection!" error, despite my being pretty sure the schema is correct.
     
  5. craigbolland

    craigbolland LI Guru Member

    Oh, and I'm using mysql server 5.0.

    I notice that the libmysql.dll file that ships with rflow connector is much smaller and older than the version that ships with 5.0. I wonder if there's a mismatch somewhere there?
     
  6. craigbolland

    craigbolland LI Guru Member

    why do I get the feeling I'm talking to myself?

    Problem solved. Putting it here to go on the record.
    Here's a tip if you're using mysql 5.0 or higher with rflow collector.

    I was having problems with writing to the database, and after much trial and error this fixed it.

    Download the MYSQL gui tools (notably MYSQL ADministrator)

    Log in to mysql administrator and make sure you add a user with the username and password specified in rflow collector. Give it full access to the rflow schema.

    Go to Startup Variables > security

    Make sure Use Old Passwords is checked.

    Stop and restart the mysql service.

    Voila.

    Craig Bolland.

    [post edited to update my solution]
     
  7. craigbolland

    craigbolland LI Guru Member

    My next question is if anyone has any cool queries written for the rflow database?
     
  8. azeari

    azeari LI Guru Member

    wow 7 posts in a day. haha, me can't help you with that, but i'd be interested to know what u did wrong that resulted in ur router bricking serveral times. might consider jumping ship to dd-wrt cuz of the vpn capabilities =)
     
  9. craigbolland

    craigbolland LI Guru Member

    Hy mate. Just bricked it jumping around between different firmwares - the linksys default, tomato, talisman and dd-wrt. It seems some don't play well with others. Also did a doofus thing and didn't install dd-wrt mini before upgrading it to the standard version - just tried the standard version right off. User error. All is good now.

    Pity nobody can help me. I did end up getting rflow talking to mysql, but it constantly drops out for some reason. It will connect, dump some data, and then after 5 minutes or so stop. I don't suppose anyone can help with that?

    Yeah, am currently using dd-wrt. I think it's the best of the bunch.
     

Share This Page