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.

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

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"