Do I need to make changes to MySQL database configuration?: Difference between revisions
No edit summary |
|||
(3 intermediate revisions by 2 users not shown) | |||
Line 1: | Line 1: | ||
In some scenarios, there are some changes required to the 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. | ||
; Incremental backups : Enable [[#Binary logs|binary logs]] on the MySQL server if incremental backups of MySQL database have to be performed. | ; Incremental backups : Enable [[#Binary logs|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 in logical volumes. | |||
; Secure communication between MySQL server and MySQL ZRM : Enable [[#SSL support on MySQL server|SSL on the MySQL server]]. | ; Secure communication between MySQL server and MySQL ZRM : Enable [[#SSL support on MySQL server|SSL on the MySQL server]]. | ||
Line 9: | Line 11: | ||
=== MySQL backup user === | === MySQL backup user === | ||
MySQL | MySQL users should be created and given sufficient privileges to do backups and recovery. The 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 require ''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, RELOAD, SUPER | |||
mysql> GRANT LOCK TABLES, SELECT, | |||
-> 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 the MySQL ''root'' user. If it is necessary to use a different MySQL user for backup and restoration, the 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=== | ===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 | To do MySQL incremental backups, it is necessary to enable binary logging on the MySQL server. The MySQL server process should be started with ''--log-bin'' option | ||
mysqld --log-bin=<binlogfilename> | mysqld --log-bin=<binlogfilename> | ||
Line 35: | Line 36: | ||
Enabling binary logs on a MySQL server causes about 1% reduction in performance. | 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. | It is a good idea to store binary logs in a filesystem (storage) different from the database data directory location. | ||
For more information on MySQL binary logs, see [http://dev.mysql.com/doc/refman/5.0/en/binary-log.html MySQL reference manual] | For more information on MySQL binary logs, see [http://dev.mysql.com/doc/refman/5.0/en/binary-log.html MySQL reference manual] | ||
=== Backups using LVM2 snapshots === | |||
MySQL ZRM can create temporary snapshots of 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 disk and obtains a read lock on the database(s) or the table(s) while taking the snapshot. Usually the creation of snapshot is essentially instantaneous; and, as a result, the read lock on the database is held for a 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 and read locks are not required. This makes the backup window smaller. | |||
If the database resides on a filesystem that supports 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 [[How_do_I_configure_MySQL_ZRM%3F#Backup_parameters|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 === | === 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. | Configuring SSL between MySQL server and ZRM : This configuration is necessary only for logical backups of a remote MySQL server. The MySQL server has to be configured for SSL. | ||
To check if SSL support in MySQL server, you can do the following: | To check if SSL support is in the MySQL server, you can do the following: | ||
# mysqld --ssl --help | # mysqld --ssl --help | ||
060828 15:25:08 [ERROR] mysqld: unknown option '--ssl' | 060828 15:25:08 [ERROR] mysqld: unknown option '--ssl' | ||
Line 55: | Line 70: | ||
+---------------+-------+ | +---------------+-------+ | ||
For more information on configuring SSL on MySQL server, see [http://dev.mysql.com/doc/refman/5.0/en/secure-using-ssl.html MySQL reference manual] | For more information on configuring SSL on a MySQL server, see [http://dev.mysql.com/doc/refman/5.0/en/secure-using-ssl.html MySQL reference manual] | ||
There are two options to configure SSL between MySQL server and ZRM. This configuration is relevant only for | There are two options to configure SSL between a 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. | * Set up SSL parameters in my.cnf file on the machine running ZRM. | ||
ssl-ca=<mysql_conf_dir>/openssl/cacert.pem | ssl-ca=<mysql_conf_dir>/openssl/cacert.pem |
Latest revision as of 22:30, 30 August 2010
In some scenarios, there are some changes required to the 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 in 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 given sufficient privileges to do backups and recovery. The 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 require 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, 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 the MySQL root user. If it is necessary to use a different MySQL user for backup and restoration, the 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. The 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 a good idea to store binary logs in a filesystem (storage) different 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 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 disk and obtains a read lock on the database(s) or the table(s) while taking the snapshot. Usually the creation of snapshot is essentially instantaneous; and, as a result, the read lock on the database is held for a 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 and read locks are not required. This makes the backup window smaller.
If the database resides on a filesystem that supports 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 a remote MySQL server. The MySQL server has to be configured for SSL. To check if SSL support is in the 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 a MySQL server, see MySQL reference manual
There are two options to configure SSL between a 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"