4 4 3:MySQL Backup

From SEPsesam
Jump to: navigation, search
Draft.png WORK IN PROGRESS
This page is a draft. Treat the information on this page with caution as it may be incomplete.

Copyright © SEP AG 1999-2020. 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.

Docs latest icon.png Welcome to the latest SEP sesam documentation version 4.4.3/4.4.3 Beefalo V2. For previous documentation version(s), check MySQL.


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:

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.

Information sign.png 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 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. MySQL bck task Beefalo V2.jpg

  4. Switch to the MySQL tab and enter the user name (account) and password to access the MySQL database.
  5. 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.
    • -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
      
      Information sign.png 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.

    MySQL bck task options Beefalo V2.jpg

  6. Click OK to create the task.
  7. 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.
  8. SEP Tip.png 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.
  9. 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.

Information sign.png Note
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.

  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 Backup options (previously 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
      

      MySQL bck task special Beefalo V2.jpg

Known issues

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

See also

Standard Backup ProcedureMySQL Restore