MySQL Samples
Below are examples of commands and configuration files for the SqlBak-CLI
utility:
1. Backup three databases, and saving to /backups/
Command to run a backup
sqlbak-cli run-backup --job-settings=backup-settings.json
Backup settings
{
"source": {
"type": "mysql",
"host": "localhost",
"user_name": "root",
"databases": [
"luxes",
"sakila",
"RevertAge"
]
},
"storages": [
{
"type": "folder",
"path": "/backups/",
"keep_settings": [
{ "backup_type": "full", "hours": 1 }
]
}
]
}
Command to run a restore
sqlbak-cli run-restore --job-settings=restore-settings.json
Restore settings
{
"type": "restore",
"target": {
"type": "mssql",
"data_source": ".",
"trust_server_certificate": true,
"user_name": "sa",
"user_password": "********",
"databases": [
{
"source_name": "wordpress",
"target_name": "wordpress"
},
{
"source_name": "logs",
"target_name": "logs"
},
{
"source_name": "market",
"target_name": "market"
}
]
},
"storage": {
"destination_type": "sftp",
"server_name": "backup-storage.com",
"user_name": "Pranastest",
"user_password": "*************",
"path": "backups"
}
}
2. Backup all MySQL databases on Windows and send to a Linux server over SSH
If the backup was successfully sent over SSH, then the backup job will delete previously uploaded backups that are older than 168 hours (7 days).
Command to run a backup
sqlbak-cli run-backup --job-settings=backup-settings.json
Backup settings
{
"source": {
"type": "mysql",
"mysql_bin_folder": "c:\\Program Files\\MySQL\\MySQL Server 8.2\\bin\\",
"host": "localhost",
"user_name": "root",
"user_password": "********",
"database_types": [ "mysql_custom" ]
},
"storages": [
{
"type": "sftp",
"server_name": "104.45.36.119",
"user_name": "Pranastest",
"user_password": "********",
"path": "backups",
"keep_settings": [
{
"backup_type": "full",
"hours": 168
}
]
}
]
}
Command to run a restore
sqlbak-cli run-restore --job-settings=restore-settings.json
Restore settings
{
"source": {
"type": "mysql",
"mysql_bin_folder": "c:\\Program Files\\MySQL\\MySQL Server 8.2\\bin\\",
"host": "localhost",
"user_name": "root",
"user_password": "*******",
"databases": [
{
"source_name": "wordpress",
"target_name": "wordpress"
},
{
"source_name": "logs",
"target_name": "logs"
},
{
"source_name": "market",
"target_name": "market"
}
]
},
"storages": [
{
"type": "sftp",
"server_name": "204.25.34.219",
"user_name": "Pranastest",
"user_password": "******",
"path": "backups"
}
]
}
3. Backup MySQL Server and send backup to FTP, in case of failure save in local folder.
For backup storage, you can set the flag is_emergency
to true; backups will only be sent to this storage if the sending to non-emergency storages fails.
We recommend always using at least one emergency storage.
Command to run a backup
sqlbak-cli run-backup --job-settings=backup-settings.json
Backup settings
{
"source": {
"type": "mysql",
"host": "localhost",
"user_name": "root",
"user_password": "*******",
"databases": [ "sakila" ]
},
"storages": [
{
"type": "ftp",
"server_name": "167.172.189.114",
"user_name": "ftpuser",
"user_password": "*****",
"path": "/files",
"keep_settings": [
{
"backup_type": "full",
"hours": 120
}
]
},
{
"type": "folder",
"path": "c:\\tmp\\",
"keep_settings": [
{ "backup_type": "full", "hours": 1 }
],
"is_emergency": true
}
]
}
Restore settings
In this example, the database is restored under a different name: sakila -> copy_of_sakila
{
"type": "restore",
"target": {
"type": "mysql",
"host": "localhost",
"user_name": "root",
"user_password": "*******",
"databases": [
{
"source_name": "sakila",
"target_name": "copy_of_sakila"
}
]
},
"storages": [
{
"type": "ftp",
"server_name": "167.172.189.114",
"user_name": "exampleuser",
"user_password": "********",
"path": "/files"
}
]
}