# MySQL Backup

Back up MySQL and MariaDB databases with scheduled dumps to any cloud storage provider.

SimpleBackups connects to your MySQL or MariaDB database — on your own server, inside a Docker container, or via a managed cloud provider — and creates scheduled dump files using `mysqldump`. Backups are compressed, uploaded to your chosen storage, and retained according to the policy you set. For connection method options, see the [database backup overview](https://simplebackups.com/docs/database-backup/overview).

## Backup options

By default, SimpleBackups runs `mysqldump` with standard settings. You can extend this behavior for specific needs.

### Passwordless authentication

Passwordless authentication lets you keep your database credentials off the SimpleBackups configuration by reading them from a `.my.cnf` file on your server instead.

1. Create or edit your MySQL configuration file (typically `/etc/mysql/my.cnf` or `/etc/mysql/conf.d/client.cnf`) and add your credentials under the `[client]` section:

```plain
[client]
user=MYSQL_USER
password=MYSQL_PASSWORD
port=3306
host=127.0.0.1
```

2. Verify the setup by running `mysql` from the command line without arguments — it should connect without prompting for credentials.
3. When creating a backup in SimpleBackups, select **Use .my.cnf** as the authentication method.

**Info:**
Passwordless authentication requires your own server. It is not compatible with the serverless backup option.

### Stored procedures and functions

Stored procedures and functions are not included in a single-database dump by default. To include them, open the backup, click **Advanced**, enter `--routines` under **Custom Dump Flags**, and click **Save**.

### Hot backups with Percona XtraBackup

For production databases where locking is not acceptable, you can use Percona XtraBackup to take hot backups without interrupting reads or writes.

- [Hot backups with Percona XtraBackup](https://simplebackups.com/docs/database-backup/mysql-backup/hot-backups-in-mysql-using-percona-xtrabackup): Set up Percona XtraBackup to take non-locking hot backups of your MySQL database.

### Binary log (incremental) backups

Binary log backups enable point-in-time recovery by capturing every change between full dumps.

- [Enable binary log for MySQL or MariaDB](https://simplebackups.com/docs/database-backup/mysql-backup/how-to-enable-binary-log-for-mysql-or-mariadb): Configure MySQL binary logging to enable incremental backups and point-in-time recovery.

## Restoring a backup

- [Restore a MySQL backup](https://simplebackups.com/docs/database-backup/mysql-backup/restore-a-mysql-backup): How to download and restore a standard MySQL dump file.

### PlanetScale and Vitess

PlanetScale and Vitess backups use a split-file format — one `-schema.sql` file per table plus separate data files — rather than a single dump. To restore:

1. Download and extract the backup archive:

```bash
wget "SIGNED_DOWNLOAD_URL" -O backup.tar.gz
tar -xvf backup.tar.gz && cd extracted-directory
```

2. Import schema files first:

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

3. Import data files, skipping schema files:

```bash
for file in *.sql; do
  [[ "$file" == *-schema.sql ]] && continue
  mysql -u username -p'password' -h host -P port < "$file"
done
```

Replace `username`, `password`, `host`, and `port` with your database credentials.

**Collation mismatch:**
If you see `ERROR 1273: Unknown collation: 'utf8mb4_0900_ai_ci'`, run the following before importing to replace the unsupported collation:

```bash
# Linux
sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_general_ci/g' *.sql
# macOS
sed -i '' 's/utf8mb4_0900_ai_ci/utf8mb4_general_ci/g' *.sql
```

## Troubleshooting

- [MySQL backup FAQ](https://simplebackups.com/docs/database-backup/mysql-backup/faq): Common errors and fixes for MySQL backup operations.