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.
恢复完毕!
使用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.
恢复完毕!