The latest from the MySQL community

Better late then never, this week I finally have the chance to catch up on some overdue posts. At the first MySQL Camp I made a list of memorable Quotes, see Day 1, Day 2, Day 3. I didn’t get as much chance as last time, however here are some pearls of the recent MySQL Camp II.

“I walked in of the street for the free food. I’m here for the free education” - Adrian.

“I’m his boss, I’m here to make sure he’s really here, and not playing hooky.”

“Mashups, collating the worse bugs from multiple API’s all in one place” - OSCON badge

“Compiles 114,000 combinations of MySQL that we are interested in.” Sandro - Skoll Project — You mean to say there are …

I was asked a question today, “DATETIME vs TIMESTAMP. When to use which & why?”

It’s a good MySQL introduction question, here are some general considerations for choosing one.

Do you need Date values other then an EPOCH value? (i.e. before 1970) If the answer is yes, then DATETIME is required.
If you do not however, then TIMESTAMP is the best choice for a few reasons.

1. The TIMESTAMP columns uses 4 Bytes to record it’s value, while DATETIME uses 8 bytes. Using the smallest storage is always a best practice for all columns.
2. The TIMESTAMP column supports the CURRENT_DATE syntax in the CREATE TABLE command. This enables the column to have a default value for INSERT or for UPDATE, but not both. Indeed this is the only data type that allows for any default value that is not a constant.
3. All date functions (at least the ones I use) work equally as well with TIMESTAMP and DATETIME.

I …

MySQL Camp II is complete. A small group of about 18 had post dinner at Tiny Thai in New York City. Some elected to drive from Brooklyn, they arrived at least 30 minutes after those of us that the subway.

I have a lot of notes to write, if ever the time permits. For now, the following few that joined for drinks are below. I know other people took photos of the camp, for a change I actually took none. If you want to add a link in comments of photos from the camp that would be great.

Other sizes here

We have started MySQL Camp II. The first session is Introductions.

I didn’t catch all the employers, but here is part of the list of attendees at the Introduction section. Great to see multiple people from many places including ESPN, priceline.com, Proven Scaling, Solid Tech - sponsors of Dorsal Source, ForSaleByOwner. fontshop.com, 9Mmedia, CafeMom, JP Morgan, …

Well readers, your either here or your not. MySQL Camp II starts today in Brooklyn, New York, at Polytechnic University. Last night’s pre drinks meetup in NYC went well, but today it’s brass tacks time. View Larger Map

For those of you not able to make it, IRC@Freenode #mysql-camp will be the place to hang out to hear what’s happening. If your not at the camp, please identify yourself. Be sure to also check out the Camp Web Site MySQL Camp II for the plans for today and tomorrow.

For those of you not here, MySQL Camp III is already in planning.

I realized recently from observation that some smaller websites which use MySQL do not have a working backup and recovery strategy. Some may have a backup approach but it will not work in a recovery. As part of a number of discussions on Backup & Recovery here is part 1.

Using straight ‘cp’ for Backup

Using ‘cp’ to simply copy your running database *DOES NOT WORK*. You may be lucky, but in a world of guarantees, this is no guarantee that your can recovery your system. Why is this so.

• The data is not consistent during the backup. If it takes say 5 minutes to copy your files, they are copied probably in alphabetical order, what if data is written during the backup to a table starting with ‘A’ and a table starting with ‘Z’, the A file has already been copied.
• When using MyISAM, Indexes are not flushed to disk unlike Table data. This means that while MyISAM has the facility of recover and …

When you use SHOW STATUS can can restrict with the LIKE syntax, allowing for a subset of values. For example:

mysql> SHOW GLOBAL STATUS LIKE 'Com%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Com_admin_commands       | 0     |
| Com_alter_db             | 0     |
| Com_alter_table          | 0     |
| Com_analyze              | 0     |
| Com_backup_table         | 0     |
| Com_begin                | 0     |
| Com_change_db            | 0     |
| Com_change_master        | 0     |
...

That’s great, but sometimes you want specific values. Using WHERE can achieve this. For Example.

mysql> SHOW GLOBAL STATUS WHERE VARIABLE_NAME IN (?Com_insert?,'Innodb_buffer_pool_pages_latched?,'threads_running?);
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| Com_insert                       | …

Last night at the MySQL NY Meetup we continued on from a very successful July presentation on “Practical Performance Tips & Tricks”. I must admit after speaking and standing all day for the MySQL DBA Bootcamp for the Oracle DBA it was a stretch, and we didn’t cover all material as expected, but the evening was still very productive to everybody. Links are here for my August Presentation and July Presentation.

Thanks to Marc and the team from LogicWorks for again sponsoring our NY Meetup Event. We don’t get the beer and food any other way.

As a consultant …

We have all done it in the past, and probably most people that read this (will admit| or lie) to still doing it, but everybody must start making an effort to improving MySQL security in the usage on your MySQL Installation, including just on your laptop, and in presentations that people read.

I spotted a reference article on Planet MySQL this evening and without looking at the details the syntax presented typifies two basic and fundamental 101 MySQL security issues.

1. Always, always, always have a password for a MySQL account, especially for the ‘root’ user.
2. Don’t use the ‘root’ user unless you really have to. The SUPER privilege is just that SUPER, there are many things you really don’t want every person accessing to have. In a larger environment …

While using the latest MySQL 5.1.20 yesterday I came across another situation that was not expected as with previous editions of MySQL. The background is experimenting with DRBD. When I configured MySQL to startup with a /etc/my.cnf file with data on a DRBD partition I got a failed startup error message with mysqld_safe.

$ bin/mysqld_safe &
[1] 12615
070720 10:10:42 mysqld_safe Starting mysqld daemon with databases from /drbd/data
070720 10:10:42 mysqld_safe mysqld from pid file /drbd/data/newyork.localdomain.pid ended

Ok. Well this happens so I went to the data directory to look for `hostname`.err.

$ cd /drbd/data
$ ls -l

What the! There is no error log. Then the discussion started about this. Apparently mysqld_safe now uses syslog (e.g. /var/log/messages) for logging messages. Ok, but where is the line between mysqld_safe and mysqld. …