Archive:SEP sesam Extension for MySQL: Difference between revisions

From SEPsesam
m (→‎Troubleshooting: fixed link)
(43 intermediate revisions by 13 users not shown)
Line 1: Line 1:
[http://download.sep.de/extensions/databases/mysql/ SEP sesam Extension for MySQL]
{{Copyright SEP AG en}}
 
(C)SEP AG
 
Copyright 1999-2009 by SEP AG. 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.




=Introduction=  
=Introduction=  


The SEP sesam extension for MySQL allows an online backup of one or more MySQL databases at run time. This includes the backup and restore of one or all databases of a MySQL database server. For backup the MySQL backup program <tt>mysqldump</tt> and for restore the program <tt>mysql</tt> will be be used. Only the programs of the database manufacturer can guarantee that the data can backup in a consistent status.
The SEP sesam extension for MySQL and MariaDB allows an online backup of one or more MySQL databases at run time. This includes the backup and restore of one or all databases of a MySQL database server. For backup the MySQL backup program <tt>mysqldump</tt> and for restore the program <tt>mysql</tt> will be used. Only the programs of the database manufacturer can guarantee that the data can backup in a consistent status.




Line 18: Line 12:


=Prerequisites=
=Prerequisites=
* MySQL server version > 4.1 auf Linux (e.g. RHEL 4/5 und SLES 9/10)
* [[SEP_sesam_OS_and_database_support_matrix#MySQL|Supported Versions]]
* SEP Sesam server version >= 3.4.1.X
* SEP sesam Server version >= 3.4.1.X; check [[Special:MyLanguage/Hardware requirements|Hardware requirements]] for [[Special:MyLanguage/SEP_sesam_Glossary#SEP_sesam_Server|SEP sesam Server]] or [[Special:MyLanguage/SEP_sesam_Glossary#RDS|RDS]].
* Sesam client >= 3.4.1.51 on the MySQL database host
* SEP sesam Client >= 3.4.1.51 on the MySQL database host
* [http://download.sep.de/extensions/databases/mysql/ SEP sesam Extension for MySQL] only, if the version of the installed Sesam client <3.6. The Sesam client >= version 3.6 already includes the MySQL extension. In this case the installation steps can be skipped.
* [http://download.sep.de/extensions/databases/mysql/ SEP sesam Extension for MySQL] only, if the version of the installed SEP sesam Client < 3.6. The SEP sesam Client >= version 3.6 already includes the MySQL extension. In this case the installation steps can be skipped.
 
* '''Windows''': The path of the MySQL executables must be part of the Windows system PATH variable, e.g. 'PATH=%PATH%;C:\Program Files\MySQL\MySQL Server 5.6\bin'.
* Functioning SEP sesam filesystem backup
* Functioning SEP sesam file system backup


=Installation Sesam Client 3.4 =
=Installation SEP sesam Client 3.4 =


== RPM ==
== RPM ==
The RPM installation is the privileged installation method of the MySQL extension.  
The RPM installation is the privileged installation method of the MySQL extension.  


* Please make sure that the Sesam client RPM is already installed
* Please make sure that the SEP sesam Client RPM is already installed
* A path backup has to be possible with this client
* A path backup has to be possible with this client


Line 38: Line 32:


== Tarball ==
== Tarball ==
* Be sure that the Sesam client is already installed
* Be sure that the SEP sesam Client is already installed
* A path backup has to be possible with this client
* A path backup has to be possible with this client
* Log in as user ''root''
* Log in as user ''root''
* Unpack the Tarball to a temporary location (e.g. <tt>/tmp</tt>
* Unpack the Tarball to a temporary location (e.g. <tt>/tmp</tt>
* Copy the MySQL components to binary directory of the Sesam client
* Copy the MySQL components to binary directory of the SEP sesam Client


Example:
Example:
Line 50: Line 44:
  cp /tmp/sesam-mysql/* /opt/sesam/bin/sesam/
  cp /tmp/sesam-mysql/* /opt/sesam/bin/sesam/


=Installation Sesam Client 3.6=
=Installation since Sesam Client 3.6=


As with Sesam Client 3.6 the MySQL Extension is included in the Client
Since SEP sesam Client 3.6 the MySQL Extension is included in the Client package, no further extensions have to be installed!
package, no further Extensions have to be installed!


=Backup Configuration=
=Backup Configuration=
The configuration will be executed in the SEP sesam GUI.
The configuration will be executed in the SEP sesam GUI.


* Create a new backup task
* Create a new backup task.
* Select ''MySQL'' as Backup type
* Select ''MySQL'' as [[Special:MyLanguage/SEP_sesam_Glossary#task_type|Task type]].
* Enter the MySQL database name in the field ''Source''
* Enter the MySQL database name in the field ''Source''.
* On the page ''Options 1'' you can define additional parameters for backup and restore. Details described in the section "Backup & Restore".
* On the page ''Options 1'' you can define additional parameters for backup and restore. Details described in the section "Backup & Restore".


Line 68: Line 61:
|-
|-
|
|
It is recommend to set the user and password options of restore to the same values as the backup options. If necessary you can set the restore options in the Restore Wizard at the time of restore.
It is recommend setting the user and password options of restore to the same values as the backup options. If necessary you can set the restore options in the Restore Wizard at the time of restore.
|}
|}


* As Source you can enter the following values:  
* As Source you can enter the following values:  
** '''<DB_NAME>'''<br>Backup of single database (e.g. ''my_db'') <b>Watched:</b> the database name has to be entered case sensitive
** '''<DB_NAME>'''<br>Backup of single database (e.g. ''my_db'') <b>Watched:</b> the database name has to be entered case sensitive
** '''DB:<DB_NAME>'''<br>Backup of a single database (e.g. ''DB:my_db''). The difference to the statement above in the backuped dump is that a ''CREATE DATABASE'' statement will be inserted
** '''DB:<DB_NAME>'''<br>Backup of a single database (e.g. ''DB:my_db''). The difference to the statement above in the backed up dump is that a ''CREATE DATABASE'' statement will be inserted
** '''all'''<br>Backup of all databases of the MySQL server
** '''all'''<br>Backup of all databases of the MySQL server
* On the page ''Options 1'' you can define additional parameters for backup and restore<br>Here are some examples:
* On the page ''Options 1'' you can define additional parameters for backup and restore<br>Here are some examples:
a) To set user and password to log on at the database:
* To set user and password to log on at the database:
  -a user=<DBuser>,password=<Passwort_DBusers>
  -a user=<DBuser>,password=<password of DBuser>
 
* Login information can also be specified in ''/etc/my.cnf'' on client side (this is also necessary to browse the database):
 
  root@mysql:~# cat /etc/my.cnf
  [client]
  user=root
  password=secret
  root@mysql:~#
 
 
* It may also possible to use configuration files, e.g. {{Path|/etc/my.cnf}} or {{Path|%WINDIR%\my.cnf}} (see [[SEP sesam Extension for MySQL#Using a configuration file to store the password| Using a configuration file to store the password]]).
  [client]
  user=root
  password=secret
 
 
* Another config file can be specified by setting ''--defaults-file'' variable
  -a defaults-file=/var/opt/sesam/var/ini/sm_mysql.cnf
 




Line 84: Line 97:
|-
|-
|
|
The previous configuration of the username and password by entries in the <tt>sm.ini</tt>-file of the client is not necessary anymore. These settings will be executed in the Sesam GUI.
The previous configuration of the username and password by entries in the <tt>sm.ini</tt>-file of the client is not necessary anymore. These settings will be executed in the SEP sesam GUI.
|}
|}


Line 96: Line 109:


{{Box Attention|ATTENTION|
{{Box Attention|ATTENTION|
# Specify the <tt>mysqldump</tt> parameter without any double hyphen. Sesam translates the given parameter into the right format when calling the MySQL program.
# Specify the <tt>mysqldump</tt> parameter without any double hyphen. SEP sesam translates the given parameter into the right format when calling the MySQL program.
# The option line behind the '''-a''' may not contain any blanks.
# The option line behind the '''-a''' may not contain any blanks.
}}
}}
Line 102: Line 115:
= Restore Configuration =
= Restore Configuration =
A restore will be started by calling the restore wizard. First, select the backup job, then the period of time and then select the desired backup job for restore. There are 2 options:
A restore will be started by calling the restore wizard. First, select the backup job, then the period of time and then select the desired backup job for restore. There are 2 options:
* '''No recover after restore'''<br>The restore stores the data as a ''Dump'' file in the Sesam ''Work'' directory (usually <tt>/var/opt/sesam/var/work</tt> of the Sesam client or in older versions in <tt>/opt/sesam/bin/sesam</tt>). The name of the file comprise of the prefix <tt>mysql-</tt>, the Saveset-ID and the suffix <tt>.tmp</tt>. This file can import into the database by MySQL command <tt>mysql</tt>.
* '''No recover after restore'''<br>The restore stores the data as a ''Dump'' file in the SEP sesam ''Work'' directory (usually {{Path|/var/opt/sesam/var/work}} of the SEP sesam Client or in older versions in {{Path|/opt/sesam/bin/sesam}}). The name of the file comprise of the prefix <tt>mysql-</tt>, the Saveset-ID and the suffix <tt>.tmp</tt>. This file can import into the database by MySQL command <tt>mysql</tt>.


   Example: mysql < /var/opt/sesam/var/work/mysql-SF20081128224529.tmp
   Example: mysql < /var/opt/sesam/var/work/mysql-SF20081128224529.tmp


* '''Auto recover after restore'''<br>The data will be stored directly into the database without caching in the filesystem.
* '''Auto recover after restore'''<br>The data will be stored directly into the database without caching in the file system.




Line 112: Line 125:
If you use the option '''Auto recover after restore''' the database has to exist already, if not the restore will fail. Also, set the option '''overwrite existing files''' to overwrite an existing database.  
If you use the option '''Auto recover after restore''' the database has to exist already, if not the restore will fail. Also, set the option '''overwrite existing files''' to overwrite an existing database.  


If the CREATE DATABASE Statement is not in the backuped data, the data can only be restored into the same database. It's not possible to change the restore target.
If the CREATE DATABASE Statement is not in the backed up data, the data can only be restored into the same database. It's not possible to change the restore target.


In the case of a restore to a different database only the option '''no recover after restore''' is possible. Then you can edit the ''Dump'' file in the filesystem manually and import it as described above.
In the case of a restore to a different database only the option '''no recover after restore''' is possible. Then you can edit the ''Dump'' file in the file system manually and import it as described above.
}}
}}
===Single DB Restore===
To be able to restore single DBs from a mySQL Server you have to consider these preconditions:
* For each mySQL DB there must be configured a separate backup task (the DB tasks can be started as a group)
* With the mySQL "all" backup you can currently only restore all DBs.


= Backup configuration for special MySQL 5.0 features (Stored Procedures, Triggers) =
= Backup configuration for special MySQL 5.0 features (Stored Procedures, Triggers) =


By default the SEP Sesam software does not backup Stored Procedures or defined Triggers.
By default the SEP sesam software does not backup Stored Procedures or defined Triggers.
However, it is also possible to use the Sesam backup client for backing up the
However, it is also possible to use the SEP sesam backup client for backing up the
Stored Procedures and Triggers defined in a database.
Stored Procedures and Triggers defined in a database.


Line 127: Line 146:
=== Backup Stored Procedures ===
=== Backup Stored Procedures ===


The following example shows how to backup Stored Procedures with Sesam.
The following example shows how to backup Stored Procedures with SEP sesam.
Switch to <b>Options 1</b> from the task and add
Switch to <b>Options 1</b> from the task and add


Line 150: Line 169:


  -a routines -a triggers
  -a routines -a triggers
= Troubleshooting =
For troubleshooting MySQL issues, see [[Troubleshooting_Guide#MySQL|Troubleshooting MySQL Backup]].


= Further Links/Literature =
= Further Links/Literature =

Revision as of 18:40, 15 October 2020

Template:Copyright SEP AG en


Introduction

The SEP sesam extension for MySQL and MariaDB allows an online backup of one or more MySQL databases at run time. This includes the backup and restore of one or all databases of a MySQL database server. For backup the MySQL backup program mysqldump and for restore the program mysql will be used. Only the programs of the database manufacturer can guarantee that the data can backup in a consistent status.


ATTENTION

All database backup options in the documentation are neither parts nor components of SEP sesam. These are only recommendations and examples of how you can backup a MySQL database. There are many different fields of application for using a MySQL database. Therefore we can only show a few parameters to using during backup. Additional information of MySQL backup policies and strategies please see the MySQL documentation and the man pages of mysqldump und mysql.

Prerequisites

  • Supported Versions
  • SEP sesam Server version >= 3.4.1.X; check Hardware requirements for SEP sesam Server or RDS.
  • SEP sesam Client >= 3.4.1.51 on the MySQL database host
  • SEP sesam Extension for MySQL only, if the version of the installed SEP sesam Client < 3.6. The SEP sesam Client >= version 3.6 already includes the MySQL extension. In this case the installation steps can be skipped.
  • Windows: The path of the MySQL executables must be part of the Windows system PATH variable, e.g. 'PATH=%PATH%;C:\Program Files\MySQL\MySQL Server 5.6\bin'.
  • Functioning SEP sesam file system backup

Installation SEP sesam Client 3.4

RPM

The RPM installation is the privileged installation method of the MySQL extension.

  • Please make sure that the SEP sesam Client RPM is already installed
  • A path backup has to be possible with this client

Install or update the SEP sesam MySQL online extension by:

rpm -Uhv sesam_mysql-3.4.1-100.i386.rpm

Tarball

  • Be sure that the SEP sesam Client is already installed
  • A path backup has to be possible with this client
  • Log in as user root
  • Unpack the Tarball to a temporary location (e.g. /tmp
  • Copy the MySQL components to binary directory of the SEP sesam Client

Example:

cd /tmp
tar xvzf sesam-mysql.tgz
cp /tmp/sesam-mysql/* /opt/sesam/bin/sesam/

Installation since Sesam Client 3.6

Since SEP sesam Client 3.6 the MySQL Extension is included in the Client package, no further extensions have to be installed!

Backup Configuration

The configuration will be executed in the SEP sesam GUI.

  • Create a new backup task.
  • Select MySQL as Task type.
  • Enter the MySQL database name in the field Source.
  • On the page Options 1 you can define additional parameters for backup and restore. Details described in the section "Backup & Restore".


Info

It is recommend setting the user and password options of restore to the same values as the backup options. If necessary you can set the restore options in the Restore Wizard at the time of restore.

  • As Source you can enter the following values:
    • <DB_NAME>
      Backup of single database (e.g. my_db) Watched: the database name has to be entered case sensitive
    • DB:<DB_NAME>
      Backup of a single database (e.g. DB:my_db). The difference to the statement above in the backed up dump is that a CREATE DATABASE statement will be inserted
    • all
      Backup of all databases of the MySQL server
  • On the page Options 1 you can define additional parameters for backup and restore
    Here are some examples:
  • To set user and password to log on at the database:
-a user=<DBuser>,password=<password of DBuser>
  • Login information can also be specified in /etc/my.cnf on client side (this is also necessary to browse the database):
 root@mysql:~# cat /etc/my.cnf
 [client]
 user=root
 password=secret
 root@mysql:~#


 [client]
 user=root
 password=secret


  • Another config file can be specified by setting --defaults-file variable
  -a defaults-file=/var/opt/sesam/var/ini/sm_mysql.cnf


Info

The previous configuration of the username and password by entries in the sm.ini-file of the client is not necessary anymore. These settings will be executed in the SEP sesam GUI.

b) For a better throughput during backup of MySQL database type ISAM. Do not use this option for InnoDB:

-a opt

c) For a consistent backup of a MySQL database of type InnoDB:

-a single-transaction

d) You can combine different parameters into one statement:

-a user=<DBuser>,password=<Passwort_DBusers>,opt,single-transaction


ATTENTION
  1. Specify the mysqldump parameter without any double hyphen. SEP sesam translates the given parameter into the right format when calling the MySQL program.
  2. The option line behind the -a may not contain any blanks.

Restore Configuration

A restore will be started by calling the restore wizard. First, select the backup job, then the period of time and then select the desired backup job for restore. There are 2 options:

  • No recover after restore
    The restore stores the data as a Dump file in the SEP sesam Work directory (usually /var/opt/sesam/var/work of the SEP sesam Client or in older versions in /opt/sesam/bin/sesam). The name of the file comprise of the prefix mysql-, the Saveset-ID and the suffix .tmp. This file can import into the database by MySQL command mysql.
 Example: mysql < /var/opt/sesam/var/work/mysql-SF20081128224529.tmp
  • Auto recover after restore
    The data will be stored directly into the database without caching in the file system.


ATTENTION

If you use the option Auto recover after restore the database has to exist already, if not the restore will fail. Also, set the option overwrite existing files to overwrite an existing database.

If the CREATE DATABASE Statement is not in the backed up data, the data can only be restored into the same database. It's not possible to change the restore target.

In the case of a restore to a different database only the option no recover after restore is possible. Then you can edit the Dump file in the file system manually and import it as described above.

Single DB Restore

To be able to restore single DBs from a mySQL Server you have to consider these preconditions:

  • For each mySQL DB there must be configured a separate backup task (the DB tasks can be started as a group)
  • With the mySQL "all" backup you can currently only restore all DBs.

Backup configuration for special MySQL 5.0 features (Stored Procedures, Triggers)

By default the SEP sesam software does not backup Stored Procedures or defined Triggers. However, it is also possible to use the SEP sesam backup client for backing up the Stored Procedures and Triggers defined in a database.

The can be enabled in the task properties from a backup job.

Backup Stored Procedures

The following example shows how to backup Stored Procedures with SEP sesam. Switch to Options 1 from the task and add

-a routines

in the additional save options field, like shown in this screenshot:

Mysql stored procedures.jpg

Backup defined Triggers

Just like Stored Procedures, it is also possible to add additional options to enable Triggers. Add the parameter

-a triggers

like shown in this screenshot:

Mysql triggers.jpg

Of course it is also possible to define multiple statements, backing up both Stored Procedures and Triggers with:

-a routines -a triggers

Troubleshooting

For troubleshooting MySQL issues, see Troubleshooting MySQL Backup.

Further Links/Literature