www.howtoforge.org

Many of you might know HowToForge, but for those who don't - there's a bunch of excellent and interesting tutorials relating to Linux and OpenSource.

There are also many about MySQL or using MySQL - here are some examples:

• How To Set Up Database Replication In MySQL


• MySQL Master Master Replication


• How To Set Up A Load-Balanced MySQL Cluster


• Running A MySQL-Based DNS Server: MyDNS


• Virtual Users And Domains With Postfix, Courier And MySQL (Fedora Core 5)


• Virtual Users And Domains With Postfix, Courier And MySQL (+ SMTP-AUTH, Quota, SpamAssassin, ClamAV)

... and more.

There's definitely a lot of stuff to play with.

Official German MySQL 5.1 Reference Manual to be released this month

All German speaking folks will have a chance to get a printed German MySQL 5.1 Reference Manual which covering 1,312 pages for a price of EUR 59.95 (hey, most books with that amount of pages cost much more than that).

It is about to be released this month and can be pre-ordered at amazon.de.

I pre-ordered my copy today. Sometimes it's nice to leave the PC for a while and dig into a good book, but still use the time to improve skills. There are still some topics about MySQL that I'm not as familiar with as I wished and this will be good opportunity to remove some gaps in my knowledge.

New Lists of Certified Candidates online

The Lists of MySQL Certified Candidates now also include the new Certifications for MySQL 5.0 - Certified MySQL 5.0 Developer and Certified MySQL 5.0 DBA:

http://www.mysql.com/training/certification/candidates.php

Also new is that you can now find some statistic figures. You can see how many have passed each individual Certification and on the detail page you also find how many people have passed this particular Certification in each specified country. So you can for example easily say: I'm one of eight Certified MySQL 5.0 DBAs in Germany.

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 ;-).

db4free.net runs dev source version again (5.1.12)

It's three months ago since MySQL 5.1.11 was released and many things have changed since then. Just watch the Change Log for 5.1.12 - I believe it's one of the longest ever:

http://dev.mysql.com/doc/refman/5.1/en/news-5-1-12.html

But I've found another nice and interesting thing: watch this bug report. This says that it's now allowed to do many more things in Prepared Statements inside Stored Procedures:

SHOW BINLOG EVENTS
SHOW (MASTER | SLAVE) STATUS
SHOW (MASTER | BINARY) LOGS
SHOW (PROCEDURE | FUNCTION) CODE (parsable only in debug builds)
SHOW CREATE (PROCEDURE | FUNCTION | EVENT | TABLE | VIEW)
SHOW (AUTHORS | CONTRIBUTORS | WARNINGS | ERRORS)
CHANGE MASTER
RESET (MASTER | SLAVE | QUERY CACHE)
SLAVE (START | STOP)
CHECKSUM (TABLE | TABLES)
INSTALL PLUGIN
UNINSTALL PLUGIN
CACHE INDEX
LOAD INDEX INTO CACHE
GRANT
REVOKE
KILL
(CREATE | RENAME | DROP) DATABASE
(CREATE | RENAME | DROP) USER
FLUSH (TABLE | TABLES | TABLES WITH READ LOCK | HOSTS | PRIVILEGES |
LOGS | STATUS | MASTER | SLAVE | DES_KEY_FILE | USER_RESOURCES)

Ain't that nice? This gives a lot more flexibility to dynamic SQL! (I know some guys who will have a lot of fun with that ;-))

This was reason enough for me to update the MySQL 5.1 server (on Port 3307) on db4free.net to this latest Development Source version. So you can try out all the nice enhancements that have been introduced in the last three months.

Enjoy!

MySQL Playground for Download

I have now uploaded my MySQL Playground and you can get it from

http://www.db4free.org/mysql-playground.tar.gz

The size of the file is 1 GB, so please take into account that it might take some time to download the file.

After download, just extract it to your "Virtual machines" directory:

tar xvfz mysql-playground.tar.gz

... and load it into your VMWare Server (should work for VMWare Workstation, VMWare Player too, I guess). I have assigned 768 MB RAM to this machine, so depending on your total amount of memory available you might have to change this.

The virtual machine runs SuSE Linux 10.1 (32-bit). The root password is simply "root" and there's a normal user named "user" with password "user". The same for the three MySQL instances (MySQL 5.1.11 each): user "root" - password "root", user "user" - password "user". There's also a replication slave user names "slave" with password "slave". The MySQL servers are installed into directory /MySQL_51/server-x, x being 1, 2 and 3. They use port 3306 for the first server, 3307 for the second server and 3308 for the third server. The database "replication_db" is set up multi-master replication, so whatever you do inside this database on one of the servers should also happen on the other two servers.

Please tell me if I have forgotten some important piece of information.

Feel free to play around with it and if you create other nice setups out of it, feel free to publish your versions.

MySQL GUI tools bundle replaces individual GUI tools

You may have noticed that you don't find any links for MySQL Administrator, MySQL QueryBrowser, MySQL MigrationToolkit and MySQL Workbench on dev.mysql.com anymore.

All these tools are now available in the new MySQL GUI Tools bundle and you can download versions for Windows, Linux and MacOS.

My new MySQL playground

The VMWare Server allows to do great things that you usually wouldn't like to do on your working machines, mostly because of the danger to damage something that can take a lot of time to recover. And it's also a great thing to play with advanced MySQL issues.

So I installed myself a "MySQL playground" - a VMWare machine running SuSE Linux 10.1 - and installed three MySQL 5.1.11 servers (placed in different directories and assigned the ports 3306, 3307 and 3308). The first thing that I played with was to set up a multimaster replication example, based on Giuseppe Maxia's article: Advanced MySQL Replication Techniques (I planned to do this for quite a while and now I finally did it!).

Giuseppe's article is great and makes this a very straight-forward task. The only thing I'd like to mention (because I overlooked it and lost a bit of time) is that if you run more than two servers, you absolutely need to add the log-slave-updates option to the [mysqld] section of your my.cnf files. Otherwise your actions will be replicated only to the next slave, but since this one wouldn't write these actions to the binlog, it would not be replicated to the other servers as well.

Just try it out - get yourself a virtual machine, install a few MySQL instances and play around with them. This can be a lot of fun and of course very instructive!

I'm not sure whether there are any legal issues distributing VMWare machines (I don't think so, but the VMWare server is proprietary software so I'm not completely sure)? If there aren't any I would be happy to offer my playground for download.

A new idea?

Since we are currently speaking a lot about new ideas, maybe there is one.

MySQL provides a lot of Live Web Seminars being offered as On-Demand Webinars a few days later. This is a great offer - it provides presentations from experts that everybody can join in and watch or download as soon as they are provided on the On-Demand Webinar pages - all for free.

During the Users Conference, there were some "HackFests" where developers showed how to extend functionality in code right in front of the attendees.

Maybe this could be combined with Webinars? There are many topics that can be presented perfectly from code, from the command line or from the desktop, better than from conventional presentations. Some examples?

* How to set up a replication environment
* How to set up MySQL cluster
* How to use the MySQL Test tools
* How to create backups, showing different options
* Performance tuning
* How to get debugging information for bug reports
* How to enable plugins
* and so on, and so forth, ...

Personally, I need easy ways to get into a feature or a technique. Once I jumped over the first barriers, it's not that difficult anymore to hook in and get involved more deeply. But the most difficult part is the beginning and the lower the barriers to get started are, the easier it gets to enhance the skills.

This includes many benefits for MySQL, too. When people get an easy way to learn how to use such things, they will use it which will in return provide better feedback for MySQL.

Maybe there are technical issues that make it difficult to present Webinars like this at the moment - but hey ... this should not be a reason not to think about it ;-).

Supporting ideas and being productive

Hey, that's a great topic and I very much like Zack's article and the responses (thanks Roland, Mats ... did I forget someone?).

Some time before I joined MySQL I worked for a local company (which is the biggest company in the region where I live) and I could perfectly see how it should not be. There were (and still are) very strong hierachies and if you are in the low areas of the hierachy structure, the management only wanted you to "work on something". The last thing they wanted was that employees speak up and shake on the structures of the company. No criticism was appreciated, employees should just do their daily work and even if they do it in a very inefficient way - that's perfectly fine.

The worst thing you could do was to make your work more efficient to get yourself a little time for breaks or for other (even educational) activities. So for example - if you stupidly entered numbers into an Excel spreadsheet for 4 hours, it was perfectly OK. If you found a way to automatically import the same data in half an hour and you allowed yourself a little coffee break so that everything together summed up to let's say 45 minutes - you were labeled lazy (speaking of coffee, cigarette or whatever breaks - I even consider them important to get your head free again and keep a distanced look at your work, so many of the best ideas arise during breaks!). The management just wanted to see you sit in front of your PC and do something that's somehow related to your work, but never do anything else. Needless to say that everyone was frustrated, everybody was working just to get paid and hardly anyone had ambitions to make things better (and those who did soon ended up as candidates to be fired).

Such conservative views are the worst idea killers. Work is done most efficiently if you like what you do and if you can identify with your work and your company. That doesn't even mean that you have to be the best skilled expert on your job. If you like what you do and if you like what your company does, you will look for good solutions and try to give the best you can. By looking for the best solutions, this individual will learn and become better skilled anyway. But if somebody isn't motivated to look for good solutions, there's no chance to learn from finding new ideas.

Being productive doesn't work without a certain amount of freedom and it definitely requires a pleasant atmosphere in your daily working life.

MySQL tries to find new employees who are already dedicated to MySQL's products and databasing in general, providing the best chances that new employees are motivated to create improvement. Most employees work from their home office which gives an employee the possibility to set up the working environment according to personal needs. Working times are more flexible - people work best at different hours, so everyone can find the working times that suit him/her best (e.g., I'm a completely night person, I could not find a company that allows me to work during night somewhere around my place). And being an Open Source company which represents open mindedness by definition makes pretty much sure that ideologies that I found in my former company don't have a chance.

Just see this - no manager from my former company would never have written or said something like Zack in his article.

I have seen two very different ways and it's easy to see which one works much better ;-).

PHP 5 Certification exam available soon

A new and updated version of the Zend PHP Certification is on the way and will be available starting August, 31st:

Zend PHP 5 Certification Page

It's a complete rewrite, but the exam will still (as with the PHP 4 exam) cover 70 questions in 90 minutes. A new Study Guide will be available starting September and current PHP 4 certified people will get a discount for the PHP 5 exam.

To get detailed information, you can also listen to the Webcast Recordings at the phparch.com website.

And for those who prefer taking a MySQL 5 certification, there's also information available for you :-).

Too many connections

Here's another good rule about using MySQL in your application:

Never let your application access the MySQL server with a user that has SUPER privileges (at least unless there's a VERY good reason to do it).

There may be more reasons for that but one is that it might stop your root user from accessing the server if all allowed connections are being used (the "Too many connections" error).

Here's what the manual says:

"mysqld actually allows max_connections+1 clients to connect. The extra connection is reserved for use by accounts that have the SUPER privilege. By granting the SUPER privilege to administrators and not to normal users (who should not need it), an administrator can connect to the server and use SHOW PROCESSLIST to diagnose problems even if the maximum number of unprivileged clients are connected."

But if your application user also has SUPER privileges, this might have used that one extra connection that should be reserved for that purpose.

A good application starts with good database design

... and most important - good use of SQL!

That doesn't sound surprising, I guess.

Recently, Giuseppe Maxia provided a good hint (An ugly query should be a warning bell) how to get an indicator that something is wrong with your query. I would like to add a hint to help you find out that something is wrong with the way you use your database inside your application. Just ask yourself the following question:

How much of my client side code is there just to get the data that I got from the database into the form that I need in my application?

Your answer should be: very little.

In most cases, SQL provides the capabilities to get out of the database exactly what you need. Needless to say that it's most important to normalize your data. It's not a bad sign if you end up with complex queries and joins that bind together several tables (just take care that your queries don't look like Giuseppe's negative example ;-)). Place the correct indexes to make sure that these queries still perform well when there are thousands and millions of records in the tables. There are many rules about good database design and they exist for a good reason.

But the message of my words shall be: don't waste hundreds of client side code lines to do what one SQL query can do!

Current activities and plans

It's very important for me to keep closely in touch with MySQL's products and all the fun stuff that comes with working with databases which is also a good way to keep in memory what the background of my job and our company is. A job is much more fun if you know and use the products that the company you work for produces instead of only doing it to get paid (that's something I also know well from local companies I worked for earlier - that makes a huge difference). Because of this, I never want to lose the fun side that comes with Community activities which involves to sometimes simply play and experiment with various things that come to my mind and write about that.

Unfortunately I found a little less time recently for Community related activities than the months before, but there are plans which I hope will bring that back to what it used to be. During the next months I plan to reorganize my working environment and my PC infrastructure which involves setting up a new office. My current office is quite a mess and now during the summer heat it gets unpleasantly hot here, so I'll move downstairs where a nice, quite large room is free and where it doesn't heat up as much as upstairs, so I can setup my new working place exactly according to my needs which will certainly raise my productivity for both my job as well as my Community work.

The reorganization of my PCs includes to start using MySQL 5.1 as my production system (besides moving many things that still run under Windows to Linux) - which will certainly bring up new topics to blog about. The same is planned for db4free.net - until autumn I'd like to move everything to one single MySQL 5.1 server (probably set up a second server as replication slave to provide better security) - so there will be a large amount of new 5.1 users who will contribute in testing 5.1 in production and hopefully provide valuable feedback to us.

So there's definitely a lot to come ;-).

Forum navigation now available!

Many people have complained that the new MySQL Forum misses an appropriate navigation. Now it's back:



Stay tuned - more enhancements are to come!

mysqldump improvements

Since the last few versions and especially since 5.0.23, the mysqldump command includes new and very important bug fixes.

I'd like to mention three of them that I was affected by:

Bug 16878 (fixed in 5.0.19, 5.1.8): Dump of triggers
Bug 17201 (fixed in 5.0.23, 5.1.12): mysqldump sometimes creates database twice
Bug 18462 (fixed in 5.0.23): mysqldump does not dump view structures correctly

New GUI Tool bundle available

You may have noticed the new link on dev.mysql.com to the MySQL GUI Tool Download.

This bundle includes new beta versions for MySQL Administrator, MySQL QueryBrowser, the MySQL MigrationToolkit and a new alpha version of MySQL Workbench. All these GUI products are supposed to be offered in one single package in the future.

To get all details, read Mike Zinner's Announcements in the forum:

http://forums.mysql.com/read.php?108,100559,100559#msg-100559
http://forums.mysql.com/read.php?108,100561,100561#msg-100561

Needless to say, Feedback and Bug Reports are very much appreciated ;-)!

Installed MySQL 5.2 today

You think I'm kidding? No way!

There is this web page (I blogged about it a few months ago) where you get an interface to BitKeeper to watch the development activities: http://mysql.bkbits.net:8080/mysql-5.1/index.html.

Just for fun I replaced 5.1 with 5.2 and - it worked. So I followed the instructions from the manual and only replaced 5.1 with 5.2 again. This way, I ended up with a MySQL 5.2.0-alpha installation:

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

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

mysql>

I doubt that there are many (if any at all) differences to the current 5.1 development source - but it's nice to see that MySQL 5.2 is on the way.

And I'm really excited to hear about the new features that are planned for 5.2!

Welcome on board, Roland and all the best to Frank!

I have been knowing that Roland Bouman is about to join 
Carsten Pedersen's MySQL Certification team for quite a while, 
but now - as of 1st July - it's official and time to wish him all the best 
and much fun in his new job!

Also Frank Mash has recently moved to New York and started
a new job as MySQL DBA (I hope, I remember everything
correctly). Also my best wishes for him!

MySQL not only rocks as a database server, but also for great job
opportunities! And the best thing is - MySQL is currently hireing ...
so check out MySQL's job page - there might be the right job for you!