Mysql uses LVM snapshots to implement backups _mysql

Create a new LVM disk, here I built the LV for MYDATALV, mounted to the/data under the

[Root@localhost ~]# LVs
 LV    VG   Attr    lsize Pool Origin data% meta% move Log cpy%sync Convert                         
 mydatalv m Ydata-wi-ao----1.00g  
[root@localhost ~]# df-h
file system           capacity used available% mount point
/dev/mapper/mydata-mydatalv 976M 2 .6M 907M  1%/data

Copy the original database file to the/data directory

[root@localhost ~]# cp -a /var/lib/mysql /data/


Modify the configuration file, place the MySQL database file on the LVM disk, and place the binaries in the/var/lib/mysql/directory of the non-LVM disk

[Root@ns1 ~]# vim/etc/my.cnf
  [mysqld]
  log_bin=/var/lib/mysql/mysql-bin
  datadir=/data/mysql
[ Root@localhost ~]# Service mariadb restart
[root@localhost ~]# ls/data/mysql aria_log.00000001-ibdata1   ib_logfile1 mysql-bin.000001 mysql-bin.000003 performance_schema
aria_log_control  ib_logfile0 MySQL    mysql-bin.000002 mysql-bin.index  test

You can see that the database file has been stored in the/data/mysql directory after the reboot.

Lock table Backup for MySQL

[Root@localhost ~]# mysql-e ' flush tables with read lock; ' lock table] [root@localhost ~]# mysql-e ' flush logs
; '     Scrolls the log,
[root@localhost ~]# mysql-e ' show master status; ' >/root/back.$ (Date +%f+%t)
[root@localhost ~]# ls
back.2016-07-13+10:14:29

Create a snapshot of LV

[root@localhost ~]# lvcreate -L 1G -n mysqlback -p r -s /dev/mydata/mydatalv


Release lock

[root@localhost ~]# mysql -e 'unlock tables;'


Create a backup directory on another disk, read-only mount snapshots, and then back up to the backup directory

[Root@localhost ~]# mkdir/myback
[root@localhost ~]# mount-r/dev/mydata/mysqlback/mnt
[root@localhost ~]# Cp-a/mnt/mysql/myback

Modify the contents of the table, and then delete the contents of the database file, that is,/data/mysql

[root@localhost ~]# mysql
  mariadb [hellodb]> use Hellodb;
  MARIADB [hellodb]> INSERT INTO classes (CLASS,NUMOFSTU) VALUES (' Xxoo ',);
[Root@localhost ~]# rm-rf/data/*

Modify the location of the binary log and database files in the configuration file

[Root@localhost ~]# vim/etc/my.cnf
  [mysqld]
  log_bin=/data/mysql/mysql-bin
  datadir=/data/mysql

Using the content restore in/myback/

[Root@localhost ~]# cp-a/myback/*/data/
[root@localhost ~]# service mariadb restart

After the snapshot is restored with the binary log, the following file is used to view the location of the binary log when the snapshot is executed

[Root@localhost ~]# cat back.2016-07-13+10\:14\:29 
File  Position  binlog_do_db  binlog_ignore_ DB
mysql-bin.000014    245

The operation after 245 of 000014 is made into a SQL file to restore

[root@localhost ~]# mysqlbinlog--start-position=245/var/lib/mysql/mysql-bin.000014 > Binlog.sql
[ Root@localhost ~]# MySQL </root/binlog.sql

viewing recovery scenarios

[root@localhost ~]# MySQL
  mariadb [(none)]> use Hellodb;
  MARIADB [hellodb]> SELECT * from classes;
  +---------+----------------+----------+
  | ClassID | Class     | Numofstu |
  +---------+----------------+----------+
  |    1 | Shaolin Pai  |    Ten |
  |    2 | Emei Pai    |    7 |
  |    3 | Qingcheng Pai |    One |
  |    4 | Wudang Pai   |    |
  |    5 | Riyue Shenjiao |    |
  |    6 | Lianshan Pai  |    |
  |    7 | Ming Jiao   |    |
  |    8 | Xiaoyao Pai  |    |
  |    9 | Xxoo      |    |
  +---------+----------------+----------+
  9 rows in Set (0.00 sec)