From The Open Source Backup Wiki (Amanda, MySQL Backup, BackupPC)
- Verify that the version(s) of MySQL server(s) that you deploy are version(s) supported by Zmanda Recovery Manager for MySQL.
- Verify mysql user and mysql group exists on the ZRM server and the mysql servers. The MySQL server process should also run as the same user and group. Thisis not required for logical backup method.
- The uid and gid of mysql user should be same on the MySQL servers and ZRM servers
- ZRM for MySQL requires 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.
- ZRM for MySQL client package must be installed on MySQL servers.
- The MySQL client commands installed on the local machine should be compatible with the MySQL servers being backed up.
- 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, accordingly configure the client command location and binary log location in mysql-zrm.conf. See MySQL server parameters section 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 mysql group.
- Three perl modules are necessary for MySQL ZRM. See Perl installation notes section of the manual.
- ZRM for MySQL packages require mailx mail package installed on the machine running ZRM for MySQL. mailx can be found as part of distribution. Mailx package must be configured to send mail from ZRM machine to the MySQL database backup administrator.
- Solaris packages require GNU utilities. These packages might be installed on the system or can be obtained from Sun Freeware web site. Choose the package version that installs the packages in '/usr/local/' directories. The required packages are:
SUNWcsu Core Solaris SUNWsshcu SSH Common SUNWgzip GNU Zip SMCtar GNU tar SMCsudo GNU sudo SMCgrep GNU grep SMCcoreu GNU coreutils
The SMC packages also have dependencies on the following libraries (also available from Sun Freeware):
SMCliconv SMClintl SMClgcc346
A few scenarios require changes to be made to MySQL database and server configuration:
- Root not doing backups
- When the user with the root privileges will not be doing backup and/or recovery of MySQL databases, create a new MySQL backup user as well as restore user.
- Incremental backups
- To do incremental backups of MySQL database enable binary logs on the MySQL server .
- Backup using snapshots
- Store MySQL data and logs in snapshot-able volumes when this backup method is required to be used.
- 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 being done on unsecured networks.
MySQL Backup User
We recommend creating a backup user instead of using MySQL root user. If MySQL backup user and restore user are different, set the privileges of backup user in mysql-zrm.conf for the backup set. Restore user privileges can be specified in the mysql-zrm command line using --user and --password options.
Minimal set of MySQL privileges that MySQL users should have to do backups and recovery are:
- backup user
- LOCK TABLES, SELECT, FILE, RELOAD, SUPER, SHOW VIEW*
- restore user
- CREATE, DROP, INDEX, SHUTDOWN, INSERT, ALTER, SUPER, REPLICATION CLIENT, CREATE VIEW*
SHOW VIEW and CREATE VIEW are required for logical backups/restores of MySQL 5.x servers.
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.
MySQL replication slave backup user should have REPLICATION CLIENT privileges in addition to above privileges.
Example: Command that grants minimal user privileges for backup user dba-backup to backup database expenses remotely from machine server.company.com (running MySQL 4.0.24):
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 the machine - 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.
Incremental backups of MySQL require binary logging enabled on the MySQL server. MySQL server process should be started with --log-bin option
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.
Backups Using Snapshots
ZRM for MySQL can create temporary snapshots of the LVM or ZFS volumes and use the snapshot volume to do backups. The snapshots can also be treated as backups. The advantage of using snapshots is that you need to lock the database tables only for the time taken to create a snapshot. The snapshots are removed when the 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 / tables use only transactional storage engines such as InnoDB, the time application is locked is less.
While taking the snapshots databases / 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) or Solaris ZFS.
All MySQL database files (data, log and indexes) that belong to the backup set must be stored in snapshot volumes. If they are not, raw backups will be performed even if the ZRM configured.
Pre-conditions for Using LVM Snapshots
- Additional free extents in the volume group are needed for creating snapshots. This can be checked with vgdisplay command. The free extents required are specified in mysql-zrm.conf. See backup parameters in the backup sets section.
Sudo(8) configuration file must be configured if snapshot is being used as MySQL backup method and ZRM for MySQL is running as default user 'mysql' or non-root user. The default user is 'mysql'. The sudo configuration must be changed on the MySQL server which is being backed up using snapshot method.
Some linux distrubution enforce sudo permissions differently. If there are errors like
"sudo: sorry, you must have a tty to run sudo,"
then do the following:
Run 'visudo' and comment the following line- #Default requiretty
- If user is not 'root' then following sudo permissions needs to be assigned in the sudo file for LVM snapshots
Example: If the ZRM user is 'mysql', the sudo file should look like this -
mysql ALL = NOPASSWD:/bin/mount, NOPASSWD:/bin/umount, NOPASSWD:/bin/df, NOPASSWD:/sbin/lvdisplay, NOPASSWD:/sbin/lvcreate, NOPASSWD:/sbin/lvremove
Example: For ZFS snapshots, edit /usr/local/etc/sudoers on the MySQL server:
SSL Between MySQL Servers and 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 server.
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 like:
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 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"