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.

Creating the myspeed_sql table

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, configure MCS to export data. The first step is to create a SQL profile.

From the MCS main menu, click Manage SQL Profiles in the administration panel.

Manage SQL Profiles

On the resulting page, click Create new SQL profile.

Create new SQL profile

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

1. Name: Enter a name for the profile. This will appear in the list of profiles on the previous page.
2. Server Type & Address: Select MySQL from the dropdown and enter the IP/domain and port number of the MySQL database.
3. 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.
4. Run: Check the Run... box. With this box checked, the SQL profile will run each time a Speed test is run, ensuring all data gets exported.

SQL connection settings

Save the profile, which will return to the profiles list. Immediately modify the profile, as the next step is defining the export data.

Modify the profile

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

The INSERT INTO statement below matches the CREATE TABLE specified in the first step. 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:

INSERT INTO statement

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.

Viewing data in the MySQL table