Databases
Managing database backups
We recommend using the managed database backup service to manage and restore database backups, but you can also manage them manually. This includes downloading the backup, decompressing it, moving it to another server and restoring it.
Restoring an automated backup
You can restore a backup directly through Cloud 66 dashboard backup page. There is a Restore button for each backup that will automatically download that backup to your server and restore it. If you'd prefer to do so manually, follow one of the guides below.
Managed backup disk space requirements
Although managed backups are stored separately from servers, the creation of each backup is run on your database server before the archive is moved to Cloud 66’s managed backup servers.
As such your server requires enough disk space to (temporarily) hold a single database backup. If your server is low on disk space you may encounter an error like this: Not enough free space. You need at least xxx MB free space for this backup
Before taking backups we calculate the size of your data directory and make sure that your server has at least twice that much storage free. This ensures that the backup doesn’t fill up the hard drive entirely.
We have suggested some methods for adding or clearing up disk space below, but before proceeding we recommend you have a solid grasp on how disks and volumes work in Ubuntu (and Linux in general). This excellent thread, this guide to mount command, and this guide to partitions explain all the concepts.
Resolving space issues
There are three main ways to resolve a lack of storage space:
1. Add additional disk space to your cloud server
Attach a new disk to your server and mount "/var/cloud66/backups"
on the new disk. Please remove the existing /var/cloud66/backups
by running "sudo rm -rf /var/cloud66/backups"
before mounting "/var/cloud66/backups"
.
You can mount the volume using a command like this:
$ mount -o discard,defaults,noatime /dev/disk/by-id/<ID-OF-DISK> /var/cloud66/backups
Your cloud provider will provide the ID of the disk, as well as its path (usually under /dev
). Note that this is different from the default name of the volume that they create for you.
If you want your disk mount to persist after the server is restarted, you should use a command like this one:
$ echo "/dev/disk/by-id/<ID-OF-DISK> /var/cloud66/backups ext4 discard,nofail,defaults 0 0" >> /etc/fstab
2. Clear your server of unneeded files
Your server may have outdated or unneeded files (logs are a common culprit). A handy way to identify these is to use the ncdu
command - this comprehensive guide explains how to install and use ncdu
.
3. Disable backup size checks
If you are confident that you have enough space and the first option is not possible, you can use the Cloud 66 Toolbelt to run "cx settings set -s STACK_NAME db.check.backup.size false"
where STACK_NAME
is the name of your application. This will disable backup size checks.
Check disk space first
If your server does not have enough space to accommodate the backup, this setting could cause the backup to fail, or the server disk to be fully utilised
Migrating data between applications
You can use automated database backups to migrate data between applications using the Dashboard, but there are some format restrictions when doing so:
DB Engine | Compatible backup format(s) for migration |
---|---|
MySQL | Binary only |
PostgreSQL | Text only |
Mongo | Either |
Redis | Either |
This only applies to migrations between applications - restores to the original database (i.e. in the same application) will work regardless of format.
To migrate data between applications:
- Add a new server of the same type (e.g. MySQL) on the destination application
- Visit the new server's page in the Cloud 66 Dashboard (click on the server type and then the name of the server)
- Click the Import Database button (on the right-hand side)
- Choose the source application (i.e. where you're pulling the backup from) and click Import - this will automatically "restore" the latest database backup to your new server.
Remember to wait for the task to be completed before you start trying to use the database for anything.
Downloading a backup
You can manually retrieve a backup in one of three ways:
1. Using Cloud 66 Toolbelt
If you are using managed backups, you can retrieve your database backup by using Cloud 66 Toolbelt. Your backup may be bigger than 350 MB, in which case it will be divided into several files. By using the toolbelt, the files are downloaded and concatenated automatically for you.
2. From your Cloud 66 Dashboard
If you are using managed backups, you can download backups via your Cloud 66 Dashboard. To do so, open your Application Overview and click the link for your database backup in the Backups panel.
This page lists your available database backups, and allows you to download and restore each one. Click the download icon and choose one of these options:
- Copy the script (shell command) by clicking on the link, and then paste this into a terminal (or shell) on the machine where you want the files to be downloaded. This will automatically download and concatenate your backup files to the destination chosen.
- Click on the "more" link to display the direct link(s) to the backup file(s). These links are only active for a few minutes, so you should use them immediately. Clicking a link will download that file via your browser.
3. Via command line
You can also copy the direct links (see above) and then manually curl
these links to your computer or server. Remember that these links are time-bound - they will expire after a few minutes.
$ curl -o "YOUR_BACKUP_FILE_NAME" "GENERATED_URL"
Preparing files for manual restoration
After downloading a backup you will need to follow several steps to restore that data. Except for the first two, these steps differ by database engine.
Concatenate multiple files
NOTE: This step can be skipped if you use the Toolbelt or script option to download the backup.
If your total backup is larger than 350MB we will split it across multiple files in increments of 350MB. Before restoring this data, you will need to concatenate (join) these files into a single large file. For example:
$ cat file.tar-aa file.tar-ab file.tar-ac > file_combined.tar
Unarchive the backup files
NOTE: This step can be skipped if you use the Toolbelt or script option to download the backup.
Before manually restoring your data will need to decompress the backup file. The file will be in the tar
format. To decompress it use:
$ tar -xvf [tar_file] -C [folder_name]
The -C option allows you to choose which folder to extract the files to.
Once you have your files prepared, you can follow the guide for your specific backup format and engine (see below).
Restoring a MySQL text backup
We recommend automated backups
This is a method for manually restoring your MySQL text backup - we only recommend this method if you cannot use the automated method described above. Please also ensure you have prepared your files before proceeding.
Step 1: Run following command to flatten the folder
$ find /path/to/unarchived/folder -type f -exec mv -i {} /path/to/unarchived/folder \;
Step 2: Run following command to find the data file
$ find /path/to/unarchived/folder '(' -name '*.sql' -o -name '*.sql.gz' ')' -type f -exec basename {} ';'
Step 3: If the result of previous step has a *.gz extension run following command to unzip it, unless go to next step.
$ gzip -d /path/to/unarchived/folder/data_file_from_previous_step
Step 4: In order to purge old data you can drop your current db and create a new one. To start, you need to set some environment variables.
You can find YOUR_MYSQL_DB_APP_USERNAME
, YOUR_MYSQL_DB_APP_PASSWORD
,YOUR_MYSQL_ADMIN_USERNAME
,YOUR_MYSQL_ADMIN_PASSWORD
and YOUR_MYSQL_DATABASE_NAME
on the Cloud 66 Dashboard MySQL server detail page. You can then use these in the commands below:
$ export $MySQL_DB_APP_USERNAME="YOUR_MYSQL_DB_APP_USERNAME"
$ export $MySQL_DB_APP_PASSWORD="YOUR_MYSQL_DB_APP_PASSWORD"
$ export $MySQL_ADMIN_USERNAME="YOUR_MYSQL_ADMIN_USERNAME"
$ export $MySQL_DATABASE_NAME="YOUR_MYSQL_DATABASE_NAME"
4.1 Use following commands to drop your database
$ MySQL -u $MySQL_ADMIN_USERNAME -p$MySQL_ADMIN_PASSWORD -e "DROP DATABASE $MySQL_DATABASE_NAME ;"
$ MySQL -u $MySQL_ADMIN_USERNAME -p$MySQL_ADMIN_PASSWORD -e "FLUSH PRIVILEGES ;"
4.2 Use following command to create a new database
$ MySQL -u $MySQL_ADMIN_USERNAME -p$MySQL_ADMIN_PASSWORD -e "CREATE DATABASE $MySQL_DATABASE_NAME CHARACTER SET utf8;"
4.3 Use following commands to revoke user privileges
$ MySQL -u $MySQL_ADMIN_USERNAME -p$MySQL_ADMIN_PASSWORD -e "REVOKE ALL PRIVILEGES ON $MySQL_DATABASE_NAME.* FROM '$MySQL_DB_APP_USERNAME'@'localhost';"
$ MySQL -u $MySQL_ADMIN_USERNAME -p$MySQL_ADMIN_PASSWORD -e "REVOKE ALL PRIVILEGES ON $MySQL_DATABASE_NAME.* FROM '$MySQL_DB_APP_USERNAME'@'%';"
$ MySQL -u $MySQL_ADMIN_USERNAME -p$MySQL_ADMIN_PASSWORD -e "FLUSH PRIVILEGES;"
4.4 Use following commands to give enough permission to you app user
$ MySQL -u $MySQL_ADMIN_USERNAME -p$MySQL_ADMIN_PASSWORD -e "GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,INDEX,ALTER,LOCK TABLES,CREATE VIEW,SHOW VIEW,EXECUTE,TRIGGER,CREATE TEMPORARY TABLES,CREATE ROUTINE,ALTER ROUTINE,EXECUTE,REFERENCES ON $MySQL_DATABASE_NAME.* TO '$MySQL_DB_APP_USERNAME'@'localhost';"
$ MySQL -u $MySQL_ADMIN_USERNAME -p$MySQL_ADMIN_PASSWORD -e "GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,INDEX,ALTER,LOCK TABLES,CREATE VIEW,SHOW VIEW,EXECUTE,TRIGGER,CREATE TEMPORARY TABLES,CREATE ROUTINE,ALTER ROUTINE,EXECUTE,REFERENCES ON $MySQL_DATABASE_NAME.* TO '$MySQL_DB_APP_USERNAME'@'%';"
$ MySQL -u $MySQL_ADMIN_USERNAME -p$MySQL_ADMIN_PASSWORD -e "FLUSH PRIVILEGES;"
Step 5: Finally, use the following command to restore your database:
$ MySQL -u $MySQL_ADMIN_USERNAME -p$MySQL_ADMIN_PASSWORD $MySQL_DATABASE_NAME < /path/to/unarchived/folder/data_file
Restoring a MySQL Binary backup
We recommend the automated method
This is a method for manually restoring your MySQL binary backup - we only recommend this method if you cannot use the automated method described above. Please also ensure you have prepared your files before proceeding.
You need Percona innobackupex to be able to restore a MySQL binary backup. Percona innobackupex is installed on your MySQL server if it is provisioned by Cloud66. If you need to install it on another server, follow this guide.
Step 1: Find the Percona backup folder. Run following command to find it:
$ find /path/to/unarchived/folder -name ibdata1 -type f -exec dirname {} ';'
Step 2: Find the MySQL Data Directory . You should be able to find it in MySQL configuration file (my.cnf
). In normal MySQL installation you can find MySQL configuration file in /etc/mysql path. Open my.cnf and search for datadir in MySQLd section.
Step 3: Stop the MySQL service :
Ubuntu 18.04 & 20.04
$ sudo systemctl stop MySQL
Step 4: Use following command to delete the MySQL data directory
$ sudo rm -rf /path/to/your/MySQL/data/directory
Back up your data directory
Please make a copy of the MySQL data directory before deleting it so that you can restore if something goes wrong.
Step 5: Use following command to create a blank MySQL data directory
$ sudo mkdir -p /path/to/your/MySQL/data/directory
Step 6: Run following command to restore the Percona backup folder (from step 1)
$ sudo innobackupex --copy-back /path/to/percona/backup/folder
Step 7: Run following command to fix the permissions on the MySQL data directory :
$ sudo chown -R MySQL:MySQL /path/to/your/MySQL/data/directory
Step 8: Start MySQL service
For Ubuntu 18.04 & 20.04 use:
$ sudo systemctl start MySQL
Restoring a Postgres text backup
We recommend the automated method
This is a method for manually restoring your Postgres text backup - we only recommend this method if you cannot use the automated method described above. Please also ensure you have prepared your files before proceeding.
Step 1: Run following command to flatten the folder
$ find /path/to/unarchived/folder -type f -exec mv -i {} /path/to/unarchived/folder \;
Step 2: Run following command to find the data file
$ find /path/to/unarchived/folder '(' -name '*.sql' -o -name '*.sql.gz' ')' -type f -exec basename {} ';'
Step 3: If the result of previous step has a *.gz extension run following command to unzip it, unless go to next step.
$ gzip -d /path/to/unarchived/folder/data_file_from_previous_step
Step 4: On order to clean old data you can drop your current db and create a new one. You can use following scripts to drop and recreate your database but first you need to set some environment variables. You can find YOUR_PG_DATABASE_NAME and YOUR_PG_APP_USERNAME in Cloud 66 Dashboard Postgresql server detail page.
$ export $PG_DATABASE_NAME=YOUR_PG_DATABASE_NAME
$ export $PG_APP_USERNAME=YOUR_PG_APP_USERNAME"
4.1 Use following command to stop all the activities on your db
$ sudo -u postgres psql -c "SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = '$PG_DATABASE_NAME' AND pg_stat_activity.pid <> pg_backend_pid();"
4.2 Use following command to drop your database
$ sudo -u postgres psql -c 'DROP DATABASE IF EXISTS $PG_DATABASE_NAME'
4.3 Use following command to create a new database
$ sudo -u postgres psql -c "CREATE DATABASE $PG_DATABASE_NAME WITH encoding 'unicode'"
4.4 If you are using postgis use following commands to add it to newly created database
$ sudo -u postgres psql -d $PG_DATABASE_NAME -c "CREATE EXTENSION postgis;"
$ sudo -u postgres psql -d $PG_DATABASE_NAME -c "CREATE EXTENSION postgis_topology;"
$ sudo -u postgres psql -d $PG_DATABASE_NAME -c "CREATE EXTENSION fuzzystrmatch;"
$ sudo -u postgres psql -d $PG_DATABASE_NAME -c "CREATE EXTENSION postgis_tiger_geocoder;"
Step 5: You can use following command to restore your database
$ MySQL -u $MySQL_ADMIN_USERNAME -p$MySQL_ADMIN_PASSWORD $MySQL_DATABASE_NAME < /path/to/unarchived/folder/data_file
$ pg -U $PG_APP_USERNAME --no-password $PG_DATABASE_NAME < /path/to/unarchived/folder/data_file
Restoring a Postgres binary backup
We recommend the automated method
This is a method for manually restoring your Postgres binary backup - we only recommend this method if you cannot use the automated method described above. Please also ensure you have prepared your files before proceeding.
Step 1: You need to find the main backup folder in unarchived folder. Run following command to find it :
$ find /path/to/unarchived/folder -name raw -type d
Step 2: Stop the postgresql service :
Ubuntu 18.04 & 20.04
$ sudo systemctl stop postgresql
Step 3: Use following command to delete the Postgres data directory
$ sudo rm -rf /usr/local/pgsql/data
Back up your data directory
Please take a backup from the Postgres data directory before deleting it to be able to restore if something goes wrong.
Step 4: Use following command to create a blank Postgres data directory
$ sudo mkdir -p /usr/local/pgsql/data
Step 5: Use following command to copy the content of main backup folder (step 1)
$ sudo cp -a /path/to/unarchived/folder/main/backup/. /usr/local/pgsql/data/
Step 6: Run following command to fix the permission of Postgres data directory :
$ sudo chown -R postgres:postgres /usr/local/pgsql/data
Step 7: Start the postgresql service
Ubuntu 18.04 & 20.04
$ sudo systemctl start postgresql
Restoring a Redis backup
We recommend the automated method
This is a method for manually restoring your Redis backup - we only recommend this method if you cannot use the automated method described above. Please also ensure you have prepared your files before proceeding.
Step 1: Run following command to flatten the folder
$ find /path/to/unarchived/folder -type f -exec mv -i {} /path/to/unarchived/folder \;
Step 2: Run following command to find the data file
$ find /path/to/unarchived/folder '(' -name '*.rdb' -o -name '*.rdb.gz' ')' -type f -exec basename {} ';'
Step 3: If the result of previous step has a *.gz extension run following command to unzip it, unless go to next step.
$ gzip -d /path/to/unarchived/folder/data_file_from_previous_step
Step 4: Use following command to stop Redis service :
$ sudo systemctl redis.service stop || sudo service redis stop
Step 5: Use following command to delete Redis data file
$ sudo rm -rf /data/redis/dump.rdb
Back up your data directory
Please take a backup from Redis data file before deleting it to be able to restore if something goes wrong.
Step 6: Use following command to copy new data file
$ sudo cp -a /path/to/unarchived/folder/data_file /data/redis/dump.rdb
Step 7: Run following command to fix the permission of Redis data directory :
$ sudo chown -R redis:redis /data/redis
Step 8: Run following command to start Redis service
$ sudo systemctl redis.service start || sudo service redis start
Restoring a MongoDB backup
We recommend the automated method
This is a method for manually restoring your MongoDB backup - we only recommend this method if you cannot use the automated method described above. Please also ensure you have prepared your files before proceeding.
Step 1: Run following command to see if there is database folder in unarchived folder (Replace YOUR_DATABASE_NAME with correct value):
$ find /path/to/unarchived/folder -name YOUR_DATABASE_NAME -type d
If the command returns a result, that is data directory we want to restore. Go to final step.
Step 2: Run following command to flatten the folder
$ find /path/to/unarchived/folder -type f -exec mv -i {} /path/to/unarchived/folder \;
Step 3: Run following command to find the data file
$ find /path/to/unarchived/folder '(' -name 'MongoDB.tar' -o -name 'Mongo*.tar.gz' ')' -type f -exec basename {} ';'
If the result of command has a *.gz extension go to 3.1 otherwise use 3.2
3.1
$ tar -xvf /path/to/unarchived/folder/Mongo*.tar.gz -C /path/to/unarchived/folder && find /path/to/unarchived/folder -type f -exec mv -i {} /path/to/unarchived/folder \;
$ rm -rf /path/to/unarchived/folder/Mongo*.tar.gz
$ rm -rf /path/to/unarchived/folder/MongoDB
3.2
$ tar -xvf /path/to/unarchived/folder/MongoDB.tar -C /path/to/unarchived/folder && find /path/to/unarchived/folder -type f -exec mv -i {} /path/to/unarchived/folder \;
$ rm -rf /path/to/unarchived/folder/MongoDB.tar
$ rm -rf /path/to/unarchived/folder/MongoDB
Step 4: Run following command to clean the unzipped folder (Replace YOUR_DATABASE_NAME with correct value) :
$ rm -rf /path/to/unarchived/folder/YOUR_DATABASE_NAME
$ find /path/to/unarchived/folder -empty -type d -delete
Step 5: Run following command to restore MongoDB. If the step 1 has a result use that as /path/to/database/back unless use /path/to/unarchived/folder . Also replace YOUR_DATABASE_NAME with correct value
$ mongorestore --drop --db YOUR_DATABASE_NAME /path/to/database/back