Do I need to make changes to MySQL database configuration?: Difference between revisions
No edit summary |
No edit summary |
||
Line 1: | Line 1: | ||
In some scenarios, there are some changes required to MySQL database and server configuration: | |||
; Different user roles : Create a new [[#MySQL backup user|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. | ; Different user roles : Create a new [[#MySQL backup user|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. | ||
Line 9: | Line 9: | ||
=== MySQL backup user === | === MySQL backup user === | ||
MySQL | 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 | ; backup user : LOCK TABLES, SELECT, FILE, RELOAD, SUPER | ||
; restore user : CREATE, DROP, INDEX, SHUTDOWN, INSERT, ALTER | ; restore user : CREATE, DROP, INDEX, SHUTDOWN, INSERT, ALTER, SUPER, REPLICATION CLIENT | ||
If | 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 | |||
mysql> GRANT LOCK TABLES, SELECT, FILE, RELOAD | |||
-> ON expenses.* | -> ON expenses.* | ||
-> TO 'dba-backup'@'company.com' | -> TO 'dba-backup'@'server.company.com' | ||
-> IDENTIFIED BY 'obscure'; | -> IDENTIFIED BY 'obscure'; | ||
MySQL ZRM should be running on | 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. | 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. |
Revision as of 18:16, 22 September 2006
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"