How To Upgrade MySQL from 5.5 to 5.7 on CentOS/AWS Linux

Author: , December 7th, 2017

START: Server version: 5.5.54-log MySQL Community Server (GPL) FINISH: Server version: 5.7.20-log MySQL Community Server (GPL) 1 2 3 4 5 6 mysql –execute="SET GLOBAL innodb_fast_shutdown=0;" service mysqld stop yum remove mysql mysql-* yum install mysql57-devel mysql57-server mysql57-test service mysqld start mysql_upgrade mysql –execute=”SET GLOBAL innodb_fast_shutdown=0;” service mysqld stop yum remove mysql mysql-* yum install […]

How To Fix MySQL Remote Login Errors

Author: , August 25th, 2016

In this case I had done a restore from a MySQL 5.1 server to a MySQL 5.6 server. That was not too smart, because it broke the mysql database table structures and prevented me from logging in remotely. Local login worked both via the socket and TCP. mysql -u root -p -h remoteHostName Enter password: […]

How To Convert from mysql_ to mysqli_ in WordPress Plugins Easily

Author: , August 24th, 2016

As PHP deprecates old functions, sometimes code maintenance changes become required for long-running sites. As of PHP 5.5, the MySQL functions are deprecated and are removed in PHP 7! I recently had to convert multiple sites to mysqli PHP functions because a new server was running PHP 5.6 and the old server was on PHP […]

How To See MySQL Traffic Using tcpdump

Author: , March 22nd, 2016

Quick-and-dirty way: tcpdump -i eth0 -s 0 -l -X dst port 3306 NOTE: That option above is a dash-ELL which provides for unbuffered output. Maciej Dobrzanski posted the following script on the Percona site at, which I am reposting without permission because it is so good: 1 2 3 4 5 6 7 8 […]

How To Select the Max Numeric in a Varchar Column in MySQL

Author: , October 2nd, 2015

I needed to automate the addition of new users with a sequential member’s ID in WordPress. Additionally, there were non-numeric entries in that column that had to be ignored. The wp_users.user_login column is a varchar(60) and so does not naturally handle numeric operations well at all. The solution is a combination of REGEXP and cast(): […]

How To Check Amazon RDS MySQL Servers in Nagios Without Ping

Author: , August 13th, 2015

Amazon Web Services (AWS) offers MySQL Server via the Relational Database Service (RDS) offering. Unfortunately, AWS doesn’t allow ICMP Ping to reach the nodes, so as configured by default, Nagios will fail on the host check. The solution is to submit a Passive Check for that host. Login to the Nagios web console Click on […]

How To Update the innodb_log_file_size Setting in MySQL

Author: , October 29th, 2014

[root@db1 ~]# service mysqld stop [root@db1 ~]# cd /var/lib/mysql [root@db1 mysql]# ls -l ib_logfile? -rw-rw—- 1 mysql mysql 5242880 Oct 29 23:00 ib_logfile0 -rw-rw—- 1 mysql mysql 5242880 Oct 29 22:38 ib_logfile1 [root@db1 mysql]# mv ib_logfile0 ib_logfile0.old [root@db1 mysql]# mv ib_logfile1 ib_logfile1.old [root@db1 ~]# vi /etc/my.cnf innodb_log_file_size=64M # comment out the old value, if one […]

How To Reset the MySQL root Password

Author: , December 31st, 2013

update mysql.user set password=PASSWORD(‘Your New Password Here’) where user=’root’;

How To Find the MySQL Config File

Author: , October 2nd, 2013

/usr/sbin/mysqld –verbose –help | grep -A 1 “Default options”

How To Force WordPress to Connect to the Database via TCP

Author: , October 2nd, 2013

wp-config.php 1 2 define(’DB_HOST’, ’′); define(’DB_PORT’, 3306); define(‘DB_HOST’, ’′); define(‘DB_PORT’, 3306);