PART 1 – This will be a multi part post.

THIS DOCUMENT IS BEING UPDATED – PLEASE WATCH FOR CHANGES!

After years of supporting MySQL, for many different companies, I’ve seen this story played out again and again.
The company:

• chooses a Database Management System (MySQL)
• installs the DBMS on a computer with other processes
• writes many programs to access the data (Without concern on how the queries are written.)
• moves DBMS to a computer of its own and writes more programs
• buy bigger computer to run the DBMS and writes more programs
• tires of DBMS response times and outages caused by developers working on production systems and hires a Database Administrator to fix the mess

This is a step by step description of  how I build a highly available, production MySQL servers. Like most things it life, these problems …

MySQL keeps many different files, some contain real data, some contain meta data. Witch ones are important? Witch can your throw away?

This is my attempt to create a quick reference of all the files used by MySQL, whats in them, what can you do if they are missing, what can you do with them.

When I was working for Dell doing Linux support my first words to a customer where “DO YOU HAVE COMPLETE AND VERIFIED BACKUP?” Make one now before you think about doing anything I suggest here.

You should always try to manage your data through a MySQL client.  If things have gone very bad this may not be possible. MySQL may not start. If your file system get corrupt you may have missing files. Sometimes people create other files in the MySQL directory (BAD).  This should help you understand what is safe to remove.

Before you try to work with one of these files make sure you have the file permissions set …

WHAT TIME IS IT?

This post started with a simple question: “Does the function NOW() get executed every time a row is examined?” According to the manual,  “Functions that return the current date or time each are evaluated only once per query …. multiple references to a function such as NOW() … produce the same result. …. (However,) as of MySQL 5.0.12, SYSDATE() returns the time (the row is) executes. “

• CURDATE() returns the current date.
• CURTIME() returns the current time.
• UTC_DATE() returns the current UTC date.
• UTC_TIME() returns the current UTC time.
• NOW() return the current date and time.
• UTC_TIMESTAMP() returns the current UTC date and time.
• SYSDATE() returns the date and …

In my business (weather) we use lots map based (Geo) information.  Almost every table has latitude and longitude. Working with this kind of data can be exciting and frustrating.  This should give you a quick start into GIS with MySQL.

“A geographic information system (GIS), or geographical information system, is any system that captures, stores, analyzes, manages, and presents data that are linked to location. In the simplest terms, GIS is the merging of cartography, statistical analysis, and database technology. GIS systems are used in cartography, remote sensing, land surveying, …

MySQL seems to be happy to convert types for you. Developers are rushed to complete their project and if the function works they just move on. But what is the costs of mixing your types? Does it matter if your are running across a million rows or more? Lets find out.

Here is what the programmers see.

mysql> select 1+1;
+-----+
| 1+1 |
+-----+
|   2 |
+-----+
1 row in set (0.00 sec)

mysql> select "1"+"1";
+---------+
| "1"+"1" |
+---------+
|       2 |
+---------+
1 row in set (0.00 sec)

Benchmark

What if we do a thousand simple loops?  How long does the looping itself take?

The BENCHMARK() function executes the expression expr repeatedly count times. It may be used to time how …

Your searching for how to create a join across two databases on two different servers and it can’t be done directly.   select  d1.a, d2.b from db1@server1 join db2@server2 where db1.c = db2.c; does not work.

You learn about federated databases.  The federated storage engine allows accesses data in tables of remote databases.  Now how do you make it work?

1) Check if the federated storage engine is supported.  Federation is OFF by default!

mysql> show engines;
+------------+---------+----------------------------------------------------------------+
| Engine     | Support | Comment                                                        |
+------------+---------+----------------------------------------------------------------+
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     |
| MyISAM     | DEFAULT | Default engine as of …

MyTOP is a console-based (non-gui) tool for monitoring the threads and overall performance of a MySQL.

UPDATE – I just fond Jeremy did update MyTOP in 2009 and released it on GitHub.  He fixed the 64x and 5.x bugs. He also incremented the version number to 1.7.  So, I’m bumping my number to 1.8.

Jeremy D. Zawodny <Jeremy@Zawodny.com> wrote the original in 2000 and has continued to update it until 2007. The 1.6 version works on MySQL up to version 4.x.

For weeks now and I’m been working on bringing it up to date.  When I started using version 1.6 it worked but didn’t return some data fields.  After …

MySQL Backup Script has been around for a long time.  I have used it on and off for years but now I’ve needed to make some  improvements.   This script is based on VER. 2.6 – http://sourceforge.net/projects/automysqlbackup/ Copyright (c) 2002-2003 wipe_out@lycos.co.uk.
I have added my own Copyright (c) 2010 mark@grennan.com – http://www.mysqlfanboy.com/Files/automysqlbackup.sh. But as the code says:  This program is distributed in the hope that it will be useful,  but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU General Public License for more details.

My improvements include:

# VER 2.6 Beta 5 – MTG – (2010-04-18)
#    …

I get ask, by application developers,  “how do you optimize MySQL”.  I do lots of things that don’t really relate to a developer. I analyze the percent of queries are being pulled from cache for instance.  What a developer can do to optimize the SQL they develop is a different questions.   So here is a quick list of things applications developers should know about MySQL.

Explain will analyze your query.

This example shows the possible indexes (keys) that could be used and the index that was selected.  2,262 rows where selected and then sorted (Using file sorts) and one record was returned (limit 1).

mysql> explain SELECT 5/9*(temp_F-32) as t, 5/9*(dewpt_F-32) as td, speed_mps as spd, dir
 > where stn='KLDM' and date_time<'2010-02-12 18:15' and …