Skip to main content

Home/ Larvata/ Group items tagged xtradb

Rss Feed Group items tagged

Filter: All | Bookmarks | Topics Simple Middle
張 旭

How Percona XtraBackup Works - 0 views

www.percona.com/...how_xtrabackup_works.html

mysql database backup percona xtradb

shared by 張 旭 on 30 Nov 19 - No Cached
  • Percona XtraBackup is based on InnoDB‘s crash-recovery functionality.
  • it performs crash recovery on the files to make them a consistent, usable database again
  • InnoDB maintains a redo log, also called the transaction log. This contains a record of every change to InnoDB data.
  • ...14 more annotations...
  • When InnoDB starts, it inspects the data files and the transaction log, and performs two steps. It applies committed transaction log entries to the data files, and it performs an undo operation on any transactions that modified data but did not commit.
  • Percona XtraBackup works by remembering the log sequence number (LSN) when it starts, and then copying away the data files.
  • Percona XtraBackup runs a background process that watches the transaction log files, and copies changes from it.
  • Percona XtraBackup needs to do this continually
  • Percona XtraBackup needs the transaction log records for every change to the data files since it began execution.
  • Percona XtraBackup uses Backup locks where available as a lightweight alternative to FLUSH TABLES WITH READ LOCK.
  • Locking is only done for MyISAM and other non-InnoDB tables after Percona XtraBackup finishes backing up all InnoDB/XtraDB data and logs.
  • xtrabackup tries to avoid backup locks and FLUSH TABLES WITH READ LOCK when the instance contains only InnoDB tables. In this case, xtrabackup obtains binary log coordinates from performance_schema.log_status
  • When backup locks are supported by the server, xtrabackup first copies InnoDB data, runs the LOCK TABLES FOR BACKUP and then copies the MyISAM tables.
  • the STDERR of xtrabackup is not written in any file. You will have to redirect it to a file, e.g., xtrabackup OPTIONS 2> backupout.log
  • During the prepare phase, Percona XtraBackup performs crash recovery against the copied data files, using the copied transaction log file. After this is done, the database is ready to restore and use.
  • the tools enable you to do operations such as streaming and incremental backups with various combinations of copying the data files, copying the log files, and applying the logs to the data.
  • To restore a backup with xtrabackup you can use the --copy-back or --move-back options.
  • you may have to change the files’ ownership to mysql before starting the database server, as they will be owned by the user who created the backup.
  • 張 旭 on 30 Nov 19
     
    "Percona XtraBackup is based on InnoDB's crash-recovery functionality."
張 旭

Load balancing with ProxySQL - 0 views

www.percona.com/...proxysql.html

proxysql mysql database HA

shared by 張 旭 on 07 Nov 19 - No Cached
  • accepts incoming traffic from MySQL clients and forwards it to backend MySQL servers.
張 旭

Connection and Privileges Needed - 0 views

www.percona.com/...privileges.html

mysql database backup percona xtradb

shared by 張 旭 on 02 Dec 19 - No Cached
  • Percona XtraBackup needs to be able to connect to the database server and perform operations on the server and the datadir when creating a backup, when preparing in some scenarios and when restoring it.
  • When xtrabackup is used, there are two actors involved: the user invoking the program - a system user - and the user performing action in the database server - a database user.
  • these are different users in different places, even though they may have the same username.
  • ...1 more annotation...
  • Once connected to the server, in order to perform a backup you will need READ and EXECUTE permissions at a filesystem level in the server’s datadir.
  • 張 旭 on 02 Dec 19
     
    "Percona XtraBackup needs to be able to connect to the database server and perform operations on the server and the datadir when creating a backup, when preparing in some scenarios and when restoring it. "
張 旭

The Backup Cycle - Full Backups - 0 views

www.percona.com/...full_backup.html

database mysql backup percona xtradb

shared by 張 旭 on 02 Dec 19 - No Cached
  • xtrabackup will not overwrite existing files, it will fail with operating system error 17, file exists.
  • Log copying thread checks the transactional log every second to see if there were any new log records written that need to be copied, but there is a chance that the log copying thread might not be able to keep up with the amount of writes that go to the transactional logs, and will hit an error when the log records are overwritten before they could be read.
  • It is safe to cancel at any time, because xtrabackup does not modify the database.
  • ...15 more annotations...
  • need to prepare it in order to restore it.
  • Data files are not point-in-time consistent until they are prepared, because they were copied at different times as the program ran, and they might have been changed while this was happening.
  • You can run the prepare operation on any machine; it does not need to be on the originating server or the server to which you intend to restore.
  • you simply run xtrabackup with the --prepare option and tell it which directory to prepare,
  • All following prepares will not change the already prepared data files
  • It is not recommended to interrupt xtrabackup process while preparing backup
  • Backup validity is not guaranteed if prepare process was interrupted.
  • If you intend the backup to be the basis for further incremental backups, you should use the --apply-log-only option when preparing the backup, or you will not be able to apply incremental backups to it.
  • Backup needs to be prepared before it can be restored.
  • xtrabackup --copy-back --target-dir=/data/backups/
  • The datadir must be empty before restoring the backup.
  • MySQL server needs to be shut down before restore is performed.
  • You cannot restore to a datadir of a running mysqld instance (except when importing a partial backup).
  • rsync -avrP /data/backup/ /var/lib/mysql/
  • chown -R mysql:mysql /var/lib/mysql
張 旭

Incremental Backup - 0 views

www.percona.com/...incremental_backup.html

mysql database backup percona xtradb

shared by 張 旭 on 02 Dec 19 - No Cached
  • xtrabackup supports incremental backups, which means that they can copy only the data that has changed since the last backup.
  • You can perform many incremental backups between each full backup, so you can set up a backup process such as a full backup once a week and an incremental backup every day, or full backups every day and incremental backups every hour.
  • each InnoDB page contains a log sequence number, or LSN. The LSN is the system version number for the entire database. Each page’s LSN shows how recently it was changed.
  • ...18 more annotations...
  • In full backups, two types of operations are performed to make the database consistent: committed transactions are replayed from the log file against the data files, and uncommitted transactions are rolled back.
  • You should use the --apply-log-only option to prevent the rollback phase.
  • An incremental backup copies each page whose LSN is newer than the previous incremental or full backup’s LSN.
  • Incremental backups do not actually compare the data files to the previous backup’s data files.
  • you can use --incremental-lsn to perform an incremental backup without even having the previous backup, if you know its LSN
  • Incremental backups simply read the pages and compare their LSN to the last backup’s LSN.
  • without a full backup to act as a base, the incremental backups are useless.
  • The xtrabackup binary writes a file called xtrabackup_checkpoints into the backup’s target directory. This file contains a line showing the to_lsn, which is the database’s LSN at the end of the backup.
  • from_lsn is the starting LSN of the backup and for incremental it has to be the same as to_lsn (if it is the last checkpoint) of the previous/base backup.
  • If you do not use the --apply-log-only option to prevent the rollback phase, then your incremental backups will be useless.
  • run --prepare as usual, but prevent the rollback phase
  • If you restore it and start MySQL, InnoDB will detect that the rollback phase was not performed, and it will do that in the background, as it usually does for a crash recovery upon start.
  • xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base \ --incremental-dir=/data/backups/inc1
  • The final data is in /data/backups/base, not in the incremental directory.
  • Do not run xtrabackup --prepare with the same incremental backup directory (the value of –incremental-dir) more than once.
  • xtrabackup --prepare --target-dir=/data/backups/base \ --incremental-dir=/data/backups/inc2
  • --apply-log-only should be used when merging all incrementals except the last one.
  • Even if the --apply-log-only was used on the last step, backup would still be consistent but in that case server would perform the rollback phase.
1 - 5 of 5
Showing 20▼ items per page