PAD Server Migration

Conversion to local MySQL database

Database changes

Following https://www.digitalocean.com/community/tutorials/how-to-allow-remote-access-to-mysql,

Configure via sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf, change bind-address from 127.0.0.1,

# localhost which is more compatible and is not less secure.
bind-address            = 172.17.0.1

restart with sudo systemctl restart mysql.

Open firewall with ufw allow from 172.16.0.0/13 to any port 3306.

Convert our user paduser using sudo mysql,

RENAME USER 'paduser'@'localhost' TO 'paduser'@'172.%.%.%';
FLUSH PRIVILEGES;

Link for how to modify user privileges https://docs.digitalocean.com/products/databases/mysql/how-to/modify-user-privileges/.

web-api/docker changes

Changes to docker compose file,

    environment:
      DATABASE_URL: mysql+pymysql://paduser:password@172.17.0.1/pad

Database reload

Method of clearing tables,

( mysqldump --add-drop-table --no-data -u root -p database | grep 'DROP TABLE' ) > ./drop_all_tables.sql
mysql -u root -p database < ./drop_all_tables.sql

phpMyAdmin changes if port moved

modify sudo nano /usr/share/phpmyadmin/config.inc.php

/* Server parameters */
$cfg['Servers'][$i]['host'] = '127.0.0.1'; // was localhost
$cfg['Servers'][$i]['compress'] = false;
$cfg['Servers'][$i]['AllowNoPassword'] = false;
$cfg['Servers'][$i]['port'] = '3307';      // new addition