Source Section

The settings for the SQL Server connection are specified in the source field of the backup job specification, or in the target field if type is specified as mysql.

{
	"type": "mysql", 
	"host": "<string>",
	"port": <int>,
	"user_name": <string>,
	"user_password": <string>,
	"databases": [ <string array> ], 
	"database_types": [ "mysql_custom", "mysql_system" ]
}

Mandatory fields

host

MySQL Server address.

port

The MySQL server’s port can be specified, by default the port 3306 is used.

user_name

The MySQL server login. Default root.

user_password

Login password. Use the [encrypt command][encrypt_command] to keep the password encrypted.

databases or database_types

Please note, databases and database_types are mutually exclusive parameters, and you can specify only one of them.

In this field, the databases that should be backed up should be specified.

This field can be filled in two ways:

databases

Specify a list of databases separated by commas:

...
"databases": [ "Database1", "Database2", "Database.." ]
...

database_types

Specify the type in database_types.

If you specify mysql_custom in database_types, all non-system databases will be backed up:

...
"database_types": [ "mysql_custom" ]
...

Alternatively, if you specify mysql_system in database_types, all system databases will be backed up:

...
"database_types": [ "mysql_system" ]
...

It’s also possible to specify both mysql_custom and mysql_system in database_types at the same time. In this case, all system and non-system databases will be backed up.

Examples

Settings for a local SQL Server connection to backup two databases: DataOne and MediaOne:

...

"source": {
    "type": "mysql",
    "host": "localhost",
    "user_name":"root",
    "user_password": "********",
    "database_types":[ "mysql_custom" ],
    "mysql_bin_folder" : "c:\\Program Files\\MySQL\\MySQL Server 8.2\\bin\\"
},

...

Advanced options

SqlBak-Cli creates a logical backup of the DB in the form of a .sql file using the mysqldump utility. In some situations, additional parameters may be required for this utility to work correctly with your database.

backup_folder

The path to the directory where the backup will be created before sending to storage.

include_column_statistics

The --column-statistics option is not supported by all versions of mysqldump, and even if it is supported, the server might not support it. Therefore, if mysqldump is an older version, this option should not be specified, and if mysqldump is a newer version but MySQL Server is older, then you should set the value to false.

By default, this parameter is not specified.

include_events

By default, mysqldump does not include EVENT type objects in the dump. SqlBak-cli forcibly includes EVENT in the backup. Set this parameter to False if you do not need it.

write_blob_as_hex

By default, sqlbak-cli includes the --hex-blob option, and although this causes blob fields to take up more space since each byte is encoded by two bytes, it guarantees correct restoration of the dump in case there are invalid characters in the blob field.

If you know that your blob fields do not contain characters that could break the dump restore, then set this field to false.

use_single_transaction

For ensuring transactional consistency of the backup, MySQL wraps the backup in a transaction. And while this is an incredibly useful option, it has its limitations and requires additional privileges from the user who performs the backup. If backup consistency is not required, you can set this to false. By default, this option is true.

lock-tables

The --single-transaction option only provides a consistent backup for InnoDB engine tables. For other engines, such as MyISAM or MEMORY, the backup will not be consistent

mysql_bin_folder

Specifies the directory path where MySQL executable files, including mysqldump, are located.

This parameter is crucial for Windows environments to ensure that SqlBak-Cli can locate and use the mysqldump utility. For Linux systems, this parameter is not mandatory if mysql-client is installed, as the system typically can locate mysqldump automatically.