Xtrabackup introduction _ MySQL

Introduction to xtrabackup bitsCN.com

Mysql backup method


1. mysqldump
Low efficiency, slow backup and restoration, and any data insertion and update operations will be suspended

2. mysqlhotcopy
Mysqlhotcopy is used to back up the myisam data table. during the backup process, any data insertion and update operations will be suspended.

3. prepare a slave server for dedicated backup (master-slave mode)

4. xtrabackup is an open-source project of percona. it supports hot backup of innodb, XtraDB, and MyISAM (locks the table)

Xtrabackup has two main tools: xtrabackup and innobackupex.
Xtrabackup can only back up InnoDB and XtraDB data tables, but cannot back up MyISAM data tables
The innobackupex-1.5.1 encapsulates xtrabackup, which is a script encapsulation, so innodb and myisam can be backed up at the same time, but a read lock is required when processing myisam

In contrast, xtrabackup backup is more reliable.

Download xtrabackup-1.6-245.rhel5.x86_64.rpm
Install rpm-ivh xtrabackup-1.6-245.rhel5.x86_64.rpm

Backup (xtrabackup only backs up data files, not the data table structure (. frm ))
Xtrabackup -- defaults-file =/etc/my. cnf -- backup -- target-dir =/bak/
Restore (executed twice)
Xtrabackup -- defaults-file =/etc/my. cnf -- prepare -- target-dir =/bak/
Xtrabackup -- defaults-file =/etc/my. cnf -- prepare -- target-dir =/bak/

II. incremental backup

Compared with innobackupex and xtrabackup, innobackupex is easier to operate, but innobackupex has a temporary lock table. xtrabackup backup has another important feature: incremental backup.

1. Full Backup
Xtrabackup -- defaults-file =/etc/my. cnf -- backup -- target-dir =/bak/

2. incremental backup
Xtrabackup -- defaults-file =/etc/my. cnf -- backup -- target-dir =/bak/-- incremental-basedir =/bak/increm/

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 generally only uses less space for the time being.

3. incremental backup recovery
Perform the prepare operation on the full backup and incremental backup respectively.
Xtrabackup -- defaults-file =/etc/my. cnf -- prepare -- target-dir =/bak/
Xtrabackup -- prepare -- target-dir =/bak/-- incremental-dir =/bak/increm/

Data files under/bak/can be directly stored in your MySQL data directory to restore data.

For example, you can use xtrabackup instead of changing the environment.

Back up data to slave without stopping production

First, ensure that datadir is specified in the/etc/my. cnf file on both sides.

Then back up the data:
Innobackupex-1.5.1 -- defaults-file =/etc/my. cnf -- stream = tar -- user = root -- password = *** -- port = 3306 -- slave-info/bak | gzip>/bak/bak_mysql.tar.gz

Run bak_mysql.tar.gz cp on another machine.
Scp/bak/bak_mysql.tar.gz root @ host:/home/bak_mysql.tar.gz

Decompress bak_mysql.tar.gz to/bak
Tar-ixvf mysqlbak.tar (I do not know why I must be added)

Restore first -- apply-log and then -- copy-back
Innobackupex-1.5.1 -- defaults-file =/etc/my. cnf -- user = root -- password = XXXX -- port = 3306 -- apply-log/bak

-- Copy-back: errors are easily reported here.
(Innobackupex-1.5.1: Copying directory '/bak/tech_soft_drp_ywmy'
Innobackupex-1.5.1: Copying directory '/bak/mysql'
Mkdir: The Directory "arb/mysql" cannot be created. the file already exists.
Innobackupex-1.5.1: Error: Failed to create directory 'arb/mysql/mysql': No that file or directory at/usr/bin/innobackupex-1.5.1 line 336 .)
I don't know. Seems to be official 1 bug see https://bugs.launchpad.net/percona-xtrabackup/+bug/737569

I looked at the/bak directory and decided to directly rm some built-in libraries such as mysql test. Command executed successfully

Innobackupex-1.5.1 -- defaults-file =/etc/my. cnf -- user = root -- password = XXXX -- port = 3306 -- copy-back/bak

An error occurred while restarting the database service.
(110719 19:30:16 mysqld_safe Starting mysqld daemon with databases from arb/mysql
110719 19:30:16 [Note] Plugin 'Federated 'is disabled.
110719 19:30:16 InnoDB: The InnoDB memory heap is disabled
110719 19:30:16 InnoDB: Mutexes and rw_locks use GCC atomic builtins
110719 19:30:16 InnoDB: Compressed tables use zlib 1.2.3
110719 19:30:16 InnoDB: Using Linux native AIO
110719 19:30:16 InnoDB: Initializing buffer pool, size = 2.0G
110719 19:30:16 InnoDB: Completed initialization of buffer pool
110719 19:30:16 InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights
InnoDB: the directory.
InnoDB: File name./ib_logfile0
InnoDB: File operation call: 'open '.
InnoDB: Cannot continue operation.
110719 19:30:16 mysqld_safe mysqld from pid file arb/mysql/r410.pid ended)


I don't know what the situation is. The online search seems to be a permission issue. just restart the mysql directory chmod 777-R mysql.

Bodhisattva bless

Configure my. cnf to server-id = 2 master/slave configuration.
Change master to MASTER_HOST = 'xxxx', MASTER_USER = 'XX', MASTER_PASSWORD = 'XXX', MASTER_LOG_FILE = 'MySQL-bin.000047 ', MASTER_LOG_POS = 43446639

MASTER_LOG_FILE: find the value of xtrabackup_binlog_info under/bak.

Success

Saves the trouble of using mysqldump to synchronize data overnight and then perform master-slave synchronization.

This is an artifact.

BitsCN.com