Apply Database Partitions to a live Zabbix database – without downtime

Due to the growth of our database (> 1TB), the 'housekeeper' no longer worked properly. The best solution to this problem is to apply Database Partitioning, however with a database of this size this takes a lot of time if you want to keep the data. We tried this action in several ways, the one below was the only way we were able to implement partitioning without downtime.

The example below must be repeated for each table and takes several hours per table.

# Create temporary partition 
CREATE TABLE `history_log_tmp` LIKE `history_log`;
# Apply partitioning
CALL partition_maintenance('zabbix', 'history_log_tmp', 30, 24, 3);

# Rename tables so the new empty table will be used by Zabbix. Leaving the old one as backup
RENAME TABLE history_log TO history_backup_log;
RENAME TABLE history_log_tmp TO history_log;

# Output all data from backup table to file
SELECT * INTO OUTFILE '/var/lib/mysql-files/history_backup_log.sql' FROM history_backup_log;

# Open MySQL Shell and start import
util.importTable("/var/lib/mysql-files/history_backup_log.sql", {schema: "zabbix", table: "history_log", columns: ["itemid","clock","value","ns"], dialect: "default", skipRows: 0, showProgress: true, fieldsOptionallyEnclosed: false, linesTerminatedBy: "\n",threads: 2, bytesPerChunk: "50M", maxRate: "10M"})

Ansible Tower – Custom Credentials Type

Within playbooks you occasionally connect to external applications or services, in my case Zabbix and ServiceNow. Because I also need login details and do not want to leave this plain text in playbooks, I use a 'Custom Credentials Type'. The advantage of this is that I can use the login details within a playbook (as a macro) and they are stored encrypted in Ansible Tower.

I first create a new credential type by defining the fields it will have and how these will be passed to my playbook. Credential types consist of two parts – “inputs” and “injectors“.

  • Inputs:
    define the value types that are used for this credential – such as a username, a password, a token, or any other identifier that’s part of the credential.
  • Injectors:
    describe how these credentials are exposed for Ansible (or us) to use – this can be Ansible extra variables, environment variables, or templated file content.

Both these configurations are specified as YAML or as JSON. In my case, the new credential type is called "ServiceNow" and i’m providing the instance, username and password as part of this credential type:

  - id: instance
    type: string
    label: ServiceNow Instance
  - id: username
    type: string
    label: ServiceNow Username
  - id: password
    type: string
    label: ServiceNow password
    secret: true
  - instance
  - username
  - password

Then in the Injector configuration:

  snow_instance: '{{ instance }}'
  snow_password: '{{ password }}'
  snow_username: '{{ username }}'

Now go to Credentials and add a new one, selecting "ServiceNow" as Credential Type:

Thats it! When you link this credential to your host, or playbook, you can use this credentials from within your playbook!

Enable ‘Previous Versions’

Anyone who’s ever trashed a spreadsheet, or accidentally deleted a file, will appreciate the 'previous versions' function. However, you will only find out that this is not enabled by default when it is already too late.

You can enable previous versions by enabling shadow copies at a ‘volume’ level, Server Manager> Tools> Computer Management > Share Folders > Configure Shadow Copies > Select the Volume > Enable. It will take about 15% of your space, so make sure you have enough room.

In my case i want a copy each hour, go to Advanced Schedule Options interface, select Repeat task, and then set the frequency to every 1 hours, then Select Time, and then change the time value to 2:58 AM.

Enable LLDP on Windows Server 2016/2019

The Link Layer Discovery Protocol (LLDP) is a vendor-neutral link layer protocol used by network devices for advertising their identity, capabilities, and neighbors on a local area network based on IEEE 802 technology, principally wired Ethernet. The protocol is formally referred to by the IEEE as Station and Media Access Control Connectivity Discovery specified in IEEE 802.1AB and IEEE 802.3 section 6 clause 79. More info here

The following will install the DatacenterBridging feature and enable lldp and all interfaces:

Enable-WindowsOptionalFeature -Online -FeatureName 'DataCenterBridging'
Get-NetAdapter | Where-Object { $_.Name -like "*Ethernet*" -and $_.Status -eq 'Up' } | ForEach { Enable-NetLldpAgent -NetAdapterName $_.Name -Verbose }

Mysql Clear Diskspace

When you are running out of diskspace you can purge the MySQL binary logs to free up some space

mysql> PURGE BINARY LOGS BEFORE 'yyyy-mm-dd hh:mm:ss';

Sometimes you are already on 99% disk space and need more drastic methods. This requires manually removing the logfiles.

systemctl stop mysql
cd /var/llog/mysql && a=`ls |grep -v relay |grep bin.index` && b=`wc -l <$a` ; c=`echo $(($b/2))` |xargs -l rm ; echo $c | head -n $b $a |cut -d "/" -f2 && sed 1,$c\d $a -i
systemctl start mysql

Enable NTP Server in Windows 2019

The Windows Time service uses the Network Time Protocol (NTP) to help synchronize time across a network. It's as easy as 3 commands using powershell:

Set-ItemProperty -Path "HKLM:\SYSTEM\CurrentControlSet\Services\w32time\TimeProviders\NtpServer" -Name "Enabled" -Value 1
Set-ItemProperty -Path "HKLM:\SYSTEM\CurrentControlSet\services\W32Time\Config" -Name "AnnounceFlags" -Value 5 
Restart-Service w32Time

Check nvme health and temperature – nvme-cli

Make sure nvme-cli is installed:

$ sudo apt install nvme-cli

Check for availible nvme disks:

$ sudo nvme list
Node             SN                   Model                                    Namespace Usage                      Format           FW Rev  
---------------- -------------------- ---------------------------------------- --------- -------------------------- ---------------- --------
/dev/nvme0n1     S4EVNFXXXXXXXX9972H      Samsung SSD 970 EVO Plus 500GB           1          26,60  GB / 500,11  GB    512   B +  0 B   2B2XXXXXM7

With nvme-cli you can now check the internal temperature, disk usage, power cycles, and much more:

$ sudo nvme smart-log /dev/nvme0
Smart Log for NVME device:nvme0 namespace-id:ffffffff
critical_warning                    : 0
temperature                         : 40 C
available_spare                     : 100%
available_spare_threshold           : 10%
percentage_used                     : 0%
data_units_read                     : 90935
data_units_written                  : 119679
host_read_commands                  : 4491381
host_write_commands                 : 2370351
controller_busy_time                : 8
power_cycles                        : 34
power_on_hours                      : 9
unsafe_shutdowns                    : 1
media_errors                        : 0
num_err_log_entries                 : 0
Warning Temperature Time            : 0
Critical Composite Temperature Time : 0
Temperature Sensor 1                : 40 C
Temperature Sensor 2                : 38 C
Thermal Management T1 Trans Count   : 0
Thermal Management T2 Trans Count   : 0
Thermal Management T1 Total Time    : 0
Thermal Management T2 Total Time    : 0

Ubuntu 18.04 – OpenVPN Server in less then 5 minutes

OpenVPN provides flexible VPN solutions to secure your data communications, whether it's for Internet privacy, remote access for employees, securing IoT, or for networking Cloud data centers. Our VPN Server software solution can be deployed on-premises using standard servers or virtual appliances, or on the cloud.

Prepare your system

Make sure all latests packages and updates have been installed:

$ sudo apt update
$ sudo apt upgrade
$ sudo apt dist-upgrade

Download and run installation script

$ wget -O
$ sudo chmod +x
$ sudo ./ 

The script will ask you some questions for it's basic configuration.
- When your IP address is asked, choose your WAN (public) address
- When protocol is asked, i recommend default UDP
- Port can be anything you want, i normally keep default
- When asked, pick as your DNS server as this is one of the fastest currently online.

After this the installation will go ahead and inform you when it's done. You can verify if OpenVPN is running or not:

$ sudo systemctl status openvpn@server # <--- get server status

You can start or stop OpenVPN with the following commands:

$ sudo systemctl stop openvpn@server # <--- stop server
$ sudo systemctl start openvpn@server # <--- start server

Client configuration

At the end of the installation you whould see a message like this:

Your client configuration is available at: /root/bontekoe.ovpn

As i am using Linux (Ubuntu) on my laptop, i can simply copy that ovpn file to my computer using scp.

$ sudo scp root@ /etc/openvpn/client.conf

This should be enough to connect! Check if everything is working by running:

$ sudo openvpn --client --config /etc/openvpn/client.conf

Now, by opening another terminal you should be able to ping (the VPN host).

If you are running windows, there is a client here.

Ubuntu 18.04 – Laggy bluetooth

After installing this version my mouse became laggy and also my headphones. Here is the fix:

# HANDLE="$(hcitool con | grep '<Bluetooth Mouse mac address>' | awk '{print $5}')"  # get the device handle
# hcitool lecup --handle $HANDLE --latency 0 --min 6 --max 8