Source:MySQL Backup

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.

1. From Main Selection -> Tasks -> By Clients, select the MySQL client and click New Backup Task. The New Backup Task window opens.
2. 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.
   Note that if you select the source by browsing, the task type and task name are set automatically. If you enter the source manually, you have to enter the task name and select MySQL as the task type.



3. 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>

• You can also specify user privileges in the /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:~#

• Alternatively, you can use the configuration files to store the password, e.g., /etc/my.cnf or %WINDIR%\my.cnf:

 [client]
 user=root
 password=secret

• You can also specify another configuration file to store the password by using the defaults-file variable:

 -a defaults-file=/var/opt/sesam/var/ini/sm_mysql.cnf

• Use this option for better throughput when backing up the MySQL database of the ISAM database type; do not use this option for the InnoDB database type:

 -a opt

• Specify this option for consistent backups of InnoDB tables:

 -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.

• You can combine different parameters in one statement:

 -a user=<DB_user>,password=<DB_user_password>,opt,single-transaction

	Note
	Specify the mysqldump parameter without a double hyphen. SEP sesam translates the given parameter into the correct format when calling the MySQL program. The line after the -a specification must not contain spaces.





4. Click OK to create the task.
5. If you want to start the newly created task immediately, right-click the name of the task and click Immediate Start. If you want to create a periodic backup, you have to create a schedule for your backup task: Click New Schedule under Main Selection -> Scheduling -> Schedules and set up a schedule. For more details, see Creating a Schedule.

	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.

6. Once you have configured a schedule, you must create a new backup event for it. For general information on creating a backup event, see Creating a Backup Event.

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.

1. Double-click the already configured MySQL backup task in the Main Selection -> Tasks -> By Clients.
2. 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

• To enable triggers:

 -a triggers

• To back up the stored procedures and triggers at the same time:

 -a routines -a triggers