LVM快照备份恢复mysql

LVM快照备份恢复mysql

使用lvm备份要满足
1.所有的innodb文件（数据文件和日志文件）必须在单个逻辑卷（lvm不能为多个lv同一时间做一致性快照）
2.卷组中必须有足够的空间来创建快照
[root@mysql mysqldata]# vgs
  VG        #PV #LV #SN Attr   VSize  VFree  
  vg_mysql1   2   6   1 wz--n- 58.59g 752.00m

[root@mysql mysqldata]# lvs
  LV           VG        Attr       LSize  Pool Origin   Data%  Move Log Cpy%Sync Convert
  LogVol00     vg_mysql1 owi-aos--- 19.53g                                               
  LogVol01     vg_mysql1 -wi-ao----  9.77g                                               
  LogVol02     vg_mysql1 -wi-ao----  9.77g                                               
  LogVol03     vg_mysql1 -wi-ao----  4.88g                                               
  LogVol04     vg_mysql1 -wi-ao----  3.91g    

创建快照
[root@mysql mysqldata]# lvcreate --size 10G --snapshot -name backup_mysql /dev/vg_mysql1/LogVol00
查看：
[root@mysql mysqldata]# lvs
  LV           VG        Attr       LSize  Pool Origin   Data%  Move Log Cpy%Sync Convert
  LogVol00     vg_mysql1 owi-aos--- 19.53g                                               
  LogVol01     vg_mysql1 -wi-ao----  9.77g                                               
  LogVol02     vg_mysql1 -wi-ao----  9.77g                                               
  LogVol03     vg_mysql1 -wi-ao----  4.88g                                               
  LogVol04     vg_mysql1 -wi-ao----  3.91g                                               
  backup_mysql vg_mysql1 swi-a-s--- 10.00g      LogVol00  21.75             
  
挂在快照拷贝出备份的文件
[root@mysql mysqldata]# mkdir /home/backup
[root@mysql mysqldata]# mount /dev/mapper/backup_mysql /home/backup 
打包mysql数据文件目录
[root@mysql mysqldata]# tar -cvf mysqldata.tar mysqldata/
拷贝数据文件
[root@mysql mysqldata]# cp mysqldata.tar /home/

恢复备份：
解压数据文件
[root@mysql mysqldata]# tar -xcf mysqldata.tar 
构造配置文件，并启动mysql
# Generated by Percona Configuration Wizard (http://tools.percona.com/) version REL5-20120208
# Configuration name mysqlmaster generated for jackwangw@126.com at 2017-06-19 09:31:40
[mysql]
# CLIENT #
port                           = 3308
socket                         = /home/mysqldata/mysql.sock
[mysqld]
# GENERAL #
user                           = mysql
default-storage-engine         = InnoDB
socket                         = /mysqldata/mysql.sock
pid-file                       = /mysqldata/mysql.pid
#skip-grant-tables
# MyISAM #
key-buffer-size                = 32M
#myisam-recover                 = FORCE,BACKUP
# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000
skip-name-resolve
@
"my.cnf" 64L, 2200C
# Generated by Percona Configuration Wizard (http://tools.percona.com/) version REL5-20120208
# Configuration name mysqlmaster generated for jackwangw@126.com at 2017-06-19 09:31:40
[mysql]
# CLIENT #
port                           = 3308
socket                         = /home/mysqldata/mysql.sock
[mysqld]
# GENERAL #
user                           = mysql
default-storage-engine         = InnoDB
socket                         = /mysqldata/mysql.sock
pid-file                       = /mysqldata/mysql.pid
#skip-grant-tables
# MyISAM #
key-buffer-size                = 32M
#myisam-recover                 = FORCE,BACKUP
# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000
skip-name-resolve
sql-mode                       = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
sysdate-is-now                 = 1


# DATA STORAGE #
datadir                        = /home/mysqldata/data
# BINARY LOGGING #
server-id                       = 1
log-bin                        = /home/mysqldata/mysql-bin
expire-logs-days               = 14
sync-binlog                    = 1
# REPLICATION #
skip-slave-start               = 1
log-slave-updates              = 1
relay-log                      = /home/mysqldata/relay-bin
slave-net-timeout              = 60

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 100

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 64M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 800M


# LOGGING #
log-error                      = /home/mysqldata/mysql-error.log
log-queries-not-using-indexes  = 1
slow-query-log                 = 1
slow-query-log-file            = /home/mysqldata/mysql-slow.log

启动mysql
mysqld_safe --defaults-file=/home/my.cnf --user=mysql --datadir=/home/mysqldata/data &

由于快照备份是备份的那一时刻的数据，之后的数据需要应用binlog日志来恢复
找到恢复日志的起点
[root@mysql mysqldata]# mysqlbinlog mysql-bin.000011 |grep -B10 -i 'CREATE TABLE `order_log_ww` '
[root@mysql mysqldata]# mysqlbinlog mysql-bin.000011 |tail -20 
MDMxMDA3MTAwMjY0MTEwMDIzMTE4mZwUcsCZnBV+wPgaJzIwMTcwMzEwMTY1ODAyNjQxMTAwNDk5
MzeZnBUaAJmcFX7A+BonMjAxNzAzMTAxNjEwMDI2NDExMDA5MjUxNJmcFRYAmZwVfsD4GicyMDE3
MDMwOTIwNTQwMjY0MTEwMDQ5OTU4mZwVGgCZnBV+wDlFBag=
'/*!*/;
# at 546853422
#170727  9:39:17 server id 1  end_log_pos 546853453 CRC32 0xdafc50f2    Xid = 1478
COMMIT/*!*/;
# at 546853453
#170727  9:39:17 server id 1  end_log_pos 546853518 CRC32 0xbb63b76b    Anonymous_GTID  last_committed=1012     sequence_number=1013
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 546853518
#170727  9:39:17 server id 1  end_log_pos 546853637 CRC32 0xed6bf6ee    Query   thread_id=12    exec_time=0     error_code=0
SET TIMESTAMP=1501119557/*!*/;
/*!40000 ALTER TABLE `wy_2order` ENABLE KEYS */
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

拷贝原来日志文件到新的恢复目录：
[root@mysql mysqldata]# cp /mysqldata/mysql-bin.000011 ./mysql-bin.wangwei
[root@mysql mysqldata]# ls
data              mysql-bin.000003  mysql-bin.000006  mysql-bin.000009  mysql-bin.000012   mysql-error.log
mysql-bin.000001  mysql-bin.000004  mysql-bin.000007  mysql-bin.000010  mysql-bin.index    mysql-slow.log
mysql-bin.000002  mysql-bin.000005  mysql-bin.000008  mysql-bin.000011  mysql-bin.wangwei  mysql.sock.lock

应用日志：
[root@mysql mysqldata]# mysqlbinlog mysql-bin.wangwei --start-position=546853637 |mysql -uroot -proot001
mysql: [Warning] Using a password on the command line interface can be insecure.
恢复完毕！