mysql xtrabackup

Mysqldump Backup principleThe basic process for backup is as follows: FLUSH TABLES功能：关闭实例上所有打开表目的：为第二步prepare，为了避免较长的事务操作造成FLUSH TABLES WITH READ LOCK操作迟迟得不到锁，但同时又阻塞了其它客户端操作 FLUSH TABLES with READ LOCK功能：加全局读锁目的：获得DB一致性状态 SET SESSION TRANSACTION Isolation level repeatable READ功能：设置当前会话的事务隔离等级为RR，RR可避免不可重复读和幻读目的：确保在备份事务中任何时刻数据都相同 START TRANSACTION with consistent SNAPSHOT功能：获取当前数据库的快照，这个是由mysqldump中--single-transaction决定的目的： 简而言之，就是开启事务并对所有表执行了一次SELECT操作，这样可保证备份时，在任意时间点执行sele

A simple introduction to the principle of implementation on Xtrabackup's wiki: First, the final checkpoint ("last checkpoint LSN") is found and recorded in the logfile, and then the logfile to Xtrabackup_logfile from the location of the LSN is started to copy InnoDB; Start copying all the data files. ibd; The copy logfile is not stopped until all data files have been copied. Because the logfile inside records all the data modification situation, therefore, immediately in the backup process the d

One. Installation1. Configure the Yum sourceRPM-UHV http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm2. InstallationYum Install Xtrabackup-ySecond, modify the MySQL master configuration file my.cnf (set according to the parameters of the compilation installation, RPM installation can be omitted)[mysqld]datadir=/data/mysqldbbasedir=/usr/local/

directory for the incremental backupPartial backup:--include: Use regular expressions to back up the matches to, for example: Back up the user table in the MySQL library, --include= ' ^mysql[. User '--table-files: If you are backing up more than one table, you can write multiple tables to a file in the form "database name. Table name" and specify this file when backing up--databases: You can back up the en

#!/bin/sh###################[emailprotected]################### #xtrabackup2 .1.8# Use method:# ./restore.sh/incremental Backup Parent directory or fully-prepared parent directory name #eg1:./restore.sh/data/backup/full/#eg2:./restore.sh/data/backup/incre/# NOTICE: Incremental Backup restore If you specify a file, the end of the previous file to the specified file name is restored. #即输入的增量文件名应为需要截止的增量文件名的下一个文件 #NOTE: Make sure that the MySQL service

#backup .sh#!/bin/sh#onxtrabackup2.2.8# It checks for a full backup the first time it executes, otherwise it creates a full-library backup # when you run it again, It is based on the settings in the script to make incremental backups on the basis of the previous full or incremental backup #[emailprotected]innobackupex_path=innobackupex# Innobackupex's command innobackupexfull=/usr/local/xtrabackup/bin/$INNOBACKUPEX _path #INNOBACKUPEX的命令路径 #

Tags: xtrabackup mysql5.6PerconaIbbackup:innodb Online Physical backupFullIncrementalMyisam:warm Backup,fullPercona:xtrabackup1. IntroductionXtrabackup is a MySQL data backup tool provided by Percona, officially described, which is the only open source tool in the world capable of hot-provisioning of InnoDB and XTRADB (enhanced InnoDB) databases, featuring:(1) The backup process is fast and reliable;(2) The

Tags: xtrabackup innodb myisamthe InnoDB engine database can be backed up using mysqldump, and if the table is large dozens of g or even hundreds of G, the display with mysqldump backup will be very slow. Then use Xtrabackup to quickly back up the InnoDB database online. InnoDB has a commercial InnoDB hotbackup, which enables online hot-provisioning of the InnoDB engine's tables. and Percona produced

192.168.31.64:Innobackupex--defaults-file=/data/mysql/3306/etc/my.cnf--user=root--compress--stream=xbstream./| SSH [email protected] "Xbstream-x-c/data/mysql/backup/"192.168.31.65:wget Http://www.quicklz.com/qpress-11-linux-x64.tarTAR-XF Qpress-11-linux-x64.tarLn-s qpress/bin/qpresscd/data/mysql/backup/For f in ' Find/-iname ' *\.QP '; DO/BIN/QPRESS-DT2 $f $ (dir

Logical backupA logical backup is a backup SQL statement that performs a backup of the SQL statement to reproduce the database data at the time of recovery.Tool: MysqldumpCharacteristics:1. Strong portability2. Backup and recovery takes a long time and does not apply to large business systemsPhysical backupPhysical backup is the backup data file, the comparison image Point is the CP under the data file, but the real backup time is not naturally the CP so simple.Tool: XtrabackupCharacteristics:1.

BINLOG for BACKUPRole: Gets the consistency site.1. Disable the operation of the site updateOptimization points:1. Allow DDL and updates until Binlog is written.UNLOCK BINLOGFTWRL (flush tables with read lock)Regardless of the backup tool, the FTWRL is strongly dependent in order to obtain consistency sites. This lock is very lethal, because the whole database is essentially unable to provide write service to the time the lock is held.Because FTWRL needs to close the table, if there is a large

Tags: mysql backupI believe that many of the small partners will use the MySQL database, the following provides a script to share with gentlemen.#!/bin/bashscript_dir=$ (dirname)Backup_dir= "/data1/mysqlbackup"backup_days=480RM-RF ${backup_dir}/*db_ip= '/sbin/ifconfig-a|grep inet|grep-v 127.0.0.1|grep-v Inet6|awk ' {print $} ' |tr-d ' addr: ' |head-1 'Backup_type= ' Xtra_full 'For i in ' Ps-ef |grep port= |

First, installation Download https://www.percona.com/downloads/XtraBackup/First download the percona-xtrabackup-24-2.4.4-1.el5.x86_64.rpm, installation report libcurl.so.3 and libev.so.4 can't find, libev.so.3 can pass yum install Libev solve, But libcurl.so.3 by making a soft link (cd/usr/lib64 ln-s libcurl.so.4 libcurl.so.3) or copying a copy from another system cannot be resolved, give up Downlo

My source[Email protected]:/tmp/percona-xtrabackup_debian8# cat/etc/apt/sources.listdeb Http://mirrors.ustc.edu.cn/debian Jessie Main contrib non-freedeb-src Http://mirrors.ustc.edu.cn/debian Jessie Main contrib non-freedeb/HTTP/Mirrors.ustc.edu.cn/debian jessie-proposed-updates main contrib non-freedeb-src Http://mirrors.ustc.edu.cn/debianJessie-proposed-updates Main contrib non-freedeb Http://mirrors.ustc.edu.cn/debian jessie-updates main contrib NON-FREEDEB-SRC Http://mirrors.ustc.edu.cn/debi

Xtrabackup One-click Automated backup Script V1 "original"Xtrabackup Backup tool installation and usehttp://990487026.blog.51cto.com/10133282/1697094Information to fill inBackup file path Backdir=/asset.innobackupexmysql used to back up the user User=rootmysql user's password pass=asset output information save path logfile=/var/www/htm L/index.htmlL[[Emailprotected]~]#viminnobackupex.v1.sh[[emailprotected]~

Configure the Percona Yum repository under centos5.6 and install the Xtrabackup toolFirst, install the dependencies:Yum Install PERL-DBIYum Install Perl-dbd-mysqlYum Install Perl-time-hiresYum Install Perl-io-socket-sslSecond, configure the Yum source,Method 1, automatically install Percona's Yum warehouse (the following are x86_64 and i386 platforms respectively)#rpm-IVH http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rp

The previous blog said how to use Xtrabackup to back up and restore databases. After the database has been restored, the two machine databases are the same, including Binlog file and Pos.At the same time, after the Xtrabackup backup is completed, the relevant Binlog file and POS information has been written to the backup information file, and the cat can view it under Linux. Then execute the Change Master

Because Mysqldump is a logical backup, and backups are inefficient, backups cannot be made quickly when there is a large amount of dataUsing Xtrabackup is an excellent backup solution at this time.1. Full backup and full restoreBackup:--backupInnobackupex--defaults-file=/etc/my.cnf.d/server.cnf--host=127.0.0.1--user=liuwei--password=123456--backup/ tmp/backup/Option Description:--defaults-file: Indicates the server's configuration file, this parameter