MySQL Enterprise Backup 8.0.16 has been released
MySQL Enterprise Backup 8.0.16, a new version of the online MySQL backup tool, is now available for download from the My Oracle Support (MOS) website as our latest GA release. This release will be available on eDelivery (OSDC) after the next upload cycle. MySQL Enterprise Backup is a commercial extension to the MySQL family of products. MySQL Enterprise Backup 8.0.16 supports only the MySQL Server 8.0.16. For earlier versions of MySQL 8.0, use the MySQL Enterprise Backup version with the same version number as the server. For MySQL server 5.7, please use MySQL Enterprise Backup 4.1, and for MySQL Server 5.6 and 5.5, please use MySQL Enterprise Backup 3.12. A brief summary of the changes in MySQL Enterprise Backup (MEB) version 8.0.16 is given below. -- Changes in MySQL Enterprise Backup 8.0.16 (2019-04-25, GA) * Functionality Added or Changed * Bugs Fixed Functionality Added or Changed * mysqlbackup now supports encrypted InnoDB undo logs (http://dev.mysql.com/doc/refman/8.0/en/innodb-tablespace-encryption.html#innodb-tablespace-encryption-undo-log). The encrypted undo tablespaces are handled the same way as the encrypted tablespaces for InnoDB tables. See Working with Encrypted InnoDB Tablespaces (http://dev.mysql.com/doc/mysql-enterprise-backup/8.0/en/meb-encrypted-innodb.html) for details. * Near the end of the backup process, instead of locking the whole server instance for a brief period of time, mysqlbackup now applies these locks consecutively: 1. A backup lock (http://dev.mysql.com/doc/refman/8.0/en/lock-instance-for-backup.html) on the server instance, which blocks DDLs (except those on user-created temporary tables), but not DMLs on InnoDB tables. 2. A FLUSH TABLES tbl_name [, tbl_name] ... WITH READ LOCK (http://dev.mysql.com/doc/refman/8.0/en/flush.html#flush-tables-with-read-lock-with-list) operation on all non-InnoDB tables, for copying the relevant ones among them into the backup. This step is skipped if no user-created non-InnoDB tables exist. 3. A brief blocking of logging activities on the server, for collecting logging-related information. See The Backup Process (https://dev.mysql.com/doc/mysql-enterprise-backup/8.0/en/meb-backup-process) for details. The removal of the lock on the whole server instance reduces disruption to the database service by the backup operation. Important The change requires that the BACKUP_ADMIN (http://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_backup-admin) and SELECT (http://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_select) privileges on all tables be granted to the user by which mysqlbackup connects to the server (the BACKUP_ADMIN (http://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_backup-admin) privilege is automatically granted to users with the RELOAD (http://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_reload) privilege when an in-place upgrade to MySQL Server 8.0 from an earlier version is performed). * mysqlbackup now supports dynamic changes to undo tablespaces (http://dev.mysql.com/doc/refman/8.0/en/innodb-undo-tablespaces.html) on the server being backed up. During a restore, the default undo tablespaces, as well as any non-default undo tablespaces resided in the backed-up server's data directory, are restored to the location pointed to by the mysqlbackup option --innodb_undo_directory. Non-default, external undo tablespaces are restored to the locations they were found on the backed-up server. See undo log files (http://dev.mysql.com/doc/mysql-enterprise-backup/8.0/en/meb-files-backed-up-summary.html#meb_file_undo-log-files) for details. * In addition to the requirement that the target data directory for a restore specified by the --datadir option must be non-existent or empty, mysqlbackup now enforces the same rule for the --innodb_data_home_dir, --innodb_log_group_home_dir, and --innodb_undo_directory options (the --force option cannot be used to override the requirement on the three options). Bugs Fixed * Zip packages of mysqlbackup contained duplicate files, which have now been removed. (Bug #29497272, Bug #94683) * mysqlbackup might quit unexpectedly if it lost its connection to the server at the middle of a backup operation. With this fix, mysqlbackup exits gracefully in the situation after throwing the appropriate errors. (Bug #29376006) * Restore of an incremental backup failed if, on the server, some binary log files had been purged in between the times the incremental backup
MySQL Enterprise Backup 3.12.4 has been released
Dear MySQL users, MySQL Enterprise Backup v3.12.4, a new version of the online MySQL backup tool, is now available for download from the My Oracle Support (MOS) website as our latest GA release. This release will be available on eDelivery (OSDC) after the next upload cycle. MySQL Enterprise Backup is a commercial extension to the MySQL family of products. A brief summary of the changes in MySQL Enterprise Backup (MEB) version 3.12.4 is given below. Changes in MySQL Enterprise Backup 3.12.4 (2019-02-15) * Functionality Added or Changed * Bugs Fixed Functionality Added or Changed * Information on the executed GTIDs is now includfed in the mysqlbackup output and the backup log when the backed-up server has GTIDs enabled. (Bug #25978803) * A backup became corrupted if, during the backup process, a DDL operation that took advantage of MySQL server's online DDL feature (http://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl.html) occurred. This was because mysqlbackup did not support the server feature---and it still does not. This fix avoids the error by having mysqlbackup turn the server's system variable old_alter_table to "1" at the beginning of a backup if it is "0," so that any DDL operations that take place during the backup are handled with the old table copy method. mysqlbackup then turns the variable back to "0" near the end of the backup operation. Important Notice that in cases where mysqlbackup quits unexpectedly and does not turn old_alter_table back to its original value, the user will have to turn the value back to "0" manually on the server, in order to return the server to its original configuration. This should be performed if the statement "Server system variable 'old_alter_table' was set to '0'. Setting it to '1'" appears in the early output of mysqlbackup, but the statement "Setting server system variable 'old_alter_table' back to '0'" does not appear before mysqlbackup quits. (Bug #25217215) * A new option, --skip-final-rescan, makes mysqlbackup skip the final rescan for InnoDB tables that are modified by DDL operations after the database has been locked near the end of a backup operation. This potentially shortens the duration for the lock and reduces the backup's impact on the server's normal operation. See the description for --skip-final-rescan for details. (Bug #21094221) * The output by mysqlbackup, which goes to the stderr stream and the message log, has now been improved to include the timestamps and thread IDs for all steps taken by mysqlbackup, in order to provide more information for debugging purposes. (Bug #20142619) * When there were no tables matching the regular expression specified with the --include-tables option during a backup operation, mysqlbackup still created a backup, which contained an empty folder for each database on the server. mysqlbackup now throws an error when --include-tables selects no tables to be backed up. (Bug #18114353) * During the final stage of a backup when MySQL Enterprise Backup tried to temporarily put the database into a read-only state using the FLUSH TABLES WITH READ LOCK statement in order to copy non-InnoDB files, if a long query was running on the server at the same time, the FLUSH TABLES WITH READ LOCK statement could be taking too long to finish, holding up further queries and eventually bringing down the server. A new mysqlbackup option --lock-wait-timeout can now be used to specify the timeout in seconds for the FLUSH TABLES WITH READ LOCK statement. If the timeout is exceeded, the statement is failed and the lock on the tables is released, so that queries held up by the lock can then be executed. mysqlbackup then retries the statement and continues with the backup. Default value for --lock-wait-timeout is 60 [seconds]. (Bug #14339483) * In order to minimize the impact of a hot backup on the MySQL server, the copying of the buffer pool dump files and some of the metadata files is now performed before the final phase of the backup in which the server instance is locked. This shortens the duration for the lock and reduces the backup's impact on the server's normal operation. Also, to minimize the resource used on a backup, the copying of the buffer pool dump files is no longer performed for partial and offline backups, for which the buffer pool dump is usually not very useful. Bugs Fixed * While MySQL Server interprets the system v
MySQL Enterprise Backup 8.0.15 has been released
Dear MySQL users, MySQL Enterprise Backup 8.0.15, a new version of the online MySQL backup tool, is now available for download from the My Oracle Support (MOS) website as our latest GA release. This release will be available on eDelivery (OSDC) after the next upload cycle. MySQL Enterprise Backup is a commercial extension to the MySQL family of products. MySQL Enterprise Backup 8.0.15 supports only the MySQL Server 8.0.15. For earlier versions of MySQL 8.0, use the MySQL Enterprise Backup version with the same version number as the server. For MySQL server 5.7, please use MySQL Enterprise Backup 4.1 and for MySQL Server 5.6 and 5.5, please use MySQL Enterprise Backup 3.12. A brief summary of the changes in MySQL Enterprise Backup (MEB) version 8.0.15 is given below. Enjoy! -- Changes in MySQL Enterprise Backup 8.0.15 (2019-02-01) This release contains no functional changes and is published to align version number with the MySQL Server 8.0.15 release. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
MySQL Enterprise Backup 8.0.14 has been released
MySQL Enterprise Backup 8.0.14, a new version of the online MySQL backup tool, is now available for download from the My Oracle Support (MOS) website as our latest GA release. This release will be available on eDelivery (OSDC) after the next upload cycle. MySQL Enterprise Backup is a commercial extension to the MySQL family of products. MySQL Enterprise Backup 8.0.14 supports only the MySQL Server 8.0.14. For earlier versions of MySQL 8.0, use the MySQL Enterprise Backup version with the same version number as the server. For MySQL server 5.7, please use MySQL Enterprise Backup 4.1 and for MySQL Server 5.6 and 5.5, please use MySQL Enterprise Backup 3.12. A brief summary of the changes in MySQL Enterprise Backup (MEB) version 8.0.14 is given below. -- Changes in MySQL Enterprise Backup 8.0.14 (2019-01-21, General Availability) * Functionality Added or Changed * Bugs Fixed Functionality Added or Changed * mysqlbackup now supports encrypted binary and relay log. See Working with Encrypted Binary and Relay Logs (http://dev.mysql.com/doc/mysql-enterprise-backup/8.0/en/advanced.encrypted-binlog-relaylog.html) for details. * mysqlbackup now supports the --ssl-fips-mode option, which controls whether mysqlbackup operates in FIPS mode. See FIPS Support (http://dev.mysql.com/doc/refman/8.0/en/fips-mode.html) for details. Bugs Fixed * An apply-incremental-backup operation failed with an error (RDR1 ERROR: Unable to remove relaylog files from full backup) when the incremental backup was created with the --compress option. (Bug #28366241) * mysqlbackup quit unexpectedly during an apply-incremental-backup operation if the backed up server had been started using relative paths for --datadir and --log-bin. (Bug #28334521) * Attempts to restore a backup of a MySQL 5.7 Server to a MySQL 8.0 Server resulted in a strange error message (Server_version is not obtained). With this fix, mysqlbackup now indicates that the operation is not supported. For related information, see Restoring a Backup with a Database Upgrade or Downgrade (http://dev.mysql.com/doc/mysql-enterprise-backup/8.0/en/restore-upgrade.html). (Bug #27952379) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
MySQL Enterprise Backup 8.0.13 has been released
Dear MySQL users, MySQL Enterprise Backup 8.0.13, a new version of the online MySQL backup tool, is now available for download from the My Oracle Support (MOS) website as our latest GA release. This release will be available on eDelivery (OSDC) after the next upload cycle. MySQL Enterprise Backup is a commercial extension to the MySQL family of products. MySQL Enterprise Backup 8.0.13 only supports MySQL Server 8.0.13. For earlier versions of MySQL 8.0, use the MySQL Enterprise Backup version with the same version number as the server. For MySQL server 5.7, please use MySQL Enterprise Backup 4.1 and for MySQL Server 5.6 and 5.5, please use MySQL Enterprise Backup 3.12. A brief summary of the changes in MySQL Enterprise Backup (MEB) version 8.0.13 is given below. Changes in MySQL Enterprise Backup 8.0.13 (2018-10-22) * Functionality Added or Changed * Bugs Fixed Functionality Added or Changed * mysqlbackup now supports backup compression (the use of the --compress and --uncompress options) for incremental backups (except for incremental backups created with the --incremental-with-redo-log-only option). * mysqlbackup now supports transparent page compression for InnoDB tables. The support is enabled by setting the mysqlbackup option --compress-method=punch-hole; see description for the option for details. Bugs Fixed * mysqlbackup hung when a backup operation failed due to a full disk. With this fix, mysqlbackup quits gracefully in the situation by throwing an error. (Bug #28399821) * A mysqlbackup operation on an image stored on an OpenStack cloud storage service sometimes failed with a segmentation fault or a bad URL error. It was because of a race condition caused by an uninitiated variable, which has been eliminated by this fix. (Bug #28189239, Bug #28183729) * Backups for databases with encrypted InnoDB tables failed when the --compress option was used. (Bug #28177466) * A mysqlbackup operation on an image stored on an OpenStack cloud storage service failed with a 401 Unauthorized error when the operation took a long time and the authentication token for the cloud access expired. With this fix, a separate thread in mysqlbackup requests a new token from the OpenStack cloud service in that situation, so that the operation can continue. (Bug #27893174) * When an incremental backup was restored without using the --log-bin option, the binary log was not restored to its original location on the backed up server, but to the location specified by --log-bin earlier during the restore of the base backup. The same occurred for relay logs of incremental backups for slaves when the --relay-log option was not used. (Bug #27545745) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
MySQL Enterprise Backup 8.0.12 has been released
Dear MySQL users, MySQL Enterprise Backup 8.0.12, a new version of the online MySQL backup tool, is now available for download from the My Oracle Support (MOS) website as our latest GA release. This release will be available on eDelivery (OSDC) after the next upload cycle. MySQL Enterprise Backup is a commercial extension to the MySQL family of products. MySQL Enterprise Backup 8.0.12 supports only the MySQL Server 8.0. For any earlier versions of the MySQL server 5.7, please use MySQL Enterprise Backup 4.1 and for MySQL Server 5.6 and 5.5, please use MySQL Enterprise Backup 3.12. For additional MySQL Enterprise Backup 8.0 documentation, see the MySQL Enterprise Backup User's Guide (Version 8.0.12) (http://dev.mysql.com/doc/mysql-enterprise-backup/8.0/en/). A brief summary of the changes in MySQL Enterprise Backup (MEB) version 8.0.12 is given below. Changes in MySQL Enterprise Backup 8.0.12 (2018-07-27) * Functionality Added or Changed * Bugs Fixed Functionality Added or Changed * Important Change: Starting from release 8.0.12, the storage engine of the mysql.backup_history table on a backed-up server has switched from CSV to InnoDB. For a MySQL 8.0.12 server that has its database restored from a backup of an earlier server version, MySQL Enterprise Backup 8.0.12 automatically migrates the table when it performs its first backup task on the server. See After restoring an earlier backup to MySQL 8.0.12 or later (http://dev.mysql.com/doc/mysql-enterprise-backup/8.0/en/mysqlbackup.privileges.html#meb-restore-to-8.0.12) for the new user privileges required by mysqlbackup for the mandatory migration to happen. * Important Change: When working with a Group Replication (http://dev.mysql.com/doc/refman/8.0/en/group-replication.html) setup, mysqlbackup now makes the backup history available to all members of the server group by making sure that the backup_history table is updated on a primary node after each mysqlbackup operation. With the implementation of this feature, the new user privilege of SELECT on performance_schema.replication_group_members is now required by mysqlbackup to work with any server, even when it does not belong to a Group Replication setup. See Grant MySQL Privileges to Backup Administrator (http://dev.mysql.com/doc/mysql-enterprise-backup/8.0/en/mysqlbackup.privileges.html) for details. * Version information for mysqlbackup is now printed to the stdout instead of the stderr stream when the --version or --help option is used. (Bug #27253989) * OAuth is now supported for Oracle Cloud Storage client authentication. Two new options, --cloud-storage-url and --cloud-oauth-token, have been introduced for the purpose. See Cloud Storage Options (http://dev.mysql.com/doc/mysql-enterprise-backup/8.0/en/backup-cloud-options.html) for details. Bugs Fixed * Backups for a server failed when it had ANSI_QUOTES in its values for sql_mode. (Bug #27939774) * The maximum value that could be set for the --safe-slave-backup-timeout option was 2700 (seconds), which automatically replaced any larger value. With this fix, there is no longer a maximum limition, even though a high value is not recommended; see the description of --safe-slave-backup-timeout for details. (Bug #27883020) * Restoring an incrementation backup on top of a data directory restored using a compressed backup failed. It was because mysqlbackup did not set is_compressed=0 in the backup_variables.txt file inside the restored data directory. (Bug #27787988) * When mysqlbackup performed sanity checks on InnoDB tablespaces and found a space ID mismatch for an FSP header and a page header, the name of the problematic tablespace was not given in the error report. (Bug #27752703) * If an ALTER TABLE (http://dev.mysql.com/doc/refman/8.0/en/alter-table.html) statement was executed on the server before an incremental backup was taken, a server restored with the backup on which the incremental backup was applied (using the apply-incremental-backup command) could not be started, as the .ibd file of the altered table was missing in the restored data. (Bug #27735134) * After a server has been restored using an incremental backup created with the --incremental-with-redo-log-only option, it could not be started. (Bug #27722525) * mysqlbackup issued a warning whenever the number of files specified in the system variable innodb_data_file_path of the server to be backed up exceeded 100. With this fix, a warning is issued only if the number of InnoDB data files to be opened exceeds the number specified
MySQL Enterprise Backup 8.0.11 has been released
Dear MySQL users,
MySQL Enterprise Backup 8.0.11, a new version of the online MySQL backup
tool, is now available for download from the My Oracle Support (MOS)
website
as our latest GA release. This release will be available on eDelivery
(OSDC)
after the next upload cycle. MySQL Enterprise Backup is a commercial
extension to the MySQL family of products.
A brief summary of the changes in MySQL Enterprise Backup (MEB)
version 8.0.11 is given below.
Changes in MySQL Enterprise Backup 8.0.11 (2018-04-19, General
Availability)
MySQL Enterprise Backup 8.0.11 is the latest release for
MySQL Enterprise Backup. It only supports MySQL Server
8.0.11. For MySQL server 5.7, please use MySQL Enterprise
Backup 4.1, and for MySQL Server 5.6 and 5.5, please use
MySQL Enterprise Backup 3.12.
* Functionality Added or Changed
* Bugs Fixed
Functionality Added or Changed
* Offline backups are no longer supported by mysqlbackup.
As a result, a number of options used for offline backup
operations have been removed. See What's New in MySQL
Enterprise Backup 8.0?
(http://dev.mysql.com/doc/mysql-enterprise-backup/8.0/en/what-is-new.html)
for details. (Bug #27429244)
* The server option --secure-auth, deprecated since MySQL
5.7.5, is no longer supported by mysqlbackup. (Bug
#27265328)
* Servers' use of the keyring_encrypted_file
(http://dev.mysql.com/doc/refman/8.0/en/keyring-encrypted-file-plugin.html)
and keyring_aws
(http://dev.mysql.com/doc/refman/8.0/en/keyring-aws-plugin.html)
plugins is now supported. See Working with
Encrypted InnoDB Tables
(http://dev.mysql.com/doc/mysql-enterprise-backup/8.0/en/meb-encrypted-innodb.html)
for details. (Bug #27127898)
* Information on the executed GTIDs is now included in the
mysqlbackup output and the backup log when the backed up
server has GTIDs enabled. (Bug #25978803)
* The backup_history table now includes a server_uuid
column, which stores the value of the server_uuid of the
backed up server.
* The options --ssl and --ssl-verify-server-cert, already
deprecated in MySQL Enterprise Backup 4.1, have now been
removed. Use the --ssl-mode option instead to configure
the security mode of your connection to the server.
* MySQL Enterprise Firewall
(http://dev.mysql.com/doc/refman/8.0/en/mysql-enterprise-firewall.html)
is now supported.
* A new option, --tls-version, specifies the protocols
mysqlbackup permits for encrypted connections to MySQL
servers.
* A file now tracks information of external tablespaces
(http://dev.mysql.com/doc/refman/8.0/en/tablespace-placing.html)
for a backup or restore in JSON format. See
description for tablespace_tracker
(http://dev.mysql.com/doc/mysql-enterprise-backup/8.0/en/meb-files-backed-up-summary.html#meb_file_tablespace-tracker)
in Types of Files in a Backup
(http://dev.mysql.com/doc/mysql-enterprise-backup/8.0/en/meb-files-backed-up-summary.html#meb-backup-files)
for details.
* The relay log for a backed-up slave server, instead of
being restored always to the data directory on the target
slave server, is now restored by default to the same
location it was found on the backed-up slave server. It
can also be restored to a different location specified
with the new --relay-log option.
* The binary log for a backed-up server, instead of being
restored always to the data directory on the target
server, is now restored by default to the same location
it was found on the backed-up server. It can also be
restored to a different location specified with the new
--log-bin option.
* The buffer size for cloud transfers can now be specified
using the new --cloud-buffer-size option. See Cloud
Storage Options
(http://dev.mysql.com/doc/mysql-enterprise-backup/8.0/en/backup-cloud-options.html)
for details.
* HTTP Basic Authentication and non-chunked transfer are
now supported for backup and restore using OpenStack
Swift-compatible object storage services. Two new
options, --cloud-basicauth-url and
--cloud-chunked-transfer, have been introduced for these
purposes. See Cloud Storage Options
(http://dev.mysql.com/doc/mysql-enterprise-backup/8.0/en/backup-cloud-options.html)
for details.
Bugs Fixed
* After restoring a full backup, if the following restore
of an incremental backup changed the restore location of
the undo log, either mysqlbackup hung, or the restored
server failed to start. With this fix, mysqlbackup quits
with a proper error ("Undo tablespace in the base backup
not found") in the situation.
Users should make sure the undo log location does not
change between successiv
MySQL Enterprise Backup 4.1.0 has been released
Dear MySQL users, MySQL Enterprise Backup v4.1.0, a new version of the online MySQL backup tool, is now available for download from the My Oracle Support (MOS) website as our latest GA release. This release will be available on eDelivery (OSDC) after the next upload cycle. MySQL Enterprise Backup is a commercial extension to the MySQL family of products. MySQL Enterprise Backup 4.1.0 supports only the MySQL Server 5.7.9 and above. For any earlier versions of the MySQL server, please use MySQL Enterprise Backup 3.12 instead. A brief summary of the changes in MySQL Enterprise Backup (MEB) version 4.1.0 is given below. Changes in MySQL Enterprise Backup 4.1.0 (2017-03-03) Functionality Added or Changed * MySQL Enterprise Backup now supports the --ssl-mode option, which enables you to specify the security state of the connection to the server. It replaces the client side --ssl and --ssl-verify-server-cert options, which are now deprecated. See the description of the --ssl-mode option in MySQL 5.7 Reference Manual (http://dev.mysql.com/doc/refman/5.7/en/) for details. (Bug #23508228) * A new option, --skip-final-rescan, makes mysqlbackup skip the final rescan for InnoDB tables that are modified by DDL operations after the database has been locked near the end of a backup operation. This potentially shortens the duration for the lock and reduces the backup's impact on the server's normal operation. See the description for --skip-final-rescan for details. (Bug #21094221) * The output by mysqlbackup, which goes to the stderr stream and the message log, has now been improved to include the timestamps and thread IDs for all steps taken by mysqlbackup, in order to provide more information for debugging purposes. (Bug #20142619) * During the final stage of a backup when MySQL Enterprise Backup tried to temporarily put the database into a read-only state using the FLUSH TABLES WITH READ LOCK statement in order to copy non-InnoDB files, if a long query was running on the server at the same time, the FLUSH TABLES WITH READ LOCK statement could be taking too long to finish, holding up further queries and eventually bringing down the server. A new mysqlbackup option --lock-wait-timeout can now be used to specify the timeout in seconds for the FLUSH TABLES WITH READ LOCK statement. If the timeout is exceeded, the statement is failed and the lock on the tables is released, so that queries held up by the lock can then be executed. mysqlbackup then retries the statement and continues with the backup. Default value for --lock-wait-timeout is 60 [seconds]. (Bug #14339483) * A full set of exit codes have now been implemented for MySQL Enterprise Backup. Also, a new mysqlbackup command, print-message, returns an exit message for any given exit code supplied with the new option --error-code. See Exit codes of MySQL Enterprise Backup (http://dev.mysql.com/doc/mysql-enterprise-backup/4.1/en/ <http://dev.mysql.com/doc/mysql-enterprise-backup/4.1/en/meb-exitcodes.html> meb-exitcodes.html <http://dev.mysql.com/doc/mysql-enterprise-backup/4.1/en/meb-exitcodes.html>) for details. * To increase the performance for hot backups, mysqlbackup now shortens the final phase of the backups by resizing the MyISAM key cache before it locks the database with a FLUSH TABLES WITH READ LOCK statement. The resize triggers a flush of the MyISAM key cache, which reduces the time it takes to run the FLUSH TABLES WITH READ LOCK statement. The MyISAM key cache size is changed back to its original value afterward. * Apply-log operations can now be performed with multiple worker threads in parallel, which can improve performance for the operations. The number of threads to be used can be specified with the --process-thread option. * The copying of redo log files into backups has been made faster, shortening the overall backup time in some cases and making it less likely that a backup fails because a redo log file has been overwritten before it is copied. * MySQL Enterprise Backup now supports optimistic incremental backup, in which mysqlbackup scans only those InnoDB data files that have been modified since the last backup for changed pages and then saves them into the incremental backup. It potentially makes incremental backups faster, and is performed by specifying --incremental=optimistic. See Full-scan versus Optimistic Incremental Backup (http://dev.mysql.com/doc/mysql-enterprise-backup/4.1/ <https://dev.mysql.com/doc/mysql-enterprise-backup/4.0/en/mysqlbackup.i
MySQL Enterprise Backup 4.0.2 has been released
Dear MySQL users, MySQL Enterprise Backup v4.0.2, a new version of the online MySQL backup tool, is now available for download from the My Oracle Support (MOS) website as our latest GA release. This release will be available on eDelivery (OSDC) after the next upload cycle. MySQL Enterprise Backup is a commercial extension to the MySQL family of products. MySQL Enterprise Backup v4.0.2 supports only the MySQL Server 5.7.9 and above. For any earlier versions of the MySQL server, please use MySQL Enterprise Backup 3.12 instead. A brief summary of the changes in MySQL Enterprise Backup (MEB) version 4.0.2 is given below. Changes in MySQL Enterprise Backup 4.0.2 (2016-07-26) Functionality Added or Changed * When there were no tables matching the regular expression specified with the --include-table option during a backup operation, mysqlbackup still created a backup, which contained an empty folder for each database on the server. mysqlbackup now throws an error when --include-tables selects no tables to be backed up. (Bug #18114353) * MySQL Enterprise Backup can now backup and restore encrypted InnoDB tables. See Working with Encrypted InnoDB Tables and Options for Working with Encrypted InnoDB Tablespaces for details. Bugs Fixed * When trying to restore a compressed image backup of a server that had separate undo tablespaces residing in the data directory with the copy-back-and-apply-log command, the operation failed at the apply-log phase, as mysqlbackup could not load the undo tablepaces. (Bug #23583961) * Attempts to restore an image backup from the cloud using the --skip-binlog option failed with a "global tail magic mismatch" error. This was because mysqlbackup failed to perform a non-sequential read from the cloud with gaps caused by the skipping of the binary logs. This fix makes sure mysqlbackup can perform such reads. (Bug #23534700) * When a compressed backup was being restored, if the undo logs had been put into separate tablespaces outside of the data directory on the backed up server, they got restored twice, once mistakenly as general tablespaces with the .ibd extension, and once as undo tablespaces without a file extension. This fix makes sure they are restored normally as undo tablespaces only. (Bug #23179194) * An extract operation for an image backup failed with a checksum mismatch error in cases when, during the backup, an InnoDB tablespace file kept growing in size, and mysqlbackup failed to put the correct file size in its file header. (Bug #22905984) References: This issue is a regression of: Bug #22613568. * During a mysqlbackup operation on a compressed backup (that is, the --uncompress option was used), mysqlbackup, in some situations, wrote to the log file multiple instances of the message "ERROR: InnoDB: file write at offset > 4 GB," even though the operation was actually successful. (Bug #22733760) * Occasionally, some files were missing from an image backup created by the --backup-to-image command. It was due to an internal race condition, which this fix eliminates. (Bug #19600687) The complete manual for MEB 4.0.2 is at http://dev.mysql.com/doc/mysql-enterprise-backup/4.0/en/index.html The tool is available for download from Oracle Software Delivery Cloud (http://edelivery.oracle.com/). You can also download the binaries from MOS, https://support.oracle.com Choose the "Patches & Updates" tab, and then use the "Product or Family (Advanced Search)" feature. If you haven't looked at MEB recently, please do so now and let us know how MEB works for you. Your feedback is greatly appreciated! Please report any problems you have at https://bug.oraclecorp.com/ for the product "MySQL Enterprise Backup" Thanks, On behalf MySQL RE team at Oracle Sreedhar S -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Workbench MySQL Enterprise Backup Error
The beauty of running enterprise versions is that you have support from Oracle. I would gently point you in their direction if not to get what you paid for but also because most of us in this list are unequipped to help you because we don't use the software you have problems with. Good luck On 22 Mar 2016 8:15 pm, "Lisa Smith" <lea...@gmail.com> wrote: > Hello all, > > I have not run across this problem where Workbench will not let me access > the "Online Backup" > link and claims that my version of MySQL Enterprise Backup is 0.0.0 (it is > 4.0.0). > > I had backups running and scheduled through Workbench previously. Yesterday > I changed my data files to another drive and when I restarted Workbench I > was no longer able to access Online Backup. > > I feel like I may be missing something obvious so any suggestions would be > greatly appreciated. > > I am running MySQL Enterprise 5.7 on Windows Server 2012. > > Thank you for reading. >
Workbench MySQL Enterprise Backup Error
Hello all, I have not run across this problem where Workbench will not let me access the "Online Backup" link and claims that my version of MySQL Enterprise Backup is 0.0.0 (it is 4.0.0). I had backups running and scheduled through Workbench previously. Yesterday I changed my data files to another drive and when I restarted Workbench I was no longer able to access Online Backup. I feel like I may be missing something obvious so any suggestions would be greatly appreciated. I am running MySQL Enterprise 5.7 on Windows Server 2012. Thank you for reading.
MySQL Enterprise Backup 4.0.0 has been released
Dear MySQL users, MySQL Enterprise Backup v4.0.0, a new version of the online MySQL backup tool, is now available for download from the My Oracle Support (MOS) website as our latest GA release. This release will be available on eDelivery (OSDC) after the next upload cycle. MySQL Enterprise Backup is a commercial extension to the MySQL family of products. MySQL Enterprise Backup v4.0.0 supports only the MySQL server 5.7.9 and above. For any earlier versions of the MySQL server, please use MySQL Enterprise Backup 3.12 instead. A brief summary of the changes in MySQL Enterprise Backup (MEB) version 4.0.0 is given below. Changes in MySQL Enterprise Backup 4.0.0 (2015-10-21) Functionality Added or Changed * MySQL Enterprise Backup 4.0 supports MySQL server 5.7, on which: + Tables in general tablespaces (http://dev.mysql.com/doc/refman/5.7/en/glossary.htm l#glos_general_tablespace <http://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_general_tablespace>) can be backed up and restored. + InnoDB tables with page sizes (http://dev.mysql.com/doc/refman/5.7/en/glossary.htm l#glos_page_size <http://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_page_size>) of 32 and 64K can be backed up and restored. + Partitioned tables can be selectively backed up or restored using the --use-tts, --include-tables, and --exclude-tables options. See What's New in MySQL Enterprise Backup 4.0? (http://dev.mysql.com/doc/mysql-enterprise-backup/4.0/en/ what-is-new.html <http://dev.mysql.com/doc/mysql-enterprise-backup/4.0/en/what-is-new.html>) for details. * MySQL Enterprise Backup now writes tape information onto the MySQL server when a backup is made to a tape using the System Backup to Tape (SBT) API. See What's New in MySQL Enterprise Backup 4.0? (http://dev.mysql.com/doc/mysql-enterprise-backup/4.0/en/ what-is-new.html <http://dev.mysql.com/doc/mysql-enterprise-backup/4.0/en/what-is-new.html>) for details. Bugs Fixed * When restoring an incremental backup image, if the binary log in the backup was larger than 16MB, the restored binary log would become corrupted. (Bug #20915642) * When the --password option was used without an argument with the copy-back-and-apply-log command, mysqlbackup did not prompt user for a password, but either took the password from the defaults files, or took it to be an empty string when no value was specified in the defaults files. (Bug #20915642) The complete manual for MEB 4.0.0 is at http://dev.mysql.com/doc/mysql-enterprise-backup/4.0/en/index.html The tool is available for download from Oracle Software Delivery Cloud (http://edelivery.oracle.com/). You can also download the binaries from MOS, https://support.oracle.com Choose the "Patches & Updates" tab, and then use the "Product or Family (Advanced Search)" feature. If you haven't looked at MEB recently, please do so now and let us know how MEB works for you. Your feedback is greatly appreciated! Please report any problems you have at https://bug.oraclecorp.com/ for the product "MySQL Enterprise Backup" Thanks, On behalf MySQL RE team at Oracle Sreedhar S
Disk space required for taking backup
Hi All I use mysqldump to take the backup of the entire DB. I want to know if there is way to find out how much disk space would mysqldump require to take the backup of the entire DB in .sql file. Currently what we are doing is to check the size of the /var/lib/mysql/data directory and assuming that the size of that entire directory is what would be required for the backup .sql file but in actual terms when we take the backup into a .sql file it is much smaller in size than the size of the /var/lib/mysql/data directory. So i wanted to know if there is a way to find out the size of file that mysqldump creates from a given DB. Thanks Jatin
MySQL Enterprise Backup 3.11.1 has been released
Dear MySQL users, MySQL Enterprise Backup v3.11.1, a new version of the online MySQL backup tool, is now available for download from the My Oracle Support (MOS) website as our latest GA release. This release will be available on eDelivery (OSDC) after the next upload cycle. MySQL Enterprise Backup is a commercial extension to the MySQL family of products. A brief summary of the changes in MySQL Enterprise Backup (MEB) version 3.11.1 is given below. Changes in MySQL Enterprise Backup 3.11.1 (2014-11-05) Bugs Fixed * The copying of the relay log files from a MySQL slave server into a backup (which has been the default behavior of MySQL Enterprise Backup since 3.11.0) crashed MySQL Enterprise Backup when the server is of version 5.5. (Bug #19904912) * The copying of the binary log files from the server into a backup (which has been the default behavior of MySQL Enterprise Backup since 3.11.0) caused some databases to be silently skipped over during the backup process when (1) the binary log files are located in the server's data directory, and the name of any database matches the data directory's base name or (2) the binary log files are located in a subdirectory of the data directory. (Bug #19883801) You can also find more information on the contents of this release in the change log: http://dev.mysql.com/doc/mysql-enterprise-backup/3.11/en/meb-news.html The complete manual for MEB 3.11.1 is at, http://dev.mysql.com/doc/mysql-enterprise-backup/3.11/en/index.html The tool is available for download from Oracle Software Delivery Cloud (http://edelivery.oracle.com/). You can also download the binaries from MOS, https://support.oracle.com Choose the Patches Updates tab, and then use the Product or Family (Advanced Search) feature. If you haven't looked at MEB recently, please do so now and let us know how MEB works for you. Your feedback is greatly appreciated! Please report any problems you have at https://bug.oraclecorp.com/ for the product MySQL Enterprise Backup Thanks, The MySQL build team at Oracle -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
backup of databases which have a mix of MyISAM- and InnoDB-tables
Hi, i've been already reading the documentation the whole day, but still confused and unsure what to do. We have two databases which are important for our work. So both are stored hourly. Now I recognized that each database has a mixture of MyISAM- and InnoDB-tables. A backup of this mix does not seem to be easy. Until now it was dumped using mysqldump --opt -u root --databases mausdb What I understand until now is that --opt is not necessary because it is default. It includes, among others, --lock-tables which is senseful for saving MyISAM-tables. For InnoDB-tables --single-transaction is useful. But both are mutually exclusive (http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html#option_mysqldump_single-transaction ). The dump of both take about 10 seconds. If the db is locked for that period I can live with. When I use --single-transaction only the InnoDB-tables are consistent. Using --lock-tables the MyISAM-tables are stored consistently. What is about --lock-tables in conjunction with InnoDB-tables ? Are they stored consistently ? Are they locked during the dumping ? As I said, I could live with a small lock period ( 30 sec). Would --lock-all-tables be better ? Lock all tables across all databases. This is achieved by acquiring a global read lock for the duration of the whole dump. This option automatically turns off --single-transaction and --lock-tables (from the manpage). I can live with a global read lock for the duration of the whole dump. --lock-tables causes any pending transactions to be committed implicitly (http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html#option_mysqldump_single-transaction ). Is that a problem for the InnoDB tables ? Our system is: mysql-5.0.26-12.29.1 on a SLES 10 SP4 64 bit host. Bernd -- Bernd Lentes Systemadministration Institut für Entwicklungsgenetik Gebäude 35.34 - Raum 208 HelmholtzZentrum münchen bernd.len...@helmholtz-muenchen.de phone: +49 89 3187 1241 fax: +49 89 3187 2294 http://www.helmholtz-muenchen.de/idg Die Freiheit wird nicht durch weniger Freiheit verteidigt Helmholtz Zentrum München Deutsches Forschungszentrum für Gesundheit und Umwelt (GmbH) Ingolstädter Landstr. 1 85764 Neuherberg www.helmholtz-muenchen.de Aufsichtsratsvorsitzende: MinDir´in Bärbel Brumme-Bothe Geschäftsführer: Prof. Dr. Günther Wess, Dr. Nikolaus Blum, Dr. Alfons Enhsen Registergericht: Amtsgericht München HRB 6466 USt-IdNr: DE 129521671 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: backup of databases which have a mix of MyISAM- and InnoDB-tables
Am 22.08.2014 um 19:40 schrieb Lentes, Bernd: i've been already reading the documentation the whole day, but still confused and unsure what to do. We have two databases which are important for our work. So both are stored hourly. Now I recognized that each database has a mixture of MyISAM- and InnoDB-tables. A backup of this mix does not seem to be easy. Until now it was dumped using mysqldump --opt -u root --databases mausdb What I understand until now is that --opt is not necessary because it is default. It includes, among others, --lock-tables which is senseful for saving MyISAM-tables. For InnoDB-tables --single-transaction is useful. But both are mutually exclusive (http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html#option_mysqldump_single-transaction ). The dump of both take about 10 seconds. If the db is locked for that period I can live with. When I use --single-transaction only the InnoDB-tables are consistent. Using --lock-tables the MyISAM-tables are stored consistently. What is about --lock-tables in conjunction with InnoDB-tables ? Are they stored consistently ? Are they locked during the dumping ? As I said, I could live with a small lock period ( 30 sec). Would --lock-all-tables be better ? Lock all tables across all databases. This is achieved by acquiring a global read lock for the duration of the whole dump. This option automatically turns off --single-transaction and --lock-tables (from the manpage). I can live with a global read lock for the duration of the whole dump. --lock-tables causes any pending transactions to be committed implicitly (http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html#option_mysqldump_single-transaction ). Is that a problem for the InnoDB tables ? Our system is: mysql-5.0.26-12.29.1 on a SLES 10 SP4 64 bit host why that complex? just setup replication because you have a lot of benefits: * in case your master crashs and the FS got damaged you have a real-time backup * for backups you can stop the slave, tar the whole datadir and start the slave * after it is restarted it pulls any change happened on the master due backup * the backup is likely smaller than verbose sql dumps * you do not need to care about table types and what not else signature.asc Description: OpenPGP digital signature
Re: backup of databases which have a mix of MyISAM- and InnoDB-tables
XTrabackup can handle both InnoDB and MyISAM in a consistent way while minimizing lock time on MyISAM tables ... http://www.percona.com/doc/percona-xtrabackup/2.1/ -- Hartmut Holzgraefe, Principal Support Engineer (EMEA) SkySQL - The MariaDB Company | http://www.skysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
MySQL Enterprise Backup 3.10.2 has been released
Dear MySQL users, MySQL Enterprise Backup 3.10.2, a new version of the online MySQL backup tool, is now available for download from the My Oracle Support (MOS) website as our latest GA release. This release will be available on eDelivery (OSDC) after the next upload cycle. MySQL Enterprise Backup is a commercial extension. A brief summary of the changes in MySQL Enterprise Backup (MEB) version 3.10.2 is given below. Changes in MySQL Enterprise Backup 3.10.2 (2014-07-01) Security Note * Security Fix: The linked OpenSSL library for MySQL Enterprise Backup 3.10 has been updated from version 1.0.1g to version 1.0.1h. Versions of OpenSSL prior to and including 1.0.1g are reported to be vulnerable to CVE-2014-0224 (http://www.cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2014-0224). (CVE-2014-0224) Functionality Added or Changed * MySQL Enterprise Backup now supports creation and restoration of single-file backups using a cloud storage. See Section 5.1.15, Cloud Storage Options for details. Bugs Fixed * After a table backed up using the transportable tablespace option (--use-tts) was restored to a server, queries on the table did not make use of its indexes. That was because the cardinalities of the indexes were not properly updated after the table's restoration. This fix adds an ANALYZE TABLE (http://dev.mysql.com/doc/refman/5.6/en/analyze-table.html) step towards the end of the restoration process for tables backed up with the --use-tts option, in order to update the indexes' cardinalities. (Bug #18682317) * When cloning a slave for a GTID-enabled server using MySQL Enterprise Backup, the backup_gtid_executed.sql script created and stored in the backup directory was not copied onto the slave by the copy-back-and-apply-log operation. This fix has the script copied into the data directory of the slave. (Bug #18674861) * The maximum number of memory buffers that could be created for a mysqlbackup operation was hard-coded to be 100, making it impossible to set the number of buffers to a larger value using the number-of-buffers option. This fix removes the hard-coded maximum number for buffers. (Bug #18560870) * mysqlbackup threw an error if a table was dropped when the backup process was running. With this fix, the dropped table is ignored (as it does not need to be restored) and mysqlbackup finishes without throwing an error. (Bug #18358912, Bug #71865) * A segmentation fault occurred when a backup image created from a backup directory was restored using the copy-back-and-apply-log subcommand. It was because copy-back-and-apply-log was not able to extract backup-my.cnf from the image and get the value for innodb_data_file_path. (Bug #18242586) * After an apply-log operation was performed on a compressed backup (with the --uncompress and --apply-log options), when a copy-back-and-apply-log was applied on the backup, the restored data was inconsistent. That was because the first operation did not delete the compressed, .ibz backup file and did not mark the data as uncompressed at the end of the operation. The subsequent copy-back-and-apply-log operation than acted on the still existing, raw, compressed file, but thought that an apply-log operation had already been performed on it. This fix makes mysqlbackup delete the compressed, raw backup file once decompression and apply-log are finished and properly mark the backup as uncompressed and up-to-date. (Bug #18005786, Bug #18005732) * After an incremental backup was applied to a full backup, a second incremental would fail if the same incremental backup directory was used and if the --incremental-base=dir:directory_path option was pointing to the full backup's directory. This was because MySQL Enterprise Backup checked the end LSN in the full backup directory against the end LSN in the MySQL history table (which might not have been updated yet) and failed the process when there was a mismatch. This fix removes that check, so user in the described situation can proceed with creating more incremental backups. (Bug #16249018) You can also find more information on the contents of this release in the change log: http://dev.mysql.com/doc/mysql-enterprise-backup/3.10/en/meb-news.html The complete manual for MEB 3.10.2 is at, http://dev.mysql.com/doc/mysql-enterprise-backup/3.10/en/index.html The tool is available for download from Oracle Software Delivery Cloud (http://edelivery.oracle.com/). You can also download the binaries from MOS, https://support.oracle.com Choose the Patches Updates tab, and then use
MySQL Enterprise Backup 3.10.0 has been released
Dear MySQL users, MySQL Enterprise Backup v3.10.0, a new version of the online MySQL backup tool, is now available for download from the My Oracle Support (MOS) website as our latest GA release. This release will be available on eDelivery (OSDC) after the next upload cycle. MySQL Enterprise Backup is a commercial extension. A brief summary of the changes in MySQL Enterprise Backup (MEB) version 3.10.0 is given below. Changes in MySQL Enterprise Backup 3.10.0 (2014-03-10) Functionality Added or Changed * When using the --skip-unused-pages option for backup operations, MySQL Enterprise Backup now displays, besides the number of pages of data skipped, the total amount of memory saved by using the option. (Bug #15915315) * In order to enhance security for backed up data, MySQL Enterprise Backup now provides encryption function for single-file backups. See Section 7.3, Encryption for Backups and Section 5.1.14, Encryption Options for details. * The compression feature of MySQL Enterprise Backup has been enhanced by the addition of two compression algorithms: the LZ4 method (the default for MySQL Enterprise Backup 3.10 and after) and the LZMA method. Because the LZ4 algorithm, though faster, produces larger files than the ZLIB algorithm used in MySQL Enterprise Backup 3.9 and earlier, users of MySQL Enterprise Backup 3.10 will find an increase in the size of the compressed files if they use the default values for the compression options. * Backups created with the --use-tts option can now be restored in a single step using the copy-back-and-apply-log subcommand. * When the apply-log or apply-incremental-backup operation was performed repeatedly on a backup without using the --force option, an error message was thrown. From MySQL Enterprise Backup release 3.10.0 onward, the same action just causes a message to be returned, saying the operation has already been performed. * MySQL Enterprise Backup can now validate data integrity of a backup directory as well as a backup image file. The validation function in 3.10 has also become more robust, as it tries to verify the checksum value of every data page. * MySQL Enterprise Backup 3.10 introduces two new options for partial backup: --include-tables and --exclude-tables. The new options are intended for replacing the older options of --include, --databases, --databases-list-file, and --only-innodb-with-frm, which will be deprecated in the near future. Bugs Fixed * mysqlbackup could not read the value of innodb_data_file_path from the server when it was more than 1024-character long. It was because mysqlbackup could not read the value from the configuration files and relied on the SHOW VARIABLES command (which reads no more than 1024 characters) to access the parameter. With this fix, innodb_data_file_path can now be read from the configuration files. (Bug #18038409) * A backup taken with both the --use-tts=with-full-locking and --skip-unused-pages options could not be restored. This was because with --use-tts=with-full-locking used, the apply-log operation was always skipped (the command did nothing to the backup), while the expansion of the unused pages was part of the apply-log operation. This fix separates the expansion from the apply-log operation, so that the backup can be restored. (Bug #17764917) * When a database was initialized with --innodb-file-per-table=0 and had a fixed-size system tablespace, all non-InnoDB files backed up had zero size. (Bug #17793020) * After a server restoration from an incremental backup with the copy-back-and-apply-log subcommand, mysqlbackup returned a success code even after a file renaming failed during the restoration process. The restored server thus failed to start with an assertion error. This fix makes sure a proper error is thrown when a file renaming fails during a restoration. (Bug #17539568) * In a replication setup, when a backup was performed on a master, the modifications of the tables mysql.backup_history and mysql.backup_progress were propagated to the slaves, causing a wrong backup status for the slaves to be registered. This fix makes MySQL Backup Enterprise disable binary logging by setting set sql_log_bin=0 during a backup on the master, so that the backup information will not get replicated to the slaves. (Bug #17449449) * When the --messages-logdir option was used and the --trace option was set to 2 or 3 for an apply-log operation, MySQL Enterprise Backup created an extra log file. (Bug #17271318) * When copy-back
Re: How to verify if backup is ok?
Thanks for the reply. pt-table-checksum performs an online replication consistency check by executing checksum queries on the master, which produces different results on replicas that are inconsistent with the master. - It should be used for verifing mysql replication, not for my problem. Any other tips? Best regards, Rafal Radecki. 2013/2/18 Johan De Meersman vegiv...@tuxera.be: - Original Message - From: Rafał Radecki radecki.ra...@gmail.com 3) drop mysql and app databases; 4) restore them from backup; Instead of dropping the DBs, simply restore to another database or server. That will also allow you to perform a comparison using some graphical tool, or if that fails mysqldumps and diff. tips? Should I do it on filesystem level or on mysql level? Are there any external tools? Filesystem level won't work, as it's fairly unlikely that the records will have been written in the same order - let alone that you won't have delete gaps etc. Percona toolkit has tools to verify master/slave setups (pt-table-compare, I believe), I suppose they would also work on non-replicated setups. -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: How to verify if backup is ok?
Thanks, I will use this tool :) 2013/2/19 Johan De Meersman vegiv...@tuxera.be: - Original Message - From: Rafał Radecki radecki.ra...@gmail.com pt-table-checksum performs an online replication consistency check by executing checksum queries on the master, which produces different results on replicas that are inconsistent with the master. - It should be used for verifing mysql replication, not for my problem. Hmm, I didn't realise that that was not a part of pt-table-checksum. The older mk-table-checksum from Maatkit doesn't particularly care about masters and slaves, and will happily compare to unrelated databases. You can still find it at http://www.maatkit.org/doc/mk-table-checksum.html#description . I just copied the contents of a db to another db on the same server, and it works as advertised. Not the first feature that I noticed hasn't been merged into the PT suite :-( -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: How to verify if backup is ok?
- Original Message - From: Rafał Radecki radecki.ra...@gmail.com pt-table-checksum performs an online replication consistency check by executing checksum queries on the master, which produces different results on replicas that are inconsistent with the master. - It should be used for verifing mysql replication, not for my problem. Hmm, I didn't realise that that was not a part of pt-table-checksum. The older mk-table-checksum from Maatkit doesn't particularly care about masters and slaves, and will happily compare to unrelated databases. You can still find it at http://www.maatkit.org/doc/mk-table-checksum.html#description . I just copied the contents of a db to another db on the same server, and it works as advertised. Not the first feature that I noticed hasn't been merged into the PT suite :-( -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
How to verify if backup is ok?
Hi All. I have a development database which is replicated to a slave. Backup is taken daily from this slave. I need to test if the restore procedure is ok. I would like to: 1) block all application access (stop services + iptables block) to the database and lock it read only; 2) make a backup with my script; 3) drop mysql and app databases; 4) restore them from backup; 5) verify if data after restore is exactly the same as before the process. I know how to make steps 1-4 but I do not know how to make step 5. Any tips? Should I do it on filesystem level or on mysql level? Are there any external tools? Best regards, Rafal Radecki. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: How to verify if backup is ok?
- Original Message - From: Rafał Radecki radecki.ra...@gmail.com 3) drop mysql and app databases; 4) restore them from backup; Instead of dropping the DBs, simply restore to another database or server. That will also allow you to perform a comparison using some graphical tool, or if that fails mysqldumps and diff. tips? Should I do it on filesystem level or on mysql level? Are there any external tools? Filesystem level won't work, as it's fairly unlikely that the records will have been written in the same order - let alone that you won't have delete gaps etc. Percona toolkit has tools to verify master/slave setups (pt-table-compare, I believe), I suppose they would also work on non-replicated setups. -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Mysql backup for large databases
Hello, Just one more suggestion to do full backups in large databases: - Dedicated slave (either physical machine, a disk cabinet using iscsi connections from a machine just with a bunch of RAM etc) - Get the slave delayed a certain time (ie: 1 hour, 2 hours...depends on your workload) using Percona Toolkit ( http://www.percona.com/doc/percona-toolkit/2.1/pt-slave-delay.html) - Make sure that slave has (*log*-*slave*-*updates) *in my.cnf. - Make sure the master keeps the necessary logs for a point in time recovery if needed. If something goes wrong you have a full copy of your database, just delayed a few hours. If you want to recover from a drop database (or any big fail executed from MySQL CLI) in the master, you can just sync up your delayed slave to that specific statment - go thru your master binlog, locate the drop database statement and set START SLAVE SQL_THREAD UNTIL master_log_file = 'mysql-bin.XXX', master_log_pos=XXX; to the position right before the bad one. That way you'd have your database as it was just before the wrong statement execution. As Rick said - if you're thinking about doing snapshots, make sure you stop your mysql daemon in the delayed slave to make sure everything is committed to disk. Otherwise can end up having a corrupted DB which won't boot when you need it. And lastly, but probably most importanttest your backups periodically!! Hope this helps Manuel. 2012/11/1 Karen Abgarian a...@apple.com Hi, For doing backups on the primary database, I know nothing better than have your tables in InnoDB and use Innobackup (or MySQL Enterprise backup). This, however, still has the possibility of hanging as it is using FLUSH TABLES WITH READ LOCK for taking backups of MyISAM tables.One may want to script it to kill the backup if the wait exceeds some threshold. The backup taken this way has incremental backups feature which may reduce the impact. For offloading the backups to a replica, there exist more options because the replica can be frozen and/or shut down. For an InnoDB database, it has to be shut down for taking a consistent backup. If it is not, it will result in cute little inconsistencies unless a DBA is one lucky guy and always wins playing roulette. Combining the two, I like the idea of doing EM backup on a replica and having all tables in InnoDB. After a backup has been taken, it will eventually need to be restored unless someone just likes taking them. For this reason, it will have to be brought to the recovered system. Unless somebody knows in advance when the database would need to be recovered (f.e. it is known that a bad guy always corrupts it on Monday mornings), the backup will need to be available for restore always. These considerations usually imply things like shared filesystems between primary and replica, rejecting backups for recoveries across datacenters and the like. Backing up binary logs allows providing continuous coverage for recovery instead of discrete. Cheers Karen On 01.11.2012, at 8:53, machiel.richa...@gmail.com wrote: Well, the biggest problem we have to answer for the clients is the following: 1. Backup method that doesn't take long and don't impact system 2. Restore needs to be done on a quick as possible way in order to minimize downtime. The one client is running master - master replication with master server in usa, and slave in south africa. They need master backup to be done in the states. Sent via my BlackBerry from Vodacom - let your email find you! -Original Message- From: Reindl Harald h.rei...@thelounge.net Date: Thu, 01 Nov 2012 16:49:45 To: mysql@lists.mysql.commysql@lists.mysql.com Subject: Re: Mysql backup for large databases good luck i would call snapshots on a running system much more dumb than innodb_flush_log_at_trx_commit = 2 on systems with 100% stable power instead waste IOPS on shared storages Am 01.11.2012 16:45, schrieb Singer Wang: Assuming you're not doing dumb stuff like innodb_flush_log_at_tx=0 or 2 and etc, you should be fine. We have been using the trio: flush tables with read lock, xfs_freeze, snapshot for months now without any issues. And we test the backups (we load the backup into a staging once a day, and dev once a week) On Thu, Nov 1, 2012 at 11:41 AM, Reindl Harald h.rei...@thelounge.netmailto: h.rei...@thelounge.net wrote: Why do you need downtime? because mysqld has many buffers in memory and there is no atomic flush buffers in daemon and freeze backend FS short ago there was a guy on this list which had to realize this the hard way with a corrupt slave taken from a snapshot that's why i would ALWAYS do master/slave what means ONE time down (rsync; stop master; rsync; start master) for a small timewindow and after that you can stop the slave, take a 100% consistent backup of it's whole datadir and start the slave
Re: Mysql backup for large databases
Hi... Quite often, they create a replica for the purposes of switching to it immediately if the master completely fails. I like the idea of taking backups on that replica, and not create another replica just for doing backups. One reason why I like it is that if I am to recover a database, I will want to do the following: a) restore the database backup; b) apply binary logs starting from the exact position as of which that backup is a snapshot of my database. If I have to use the binary logs taken on another database (f.e. backups are taken on a replica and binary logs on a master), then I will need to match the contents of one database binary logs with the time the backup was taken, which will make my recovery approximate and error prone. From this, it kind of follows that I will want to set log-slave-updates and, for recovery, use backups of a replica and the binary logs generated by that replica. If the backups are on the third system, and I also have another replica for failovers, I will not only use an additional server and storage, but also my recovery plan will become messy. On a failure of a primary, if I will be switching to a replica, I will have to make it a master to the replica I am taking backups on, which can be done, well, approximately. If I do not want to deal with manually finding a position to synchronize them together, I will end up destroying a second replica for backups and rebuilding it from the replica I just switched to, for which in turn I will have to take another backup. On a failure of primary in which I will be restoring the database from the replica I designated for backups, I will also have to restore the replica I use for failover protection. All this tells me that it will be substantially simpler if the replica for backups and replica for failovers are the same thing. Peace Karen On 02.11.2012, at 0:55, Manuel Arostegui wrote: Hello, Just one more suggestion to do full backups in large databases: - Dedicated slave (either physical machine, a disk cabinet using iscsi connections from a machine just with a bunch of RAM etc) - Get the slave delayed a certain time (ie: 1 hour, 2 hours...depends on your workload) using Percona Toolkit ( http://www.percona.com/doc/percona-toolkit/2.1/pt-slave-delay.html) - Make sure that slave has (*log*-*slave*-*updates) *in my.cnf. - Make sure the master keeps the necessary logs for a point in time recovery if needed. If something goes wrong you have a full copy of your database, just delayed a few hours. If you want to recover from a drop database (or any big fail executed from MySQL CLI) in the master, you can just sync up your delayed slave to that specific statment - go thru your master binlog, locate the drop database statement and set START SLAVE SQL_THREAD UNTIL master_log_file = 'mysql-bin.XXX', master_log_pos=XXX; to the position right before the bad one. That way you'd have your database as it was just before the wrong statement execution. As Rick said - if you're thinking about doing snapshots, make sure you stop your mysql daemon in the delayed slave to make sure everything is committed to disk. Otherwise can end up having a corrupted DB which won't boot when you need it. And lastly, but probably most importanttest your backups periodically!! Hope this helps Manuel. 2012/11/1 Karen Abgarian a...@apple.com Hi, For doing backups on the primary database, I know nothing better than have your tables in InnoDB and use Innobackup (or MySQL Enterprise backup). This, however, still has the possibility of hanging as it is using FLUSH TABLES WITH READ LOCK for taking backups of MyISAM tables.One may want to script it to kill the backup if the wait exceeds some threshold. The backup taken this way has incremental backups feature which may reduce the impact. For offloading the backups to a replica, there exist more options because the replica can be frozen and/or shut down. For an InnoDB database, it has to be shut down for taking a consistent backup. If it is not, it will result in cute little inconsistencies unless a DBA is one lucky guy and always wins playing roulette. Combining the two, I like the idea of doing EM backup on a replica and having all tables in InnoDB. After a backup has been taken, it will eventually need to be restored unless someone just likes taking them. For this reason, it will have to be brought to the recovered system. Unless somebody knows in advance when the database would need to be recovered (f.e. it is known that a bad guy always corrupts it on Monday mornings), the backup will need to be available for restore always. These considerations usually imply things like shared filesystems between primary and replica, rejecting backups for recoveries across datacenters and the like. Backing up binary logs allows providing continuous coverage for recovery instead
Mysql backup for large databases
Hi All I am busy investigating some options relating to the backup for MySQL databases when they get quite large. When using the MySQL enterprise, there is the option to use the MySQL enterprise backup as it is part of the Enterprise license. However, when using the GA (freely available) versions, the options for backups on large databases seems to be a bit limited. Using mysqldump and restores on an 80-100GB database seems a bit unpractical as the restore times seems to get quite long as well as the backup times. Does anybody have some ideas on backup strategies that can be investigated in this regard? Just for information, the data in these databases are very crucial data and thus up to 2 years of data needs to be kept in the database for easy retrieval and thus archiving is limited to data older than 2 years while the database receives about 1mil entries per day. Any ideas would be appreciated. Regards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Mysql backup for large databases
On 01/11/2012 11.28, Machiel Richards - Gmail wrote: [...] I am busy investigating some options relating to the backup for MySQL databases when they get quite large. When using the MySQL enterprise, there is the option to use the MySQL enterprise backup as it is part of the Enterprise license. However, when using the GA (freely available) versions, the options for backups on large databases seems to be a bit limited. [...] Hi Machiel, I'm currently evaluating Percona xtrabackup for the same reasons. A few notes: - backup/restore times will be definitely shorter compered to mysqldump - MyISAM tables will be locked for consistency during the backup (just like with MEB or mysqldump). This is optional, but it's required if you want a consistent backup - if I read the documentation correctly, the only way to restore a single database in a consolidated environment is to use the -export option AND percona server as an import server Hope this helps Dimitre -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Mysql backup for large databases
Am 01.11.2012 11:28, schrieb Machiel Richards - Gmail: Using mysqldump and restores on an 80-100GB database seems a bit unpractical as the restore times seems to get quite long as well as the backup times. * setup a master/slave configuration * stop the slave * rsync the raw datadir to whatever backup-medium/location * start the salve again signature.asc Description: OpenPGP digital signature
RE: Mysql backup for large databases
Full backup: * Xtrabackup (Backup: slight impact on source; more if you have MyISAM (as mentioned)) * Slave (Backup: zero impact on Master -- once replication is set up) * LVM -- a minute of server down; see below Full restore: * Xtrabackup - Takes time * Slave - minute(s) to failover, mostly dealing with clients pointing to the new master. * LVM -- a minute? see below With LVM you are taking a filesystem snapshot. This requires a brief restart of mysqld to assure that anything cached is sync'd to disk. After the snapshot is taken, you are at liberty to copy the snapshot to somewhere else. (This must be done before you fill up the volume used for copy-on-write stuff, etc.) The snapshot can be used to instantly restore the entire system on this or some other server. For partial backups... * Xtrabackup - already discussed * Slave -- You are free to construct whatever slicing and dicing, even changing engines to MyISAM and copying files. * LVM -- probably not useful. Consider using PARTITIONing. With it, you could split up a table according to time, copy (row by row) the oldest partition to somewhere else, DROP PARTITION (instantaneous). Restoring is no better than LOAD DATA. In the near future (5.6.x?), you can disconnect a partition from a table and move it to another table; this will greatly speed up archiving. -Original Message- From: Reindl Harald [mailto:h.rei...@thelounge.net] Sent: Thursday, November 01, 2012 4:47 AM To: mysql@lists.mysql.com Subject: Re: Mysql backup for large databases Am 01.11.2012 11:28, schrieb Machiel Richards - Gmail: Using mysqldump and restores on an 80-100GB database seems a bit unpractical as the restore times seems to get quite long as well as the backup times. * setup a master/slave configuration * stop the slave * rsync the raw datadir to whatever backup-medium/location * start the salve again -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Mysql backup for large databases
On Thu, Nov 1, 2012 at 11:34 AM, Rick James rja...@yahoo-inc.com wrote: Full backup: * Xtrabackup (Backup: slight impact on source; more if you have MyISAM (as mentioned)) * Slave (Backup: zero impact on Master -- once replication is set up) * LVM -- a minute of server down; see below Why do you need downtime? Full restore: * Xtrabackup - Takes time * Slave - minute(s) to failover, mostly dealing with clients pointing to the new master. * LVM -- a minute? see below With LVM you are taking a filesystem snapshot. This requires a brief restart of mysqld to assure that anything cached is sync'd to disk. After the snapshot is taken, you are at liberty to copy the snapshot to somewhere else. (This must be done before you fill up the volume used for copy-on-write stuff, etc.) The snapshot can be used to instantly restore the entire system on this or some other server. For partial backups... * Xtrabackup - already discussed * Slave -- You are free to construct whatever slicing and dicing, even changing engines to MyISAM and copying files. * LVM -- probably not useful. Consider using PARTITIONing. With it, you could split up a table according to time, copy (row by row) the oldest partition to somewhere else, DROP PARTITION (instantaneous). Restoring is no better than LOAD DATA. In the near future (5.6.x?), you can disconnect a partition from a table and move it to another table; this will greatly speed up archiving. -Original Message- From: Reindl Harald [mailto:h.rei...@thelounge.net] Sent: Thursday, November 01, 2012 4:47 AM To: mysql@lists.mysql.com Subject: Re: Mysql backup for large databases Am 01.11.2012 11:28, schrieb Machiel Richards - Gmail: Using mysqldump and restores on an 80-100GB database seems a bit unpractical as the restore times seems to get quite long as well as the backup times. * setup a master/slave configuration * stop the slave * rsync the raw datadir to whatever backup-medium/location * start the salve again -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Mysql backup for large databases
Am 01.11.2012 16:36, schrieb Singer Wang: On Thu, Nov 1, 2012 at 11:34 AM, Rick James rja...@yahoo-inc.com mailto:rja...@yahoo-inc.com wrote: Full backup: * Xtrabackup (Backup: slight impact on source; more if you have MyISAM (as mentioned)) * Slave (Backup: zero impact on Master -- once replication is set up) * LVM -- a minute of server down; see below Why do you need downtime? because mysqld has many buffers in memory and there is no atomic flush buffers in daemon and freeze backend FS short ago there was a guy on this list which had to realize this the hard way with a corrupt slave taken from a snapshot that's why i would ALWAYS do master/slave what means ONE time down (rsync; stop master; rsync; start master) for a small timewindow and after that you can stop the slave, take a 100% consistent backup of it's whole datadir and start the slave again which will do all transactions from the binarylog happened in the meantime signature.asc Description: OpenPGP digital signature
Re: Mysql backup for large databases
Assuming you're not doing dumb stuff like innodb_flush_log_at_tx=0 or 2 and etc, you should be fine. We have been using the trio: flush tables with read lock, xfs_freeze, snapshot for months now without any issues. And we test the backups (we load the backup into a staging once a day, and dev once a week) S S On Thu, Nov 1, 2012 at 11:41 AM, Reindl Harald h.rei...@thelounge.netwrote: Am 01.11.2012 16:36, schrieb Singer Wang: On Thu, Nov 1, 2012 at 11:34 AM, Rick James rja...@yahoo-inc.commailto: rja...@yahoo-inc.com wrote: Full backup: * Xtrabackup (Backup: slight impact on source; more if you have MyISAM (as mentioned)) * Slave (Backup: zero impact on Master -- once replication is set up) * LVM -- a minute of server down; see below Why do you need downtime? because mysqld has many buffers in memory and there is no atomic flush buffers in daemon and freeze backend FS short ago there was a guy on this list which had to realize this the hard way with a corrupt slave taken from a snapshot that's why i would ALWAYS do master/slave what means ONE time down (rsync; stop master; rsync; start master) for a small timewindow and after that you can stop the slave, take a 100% consistent backup of it's whole datadir and start the slave again which will do all transactions from the binarylog happened in the meantime
Re: Mysql backup for large databases
good luck i would call snapshots on a running system much more dumb than innodb_flush_log_at_trx_commit = 2 on systems with 100% stable power instead waste IOPS on shared storages Am 01.11.2012 16:45, schrieb Singer Wang: Assuming you're not doing dumb stuff like innodb_flush_log_at_tx=0 or 2 and etc, you should be fine. We have been using the trio: flush tables with read lock, xfs_freeze, snapshot for months now without any issues. And we test the backups (we load the backup into a staging once a day, and dev once a week) On Thu, Nov 1, 2012 at 11:41 AM, Reindl Harald h.rei...@thelounge.net mailto:h.rei...@thelounge.net wrote: Why do you need downtime? because mysqld has many buffers in memory and there is no atomic flush buffers in daemon and freeze backend FS short ago there was a guy on this list which had to realize this the hard way with a corrupt slave taken from a snapshot that's why i would ALWAYS do master/slave what means ONE time down (rsync; stop master; rsync; start master) for a small timewindow and after that you can stop the slave, take a 100% consistent backup of it's whole datadir and start the slave again which will do all transactions from the binarylog happened in the meantime signature.asc Description: OpenPGP digital signature
Re: Mysql backup for large databases
Well, the biggest problem we have to answer for the clients is the following: 1. Backup method that doesn't take long and don't impact system 2. Restore needs to be done on a quick as possible way in order to minimize downtime. The one client is running master - master replication with master server in usa, and slave in south africa. They need master backup to be done in the states. Sent via my BlackBerry from Vodacom - let your email find you! -Original Message- From: Reindl Harald h.rei...@thelounge.net Date: Thu, 01 Nov 2012 16:49:45 To: mysql@lists.mysql.commysql@lists.mysql.com Subject: Re: Mysql backup for large databases good luck i would call snapshots on a running system much more dumb than innodb_flush_log_at_trx_commit = 2 on systems with 100% stable power instead waste IOPS on shared storages Am 01.11.2012 16:45, schrieb Singer Wang: Assuming you're not doing dumb stuff like innodb_flush_log_at_tx=0 or 2 and etc, you should be fine. We have been using the trio: flush tables with read lock, xfs_freeze, snapshot for months now without any issues. And we test the backups (we load the backup into a staging once a day, and dev once a week) On Thu, Nov 1, 2012 at 11:41 AM, Reindl Harald h.rei...@thelounge.net mailto:h.rei...@thelounge.net wrote: Why do you need downtime? because mysqld has many buffers in memory and there is no atomic flush buffers in daemon and freeze backend FS short ago there was a guy on this list which had to realize this the hard way with a corrupt slave taken from a snapshot that's why i would ALWAYS do master/slave what means ONE time down (rsync; stop master; rsync; start master) for a small timewindow and after that you can stop the slave, take a 100% consistent backup of it's whole datadir and start the slave again which will do all transactions from the binarylog happened in the meantime
Re: Mysql backup for large databases
as said: use a replication slave dedicated for backups you can even let a slave write a binlog and sync another slave with this one * rsync backups working with diff * they are extremly fast after the first time * a dedicated backup-slave has ZERO impact i am doing rsync-backups of 1.5 TB data over a WAN link since years each day and the real traffic is between 2 and 5 GB each day Am 01.11.2012 16:53, schrieb machiel.richa...@gmail.com: Well, the biggest problem we have to answer for the clients is the following: 1. Backup method that doesn't take long and don't impact system 2. Restore needs to be done on a quick as possible way in order to minimize downtime. The one client is running master - master replication with master server in usa, and slave in south africa. They need master backup to be done in the states. Sent via my BlackBerry from Vodacom - let your email find you! -Original Message- From: Reindl Harald h.rei...@thelounge.net Date: Thu, 01 Nov 2012 16:49:45 To: mysql@lists.mysql.commysql@lists.mysql.com Subject: Re: Mysql backup for large databases good luck i would call snapshots on a running system much more dumb than innodb_flush_log_at_trx_commit = 2 on systems with 100% stable power instead waste IOPS on shared storages Am 01.11.2012 16:45, schrieb Singer Wang: Assuming you're not doing dumb stuff like innodb_flush_log_at_tx=0 or 2 and etc, you should be fine. We have been using the trio: flush tables with read lock, xfs_freeze, snapshot for months now without any issues. And we test the backups (we load the backup into a staging once a day, and dev once a week) On Thu, Nov 1, 2012 at 11:41 AM, Reindl Harald h.rei...@thelounge.net mailto:h.rei...@thelounge.net wrote: Why do you need downtime? because mysqld has many buffers in memory and there is no atomic flush buffers in daemon and freeze backend FS short ago there was a guy on this list which had to realize this the hard way with a corrupt slave taken from a snapshot that's why i would ALWAYS do master/slave what means ONE time down (rsync; stop master; rsync; start master) for a small timewindow and after that you can stop the slave, take a 100% consistent backup of it's whole datadir and start the slave again which will do all transactions from the binarylog happened in the meantime signature.asc Description: OpenPGP digital signature
RE: Mysql backup for large databases
Are both Masters writeable? If so, you have even worse problems. If not, then consider switching to one master with 2 slaves and use MHA for failover, etc. With that, you can seamlessly and nearly instantly switch to any slave. It does not involve a restore, but gets you alive very quickly by switching to another machine. For your situation: Master (local), another Slave somewhere else but nearby, Slave in South Africa. Then, during failover, prefer the nearby slave. After failing over, worry about 'fixing' the dead Master at your leisure. What is the purpose of the restore? 1. Recovery from dead Master? -- MHA may be best 2. Recovery to some previous point-in-time? -- much of the discussion has centered around this obscure use 3. Building staging/qa/dev/etc machine that matches the master at some point in time? There are different solutions to each of these. #3 is probably best done by having a slave hanging off the Master, then disconnecting it when you want it. If you muck with the data, it will need a long restore; if not, it is a matter of reconnecting and letting replication catch up. -Original Message- From: machiel.richa...@gmail.com [mailto:machiel.richa...@gmail.com] Sent: Thursday, November 01, 2012 8:54 AM To: Reindl Harald; mysql@lists.mysql.com Subject: Re: Mysql backup for large databases Well, the biggest problem we have to answer for the clients is the following: 1. Backup method that doesn't take long and don't impact system 2. Restore needs to be done on a quick as possible way in order to minimize downtime. The one client is running master - master replication with master server in usa, and slave in south africa. They need master backup to be done in the states. Sent via my BlackBerry from Vodacom - let your email find you! -Original Message- From: Reindl Harald h.rei...@thelounge.net Date: Thu, 01 Nov 2012 16:49:45 To: mysql@lists.mysql.commysql@lists.mysql.com Subject: Re: Mysql backup for large databases good luck i would call snapshots on a running system much more dumb than innodb_flush_log_at_trx_commit = 2 on systems with 100% stable power instead waste IOPS on shared storages Am 01.11.2012 16:45, schrieb Singer Wang: Assuming you're not doing dumb stuff like innodb_flush_log_at_tx=0 or 2 and etc, you should be fine. We have been using the trio: flush tables with read lock, xfs_freeze, snapshot for months now without any issues. And we test the backups (we load the backup into a staging once a day, and dev once a week) On Thu, Nov 1, 2012 at 11:41 AM, Reindl Harald h.rei...@thelounge.net mailto:h.rei...@thelounge.net wrote: Why do you need downtime? because mysqld has many buffers in memory and there is no atomic flush buffers in daemon and freeze backend FS short ago there was a guy on this list which had to realize this the hard way with a corrupt slave taken from a snapshot that's why i would ALWAYS do master/slave what means ONE time down (rsync; stop master; rsync; start master) for a small timewindow and after that you can stop the slave, take a 100% consistent backup of it's whole datadir and start the slave again which will do all transactions from the binarylog happened in the meantime
Re: Mysql backup for large databases
Hi, For doing backups on the primary database, I know nothing better than have your tables in InnoDB and use Innobackup (or MySQL Enterprise backup). This, however, still has the possibility of hanging as it is using FLUSH TABLES WITH READ LOCK for taking backups of MyISAM tables.One may want to script it to kill the backup if the wait exceeds some threshold. The backup taken this way has incremental backups feature which may reduce the impact. For offloading the backups to a replica, there exist more options because the replica can be frozen and/or shut down. For an InnoDB database, it has to be shut down for taking a consistent backup. If it is not, it will result in cute little inconsistencies unless a DBA is one lucky guy and always wins playing roulette. Combining the two, I like the idea of doing EM backup on a replica and having all tables in InnoDB. After a backup has been taken, it will eventually need to be restored unless someone just likes taking them. For this reason, it will have to be brought to the recovered system. Unless somebody knows in advance when the database would need to be recovered (f.e. it is known that a bad guy always corrupts it on Monday mornings), the backup will need to be available for restore always. These considerations usually imply things like shared filesystems between primary and replica, rejecting backups for recoveries across datacenters and the like. Backing up binary logs allows providing continuous coverage for recovery instead of discrete. Cheers Karen On 01.11.2012, at 8:53, machiel.richa...@gmail.com wrote: Well, the biggest problem we have to answer for the clients is the following: 1. Backup method that doesn't take long and don't impact system 2. Restore needs to be done on a quick as possible way in order to minimize downtime. The one client is running master - master replication with master server in usa, and slave in south africa. They need master backup to be done in the states. Sent via my BlackBerry from Vodacom - let your email find you! -Original Message- From: Reindl Harald h.rei...@thelounge.net Date: Thu, 01 Nov 2012 16:49:45 To: mysql@lists.mysql.commysql@lists.mysql.com Subject: Re: Mysql backup for large databases good luck i would call snapshots on a running system much more dumb than innodb_flush_log_at_trx_commit = 2 on systems with 100% stable power instead waste IOPS on shared storages Am 01.11.2012 16:45, schrieb Singer Wang: Assuming you're not doing dumb stuff like innodb_flush_log_at_tx=0 or 2 and etc, you should be fine. We have been using the trio: flush tables with read lock, xfs_freeze, snapshot for months now without any issues. And we test the backups (we load the backup into a staging once a day, and dev once a week) On Thu, Nov 1, 2012 at 11:41 AM, Reindl Harald h.rei...@thelounge.net mailto:h.rei...@thelounge.net wrote: Why do you need downtime? because mysqld has many buffers in memory and there is no atomic flush buffers in daemon and freeze backend FS short ago there was a guy on this list which had to realize this the hard way with a corrupt slave taken from a snapshot that's why i would ALWAYS do master/slave what means ONE time down (rsync; stop master; rsync; start master) for a small timewindow and after that you can stop the slave, take a 100% consistent backup of it's whole datadir and start the slave again which will do all transactions from the binarylog happened in the meantime ‹¢ÒÒ‹¤◊5ÂvVæWÂֈƈærƘ7@‹¤f÷Ƙ7B6∫˜fW3¢ΩGG¢òöƘ7G2æ◊˜7Âæ6öÒö◊˜7À‹¥FòVç7V'67ˆS¢ΩGG¢òöƘ7G2æ◊˜7Âæ6öÒö◊˜7À‹ ‹ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Backup Error while backing up MySQL Cluster
Hi, After following the steps at the following website, I tried to do an online backup of the cluster. http://dev.mysql.com/doc/refman/5.0/en/mysql-cluster-backup-using-management-client.html It is a plain vanilla command which is below. The cluster is almost an empty database, but backup is crashing at least one data node and was never successful. ndb_mgm -e START BACKUP WAIT COMPLETED Did anyone have this kind of error before? I tried searching on web but could not find a solution. Here is the error I received. Connected to Management Server at: localhost:1186 Waiting for completed, this may take several minutes Backup failed * 3001: Could not start backup *Backup aborted due to node failure: Permanent error: Internal error ALERT-- Node 2: Backup 2 started from 49 has been aborted. Error: 1326 Here is little background about our setup. OS: Redhat Linux 5.8 Cluster: MySQL 5.5, NDB 7.2.7 Cluster was installed and set up on two hosts using MCM, one host hosting mysqld, ndb_mgmd and the other hosting ndbmtd1 and ndbmtd2. I even tried by setting up the following parameters, but got the same error again. set BackupDataBufferSize:ndbmtd=256M attcluster; set BackupLogBufferSize:ndbmtd=256M attcluster; set BackupMemory:ndbmtd=512M attcluster; set BackupWriteSize:ndbmtd=32M attcluster; set BackupMaxWriteSize:ndbmtd=128M attcluster; Here are some links I found on web similar to my error. http://forums.mysql.com/read.php?25,230891,230959#msg-230959 http://grokbase.com/t/mysql/cluster/0578z8cj71/backup-error http://bugs.mysql.com/bug.php?id=66104 mcm show status -r attcluster; ++--+-+-+---+-+ | NodeId | Process | Host| Status | Nodegroup | Package | ++--+-+-+---+-+ | 49 | ndb_mgmd | ut06sandboxdb01 | running | | 7.2.7 | | 50 | mysqld | ut06sandboxdb01 | running | | 7.2.7 | | 1 | ndbmtd | ut06sandboxdb02 | failed | 0 | 7.2.7 | | 2 | ndbmtd | ut06sandboxdb02 | running | 0 | 7.2.7 | ++--+-+-+---+-+ 4 rows in set (0.07 sec) mcm I see the core dump in DataDir of node 1. [root@ut06sandboxdb02 data]# ls -ltr /opt/app/mcm-1.1.6_64-linux-rhel5-x86/mcm_data/clusters/attcluster/1/data total 16949760 -rw-rw-r-- 1 mysql mysql 0 Oct 19 12:23 ndb_1_out.err -rw-rw-r-- 1 mysql mysql 1 Oct 21 04:02 ndb_1_trace.log.next -rw-rw-r-- 1 mysql mysql568 Oct 21 04:02 ndb_1_error.log -rw-rw-r-- 1 mysql mysql 12202 Oct 21 04:02 ndb_1_trace.log.1_t4 -rw-rw-r-- 1 mysql mysql 923467 Oct 21 04:02 ndb_1_trace.log.1_t3 -rw-rw-r-- 1 mysql mysql 923489 Oct 21 04:02 ndb_1_trace.log.1_t2 -rw-rw-r-- 1 mysql mysql 934663 Oct 21 04:02 ndb_1_trace.log.1_t1 -rw-rw-r-- 1 mysql mysql 948989 Oct 21 04:02 ndb_1_trace.log.1 -rw--- 1 mysql mysql 4104044544 Oct 23 11:04 core.21529 -rw--- 1 mysql mysql 5880332288 Oct 23 18:22 core.8108 -rw--- 1 mysql mysql 4538155008 Oct 23 23:56 core.1124 -rw--- 1 mysql mysql 2924789760 Oct 24 00:32 core.9176 -rw-rw-r-- 1 mysql mysql 460826 Oct 24 00:33 ndb_1_out.log Here is more info from mcmd.log. I really appreciate any help on this. 2012-10-23 18:09:58.193: (message) [T0x19add970 RECFG]: [1,ndb_mgmd,0]: 2012-10-23 18:09:58 [MgmtSrvr] WARNING -- Node 2: Failed to memlock pages, error: 12 (Cannot allocate memory) [1,ndb_mgmd,0]: 2012-10-23 18:09:58 [MgmtSrvr] INFO -- Node 2: Waiting 30 sec for nodes 1 to connect, nodes [ all: 1 and 2 connected: 2 no-wait: ] 2012-10-23 18:09:58.193: (message) [T0x19add970 RECFG]: [1,ndb_mgmd,0]: 2012-10-23 18:09:58 [MgmtSrvr] INFO -- Node 2: Waiting 30 sec for nodes 1 to connect, nodes [ all: 1 and 2 connected: 2 no-wait: ] 2012-10-23 18:09:58.286: (message) [T0x19add970 RECFG]: [1,ndb_mgmd,0]: 2012-10-23 18:09:58 [MgmtSrvr] INFO -- Node 2: Communication to Node 1 opened [1,ndb_mgmd,0]: 2012-10-23 18:09:58 [MgmtSrvr] INFO -- Node 2: Node 1 Connected 2012-10-23 18:09:58.347: (message) last message repeated 1 times 2012-10-23 18:09:58.347: (message) [T0x19add970 RECFG]: [1,ndb_mgmd,0]: 2012-10-23 18:09:58 [MgmtSrvr] INFO -- Node 1: Node 2 Connected [1,ndb_mgmd,0]: 2012-10-23 18:09:58 [MgmtSrvr] INFO -- Node 1: Node 2: API mysql-5.5.25 ndb-7.2.7 [1,ndb_mgmd,0]: 2012-10-23 18:09:58 [MgmtSrvr] INFO -- Node 2: CM_REGCONF president = 1, own Node = 2, our dynamic id = 0/13 [1,ndb_mgmd,0]: 2012-10-23 18:09:58 [MgmtSrvr] INFO -- Node 2: Node 1: API mysql-5.5.25 ndb-7.2.7 [1,ndb_mgmd,0]: 2012-10-23 18:09:58 [MgmtSrvr] INFO -- Node 2: Start phase 1 completed 2012-10-23 18:09:58.437: (message) last message repeated 1 times 2012-10-23 18:09:58.437: (message) [T0x19add970 RECFG]: [1,ndb_mgmd,0]: 2012-10-23 18:09:58 [MgmtSrvr] INFO -- Node 2: Start phase 2 completed (node restart) 2012-10-23 18:09:58.467: (message
Re: Backup Error while backing up MySQL Cluster
On 10/24/2012 11:57 AM, Bheemsen Aitha wrote: Hi, After following the steps at the following website, I tried to do an online backup of the cluster. http://dev.mysql.com/doc/refman/5.0/en/mysql-cluster-backup-using-management-client.html It is a plain vanilla command which is below. The cluster is almost an empty database, but backup is crashing at least one data node and was never successful. ndb_mgm -e START BACKUP WAIT COMPLETED Did anyone have this kind of error before? I tried searching on web but could not find a solution. Here is the error I received. Connected to Management Server at: localhost:1186 Waiting for completed, this may take several minutes Backup failed * 3001: Could not start backup *Backup aborted due to node failure: Permanent error: Internal error ALERT-- Node 2: Backup 2 started from 49 has been aborted. Error: 1326 Here is little background about our setup. OS: Redhat Linux 5.8 Cluster: MySQL 5.5, NDB 7.2.7 Cluster was installed and set up on two hosts using MCM, one host hosting mysqld, ndb_mgmd and the other hosting ndbmtd1 and ndbmtd2. I even tried by setting up the following parameters, but got the same error again. set BackupDataBufferSize:ndbmtd=256M attcluster; set BackupLogBufferSize:ndbmtd=256M attcluster; set BackupMemory:ndbmtd=512M attcluster; set BackupWriteSize:ndbmtd=32M attcluster; set BackupMaxWriteSize:ndbmtd=128M attcluster; Here are some links I found on web similar to my error. http://forums.mysql.com/read.php?25,230891,230959#msg-230959 http://grokbase.com/t/mysql/cluster/0578z8cj71/backup-error http://bugs.mysql.com/bug.php?id=66104 At the bottom of the bug you found, it says: [7 Sep 6:31] Ole John Aske This bug has been fixed in MySQL CLuster 7.2.8 which is now available on http://dev.mysql.com/downloads/cluster/ You need to upgrade to receive this fix. Let us know if that works. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Backup Error while backing up MySQL Cluster
Just for others to know, it was the memory problem. I re-set the memory parameters for ndbmtd (two nodes) to minimum. Then I could run the backup successfully. Thanks BA On Wed, Oct 24, 2012 at 11:57 AM, Bheemsen Aitha pgb...@motorola.comwrote: Hi, After following the steps at the following website, I tried to do an online backup of the cluster. http://dev.mysql.com/doc/refman/5.0/en/mysql-cluster-backup-using-management-client.html It is a plain vanilla command which is below. The cluster is almost an empty database, but backup is crashing at least one data node and was never successful. ndb_mgm -e START BACKUP WAIT COMPLETED Did anyone have this kind of error before? I tried searching on web but could not find a solution. Here is the error I received. Connected to Management Server at: localhost:1186 Waiting for completed, this may take several minutes Backup failed * 3001: Could not start backup *Backup aborted due to node failure: Permanent error: Internal error ALERT-- Node 2: Backup 2 started from 49 has been aborted. Error: 1326 Here is little background about our setup. OS: Redhat Linux 5.8 Cluster: MySQL 5.5, NDB 7.2.7 Cluster was installed and set up on two hosts using MCM, one host hosting mysqld, ndb_mgmd and the other hosting ndbmtd1 and ndbmtd2. I even tried by setting up the following parameters, but got the same error again. set BackupDataBufferSize:ndbmtd=256M attcluster; set BackupLogBufferSize:ndbmtd=256M attcluster; set BackupMemory:ndbmtd=512M attcluster; set BackupWriteSize:ndbmtd=32M attcluster; set BackupMaxWriteSize:ndbmtd=128M attcluster; Here are some links I found on web similar to my error. http://forums.mysql.com/read.php?25,230891,230959#msg-230959 http://grokbase.com/t/mysql/cluster/0578z8cj71/backup-error http://bugs.mysql.com/bug.php?id=66104 mcm show status -r attcluster; ++--+-+-+---+-+ | NodeId | Process | Host| Status | Nodegroup | Package | ++--+-+-+---+-+ | 49 | ndb_mgmd | ut06sandboxdb01 | running | | 7.2.7 | | 50 | mysqld | ut06sandboxdb01 | running | | 7.2.7 | | 1 | ndbmtd | ut06sandboxdb02 | failed | 0 | 7.2.7 | | 2 | ndbmtd | ut06sandboxdb02 | running | 0 | 7.2.7 | ++--+-+-+---+-+ 4 rows in set (0.07 sec) mcm I see the core dump in DataDir of node 1. [root@ut06sandboxdb02 data]# ls -ltr /opt/app/mcm-1.1.6_64-linux-rhel5-x86/mcm_data/clusters/attcluster/1/data total 16949760 -rw-rw-r-- 1 mysql mysql 0 Oct 19 12:23 ndb_1_out.err -rw-rw-r-- 1 mysql mysql 1 Oct 21 04:02 ndb_1_trace.log.next -rw-rw-r-- 1 mysql mysql568 Oct 21 04:02 ndb_1_error.log -rw-rw-r-- 1 mysql mysql 12202 Oct 21 04:02 ndb_1_trace.log.1_t4 -rw-rw-r-- 1 mysql mysql 923467 Oct 21 04:02 ndb_1_trace.log.1_t3 -rw-rw-r-- 1 mysql mysql 923489 Oct 21 04:02 ndb_1_trace.log.1_t2 -rw-rw-r-- 1 mysql mysql 934663 Oct 21 04:02 ndb_1_trace.log.1_t1 -rw-rw-r-- 1 mysql mysql 948989 Oct 21 04:02 ndb_1_trace.log.1 -rw--- 1 mysql mysql 4104044544 Oct 23 11:04 core.21529 -rw--- 1 mysql mysql 5880332288 Oct 23 18:22 core.8108 -rw--- 1 mysql mysql 4538155008 Oct 23 23:56 core.1124 -rw--- 1 mysql mysql 2924789760 Oct 24 00:32 core.9176 -rw-rw-r-- 1 mysql mysql 460826 Oct 24 00:33 ndb_1_out.log Here is more info from mcmd.log. I really appreciate any help on this. 2012-10-23 18:09:58.193: (message) [T0x19add970 RECFG]: [1,ndb_mgmd,0]: 2012-10-23 18:09:58 [MgmtSrvr] WARNING -- Node 2: Failed to memlock pages, error: 12 (Cannot allocate memory) [1,ndb_mgmd,0]: 2012-10-23 18:09:58 [MgmtSrvr] INFO -- Node 2: Waiting 30 sec for nodes 1 to connect, nodes [ all: 1 and 2 connected: 2 no-wait: ] 2012-10-23 18:09:58.193: (message) [T0x19add970 RECFG]: [1,ndb_mgmd,0]: 2012-10-23 18:09:58 [MgmtSrvr] INFO -- Node 2: Waiting 30 sec for nodes 1 to connect, nodes [ all: 1 and 2 connected: 2 no-wait: ] 2012-10-23 18:09:58.286: (message) [T0x19add970 RECFG]: [1,ndb_mgmd,0]: 2012-10-23 18:09:58 [MgmtSrvr] INFO -- Node 2: Communication to Node 1 opened [1,ndb_mgmd,0]: 2012-10-23 18:09:58 [MgmtSrvr] INFO -- Node 2: Node 1 Connected 2012-10-23 18:09:58.347: (message) last message repeated 1 times 2012-10-23 18:09:58.347: (message) [T0x19add970 RECFG]: [1,ndb_mgmd,0]: 2012-10-23 18:09:58 [MgmtSrvr] INFO -- Node 1: Node 2 Connected [1,ndb_mgmd,0]: 2012-10-23 18:09:58 [MgmtSrvr] INFO -- Node 1: Node 2: API mysql-5.5.25 ndb-7.2.7 [1,ndb_mgmd,0]: 2012-10-23 18:09:58 [MgmtSrvr] INFO -- Node 2: CM_REGCONF president = 1, own Node = 2, our dynamic id = 0/13 [1,ndb_mgmd,0]: 2012-10-23 18:09:58 [MgmtSrvr] INFO -- Node 2: Node 1: API mysql-5.5.25 ndb-7.2.7 [1,ndb_mgmd,0
Percona Backup
hi! I am trying to work with percona's extra backup. Has anyone found any simple instructions how to install it? We are using innodb file per table Thanks! Sabika
Re: Percona Backup
http://www.percona.com/doc/percona-xtrabackup/ Cheers Claudio 2012/10/23 Sabika M sabika.makhd...@gmail.com hi! I am trying to work with percona's extra backup. Has anyone found any simple instructions how to install it? We are using innodb file per table Thanks! Sabika -- Claudio
Emergency backup from *.frm *.MYD *.MYI files
Hello My linux system died due a bad system driver and it does not boot ANYMORE, I am backing up my data files to restore a previous worlking backup of the whole system that DOES NOT INCLUDE THE DATABASES... I'm still backing up and will take some time before restore the system, in the mean time I want to know if I can restore the databases from the *.frm *.MYD and *.MYI files, maybe creating the databases again and replace them with the old data files? Or please tell me what procedure should I follow to backup my databases FROM THE FILESYSTEM since I have not access to the mysql, I am using a live CD to access the files... Thanks for your help! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Emergency backup from *.frm *.MYD *.MYI files
On 2012-06-09, Miguel Cardenas renit...@gmail.com wrote: My linux system died due a bad system driver and it does not boot ANYMORE, I am backing up my data files to restore a previous worlking backup of the whole system that DOES NOT INCLUDE THE DATABASES... I hope you will be correcting that in the future! I'm still backing up and will take some time before restore the system, in the mean time I want to know if I can restore the databases from the *.frm *.MYD and *.MYI files, maybe creating the databases again and replace them with the old data files? If they are MyISAM tables, then you should be able to restore most of your tables in this fashion. It depends on how often these tables are written to; if they get writes very frequently then they may be too inconsistent to recover. The basic process is to create the directory (either with CREATE DATABASE or directly on the filesystem; make sure the mysql user can write the directory), then copy the files in to the directory (make sure nobody is doing any writes to the db during this process, and make sure the files are owned by the mysql user when done). This is basically how mysqlhotcopy works--it locks the tables, then copies the raw files to the destination. Your only potential problem is that the tables were open at the time, so changes might not have been written before your crash. Once you have copied the needed files, you should run myisamchk (make sure mysqld is *not* running) on the *.MYI files. --keith -- kkel...@wombat.san-francisco.ca.us -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Emergency backup from *.frm *.MYD *.MYI files
Am 09.06.2012 03:29, schrieb Keith Keller: On 2012-06-09, Miguel Cardenas renit...@gmail.com wrote: I'm still backing up and will take some time before restore the system, in the mean time I want to know if I can restore the databases from the *.frm *.MYD and *.MYI files, maybe creating the databases again and replace them with the old data files? If they are MyISAM tables, then you should be able to restore most of your tables in this fashion. It depends on how often these tables are written to; if they get writes very frequently then they may be too inconsistent to recover. The basic process is to create the directory (either with CREATE DATABASE or directly on the filesystem; make sure the mysql user can write the directory), then copy the files in to the directory (make sure nobody is doing any writes to the db during this process, and make sure the files are owned by the mysql user when done) oh no, do NOT start mysqld or create datanases copy simply the WHOLE datadir while mysqld is stopped pn both sides and you are done, this does include any myisam, innodb tables including myysql-user-table before you are starting mysqld make shure chown -R mysqld /path/to/data/dir that was ot, you are done in the worst case it is the ame as a normal crash and reboot on a working system and needs repair table on some innodb tables to repeir indexes there is no magic or rocket science needed signature.asc Description: OpenPGP digital signature
Re: [First email] Problem with backup and ID's for a Database with INNODB
Hello, I'm new here, so since this is my first question email, I'm looking for an advice/help a way to do it or a link which can explain me more about my related question. [snip] Carlos, you might have better luck if you break the problem down into smaller pieces.. both to make it easier for you to debug, and also easier to ask one small thing at a time on this list. -Govinda -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
[First email] Problem with backup and ID's for a Database with INNODB
Hello, I'm new here, so since this is my first question email, I'm looking for an advice/help a way to do it or a link which can explain me more about my related question. This is the thing: Reciently I've made a backup of a database using INNODB, 1GB database, for a web based - software (IEM) since, it was too big and there were things that did not match in any way like: the links, keys, etc. I've create a new database for this, and uploaded some of the tables from the backup not all, just some tables to the new database. In a first view, I thought that everything was working fine; but in a second view, I've noticed some problems: This is an example to understand better the problem: *Conctact list A -- 90 email address - add, view, edit, delete* * * *If I press view or add, it says:* * * *Contact list A has 200 emails* * * This is a problem, because since it does not show me the real data, when I send emails, it does not send to all of the emails in the list. I am not sure why is this happening, that's why I need to know if someone of you can explain me more or tell me how to fix it. I don't know if this is related but I will mention: Since, in the backup, the email_list (table) does not start with ID1, it starts with ID12 (example) and the second one is ID27 (example) they not follow the ID number. this is not a problem for me anyway, I just want to mention it because I don't know if that could be part of the problem. To understand more here is a sample of the problem: * List Name Created Contacts List Owner ArchiveAction * * List-Name-One02 Aug 2010 59Communi-Clé Santé View Contacts Add Contact Edit Copy Delete* And when I click on View Contacts it shows the following thing: *You have 90 contacts in your mailing list. They are shown below.* * * * * And actually, if you wonder, I count it, and there are 90 contacts on the list... Any help would be appreciated. Best regards, -- Carlos Sura.- www.carlossura.com
Re: MySQL Backup solution for non-technical user
Hi everybody! Dotan Cohen wrote: Is there a simple browser-based MySQL backup solution for non-technical users. [[...]] The main features needed are: 1) Automatic scheduled off-site backups (via SSH or FTP) Off-site = good (for reliability purposes). 2) Backup multiple databases and all their tables Definitely a must have. 3) Single-table recovery via GUI (the user simply chooses which database and which table to recover) If your backup/recovery tool has this feature and your users ever go that route, you (your DBA / your authorities) must be aware that this will break any dependencies between that recovered table and all other, un-recovered ones. Example: Assume a new entry is added to the customer table, then (at least) one order is entered for this customer. Before, during, or after that, some garbage change is done to the customer table, it is detected, and someone decides let's recover the customer table from the last good backup. This will get rid of the garbage, but will also make the orders for new customer be pointing to nowhere. IOW: As soon as you have relationships crossing table boundaries, a single-table recovery is a very risky operation, and it will violate any referential integrity constraints involving that table. [[...]] Regards, Joerg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@oracle.com ORACLE Deutschland B.V. Co. KG, Komturstrasse 18a, D-12099 Berlin Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven Amtsgericht Muenchen: HRA 95603 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL Backup solution for non-technical user
On Fri, May 13, 2011 at 10:21, Joerg Bruehe joerg.bru...@oracle.com wrote: Hi everybody! Dotan Cohen wrote: Is there a simple browser-based MySQL backup solution for non-technical users. [[...]] The main features needed are: 1) Automatic scheduled off-site backups (via SSH or FTP) Off-site = good (for reliability purposes). 2) Backup multiple databases and all their tables Definitely a must have. 3) Single-table recovery via GUI (the user simply chooses which database and which table to recover) If your backup/recovery tool has this feature and your users ever go that route, you (your DBA / your authorities) must be aware that this will break any dependencies between that recovered table and all other, un-recovered ones. Example: Assume a new entry is added to the customer table, then (at least) one order is entered for this customer. Before, during, or after that, some garbage change is done to the customer table, it is detected, and someone decides let's recover the customer table from the last good backup. This will get rid of the garbage, but will also make the orders for new customer be pointing to nowhere. IOW: As soon as you have relationships crossing table boundaries, a single-table recovery is a very risky operation, and it will violate any referential integrity constraints involving that table. [[...]] Regards, Joerg Thanks Joerg for that insight. In fact, this is a very simple installation with no joins but I will keep that in mind for the future. Terrific point. -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL Backup solution for non-technical user
On Tue, May 10, 2011 at 22:58, Michael Heaney mhea...@jcvi.org wrote: Check out Zmanda: http://zmanda.com/zrm-mysql-enterprise.html Michael Heaney JCVI On Wed, May 11, 2011 at 10:00, Johan De Meersman vegiv...@tuxera.be wrote: Zmanda ZRM backup, although the fancy webinterface is only available in the commercial version. Backups are stored on the host that runs the server, and of course it serves multiple MySQL machines. Webinterface is annoyingly slow, though :-) Thanks, I passed the suggestion on. Might be what he is looking for. -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL Backup solution for non-technical user
Zmanda ZRM backup, although the fancy webinterface is only available in the commercial version. Backups are stored on the host that runs the server, and of course it serves multiple MySQL machines. Webinterface is annoyingly slow, though :-) - Original Message - From: Michael Heaney mhea...@jcvi.org To: mysql@lists.mysql.com Sent: Tuesday, 10 May, 2011 9:58:43 PM Subject: Re: MySQL Backup solution for non-technical user Check out Zmanda: http://zmanda.com/zrm-mysql-enterprise.html Michael Heaney JCVI -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MySQL Backup solution for non-technical user
Is there a simple browser-based MySQL backup solution for non-technical users. The server is running Red Hat Enterprise Linux. The main features needed are: 1) Automatic scheduled off-site backups (via SSH or FTP) 2) Backup multiple databases and all their tables 3) Single-table recovery via GUI (the user simply chooses which database and which table to recover) 4) FOSS-license a big plus, but other licenses considered I have ruled out cron/mysqldump for the GUI (browser-based) recovery requirement.I found phpMyBackupPro which looks like a possible solution, and I'd really appreciate other MySQL users' input on the topic. Thank you! -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL Backup solution for non-technical user
On 5/10/2011 3:55 PM, Dotan Cohen wrote: Is there a simple browser-based MySQL backup solution for non-technical users. The server is running Red Hat Enterprise Linux. The main features needed are: 1) Automatic scheduled off-site backups (via SSH or FTP) 2) Backup multiple databases and all their tables 3) Single-table recovery via GUI (the user simply chooses which database and which table to recover) 4) FOSS-license a big plus, but other licenses considered I have ruled out cron/mysqldump for the GUI (browser-based) recovery requirement.I found phpMyBackupPro which looks like a possible solution, and I'd really appreciate other MySQL users' input on the topic. Check out Zmanda: http://zmanda.com/zrm-mysql-enterprise.html Michael Heaney JCVI -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Question about Backup
You are assuming that the database is one table of 5.000 gigabyte, and not 5.000 tables of one gigabyte; and that the backup needs to be consistent :-p - Original Message - From: Reindl Harald h.rei...@thelounge.net To: mysql@lists.mysql.com Sent: Monday, 21 March, 2011 12:44:08 PM Subject: Re: Question about Backup Forget mysqldump because TABLE LOCKS for so hughe databases I would setup a replication-slave because you can stop the salave and make a filesystem-backup of the whole db-folder while the production server is online, we do this with our dbmail-server since 2009 -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Question about Backup
Why, if they shut down the slave, it will be quite consistent. Only that this technique is not as much of the 21th century, but is like 30 years old. Placing locks is about the same as shutting it down. On Mar 22, 2011, at 6:01 AM, Johan De Meersman wrote: You are assuming that the database is one table of 5.000 gigabyte, and not 5.000 tables of one gigabyte; and that the backup needs to be consistent :-p - Original Message - From: Reindl Harald h.rei...@thelounge.net To: mysql@lists.mysql.com Sent: Monday, 21 March, 2011 12:44:08 PM Subject: Re: Question about Backup Forget mysqldump because TABLE LOCKS for so hughe databases I would setup a replication-slave because you can stop the salave and make a filesystem-backup of the whole db-folder while the production server is online, we do this with our dbmail-server since 2009 -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=a...@apple.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Question about Backup
On Tue, March 22, 2011 10:32, Karen Abgarian wrote: Why, if they shut down the slave, it will be quite consistent. Only that this technique is not as much of the 21th century, but is like 30 years old. Placing locks is about the same as shutting it down. Ah, but if you have the dump function do the locking it will also remember to restart it when its done. On Mar 22, 2011, at 6:01 AM, Johan De Meersman wrote: You are assuming that the database is one table of 5.000 gigabyte, and not 5.000 tables of one gigabyte; and that the backup needs to be consistent :-p - Original Message - From: Reindl Harald h.rei...@thelounge.net To: mysql@lists.mysql.com Sent: Monday, 21 March, 2011 12:44:08 PM Subject: Re: Question about Backup Forget mysqldump because TABLE LOCKS for so hughe databases I would setup a replication-slave because you can stop the salave and make a filesystem-backup of the whole db-folder while the production server is online, we do this with our dbmail-server since 2009 -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=a...@apple.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mussa...@csz.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Question about Backup
Hi I need set up a backup strategy for a mysql database in a ubuntu server, the database will grow up to a 5TB. What would be the best option ?? Maybe a script that uses mysqldump?? There is a better way to do this? Thanks in advance to all Pedro.
Re: Question about Backup
Forget mysqldump because TABLE LOCKS for so hughe databases I would setup a replication-slave because you can stop the salave and make a filesystem-backup of the whole db-folder while the production server is online, we do this with our dbmail-server since 2009 Am 21.03.2011 12:23, schrieb Pedro Nuñez: Hi I need set up a backup strategy for a mysql database in a ubuntu server, the database will grow up to a 5TB. What would be the best option ?? Maybe a script that uses mysqldump?? There is a better way to do this? Thanks in advance to all Pedro. -- Mit besten Grüßen, Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / software-development / cms-solutions p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 icq: 154546673, http://www.thelounge.net/ signature.asc Description: OpenPGP digital signature
Re: Question about Backup
Hi, The statement like 'I need to back up a 5T database' is not a backup strategy. It is intention. There are some specifics that have to be determined to work out a strategy. Going from there, the backup solution can be chosen. The examples of questions one typically asks when determining a strategy are as follows: 1. What is my allowed data loss in time units, f.e. hours and minutes? 2. What are my availability requirements for the data to be brought up, that is how quickly I want to recover from backup? 3. Do I need to be able to recover as of discrete points of time or as of any continuous time range? 4. What are my availability requirements during backups? Tx Karen. On Mar 21, 2011, at 4:44 AM, Reindl Harald wrote: Forget mysqldump because TABLE LOCKS for so hughe databases I would setup a replication-slave because you can stop the salave and make a filesystem-backup of the whole db-folder while the production server is online, we do this with our dbmail-server since 2009 Am 21.03.2011 12:23, schrieb Pedro Nuñez: Hi I need set up a backup strategy for a mysql database in a ubuntu server, the database will grow up to a 5TB. What would be the best option ?? Maybe a script that uses mysqldump?? There is a better way to do this? Thanks in advance to all Pedro. -- Mit besten Grüßen, Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / software-development / cms-solutions p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 icq: 154546673, http://www.thelounge.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Question about Backup
That would be the last question :-) Suppose we worked out strategy, lined up the solutions along with their costs and then compare them with our budget. That would be easy to find the one we can afford, and we will know what we could dream about :-). On Mar 21, 2011, at 11:28 AM, Singer X.J. Wang wrote: Also, very important but often not asked: 1) What's my budget? On Mon, Mar 21, 2011 at 14:24, Karen Abgarian a...@apple.com wrote: Hi, The statement like 'I need to back up a 5T database' is not a backup strategy. It is intention. There are some specifics that have to be determined to work out a strategy. Going from there, the backup solution can be chosen. The examples of questions one typically asks when determining a strategy are as follows: 1. What is my allowed data loss in time units, f.e. hours and minutes? 2. What are my availability requirements for the data to be brought up, that is how quickly I want to recover from backup? 3. Do I need to be able to recover as of discrete points of time or as of any continuous time range? 4. What are my availability requirements during backups? Tx Karen. On Mar 21, 2011, at 4:44 AM, Reindl Harald wrote: Forget mysqldump because TABLE LOCKS for so hughe databases I would setup a replication-slave because you can stop the salave and make a filesystem-backup of the whole db-folder while the production server is online, we do this with our dbmail-server since 2009 Am 21.03.2011 12:23, schrieb Pedro Nuñez: Hi I need set up a backup strategy for a mysql database in a ubuntu server, the database will grow up to a 5TB. What would be the best option ?? Maybe a script that uses mysqldump?? There is a better way to do this? Thanks in advance to all Pedro. -- Mit besten Grüßen, Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / software-development / cms-solutions p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 icq: 154546673, http://www.thelounge.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=w...@singerwang.com -- The best compliment you could give Pythian for our service is a referral.
Re: Backup Policy
Interestingly, this page does not say anything about MySQL Enterprise Backups. On Mar 15, 2011, at 8:48 AM, a.sm...@ukgrid.net wrote: Hi, there is a lot of info on different backup methods here: http://dev.mysql.com/doc/refman/5.1/en/backup-methods.html For example, for incremental backups see Making Incremental Backups by Enabling the Binary Log, cheers Andy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=a...@apple.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Backup Policy
Dear all, Taking Backup is must needed task in Database Servers. I research a lot and find techniques to perform it in Mysql. We have options RAID, mylvmbackup , mysqldump. But it depends on the company requirement too. We have a database of more than 250GB in mysql database which is increasing day by day. Currently I am using mysqldump utility of MySQL I perform a full backup about 28 days ago. But is there any mechanism or script to backup only the incremental backups on weekly or daily bases. Data is inserted in separate tables in separate databases. We cann't afford to have some proprietary solution. Any suggestions will always Welcome. Thanks best Regards, Adarsh Sharma -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Backup Policy
Hi, What storage engine are you using? Peter Boros On 03/15/2011 02:12 PM, Adarsh Sharma wrote: Dear all, Taking Backup is must needed task in Database Servers. I research a lot and find techniques to perform it in Mysql. We have options RAID, mylvmbackup , mysqldump. But it depends on the company requirement too. We have a database of more than 250GB in mysql database which is increasing day by day. Currently I am using mysqldump utility of MySQL I perform a full backup about 28 days ago. But is there any mechanism or script to backup only the incremental backups on weekly or daily bases. Data is inserted in separate tables in separate databases. We cann't afford to have some proprietary solution. Any suggestions will always Welcome. Thanks best Regards, Adarsh Sharma -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Backup Policy
xtrabackup, mysqlhotcopy for myisam, incremental backup using zamanda. Krishna On Tue, Mar 15, 2011 at 9:09 PM, petya pe...@petya.org.hu wrote: Hi, What storage engine are you using? Peter Boros On 03/15/2011 02:12 PM, Adarsh Sharma wrote: Dear all, Taking Backup is must needed task in Database Servers. I research a lot and find techniques to perform it in Mysql. We have options RAID, mylvmbackup , mysqldump. But it depends on the company requirement too. We have a database of more than 250GB in mysql database which is increasing day by day. Currently I am using mysqldump utility of MySQL I perform a full backup about 28 days ago. But is there any mechanism or script to backup only the incremental backups on weekly or daily bases. Data is inserted in separate tables in separate databases. We cann't afford to have some proprietary solution. Any suggestions will always Welcome. Thanks best Regards, Adarsh Sharma -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com
Re: Backup Policy
Hi, there is a lot of info on different backup methods here: http://dev.mysql.com/doc/refman/5.1/en/backup-methods.html For example, for incremental backups see Making Incremental Backups by Enabling the Binary Log, cheers Andy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Backup Policy
- Original Message - From: Krishna Chandra Prajapati prajapat...@gmail.com incremental backup using zamanda. I'm running Zmanda on about two dozen hosts, and it comes well-recommended. It doesn't do anything that you can't do yourself, but it's easy to set up, reports well and backs up in what are basically tarfiles, so if all else fails you can still do manual restores. If you have plenty of servers, the enterprise version adds a web interface that the free doesn't have - not a huge thing, but very nice to have. Support is reasonably responsive, albeit at times a bit... well, prone to asking the obvious :-) -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Backup Policy
Hi! Adarsh Sharma wrote: Dear all, Taking Backup is must needed task in Database Servers. [[...]] Correct. We have options RAID, mylvmbackup , mysqldump. But it depends on the company requirement too. RAID is no backup! A RAID system may give you protection against a single disk drive failing (depending on the RAID level you configure), but it doesn't protect you against misuse, operator error, malware (virus, trojan), wilful deletion, ... RAID is no backup! (This can't be repeated often enough.) Doing a backup means to take the data (including schema, privileges, passwords, triggers, ...) to some independent media where it is safe from all misfunction on the original machine. IMNSHO, a backup must be taken offline or write-protected in some other way, so that even a misfunction of the backup machine does not damage your backup. Old tape drives (or newer tape cartridges) with their physical write protection (ring, slider, ...) did provide such protection, it is a pity that they are too slow and too small for today's data (or too expensive for most people). With disks, my solution is: - Have the backup disks on a separate machine, via the network. - Have external backup disks, which are powered off if not is use. - Have two (or more) and use them alternating, so that even in case of a misfunction or drive failure (affecting the backup disk currently in use) the previous backup (on the other disk) remains safe. We have a database of more than 250GB in mysql database which is increasing day by day. Currently I am using mysqldump utility of MySQL I perform a full backup about 28 days ago. But is there any mechanism or script to backup only the incremental backups on weekly or daily bases. Data is inserted in separate tables in separate databases. We cann't afford to have some proprietary solution. If you can afford downtime (shutting down the database), dirvish is a good means to take a file system backup (all your data areas). Check it at www.dirvish.org There are plenty of alternatives, but I didn't try most of them. What I did try was rsnapshot, but I found it too inflexible for my purposes. I can't comment on the other approaches. Whatever approach you take: Make sure the backup gets stored os some separate, protected media. HTH, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@oracle.com (+49 30) 417 01 487 ORACLE Deutschland B.V. Co. KG, Komturstrasse 18a, D-12099 Berlin Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven Amtsgericht Muenchen: HRA 95603 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Backup Policy
On Tue, March 15, 2011 12:36, Joerg Bruehe wrote: Hi! Adarsh Sharma wrote: Dear all, Taking Backup is must needed task in Database Servers. [[...]] Correct. We have options RAID, mylvmbackup , mysqldump. But it depends on the company requirement too. RAID is no backup! A RAID system may give you protection against a single disk drive failing (depending on the RAID level you configure), but it doesn't protect you against misuse, operator error, malware (virus, trojan), wilful deletion, ... RAID is no backup! (This can't be repeated often enough.) Doing a backup means to take the data (including schema, privileges, passwords, triggers, ...) to some independent media where it is safe from all misfunction on the original machine. IMNSHO, a backup must be taken offline or write-protected in some other way, so that even a misfunction of the backup machine does not damage your backup. Old tape drives (or newer tape cartridges) with their physical write protection (ring, slider, ...) did provide such protection, it is a pity that they are too slow and too small for today's data (or too expensive for most people). With disks, my solution is: - Have the backup disks on a separate machine, via the network. - Have external backup disks, which are powered off if not is use. - Have two (or more) and use them alternating, so that even in case of a misfunction or drive failure (affecting the backup disk currently in use) the previous backup (on the other disk) remains safe. We have a database of more than 250GB in mysql database which is increasing day by day. Currently I am using mysqldump utility of MySQL I perform a full backup about 28 days ago. But is there any mechanism or script to backup only the incremental backups on weekly or daily bases. Data is inserted in separate tables in separate databases. We cann't afford to have some proprietary solution. If you can afford downtime (shutting down the database), dirvish is a good means to take a file system backup (all your data areas). Check it at www.dirvish.org There are plenty of alternatives, but I didn't try most of them. What I did try was rsnapshot, but I found it too inflexible for my purposes. I can't comment on the other approaches. Whatever approach you take: Make sure the backup gets stored os some separate, protected media. HTH, J�rg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@oracle.com Another advantage of external drives is they can be taken OFF site and stored away from the building. We use three. One on site, one in transit and one that mirrors (off site of course) the images of the other two. We dump nightly and then backup that. We also backup the binary logs which get rotated every two days (restore is nightly back followed by the binary logs). The only only restore we have had to do is the nephew who knows html. The disks are raided, but as was stated, that is to protect against single point failure. -- William R. Mussatto Systems Engineer http://www.csz.com 909-920-9154 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Backup Policy
You might want to look into replication (http://dev.mysql.com/doc/refman/5.5/en/replication.html). You can set up a replication slave to follow the master DB in real time, or offset by minutes, hours, days, or weeks, or whatever. That way you have a copy already served up waiting in the wings, very accessible. It's best to have at least one slave that is offset by at least a day IMHO because the problem could be human error and this will be faithfully replicated to the slave. MySQL replication works very well. Doing a dump is useful in some situations but we are more and more looking to more convenient ways, as storage and hardware is pretty cheap but time in a critical failure is not cheap. Jim McNeely On Mar 15, 2011, at 1:51 PM, Wm Mussatto wrote: On Tue, March 15, 2011 12:36, Joerg Bruehe wrote: Hi! Adarsh Sharma wrote: Dear all, Taking Backup is must needed task in Database Servers. [[...]] Correct. We have options RAID, mylvmbackup , mysqldump. But it depends on the company requirement too. RAID is no backup! A RAID system may give you protection against a single disk drive failing (depending on the RAID level you configure), but it doesn't protect you against misuse, operator error, malware (virus, trojan), wilful deletion, ... RAID is no backup! (This can't be repeated often enough.) Doing a backup means to take the data (including schema, privileges, passwords, triggers, ...) to some independent media where it is safe from all misfunction on the original machine. IMNSHO, a backup must be taken offline or write-protected in some other way, so that even a misfunction of the backup machine does not damage your backup. Old tape drives (or newer tape cartridges) with their physical write protection (ring, slider, ...) did provide such protection, it is a pity that they are too slow and too small for today's data (or too expensive for most people). With disks, my solution is: - Have the backup disks on a separate machine, via the network. - Have external backup disks, which are powered off if not is use. - Have two (or more) and use them alternating, so that even in case of a misfunction or drive failure (affecting the backup disk currently in use) the previous backup (on the other disk) remains safe. We have a database of more than 250GB in mysql database which is increasing day by day. Currently I am using mysqldump utility of MySQL I perform a full backup about 28 days ago. But is there any mechanism or script to backup only the incremental backups on weekly or daily bases. Data is inserted in separate tables in separate databases. We cann't afford to have some proprietary solution. If you can afford downtime (shutting down the database), dirvish is a good means to take a file system backup (all your data areas). Check it at www.dirvish.org There are plenty of alternatives, but I didn't try most of them. What I did try was rsnapshot, but I found it too inflexible for my purposes. I can't comment on the other approaches. Whatever approach you take: Make sure the backup gets stored os some separate, protected media. HTH, J�rg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@oracle.com Another advantage of external drives is they can be taken OFF site and stored away from the building. We use three. One on site, one in transit and one that mirrors (off site of course) the images of the other two. We dump nightly and then backup that. We also backup the binary logs which get rotated every two days (restore is nightly back followed by the binary logs). The only only restore we have had to do is the nephew who knows html. The disks are raided, but as was stated, that is to protect against single point failure. -- William R. Mussatto Systems Engineer http://www.csz.com 909-920-9154 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=j...@newcenturydata.com
Re: Backup onle one procedure
sorry, my bad. Its -R and not -p. regards anandkl On Thu, Feb 10, 2011 at 1:58 PM, Singer X.J. Wang w...@singerwang.comwrote: Remember that procedure is defined per database, mysqldump -u[user] -p[pass] --where=db=`whatyouwant` and name=`whatyouwant` mysql proc On Thu, Feb 10, 2011 at 02:55, Ananda Kumar anan...@gmail.com wrote: there is -p option please used that. On Thu, Feb 10, 2011 at 12:47 PM, Johan De Meersman vegiv...@tuxera.be wrote: On Thu, Feb 10, 2011 at 7:43 AM, Adarsh Sharma adarsh.sha...@orkash.com wrote: I am researching all the ways to backup in mysql and donot able to find a command that take individual backup of only one procedure in mysql. Have a look at the SHOW CREATE PROCEDURE syntax. It's not mysqldump, but it will yield a statement that can recreate the given procedure from scratch. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- The best compliment you could give Pythian for our service is a referral.
Re: Backup onle one procedure
Hmm, I haven't seen the mail from Singer, yet. On Thu, Feb 10, 2011 at 9:33 AM, Ananda Kumar anan...@gmail.com wrote: On Thu, Feb 10, 2011 at 1:58 PM, Singer X.J. Wang w...@singerwang.comwrote: mysqldump -u[user] -p[pass] --where=db=`whatyouwant` and name=`whatyouwant` mysql proc Yes, I thought of that, too; but the manual explicitly states that manual manipulation of the proc procedure isn't supported (which i presume to mean there is no equivalent to *flush privileges*), so that's gonna take manual reassembly to restore. The privileges to the procedure (creator/invoker and so) are also in a separate table, procs_priv. *Show create procedure* should yield a complete, executable statement that recreates the procedure as-is. Something to keep in mind, incidentally, is that security is *creator* by default - the proc runs with the creator's privileges. If you migrate the procedure to a host where that same user does not exist or has different permissions, it might mysteriously not work. IMO, security should be set to *invoker* by default, as that is also the more secure option - if you don't have permission on the tables, I don't want you running stored procs on them. That's for MySQL to change, though :-) -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Backup onle one procedure
Dear all, I am researching all the ways to backup in mysql and donot able to find a command that take individual backup of only one procedure in mysql. I know this command and it needs some modification. Please help. mysqldump -h192.168.0.10 -uroot -porkash --routines --no-create-info --no-data --no-create-db --skip-opt test_crawler adarsh.sql Thanks Regards Adarsh Sharma -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Backup onle one procedure
On Thu, Feb 10, 2011 at 7:43 AM, Adarsh Sharma adarsh.sha...@orkash.comwrote: I am researching all the ways to backup in mysql and donot able to find a command that take individual backup of only one procedure in mysql. Have a look at the SHOW CREATE PROCEDURE syntax. It's not mysqldump, but it will yield a statement that can recreate the given procedure from scratch. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Backup onle one procedure
there is -p option please used that. On Thu, Feb 10, 2011 at 12:47 PM, Johan De Meersman vegiv...@tuxera.bewrote: On Thu, Feb 10, 2011 at 7:43 AM, Adarsh Sharma adarsh.sha...@orkash.com wrote: I am researching all the ways to backup in mysql and donot able to find a command that take individual backup of only one procedure in mysql. Have a look at the SHOW CREATE PROCEDURE syntax. It's not mysqldump, but it will yield a statement that can recreate the given procedure from scratch. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re:Incremental Backup Script
Hi Please be send sample incremental backup script (bash Shell script Easy to understand) Thanks Regards, Kranthikiran
Re: Incremental Backup Script
On 10/13/2010 9:18 AM, kranthi wrote: Hi Please be send sample incremental backup script (bash Shell script Easy to understand) Thanks Regards, Kranthikiran I think you missed the points of the previous replies. MySQL does not do incremental backups the the same way that other RDBMS products you may be familiar with. You can take full backups (all of the tables and all of the data) and partial backups (some of the tables or some of the data). With those, you can combine the contents of the Binary Log Files to provide yourself with the ability to perform a point-in-time-recovery (PITR). Which combination of backup techniques (and there are multiple techniques) you use depends on your hardware, software, and operational requirements. Please read the fine manual for more details: http://dev.mysql.com/doc/refman/5.1/en/backup-and-recovery.html -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Backup
Do keep in mind that what you get there is going to be useless if your database doesn't already contain all the previous data. The inserts will work, of course, but any data modification may fail because the rows you modify aren't there when you restore. Make sure you know exactly what you want and what you're doing before implementing this. On Mon, Oct 11, 2010 at 5:10 PM, kranthi kranthikiran@gmail.com wrote: I get, I use following mysqlbinlog --start-date=2010-10-09 9:55:00 \ --stop-date=2010-10-10 10:05:00 \ /var/log/mysql/bin.123456 \ /tmp/mysql_restore.sql Thanks all From: andrew.2.mo...@nokia.com [mailto:andrew.2.mo...@nokia.com] Sent: Sunday, October 10, 2010 4:46 AM To: kranthikiran@gmail.com Subject: Re: Backup hmm a yesterday backup? MySqldump using where clauses? Why do you only want yesterday? -- Sent from my HTC Desire on 3 -- - Reply message - From: ext kranthi kranthikiran@gmail.com Date: Sun, Oct 10, 2010 04:51 Subject: Backup To: bac...@lists.mysql.com bac...@lists.mysql.com Hi , My database size is 900GB.i don't want full database backup. I need only yesterday backup. how can I take, please help me. Thanks Regards, Kranti -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Backup
Hi kranthi, Take a look at LVM and xtrabackup. http://www.mysqlperformanceblog.com/2009/02/24/xtrabackup-open-source-alternative-for-innodb-hot-backup-call-for-ideas/ http://marcus.bointon.com/archives/87-MySQL-backups-with-Perconas-XtraBackup.html Krishna CGI (cgi.com) On Sun, Oct 10, 2010 at 10:30 AM, kranthi kranthikiran@gmail.comwrote: Hi , My database size is 900GB.i don't want full database backup. I need only yesterday backup. how can I take, please help me. Thanks Regards, Kranti
Re: innodb backup
I'm interested in InnoDB backups. Does anyone use PHPMyAdmin ? I've a MySQL server on a shared hosting server. Cheers Neil On Sat, Oct 9, 2010 at 3:21 AM, short.cut...@yahoo.com.cn wrote: Hello, Is there any good document for backup of InnoDB? includes the increment backup and full backup. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=neil.tompk...@googlemail.com
Re: innodb backup
Hi, On Sat, Oct 9, 2010 at 4:21 AM, short.cut...@yahoo.com.cn wrote: Hello, Is there any good document for backup of InnoDB? includes the increment backup and full backup. There is an overview of backups here http://dev.mysql.com/doc/refman/5.1/en/backup-methods.html XtraBackup supports incremental backups for InnoDB/XtraDB. http://www.percona.com/software/percona-xtrabackup/ Ewen Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=ewen.fort...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Backup
Hey Kranthi, If you have binlogs enabled, do a binary logs backup everyday i.e going to be your everyday backup which consists of the sql modified statements. On Sun, Oct 10, 2010 at 11:13 AM, yung inno...@gmail.com wrote: 2010/10/10 kranthi kranthikiran@gmail.com: Hi , My database size is 900GB.i don't want full database backup. I need only yesterday backup. how can I take, please help me. How about the document there: http://dev.mysql.com/doc/refman/5.1/en/backup-strategy-example.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com -- Thanks Suresh Kuna MySQL DBA
Re: Backup
I get, I use following mysqlbinlog --start-date=2010-10-09 9:55:00 \ --stop-date=2010-10-10 10:05:00 \ /var/log/mysql/bin.123456 \ /tmp/mysql_restore.sql Thanks all From: andrew.2.mo...@nokia.com [mailto:andrew.2.mo...@nokia.com] Sent: Sunday, October 10, 2010 4:46 AM To: kranthikiran@gmail.com Subject: Re: Backup hmm a yesterday backup? MySqldump using where clauses? Why do you only want yesterday? -- Sent from my HTC Desire on 3 -- - Reply message - From: ext kranthi kranthikiran@gmail.com Date: Sun, Oct 10, 2010 04:51 Subject: Backup To: bac...@lists.mysql.com bac...@lists.mysql.com Hi , My database size is 900GB.i don't want full database backup. I need only yesterday backup. how can I take, please help me. Thanks Regards, Kranti
Re: Backup
2010/10/10 kranthi kranthikiran@gmail.com: Hi , My database size is 900GB.i don't want full database backup. I need only yesterday backup. how can I take, please help me. How about the document there: http://dev.mysql.com/doc/refman/5.1/en/backup-strategy-example.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
innodb backup
Hello, Is there any good document for backup of InnoDB? includes the increment backup and full backup. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
mysqldump backup
Could you please inform me how to test the mysql databases backup failure by using the mysqldump utility. I written a unix script for sending status notification against ten mysql databases. I need to test the nine databases backups have completed successfully and one failed due some reason. Thanks for your help.
Re: mysqldump backup
redirect your standard errors to some log file.. mysqldump --all-databases --flush-logs --master-data=2 /bk01/all_databases_`date +%a`.sql 2 /tmp/test.log i follow the above syntax for logging the errors in my script. Thanks Anand On Fri, May 28, 2010 at 5:44 PM, Angelina Paul arshup...@gmail.com wrote: Could you please inform me how to test the mysql databases backup failure by using the mysqldump utility. I written a unix script for sending status notification against ten mysql databases. I need to test the nine databases backups have completed successfully and one failed due some reason. Thanks for your help.
Re: mysqldump backup
I looking for a way to corrupt a mysql database forcefully for testing purpose and then the mysqldump utility will fail for taking backup against it. On Fri, May 28, 2010 at 8:06 AM, Anand.S anand@gmail.com wrote: redirect your standard errors to some log file.. mysqldump --all-databases --flush-logs --master-data=2 /bk01/all_databases_`date +%a`.sql 2 /tmp/test.log i follow the above syntax for logging the errors in my script. Thanks Anand On Fri, May 28, 2010 at 5:44 PM, Angelina Paul arshup...@gmail.comwrote: Could you please inform me how to test the mysql databases backup failure by using the mysqldump utility. I written a unix script for sending status notification against ten mysql databases. I need to test the nine databases backups have completed successfully and one failed due some reason. Thanks for your help.
Re: mysqldump backup
In infinite wisdom Angelina Paul arshup...@gmail.com wrote: [1 text/plain; ISO-8859-1 (7bit)] I looking for a way to corrupt a mysql database forcefully for testing purpose and then the mysqldump utility will fail for taking backup against it. kill -9 MYSQLD_PID while the backup is running should give you an indication. -- Raj Shekhar - If there's anything more important than my ego around, I want it caught and shot now. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: mysqldump backup
-Original Message- From: Raj Shekhar [mailto:rajl...@rajshekhar.net] Sent: Friday, May 28, 2010 2:40 PM To: mysql@lists.mysql.com Cc: mysql@lists.mysql.com Subject: Re: mysqldump backup In infinite wisdom Angelina Paul arshup...@gmail.com wrote: [1 text/plain; ISO-8859-1 (7bit)] I looking for a way to corrupt a mysql database forcefully for testing purpose and then the mysqldump utility will fail for taking backup against it. kill -9 MYSQLD_PID while the backup is running should give you an indication. [JS] If you know the file name of one of your databases, how about just using the cat (*nix) or copy (Windows) command to trash a little bit of it. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com -- Raj Shekhar - If there's anything more important than my ego around, I want it caught and shot now. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=je...@gii.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Recommend A Backup User / Privileges?
Carlos mennens wrote: Can you guys recommend something for me here? I don't really know enough about MySQL 'grant' permissions to determine which would work or if the 'backup' user would require high level privileges. Hi, Set the rights of the script file to rwx-- . So only the owner can read it. Or provide in the home of the user who executes the script a file named .my.cnf . Inside that file you provide a section [mysqld] , where you can specify the user and the password to connect to the database. Also this file has only to be readable for the user who executes the script (rwx--). Bernd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
How to backup a mix from InnoDB- and MyISAM-tables ?
Hi, I have the problem that i have to backup several databases who include a mix of InnoDB- and MyISAM-tables. I'd like to use mysqldump. The manpage proposes different options for MyISAM- and InnoDB-tables. What is about --single-transaction ? --single-transaction is recommend for InnoDB-tables. MyISAM-tables are not dumped in a consistent state using this option, so it's not the optimum for my scenario. Is --single-transaction a recommendation for InnoDB-tables or is it a must ? What is about --lock-all-tables ? The manpage says --single-transaction is a much better option for InnoDB-tables (see above). But does --lock-all-tables work with InnoDB-tables ? The manpage also says: This is achieved by acquiring a global read lock for the duration of the whole dump. Does that mean that also reading is locked, or does it mean that it is locked to enable reading ? Currently i have the impression that --lock-all-tables is the better way for me dumping databases including both types of tables. Thanks for any answer. Bernd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Recommend A Backup User / Privileges?
Hi Carlos, I would say the below grants for a user to perform backup is the minimum grants which we can provide.. grant select , lock tables, file on *.* to backup_user'@'localhost' identified by 'somepassword'; Thanks Anand On Wed, Apr 28, 2010 at 10:28 PM, Carlos Mennens carlosw...@gmail.comwrote: I downloaded a MySQL backup script today since I have 3 or 4 relatively small databases. The script can be found here: http://sourceforge.net/projects/automysqlbackup/ Now I was wondering if I can create a local database user 'backup'@'localhost' grant him a level of permissions needed to perform a 'mysqldump' so he can backup the databases to an directory on the server. My Question is what level or permissions does a user need to perform a 'mysqldump' on a database since that particular user's password will be entered into the script noted above via plain text. I am scared the password will be compromised that would be bad if it's root or someone who has GRANT ALL PRIVILEGES ON *.*. Can you guys recommend something for me here? I don't really know enough about MySQL 'grant' permissions to determine which would work or if the 'backup' user would require high level privileges. -Carlos -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=anand@gmail.com
Recommend A Backup User / Privileges?
I downloaded a MySQL script that backs up all my databases automatically which looks to work amazing. My question is in this particular script (linked below) there is a section to add a user name and password for someone who has access to the databases I am attempting to backup with the script. Now I could just place the MySQL root user or my own user (admin) account in here which has a plain text password exposed in the configuration for all to see. Should I create a generic user in MySQL just for backup roles and privileges? I don't know enough about GRANT OPTIONS or backups to know if I can create a dummy user and give him only what he requires to backup the databases safely or if this script requires the root or someone with GRANT ALL OPTIONS enabled. http://sourceforge.net/projects/automysqlbackup/ Thanks for any info! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Recommend A Backup User / Privileges?
I downloaded a MySQL backup script today since I have 3 or 4 relatively small databases. The script can be found here: http://sourceforge.net/projects/automysqlbackup/ Now I was wondering if I can create a local database user 'backup'@'localhost' grant him a level of permissions needed to perform a 'mysqldump' so he can backup the databases to an directory on the server. My Question is what level or permissions does a user need to perform a 'mysqldump' on a database since that particular user's password will be entered into the script noted above via plain text. I am scared the password will be compromised that would be bad if it's root or someone who has GRANT ALL PRIVILEGES ON *.*. Can you guys recommend something for me here? I don't really know enough about MySQL 'grant' permissions to determine which would work or if the 'backup' user would require high level privileges. -Carlos -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
backup: mysqldump or mysqlhotcopy ?
hello, i'm new to MySQL, so i have currently some basic questions. I have a MySQL-Server with 15 users. Every User can create databases. I expect that the amount of data which has to be backuped will increase constantly. What i want: I'd like to have a backup on a regular basis. I think i will create two cronjobs, one running daily, the other running weekly. Initially, we will have just MyISAM tables, later on maybe InnoDB tables. I'd like to backup _all_ databases, but having the possibility to restore only some databases. And the number and names of the databases change often, but i don't like to adapt my script regulary. Which tool do you recommend ? mysqldump or mysqlhotcopy ? Can i backup all databases with mysqlhotcopy using mysqlhotcopy --regexp=[a-zA-Z0-9]\+ ? Can i restore from this backup only some databases just copying the saved directories of the backuped databases to the MySQL-directory ? Thanks in advance. Bernd -- Bernd Lentes Systemadministration Institut für Entwicklungsgenetik HelmholtzZentrum münchen bernd.len...@helmholtz-muenchen.de phone: +49 89 3187 1241 fax: +49 89 3187 3826 http://www.helmholtz-muenchen.de/idg Im Kampf um das Unerreichbare verliert das Erreichte seinen Wert -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: better way to backup 50 Gig db?
Gavin, Right, that is also an option, but you are really not sure 100% that everything that is on memory is on the disk (buffers etc...) also if it is definitely good for a disaster recovery. What I meant is that the only way to have a 100% guaranteed consistent binary backup is when the database is shut down. Of course this is almost never an option, unless (tada) you have a slave dedicated for that. One remark on your note: Just a note though, I noticed someone added replication to a slave as a backup option. I really discourage that. Replication makes no guarantees that the data on your slave is the same as the data on your master. Unless you're also checking consistency, a slave should be treated as a somewhat unreliable copy of your data. While it is true that replication makes no guarantees, if your slave is not the same as the master and you rely on that for production, you have some problems, try to go to business and say, our slave (which at least 50% of our applications use to read data) is not really in sync, watch their facial expression! Believe me, in many production environments the method used for backups relies on the slave, not on the master. It is so much useful and important that you should have all your efforts go for having a consistent read-only slave 'dedicated' only for backups, no other client messing with it. Just my two cents Claudio Gavin Towey wrote: You can make binary backups from the master using filesystem snapshots. You only need to hold a global read lock for a split second. Regards, Gavin Towey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: better way to backup 50 Gig db?
How does the below not guarantee me 100% that everything that can be consistent, is ? mysql flush tables with read lock; unixhost# sync unixhost# lvm create snapshot mysql unlock tables; I agree that there may be data inconsistencies within MyISAM, as it has no transactions, but there's also no guarantee that there isn't an application in the middle of multiple insert statements the moment you shut your database or your application. You can't magically get full consistency out of your database if your application hasn't been designed for it. Shutting systems down for backup may be fine for small setups, but on a server that hosts multiple critical applications, you just can't do that. You back up as consistently as you can without downtime, and pick the time of night with the lowest likelyhood of activity for it. On Wed, Apr 21, 2010 at 8:12 AM, Claudio Nanni claudio.na...@gmail.comwrote: Gavin, Right, that is also an option, but you are really not sure 100% that everything that is on memory is on the disk (buffers etc...) also if it is definitely good for a disaster recovery. What I meant is that the only way to have a 100% guaranteed consistent binary backup is when the database is shut down. Of course this is almost never an option, unless (tada) you have a slave dedicated for that. One remark on your note: Just a note though, I noticed someone added replication to a slave as a backup option. I really discourage that. Replication makes no guarantees that the data on your slave is the same as the data on your master. Unless you're also checking consistency, a slave should be treated as a somewhat unreliable copy of your data. While it is true that replication makes no guarantees, if your slave is not the same as the master and you rely on that for production, you have some problems, try to go to business and say, our slave (which at least 50% of our applications use to read data) is not really in sync, watch their facial expression! Believe me, in many production environments the method used for backups relies on the slave, not on the master. It is so much useful and important that you should have all your efforts go for having a consistent read-only slave 'dedicated' only for backups, no other client messing with it. Just my two cents Claudio Gavin Towey wrote: You can make binary backups from the master using filesystem snapshots. You only need to hold a global read lock for a split second. Regards, Gavin Towey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: better way to backup 50 Gig db?
Johan, Is the fact that InnoDB ignores the command FLUSH TABLES WITH READ LOCK; enough? :) InnoDB has buffers and activities going on even if you locked the tables and you are not sure that its buffers are on the disk when you snapshot. Again, you might be lucky and trust in the InnoDB recovery, what I state is that there is only one 100% guaranteed safe way to have binary backups. have a look at these, very interesting: http://forge.mysql.com/w/images/c/c1/MySQL_Backups_using_File_System_Snapshots-2009-02-26.pdf http://www.mysqlperformanceblog.com/2006/08/21/using-lvm-for-mysql-backup-and-replication-setup/ Cheers Claudio 2010/4/21 Johan De Meersman vegiv...@tuxera.be How does the below not guarantee me 100% that everything that can be consistent, is ? mysql flush tables with read lock; unixhost# sync unixhost# lvm create snapshot mysql unlock tables; I agree that there may be data inconsistencies within MyISAM, as it has no transactions, but there's also no guarantee that there isn't an application in the middle of multiple insert statements the moment you shut your database or your application. You can't magically get full consistency out of your database if your application hasn't been designed for it. Shutting systems down for backup may be fine for small setups, but on a server that hosts multiple critical applications, you just can't do that. You back up as consistently as you can without downtime, and pick the time of night with the lowest likelyhood of activity for it. On Wed, Apr 21, 2010 at 8:12 AM, Claudio Nanni claudio.na...@gmail.comwrote: Gavin, Right, that is also an option, but you are really not sure 100% that everything that is on memory is on the disk (buffers etc...) also if it is definitely good for a disaster recovery. What I meant is that the only way to have a 100% guaranteed consistent binary backup is when the database is shut down. Of course this is almost never an option, unless (tada) you have a slave dedicated for that. One remark on your note: Just a note though, I noticed someone added replication to a slave as a backup option. I really discourage that. Replication makes no guarantees that the data on your slave is the same as the data on your master. Unless you're also checking consistency, a slave should be treated as a somewhat unreliable copy of your data. While it is true that replication makes no guarantees, if your slave is not the same as the master and you rely on that for production, you have some problems, try to go to business and say, our slave (which at least 50% of our applications use to read data) is not really in sync, watch their facial expression! Believe me, in many production environments the method used for backups relies on the slave, not on the master. It is so much useful and important that you should have all your efforts go for having a consistent read-only slave 'dedicated' only for backups, no other client messing with it. Just my two cents Claudio Gavin Towey wrote: You can make binary backups from the master using filesystem snapshots. You only need to hold a global read lock for a split second. Regards, Gavin Towey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- Claudio
Re: better way to backup 50 Gig db?
On Wed, Apr 21, 2010 at 10:50 AM, Claudio Nanni claudio.na...@gmail.comwrote: Johan, Is the fact that InnoDB ignores the command FLUSH TABLES WITH READ LOCK; enough? :) InnoDB has buffers and activities going on even if you locked the tables and you are not sure that its buffers are on the disk when you snapshot. Again, you might be lucky and trust in the InnoDB recovery, what I state is that there is only one 100% guaranteed safe way to have binary backups. Aha, that's a tidbit I didn't know :-) It does hold back queries, but the engine doesn't stop it's maintenance threads. I tend to do a full dump with --single-transaction once a week, and copy the binlogs on the other days, though. That should suffice, I guess ? :-) -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
