First off, you can substitute "MariaDB" where I reference MySQL. I'm not even sure which one is installed on the two hosting services.
Background:
I have some standard database-driven web apps installed on two different low-cost web hosting services, plus my own home lab. Two of these apps have fully functioning copies installed at a different hosting site. The hosts give me varying levels of system access, with one only having web-based admin tools like CPanel and Plesk, but no command shell.
Goal:
Replicate data from web apps over to their clones that run on the service with no Linux shell admin access, and automate it to happen daily/weekly. Then, set up a workflow to load the data with minimal effort.
First I needed database backups. The web hosting service that provides shell access conveniently does automatic rotating backups of my databases. They were compressed in ZST format (which I couldn't use on the target server) so I figured out the command to uncompress it:
unzstd backup/latest/app1_data.sql.zst -f -o tmp/app1_data.sql
On my homelab server, I did a mysqldump command to generate the backup file app2_data.sql. [More on that later.]
Next was to get the file transferred. The receiving hosting service doesn't have SFTP, just plain FTP. It has to be passive mode, which seems common with cheap shared hosting. It was working with the ordinary FTP command on my home server, but when I pushed the file from the shell on the other hosting service, it would give me an annoying error:
ftp> put app1_data.sql
local: app1_data.sql remote: app1_data.sql
200 PORT command successful
425 Could not open data connection to port 41069: Connection refused
I checked on the server and the file was there. Maybe it was just failing to send my FTP client the confirmation or something? Thought it was fine, but manual imports were failing. I looked at the file at the destination, and it turned out they were truncated, maybe the last ~40kB was missing.
I remembered ncftp from my mental archives, and that was conveniently installed on the server! That worked great, no error uploading.
To automate the upload:
ncftp has its own way of saving sites so you don't have to type in credentials when connecting. But the mistake I made was using the server's actual DNS name for the ncftp "bookmark". Problem with that is that if you say "ncftp ftpserversite.net" it just makes a new connection to that server and ignores your bookmark. So I gave it a non-Internet-literal name.
ncftp> open ftpserversite.net
... [enter credentials]
ncftp> bookmark ftpserversite
Then in my script, I referenced the bookmark, with a single command to perform the upload.
ncftpput -R ftpserversite htdocs/phpmyadmin/upload/ tmp/app1_data.sql
I made backup shell scripts that did these two steps, on both the hosting site for app1 and my home server for app2. So to recap for app1:
unzstd backup/latest/app1_data.sql.zst -f -o tmp/app1_data.sql
ncftpput -R ftpserversite htdocs/phpmyadmin/upload/ tmp/app1_data.sql
app2 on my server is more like this:
mysqldump -u [dbusername] -p'[dbpassword]' dbname_app2 >/var/backup/app2_data.sql
ftp -p ftpserversite.net <<END_SCRIPT
lcd /var/backup
cd htdocs/phpmyadmin/upload
rm app2_data.sql
put app2_data.sql
bye
END_SCRIPT
I did this one first, or I would have just done the whole ncftp bookmark thing and been done with it. But plain FTP worked on the homelab, so I made a .netrc entry to store the authentication credentials for the FTP server. Maybe I'll switch it to match the simpler one, but this retro version ain't broke...
Importing the data:
Maybe you noticed I put the file in an /upload folder for PHPMyAdmin. That goes back to planning this whole thing: the web-tools-only hosting service has PHPMyAdmin built into their admin panel, where you can click and go straight to a specific database without having to authenticate again. But the only way to import a backup file was to download it to your computer, and then upload it in your browser. Lots of logging in and manual steps. So... I asked an AI chatbot. Not my favorite thing, and it didn't solve the need.
AI Fail:
I asked (paraphrasing here) how I could put a file on the server in my hosting account data with that particular service, and import it with PHPMyAdmin directly from the server, instead of from my computer/browser. The answer was wrong in two ways:
- It described and gave the wrong setting to configure PHPMyAdmin to allow importing files on the server
- It said that the hosting service does not allow changing that setting to enable the feature, for security reasons
Crestfallen, I checked the PHPMyAdmin documentation for how to make that settings change. That's how I discovered it gave the wrong setting. What I really wanted to do was specify the name of the directory for uploads, and that's all it takes:
$cfg['UploadDir']='upload'
Then the second part was fun: sure, I can't modify the setup for the built-in PHPMyAdmin clickable buttons from their web panel. But this host provides Softaculous, and once of the apps I can install is PHPMyAdmin! I installed that in my account, created an upload directory, and then edited the config file, which had the UploadDir setting in there already but it was blank; I just filled it in like above. That gave me a button on the import tab, with a drop down to select the files I uploaded, ready to rock.
Mission accomplished.
True, it doesn't do fully hands-off replication. But I have to sign on to their web admin panel monthly anyway to renew my server LetsEncrypt certificates, so when I'm in there I just pop over to PHPMyAdmin (and I can grab the credentials from the admin panel), then refresh the databases from backup. Pretty easy.
If either app has an outage and I want to start using the hot spare, I can either tolerate stale data and use it right away, or go to PHPMyAdmin and refresh it from the backups that are already there. I'm assured that my backups work without tampering with the "production" site, and I can do upgrades on the hot spare first to confirm it works.
Now I have two instances of Nextcloud that are behind on upgrades, but those are going to take some more work to sync before I feel safe upgrading.