5 1 0:MySQL Backup
Overview
The procedures and functions described here represent only some of the recommendations and examples for MySQL database backup. The provided database backup options are not part of SEP sesam, but only some of the options that can be used for backup. Detailed information about MySQL backup policies and strategies can be found in MySQL Documentation.
The SEP sesam extension for MySQL and MariaDB is part of a client package and enables online backup of one or more MySQL databases. SEP sesam uses the mysqldump program to connect to the MySQL server and create the SQL dump file. The dump file produces a set of SQL statements required to re-create the database. For details on mysqldump program, see MySQL documentation mysqldump — A Database Backup Program.
Requirements
To ensure error-free operation of SEP sesam and improve performance, make sure that the following conditions are met:
- Check the support matrix for a list of supported MySQL/MariaDB versions.
- SEP sesam Server v. ≥ 4.4.3. Check the hardware requirements for SEP sesam Server, RDS and SEP sesam Client.
- Download the SEP sesam Client package on the MySQL database host (the MySQL backup module is already included in the client package).
- A user account with sufficient privileges to perform backup and restore operations.
- SEP sesam MySQL backup module requires a license. For details, see Licensing.
Installing and adding MySQL client to SEP sesam environment
Once you have downloaded and installed the SEP sesam Client package, configure your MySQL client by adding it to the SEP sesam environment: Main selection -> Components -> Clients -> New Client -> add your MySQL client. For details, see Quick Install Guide and Configuring Clients.
Note | |
Before proceeding, you should validate that you can back up to SEP sesam. Run a test Path backup manually to ensure that the backups work. For details on typical backup configuration, see Standard Backup Procedure. |
Configuring MySQL database backup
Create a new backup task to back up a single database or the complete MySQL database. Note that you have to create a separate backup task for each database to be able to restore a single database.
- From Main Selection -> Tasks -> By Clients, select the MySQL client and click New Backup Task. The New Backup Task window opens.
- Specify the Source. Click the Browse button (big arrow) and select either the individual database or all MySQL databases you want to back up. You can also enter the source manually, as follows:
- <DB_name>: Backup of a single database (e.g., my_db).
- DB:<DB_name>:Backup of a single database (e.g., DB:my_db). Similar to the above example except that in this case the CREATE DATABASE statement is inserted; this is required for restoring a single database to a different location.
- all: Backup of all MySQL server databases.
- Enter the credentials to access the MySQL database. On SEP sesam version 5.1.0.14 Apollon V2 or higher, switch to the MySQL tab and enter the username (account) and password to access the MySQL database.
On SEP sesam version 5.1.0.7 Apollon or lower, switch to the Options tab and enter additional parameters in the Backup options field, if needed.
- Enter the username and password to access the database. Enter the username and password for accessing the database. It is recommended to set the same permissions for backup and restore. For restore, you can also set them later under the Expert options in the restore wizard.
-a user=<DB_user>,password=<DB_user_password>
/etc/my.cnf
file on the client side. This is required for browsing for the database.root@mysql:~# cat /etc/my.cnf [client] user=root password=secret root@mysql:~#
/etc/my.cnf
or %WINDIR%\my.cnf
:[client] user=root password=secret
-a defaults-file=/var/opt/sesam/var/ini/sm_mysql.cnf
-a opt
-a single-transaction
This option is useful only with transactional tables such as InnoDB; it dumps the consistent state of the database at the time when START TRANSACTION was issued without blocking any applications. Keep in mind that only InnoDB tables are dumped in a consistent state, while any MyISAM or MEMORY tables dumped while using this option may still change state.
-a user=<DB_user>,password=<DB_user_password>,opt,single-transaction
Note | |
|
Tip | |
You can also add your backup task to an existing schedule by double-clicking the backup task, selecting the tab Schedules and adding it to one or more schedules. Additionally, you can group your backup tasks to task groups. For details, see Adding a Task to the Task Group. |
Monitoring backup
You can view the status of your backup jobs in the GUI (Monitoring -> Last Backup State or Job State -> Backups) or SEP sesam Web UI. The backup status overview provides detailed information about the last run of backup jobs, including task name, start and stop time of the last backup, backup level, data size, throughput, assigned media pool, etc.
Configuring MySQL special features backup (stored procedures and triggers)
By default, SEP sesam does not back up stored procedures nor triggers from dumped databases. However, you can specify that these are also backed up by using the Backup options field in the backup task properties.
- Double-click the already configured MySQL backup task in the Main Selection -> Tasks -> By Clients.
- Specify the relevant parameters for backing up stored routines or triggers: click the Options tab and enter the following in the Backup optionsfield:
- To back up the stored procedures:
-a routines
-a triggers
-a routines -a triggers
See also
MySQL Restore - Standard Backup Procedure