Export Speed Results to MySQL

The prerequisites for this tutorial are that MySQL has already been installed and that a database has been created. The database name in these examples will be mcs.

The CREATE TABLE statement below can be used to create a table named myspeed_sql. The table includes some generic test metrics, such as ISP, IP and Session ID (SID), along with the results of the test.

CREATE TABLE myspeed_sql (recordid int, testid int, timeint bigint, sid varchar(255), ip varchar(64), isp varchar(255), dspeed bigint, uspeed bigint, qos int, uqos int, rtt int, rttmax int, rttavg int, rttconsistency int, maxpause int, avgpause int, bandwidth bigint, routespeed bigint, forcedidle int, routeconc float, dtesttype varchar(6), utesttype varchar(6), PRIMARY KEY (recordid));

The image below is an example of copying and pasting the above statement into a MySQL command prompt. Once the table has been created the describe tablename; command can be used to view the newly created table structure.

The next step is to enable MySQL in MyConnection Server (MCS). This step may have already been done. If not, click here.

With MySQL enabled and the table created it's time to configure MCS to export data. The first step in doing this is to create an SQL profile.

From the MCS main menu click on Manage SQL Profiles, which is in the administration panel, as shown below.

On the resulting page click the Create new SQL profile link, as shown below.

The first step when creating a new profile is to enter the connection settings.

• Name: Enter a name for the profile. This will appear in the list of profiles on the previous page.
• Server Type & Address: Select MySQL from the drop down list box and then enter the IP/domain and port number of the MySQL database.
• Credentials & DB: Enter the login credentials for the MySQL server and the database name that contains the table created in the first part of this tutorial.
• Run Check the Run... box. With the box checked the SQL profile will run each time a Speed test is run, ensuring all data gets exported.

Save the profile, which will take you back to the profiles list. Immediately modify the profile as the next step is definiing the export data.

The SQL Statements to run text box is where the export data is specified, in the form of an INSERT INTO statement, as shown below.

The INSERT INTO statement below matches the CREATE TABLE specified in the first step. So, simply copy and paste it into the text box.

[speed] INSERT INTO myspeed_sql (recordid, testid, timeint, sid, ip, isp, dspeed, uspeed, qos, uqos, rtt, rttmax, rttavg, rttconsistency, maxpause, avgpause, bandwidth, routespeed, forcedidle, routeconc, dtesttype, utesttype) VALUES (%RECORDID%, %TESTID%, %TIMEINT%, '%SID%', '%IP%', '%ISP%', %SPEED.DSPEED%, %SPEED.USPEED%, %SPEED.QOS%, %SPEED.UQOS%, %SPEED.RTT%, %SPEED.RTTMAX%, %SPEED.RTTAVG%, %SPEED.RTTCONSISTENCY%, %SPEED.MAXPAUSE%, %SPEED.AVGPAUSE%, %SPEED.BANDWIDTH%, %SPEED.ROUTESPEED%, %SPEED.FORCEDIDLE%, %SPEED.ROUTECONC%, '%SPEED.DTESTTYPE%', '%SPEED.UTESTTYPE%');

Once pasted it should look like this:

Save the profile to complete the process!

Now, whenever a Speed test is run, this MySQL Export should run and put data in the database.

When using a MySQL command prompt the data for a table can be viewed by using the command select * from tablename, as shown below.