mySql Utility to Set Up a Slave Server, Unattended

This utility written by Kevin Burton is worth checking into - even though it is a first version and needs to be modded for your purposes (in my case I had to alter the local mysql connection string). However, in any case, this is going to be needed by us DBAs in the future, as we manage and build more and more slave servers.

This is what the script does, in Kevin's words:

This is a script to perform unattended cloning of MySQL slave servers (or masters) to put a new slave online with minimal interaction.

It connects to the source node, performs a mysqldump or mysqlhotcopy, transfers the data, restores the data, and then sets up all replication parameters, on the target machine.

It then starts the slave which then begins to catch up to the master

For DBAs that spend hours setting up slave servers, save yourself some time and check this script out!

Til next time,
David

Google + mysql = distributed fun!

Google seems to be everybody's favorite girlfriend these days. We might even replace the antiquated statement "Midas touch" with "googlitis", a condition you get when google looks in your direction. Your valuation goes up even if google does not become a successful bidder (MSN + facebook = $15 billion dollar valuation), and everybody wants to be your best friend, even if you are politely collecting data from them.

So the daytime soap continues on, this time in favor of mysql -- with articles like:
MySQL to get injection of Google code and
MsSQL getting Google's blessing...and code
where google documents their progress here.

Add to this an increasing acceptance (not just awareness) of open-source databases, and that makes for another exciting day...

Til next time,
David

IP Addresses: No more char(15)'s allowed!

OK... This is my second entry on this blog. And it is about something I want every mysql/php newbie to know at the starting gate:

DON'T USE CHAR(15) FOR IP Address fields in mysql!

Well... what about the periods in ip addresses, you say?

Well... mysql has a FANTASTIC way of solving this issue! Convert the standard ip address into an unsigned integer!

ok... at first it sounds a little cryptic, but say it at least 3 times before your morning coffee:

INET_ATON
INET_NTOA


loosely meaning....

INET_ATON = Internet address to number
INET_NTOA = Internet number to address

SO... try this:

mysql> SELECT INET_ATON('192.168.1.5');
+--------------------------+
| INET_ATON('192.168.1.5') |
+--------------------------+
| 3232235781 |
+--------------------------+
1 row in set (0.04 sec)

Now, let's reverse it!


mysql> SELECT INET_NTOA(3232235781);
+-----------------------+
| INET_NTOA(3232235781) |
+-----------------------+
| 192.168.1.5 |
+-----------------------+
1 row in set (0.01 sec)

Fun, eh?

Now... what are the savings on this, you ask?

On a 17 million row table consisting only of an auto_increment id (another must; don't leave home without an auto_increment id!!), non-indexed exact searches are about the same. In fact, the searches on the char column sometimes perform up to 10% faster.

After indexing, exact searches are once again the same, although they take a fraction of the time they did when they were not indexed. However, where the crown jewel shines is on ranges. On a count of an ip range, on the int based table, the cost was 3.78 seconds, but on the char(15) based table, the cost was 26.21 seconds.

Also -- the size of the int based data table (MyISAM) is 148M compared to 329M on the char(15) based table.

Are you convinced now? :) If so, now you know what you're going to do!

Until next time (whenever that will be),
David