LVM快照备份恢复mysql

墨蓝 2022-06-11 01:26 302阅读 0赞

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.  
恢复完毕！