Using LVM snapshots for database backup

Mysql> show global varibable like '%log% ';

Sql_log_bin whether to log binary logs

Mysql> set Sql_log_bin=off; Temporarily turn off binary logging

Mysql> Show master status; View binary log

Mysql> show Binlog events in ' mysql-bin.000001 '; Viewing the contents of a specified binary file

To close a binary file when restoring a database

Mysql> set Sql_log_bin=off;

mysql> \. /root/students.sql

Mysqldump

--database DB1,DB2,...

--all-databases

MyISAM: Warm Backup

--lock-all-tables

--lock-tables

InnoDB: Hot Backup

--single-transaction

--flush-logs

--events Backup Events

--routines stored procedures and stored functions

--triggers Trigger

--MASTER-DATA{0|1|2}

Logical Backup:

1. Floating-point data loss accuracy

2. Backed up data may be larger than the original data, after compression can greatly save space

3. Not suitable for full backups of large databases

--------------------------------------------------------

For InnoDB: Warm

Mysql> flush tables with read lock;

After you wait for the background to sync to the data file

Show engine InnoDB status; View Background Data synchronization ibuf:

Mvcc,repeatable-read Multi-version concurrency control

--single-transaction

This restore does not write to the 2 binary log:

-----------------------------------------------

SELECT * into outfile '/tmp/t1.txt ' from T1; Backup

CREATE table t1s like T1; first creates an empty table with the same table structure

Load data infile '/tmp/t1.txt ' into table t1s; restore

---------------------------------------------------

# mysqlbinlog '/mydata/data/mysql-bin.00001 '

#at 605

# Mysqlbinlog--start-position=605/mydata/data/mysql-bin.00001 >/root/a.sql start from 605 to the end and save it again

CREATE TABLE tutors like tutor; Create a new table

TRUNCTE table tutor; clear the table data

Set sql_log_bin=0; Turn off binary logging

# source/root/a.sql; recovery

---------------------------------------------

Almost hot preparation: LVM

Snapshot

Premise:

1. The data file to be on the logical volume

2. The volume group that contains this logical volume must have sufficient space to use the snapshot volume

3. Data files and transaction logs to be on the same volume

Steps:

1. Open the session by applying a read lock name to lock all tables;

Mysql> flush tables with read lock;

mysql> flush logs;

2. Through another terminal, save the binary log file and related location information;

$ mysql-uroot-p-E ' show Master Status\g ' >/path/to/master.info

3. Create a Snapshot volume

#lvcreate-L #-s-p R-n lv_name/path/to/source_lv

4. Release the lock

mysql> unlock tables;

5. Mount the snapshot volume, backup

Mount

Cp

6. Deleting a snapshot volume

7. Incremental backup binary Log

Load data infile '/tmp/tutor.txt ' info table tutors;

Start Transaction Startup transaction

Use Jiaowu;

Desc tuors;

INSERT into Tutors (tname) value (' stu001 ');

SELECT @ @tx_isolation; Viewing isolation Levels

Commit; commit a transaction

Flush tables with read lock; Refresh the table and lock the table read-only

After execution

flush logs; Refresh Log

Show master status; Ensure the location of the current binary file information

# mysql-e ' Show Master Status\g ' >/backup/master-' Date +%f '. Info

Create snapshot volume, read-only, size 50M

# lvcreate-l 50m-s-P r-n mydata-snap/dev/myvg/mydata

unlock tables; release lock

Mount/dev/myvg/mydata/mydata-snap/mnt-o RO Mount

mkdir/backup/full-backup-2016-10-21/

[Email protected] data]# cp-a./*/backup/full-backup-2016-10-21

Umount/mnt

Lvremove--force/dev/myvg/mydata-snap Deleting a snapshot

Delete binary logs from backup files

Cat/backup/master-2016-10-21.info

Event hangs on the file, it's going to be limited by events.

Mysqlbinlog--start-datetime= ' 2016-10-21 11:10:10 ' mysql-bin.00003 mysql-bin.00004 >/backup/incremental-' Date +% f-%h-%m-%s '. sql;

[[email protected] data]# RM-RF./* Delete data

# cp-a/backup/full-backup-2016-10-21/*/mydata/data

# server Mysqld Start

MySQL > Set sql_log_bin=0

MySQL > Soure/backup/incremental-...

This article from "Operation and maintenance Growth Road" blog, declined reprint!

Using LVM snapshots for database backup