Table of contents
Specifying What to Back Up
The Backup What page lets you define what databases and tables from a particular MySQL server to back up in the current backup set. To define multiple MySQL servers requires multiple backup sets.
As with many of the Zmanda Console Management forms, the fields change depending on the context; selecting different values can change the options that are displayed.
Note also that backup sets inherit default values from the Set Site Defaults page, which in turn will default to factory settings unless you change them. Text fields show inherited defaults on a gray background (see Host parameter localhost in the example screen shown above).
Server Parameters
The first group of options (Server Parameters) let you specify the connection details for the MySQL Server to back up. MySQL Server is selected as the Server Type by default, and that is what is documented here. If you choose MySQL cluster, Backup What displays different options, which are described on the Backing Up a MySQL Cluster page.
- Connection Type
- Choosing Port lets you enter a port number for communication with the MySQL server; choosing Socket Filelets you enter a path to the socket file.
- Host
- Enter the host name or IP address of the MySQL server being backed up, or localhost if the MySQL server and the ZRM server are on the same machine. Binary logs must be enabled for the MySQL server.
- MySQL Client Utilities Path
- If you have installed the same versions of MySQL client commands on the ZRM server and the MySQL server (typically in /usr/bin), use the factory (Or Site Settings) defaults for all backup sets. If you have installed different versions of MySQL on the ZRM server and the MySQL server (i.e., the backup client), enter the full path to where MySQL binary commands are installed on the backup client. For example: /opt/lampp/bin or
-
C:\Program Files\MySQL\MySQL Server 5.0\bin\
MySQL User Parameters
User Name
Enter the username of the MySQL User on the MySQL server. If no user is specified here, ensure that you have the MySQL backup user (with appropriate privileges) specified explicitly as a Site Default, or that the MySQL user specified in the my.cnf or --options file has the necessary privileges described in System Requirements.
Password
Enter the password for the MySQL User. If you wish to prevent the password from being sent in plaintext over the network, you may wish to configure the MySQL username and password using the MySQL my.cnf or --options file. The following characters are allowed in passwords: a-z A-Z 0-9 _ - / . = " ' + * and space. For the remaining criteria please see this MySQL documentation.
SSL options
This field is valid only for logical full backups. If the connection from ZMC server to the remote server is via SSL, specify the MySQL ssl parameters here. If you are backing up databases on the localhost, leave this field empty. Please see configuring SSL connection between MySQL server and ZRM server.
What to Backup
Choose an option from the dropdown menu. Note that because the database(s) and table(s) shown on this page are updated dynamically based on information retrieved from the MySQL server, these may not match what has been set on the Site Settings page. The Go buttons allow you to refresh the connection to the MySQL server(s) to determine what databases and tables are currently available for backup. Available options are:
All Database(s)
Choosing this option includes all databases and all tables for backup.
- Specific Database(s)
- Choosing this option displays a list of databases available for backup on the MySQL server. Check all the databases that you want to add to this backup set. Size of the databases are also displayed if show size checkbox is selected.This will help determine the size needed for full uncompressed backup images. See Note below.
- Specific Table(s)
- Choosing this option displays a dropdown menu that lets you select a database from which to select tables to backup, followed by the list of tables in the selected database; check all the tables that you want to add to this backup set. Size of the tables are also displayed if show size checkbox is selected. This will help determine the size needed for full uncompressed backup images. See Note below.
- If you are planning to use parallel logical backup as full method and select specific tables for backup, stored procedures, views and triggers will not be backed up. Please use Specific Databases if you want to back up stored procedures, views and/or triggers.
- Parallel Logical backups are completed much faster if you select all tables in a database instead of selecting the database.
- Database names and table names cannot contain # character.
If you are planning to restore a table from logical backups (full backup method is logical), you must select specific table(s) in the backup set.
You must have innodb_file_per_table parameter enabled in the MySQL server to restore database(s) that use InnoDB storage engine.
Note: Database(s) or table(s) using innoDB storage engine can be restored to another MySQL server containing other InnoDB tables only if the restore target MySQL server is running Percona MySQL server and backup is done using Xtrabackup method.If you are planning to use snapshot backup as full backup method and are using InnoDB storage engine, you should select all databases in the MySQL server. You will be able to restore all databases to the orginal or alternate server. You will not be able to selectively restore the database.
What to Exclude
Exclusions can be used to backup all databases except few or all tables in a database except few that match the specified pattern. This is useful when a MySQL server has mixture of production and test databases and test databases need not be backed up. It can also be used to split large environment (lot of databases or lot of tables) into multiple backup sets.
The pattern applies to database names if specific databases are in the backup set. The pattern is matched with table names if a specific tables are in the backup set.
The exclude pattern does not work when all databases are selected. Users can be backup all databases and exclude databases from restoration.
The wildcards that are supported are:
- * matches one or more character. For example: zmanda*db pattern will exclude zmanda_bugs_db,zmanda_wiki_db and will not match zmanda_network names.
- ? match only one character. For example: zmanda_db? will match zmanda_test1 and they will be excluded from the backup set.
- [<char><char><char>...] match any character. For example: test[123] will match test1, test2 and test3.
- | match one of the patterns. For example: cat*|dog* will match all names that begin with cat or dog.
Backing up MySQL Application-specific files
MySQL applications such as Sugar and MediaWiki consist of MySQL databases and associated configuration files. Backing up the databases alone will not completely protect these application servers from mishaps. For this reason, Zmanda allows you to add configuration files to backup, however the ZMC GUI does not yet support this. To add such application-specific files to the backup, edit the MySQL configuration file, mysql-zrm.conf for the given backup set, and use the --config-file-list option to mysql-zrm-backup(1) command to specify the files you wish to backup.
All application files that are backed up are compressed or encrypted depending on the backup set configuration. Backup of configuration files specified by config-file-list in the mysql-zrm.conf are backed up only during full backups. Incremental backup of these files are not performed. The backup is performed as mysql user. So, mysql user should have permissions to read these files.
This functionality is available only on Linux and Solaris platforms.
To use this functionality:
-
Define config-file-list parameter in the mysql-zrm.conf for the backup set. Edit /etc/mysql-zrm/<backup set name>/mysql-zrm.conf. for example:
config-file-list="/etc/mysql-zrm/<backup set name>/application-configuration-files"
The configuration file can be in any directory in the ZRM server.
-
(optional) It might be useful to generate the application configuration file dynamically before the backup run. This will allow all application files to be part of the backup set. The generation of configuration file can be done using pre-backup-plugin. Pre-backup plugin can be configured in ZMC Backup|How page.
- The pre-backup plugin can be use find command to generate list of files in the application directory. You can specify list of directories or files to exclude to find command. Following script can be used to backup ZMC dependency configuration files. This script can be used as a pre-backup plugin.
#!/bin/sh
#
# List of directories to backup
#
dirs="/opt/zmanda/zrm /opt/zmanda/common";
#
# List of sub-directories to be excluded from the backup
#
exclude_dirs="/opt/zmanda/zrm/tmp
/opt/zmanda/zrm/php/tmp
/opt/zmanda/zrm/mysql/tmp";
# # List of files that should be excluded # exclude_filename_wildcards="*.tmp *svn*";
#
# configuration_file: List of files to be backed up. Do not change this value without
# fixing mysql-zrm.conf
#
configuration_file="/etc/mysql-zrm/zmc-backup/zmc-configuration-files";
excl_fname_pattern="";
for exclude in $exclude_filename_wildcards;
do
excl_fname_pattern="$excl_fname_pattern -o -name '$exclude'";
done
# strip the first -o excl_fname_pattern=`echo $excl_fname_pattern | sed -e 's/-o//1'`;
excl_dir_pattern="";
for exclude in $exclude_dirs;
do
excl_dir_pattern="$excl_dir_pattern -o -type d -path $exclude -prune";
done
rm -f $configuration_file
for dir in $dirs;
do
find $dir -type f -not '(' $excl_fname_pattern $excl_dir_pattern ')' -print >> $configuration_file;
done
exit 0;
MySQL cluster (NDB storage engine) backup/recovery
-
Please note that MySQL cluster is backed up using NDB management tools (ndb_mgm and ndb_restore) tools. NDB management tools are required on the ZRM server.
-
Full backup of each NDB data node is performed on each data node in the backup directory (configured inBackup|Where page). The backup directory must be present in each NDB data node and cannot be the same directory across nodes. The backup is then copied to ZRM server using copy plugin. The ssh or socket copy plugin must be configured in each NDB data node (See Backup|How page).
-
Incremental backups of MySQL cluster cannot be performed on NDB data nodes. Since there can be multiple SQL nodes in a NDB cluster, the incremental backups cannot be performed correctly.
Backup What page
By default, ZRM for MySQL & MariaDB shows the options for a MySQL server on the Backup What page. When you chooseCluster as the server type, options appropriate for MySQL Cluster are displayed.
- NDB Connect String
- This identifies the cluster to connect with for making the backup. To back up multiple clusters, multiple backup sets are required, each set with the appropriate NDB connect string. The connect string is not validated when you save the settings, so be careful to enter a valid connect string if you want to avoid subsequent backup failures. Consult the [MySQL Cluster documentation] for further details.
- MySQL Client Utilities Path
- The location of MySQL binary commands used to manage the cluster server.
- What to Backup
- When performing backups on clusters, all databases are backed up; the What to backup option is grayed out and disabled.