cPanel MySQL Tuning
The default configuration for MySQL provided by cPanel is pretty barren. /etc/my.cnf
[mysqld] skip-innodbThats 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.
[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] Manually reset a Wordpress password
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[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
mysql -uThen,
GRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY '[A PASSWORD]'Exit and you're done.
Getting mytop to work on CentOS/Redhat
Screw all these AJAXified, buzz-wordified MySQL monitors. I'm old school. I use the fantastic mytop tool Jeremy Zawodny created 9 years ago.
Unfortunately it won't run on CentOS. I got the error, 'Error in option spec: "long|!"'
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.
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.
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.
Reset MySQL password on Ubuntu in about 1 second*
* It may take a little longer depending on your server and finger speeds!
dpkg-reconfigure mysql-server-5.0
MySQL Tuning Server Parameters
These are some of the handy MySQL 5.0 tweaks I do to get the most out a MySQL database;
I adjust the following to suit my server, I try to give MySQL as much resource as possible.