Do I need to make changes to MySQL database configuration?

There are some changes required to MySQL database and server configuration for the following configurations:

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 backup user should be created and sufficient privileges to do backups and recovery. Minimal set of MySQL privileges for

backup user

LOCK TABLES, SELECT, FILE, RELOAD

restore user

CREATE, DROP, INDEX, SHUTDOWN, INSERT, ALTER

If backups are being done from MySQL replication slave, backup user should have REPLICATION CLIENT, SUPER privileges in addition to the above mentioned ones.

MySQL command to grant privileges for a user:

mysql> GRANT <privileges> ON

{tbl_name | * | *.* | db_name.*} TO <user name> identified by PASSWORD <user password>; Example: Command that grants minimal user privileges for backup user dba-backup to backup database expenses remotely from domain company.com: mysql> GRANT LOCK TABLES, SELECT, FILE, RELOAD -> ON expenses.* -> TO 'dba-backup'@'company.com' -> IDENTIFIED BY 'obscure'; MySQL ZRM should be running on a machine with domain name 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"