Mysql instance backup strategy

System Centos6.0

First, the following Backup terms are popularized:

• Hot Backup: Read and Write are not affected;

• Warm backup: only read operations can be performed;

• Cold backup: offline backup; read and write operations are suspended;

We usually back up the following parts:

1. Data Files

2. log files such as transaction logs and binary logs)

3. Stored Procedures, stored functions, and triggers

4. The configuration file is very important, and each configuration file must be backed up)

I. Common backup tools

1. Mysql built-in backup tools

• The mysqldump logical backup tool supports all engines. The MyISAM engine is a warm backup, and the InnoDB engine is a hot backup. The backup speed is medium and the restoration speed is very slow. However, when restoring data, there is a lot of room for operation. Good elasticity.

• Mysqlhotcopy physical backup tool, but only supports the MyISAM engine, basically belongs to the category of cold backup, physical backup, fast.

2. File System Backup tools

• Cp cold backup supports all engines and copy commands, which can only be used for cold backup and physical backup. Archive tools and cp commands are used to back up the data. The backup speed is fast and the restoration speed is almost the fastest. However, the flexibility is very low, and the data can be cross-system, but the cross-platform capability is poor.

• Lvm is almost a hot backup. It supports all engines and physical backup Based on snapshots (LVM, ZFS). It is very fast and almost always hot backup. It takes only a few seconds for the data to be affected. However, the snapshot creation process affects online database usage. Therefore, the backup speed is faster and the recovery speed is faster. There is no elastic space, and the LVM restrictions are as follows: multiple logical volumes cannot be backed up at the same time. Therefore, data files, transaction logs, and other files must be stored on the same LVM. ZFS can be backed up between multiple logical volumes.

3. Other Tools

• Xtrabackup open-source tool MyISAM is a warm backup, InnoDB is a hot backup, and is an alternative tool for ibbackup commercial tools

Ii. Back up an instance:

This article has made a lvm partition: mysql5.6 compilation and installation is complete;

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/13013V626-0.jpg "title =" 1.jpg"/>

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/13013V0R-1.jpg "title =" 1.jpg"/>

Mysql datadir =/usr/data, which is the place where we mount the lvm.

There are two tables in the mysql test database; t1 is about 388M; t2 is about 210M;

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/13013V603-2.jpg "title =" 1.jpg"/>

Let's take a look at My. cnf in this test.

[root@centos test]# vim /etc/my.cnf[mysqld]datadir=/usr/datasocket=/tmp/mysql.sockuser=mysqlserver-id=1log-bin=mysql-binreport-port=3306port=3306symbolic-links=0[mysql_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid

There are four backup operations:

Method 1>; mysqldump:

Mysqldump backup database full backup + increase backup, the speed is relatively slow, suitable for small and medium databases) MyISAM is a warm backup, InnoDB is a hot backup)

Mysqldump adopts an SQL-level backup mechanism. It imports data tables into SQL script files and is suitable for upgrading between different MySQL versions. This is also the most common backup method. Mysqldump is slower than direct copy. For systems with medium business volume, the backup policy can be set as follows: the first full backup, one Incremental Backup every day, and one full backup every week. For important and BUSY systems, full backup may be required once a day, Incremental backup once an hour, or even more frequently. To achieve online backup and Incremental Backup without affecting online services, the best way is to use the master-slave replication mechanism (replication) to back up data on the slave machine.

Method 2>; direct cp database file:

When using the direct backup method, you must ensure that the table is not used. If the server changes a table while you are copying it, the copy will be meaningless. The best way to ensure your copy integrity is to close the server, copy files, and restart the server. If you do not want to shut down the server, you must lock the server while performing the table check. If the server is running, the same constraints apply to copying files, and you should use the same locking protocol to make the server "quiet ". When you have completed the backup, you need to restart the server (if it is disabled) or release the lock added to the table (if you want the server to run ). To copy a database from one machine to another, just copy the file to the appropriate data directory of another server host. Make sure the file is in MyIASM format or the two machines have the same hardware structure. Otherwise, your database has strange content on another host. You should also ensure that the servers on the other machine do not access them when you are installing database tables.

Method 3>; lvm snapshot:

From the physical perspective, lvm snapshot achieves almost full hot backup, and Incremental backup with binary log backup, which is fast and suitable for busy databases.

Prerequisites:

• Data files must be on logical volumes;

• The volume group of the logical volume must have enough space to use the snapshot volume;

• Data Files and transaction logs must be on the same logical volume;

Method 4>; xtrabackup backup tool. Xtrabackup back up the database to achieve full hot backup and incremental Hot Backup MyISAM is a warm backup, InnoDB is a hot backup)

3. instance operations:

Method 1>; mysqldump:

● View the current log status;

mysql> show master status;

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/13013T960-3.jpg "title =" 1.jpg"/>

● Back up the entire database; Determine the backup file

mysqldump -u root -p --all-databases --lock-all-tables --routines --triggers --events --master-data=2 --flush-logs > /backup/201308071550.full.sql

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/13013U352-4.jpg "title =" 1.jpg"/>

-- All-databases # Back up all databases -- lock-all-tables # apply a read lock to all tables -- routinge # stored procedures and functions -- triggers # Trigger -- events # record events -- master-data = 2 # record the location of the current binary log in the backup file, and it is annotated. 1 is meaningful only when the comment is not commented out in master-slave replication -- flush-logs # the log is rolled once.

● View the log status at this time;

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/13013U410-5.jpg "title =" 1.jpg"/>

● Added several data records for Incremental backup;

Mysql> insert into test. t1 (id, user_name, user_passwd) values (10000, 'andy Lau ', '000000'), (123, 'zhang Xueyou', '000000'), (10001, 'kwok city ', '123 ');

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/13013W2Y-6.jpg "title =" 1.jpg"/>

● Back up binlog logs

cp /usr/data/mysql-bin.000007 /backup/201308071550.full.binlog.000001

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/13013R4N-7.jpg "title =" 1.jpg"/>

● Simulate database corruption and restore

rm -rf /usr/data/*killall mysqld/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/usr/data/ --user=mysqlservice mysqld startmysql -u root -pmysql> source /backup/201308071550.full.sql

● At this point, you will find that the data has been restored to our full backup, and there is no "Three Kings" inserted later ";

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/13013VF2-8.jpg "title =" 1.jpg"/>

● Restore the data we inserted later to see if there is any data from Tianwang;

[root@centos ~]# mysqlbinlog /backup/201308071550.full.binlog.000001 |mysql test

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/13013QE0-9.jpg "title =" 1.jpg"/>

At this point, the mysqldump operation is over. In recovery mode, binlog is added for the first full backup;

Method 2>; direct cp database file (this method is relatively simple, I suggest you lock before cp)

◆ First, lock the table:

mysql> flush tables with read lock;

◆ Cp data file:

mkdir /backup/`date +%F-%H-%M-%S`cp -pr /usr/data/* /backup/2013-08-07-17-02-19/

◆ Unlock:

mysql> unlock tables;

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/13013Q307-10.jpg "title =" 1.jpg"/>

◆ Simulate database destruction and restore:

rm -rf /usr/data/*killall mysqld /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/usr/data/ --user=mysqlcp -pr /backup/2013-08-07-17-02-19/* /usr/data/service mysqld start

◆ Now the system returns to normal;

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/13013W1H-11.jpg "title =" 1.jpg"/>

Method 3>; lvm Snapshot

★Lock table, refresh log;

mysql> flush tables with read lock;mysql> flush logs;

★Another terminal, lvm Snapshot

lvcreate -L 1G -n mysql-snapshot -s -p r /dev/mylvm/lansgg

Note: My/dev/mylvm vg has a total of 5 GB, 4 GB for lansgg and 1 GB for it;

-L 1G # logical volume size-n mysql-snapshot # Name of the generated snapshot-s # specify the source logical volume-p r # permission settings

Unlock:

mysql> unlock tables;

★Attach a snapshot, cp the data in the snapshot to a directory, detach and delete the snapshot.

mkdir /backup/lvmsnapshotmount /dev/mylvm/mysql-snapshot /mnt/cp -pr /mnt/* /backup/lvmsnapshot/umount /mnt/lvremove /dev/mylvm/mysql-snapshot

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/13013Q342-12.jpg "title =" 1.jpg"/>

★Perform data destruction and recovery

rm -rf /usr/data/*killall mysqldcp -pr /backup/lvmsnapshot/* /usr/data/

Recovery completed

Ps: The above Incremental Backup can be the same as the method in method 1;

Method 4>; xtrabackup Backup Tool

▲Install the required package:

yum install libaio libaio-devel perl-Time-HiRes curl curl-devel zlib-devel openssl-devel perl cpio expat-devel gettext-devel perl-ExtUtils-MakeMaker perl-DBD-MySQL.*  -y

▲Install xtrabackup:

wget http://www.percona.com/redir/downloads/XtraBackup/LATEST/binary/Linux/i686/percona-xtrabackup-2.1.3-608.tar.gztar zxvf percona-xtrabackup-2.1.3-608.tar.gzcd percona-xtrabackup-2.1.3/bin/cp * /usr/bin/

▲Full backup:

innobackupex --ibbackup=xtrabackup --user=root --password=123 --defaults-file=/etc/my.cnf /tmp/data/

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/13013S9D-13.jpg "title =" 1.jpg"/>

Omitted ~~~ End

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/13013S323-14.jpg "title =" 1.jpg"/>

View the backup file:

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/13013WP5-15.jpg "title =" 1.jpg"/>

The data is completely backed up to the directory named current date in/tmp/data, and xtrabackup backs up all InnoDB tables, the MyISAM table only copies the table structure files and files related to MyISAM, MERGE, CSV, and ARCHIVE tables. It also backs up the files related to the trigger and database configuration information. In addition to saving data, some data files required for xtrabackup are generated.

• Xtrabackup_checkpoints backup type such as full or incremental), backup status such as whether it is already in the prepared status) and LSN (log serial number) range information; each InnoDB page (usually 16 K size) it will contain a log serial number (LSN. The LSN is the system version number of the entire database system. The LSN related to each page can indicate how the page has changed recently.

• Xtrabackup_binlog_info the binary log file currently in use on the mysql server and the location of the binary log event until the moment of backup.

• The xtrabackup executable file used in xtrabackup_binary backup.

• Configuration option information used by the backup-my.cnf BACKUP command.

• Xtrabackup_logfile records standard output information xtrabackup_logfile

▲Test data recovery:

killall mysqldrm -rf /var/lib/mysql/*innobackupex --apply-log /tmp/data/2013-08-11_14-54-26/innobackupex --copy-back /tmp/data/2013-08-11_14-54-26/chown  -R mysql.mysql /var/lib/mysql/service mysqld start

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/13013TO8-16.jpg "title =" 1.jpg"/>

Complete recovery;

Note:


-- Apply-log: cancels a transaction that has not been committed during backup. The transaction logs that have been committed are still applied to the database.

-- Copy-back database recovery, followed by the location of the Backup Directory

2. ▲incremental backup test:

Insert data:

Mysql> insert into test. t1 (id, user_name) values (10, 'zhang san ');

Incremental Backup:

Innobackupex -- ibbackup = xtrabackup -- user = root -- password = 123 -- incremental-basedir =/tmp/data/2013-08-11_14-54-26/tmp/data/# -- incremental specifies incremental Backup # -- incremental-basedir specifies which full backup is used for incremental backup, finally, the directory saved by Incremental Backup ps: Incremental Backup can only perform Incremental backup on the InnoDB engine, and the MyISAM table is completely copied.

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/13013SM7-17.jpg "title =" 1.jpg"/>

Incremental Backup test recovery:

killall mysqldrm -rf /var/lib/mysql/*innobackupex --ibbackup=xtrabackup --apply-log --redo-only /tmp/data/2013-08-11_14-54-26/innobackupex --ibbackup=xtrabackup --apply-log /tmp/data/2013-08-11_14-54-26/ --incremental-dir=/tmp/data/2013-08-11_15-16-47/innobackupex --copy-back /tmp/data/2013-08-11_14-54-26/chown -R mysql.mysql /var/lib/mysql/service mysqld start

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/13013WY5-18.jpg "title =" 1.jpg"/>

Proof, incremental test recovery;

3. Extended usage:

▼ Complete backup of a single database and packaging:

innobackupex --ibbackup=xtrabackup --user=root --password=123 --defaults-file=/etc/my.cnf --database=test --stream=tar /tmp/data/ > /tmp/data/testfull201308111624.tar

▼ Complete backup of a single database and compress the package:

innobackupex --ibbackup=xtrabackup --user=root --password=123 --defaults-file=/etc/my.cnf --database=test --stream=tar /tmp/data/ |gzip > /tmp/data/testfull201308111628.tar.gz

Single-database Incremental Backup:

innobackupex --ibbackup=xtrabackup --user=root --password=123 --database=test --incremental --incremental-basedir=/tmp/data/2013-08-11_14-54-26/ /tmp/data/add/

This article is from the Coffee _ Blue Mountains blog, please be sure to keep this source http://lansgg.blog.51cto.com/5675165/1270430