by carbonlogic | Sep 19, 2022 | blog
There is a wonderful bash script to back up PowerDNS zone files, written by the author who posts as ‘msb’, here:
https://stack-source.com/2021/01/25/powerdns-zone-backups/
I am reproducing it here because I found it very useful for keeping text version backups of some of zone files which are stored in a mysql database by powerdns.
I’ve updated the script to make a few modifications. Namely, I would like to store each set of daily backups to a separate folder. Here is my updated script:
#!/bin/bash
# creates dump/export/backup of all DNS zones
today=`date +%Y%m%d`
if [ ! -d /info/backup/zones/$today ]; then
if [ ! -d /info/backup/zones/$today ]; then
mkdir /info/backup/zones/$today
fi
mkdir /info/backup/zones/$today
fi
zones=(`/usr/bin/pdnsutil list-all-zones`)
for z in "${!zones[@]}"
do
/usr/bin/pdnsutil list-zone ${zones[$z]} > "/info/backup/zones/$today/${zones[$z]}-$today.zone"
done
If you compare the two scripts, you can see that I moved the ‘today’ variable higher up in the script so it can be used to create subdirectories for the daily backup when the pdnsutil command is used near the bottom.
When the script is run, it will now create a subdirectory in the backup directory for today’s date and place all the zone files with a timestamp in that directory.
I also chose to remove the find command and place it separately on its own as a cronjob.
by carbonlogic | May 31, 2022 | blog
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/
by carbonlogic | Aug 5, 2021 | blog
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/
by carbonlogic | Aug 3, 2021 | blog
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
by carbonlogic | Aug 3, 2021 | blog
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
Recent Comments