Terrible, hacky database syncing

30/1/2019

Someone in my family wanted a custom inventory system made that was accessible from any device and had some features they couldn't find in any pre-made solution so I made my own. It works great (code not distributed, if you want it ask, GPL3), however prior to creating it they never mentioned wanting to view it away from their LAN. So I had to come up with a solution.

The server that the website (it's PHP/mysql) is located on has no access to external servers, so I had to route everything through my own computer.

My solution was cronjobs, sqldumps and sftp. Perfectly hacky.

Every hour (*/59 * * * *, each computer has a different localtime, so there isn't any concerns there) the server hosting the website creates an sql dump of the database and backups the uploaded images, my local computer then pulls that dump and backup and uploads the sql dump to my external server, which then imports it.

The images are not uploaded to the external server, but this is fine, the user indicated they only need access to the textual data off-site. It would be just too much data otherwise.

I also created a cut-down viewer for the data removing the ability to view images, add, edit and delete items.

sql dump and images backup:

name=$(date +"%d-%b-%Y-%H-%M")
mysqldump -u inventory -ppasswordhere inventory > /home/user/inventory.sql

tar -zcvf /webuser/backups/inventory_directory.tar.gz /var/www/html/inventory

cp /webuser/backups/inventory_directory.tar.gz /home/user/inventory_directory.tar.gz

sftp user@tewi:/home/user <<EOF
get /home/user/inventory.sql /home/daniel_j/programming/inventory.sql
get /home/user/inventory_directory.tar.gz /home/daniel_j/programming/inventory_directory.tar.gz
exit
EOF

sftp -oPort=port user@danieljon.es:/home/user	 <<EOF
put /home/daniel_j/programming/inventory.sql
exit
EOF

mysql -u inventory -ppasswordhere inventory < /home/user/inventory.sql