cPanel and Remote MySQL connectivity issues

cPanel and Remote MySQL connectivity issues

The Remote MySQL section of a cPanel account allows you to add hosts (as FQDNs or IP address) for remote access to your databases.

At the MySQL level, what this operation does when you add a hostname is to create users for your databases which have the hostname which you just supplied.

The MySQL code for creating a user has the following format:

CREATE USER 'myuser'@'123.123.123.123' IDENTIFIED BY 'mypass';

When you added a host via the GUI interface in cPanel, each of your current database users will have another user created (with the same permissions) which allows them to connect from that host.

For example, if I have a mySQL user called ‘carbonlogic’, and I add the IP address: 155.165.155.165 to the remote MySQL list, cPanel will create another user ‘carbonlogic@155.165.155.165’. This will allow you to connect the ‘carbonlogic’ user from the host 155.165.155.165.

You can see the changes which take effect by logging on at the command line and logging on to mySQL as root:

mysql -u root

(you should be able to log in without -p, since it will be stored in your my.cnf file)

if you perform this command:

 SELECT user,host FROM mysql.user

Before, and then after you have added your new host to the Remote MySQL list, you will see something like this:

mysql> SELECT user,host FROM mysql.user;
+-------------+----------------+
| user        | host           |
+-------------+----------------+
| carbonlogic | localhost      |
+-------------+----------------+
1 row in set (0.00 sec)

mysql> SELECT user,host FROM mysql.user;
+------------------+----------------+
| user             | host           |
+------------------+----------------+
| carbonlogic      | localhost      |
| carbonlogic      | 155.165.155.165|
+------------------+----------------+

A very important point to remember, is that as of cPanel version 102.0 (build 17), these changes are applied to existing users, once, at the time of the addition of the hostname.

What this means is that the Remote MySQL feature does not function like a typical whitelist. If you add new users in the future, after adding all your users, you will need to re-apply the changes by deleting the hostname, and then re-add it.

You can find the cPanel docs for Remote MySQL below:

https://docs.cpanel.net/cpanel/databases/remote-mysql/

Get all column names from a mysql table

Get all column names from a mysql table

A quick one: have you ever wanted to get the column names of a mysql table in one column?

You can retrieve the column names of a table by running an SQL query:

select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='tablename'

Of course, remember the replace ‘tablename‘ with the name of your table.

This query will return all your column names in a single column, which is easy to cut and paste. If you want them in a row, open MS Excel and use Paste Special -> transpose your values after you have copied them.

Update: There is a nice summary of how to get column names, including in CSV format, here: https://thispointer.com/mysql-get-column-names/

Nagios check all services script

Nagios check all services script

If you’re running a Nagios server, sometimes you may want to check all services across all of your hosts. As of the time of writing, Nagios doesn’t have a built in feature to do this.

However, there is one way to force a recheck of all services across all hosts using bash.

The script makes use of two special nagios files:

/var/log/nagios/status.dat
/var/spool/nagios/cmd/nagios.cmd

The status.dat file holds the current state of the Nagios system, including all host and service states.

The nagios.cmd file is a pipe file in which you can send commands to.

Please check those paths are valid on your filesystem and, in necessary, update the script with the new paths.

The following is a bash script, which, when run without any arguments, will re-check all services across all hosts that you are monitoring:

#!/usr/bin/env bash

### This program will run a check on all services on all hosts immediately.

array_of_hosts_with_dups=()

for temp_host in `cat /var/log/nagios/status.dat | egrep 'host_name'`; do
        fixed_host=$(echo $temp_host | sed -e 's/host_name=//g' | sed -e 's/^[ \t]*//');
        array_of_hosts_with_dups+=($fixed_host)
done

### This removes all duplicates
array_of_unique_hosts=($(for v in "${array_of_hosts_with_dups[@]}"; do echo "$v";done| sort| uniq| xargs))

### Print out list of unique hosts
### Do a scheduled forced recheck of all hosts in array_of_unique_hosts
for uniquehost in "${array_of_unique_hosts[@]}"
do
        echo $uniquehost
        /bin/printf "[%lu] SCHEDULE_FORCED_HOST_SVC_CHECKS;$uniquehost;1110741500\n" $now > /var/spool/nagios/cmd/nagios.cmd
done

SSH Key Permissions

SSH Key Permissions

One of the problems users have getting ssh public key authentication to work is that the permissions on the files or directories are incorrect (often too permissive). Below is a short list of commands to run in the user’s home directory in order to set the correct permissions.

All of these files/folders should be owned by the user, not root.

chmod 700 ~/.ssh
chmod 644 ~/.ssh/authorized_keys
chmod 644 ~/.ssh/known_hosts
chmod 644 ~/.ssh/config
chmod 600 ~/.ssh/id_rsa
chmod 644 ~/.ssh/id_rsa.pub