How It Works

⌘K
  2. Home
  4. Docs
  6. How It Works
  8. How to Backup/Restore Dat...
  10. MySQL

MySQL

Full backup

For MySQL, a backup is created using the utility mysqldump.exe.

mysqldump  --user "user" --password="xxxxxxxxx" --databases "db_name" --result-file="c:\path\to\backup\sakila202403291110.sql"

This utility creates a logical backup in the form of an SQL script.

Restore is performed using the utility mysql.exe.

mysql <  sakila202403291110.sql
In order to see the full commands (with all used parameters), click Tools – Advanced Log… and enable the Trace level. Next, perform a backup or restore and search for the substring “Mysqldump parameters” or “The MySQL command” in the logs. These lines will contain all executions of the mysql.exeand mysqldump.exeutilities.

Additionally, if you click the information button in the connection settings, you will see the command used to connect to the database. If you encounter connection issues, you can manually execute this command via the command line to diagnose the problem.

mysql.exe and mysqldump.exe are installed along with the application files in the directory C:\Program Files (x86)\SQLBackupAndFTP\DBMS\MySql\.

If you encounter version compatibility issues, you can specify the path to these utilities yourself in the advanced connection settings. Just specify the path to MySQL Workbench utilities, for example, “C:\Program Files\MySQL\MySQL Workbench 8.0.”

Incremental backup

Incremental backups are created based on MySQL binary logs. For the application to be able to create these backups, binary logging must be enabled.

How to Enable Binary Logging on Windows

How does SQLBackupAndFTP backup binary logs?

  1. SQLBackupAndFTP runs a FLUSH BINARY LOGS;operation which leads to a new binlog file creation
  2. SQLBackupAndFTP remembers the name of the newly created binlog file, this file will be the beginning of the next incremental backup
  3. SQLBackupAndFTP reads the list of available binlog files from the binlog.index file
  4. SQLBackupAndFTP copies all binlog files that have appeared since the previous incremental backup to an archive

We recommend creating one backup job for all databases. This is due to the fact that all transactions for all databases are written in one file. Parallel launch of incremental backups in different jobs will lead to duplication of data in backups and increase usage of free space in the destinations.

SQLBackupAndFTP must have access to the directory where the binlog files are stored. That means that the app must be installed on the same server where MySQL Server is installed.

When does SQLBackupAndFTP perform a full backup instead of an incremental one?

  1. After adding incremental backups to a backup plan. To restore data from incremental backups, a full backup created with special options is required. By default, SQLBackupAndFTP does not use them. Therefore, the first full backup will be executed with additional parameters.
  2. When data schema (tables or columns) have been changed. Transactions that change the data schema are also written to binlog files. These transactions are written at the DBMS level, and they cannot be correctly retrieved to restore a database under a different name. To understand that the data schema has been changed, SQLBackupAndFTP performs a data schema backup and remembers its checksum. If the checksum changes, then SQLBackupAndFTP runs a full backup.
  3. If a previous run of a backup job failed. If a backup job fails, it leads to a backup chain violation, so SQLBackupAndFTP creates a full backup to ensure recovery without missing data.
  4. After restoring any database via SQLBackupAndFTP. All SQL statements that are involved in a database restore process go into binlog files, which greatly increases its size. Therefore, to save space, SQLBackupAndFTP performs a full backup.