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

Editor to share with you how to achieve MySQL backup and recovery, I hope you will learn something after reading this article, let's discuss it together!

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://www.linuxidc.com/Linux/2012-04/58990.htm

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 laoguang

Mysql > use laoguang

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-tables backs up all libraries

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

-- routinge 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 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 laoguang

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 / 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 select * from linux

After reading this article, I believe you have a certain understanding of "how to achieve backup and recovery in MySQL". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

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.