MySQL backup recovery based on LVM snapshot

Directory

1, Database Full backup

2. Preparing the LVM Volume

3. Data recovery to LVM volume

4. Backup data based on LVM snapshot

5. Data Disaster recovery

6. Summary

Write in front:

The MySQL 5.5.36 database is already installed in the test environment, but the data directory is not stored in the LVM volume, this time to demonstrate LVM-based data backup and recovery, so the MySQL data first migrated to LVM and then the snapshot volume backup.

If the InnoDB table is based on LVM, then the transaction log files and data files are on the same LVM logical volume, because at the same time we can only take a snapshot of one logical volume, if the transaction log is not on the same volume as the data file, The events that take the snapshot cause the transaction log to be inconsistent with the transactions that are committed in the real data.

1, Database Full backup

Take the existing data with the Mysqldum tool for full backup first:

[Email protected] ~]# mysqldump-uroot-p123456--lock-all-tables--flush-logs--events--routines--master-data=2--all -databases >/backup/data_dir/fulldata-' Date +%f '. Sql[[email protected] ~]# ls/backup/data_dir/ Fulldata-2015-04-14.sql[[email protected] ~]# service mysqld Stop #停止mysql

2. Preparing the LVM Volume

Prepare two volumes in advance as a PV for LVM:

[[email protected] ~]# fdisk -ldisk /dev/sda: 53.7 gb, 53687091200  bytes255 heads, 63 sectors/track, 6527 cylindersunits = cylinders  of 16065 * 512 = 8225280 bytesSector size  (logical/physical):  512 bytes / 512 bytesI/O size  (Minimum/optimal): 512 bytes  / 512 bytesdisk identifier: 0x000bf287   device boot       Start         End       Blocks   Id  System/dev/sda1   *            1          13       102400   83  linuxpartition 1 does not  end on cylinder boundary./dev/sda2               13        1926    15360000   83   Linux/dev/sda3            1926         2056     1048576   82   Linux swap / Solaris/dev/sda4             2056        6527    35916127+    5  Extended/dev/sda5             2056        2709     5248011    8e  Linux LVM/dev/sda6             2710  &Nbsp;     3363     5253223+  8e  linux  lvm

Create PV:

[Email protected] ~]# Pvcreate/dev/sda5/dev/sda6 physical Volume "/DEV/SDA5" Successfully created physical volume "/d Ev/sda6 "successfully created

Create VG:

[Email protected] ~]# vgcreate mygroup/dev/sda5/dev/sda6 Volume Group "MyGroup" successfully created

Create LV:

[Email protected] ~]# lvcreate-n mysqldata--size 3G mygroup #大小设置为3GB Logical Volume "Mysqldata" created

Format the LV and mount it:

[Email protected] ~]# lvdisplay #显示逻辑卷的详细信息 [[email protected] ~]# mke2fs-t ext4/dev/mygroup/mysqldata #格式化 [[Email Prote CTED] ~]# blkid/dev/mygroup/mysqldata #获取UUID/dev/mygroup/mysqldata:uuid= "F863E626-E34E-4207-B9CB-7FBB9C5B7F1E" Type= "Ext4" [[email protected] ~]# Mkdir/mnt/mydata #创建挂载目录

Add the following line to the/etc/fstab file:

[[email protected] ~]# vim/etc/fstabuuid=f863e626-e34e-4207-b9cb-7fbb9c5b7f1e/mnt/mydata ext4 defaults 0 0[[email PR Otected] ~]# mount-a #重新挂载 [[email protected] ~]# mount/dev/sda2 on/type ext4 (rw) proc On/proc type proc (rw) Sysfs on/ SYS type SYSFS (rw) devpts on/dev/pts type devpts (rw,gid=5,mode=620) Tmpfs on/dev/shm type TMPFS (rw)/dev/sda1 on/boot t  Ype ext4 (rw) None On/proc/sys/fs/binfmt_misc type Binfmt_misc (rw)/dev/mapper/mygroup-mysqldata on/mnt/mydata type Ext4 (rw)

3. Data recovery to LVM volume

To create a data directory and permission settings:

[Email protected] ~]# mkdir/mnt/mydata/data[[email protected] ~]# chown-r mysql:mysql/mnt/mydata/data[[email protecte D] ~]# vim/etc/my.cnf #修改数据目录位置datadir =/mnt/mydata/data[[email protected] ~]# cd/opt/lamp/mysql55/#切换到mysql的安装目录

Initialize the database:

[Email protected] mysql55]# scripts/mysql_install_db--user=mysql--datadir=/mnt/mydata/data

To modify the directory that DataDir points to:

[Email protected] mysql55]# vim/etc/rc.d/init.d/mysqld datadir=/mnt/mydata/data

Starting MySQL, it is strange that I can start normally when I do not modify the DataDir in the mysqld script file:

[[Email protected] mysql55]# service mysqld start

Access MySQL, modify access password import backup data:

mysql> SET PASSWORD for [email Protected]=password (' 123456 ');mysql> FLUSH privileges;mysql> source/backup/  data_dir/fulldata-2015-04-14.sqlmysql> show databases; #数据已恢复 +--------------------+| Database |+--------------------+| Information_schema | | MYDB1 | | MYDB2 | | MySQL | | Performance_schema | | Test |+--------------------+

4. Backing Up the database

4.1. Prepare backup Directory

[[email protected] mysql55]# mkdir-pv/backup/' date +%f '/{data,binlog} #以日期命令一个目录, also create a data and Binlog directory within, For storing data and binary log information, respectively

4.2, modify variables, request full table read lock

mysql> set global sync_binlog=1;  #设置此变量为1 so that each event is synchronized to the binary log file as much as possible to consume IO to ensure data consistency as much as possible mysql>  SHOW MASTER STATUS;  #查看二进制日志和position, this information is stored in/backup/2015-04-14/binlog Binlog.txt +------- -----------+----------+--------------+------------------+| file              | position | binlog_do_db | binlog_ignore_db  |+------------------+----------+--------------+------------------+| mysql-bin.000016 |   1068076 |              |                   |+ ------------------+----------+--------------+------------------+1 row in set  (0.00  SEC) mysql> flush logs;  #刷新日志mysql > FLUSH TABLES WITH READ LOCK;   #读锁请求到后不要关闭此mysql交互Interface 

In the INNODB table, even if a read lock is requested, INNODB may still have transactions in the background for read and write operations, which can be "mysql> SHOW ENGINE INNODB STATUS;" View the status of the background process, and then make a backup without writing the request.

4.2. Create a Snapshot

Create a snapshot volume of 1GB size in a read-only manner Data-snap:

[Email protected] mysql55]# lvcreate--snapshot/dev/mygroup/mysqldata-n data-snap--size 1G--permission R

To view the details of a snapshot volume (the snapshot volume is also LV):

[Email protected] mysql55]# Lvdisplay

4.3. Unlock the Lock

Back to the lock table of the MySQL interactive interface, unlock:

mysql> UNLOCK tables;mysql> SET GLOBAL sync_binlog=0; #此参数可以根据服务器磁盘IO的负载来调整

4.4. Mount the snapshot volume and back up the data

[Email protected] ~]# mount/dev/mygroup/data-snap/tmp/#挂载快照卷 [[email protected] ~]# ls/tmp/data/ibdata1 IB_LOGFILE0 Ib_logfile1 mariadb.pid mydb1 mydb2 mysql performance_schema test[[email protected] mysql55]# Cp-ar/tmp/data/bac kup/2015-04-14/data/#备份数据 [[email protected] mysql55]# ls/backup/2015-04-14/data/data/ibdata1 IB_LOGFILE0 ib_ Logfile1 mariadb.pid mydb1 mydb2 mysql performance_schema test

4.5. Release the snapshot volume

[[email protected] ~]# umount/tmp/[[email protected] ~]# lvremove/dev/mygroup/data-snapdo you really want to remove acti ve logical volume DATA-SNAP? [y/n]: Y Logical Volume "data-snap" successfully removed

5. Data Disaster recovery

Destroy the data and delete all the files in the data directory:

[[email protected] mysql55]# Rm-rf/mnt/mydata/data/*[[email protected] mysql55]# service mysqld Stop #PID文件也被删掉了, does not stop properly Stop service error! MySQL server PID file could not being found! [Email protected] mysql55]# Killall mysqld

Copy the backup data to the data directory:

[Email protected] mysql55]# cp-ra/backup/2015-04-14/data/data/*/mnt/mydata/data/[[email protected] mysql55]# ll/mnt /mydata/data/#确认各文件的权限是否为mysql启动服务: [[email protected] mysql55]# service mysqld startstarting MySQL success!

Check to see if the data has been restored:

mysql> show databases;+--------------------+| database            |+--------------------+| information_schema | |  mydb1              | |  mydb2              | |  mysql              | |  performance_schema | |  test               |+-------- ------------+6 rows in set  (0.00 sec) mysql> select * from  mydb1.tb1;+----+------+------+| id | name | age  |+----+------+------+|   1 | tom  |   10 | |   2 | jack |   20 | |   3 | zcj  |   18 |+----+------+------+ 

6. Summary

The LVM-based data backup scheme is almost a hot spare, but it affects the business of the line when a read lock is requested, and once the read lock request is made, the snapshot is almost instantaneous, and the snapshot volume is created so that it can be unlocked, and the backup is as simple as copying or archiving the file. Data recovery only need to shut down the MySQL process, copy the backup data to the appropriate data directory, ensure that the data directory permissions are correct, enable the service, because it is directly copied data files, so the index does not need to be rebuilt.

This article is from the "knowledge needs summary and records" blog, please be sure to keep this source http://zhaochj.blog.51cto.com/368705/1632736

MySQL backup recovery based on LVM snapshot