4 4 3:MySQL Backup
The procedures and features explained herein provide only some of the recommendations and examples on how to back up the MySQL databases. Provided database backup options are not part of SEP sesam, they are merely some of the options that can be used for backup. For detailed information on MySQL backup policies and strategies, refer to MySQL Documentation.
SEP sesam extension for MySQL and MariaDB is a part of a client package and enables an online backup of one or more MySQL databases. SEP sesam uses mysqldump program for connecting to MySQL server and creating 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.
To ensure error-free operation of SEP sesam and improve performance, make sure that the following conditions are met:
- Check the support matrix for the list of supported MySQL/MariaDB versions.
- SEP sesam Server v. ≥ 4.4.3. Check Hardware requirements for SEP sesam Server, RDS and SEP_sesam_Client.
- Download the SEP sesam Client package on the MySQL database host (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 List of Licenses.
Adding the MySQL client to SEP sesam environment
Once you have downloaded the SEP sesam Client package, configure your MySQL client by adding it to the SEP sesam environment: Main selection -> Components -> Topology -> New Client -> add your MySQL client. For details, see Configuring Clients.
|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 single database you want to back up to be able to restore a single database.
- From Main Selection -> Tasks -> By Clients, select 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 single 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 example above, except in this case the CREATE DATABASE statement will be inserted; this is required for restoring a single database to the different location.
- all: Backup of all MySQL server databases.
- Switch to the MySQL tab and enter the user name (account) and password to access the MySQL database.
- Optionally, switch to the Options tab and enter additional parameters in the Backup options (previously Save options) field, if required.
- Enter the user name and password to access the database. It is recommended that you set the same privileges for the backup and the restore. For the restore you can also set them later under the Expert options in the restore wizard.
/etc/my.cnfon the client side. This is required for browsing for the database.
root@mysql:~# cat /etc/my.cnf [client] user=root password=secret root@mysql:~#
[client] user=root password=secret
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 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.|
You can view the status of your backup jobs by selecting Last Backup State in the Main Selection window. The backup status overview provides detailed information on the last run of backup jobs, including the task name, start and stop time of the last backup, backup level, data size, throughput, assigned media pool, etc.
|As of 4.4.3 Beefalo V2, you can check the details of your backups online by using new Web UI. For details, see SEP sesam Web UI.|
Configuring MySQL special features backup (stored routines and triggers)
By default, SEP sesam does not back up stored routines (procedures and functions) nor triggers from the dumped databases. However, it is possible to define that these are also backed up by using the Backup options (previously Save options) field in the backup task properties.
- Double-click on the already configured MySQL backup task in the Main Selection -> Tasks -> By Clients.
- Specify the relevant parameter for backing up stored routines or triggers: click the Options tab and enter the following in the Backup options (previously Save options) field:
- To back up the stored routines:
-a routines -a triggers