Shulou(Shulou.com)06/01 Report--

The following will give you an introduction of the three methods of backing up and restoring MySQL database. If you are interested, let's take a look at this article. I believe it is more or less helpful for you to read the introduction of the three methods of backup and recovery of MySQL database.

Three methods of backup and recovery of MySQL Database

1. Complete backup of mysql from a logical point of view by using mysqldump, and incremental backup with binary log backup

two。 Use lvs snapshots to achieve almost hot standby full backup from a physical point of view, and cooperate with binary log backup to achieve incremental backup

3. Complete hot backup and incremental hot backup by using xrabackup of percona company

Lab environment: RHEL5.8, SElinux is closed, MySQL is the tar package initialization installation version 5.5.28

one。 Test environment preparation

1.1For the installation of mysql, see http://laoguang.blog.51cto.com/6013350/1039208

1.2 Editor / etc/my.cnf changes the binary log directory to another non-data directory, innodb one file per table

Set up a directory to store binary logs

Mkdir / mybinlog

Chown mysql:mysql / mybinlog

Modify my.cnf

Vim / etc/my.cnf

Log-bin=/mybinlog/mysql-bin # # binary log directory and file name prefix

Innodb_file_per_table = 1 # # enable InnoDB table one file per table. By default, all libraries use one tablespace.

Start mysqld

Service mysqld start

1.3 create a test library and test table

Mysql > create database benet

Mysql > use benet

Mysql > create table linux (id tinyint auto_increment primary key,name char (10))

Mysql > insert into linux (name) values ('apache'), (' nginx'), ('php')

1.4 create a directory to store backups

Mkdir / myback

Chown-R mysql:mysql / myback

Second, use mysqldump to realize backup

2.1 mysqldump is used to warm up, so we have to add read locks for all libraries and scroll through the binary log and record the current binary location

Mysqldump-all-databases-lock-all-tables-routines-triggers-master-data=2\

-- flush-logs > / myback/2012-12-3.19-23.full.sql

-- all-databases backs up all libraries

-- lock-all-tables adds read locks to all tables

-- routines stored procedures and functions

-- triggers trigger

-- master-data=2 records the location of the current binary log in the backup file and is annotated. 1 is meaningful only if it is not commented out in the master-slave replication.

-- flush-logs log scrolls once

Check to see if the backup is successful, whether the new binary log is enabled, and whether the location of the binary after the full backup is recorded in the backup file.

Backup binary log

Cp / mybinlog/mysql-bin.000001 / myback/2012-12-3.19-23.full.00001

2.2 simulate accidental database damage and test full recovery

Rm-rf / data/mydata/*

Rm-rf / mybinlog/*

Initialize mysql and start mysql

Cd / usr/local/mysql

. / scripts/mysql_install_db-- user=mysql--datadir=/data/mydata

Rm-rf / mybinlog/* # # because we are not newly initialized and may have binary logs that report errors, we do not need

The new binary log will be regenerated when service mysqld start # # starts.

To restore to the backup state, turn off binary logging of the recovery process before backup, because it is meaningless to record recovery statements

Mysql > set global sql_log_bin=0

Mysql

< /myback/2012-12-3.19-23.full.sql ##如果有账号密码记的-u -h哦 打开记录并查看恢复状况 mysql>

Set global sql_log_bin=1

Mysql > show databases

Open the binary record and view the recovery status

Mysql > set global sql_log_bin=1

Mysql > show databases

2.3 simulate a scenario where I add new data to the linux table, and then accidentally delete the table, we need to return to the state before deletion, and the newly added data still exists.

2.3.1 New data

Mysql > use benet

Mysql > insert into linux (name) values ('haddop'), (' mysql')

Mysql > drop table linux

Mysql > show master status; # # View the location in the current binary log

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000001 | 9005 | |

+-+

We first restore the complete data, and then restore the data from the full backup to the data before deletion. The corresponding binary log is the position of the binary log after the full backup to the position before the table is deleted.

2.3.2 restore the full backup first, and do not log in the same recovery process

Mysql > set global sql_log_bin=0

Mysql

< /myback/2012-12-3.19-23.full.sql 2.3.3 查看删除表时的记录位置 mysqlbinlog /mybinlog/mysql-bin.000001 # at 8893 #121202 14:14:07 server id 1 end_log_pos 9005 Query thread_id=5exec_time=0error_code=0 SET TIMESTAMP=1354428847/*!*/; DROP TABLE `linux` /* generated by server */ /*!*/; DELIMITER ; # End of log file 2.3.4 由上图可知删除是在8893时做的，将二进制文件中完整备份到删除表之前的记录导出 mysqlbinlog --stop-position=8893 /mybinlog/mysql-bin.000001 >

/ tmp/change.sql

-- start-position specifies where to start exporting binary logs

-- stop-position specifies where to end

When does start-datetime start in a format such as "2005-12-25 11:25:56"

-- when does stop-datetime end?

Since this binary log is only enabled after we have fully restored it, we can start from scratch. If you have a lot of binary logs, please check the location of the backup recorded in the full backup, from there to before deletion.

Apply this binary record to the library of mysql

Mysql

< /tmp/change.sql 进入数据库查看数据有没有恢复 mysql>

Select * from linux

Based on mysqldump, we usually restore it with full backup and binary log.

Third, use the snapshot of lvm to back up MySQL

Requires that the data directory of your MySQL must be on a lvm volume. Here is a demonstration of the process.

3.1 set up a lvm volume group and mount it to / data/mydata. I won't demonstrate this.

3.2 when initializing MySQL, point the data directory to / data/mydata, see the link above for the installation process

3.3 prepare the environment as in the first step

3.4 add read locks for all tables in MySQL, do not close the terminal, otherwise the lock will expire and scroll the log

Mysql > flush tables with read lock

Mysql > flush logs

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000002 | 107 |

+-+

3.5 create a snapshot at another terminal speed, and my volume group is / dev/myvg/mydata

Lvcreate-L 200m-n mysql-snap-s-p r / dev/myvg/mydata

3.4 Speed release read lock

Mysql > unlock tables

3.5 Mount snapshots, copy them out, unmount snapshots, delete snapshots

Mount / dev/myvg/mysql-snap / mnt

Mkdir / myback/lvm

Cp-pR / mnt/* / myback/lvm

Umount / mnt

Lvremove / dev/myvg/mysql-snap

3.6 such a full backup is completed, so let's test whether it can be used properly.

Servivce mysqld stop

Rm-R / data/mydata/*

Cp-Rp / myback/lvm/* / data/mydata

Service mysqld start # # if you can start normally, it means there is no problem. If you can't get up, please see the permissions of the data directory.

3.7 if MySQL fails after a full backup, like mysqldump, first restore the last full backup, then use binary log recovery, binary recovery goes through it again, find the binary location of the full backup, export the logs from then to before the failure with mysqlbinlog, and then import them into MySQL in batch mode. This is consistent with the experiment in mysqldump and will not be repeated.

Using lvm snapshots to backup is very fast, and almost hot backup, recovery is also very fast, the operation is also simple, and then restore the corresponding binary system after a complete recovery.

Four: full backup, incremental backup, hot backup MySQL based on xtrabackup

Download address: http://www.percona.com/software/percona-xtrabackup

Download and install xtrabackup, I use percona-xtrabackup-2.0.3-470.rhel5.i386.rpm

Yum install perl-DBD-MySQL

Rpm-ivh percona-xtrabackup-2.0.3-470.rhel5.i386.rpm

4.2 the basic MySQL environment is consistent with that of step 1

4.3 create a user with backup privileges for backup

Mysql > create user 'percona'@'localhost' identified by' redhat'

Mysql > revoke all privileges,grant option from 'percona'@'localhost'

Mysql > grant reload,lock tables,replication client on *. * to 'percona'@'localhost'

Mysql > flush privileges

4.4 A full backup of MySQL

Innobackupex-host=locahost-user=percona-password=redhat-defaults-file=/usr/local/mysql/my.cnf / myback/

The data will be fully backed up to the current date in / myback/, and extrabackup will back up all InnoDB tables. The MyISAM table will only copy table structure files, as well as related files for MyISAM, MERGE, CSV, and ARCHIVE tables, as well as files related to triggers and database configuration information. In addition to saving the data, some data files needed by extrabackup are also generated.

1) xtrabackup_checkpoints backup type (such as full or incremental), backup status (such as whether it is already prepared status), and LSN (log serial number) scope information; each InnoDB page (usually 16k size) contains a log serial number, that is, LSN. LSN is the system version number of the entire database system, and the LSN associated with each page can indicate how the page has changed recently.

2) the binary log files currently in use on the xtrabackup_binlog_info mysql CVM and the location of the binary log events up to the moment of backup.

3) the current position of the xtrabackup_binlog_pos_innodb binary log file and the binary log file for the InnoDB or XtraDB table.

4) executable files of xtrabackup used in xtrabackup_binary backup

5) configuration option information used by backup-my.cnf backup command

4.4.Test recovery MySQL, complete recovery with extrabackup

Service mysqld stop

Rm-Rf / data/mydata

Innobackupex-- apply-log / myback/2012-12-02 / 20-06-12 /

-- the meaning of apply-log is to undo transactions that are not commit at the time of backup, and those that have been commit but are still in the transaction log are applied to the database.

Innobackupex-- copy-back / myback/2012-12-02 / 20-06-12 /

-- copy-back database recovery followed by the location of the backup directory

Chown-R mysql:mysql / data/mydata

Service mysqld start # # if it can be started, it will return to normal.

4.5 Let's experiment with incremental backup

4.5.1 add some data to the table

Mysql > insert into linux (name) values ('tomcat'), (' memcache'), ('varnish')

4.5.2 incremental backup

Innobackupex-user=percona--password=redhat-incremental\

-- incremental-basedir=/myback/2012-12-02 20-06-12 / / myback/

-- incremental specifies an incremental backup

-- incremental-basedir specifies which backup to use for the incremental backup, and finally the directory where the incremental backup is saved

Incremental backup can only do incremental backup to the InnoDB engine, and full replication of MyISAM tables

4.6 Test incremental backup recovery

Service mysqld stop

Rm-Rf / data/mydata/*

Innobackupex-apply-log-redo-only / myback/2012-12-02 20-06-12 /

-- redo-only refers to the data applied in the transaction log that is commit at the time of backup, but not undone if it has not been committed.

Because this transaction may be committed in an incremental backup, if it is undone, it will not be committed in the incremental backup, because the transaction is already incomplete

Merge incremental backups into full backups

Innobackupex-- apply-log / myback/2012-12-02 / 20-06-12 /\

-- incremental-dir=/myback/2012-12-02 20-28-49 /

/ myback/2012-12-02 / 20-06-12 / this is the purpose of the full backup

-- incremental-dir is followed by the directory of the incremental backup

This will merge the data from the incremental backup into the full backup. If there is an incremental backup, continue to merge and restore the full backup during recovery.

Recover data and start MySQL

Innobackupex-- copy-back / myback/2012-12-02 / 20-06-12 /

Chown-R mysql:mysql / data/mydata

Service mysqld start

Check to see if the data is missing

If the database fails after an incremental backup, we need to restore it through a full backup + all incremental backups so far + the last incremental backup to the current binary log.

Read the above on the backup and recovery of the MySQL database of the three methods, whether there are gains. If you want to know more about it, you can continue to follow our industry information section.

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.