MySQL: Can’t get hostname for your address

Can”t remotely login to your shiney, new MySQL server due “Can’t get hostname for your address” or “Can’t get hostname for your address”. Another random MySQL error message.

Simply put, if you look in the logs, MySQL cannot lookup your IP/hostname. Its that simple.

Is your DNS broke? Or just on a Local Area Network with no DNS anyway?
Just add skip-name-resolve option to the [mysqld] section of your my.cnf/my.ini

cPanel MySQL Tuning

The default configuration for MySQL provided by cPanel is pretty barren. /etc/my.cnf

[codesyntax lang=”bash”]
[mysqld] skip-innodb

Thats it. One line. Its rubbish.

Here’s mine to help you along the way. Its got some decent logging set, some decent cache and memory options for a shared server set and some helpful security settings enabled.

[codesyntax lang=”bash”]
[mysqld]
max_connections = 200
bind-address = 127.0.0.1

safe-show-database
skip-locking
skip-innodb

# MySQL 4.x has query caching available.
# Enable it for vast improvement and it may be all you need to tweak.
query_cache_type=1
query_cache_limit=1M
query_cache_size=32M

interactive_timeout=100

# Reduced wait_timeout to prevent idle clients holding connections.
wait_timeout=15
connect_timeout=10

# Checked opened tables and adjusted accordingly after running for a while.
table_cache=512

# Reduced it to 32 to prevent memory hogging.
thread_cache=32

# Reduced it by checking current size of *.MYI files.
key_buffer=128M

thread_concurrency=1

log_error = /var/log/mysql/error.log

# log slow queries is a must.
log_slow_queries=/var/log/mysqld.slow.log
long_query_time=2

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[mysqlhotcopy]
interactive-timeout

[Snippet] Allow mysql root access from anywhere

A small annoyance of mine is on local development servers, I can’t login to the MySQL server as root.

Simply connect the mysql shell

mysq[codesyntax lang=”bash”]
mysql -u

Then,

[codesyntax lang=”bash”]
GRANT ALL ON *.* TO ‘root’@’%’ IDENTIFIED BY ‘[A PASSWORD]’

Exit and you’re done.

SQLyog Query Profiler

MySQL has always lacked the sophisticated profiling tools shipped with proprietary databases like SQL Server, etc. MySQL developers have largely depended on EXPLAIN for tuning queries. The SHOW PROFILE patch by Jeremy Cole was introduced in the MySQL Community version 5.0.37 and it provided much more insight into where the query spends its time. However, to take advantage of this feature, MySQL developers were supposed to switch on profiling, run their queries and then filter the profiling data from a table that contained the profiling results of the last few profiled queries. A lot of manual book-keeping is required to take advantage of this powerful feature. In an ideal situation, the MySQL developer should execute the queries and the profiling info should be available along with the result-set. Unfortunately, none of the MySQL client tools (desktop or web-based) provide intrinsic support for this feature.

Watch the Screencast on Query Profiler.

I♥MySQL

Convert phpBB IP Addresses

phpBB stores its IP addresses in a funky format. They’re useless to the human eye!

Convert them to normal IPs with the following code

SELECT CONCAT( CONV( substring( poster_ip, 1, 2 ) , 16, 10 ) , '.', CONV( substring( poster_ip, 3, 2 ) , 16, 10 ) , '.', CONV( substring( poster_ip, 5, 2 ) , 16, 10 ) , '.', CONV( substring( poster_ip, 7, 2 ) , 16, 10 ) ) AS IP
FROM phpbb_posts
WHERE `poster_id` = USERID
LIMIT 0 , 30
This code will convert all know IPs from a selected user (In my case 51).

Retro Fitting Record Pagination with ADOdb

For my first project of the New Year I had to retro-fit pagination controls to an existing ADOdb powered web application for a client of mine. Looking back at my previous post on this topic, it was relatively simple to add the required features. Hers’s how I did it. Continue reading “Retro Fitting Record Pagination with ADOdb”

Yahoo Term Extractor

A recent project I was working on cause me to stumble over the Yahoo Term Extractor. Something I had previously never heard of – it is a very underrated tool.

The Term Extraction Web Service provides a list of significant words or phrases extracted from a larger content. Continue reading “Yahoo Term Extractor”