db4free.net now exclusively runs MySQL 5.1

It's past 7 o'clock in the morning, but this night has been a productive one (hey - no bad thinking please ;-)).

I have updated db4free.net to the latest MySQL 5.1 development source version (5.1.12-beta) and discontinued the 5.0 server. The "former" 5.1 server which ran on port 3307 is still alive, but no new accounts are being created there anymore. During the next days I plan to make the databases that run on the port 3307 server accessible on the main 5.1 server (with standard port 3306) to have everything together on one server - and let the port 3307 server die.

Before the update, most users have only used the 5.0 server, but only a little number of users did anything on the 5.1 server. My intention is to test 5.1 more intensively under a higher load and I hope that this also helps to contribute to the development of MySQL 5.1 by finding bugs or other flaws on a production system. I'm also thinking of setting up a replication slave server (instead of the second production server) to do regular backups from there - and of course for testing and finding bugs.

Running MySQL 5.1 offers new possibilities for the users and for me as administrator. The users can now create events and triggers (it was not possible for users to create triggers on the 5.0 server since this would have required SUPER privileges). I want to make use of new logging capabilities, events and new information_schema views to set up a monitoring system - all done by the server. The new MySQL 5.1 features make it much more comfortable for me to keep track of what's happening on the server. This will also be used to clean up unused accounts and to find misused accounts more easily and more quickly.

So this is a great improvement for the users as well as for me - and hopefully also for the MySQL company and products.

New information_schema views

As you might have recognized already, I love to take a look into the Change logs from time to time and hope to find some goodies there. Here's an especially nice one in the Change log of 5.1.12:

INFORMATION_SCHEMA contains new tables, GLOBAL_STATUS, SESSION_STATUS, GLOBAL_VARIABLES, and SESSION_VARIABLES, that correspond to the output from the SHOW {GLOBAL|SESSION} STATUS and SHOW {GLOBAL|SESSION} VARIABLES statements.

This was reason enough for me to compile MySQL 5.1 from source and take a look:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.1.12-beta-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use information_schema
Database changed
mysql> show tables;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| CHARACTER_SETS                        |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
| ENGINES                               |
| EVENTS                                |
| FILES                                 |
| GLOBAL_STATUS                         |
| GLOBAL_VARIABLES                      |
| KEY_COLUMN_USAGE                      |
| PARTITIONS                            |
| PLUGINS                               |
| PROCESSLIST                           |
| REFERENTIAL_CONSTRAINTS               |
| ROUTINES                              |
| SCHEMATA                              |
| SCHEMA_PRIVILEGES                     |
| SESSION_STATUS                        |
| SESSION_VARIABLES                     |
| STATISTICS                            |
| TABLES                                |
| TABLE_CONSTRAINTS                     |
| TABLE_PRIVILEGES                      |
| TRIGGERS                              |
| USER_PRIVILEGES                       |
| VIEWS                                 |
+---------------------------------------+
27 rows in set (0.00 sec)

So what can you do with it? Certainly a lot. You want to know when your server has been restarted the last time? Here's what to do:

mysql> SELECT (NOW() - INTERVAL VARIABLE_VALUE SECOND) 
   AS server_start_time
    -> FROM information_schema.GLOBAL_STATUS
    -> WHERE VARIABLE_NAME = 'UPTIME';
+---------------------+
| server_start_time   |
+---------------------+
| 2006-09-23 20:38:05 |
+---------------------+
1 row in set (0.00 sec)

There are other nice things you can do. You can create a monitoring table and an event to copy the values of all Com% status variables into the monitoring table regularily, like so:

SELECT NOW() AS ts, 
   VARIABLE_NAME, VARIABLE_VALUE
FROM information_schema.GLOBAL_STATUS
WHERE VARIABLE_NAME LIKE 'Com%'

And that's certainly not everything you can do ;-).