/ tech-note

How to backup MySQL database on Compute Engine?

If you are looking for how to backup your MySQL database that is hosted over a cloud instance, it shall be better to use Cloud SQL or something others that cloud providers offer, as they take care of replication, backup, patches and updates, etc...

However, it is a bit expensive IMHO, so let's backup your database manually on the instance.

First, use mysqldump command. However, since the redirection doesn't work without running as root, alternatively you run it as bash, like:

sudo bash -c "mysqldump -uDB_USER -p DB_NAME --single-transaction | gzip -9 > backup.sql.gz"

Or if you use mysql_config_editor, you can avoid typing in the password at the prompt.

Now you obtain the backup file. So you can port it anywhere you like, such as Cloud Storage.

In order to unarchive the .gz file, use gunzip:

gunzip backup.sql.gz