Here’s a quick and easy way to enable the MySQL General Log, to log all queries sent to the database. Great for debugging.
Open up a MySQL console and input the following. Change the log file location to suit. Continue reading “Enable General MySQL Log without a restart”
MariaDB is an enhanced, drop-in replacement for MySQL – best of all, nobody from Oracle phones you trying to sell you a premium version!
I see many applications moving over to soon when Oracle steps up it’s marketing, after all, I assume that is why Oracle bought MySQL.
Here’s how I replaced MySQL with MariaDB on cPanel/WHM. Continue reading “How to replace MySQL with MariaDB in 11.36”
Pssst – There’s an updated version of this post – OpenCart is slow with many categories – 2013 Edition
There is post after post after post on the OpenCart forums regarding OpenCart being slow with many categories. Its quite disappointing with such a promising product.
I only found this out after importing 2,400 categories for a customer. I wrote two scripts to migrate products and categories from a legacy application to OpenCart. I got as far as importing the 2,400 categories and 16 products when the site ground to a halt. Front and back end load times increased to over 20 seconds. I gave up migrating the other 5,000 products, what was the point until I solved this slow issue. Continue reading “OpenCart is slow with many categories”
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.
Continue reading “MySQL 5.0 or 5.1 to 5.5 Upgrade Traumas on CentOS”
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.
Continue reading “Upgrading to MySQL 5.1 in cPanel/WHM”
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.
Select check / -auto-repair or optimize
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.
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.
Continue reading “Installing nginx & PHP-FPM securely on CentOS”
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
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 Continue reading “MySQL Server on CentOS, what the RPM doesn’t do for you”
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. Continue reading “[Snippet] Simplified UK/US Date Conversion”
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.
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.” Continue reading “[Snippet] Tuning / Tweaking MySQL”