Targeted at DBAs, this article offers an overview of CLI utilities for MySQL backup. The commands described can be implemented in Linux Shell (.sh) scripts, as well as in Windows cmd (.bat) and PowerShell (.ps1) scripts. In addition to creating backups, dumps and exporting data, we’ll also delve into MySQL restore commands.
A MySQL dump is a file that contains SQL commands, which, when executed, would lead to the recreation of the MySQL database at the moment of the dump’s export. Essentially, a MySQL dump database is a logical MySQL backup database.
mysqldumpis a utility that creates a MySQL dump. It is part of MySQL and MariaDB and gets installed alongside the MySQL client.
本博客文章所述解决方案适用于任何 Linux 发行版：Ubuntu、Red Hat、Open SUSE、CentOS、Debian、Oracle Linux 等。然而，包安装命令可能会有所不同。
自动备份 MySQL 服务器数据库对于防止数据丢失至关重要。要正确自动化备份，您需要按照以下步骤执行：
- 使用 FTP、Dropbox、AWS、Google Drive 等将备份发送到云存储
In order to create database backups through SqlBak or SqlBackupAndFtp, the user specified when adding the connection must have sufficient privileges to perform the backup operation. Although you can use a superuser to create a backup without any problems, it is considered best practice to create a separate user specifically for this purpose.
Below are examples of SQL scripts for creating a backup user, as well as the necessary privileges for database recovery.
Please note that it is not necessary to grant recovery permissions immediately. This can be done when you need to restore the database.
Creating backups of a SQL Server database is one of the most critical aspects of maintaining a system. There are various tools available for creating backups, such as SQL Server Management Studio, SqlBak, and SQLBackupAndFTP. However, performing backups via the command line interface can provide even more flexibility and customization options.
PowerShell and batch scripts can be used to perform SQL Server backups via the command line interface. PowerShell scripts offer advanced functionality, while batch scripts are simpler and easier to use. Command line backups provide more flexibility and customization options than GUI-based tools.
This article provides an overview of the process involved in performing SQL Server backups via the command line interface and discusses the advantages of using PowerShell and batch scripts.
If a database that should be backed up via SqlBak is not particularly large, then there is no need to set compression options in a specific way. It is recommended to use the default values.
The features below are designed to fine-tune compression for highly loaded systems and large databases.
If you specify a password in the “Encrypt compressed files” section, then the backup files will be encrypted.
This guide contains recommendations for setting up a SqlBak backup job. This information has been developed and collected over years of interaction with SqlBak users. There will be no theory and formulas, only practical advice.
Note that these practices are not the only solutions. They are suitable in most cases, but can be fundamentally wrong under various circumstances.
Let’s consider the steps that are used to set up a backup job.
Differential and transaction log backups are available only for local SQL Server connections. If you backup SQL Server using SqlBak, then you can add differential or transaction log backups to your backup plan in the “Advanced backup schedule” settings.