Table of contents
- Verify that the version(s) of MySQL server(s) that you deploy are supported by Zmanda Recovery Manager for MySQL.
- Verify that a mysql user and a mysql group exist on the ZRM server.
- ZRM for MySQL requires the MySQL client commands listed below to be installed on the local server where ZRM for MySQL is running. These commands are required on the ZRM machine even if ZRM for MySQL is backing up remote MySQL servers:
- The ZRM for MySQL socket server package requires MySQL server and must be installed on MySQL servers.
Note: The ZRM for MySQL debian package explicitly checks for the presence of the mysql-client package on the ZRM machine before installation. The ZRM for MySQL socket server debian package explicitly checks for the presence of the mysql-server package on the MySQL server.
- The MySQL client commands installed on the local machine should be compatible with the local MySQL server.
- It is preferable to use the same version of MySQL on local and remote MySQL servers.
- Check that MySQL client commands are installed in /usr/bin/ . If they are not, you must change the configuration of the client command location and binary log location in {{web.html($Config_method)}}. See the MySQL subsection of the {{web.html($BS_Config_method)}} for details.
- Installation and configuration of Zmanda Recovery Manager for MySQL requires superuser access. All ZRM for MySQL commands can be run as any user belonging to the mysql group.
- Two perl modules are necessary for MySQL ZRM. See the Perl installation notes section of the manual.
- perl-DBI
- perl-XML-Parser
- These packages are available from CPAN repository or as part of the distribution. Following command can install perl DBI module:
# perl -MCPAN -e 'install DBI'
- ZRM for MySQL packages require the mailx mail package installed on the machine running ZRM for MySQL. mailx can be found as part of the distribution. The mailx package must be configured to send mail from the ZRM machine to the MySQL database backup administrator.
Pre-Installation Changes
A few scenarios require changes to be made to MySQL database and server configuration:
- Root not doing backups
- When a user with root privileges will not be doing backup and/or recovery of MySQL databases, create a new MySQL backup user as well as a restore user.
- Incremental backups
- To incrementally back up of MySQL database, enable binary logs on the MySQL server .
- Backup using LVM snapshots
- Store MySQL data in logical volumes when this backup method is required at your site.
- Secure communication between remote MySQL server and local ZRM for MySQL
- We recommended that you enable SSL on the MySQL server if the backups are performed on unsecured networks. Enabling SSL is described towards the bottom of this page.
MySQL Backup User
Recommendations
We recommend creating a backup user instead of using the MySQL root user. If the MySQL backup user and restore user are different, set the privileges of the backup user in {{web.html($BS_Config_method)}} for the backup set.
Restore user privileges can be specified on the mysql-zrm command line via the --user and --password options.
Minimal Set
MySQL backup and restore users need the following MySQL privileges:
- backup user
- LOCK TABLES, SELECT, FILE, RELOAD, SUPER, SHOW VIEW
- restore user
- CREATE, DROP, INDEX, SHUTDOWN, INSERT, ALTER, SUPER, REPLICATION CLIENT, CREATE VIEW
MySQL backup user requires SUPER privileges even when MySQL replication is not being used.
For incremental backups, ZRM for MySQL requires SUPER privileges to enable binary logging.
A MySQL replication slave backup user should have REPLICATION CLIENT privileges in addition to the above privileges.
Example: Command that grants minimal user privileges for backup user dba-backup to backup database expenses remotely from machine server.company.com :
mysql> GRANT LOCK TABLES, SELECT, FILE, RELOAD, SUPER
-> ON expenses.*
-> TO 'dba-backup'@'server.company.com'
-> IDENTIFIED BY 'obscure';
ZRM for MySQL should be running on server.company.com.
Note: If you are backing up remote MySQL server, backup user privileges should be granted for the backup user accessing from the MySQL server as well as the server running ZRM.
Binary Logs
Incremental backups of MySQL require binary logging enabled on the MySQL server. Start the MySQL server daemon (mysqld) with the --log-bin option:
mysqld --log-bin=BinLogFilename
Note: Enabling binary logs on a MySQL server reduces performance by about 1%.
It is good idea to store binary logs in a file system (storage) in a different file system than the file system containing the database directories.
Consult MySQL reference manual for more information on MySQL binary logs.
Backups Using LVM Snapshots
ZRM for MySQL can create temporary snapshots of the logical volumes and use these snapshot volumes to do backups. Because snapshots to disk are faster than backups to other media, this reduces the time that database tables must be locked. The snapshots are removed when backups are completed. Snapshots help to create a consistent copy of the MySQL database as the consistency is ensured before the snapshot is taken.
If the MySQL databases or tables use only transactional storage engines such as InnoDB, the time the application is locked reduced.
While taking snapshots of databases or tables that use non-transactional storage engines such as MyISAM, ZRM for MySQL flushes the database pages to the disk and obtains a read lock on the database(s) / table(s). The read lock is held only for a moment.
The file system I/O is stopped before taking a snapshot when the database resides on the file systems that support freeze/thaw operations such as XFS, VxFS (Veritas file systems).
Pre-conditions for Using LVM Snapshots
- Additional free extents in the volume group are needed for creating snapshots. This can be checked using the vgdisplay command. The free extents required are specified in {{web.html($Config_method)}}.
- All MySQL database files (data, log, indexes) must be stored in LVM logical volumes to ensure consistency.
- If any of the files are not on LVM, lvm-snapshot will not execute. Instead, raw backup via mysqlhotcopy is performed.
SSL Between MySQL Servers and the server running ZRM for MySQL
SSL provides an additional layer of security while moving backups over a network. Installing SSL between the local ZRM for MySQL server and remote MySQL server(s) is necessary only for logical backups of remote MySQL servers.
To verify the availability of SSL support in the MySQL server, you can either:
- Run the following command on the local server:
# mysqld --ssl --help
- When the SSL support does not exist, the system responds with a message like this:
060828 15:25:08 [ERROR] mysqld: unknown option '--ssl'
- Examine the value of the have_openssl system variable:
mysql> SHOW VARIABLES LIKE 'have_openssl';
+---------------+-------+ | Variable_name | Value | +---------------+-------+ | have_openssl | YES | +---------------+-------+
Consult MySQL reference manual for configuring SSL on MySQL.
We recommend using either of the two options given below to configure SSL when remote backups of MySQL server s done using unsecured networks.
- Set SSL parameters in the my.cnf file of MySQL on the local machine running ZRM for MySQL.
ssl-ca=mySQL_conf_dir/openssl/cacert.pem ssl-cert=mySQL_conf_dir/openssl/client-cert.pem ssl-key=mySQL_conf_dir/openssl/client-key.pem
- Set SSL parameters for all backup set in mysql-zrm.conf file of ZRM for MySQL.
ssl-options="--ssl --ssl-ca=mySQL_conf_dir/openssl/cacert.pem
--ssl-cert=mySQL_conf_dir/openssl/client-cert.pem
--ssl-key=mySQL_conf_dir/openssl/client-key.pem"