Do I need to make changes to MySQL database configuration?

In some scenarios, there are some changes required to MySQL database and server configuration:

Different user roles

Create a new MySQL backup user as well as restore user. This is required only if the user with the root privileges will not be doing backup and/or recovery of MySQL databases.

Incremental backups

Enable binary logs on the MySQL server if incremental backups of MySQL database have to be performed.

Backup using LVM2 snapshots

If this backup method is used, it is important to store the MySQL data is logical volumes.

Secure communication between MySQL server and MySQL ZRM

Enable SSL on the MySQL server.

MySQL backup user

MySQL users should be created and sufficient privileges to do backups and recovery. Minimal set of MySQL privileges for

backup user

LOCK TABLES, SELECT, FILE, RELOAD, SUPER

restore user

CREATE, DROP, INDEX, SHUTDOWN, INSERT, ALTER, SUPER, REPLICATION CLIENT

MySQL backup user requires SUPER privileges even when MySQL replication is not being used. MySQL ZRM requires binary logging to do incremental backups. Commands related to binary logging requires SUPER privileges. If the backup is being done from a MySQL replication slave, the backup user should have REPLICATION CLIENT privilege 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:

mysql> GRANT LOCK TABLES, SELECT, FILE, RELOAD, SUPER 
    ->     ON expenses.*
    ->     TO 'dba-backup'@'server.company.com'
    ->     IDENTIFIED BY 'obscure';

MySQL ZRM should be running on the machine - server.company.com.

It is recommended to create a backup user instead of using MySQL root user. If it is necessary to use different MySQL user for backup and restoration, backup user should be specified in mysql-zrm.conf for the backup set. The restore user can be specified in the mysql-zrm command line using --user and --password options.

Binary logs

To do MySQL incremental backups, it is necessary to enable binary logging on the MySQL server. MySQL server process should be started with --log-bin option

mysqld --log-bin=<binlogfilename>

Enabling binary logs on a MySQL server causes about 1% reduction in performance.

It is good idea to store binary logs in a filesystem (storage) from the database data directory location.

For more information on MySQL binary logs, see MySQL reference manual

Backups using LVM2 snapshots

MySQL ZRM can create temporary snapshots of the logical volumes and use the snapshot volume to do backups. The snapshots are removed when the backups are completed.

Snapshotting helps in creating a consistent copy of the MySQL database. If the MySQL database has tables that use non-transactional storage engines such as MyISAM, MySQL ZRM flushes the database pages to the disk and obtains a read lock on the database(s) or the table(s) while taking the snapshot. Usually the creation of snapshot is instantaneous as a result, the read lock on the database is held for short period of time. If the MySQL database only has tables that use transactional storage engines such as InnoDB, the pages are not flushed to disk as well as read lock are not required. This makes the backup window smaller.

If the database resides on the filesystems that support freeze/thaw operations such as XFS, VxFS (Veritas file systems), the filesystem I/O is stopped before taking a snapshot.

To do backups using LVM2 snapshots

• Additional disk space is required to create snapshots. The disk space required is specified in mysql-zrm.conf. See backup parameters in mysql-zrm.conf section.

• All MySQL database files (data, log, indexes) must be stored in LVM2 logical volumes.

SSL support on MySQL server

Configuring SSL between MySQL server and ZRM : This configuration is necessary only for logical backups of remote MySQL server. MySQL server has to be configured for SSL. To check if SSL support in MySQL server, you can do the following:

# mysqld --ssl --help
060828 15:25:08 [ERROR] mysqld: unknown option '--ssl'

To check whether a running mysqld server supports SSL, examine the value of the have_openssl system variable:

mysql> SHOW VARIABLES LIKE 'have_openssl';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_openssl  | YES   |
+---------------+-------+

For more information on configuring SSL on MySQL server, see MySQL reference manual

There are two options to configure SSL between MySQL server and ZRM. This configuration is relevant only for logical remote backups.

• Set up SSL parameters in my.cnf file on the machine running ZRM.

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

• You can configure ssl-ca, ssl-cert, ssl-key in mysql-zrm.conf for the backup set.

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"