kieranbarnes Independent PHP, WordPress & CubeCart Programmer

MySQL 5.0 or 5.1 to 5.5 Upgrade Traumas on CentOS

Posted on January 7, 2012

Ignoring all the panic-mongers on the rest of the internet upgrading MySQL from 5.0 or 5.1 to 5.5 shouldn't be that difficult. My task was to upgrade from 5.0 to 5.5 on CentOS (Remi Repos). It was to fix a bug with MySQL 5.0 ignoring the wait_timeout parameter in my.cnf.

Five to five on a Friday, I dive into the swamp, which turns out to be full of alligators.

Alligator 1: Version depenencies
Alligator 2: /usr/libexec/mysqld: unknown option '--skip-locking'
Alligator 3:  [ERROR] Error message file '/usr/share/mysql/english/errmsg.sys' had only 481 error messages,
but it should contain at least 641 error messages.
Or some other variation of numbers.

Upgrading to MySQL 5.1 in cPanel/WHM

Posted on August 8, 2011

Upgrading to MySQL 5.1 in cPanel is currently not the easiest thing in the world. I've seen guides detailing disabled various cPanel updates, hacking cPanel scripts and even installing your own 5.1 RPMs, all of which will probably end in tears at some point when cPanel runs one of its update scripts.

Heres the easiest and safest method I've found.

Repair and optimize all tables in all MySQL databases

Posted on July 14, 2011

MySQL tables can crash or become corrupt quite easily if you don't shut down the server correctly, for example hard reboot or power outage.

Thankfully there is a quick way of repairing all the tables.

  1. mysqlcheck --auto-repair --check --optimize --all-databases

You may need to specify the username and/or password -u or -p

See the mysqlcheck docs for more information.

Filed under: MySQL No Comments

Installing nginx & PHP-FPM securely on CentOS

Posted on March 9, 2011

I won't go into the reasons why you should install nginx, if you're here, you've already decided.
Here's how I installed nginx and PHP-FPM on CentOS.

[Snippet] Simplified UK/US Date Conversion

Posted on March 4, 2011

One of my major annoyances in PHP & MySQL world is the different formats when working with dates.

US format: mm-dd-yy, UK format: dd-mm-yy and MySQL's date,datetime, timestamp columns yy-mm-dd. How do you cope with them all? I've been working on a rich UI recently which includes the jQuery date picker, by default thats in US format. UK users expect a date in UK format.

Tagged as: , , Continue reading

[Snippet] Tuning / Tweaking MySQL

Posted on November 20, 2010

There are a couple of good scripts out there that will help you to tune your mysql usage for better perfomance.  I’ve used both of these, and they are very intuative.  Personally, I prefer the latter, however, I still use both for tweaking.

tuning-primer

OK, so not the most original blog post ever, but useful all the same.
“This script takes information from “SHOW STATUS LIKE…” and “SHOW VARIABLES LIKE…”
to produce sane recomendations for tuning server variables.”

MySQL Root Password on cPanel

Posted on November 4, 2010

Wheres the MySQL root password in cPanel installs? Easy, look in /root/.my.cnf

Filed under: cPanel/WHM, MySQL No Comments

MySQL: Can’t get hostname for your address

Posted on October 28, 2010

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

Posted on August 3, 2010

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

  1. [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.

  1. [mysqld]
  2. max_connections = 200
  3. bind-address = 127.0.0.1
  4.  
  5. safe-show-database
  6. skip-locking
  7. skip-innodb
  8.  
  9. # MySQL 4.x has query caching available.
  10. # Enable it for vast improvement and it may be all you need to tweak.
  11. query_cache_type=1
  12. query_cache_limit=1M
  13. query_cache_size=32M
  14.  
  15. interactive_timeout=100
  16.  
  17. # Reduced wait_timeout to prevent idle clients holding connections.
  18. wait_timeout=15
  19. connect_timeout=10
  20.  
  21. # Checked opened tables and adjusted accordingly after running for a while.
  22. table_cache=512
  23.  
  24. # Reduced it to 32 to prevent memory hogging.
  25. thread_cache=32
  26.  
  27. # Reduced it by checking current size of *.MYI files.
  28. key_buffer=128M
  29.  
  30. thread_concurrency=1
  31.  
  32. log_error = /var/log/mysql/error.log
  33.  
  34. # log slow queries is a must.
  35. log_slow_queries=/var/log/mysqld.slow.log
  36. long_query_time=2
  37.  
  38. [mysqldump]
  39. quick
  40. max_allowed_packet=16M
  41.  
  42. [mysql]
  43. no-auto-rehash
  44.  
  45. [isamchk]
  46. key_buffer=64M
  47. sort_buffer=64M
  48. read_buffer=16M
  49. write_buffer=16M
  50.  
  51. [myisamchk]
  52. key_buffer=64M
  53. sort_buffer=64M
  54. read_buffer=16M
  55. write_buffer=16M
  56.  
  57. [mysqlhotcopy]
  58. interactive-timeout
Tagged as: , No Comments
Page 1 of 212