MySQL's LVM snapshot backup recovery

It seems that the general people do not use this bar, here is only as a record it.

In fact, the LVM snapshot no matter what storage engine can be close to hot standby, looks also good look. But it also needs some necessary conditions.

For example: LVM volumes, and there is enough free space to take snapshots, and the transaction log is also associated with the data file.

Well, don't say much nonsense, let's see how we can use it first.

Approximate steps:

1, Global lock.

2, rolling binary log, and do a good record

3. Create an LVM snapshot.

4, Release the lock.

5. Manually copy data from the snapshot disk.

6. Unmount and remove the snapshot disk.

Come on.

First look at the LVM situation:

[[email protected] backup]# vgs                  #卷组空闲4G多. I have enough here.   VG    #PV   #LV   #SN  Attr   VSize  VFree   vg0    1   5   0 wz--n- 58.59g 4.82g[[ Email protected] backup]# lvs  lv    vg   attr        LSize  Pool Origin Data%  Move  Log cpy%sync convert  mysql vg0  -wi-ao----  3.00g         #mysql的逻辑卷, very small.                                          root  vg0   -wi-ao---- 14.65g                                                 swap   vg0  -wi-ao----  1.94g                                                  usr   vg0  -wi-ao---- 19.53g                                                  var   vg0  -wi-ao---- 14.65g 

Because the lock time is shorter, the better, so with two terminals operate separately.

Terminal A starts creating a global lock on the MySQL command line. It may take a while to wait in a busy environment.

mariadb [(None)]> flush tables with read lock;    # Write the information in the buffer to disk and create a global lock query ok, 0 rows affected  (0.00 sec) mariadb [(none)]>  FLUSH LOGS;                      #滚动二进志日志Query  OK, 0 rows affected  (0.01 sec) mariadb [(None)]> show master status;              #查看当前日志, and do record +-------------------+----------+--------------+------------------+|  file              | position |  binlog_do_db | binlog_ignore_db |+-------------------+----------+--------------+--------- ---------+| master-bin.000002 |      245 |               |                   |+-------------------+----------+--------------+------------------+1  row in set  (0.00 SEC)

Terminal B hurriedly creates a snapshot. Note that the size of the snapshot disk, too small will explode.

[Email protected] backup]# lvcreate-l 2g-n backup-s-P r/dev/vg0/mysql Logical Volume "Backup" created

Created successfully, terminal a unlocks.

MariaDB [(None)]> UNLOCK TABLE; Query OK, 0 rows Affected (0.00 sec)

Then you can start copying data, CP or other commands.

[[email protected] backup]# mount /dev/vg0/backup  /mnt/ -r[[email protected] backup]# cp -a /mnt/data . [[email protected] backup]# lsdata[[email protected] backup]# cd data[[ email protected] data]# lsaria_log.00000001  ba   hellodb   ib_logfile0  mysql              mysql-bin.index  nfs.star.com.err  performance_schema  testdb1   Testdb3aria_log_control   ddd  ibdata1  ib_logfile1  nfs-slow.log      nfs.star.com.pid  test                 testdb2  ttt[[email protected] data]# 

When the copy is complete, a full backup is completed. The transaction log cannot be deleted, and there may be transactions that have been committed or have not yet been committed.

Unmount and remove the snapshot disk.

[[email protected] data]# umount/mnt[[email protected] data]# pwd/backup/data[[email protected] data]# lvremove/dev/vg0 /backupdo really want to remove active logical volume backup? [y/n]: Y Logical Volume "Backup" successfully removed[[email protected] data]#

After we unlock the global lock, there may already be data written in the database. The backup you just made is just a previous backup. If you want to revert to the latest state, you will need to use a binary log. Just not recorded a binary log information, from that log after all is the backup after the change.

So let's create some more tables and insert some data to try. To simulate the data modifications after the backup.

MariaDB [(None)]> CREATE DATABASE ABCD; Query OK, 1 row Affected (0.00 sec) MariaDB [(none)]> CREATE TABLE abcd.tb1 (ID int,name char (30)); Query OK, 0 rows affected (0.03 sec) MariaDB [(none)]> INSERT into Abcd.tb1 VALUES (1, ' Tom '), (2, ' Lili '), (3, ' Tonny '); Query OK, 3 rows affected (0.01 sec) Records:3 duplicates:0 warnings:0

I created a ABCD library here and created the TB1 table in the library and inserted the data.

Recover data:

Simulate data corruption and erase all data files. and stop the database.

[[email protected] data]# lsabcd                aria_log_control  DDD      ibdata1       ib_logfile1  mysql-bin.000014  nfs-slow.log       nfs.star.com.pid    test     testdb2   TTTaria_log.00000001  BA                 hellodb  ib_logfile0  mysql         mysql-bin.index   nfs.star.com.err  performance_schema   testdb1  testdb3[[email protected] data]# rm -rf *[[email  protected] data]# ls[[email protected] data]# killall mysqld[[email  protected] binlogs]# service mysqld start         #数据库已经不能启动了. starting mysql...                                            [failed][[email protected] binlogs]#

Exports the contents of a binary file that started from the last record. and head to see, Million is the file is an error message.

[Email protected] binlogs]# lsmaster-bin.000001 master-bin.000002 master-bin.index[[email protected] binlogs]# Mysqlbinlog--start-position=245 master-bin.000002 >/backup/backup.sql[[email protected] binlogs]# head-n 30/ Backup/backup.sql

Recovery:

1. Copy all the files you have backed up to the database directory.

[[email protected] data]# ls         #刚才启动数据库所生成的文件. Aria_log.00000001  aria_log_control  ibdata1  ib_logfile0  ib_logfile1   nfs-slow.log  nfs.star.com.err[[email protected] data]# rm -rf  *[[email protected] data]# cp -a /backup/data/* .    # Copy the data we've backed up, [[email protected] data]# lsaria_log.00000001  ba   . hellodb  ib_logfile0  mysql              mysql-bin.index  nfs.star.com.err  performance_schema  testdb1   testdb3aria_log_control   DDD  ibdata1  ib_logfile1   mysql-bin.000014  nfs-slow.log     nfs.star.com.pid  test                 testdb2  ttt[[email protected]  data]# ll         #查看权限是否有问题. [[Email protected] data]# service mysqld startstarting mysql].                                              [  ok  ][[email protected] data]#

The

started successfully. But without the library ABCD we just created.

mariadb [(None)]> show databases;+--------------------+| database            |+--------------------+| information_schema | |  BA                  ||  ddd                | |  ttt                | |  hellodb            | |  mysql              | |  performance_schema | |  test               | |  testdb1            | |  testdb2            | |  testdb3            |+--------------------+11  rows in set  (0.00 sec) mariadb [(none)]>

2. Restore the binary log.

MariaDB [(None)]> SET sql_log_bin=0-A; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> SHOW VARIABLES like ' sql_log_bin '; +---------------+-------+| variable_name | Value |+---------------+-------+| Sql_log_bin | OFF |+---------------+-------+1 row in Set (0.00 sec) MariaDB [(none)]> Source/backup/backup.sql

3. View data.

MariaDB [(None)]> use abcd;database changedmariadb [abcd]> SHOW tables;+----------------+| TABLES_IN_ABCD |+----------------+| TB1 |+----------------+1 row in Set (0.00 sec) MariaDB [abcd]> SELECT * from tb1;+------+-------+| ID |    Name |+------+-------+| 1 |    Tom | | 2 |    Lili | | 3 | Tonny |+------+-------+3 rows in Set (0.00 sec) MariaDB [abcd]>

650) this.width=650; "src=" Http://img.baidu.com/hi/jx2/j_0004.gif "alt=" J_0004.gif "/>

This article is from the "Big Tomato" blog, be sure to keep this source http://fanqie.blog.51cto.com/9382669/1710751

MySQL's LVM snapshot backup recovery