All Categories
Database Backup
Restore PlanetScale / Vitess MySQL Backups

Restore PlanetScale / Vitess MySQL Backups

This guide covers the entire process of restoring PlanetScale / Vitess backups along with schema and data.

In this guide we will go through the process of restoring a MySQL backup. This method is applicable to anyone using PlanetScale or Vitess for scalable MySQL deployments.

 

Step 1: Download the PlanetScale / Vitess Backup File

First, locate the URL of your backup, and download it to your local system:

wget 'http://example.com/path/to/your-planetscale-vitess-backup.tar.gz' -O backup.tar.gz
 

Step 2: Extract the Backup File

Extract the downloaded file containing your PlanetScale / Vitess MySQL backup:

tar -xzvf backup.tar.gz

Navigate into the extracted directory:

cd your_backup_folder
 

Step 3: Identify and Import Schema Files

Schema files ending in -schema.sql can be imported as follows:

for file in *-schema.sql; do
  mysql -u username -p'password' -h host -P port < "$file"
done
 

Step 4: Identify and Import Data Files

Import all other .sql files, excluding the schema files, as data files with these commands:

for file in *.sql; do
  if [[ ! "$file" == *-schema.sql ]]; then
    mysql -u username -p'password' -h host -P port < "$file"
  fi
done
 

Streamlining Your Restoration Process with SimpleRestore

While the above steps guide you through manually restoring your PlanetScale / Vitess MySQL backups, we understand that managing this process can be complex and time-consuming. To further simplify your backup restoration, consider using our SimpleRestore tool. SimpleRestore offers an intuitive and automated approach to restoring your databases, reducing the manual workload and potential for error. Learn more and streamline your backup restoration at simplerestore.io.

Common Errors and Solutions

During the process of importing the MySQL backup files, you may encounter some common errors. Here's how to resolve them:

 

Error: Unknown Collation 'utf8mb4_0900_ai_ci'

If you encounter the error ERROR 1273 (HY000) at line 1: Unknown collation: 'utf8mb4_0900_ai_ci', it means that the collation is not recognized by your version of MySQL. This can be resolved by replacing the unsupported collation with a supported one.

 

For Linux:

After extracting the backup and before importing the schema and data files, run:

sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_general_ci/g' *.sql
 

For macOS:

The command differs slightly on macOS. Run:

sed -i '' 's/utf8mb4_0900_ai_ci/utf8mb4_general_ci/g' *.sql

This modification ensures compatibility with older versions of MySQL by replacing the 'utf8mb4_0900_ai_ci' collation with 'utf8mb4_general_ci'.

 

Important Notes

  • Database Credentials: Replace username, password, host, port, and your_database with your specific details.
  • MySQL Server: Ensure that your MySQL server is running and that you have the required permissions.
  • Security Consideration: Utilize a configuration file or environment variables for secure authentication.
  • Database Creation: If needed, create the database:
      • mysql -u username -p'password' -h host -P port -e "CREATE DATABASE your_database;"
       
       
 
Did this answer your question?
😞
😐
🤩

Last updated on November 30, 2023

Hot Backups in MySQL Using Percona XtraBackup Restore a PostgreSQL backup