Xtrabackup and innobackupex Hot Backup MySQL Data

Xtrabackup and innobackupex Hot Backup MySQL Data
Xtrabackup and innobackupex Hot Backup MySQL Data 1. xtrabackup introduction and backup principles Description: Xtrabackup is an open-source software developed by percona. It can quickly back up and restore mysql Databases and supports online Hot Backup (data read/write is not affected during Backup ), this software is an open-source alternative to innodb Hot Backup Tool ibbackup. Xtrabackup contains two tools: 1. xtrabackup: used for hot backup of innodb and xtradb engine tables. Other tables cannot be backed up. 2. innobackupex: the perl script encapsulated in xtrabackup provides the capabilities for myisam (locking tables) and innodb engines, as well as hybrid engine backup. What can Xtrabackup do? 1. Online (hot) backup InnoDB of the entire database, XtraDB Table 2. Incremental Backup Based on the last full database backup of xtrabackup (innodb only) 3. A backup is generated in the form of a stream and can be saved directly to a remote machine (useful when the hard disk space of the local machine is insufficient). The tools provided by the MySQL database itself do not support real Incremental backup, binary Log Recovery refers to point-in-time recovery rather than Incremental backup. The Xtrabackup tool supports Incremental backup of the InnoDB Storage engine. The working principle is as follows: (1) complete a full backup and record the LSN (Log Sequence Number) of the checkpoint at this time ). (2) during Incremental backup, compare whether the LSN of each page in the tablespace is greater than the LSN of the last backup. If yes, back up the page and record the LSN of the current checkpoint. The implementation principle is briefly introduced on the Xtrabackup wiki: first, find and record the last checkpoint ("last checkpoint LSN") in the logfile "), start copying the InnoDB logfile to xtrabackup_logfile from the LSN. Then, Start copying all the data files. ibd; stops copying logfiles only after all data files are copied. Because all the data changes are recorded in the logfile, even if the data file has been modified during the backup process, the data consistency can still be maintained through parsing xtrabackup_logfile. Tip1: Xtrabackup is an open-source alternative to InnoDB Hot Backup, which is used to back up InnoDB/XtrDB. Tip2: When you use stream = tar for backup, your xtrabackup_logfile may be temporarily stored in the/tmp directory, if the number of concurrent writes during Backup is large, the xtrabackup_logfile may be large (5 GB +) and may be full of your/tmp directory, you can solve this problem by specifying the directory with the -- tmpdir parameter. Backup principle XtraBackup is based on InnoDB's crash-recovery function. It copies the data file of innodb. Because the table is not locked, the copied data is inconsistent, and crash-recovery is used during recovery to ensure data recovery is consistent. InnoDB maintains a redo log, also known as the transaction log, which contains all changes to innodb data. When InnoDB is started, it checks data file and transaction log first, and performs two steps: 1.It applies committed transaction log entries to the data files 2.it performs an undo operation on any transactions that modified data but did not commit. during backup, XtraBackup copies innodb data one page at a time without locking the table. At the same time, XtraBackup has another thread that monitors the transactions log. Once the log changes, copy the changed log pages. Why are we in a hurry to copy it? This problem was raised in the previous chapters. Because the transactions log file is limited in size, it will be written from the beginning after it is fully written, so the new data may overwrite the old data. During the prepare process, XtraBackup uses the copied transactions log to perform crash recovery ing on the backed-up innodb data file. The detailed File Permission xtrabackup opens the innodb data file in read-write mode, and then copies it. In fact, it will not modify this file. That is to say, the user running xtrabackup must have read and write permissions on the data files of innodb. Why the rw mode? Is the Direct read mode not good? Xtrabackup uses its built-in innodb library to open files, while the innodb library is rw when opening files. Tuning the OS Buffers because XtraBackup needs to copy a large amount of data from the file system, it tries its best to use posix_fadvise () to tell the OS not to cache the data read to improve performance. Because the data will not be reused, but the OS is not so smart. If you want to cache a few GB of data, it will put a lot of pressure on the virtual memory of the OS. other processes, such as mysqld, may be pushed out by swap, in this way, the system will be greatly affected. Posix_fadvise (file, POSIX_FADV_DONTNEED) and XtraBackup tries its best to pre-read the data when reading the data: posix_fadvise (file, POSIX_FADV_SEQUENTIAL, xtraBackup reads and writes 1 MB of data each time, and 1 MB/16 KB = 64 pages. This cannot be configured. After reading 1 MB of data, XtraBackup traverses the 1 mb data page by page. Use the buf_page_is_upted () function of innodb to check whether the data on this page is normal. If the data is abnormal, read this page again and read the page again for up to 10 times. If the page still fails, the backup will fail and exit. It skips this check on the doublewrite buffer ?? Each time a transactions log is copied, KB of data is read and written. It cannot be configured. II. download and install configuration 1. xtrabackup binary 64-bit: http://www.percona.com/downloads/XtraBackup/XtraBackup-1.6/Linux/binary/x86_64/ 2. mySQL5.1.56: http://dev.mysql.com/downloads/mysql/5.1.html#downloadsoperating system environment: centos release 5.4 (Final) x86_64 GNU/Linux 2.6.18-164. el5 2. installation configuration (1) Installation configuration mysql-5.1.56 in MySQL versions earlier than 5.1.38, when you need to install InnoDB Plugin, you must download the Plugin file, unzip and then perform a series of installation. Since MySQL 5.1.38, MySQL has two different versions of the InnoDB Storage engine-one is the old version of the engine, called build-in innodb; the other is the InnoDB Storage engine 1.0.4. Shell> tar-zxvvf mysql-5.1.56.tar.gzShell> cd mysql-5.1.56Shell>. /configure -- prefix =/opt/mysql5156 \ -- with-charset = utf8 -- with-collation = utf8_general_ci \ -- with-extra-charsets = latin1, gb2312 \ -- with-plugins = innobase, innodb_plugin, myisam, heap, csv, federated, blackhole \ -- enable-local-infile -- enable-thread-safe-clientShell> makeShell> make installShell> cp support-files/my-medium.cnf/etc/my. cnfShel L> cd/opt/mysql5156Shell> chown-R mysql. shell> chgrp-R mysql. shell> bin/mysql_install_db-user = mysqlShell> chown-R root. shell> chown-R mysql var configuration start plugin-innodbShell> vi/etc/my. cnfport = 3307 socket =/tmp/mysql5156.sock [mysqld] port = 3307 socket =/tmp/mysql5156.sockignore _ builtin_innodb start: shell>/opt/mysql5156/bin/mysqld_safe-user = mysql & Load plugin-innodb: mysql> install plugin innodb soname' h Comment '; mysql> install plugin INNODB_TRX SONAME 'Ha _ innodb_plugin.so'; mysql> install plugin INNODB_LOCKS SONAME 'Ha _ comment '; mysql> install plugin INNODB_LOCK_WAITS SONAME 'Ha _ comment '; mysql> install plugin INNODB_CMP SONAME 'Ha _ innodb_plugin.so '; mysql> install plugin INNODB_CMP_RESET SONAME 'Ha _ innodb_plugin.so'; mysql> install plugin INNODB_CMPMEM SONAME 'Ha _ innodb_p Lugin. so'; mysql> install plugin INNODB_CMPMEM_RESET SONAME 'Ha _ innodb_plugin.so '; The preceding statement only needs to be executed once. Even if mysqld is restarted, it does not need to be installed again. Mysql> show plugins; mysql> select @ innodb_version; + ------------------ + | @ innodb_version | + -------------------- + | 1.0.15 | + ---------------- + 1 row in set (0.00 sec) mysql executes install plugin innodb soname 'Ha _ innodb_plugin.so '. If The ERROR message is: ERROR 1289 (HY000): The 'plugin' feature is disabled; you need MySQL built with 'have _ DLOPEN 'to HAVE it working to find a solution via Internet search: Do not use static compilation during compilation and delete the following two from the compilation parameters: -- with-client-l Dflags =-all-static -- with-mysqld-ldflags =-all-static. The reason is not clear. (2) install and configure Xtrabackup. First, check how to install Xtrabackup. The simplest installation method is to use the RPM package. However, if you want to use the source code for installation, the installation method is a bit odd, it is installed by patching the MySQL source code. Here we use the Binary Package installation method, which is relatively flexible. Shell> mkdir/usr/local/xtrabackupShell> tar-zxvf xtrabackup-1.6.tar.gz-C/usr/local/xtrabackupShell> cd/usr/local/xtrabackup/binShell> ln-s innobackupex-1.5.1 innobackupex configuration Environment variable: shell> export PATH = $ PATH:/usr/local/xtrabackup/bin modify mysql configuration file: Shell> vi/etc/my. cnf addition or modification: datadir =/usr/local/mysql/var (Database directory) Special Note: default_table_type = InnoDB (must be changed; otherwise, it will not succeed during Incremental Backup) III. xtrabackup backup and recovery use Parameter options about xtrabackup, As follows: -- defaults-file = # path of the default configuration file. If this parameter is set, xtrabackup searches for the configuration file from the following locations in sequence:/etc/my. cnf/etc/mysql/my. cnf/usr/local/etc/my. cnf ~ /. My. cnf and read the [mysqld] and [xtrabackup] configuration sections in the configuration file -- defaults-extra-file = # If this parameter is used, after reading the global configuration file, read the specified configuration file -- target-dir = name # backup file storage directory path -- backup # Back up to target-dir -- prepare # restore the backup file preparations before (generate InnoDB log file) -- print-param # print the parameter required for backup or recovery -- use-memory = # this parameter is used in prepare, control the amount of memory used by the innodb instance during prepare-suspend-at-end # generate an xtrabackup_susponded file in the target-dir directory and suspend the xtrabackup process, synchronize changes to data files to backup files until you manually delete xtrabackup_suincluded File -- throttle = # The number of I/O operations per second, which limits the I/O operations used during backup to minimize the impact of backup on normal business of the database -- log-stream this parameter is used during backup, output the content of xtrabackup_logfile to the standard output. When this parameter is used, the -- suspend-at-end parameter is automatically used. This parameter is used in the -- stream mode of the innobackupex script. -- Incremental-lsn = name # When performing incremental backup, only copy the ibd pages with a new value specified by LSN. The LSN used for the previous backup can be used to check the xtrabackup_checkpoints file of the previous backup set. -- Incremental-basedir = name # this parameter is used during backup, backup is the new idb pages -- incremental-dir = name # generated when prepare is specified in the backup set specified by this parameter. storage path of delta files and log files -- tables = name # used when backing up file-per-table data files, use the regular expression to specify the innodb table to be backed up -- datadir = name # MySQL database data file directory. Xtrabackup only backs up InnoDB data files, and the table structure is not backed up. Therefore, you must have the corresponding table structure file (. frm) 1. normal backup (full backup) shell> mkdir-p/backup/xtrabackupshell> xtrabackup -- defaults-file =/etc/my. cnf -- backup -- target-dir =/backup/xtrabackup full backup simulated recovery: xtrabackup -- prepareshell> xtrabackup -- defaults-file =/etc/my. cnf -- prepare -- target-dir =/backup/xtrabackupshell> xtrabackup -- defaults-file =/etc/my. cnf -- prepare -- target-dir =/backup/xtrabackup delete a database Data files in the directory: shell> rm-rf/usr/local/mysql/var/ib * Shell> cp/backup/xtrabackup/ib */usr/local/mysql/varShell> chown-R mysql. root/usr/local/mysql/var restart to test whether the recovery is successful. Note that xtrabackup only backs up data files and does not back up the data table structure (. frm). Therefore, when using xtrabackup for restoration, you must have a table structure file (. frm ). 2. Advantages of normal backup (Incremental Backup) Incremental Backup: 1. The database is too large to have enough space for full backup, which effectively saves space and improves efficiency. 2. Supports hot backup. The table is not locked during the backup process and is not subject to time restrictions. 3. Daily backup only generates a small amount of data, making remote backup and transmission easier. Saves space at the same time. 4. Backup recovery is based on file operations to reduce the risks of direct database operations. 5. Higher backup efficiency and higher recovery efficiency. Incremental backup and simulated recovery steps: shell> mkdir-p/backup/xtrabackup/baseshell> mkdir-p/backup/xtrabackup/delta (1) perform a full backup first, because the subsequent Incremental backup should be based on this shell> xtrabackup -- defaults-file =/etc/my. cnf -- backup -- target-dir =/backup/xtrabackup/baseshell> ls/backup/xtrabackup/baseibdata1 xtrabackup_checkpoints xtrabackup_logfile (2) use this full backup as the basis for Incremental Backup Shell> xtrabackup -- defaults =/etc/my. cnf -- backup -- target-dir =/backup/xtrabackup/delta/-- inc Remental-basedir =/backup/xtrabackup/base/shell> ls/backup/xtrabackup/deltaibdata1.delta ready xtrabackup_logfile ps: In the Incremental backup Directory, all data files end with. delta. Incremental Backup only backs up the page modified after the last full backup. Therefore, Incremental Backup only uses less space. Incremental Backup can be incremental Based on Incremental backup. (Note:/backup/xtrabackup/delta/must be modified each time. For example, if the second increment is changed to/backup/xtrabackup/delta2, you can write a script to perform automatic backup.) The Incremental backup simulates the recovery process: we need to perform prepare operations on both full and incremental backups. Shell> xtrabackup -- defaults-file =/etc/my. cnf -- prepare -- target-dir =/backup/xtrabackup/base/shell> xtrabackup -- defaults-file =/etc/my. cnf -- prepare -- target-dir =/backup/xtrabackup/base/-- incremental-dir =/backup/xtrabackup/delta/shell> xtrabackup -- defaults-file =/etc/my. cnf -- prepare -- target-dir =/backup/xtrabackup/base/shell> rm-rf/usr/local/mysql/var/ib * # delete data files in the original data directory shell> cp/backup/xtrabackup/B Ase/ib */usr/local/mysql/varShell> chown-R mysql. root/usr/local/mysql/var restart to test whether the restart is successful. Remind you again that xtrabackup only backs up InnoDB data files, and the table structure is not backed up. Therefore, you must have the corresponding table structure file (. frm) when restoring ). 3. Compared with innobackupex and xtrabackup, we can see that innobackupex is easier to operate, but innobackupex has a temporary lock table (the time depends on the MyISAM size ). Xtrabackup backup has another important feature: Incremental backup. (In future versions, innobackupex may also increase this function.) For example, if the table currently contains 10 data records, back up the data first. back up to the/usr/local/backup/1/directory (full backup) shell> xtrabackup -- defaults-file =/etc/my. cnf -- backup -- target-dir =/usr/local/backup/1/view its lsn: shell> cat xtrabackup_checkpoints backup_type = full-backupedfrom_lsn = 0: 0to_lsn = 0: 592411 insert data into the table and perform Incremental Backup shell> xtrabackup -- defaults-file =/etc/my. cnf -- backup -- target-dir =/usr/local/backup/2/-- incremental-basedir =/usr/local/ Backup/1/Incremental backup to 2 (Incremental backup from 1 to 2) view its lsn: shell> cat xtrabackup_checkpoints backup_type = incrementalfrom_lsn = 0: 592411to_lsn = 0: 908563 insert data into the table and perform Incremental Backup shell> xtrabackup -- defauls-file =/etc/my. cnf -- backup -- target-dir =/usr/local/backup/3/-- incremental-basedir =/usr/local/backup/1/incremental backup to 3 (1 to 3) incremental backup, 2. the backup has been included. For this method of backup, you only need to restore the full backup and the last Incremental backup.) view its lsn: shell> cat xtrabackup_checkpoints backup_type = in Crementalfrom_lsn = 0: 592411to_lsn = 0: 1507854 or another Incremental backup method: shell> xtrabackup -- defauls-file =/etc/my. cnf -- backup -- target-dir =/usr/local/backup/3/-- incremental-basedir =/usr/local/backup/2/incremental backup to 3 (2 to 3 incremental backup, each Incremental backup is independent. In this way, the final restoration requires a full backup and each Incremental backup.) check its lsn: shell> cat xtrabackup_checkpoints backup_type = incrementalfrom_lsn = 0: 908563to_lsn = 0: 1507854 Incremental Backup simulated recovery: we need to perform prepare operations on both full and Incremental backup. ①: Based on (Incremental backup from 1 to 3, the backup from 2 is included. If you follow this method to back up, the final restoration only requires the restoration of the full backup and the last Incremental backup. The 1st full recovery shell> xtrabackup -- defaults-file =/etc/my. cnf -- prepare -- target-dir =/usr/local/backup/1/continue to restore the Incremental backup to shell> xtrabackup -- prepare -- target-dir =/usr/local/backup/ 1/-- incremental-dir =/usr/local/backup/3/shell> xtrabackup -- prepare -- target-dir =/usr/local/backup/1/after recovery is complete, cd/ usr/local/backup/1/cp 1/ib */usr/local/mysql/var/chown -R mysql. root/usr/local/mysql/var/etc/init. d/mysql restart ② Based on (2-3 Incremental backup, each Incremental backup is independent, according to this method, the final restoration needs to restore the full backup and each Incremental Backup) restore the complete xtrabackup of 1st times -- defaults-file =/etc/my. cnf -- prepare -- target-dir =/usr/local/backup/1/continue to restore the Incremental backup to shell> xtrabackup -- prepare -- target-dir =/usr/local/backup/ 1/-- incremental-dir =/usr/local/backup/2/continue to restore the incremental backup to shell> xtrabackup -- prepare -- target-dir =/usr/local/backup/1 /-- incremen Tal-dir =/usr/local/backup/3/recovery complete followed by cd/usr/local/backup/1/cp 1/ib */usr/local/mysql/var/ chown-R mysql. root/usr/local/mysql/var/etc/init. d/mysql restart 4. innobackupex backup and recovery Usage: innobackup [-- sleep = MS] [-- compress [= LEVEL] [-- include = REGEXP] [-- user = NAME] [-- password = WORD] [-- port = PORT] [-- socket = SOCKET] [-- no-timestamp] [-- ibbackup = IBBACKUP-BINARY] [-- slave-info] [-- stream = tar] [-- defaults-file = MY. CNF] [-- databases = LIST] [-- remote-host = HOSTNAME] BACKUP-ROOT-DIRinnobackup -- apply-log [-- use-memory = MB] [-- uncompress] [-- defaults -file = MY. CNF] [-- ibbackup = IBBACKUP-BINARY] BACKUP-DIRinnobackup -- copy-back [-- defaults-file = MY. CNF] The first command line for the BACKUP-DIR is a hot backup mysql database. The command with the -- apply-log option is to start the mysql service on a backup. Commands with the -- copy-back option copy data, indexes, and logs from the backup directory to the specified initial location in the my. cnf file. Parameter annotation: -- defaults-file = # Same as the -- defaults-file parameter of xtrabackup; -- apply-log # encapsulation of the -- prepare parameter of xtrabackup; -- copy-back # copy the backup data file to the datadir of the MySQL server during data recovery; -- remote-host = HOSTNAME # store the backup data to the process server through ssh; -- stream = [tar] # backup file output format. When tar is used, tar4ibd is used. This file can be obtained from the XtarBackup binary file. If -- stream = tar is specified during backup, the Directory of the tar4ibd file must be in $ PATH (because tar4ibd is used for compression, this file can be obtained in the binary package of XtraBackup ); -- tmpdir = DIRECTORY # When -- remote-host or -- stream is specified, the temporary DIRECTORY for storing transaction logs is tmpdir by default; -- redo-only -- apply-log group, # only redo is performed when logs are forcibly backed up, and rollback is skipped. This is necessary for Incremental backup; -- use-memory = # this parameter is used when prepare is used to control the memory used by the innodb instance during prepare; -- throttle = IO # Same as the -- throttle parameter of xtrabackup; -- sleep = # is used by ibbackup to specify the number of milliseconds to stop copying each 1 MB of data, to minimize the impact on normal services during backup, you can view the ibbackup manual. -- compress [= LEVEL] # compresses the backup data row and only supports ibbackup, xtrabackup is not implemented yet; -- include = REGEXP # encapsulation of the xtrabackup parameter -- tables, ibbackup is also supported; -- databases = LIST # LIST the databases to be backed up. If this parameter is not specified, all databases that contain MyISAM and InnoDB tables are backed up. -- uncompress # decompress the backup data file. And supports ibbackup. xtrabackup has not yet implemented this function. -- slave-info # backup slave database, and an xtrabackup_slave_info file will be generated under the -- slave-info Backup Directory, the main log file and offset will be saved here. The file content is similar TO: change master to MASTER_LOG_FILE = '', MASTER_LOG_POS = 0 -- socket = SOCKET # MySQL server's socket File Location 1. Use Innobachkupex for backup (Incremental backup is not supported currently) (1) normal backup shell> mkdir-p/backup/innobackupex/Shell> innobackupex -- defaults-file =/etc/my. cnf -- user = root -- password = root \ -- databases = 'I _ db' -- no-lock/backu P/innobackupex/\ 2>/tmp/innobackup. log xtrabackup_51 Ver 1.5 Rev undefined for 5.1.52 unknown-linux-gnu (x86_64) [01] Copying/usr/local/mysql/var/ibdata1 to/backup/innobackupex/2011-04-12_00-44-29/ibdata1 [01]... donextrabackup: The latest check point (for incremental): '0: 32538662 'xtrabackup: Stopping log copying thread. xtrabackup: Transaction log of lsn (0 32538662) to (0 32538662) was copied. Note: the root user of mysql is used for backup. The password is/backup/innobackupex /, this directory will create a backup directory named by the timestamp -- no-lock backup will not lock the table -- databases = "I _db" is used to specify the database to be backed up 2>/tmp/backup. log stores the output information in the backup process to/tmp/backup. log Note: When innobackupex is used for backup. specify the mysql data file directory in cnf. Otherwise, an error is reported. [Mysqld] datadir =/usr/local/mysql/var (2) packaging (tar) backup Shell> innobackupex -- defaults-file =/etc/my. cnf -- user = root -- password = root \ -- databases = 'I _ db' -- no-lock -- stream = tar/backup/innobackupex/\ 2>/tmp/innobackup. log 1>/backup/innobackupex/I _aura.tar backup takes a long time, mainly for packaging. To decompress the package, use tar ixvf to decompress the corresponding file. Note that parameter-I must be added. Simulate the recovery process: shell> mysql-uroot-proot-e 'drop database I _db 'Shell> mysqladmin-uroot-proot shutdownShell> rm-rf/usr/local/mysql/var/ib * shell> cd /backup/innobackupex/shell> tar ixvf I _aura.tarshell> lsbackup-my.cnf I _aura.tar ibdata1 I _db stderr stdout xtrabackup_binary restore prepare restore shell> innobackupex-1.5.1 -- defaults-file =/etc/my. cnf -- no-lock -- apply-log/data/back _ Data/copy and restore data: shell> innobackupex -- defaults-file =/etc/my. cnf-no-lock -- copy-back/backup/innobackupex/Shell> cd/usr/local/mysql/varShell> chown-R mysql: root. # Be sure to modify the permission shell> mysqld_safe-user = mysql & check the deleted database. If it is restored, the restoration process is correct. (3) compression (tar) backup Shell> innobackupex -- defaults-file =/etc/my. cnf -- user = root -- password = root \ -- databases = "I _db test" -- no-lock -- stream = tar/backup/innobackupex/\ 2>/tmp/innobackup. log | gzip>/backup/innobackupex/I _aura.tar backup takes a long time, mainly for compression. To decompress the package, use tar izxvf to decompress the corresponding file. Note that parameter-I must be added. Restore: After extracting the corresponding file using tar-izxvf, the operation is exactly the same as the normal backup. Simulate the recovery process: shell> mysql-uroot-proot-e 'drop database I _db 'Shell> mysqladmin-uroot-proot shutdownShell> rm-rf/usr/local/mysql/var/ibdata1 ib_logfile * shell> cd/backup/innobackupex/shell> tar ixvf I _aura.tarshell> lsbackup-my.cnf I _aura.tar.gz ibdata1 I _db stderr stdout xtrabackup_binary xtrabackup_checkpoints xtrabackup_logfile ready for restoration: shell> innobackupex -- defaults-file =/etc/my. cnf-no-lock -- apply-log /Backup/innobackupex /...... Copy and restore Data: shell> innobackupex -- defaults-file =/etc/my. cnf-no-lock -- copy-back/backup/innobackupex/Shell> cd/usr/local/mysql/varShell> chown-R mysql: root. shell> mysqld_safe-user = mysql & check the deleted database. If yes, the restoration process is correct. (4) full-database backup and recovery. Backup: Shell> innobackupex -- user = root -- password = root -- defaults-file =/etc/my. cnf \/backup/innobackupex/check the backup file: Shell> ls/backup/innobackupex/2011-04-12_09-20-13 simulated recovery: Stop the database and delete all database files under the database directory. Shell> mysql-uroot-proot shutdownShell> rm-rf/usr/local/mysql/var/* first write the log file and restore it, as shown below. (Note: 2011-04-12_09-20-13 is an automatically generated folder named after the backup time. Select the folder based on the situation when restoring) Shell> innobackupex -- apply-log -- defaults-file =/etc/my. cnf \/backup/innobackupex/2011-04-12_09-20-13Shell> innobackupex -- copy-back -- defaults-file =/etc/my. cnf \/backup/innobackupex/2011-04-12_09-20-13 run the cd or ls command to check whether there are files in your database directory? Modify database file permissions. Otherwise, the database cannot be started. (This step is taken from cd to the original database directory.) Shell> chown-R mysql: mysql. Restart the database to test whether the restoration is successful. 5. Back up data to the remote server (1) innobackupex remote backup method 1 IP address of the current database machine: 192.168.1.131. Back up data to the/data Directory of 192.168.1.132. Shell> innobackupex -- user = root -- password = root -- defaults =/etc/my. cnf -- stream = tar/data | ssh root@192.168.1.132 cat ">"/data/backup.tar then enter the remote server password ...... then the backup starts. After the backup is complete, log on to the remote server to see if there is any? Currently, remote backup is not supported when xtrabackup is used. How can this problem be solved? You can use the nfs Network File System to mount a remote server disk to a local disk. (2) innobackupex remote backup method 2 # enable the 1234 listener on the local background, to accept the backup file nc-d-l 1234>/data/$ A/20100118000001.tar & # ssh to server A for backup, and push the backup package to backup server B. Record logs at the same time. Ssh $ A 'innobackupex-1.5.1 -- stream = tar -- include = 'renren. * '-- throttle = 500 -- socket =/data/mysql/backup/mysql. sock -- defaults-file =/data/dbbackup/my. cnf -- user = root -- password = xxxxxxx -- slave-info/data/dbbackup | nc $ B 1234 '</dev/null>/data/xtrabackup. log 2> & 1 parameter explanation: -- stream = tar: tells xtrabackup that the backup must be output as a tar file stream. -- Include = 'renren. * ': Back up the database tables that are included in the renren database. For example, to back up all the tables in the renren database. This parameter is omitted if full backup is required. To back up two tables under the renren Database: tableA & tableB, write it as -- include = 'renren. tableA | renren. tableb'; if the renren database contains only two tables with the table prefix, you can also write it as: -- include = 'renren. table *'. -- Throttle = 500: xtrabackup has many IO operations during the backup process. Therefore, I/O operations must be limited. To avoid excessive server pressure, it seems that the role is not very obvious, and you do not know whether the setting is too large or not. To be tested... -- Socket =/data/mysql/backup/mysql. sock: Specify mysql. the location of the sock so that the backup process can log on to mysql. -- defaults-file =/data/dbbackup/my. cnf: If your/etc/my. cnf does not use a segment such as [mysqld3306] To start mysql. This line of parameters is not required. If the above segment is used, xtrabackup cannot be parsed./etc/my. the cnf file (which will be parsed incorrectly strictly), so we need to manually complete a my. cnf file, the File Fragment is as follows. You only need to specify six parameters to make xtrabackup work normally. For the meanings of each parameter, see the mysql documentation: [mysqld] datadir =/data/varinnodb_data_home_dir =/data/ibdatainnodb_data_file_path = ibdata1: 10 M; ibdata2: 10 M: bytes =/data/bytes = 2innodb_log_file_size = 1G