The MySQL Server refers the database server being backed up by the ZRM for MySQL Server, which also called the ZRM Server. The ZRM for MySQL components required on each are described in /div/p/span, line 1, column 23: ")" expected.
We recommend creating a MySQL database user user for ZRM backup and recovery instead of using the MySQL root user. If the MySQL database 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.
MySQL backup and restore users need the following MySQL privileges:
SHOW VIEW and CREATE VIEW privileges are required for MySQL server version 5.0 or greater.
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 database user dbabackup to backup and restore database expenses (which uses MySQL 4.x) remotely from machine server.company.com :
mysql> GRANT LOCK TABLES, SELECT, FILE, RELOAD, SUPER, CREATE, DROP, INDEX, SHUTDOWN, INSERT, ALTER, SUPER, REPLICATION CLIENT -> ON expenses.* -> TO 'dbabackup'@'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 (ZRM server).
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.
ZRM for MySQL can create temporary snapshots of the filesystems or storage volumes and use these snapshot volumes to do backups. If the database resides on a Veritas File System (VxFS), storage checkpoints can be leveraged.
Because snapshot and storage checkpoint mechanisms are faster than backups to other media, this reduces the time that database tables must be locked. These technologies create a consistent copy of the MySQL database with little impact on MySQL applications, and thus scale well as databases grow.
If the MySQL databases or tables use only transactional storage engines such as InnoDB, the time the application is locked is further 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.
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).
Snapshots of LVM (logical volume manager) in Linux platforms, VxFS (Veritas filesystems) Storage Checkpoints, VSS (Volume Shadow Services) on Windows, SnapManager (on Network Appliance filers), ZFS on Solaris platforms are supported.
All MySQL database files (data, log, indexes) must be stored on snapshot-capable storage volumes. If any of the files are not on snapshot cable storage volumes, snapshot backup method will not be used. Instead, raw backup via mysqlhotcopy is performed.
Snapshots and the pre-conditions for using them are discussed in more detail in Snapshot Plugins Overview.
SSL provides an additional layer of security while moving backups over a network. We recommended that you enable SSL on the MySQL server if the backups are performed on unsecured networks. 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:
# mysqld --ssl --help
060828 15:25:08 [ERROR] mysqld: unknown option '--ssl'
mysql> SHOW VARIABLES LIKE 'have_openssl';
+---------------+-------+ | Variable_name | Value | +---------------+-------+ | have_openssl | YES | +---------------+-------+
Consult the MySQL reference manual for configuring SSL on MySQL.
Zmanda recommends using either of the two options given below to configure SSL when remote backups of MySQL servers done using unsecured networks.
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
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"