DEV Community

Appwrite In Production: Backups and Restores

Bradley Schofield on July 10, 2021

Backing up and restoring data is an extremely important part of running servers. It's a virtual safety net against most bad things that can happen....

Read full post

Fabian Reyes • Jul 21 '21

Hi, I have tried to replicate this guide and everything works fine except the database restore, I get this error: ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'mysqldump: Got error: 1045: "Access denied for user 'user'@'localhost' (using...' at line 1

Eldad A. Fux • Sep 4 '21

I think you need to make sure your target database has the same user credentials.

Fabian Reyes • Sep 20 '21 • Edited

For now, I managed to restore the files and the database without any problems. Now, there is a problem. The files do not work, apparently they were restored but it is not possible to access or download them.

dev-to-uploads.s3.amazonaws.com/up...
dev-to-uploads.s3.amazonaws.com/up...

Eldad A. Fux • Sep 21 '21

Make sure you use the same encryption key in your env file

Fabian Reyes • Sep 21 '21

You mean, this one? _APP_OPENSSL_KEY_V1

Eldad A. Fux • Sep 21 '21

Yep :)

Steven • Dec 2 '21

I think there is a typo/bug in the user part of the database backup command. I think it should be:

docker-compose exec mariadb sh -c 'exec mysqldump --all-databases --add-drop-database -u"root" -p"$MYSQL_ROOT_PASSWORD"' > ./dump.sql

Vincent Ge • May 4 '22

Thanks for pointing this out ;)

Took a while but we got to it! It should be fixed now <3

Steven • May 8 '22

Thanks! Looks like the same typo is in the mariadb restore command too.

Steven • Dec 6 '21

I think people have also had problems migrating if they don't migrate their appwrite-certificates and maybe appwrite-config volume. This is because the database has data about certificates generated, but the certificates don't actually exist on disk so HTTPS doesn't work.

If you didn't get a chance to backup those, you can try to manually delete the certificates info in the database manually. $ and > characters to indicate whether the command is executed in the shell or mysql CLI, respectively:

# shell into the mariadb container
$ docker-compose exec mariadb sh

# authenticate with mysql
# do not add whitespace between -p and password
$ mysql -u root -p"$MYSQL_ROOT_PASSWORD"

# use the appwrite database
> use appwrite;

# Find the documentID for your domain in the database:
> SELECT * FROM `app_console.database.properties` p where p.key = "domain";

# note the documentUid - that's what we'll use to query/delete them from the database
# We'll need to remove the document from two tables, here's the other one
> SELECT * FROM `app_console.database.documents` d where d.uid = "thedocumentUID";

# Now we can delete these two rows:
> DELETE FROM `app_console.database.properties` where documentUid = "thedocumentUID";
> DELETE FROM `app_console.database.documents` where uid = "thedocumentUID";

Credits to @kodumbeats for the SQL queries.

Brian Mutiso • Jun 10 '22

I was not able to back up maria db all the scripts in this article only produces a 0byte backup file

docker-compose exec mariadb sh -c exec mysqldump --all-databases --add-drop-database -u"root" -p"$MYSQL_ROOT_PASSWORD" > ./dump.sql

subrotoxing • Apr 15 '22 • Edited

I'm trying to backup my database ... but dump.sql seems to be empty... please advise

i'm typing this into cmd where the .env file located

docker-compose exec mariadb sh -c exec mysqldump --all-databases --add-drop-database -u user -p password > ./dump.sql

Matej Bačo • May 4 '22 • Edited

Hey there 👋 Tiny syntax mistake, it should be:

docker-compose exec mariadb sh -c exec mysqldump --all-databases --add-drop-database -u"user" -p"password" > ./dump.sql

Nicolas Klein • Jul 29 '22

Still does not work for me on Appwrite v15

Matej Bačo • Nov 1 '21

You can't imagine how many times have I opened this article 😅 Thanks a lot for this article!

mandolf0 • Mar 21 '23 • Edited

Your restore/backup gist helps tremendously. I just migrated local dev to the ☁. One suggestion I'd like to make is to delete the cloud function deployments and use only the latest one. The backup I moved was 800MB because of those deployments. Other than that, 100% confirmed it works to a tee.

As of now with Appwrite 1.2.0, I deleted some rows in other tables.

delete from _console_domains;

//show domain permissions
select * from \_console_domains_perms;

//delete domain permissions
delete from_console_domains_perms;

rsync is our friend when moving to a new server.

rsync -avz --progress backups/backup-2023-03-21T02-05-16.tar.gz root@[targetServer]:/root/appwrite/backups/backup.tar.gz

2023-03-21T02-05-16.tar.gz is the source file to copy
backup.tar.gz is the target because that is the name restore.sh expects to find alongside it.

Issue certificates
docker-compose up -d docker compose exec appwrite ssl domain="app.mydomain.com"

FuadEfendi • Oct 3 '22

And how to backup/restore on MacOS?

Brian Mutiso • Jun 10 '22

I don't seem to get it, even after running the above commands i still get an empty dump.sql file

Nicolas Klein • Jul 29 '22

I found this solution on discord:

here , what i did and it works
backup data :
docker-compose exec mariadb sh -c 'exec mysqldump --all-databases --add-drop-database -u"user" -p"password"' > ./dump.sql

restore data :
docker-compose exec -T mariadb sh -c 'exec mysql -u”user”-p"password"' < dump.sql