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

This article introduces you how to achieve backup and recovery in MySQL, the content is very detailed, interested friends can refer to, hope to be helpful to you.

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

2. Use lvs snapshot to realize almost hot standby full backup from the physical point of view, and cooperate with binary log backup to realize incremental backup.

3. Realize full 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

I. preparation of the test environment

1.1 not to mention the installation of mysql

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

1.mkdir / mybinlog

2.chown mysql:mysql / mybinlog

Modify my.cnf

1.vim / etc/my.cnf

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

3.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

1.mysql > create database laoguang

2.mysql > use laoguang

3.mysql > create table linux (id tinyint auto_increment primary key,name char (10))

4.mysql > insert into linux (name) values ('apache'), (' nginx'), ('php')

1.4 create a directory to store backups

1.mkdir / myback

2.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

1.mysqldump-all-databases-lock-all-tables-routines-triggers-master-data=2\

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

3.

4.--all-databases backs up all libraries

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

6.--routines stored procedures and functions

7.--triggers trigger

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

9.--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

1.rm-rf / data/mydata/*

2.rm-rf

3.According to mybinlogUniverse *

Initialize mysql and start mysql

1.cd / usr/local/mysql

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

3.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 4.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

1.mysql > set global sql_log_bin=0

2.mysql

Set global sql_log_bin=1

5.mysql > show databases

Open the binary record and view the recovery status

1.mysql > set global sql_log_bin=1

2.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 the deletion, and the newly added data still exists.

2.3.1 New data

1.mysql > use laoguang

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

3.mysql > drop table linux

4.mysql > show master status; # # View the location in the current binary log

5. Music +

6. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

7. Music +

8. | mysql-bin.000001 | 9005 |

9.The color of music +

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.

On how to achieve backup and recovery in MySQL to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.