Automatic offsite PlanetScale database backups

Summary

In this informative video, Brian Morrison from Planet Scale's developer education team demonstrates how to use the Planet Scale CLI and a Cron job to automate off-site backups of a Planet Scale database on an Ubuntu virtual machine in AWS. He covers the setup of a bash script named 'backupdb.sh' that utilizes the 'p-scale db dump' command to create MySQL dumps at scheduled intervals. The script is designed to run every minute for the demo, but can be adjusted for different backup frequencies. The video also explains how to handle environment variables and execute the script within the context of the logged-in user to ensure the Cron job's success.

Takeaways

• 🚀 Brian Morrison, part of the Developer Education team at PlanetScale, demonstrates how to use the PlanetScale CLI and a Cron job for off-site database backups.
• 💻 All steps are executed within an Ubuntu virtual machine on AWS, with the PlanetScale CLI pre-installed and configured, along with service token authentication.
• 🔧 The 'p-scale dbls' command is used to list databases in PlanetScale, showcasing the setup of the 'bookingsdb' database.
• 📦 The 'p-scale DB dump' command is introduced for creating MySQL dumps from a PlanetScale database, requiring the database name and branch name, with an optional output directory.
• 🛠️ A practical scenario of automating backups through a Bash script and Cron job is explained, ensuring backups occur at regular intervals.
• ⏱️ The script utilizes a timestamped directory for each backup, ensuring a unique directory for every backup instance.
• 🔑 Environment variables for service token and ID are crucial for authentication and must be set for the script to execute successfully.
• 📂 Brian covers how to add environment variables to '/etc/profile' to ensure they are available for the Cron job, addressing execution context issues.
• 🔄 A Cron job setup is detailed, highlighting the importance of setting the environment for the script and choosing an appropriate backup interval.
• 📈 The video concludes with a successful demonstration of the automated backup process, showcasing the creation of timestamped backup directories.

Q & A

What is the purpose of using the Planet Scale CLI in the video?

The purpose of using the Planet Scale CLI is to create off-site backups of a database hosted on Planet Scale.

Who is the speaker in the video?

The speaker in the video is Brian Morrison, a member of the Developer Education team at Planet Scale.

Which operating system is used for the demonstration in the video?

The demonstration is performed inside an Ubuntu virtual machine hosted in AWS.

What are the two required parameters for the p-scale db dump command?

The two required parameters are the name of the database in Planet Scale and the branch name.

What is the significance of the output directory in the p-scale db dump command?

The output directory is where the database dump will be saved. It must not exist before the dump takes place, otherwise, the command will fail.

How does the video demonstrate the creation of a MySQL dump of a database?

The video demonstrates this by using the p-scale db dump command with the database name and branch name as parameters.

What is the role of the bash script named backupdb.sh in the video?

The backupdb.sh script is used to automate the database backup process at regular intervals using a Cron job.

How does the video ensure that the backup script runs with the correct environment variables?

The video adds the environment variables to the /etc/profile file, which is then utilized by the Cron job when executing the script.

What is the interval at which the Cron job is set up to run in the video?

For demonstration purposes, the Cron job is set up to run every minute, but typically, backups would be scheduled on a daily, weekly, or monthly basis.

How can you verify that the Cron job has executed successfully?

You can verify the successful execution of the Cron job by monitoring the p-scale dump.log file and checking for new backup folders with timestamps in the home directory.

What additional automation feature is mentioned in the video for Planet Scale and GitHub?

The video mentions the use of Planet Scale GitHub actions to automatically create database branches whenever a new branch is created in GitHub.