My http://www.yaddnet.org DSC database originally ran on various machines in my shack at home. It was migrated to a server donated for the purpose - located in Las Vegas, in 2019. This document records the steps needed to transfer everything and get it running on the new Virtual Private Server. In case it ever needs doing again in future this will help jog my memory of what's involved.

Created Thursday 24 October 2019

Server IP: 64.235.39.127

MIGRATING TO A NEW UBUNTU 20.4 VPS REQUIRED THE FOLLOWING

https://ostechnix.com/how-to-reset-root-password-in-mysql-8-on-ubuntu-linux/

1) sort out passwordless root access to MySQL

add skip-grant-tables to end of config file:

(such as….)

/etc/mysql/mysql.conf.d/mysqld.cnf

restart mysqld

login to mysql

$ mysql -u root
mysql>FLUSH PRIVILEGES;
mysql>ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '';
mysql>quit;

remove skip-grant-tables from /etc/mysql/mysql.conf.d/mysqld.cnf

restart mysqld

2) create blank/empty yadd database

$ mysql -u root

mysql> create database yadd;
mysql> quit;

2a) scp migrate tarfile to remote server

and wait….

2b) untar migrate tarfile

3) use saved schema file to create the tables:

$ mysql -u root yadd < schema.sql

4) import the full database backup:

$ mysql -u root yadd < yadd_xxxxx.sql

and wait……. and wait……

5) test all the data is there…!

6) extract yaddnet files from tarball

7) extract html files into /var/www/

8) permissions/ownership of /var/www/html ? Not Needed

9) apache “sites-enabled” - change virtual server's port? Not needed

10) /etc/rc.local command to start tmux session

sleep 60; su USER -c /home/USER/yaddnet/tmux_yadd.sh &

11) start tmux session and see if it's working…

12) fire some test packets at it from uCoastTester.py

13) crontab entries

15 */4 * * * /home/USER/yaddnet/purge_err.sh > /dev/null 
* * * * * /home/USER/yaddnet/purge_tmp.sh > /dev/null 

14) Character encoding for pages

edit /etc/php/7.2/apache2/php.ini

change default_charset from UTF-8 to ISO-8859-1:

default_charset = "ISO-8859-1"

This renders “degree” signs etc. on web pages correctly.

15) mysql config

add to /etc/mysql/mysql.conf.d/mysqld.conf

sql_mode = "STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

restart mysql

This (among other things) allows the group-by queries to work.

16) Open firewalld UDP port 4530 for DSCDecoder (and close unneeded port 9999)

firewall-cmd --permanent --service=yadd --add-port=4530/udp

firewall-cmd --permanent --service=yadd --remove-port=9999/udp

firewall-cmd --complete-reload

While creating this Wiki page, from a previous note in ZimWiki, I wondered about the firewall config, as it appears I didn't explicitly open UDP/50666 for YaDD's traffic, but I added udp/4530 for DSCDecoder.

Looking at /etc/firewalld/services/yadd.xml I see:

<?xml version="1.0" encoding="utf-8"?>
<service>
  <short>yadd</short>
  <description>YaDD Service</description>
  <port port="50666" protocol="udp"/>
  <port port="4530" protocol="udp"/>
</service>

so 50666 must have been added when the VPS was built (not my me). I guess I asked for it when the offer to provide the VPS was made, but forgot about DSCDecoder until later.

16) Buy domain yaddnet.org from “fasthosts.co.uk” and set DNS record to point host www.yaddnet.org to the new server IP address

£10.79 per year, auto-renew via PayPal.

Received an email from kb7ypf@yahoo.com:

“I have been using yaDD 1.7.1 monitoring and logging GMDSS signals. I have been using it for about two weeks. This morning when I started yaDD it worked great, as usual, however, when I went to the web and accessed the message logging page, I got this message: “Failed to connect to MySQL: Access denied for user 'root'@localhost' …….”

I confirmed the same symptoms…

but it was working this morning?!

SSH'd into the server and couldn't access mysql from the commandline, either, same error.

This reminded me of the behaviour of a “stock” mysql server before doing the steps in 1) above.

Found an entry in /var/log/apt/history.log from 06:25 PST (14:25 UTC)

Start-Date: 2019-11-18  06:25:08
Commandline: /usr/bin/unattended-upgrade
Upgrade: mysql-server-5.7:amd64 (5.7.27-0ubuntu0.18.04.1, 5.7.28-0ubuntu0.18.04.4), mysql-server-core-5.7:amd64 (5.7.27-0ubuntu0.18.04.1, 5.7.28-0ubuntu0.18.04.4)
End-Date: 2019-11-18  06:25:29

Looks like “unattended-upgrade” has upgraded mysql, and also reset the normal permissions/privileges. Bloody Hell!

• stop mysql
• edit mysqld.cnf to add “skip-grant-tables”
• start mysql
• log in to mysql “mysql -u root”

$ mysql -u root
mysql>FLUSH PRIVILEGES;
mysql>ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '';
mysql>quit;

• stop mysql
• remove “skip-grant-tables” from mysqd.cnf
• start mysql
• test…

looks like we lost about 1 hour of DSC loggings while the SQL server refused to talk to us, from 14:25 UTC until ~15:30 UTC when I got it running normally again.

…..and relax!

Have 2 choices - remove unattended-upgrades package and lose all automatic security upgrades OR configure unattended-upgrades to blacklist mysql-server from upgrades.

https://wiki.debian.org/UnattendedUpgrades and https://help.ubuntu.com/community/AutomaticSecurityUpdates

has some helpful info… found where to blacklist individual packages….

service unattended-upgrades stop

edit /etc/apt/apt.conf.d/50unattended-upgrades to blacklist mysql

	// List of packages to not update (regexp are supported)
	Unattended-Upgrade::Package-Blacklist {
			"mysql-server";
			"mysql-server-5.7";
			"mysql-server-core-5.7";
	};

service unattended-upgrades start

check it's running:

gm4slv@lasvegas-nv-datacenter:~$ apt-config dump APT::Periodic::Unattended-Upgrade

APT::Periodic::Unattended-Upgrade "1";

hopefully that will sort it?

Page Updated: 15/04/24 08:11 BST