Moving YaDDNet from USA to UK

• YaDDNet started hosted on a machine in my shed
• In 2019 I almost gave up maintaining & hosting it and was approached by Alan Spindel ex. of WLO/Mobile Radio with the offer of a free VPS in the States
• I moved everything from my own PC to the new Cloud Server - as documented largely here Yaddnet Migration
• It's run quietly and without too may hiccups since 2019 until now, April 13th 2024
• Alan has now been asked to contribute financially for continued access to the VPS - up to $200 per month
• This is way too much money!
• I looked at my current VPS hosted by Fasthosts and they do a VPS with similar specs to the existing USA-based server - for £12 per month
• I let Alan know I'd take on the new server and migrate YaDDNet ASAP, and began the process of obtaining server, installing software required, copying YaDDNet Python code, transferring YaDDNet HTML/PHP web pages and then the big job of taking a MYSQL dump of the YaDDNet database and copying it over, and putting it into the new SQL server.
• Let the fun begin

• I bought a VPS L from Fasthosts
  • 120GB HDD, 4GB RAM, 2 CPU, Ubuntu 20.4
• I copied the yaddnet python code (in a tar-ball) that deals with the network feeds of Yadd and DSCDecoder and injects the resulting data into the MySQLdatabase from the existing server.
• I installed the necessary software
  • Python2.7
  • python-is-python2
  • python-dev-is-python2
  • MySQL Client
  • MySQL Server
  • Apache2
  • PHP
  • Tmux
  • Vim
  • copied my .bashrc from the old server for colour prompt etc.

• Using the previous Yaddnet Migration info Yaddnet Migration I had some guidance of any tweaks needed.
• I needed extra Python2 supporting libraries
  • For MySQLdb → pip install MySQL-python
  • but for pip…. I need pip2… how to get it - it's no longer available from Ubuntu's apt repositories.
    • I found a script get-pip.py at github
      • https://github.com/pypa/get-pip
      • also need python-dev from apt
      • also need my_config.h from where..?
      • finally can pip install MySQL-python

curl https://bootstrap.pypa.io/pip/2.7/get-pip.py --output get-pip.py
sudo python2 get-pip.py
sudo apt-get install python-dev
sudo wget https://raw.githubusercontent.com/paulfitz/mysql-connector-c/master/include/my_config.h -O /usr/include/mysql/my_config.h
sudo pip2 install MySQL-python

• at this point I could run the python server code, in a tmux session as usual, using the shell script in the python directory extracted from the tar-ball. All the supporting libraries were there.

• My old notes had instructions of how to set MySQL for password-less root access, but I found by trial and error that these steps no longer work in Ubuntu 20.4, with systemd
• I found some help online https://ostechnix.com/how-to-reset-root-password-in-mysql-8-on-ubuntu-linux/
  • the basic is that it's now necessary to add options to MySQL via systemd
  • for skip-grant-tables

$ sudo systemctl stop mysql
$ sudo systemctl status mysql
$ sudo systemctl edit mysql

[Service]
ExecStart=
ExecStart=/usr/sbin/mysqld --skip-grant-tables --skip-networking

$ sudo systemctl daemon-reload
$ sudo systemctl start mysql
$ sudo mysql -u root

mysql> FLUSH PRIVILEGES;
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '';
mysql> exit

$ sudo systemctl revert mysql
$ sudo systemctl daemon-reload
$ sudo systemctl restart mysql

$mysql -u root

mysql> create database yadd;
mysql> quit;

• on the old server

$ mysqldump -u root yadd > yadd_backup.sql

• and wait…. and wait…..
• eventually there's a 6.2GB sql file containing all the existing data
• bzip2 the file to make it smaller (it's about 400MB now) for scp transfer to the new server
• send it across:
  • scp yaddnet_backup.sql.bz2 gm4slv@109.228.53.165:

• on the new server
  • bunzip2 yaddnet_backup.sql.bz2
  • and wait….and wait
• then inject it into the new yadd database

mysql -u root yadd < yaddnet_backup.sql

• and wait…. and wait…. and wait
• eventually the command returns and the database is restored on the new machine

• extract the backed-up webpages from the old server and copy them to /var/www/html
• To be able to retrieve SQL data via PHP I needed to install more

sudo apt-get install php-mysql

• Almost there….
• New MYSQL user with a password for PHP data retrieval
  • Alan Spindel informed me that there was an update some time ago and it was necessary to create a new MySQL user, with a password, and to modify the PHP files to use it in the PHP → MySQL transactions

CREATE USER 'XXXXX'@'localhost' IDENTIFIED WITH mysql_native_password BY 'YYYYY';
GRANT ALL PRIVILEGES ON *.* TO 'XXXXX'@'localhost' WITH GRANT OPTION;

• XXXXX is the username as set in all the PHP files and YYYYY is the password for the user
• to perform some of the SQL queries (group-by) - need to alter sql-mode but again the old method using *.cnf files no longer works

# MySQL systemd service file

[Unit]
Description=MySQL Community Server
After=network.target

[Install]
WantedBy=multi-user.target

[Service]
Type=notify
User=mysql
Group=mysql
PIDFile=/run/mysqld/mysqld.pid
PermissionsStartOnly=true
ExecStartPre=/usr/share/mysql/mysql-systemd-start pre
ExecStart=/usr/sbin/mysqld  --sql-mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
TimeoutSec=infinity
Restart=on-failure
RuntimeDirectory=mysqld
RuntimeDirectoryMode=755
LimitNOFILE=10000

# Set enviroment variable MYSQLD_PARENT_PID. This is required for restart.
Environment=MYSQLD_PARENT_PID=1
~
~

Then

systemctl daemon-reload
systemctl restart mysql

• start the Python code to listen for new data

./new_tmux.sh

• open firewall for UDP/50666 and UDP/4530
• set up my own Yadd receiver pointing at 109.228.53.165 port 50666 and let it send new data….
• it's arriving!
• Alter DNS for the domain at Fasthosts to point yaddnet.org and www.yaddnet.org → 109.228.53.165
• Wait for new data from other YaDD receivers to start arriving as the DNS change propagates….
• it's arriving
• make /var/www/html/pages/php/test/tmp world read/writable to allow new text files to be written there (eg REU searches etc.)

• that's everything working correctly
• crontab entries to maintain everything

@reboot sleep 60; sudo -u gm4slv /home/gm4slv/yaddnet/tmux_yadd.sh >/dev/null 2>&1
 
 
15 */4 * * * /home/gm4slv/yaddnet/purge_err.sh > /dev/null
* * * * * /home/gm4slv/yaddnet/purge_tmp.sh > /dev/null

• get SSL certificates from Fasthost and convert Apache2 to HTTPS, per previous posts
  • SSL Install on VPS
  • VPS Notes
  • Thu 04/04/24 : VPS & SSL
• add a PayPal Donate button

• install mercurial on new server and make new code repositories

hg init
hg add
hg com -m "first commit, new server"

• yaddnet_repo containing the python and various bash scripts for handling incoming data and SQL injection
• yaddnet_web_repo containing the html and php web pages

• config directory needs access control via .htaccess and .htpasswd
  • first apache2 needs config tweaking

<Directory /path/to/website/database_tweaks/config>
        Options Indexes FollowSymLinks
        AllowOverride All
        Require all granted
</Directory>

• .htaccess file in config directory

AuthType Basic
AuthName "Password Protected Area"
AuthUserFile  /path/to/website/database_tweaks/config/.htpasswd
Require valid-user

• create a .htpasswd file for the user who can have access to config
• restart apache2
• to prevent directory file-listing (where there's no index.html file) remove Indexes from apache2.conf

<Directory /var/www/>
        Options  FollowSymLinks
        AllowOverride None
        Require all granted
</Directory>

• to allow the test directory itself to be listed (I can't be bothered to maintain an index for these scraps of php)

<Directory /var/www/html/pages/php/test/>
        Options Indexes FollowSymLinks
        AllowOverride None
        Require all granted
</Directory>

• restart apache2

— John Pumford-Green 14/04/24 18:11 BST