Author:
erics, May 16th, 2018
SELECT t1.id FROM tableOne t1 LEFT JOIN tableTwo t2 ON t1.id = t2.id WHERE t2.id IS NULL The “WHERE t2.id IS NULL clause” restricts the results to only those rows where the id returned from tableTwo is null. tableTwo.id will be NULL for all records from tableOne where the id is not found in tableTwo.
Categories: How-To's, Technology Tags: howto, Join, Left Join, mysql, Query, Row, Rows, select, SQL, Table, tips, Where
| 
No comments
Author:
erics, 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 […]
Categories: How-To's, Technology Tags: 5.5, 5.7, AWS, AWS Linux, CentOS, howto, mysql, MySQL 5.5, MySQL 5.7, tips, upgrade
| No comments
Author:
erics, 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: […]
Categories: How-To's, Technology Tags: Error, howto, login, mysql, mysql_upgrade, remote, Remote Login, tips
| No comments
Author:
erics, 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 […]
Categories: How-To's, Technology Tags: convert, mysql, mysqli, php, PHP5.5, PHP5.6, PHP55, PHP56, WordPress
| No comments
Author:
erics, 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 https://www.percona.com/blog/2008/11/07/poor-mans-query-logging/, which I am reposting without permission because it is so good: 1 2 3 4 5 6 7 8 […]
Categories: How-To's, Technology Tags: 3306, mysql, Network, Scan, Sniff, Sniffer, tcpdump, Watch
| No comments
Author:
erics, 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(): […]
Categories: How-To's, Technology Tags: Cast, Char, Column, Filter, howto, Max, mysql, Numeric, Regex, regexp, select, sort, SQL, tips, Varchar, Where, WordPress
| No comments
Author:
erics, 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 […]
Categories: How-To's, Technology Tags: Amazon, AWS, Check, Host, Host Check, howto, mysql, nagios, Passive, Passive Check, Ping, RDS, tips
| No comments
Author:
erics, 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 […]
Categories: How-To's, Technology Tags: Change, howto, InnoDB, innodb_log_file_size, my.cnf, mysql, Percona, tips, update
| No comments
Author:
erics, December 31st, 2013
update mysql.user set password=PASSWORD(‘Your New Password Here’) where user=’root’;
Categories: How-To's, Technology Tags: howto, mysql, Password, Reset, root, tips
| No comments
Author:
erics, October 2nd, 2013
/usr/sbin/mysqld –verbose –help | grep -A 1 “Default options”
Categories: How-To's, Technology Tags: CNF, Config, Default, howto, my.cnf, mysql, Options, Path, tips
| No comments
