Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to backup xtrabackup in mysql. Many people may not know much about it. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

Backup mode of mysql

1.mysqldump

The efficiency is low, the speed of backup and restore is slow, and any data insertion and update operation will be suspended.

2.mysqlhotcopy

Mysqlhotcopy is dedicated to backup myisam data tables. During the backup, any data insertion and update operations will be suspended.

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

4.xtrabackup is an open source project of percona, which can hot backup innodb, XtraDB,MyISAM (lock table)

Xtrabackup has two main tools: xtrabackup and innobackupex

Xtrabackup can only back up InnoDB and XtraDB data tables, but not MyISAM data tables

Innobackupex-1.5.1 encapsulates xtrabackup, which is a script encapsulation, so it can back up and process innodb and myisam at the same time, but a read lock is needed when processing myisam.

In comparison, 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 data table structure (.frm))

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

Restore (performed twice)

Xtrabackup-defaults-file=/etc/my.cnf-prepare-target-dir=/bak/

Xtrabackup-defaults-file=/etc/my.cnf-prepare-target-dir=/bak/

Second, incremental backup

It is more convenient to operate than innobackupex and xtrabackup,innobackupex, 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/

two。 Incremental backup

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

In the directory of incremental backups, the data files end in .delta. Incremental backups only back up page that has been modified since the last full backup, so incremental backups generally use less space temporarily.

3. Incremental backup recovery

Prepare the full backup and the incremental backup once respectively.

Xtrabackup-defaults-file=/etc/my.cnf-prepare-target-dir=/bak/

Xtrabackup-prepare-target-dir=/bak/-incremental-dir=/bak/increm/

The data files under / bak/ can be directly placed in your MySQL data directory to recover the data.

General backups, such as not changing to a brand new environment, just use xtrabackup.

Backup data to slave in non-stop production

First, make sure that datadir is specified in the / etc/my.cnf files 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

Transfer bak_mysql.tar.gz cp to another machine

Scp / bak/bak_mysql.tar.gz

Extract bak_mysql.tar.gz to / bak

Tar-ixvf mysqlbak.tar (be sure to add I parameter. I don't know why)

Restore first-- apply-log then-- copy-back

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

-- copy-back, it's easy to make a mistake here.

(innobackupex-1.5.1: Copying directory'/ bak/tech_soft_drp_ywmy'

Innobackupex-1.5.1: Copying directory'/ bak/mysql'

Mkdir: unable to create directory "arb/mysql/mysql": file already exists

Innobackupex-1.5.1: Error: Failed to create directory 'arb/mysql/mysql': there is no such file or directory at / usr/bin/innobackupex-1.5.1 line 336.)

I don't know what to do. Looks like the official 1bug. See you.

I looked at the / bak directory and decided to rm some of the libraries that come with mysql test and so on directly. Successful execution of the issued command

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

There is another problem with 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 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 d does not have the access rights to

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 happened. Online search seems to be a matter of authority. Restart the service of mysql directory chmod 777-R mysql directly.

Bodhisattva bless the question.

The configuration of my.cnf as server-id=2 master-slave configuration is not mentioned here.

CHANGE MASTER TO MASTER_HOST='xxxx',MASTER_USER='xx',MASTER_PASSWORD='xxx',MASTER_LOG_FILE='mysql-bin.000047',MASTER_LOG_POS=43446639

Go to the xtrabackup_binlog_info under / bak for the value of MASTER_LOG_FILE.

Save the previous overnight use of mysqldump to synchronize data and then do master-slave synchronization

After reading the above, do you have any further understanding of how to backup xtrabackup in mysql? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.