How To Fix MySQL Remote Login Errors

Author: , Posted on Thursday, August 25th, 2016 at 7:57:26am

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:
ERROR 1045 (28000): Access denied for user 'root'@'yourHost.com' (using password: YES)

I next tried to re-add the root@% user, but that failed too, giving me this error:
mysql> CREATE USER root@'%' IDENTIFIED BY 'yourPasswordHere';
ERROR 1054 (42S22): Unknown column 'plugin' in 'mysql.user'

So I executed the following to rebuild the databases:
mysql_upgrade -u root -p
Enter password:

Then re-added the remote root user:
mysql> CREATE USER root@'%' IDENTIFIED BY 'yourPasswordHere';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL ON *.* TO root@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

And all was well after that:
mysql -u root -p -h remoteHostName
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 545
Server version: 5.6.32 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

How To Protect a WordPress Site Using Basic Auth in Apache 2.4

Author: , Posted on Thursday, August 25th, 2016 at 7:37:07am

Apache 2.4 changed the security configuration directives a bit.

Here is an example using basic auth:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<Directory "/path/to/your/wordpress">
   AllowOverride All 
   Options +FollowSymLinks +ExecCGI -Indexes
   Order allow,deny
   Allow from all
 
   <RequireAll>
      AuthType Basic
      AuthName "Protected Resource"
      AuthUserFile /path/to/your/.htpasswd
      Require valid-user
   </RequireAll>
 
</Directory>
<Directory "/path/to/your/wordpress">
   AllowOverride All 
   Options +FollowSymLinks +ExecCGI -Indexes
   Order allow,deny
   Allow from all

   <RequireAll>
      AuthType Basic
      AuthName "Protected Resource"
      AuthUserFile /path/to/your/.htpasswd
      Require valid-user
   </RequireAll>

</Directory>

What tripped me up for a while was that I still had the Require all granted directive inside the container, and that needed to be removed for the auth to work.

How To Convert from mysql_ to mysqli_ in WordPress Plugins Easily

Author: , Posted on Wednesday, August 24th, 2016 at 5:16:27pm

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 5.1

This method is a shortcut and one should really use the native WordPress interfaces to the database as illustrated here:
https://codex.wordpress.org/Class_Reference/wpdb

Here are some examples of things I did to convert.

First, add these two replacement functions to your code somewhere:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
function mysqli_field_name($result, $field_offset)
{
    $properties = mysqli_fetch_field_direct($result, $field_offset);
    return is_object($properties) ? $properties->name : null;
}
 
function mysqli_result($res,$row=0,$col=0){ 
    $numrows = mysqli_num_rows($res); 
    if ($numrows && $row <= ($numrows-1) && $row >=0){
        mysqli_data_seek($res,$row);
        $resrow = (is_numeric($col)) ? mysqli_fetch_row($res) : mysqli_fetch_assoc($res);
        if (isset($resrow[$col])){
            return $resrow[$col];
        }
    }
    return false;
}
function mysqli_field_name($result, $field_offset)
{
    $properties = mysqli_fetch_field_direct($result, $field_offset);
    return is_object($properties) ? $properties->name : null;
}

function mysqli_result($res,$row=0,$col=0){ 
    $numrows = mysqli_num_rows($res); 
    if ($numrows && $row <= ($numrows-1) && $row >=0){
        mysqli_data_seek($res,$row);
        $resrow = (is_numeric($col)) ? mysqli_fetch_row($res) : mysqli_fetch_assoc($res);
        if (isset($resrow[$col])){
            return $resrow[$col];
        }
    }
    return false;
}

A key difference between the mysql_ functions and the mysqli_ functions, is that the new mysqli functions often require a database handle to be provided to the function, where the old mysql_ functions did not.
Search for all instances of “mysql_” in your code. Add the following two lines of code above your existing mysql_ commands to give you access to the needed WordPress database handle:

global $wpdb;
$dbh = $wpdb->dbh;

Then add the database handle variable into the needed locations and change the command to mysqli_ – for example:
$result = mysql_query($sql);
becomes
$result = mysqli_query($dbh,$sql);

These are the commands I changed that needed the database handle:
$mysqliQueryResult = mysqli_query($dbh,$sql);
$error = mysqli_error($dbh);
$errno = mysqli_errno($dbh);
$escaped = mysqli_real_escape_string($dbh, "yourString");

These are the commands that simply needed to be converted to mysqli_ – for example:

$numFields = mysqli_num_fields($result);
while ($row = mysqli_fetch_assoc($result)) {}
$columnName = mysqli_field_name($result , $i);
$data = mysqli_result($result, 0);
$obj = mysqli_fetch_object($mysqliQueryResult);

This is NOT a complete list, just the items I had to address. As always, YMMV…

References:
https://www.experts-exchange.com/articles/11177/PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html
http://stackoverflow.com/questions/14629636/mysql-field-name-to-the-new-mysqli
http://mariolurig.com/coding/mysqli_result-function-to-match-mysql_result/

How To Install and Use the screen Command on AWS Linux

Author: , Posted on Friday, August 12th, 2016 at 8:29:46pm

Install it: yum install screen
Start it up: screen
Detach from the session: Control-a d
Re-attach to the session later on: screen -r

This is a good reference guide:
https://www.rackaid.com/blog/linux-screen-tutorial-and-how-to/

How To Install a .pkg file from the Command Line on MacOSX

Author: , Posted on Friday, August 12th, 2016 at 7:21:56pm

sudo /usr/sbin/installer -pkg /path/to/yourFile.pkg -target /

How To Easily Convert From Centigrade/Celsius to Fahrenheit

Author: , Posted on Monday, August 8th, 2016 at 2:32:21pm

The standard formula (F = 9/5C + 32) is not that easy to use.

To create an easy estimate, simply double the Centigrade temp, then subtract the first digit of that result from the result, then add 32.

The resulting estimate should not be more than 1 degree Fahrenheit off.

Exact method yields a conversion from 44 degrees C to 111.2 degrees F.

The estimation method:
44°C x 2 = 88
88 – 8 = 80;
80 + 32 = 112°F

As you can see, our estimate is less than one degree off, and that is good enough for me!

How To Enable DNS Hostnames for AWS VPC

Author: , Posted on Monday, August 8th, 2016 at 12:10:43pm

When you launch an instance into a VPC, AWS provides the instance with a public DNS hostname only if DNS hostnames are enabled for the VPC. By default, DNS hostnames are enabled only for default VPCs and VPCs that you create using the VPC wizard in the VPC console.

If you enable DNS hostnames and DNS support in a VPC that didn’t previously support them, an instance that you already launched into that VPC gets a public DNS hostname if it has a public IP address or an Elastic IP address.

Open the Amazon VPC console at https://console.aws.amazon.com/vpc/.

In the navigation pane, choose Your VPCs.

Select the VPC from the list.

Review the information in the Summary tab. You must set both to Yes.
dns-settings-gwt

To update these settings, do both:

Actions > Edit DNS Resolution
Actions > Edit DNS Hostnames

In the dialog box that opens for each, choose Yes then Save.

How To Force Sendmail to Process the Mail Queue

Author: , Posted on Thursday, August 4th, 2016 at 3:56:02pm

To force sendmail to process the mail queue, execute as root:
sendmail -q -v
The optional -v enables verbose mode to assist with troubleshooting.

To display the current contents of the mail queue, execute as root:
mailq

How To Fix apt-get Errors on Old Ubuntu Hosts

Author: , Posted on Wednesday, August 3rd, 2016 at 8:20:04am

As usual, whenever I have to do sysadmin on a Ubuntu-based OS, I find problems. When will people realize what a horrible OS it is?

In this case, packages needed were unavailable.

For apt-get to function, the file /etc/apt/sources.list must be edited to change all URLs from security.ubuntu.com and archive.ubuntu.com to old-releases.ubuntu.com, using the sed command:

cp /etc/apt/sources.list /etc/apt/sources.list.orig
sed -i.bak -r ‘s/(archive|security).ubuntu.com/old-releases.ubuntu.com/g’ /etc/apt/sources.list
apt-get update

Then you may proceed to install the missing packages…

As always, YMMV!

How To Compare Two Lists Using grep

Author: , Posted on Monday, August 1st, 2016 at 12:39:07pm

Output all lines in list.txt which don’t match any line in excluded.txt:
grep -Fxv -f excluded.txt list.txt