Hi. I’m not at all familiar with Docker and it took me a lot of time, patience, effort and several online guides to finally get my deployment working a while ago. I have three containers (because for some inscrutable reason it’s not “best practice” to have a single container with everything I need in it - God forbid anything is easy), one of which has my application itself and another of which has a MariaDB database. That container connects only to the main one and isn’t accessible from the Internet.
I need to back up that database. I know I need to change things in its fly.toml file to do that, though I don’t know exactly what. Then I need to run mysqldump in that container - but how? Guides I found online mention using docker exec db_container_name mysqldump etcetera but I don’t know how to pass a docker command along via flyctl.
I am beyond frustrated. Can somebody please help me?
Hi… It shouldn’t actually be necessary to modify fly.toml for this. That’s because flyctl can bridge the MariaDB Machine’s port over temporarily, so it behaves as if it’s listening at localhost:13306 on your development laptop/desktop. (What’s really happening is that flyctl is shuttling packets local ↔ remote behind the scenes.) Alternatively, you can run the dump command on the Machine itself, and then download the result via SFTP.
I admittedly don’t know MySQL itself that well, but here’s how that looks with Postgres:
(Note that you leave flyctl proxy running for as long as you need the connection. This surprises many people.)
Finally, separating the web-app from the database has many advantages and one near-necessity on the Fly.io platform: single-volume deployments are unusually risky here. If you’re taking regular backups and can afford to lose all the writes that occurred in the interim, then you may be ok(-ish), but many people have been unpleasantly surprised to find that they’ve lost entire databases, permanently…
Thank you very much. Now I have a different problem. Progress!
I edited the fly.toml and Dockerfile files to add the variables MYSQL_ROOT_PASSWORD, MYSQL_USER and MYSQL_PASSWORD (identical in both files) and set the variable MYSQL_RANDOM_ROOT_PASSWORD = “no” in fly.toml (it was previously set to “yes”) and redeployed. Then I ran fly proxyas described in the link you provided, left that terminal window open and attempted to connect to my database from another terminal.
However, I run into the following error:
ERROR 2026 (HY000): TLS/SSL error: SSL is required, but the server does not support it
So I added --ssl=FALSE --ssl-verify-server-cert=FALSE to the mariadb command. Now I get a different error:
ERROR 1045 (28000): Access denied for user 'root'@'fdaa:2:3979:a7b:9330:0:a:b00' (using password: YES)
…and an identical error for the other user I created. I tried forwarding to a few different ports to rule out a problem with the default one, and I get the same error. When attempting to access the selected port in my browser, I get a garbled MariaDB message, so at least I know that fly proxyis working as it should:
i���
11.1.1-MariaDB-1:11.1.1+maria~ubu2204�
���OMqtpU:b�юч-�яЃ������ ���^4%Zxk3ZMR3G�mysql_native_password�!��я„#08S01Got packets out of order
I also can’t ssh into the machine using localhost and the proxied port. I googled around but did not find any leads, as all the solutions require root access to the system running the database.
It seems I moved in the right direction but I still haven’t reached the finish line. Help?
Glad to hear there was progress… This part is easier: fly ssh console --user root -a db-app-name.
Changing the environment variables might not have altered any passwords, since those are typically stored on its disk after the first boot. Database containers that I’ve looked at in the past didn’t try to detect changes, in other words, only complete absence. I don’t know that that’s definitely true of the one you’re using, however. Anyway, if it was set to generate a random string when it first booted, then it might be necessary to go through the full “forgot root† password” process at this point.
†The password within MariaDB, I mean, not the one for the Machine’s own superuser.
It worked! I had to add the --skip-grant-tables argument to the Dockerfile’s CMD line and redeploy in order to reset the root password, but then I managed to use fly ssh to dump the database and transfer it to my local machine. What an ordeal!
Thank you so very much, I could never have done this without your guidance.
This probably stems from the microservices approach of each container being independently scalable and independently monitorable. I do generally try to stick to it, but once one gets a bit more experienced with Docker, it is possible to experiment with breaking this guideline.
Fun fact: the forum software we’re using right now is packaged as a single Docker container, including the database.