Wheres the MySQL root password in cPanel installs? Easy, look in /root/.my.cnf
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
The default configuration for MySQL provided by cPanel is pretty barren. /etc/my.cnf
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.
max_connections = 200
bind-address = 127.0.0.1
# MySQL 4.x has query caching available.
# Enable it for vast improvement and it may be all you need to tweak.
# Reduced wait_timeout to prevent idle clients holding connections.
# Checked opened tables and adjusted accordingly after running for a while.
# Reduced it to 32 to prevent memory hogging.
# Reduced it by checking current size of *.MYI files.
log_error = /var/log/mysql/error.log
# log slow queries is a must.
WordPress stores user passwords in MD5 checksums, so using your SQL admin tool of choice you can easily reset it.
UPDATE `wp_users` SET `user_pass` = MD5(‘NEW PASSWORD’) WHERE `wp_users`.`user_login` = “THE USER” LIMIT 1
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
GRANT ALL ON *.* TO ‘root’@’%’ IDENTIFIED BY ‘[A PASSWORD]’
Exit and you’re done.
Unfortunately it won’t run on CentOS. I got the error, ‘Error in option spec: “long|!”‘
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.
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
WHERE `poster_id` = USERID
LIMIT 0 , 30
This code will convert all know IPs from a selected user (In my case 51).
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”
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”