Minimalist MySQL Reporting

Sometimes business folk can have crazy demands on the types of reports your system should produce and sometimes these can be really time consuming and painful to get right. Often I find the most flexible solution is to give these power-users access to tidier version of the raw data within a tool they're comfortable playing with themselves.

To that end, Excel has a serviceable ODBC connector that costs $35 and will allow your power users to pull data in from MySQL and filter/play around to their hearts content. The problem is a) formatting the data in such a way to provide useful information to your users and b) opening up data access just enough to allow these reporting users into your system.

A) Formatting the data

The easiest way to format data in MySQL is to create a view, these are not difficult to setup. You simply need to wrap some simple syntax around the query you wish to run. So build the query that returns the

CREATE VIEW view_name AS
 SELECT column_name(s)
 FROM table_name
 WHERE condition
 ```

One thing to note is that the ODBC connector won't render BLOB data, so
to handle this use

```sql
SELECT (CONVERT field utf8) FROM table;

to convert it to a readable format.

B) Opening up data access

Step 1: By changing the bind-address from 127.0.0.1 to 0.0.0.0 in /etc/mysql/my.cnf this opens MySQL for remote connections. A better alternative to this could be to keep MySQL closed and tunnel the connections over SSH, this can be done with Putty on Windows or just run ssh -ND user@remoteserver to setup a proxy.

Step 2: If you decide to open MySQL to the outside world I strongly recommend using a firewall to restrict access to the port. On ubuntu to open up the firewall to allow connections to port 3306 from your office ip just run this command.

sudo ufw allow from yourip to any port 3306

Step 3: Now create the remote user.

CREATE USER 'reportUser'@'yourip' IDENTIFIED BY 'yourpassword';

Step 4: Granting access to only a view for users on your IP.

GRANT SELECT ON database.view_name TO 'reportUser'@'yourip';

FLUSH PRIVILEGES

Step 5: Simply point the ODBC Excel connector to your MySQL host and have it run the query

SELECT * FROM database.view_name

and the result set should populate in Excel.

One final key thing to note is that it's a very dangerous thing to allow reporting users to hit live databases, they could really kill the system for live users. Ideally, you would do all of this on a replicated MySQL server that only allows read-only actions against it, like backups and reporting.