Source:MySQL Backup

Copyright © SEP AG 1999-2024. All rights reserved.

Any form of reproduction of the contents or parts of this manual is allowed only with the express written permission from SEP AG. When compiling and designing user documentation SEP AG uses great diligence and attempts to deliver accurate and correct information. However, SEP AG cannot issue a guarantee for the contents of this manual.

'

Overview

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.

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 the list of supported MySQL/MariaDB versions.
• SEP sesam Server v. ≥ 4.4.3. Check Hardware requirements for SEP sesam Server.
• 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.

	Note
	Before proceeding, you should validate that you can backup 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.

1. From Main Selection -> Tasks -> By clients, select 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 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.
   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. Switch to the MySQL tab and enter the user name (account) and password to access the MySQL database.
4. Optionally, switch to the Options tab and enter additional parameters in the 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.
-a user=<DB_user>,password=<DB_user_password>
• You can also specify user privileges in the /etc/my.cnf 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 during the MySQL database backup of the ISAM database type; do not use this option for the InnoDB database type:
-a opt
• Specify this option for the consistent backup of the 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 into one statement:
-a user=<DB_user>,password=<DB_user_password>,opt,single-transaction

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




5. Click OK to create the task.
6. 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.

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

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.

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 Save options field in the backup task properties.

1. Double-click on the already configured MySQL backup task in the Main Selection -> Tasks by Clients.
2. Specify the relevant parameter for backing up stored routines or triggers: click the Options tab and enter the following in the Save options field:
• To back up the stored routines:
-a routines
• To include the triggers:
-a triggers
• To back up the stored routines and triggers at the same time:
-a routines -a triggers

Known issues

If you have problems with MySQL backup, check the Troubleshooting Guide.

See also

MySQL Restore