Skip to main content

MySQL 5.x – Finally improved client

Looking at my favorite rss feeds today, I found this post on great MySQL Performance Blog:

…if you press CTRL-C MySQL Command Line Client will not exit but will terminate query being executed.

In other words, in previous versions of MySQL client program, if you issue a query and try to interrupt it by hitting CTRL-C, CTRL-C would actually kill MySQL client itself, but query still continue running in background! In this case the only solution to really kill that query is to find it’s ID on process list (by issuing “show full processlist” query), and then to kill it with a query like “kill 12345”, where 12345 is ID of query that you want to be killed. In other words, something like this:

mysql> select * from odm_result_keywords where keyword like '%foo%joe%';
^CAborted
bash-2.05b$ mysql -A --enable-local-infile -udinke -ppass mydb
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1512 to server version: 4.1.18-log

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

mysql> show full processlist;
+------+-------+----------------------------+-------------------+---------+------+--------------+--------------------------------------------------------------------+
| Id   | User  | Host                       | db                | Command | Time | State        | Info                                                               |
+------+-------+----------------------------+-------------------+---------+------+--------------+--------------------------------------------------------------------+
| 1486 | dinke | localhost                  | mydb | Query   |    3 | Sending data | select * from odm_result_keywords where keyword like '%foo%joe.cl' |
+------+-------+----------------------------+-------------------+---------+------+--------------+--------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> kill 1486;
mysql>

Thanks to changes in MySQL client program, all you have to do now is to hit CTRL-C, and query will be stopped immediately:

mysql> select domain from odm_result_keywords_de where whois_status is null and domain like '%.%.%';
Query aborted by Ctrl+C
ERROR 1317 (70100): Query execution was interrupted
mysql>

For more information about this feature (as well as other changes in MySQL 5.0.25) please follow this link.

MySQL – Backup of Big MyISAM Tables

If you ever dealt with backup of MySQL tables, you probably used mysqldump utility, which allows you to dump all data into some mytables.sql file, which you can backup somewhere, import to other MySQL server etc. I used this procedure for a thousands times so far, and generally it goes as follows:

export:

mysqldump -udinke -pmojpass -hhostname.of.server1 dbname table1 table2 tableN > dump_file.sql
mysql -udinke -pmojpass -hhostname.of.server2 dbname  < dump_file.sql

Sometimes there is a problem when you move data from new version of MySQL to old, and in that case you have to specify proper compability flag when running mysql dump (--compatible=name where name can be mysql323, mysql40, postgresql, oracle etc.).

Anyway, few days ago in order to move data from one MySQL to another, I had to dump some ... let say big mysql tables (about 10 tables, where each contained about 10 millions of records). After long lasted procedure (dump to file, gzip, scp to other server) I finally started import. However, after 3 hours (yes, three hours) instead of Linux prompt I got this:

[dinke@um-917 ~/public_html]$ mysql -udinke -p325ewfwt23rasf
keyword_discovery < es_miner_data.sql
ERROR 1582 (23000) at line 163833: Duplicate entry '1167548' for key
'PRIMARY'

WTF? I moved data from old server to new, which means import should run without compability problems. It could be indexes on old table were damaged, but instead to wait like 2 more hours in order to complete check & repair procedure, I've decided to create dump file, this time with ignore option, so all insert queries in dump file are "insert ignore", so in case of error like previous one, errors will be ignored. Not very smart, but those data are not really high sensitive, and I can afford to lose few records but can't afford to lose 10 hours for import!

So, dump, gzip, scp, import again ... which lasted long... loooooong .... so fucking long that after 3 hours after I started import I started to think about other solutions. And solution was dumb but effective. We simple moved all MySQL data files (*.MYI, *.MYD i *.frm) from one server to another, and then we run myisamchk in order to fix those tables because ... we didn't shutdown MySQL server during copy procedure which generally could cause some problems with data.

All in all, this procedure went very fast (the longest was actual copy from one host to another), and in less than half hour I got everything settled down.

At the end I came with conclusion that in case of really big tables, using tool like mysqldump is unaccepted as backup solution because it takes literally hours to complete. One of solution to that problem can be to copy MySQL data files like I did. That shouldn't be a problem, because tables are "platform safe" meaning, binary file created on one platform (ie *.MYI file on Linux) will work without problem when moved to other problem (ie. on Windows). The only one real problem is that if you copy data wihout MySQL shutdown, table files there are in some kind of "state of flux" which can be a problem, especialy if they are highly used in moment when you do copy. That's why you need to do myisamchk on it.

Introduction to GeoIP

In case you’ve ever used Google Analytics or any simmilar tool where you can see exact location from where visitors of your site came from, you’ve probably wondered how they were able to dig that info. Is it magic or what? Of course, it’s not kind of magic, exact location of visitor is defined by visitor’s IP address, and technology used to locate user by his IP is well known as GeoIP.

Today we are going to look how to locate visitor of your site with PHP and Max Mind’s GeoIP database. In examples bellow we used free(lite) versions of GeoIP databases, because fully supported GeoIP databases are not free(you’d have to pay $50USD setup + $12USD update for GeoIP Country and $370USD + $90USD for GeoIP City base). Drawback of lite version is that it is not as accurate as fully supported GeoIP databases, but it is still very usefull and probably good enough for great majority of live projects.

MaxMind offer API for dozen of programming languages (full list is available here), details about PHP API are available here. This tutorial deal with so called “Pure PHP API”, there are also PECL extensions and apache mod_geoip modul available. Apache modul provide better perfomance, but Pure PHP API is easier to set up.

Just for a start let’s download all PHP API files from http://www.maxmind.com/download/geoip/api/php/, and save them somewhere inside of your Web tree(let say /htdocs/geoip). To use GeoIP Country you need to download lite database from here, and for GeoLiteCity download database from here. Just for the sake of simplicity, we are going to unpack both bases to the same dir where we saved our PHP API’s files (/htods/geoip in our example).

GeoIP Country
——————————–
Now, let’s see how country detection works:

<?php
/**
 * GeoIP Country Database Example
 *
 * @version $Id$
 * @package geoip
 * @copyright © 2006 Lampix.net
 * @author Dragan Dinic <dinke@lampix.net>
 */

require_once("geoip.inc");

$gi = geoip_open("GeoIP.dat", GEOIP_STANDARD);

$ip = $_SERVER['REMOTE_ADDR'];
//if you test on localhost use IP bellow for test
//since $_SERVER['REMOTE_ADDR'] would be 127.0.0.1
//$ip = "89.216.226.174";

$country_name = geoip_country_name_by_addr($gi, $ip);
$country_code = geoip_country_code_by_addr($gi, $ip);
if($country_name)
{
	echo "Your country is: $country_name <br />";
	echo "Country Code is: $country_code <br />";
}
else
{
	echo "Sorry, we weren't able to locate you.";
}

geoip_close($gi);
?>

So, at the beggining we’ve included geoip.inc which contains all functions needed to use GeoIP country database, then we’ve created new instance of GeoIP class with geoip_open function, and at the end we called proper functions(geoip_country_name_by_addr and geoip_country_code_by_addr) to get country name/code in which detected IP address reside. Again, in case you test localy, don’t use $_SERVER[‘REMOTE_ADDR’].

When you run script above you should get something like this as output:

Your country is: Serbia and Montenegro
Country Code is: CS

GeoIP City
—————————-
Now, let’s extend visitor’s country data with exact location(like city, postal code etc.)

<?php
/**
 * GeoIP City Database Example
 *
 * @version $Id$
 * @package geoip
 * @copyright © 2006 Lampix.net
 * @author Dragan Dinic <dinke@lampix.net>
 */

require_once("geoipcity.inc");

$gi = geoip_open("GeoLiteCity.dat", GEOIP_STANDARD);

$ip = $_SERVER['REMOTE_ADDR'];
//if you test on localhost use IP bellow for test
//since $_SERVER['REMOTE_ADDR'] would be 127.0.0.1
//$ip = "89.216.226.174";

$record = geoip_record_by_addr($gi, $ip);

if(!$record)
{
	echo "Sorry, we weren't able to locate you.";
}
else
{
	echo "Country: " .$record->country_name . "<br />";
	echo "Country Code: " . $record->country_code . "<br />";
	echo "Country Code 2: " . $record->country_code3 . "<br />";
	echo "Region: " .$record->region . "<br />";
	echo "City: " .$record->city . "<br />";
	echo "Postal Code: " .$record->postal_code . "<br />";
	echo "Latitude: " .$record->latitude . "<br />";
	echo "Longitude: " .$record->longitude . "<br />";
	echo "DMA Code: " .$record->dma_code . "<br />";
	echo "Area Code: " .$record->area_code . "<br />";
}

geoip_close($gi);
?>

As you see, PHP code is simmilar as in our country detection example, with exception that we used geoipcity.inc and GeoLiteCity.dat database. Function geoip_record_by_addr($gi, $ip) return instance of ‘geoiprecord’ class which contains in it’s properties location’s data we used in our example. After you run script you should get output like this one:

Country: Serbia and Montenegro
Country Code: CS
Country Code 2: SCG
Region: 02
City: Beograd
Postal Code: 11000
Latitude: 44.8186
Longitude: 20.4681
DMA Code:
Area Code:

CaseStudy – Redirection depending of Country
————————————————————–
At the end, we are going to see some real GeoIP usage. Our goal is to redirect users on multy language site(blog) to proper language section on the site depending of their location. Here is how code looks like on my own blog:

<?php
/**
 * Case Study - GeoIP Redirection
 *
 * @version $Id$
 * @package geoip
 * @copyright © 2006 Lampix.net
 * @author Dragan Dinic <dinke@lampix.net>
 */

require_once("geoip/geoip.inc");

$gi = geoip_open("geoip/GeoIP.dat",GEOIP_STANDARD);

$country_code = geoip_country_code_by_addr($gi, $_SERVER['REMOTE_ADDR']);

geoip_close($gi);

if($country_code == 'CS')
{
        header("HTTP/1.1 301 Moved Permanently");
        header('Location: http://www.dinke.net/blog/sr/');
}
else
{
        header("HTTP/1.1 301 Moved Permanently");
        header('Location: http://www.dinke.net/blog/en/');
}
?>

Above example is used on this blog in order to redirect all users located out of Serbia to english version of the blog. Sending custom 301 redirection headers is important so bots(like google etc. google) are able to index blog pages without problems.