MySQL 5.0 or 5.1 to 5.5 Upgrade Traumas on CentOS
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
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
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.
- 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.
Installing nginx & PHP-FPM securely on CentOS
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.
MySQL Server on CentOS, what the RPM doesn’t do for you
Installing MySQL via YUM
Once the updates have finished we will be ready to install the MySQL server. Type the following command to install the MySQL server:
# sudo yum -y install mysql-server
Starting MySQL
Once the installation has finished we are ready to start our server for the first time. Upon initially starting it will create several test databases and the mysql system databases. To start the server type the following:
# sudo /etc/init.d/mysqld start
[Snippet] Simplified UK/US Date Conversion
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.
[Snippet] Tuning / Tweaking MySQL
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
Wheres the MySQL root password in cPanel installs? Easy, look in /root/.my.cnf
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
- [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.
- [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
About Kieran
