Shulou(Shulou.com)06/01 Report--

This article is mainly about what the physical backup lvm-snapshot of MySQL refers to. If you are interested, let's take a look at this article. I believe it is of some reference value to everyone after reading what the physical backup lvm-snapshot of MySQL refers to.

Lvm-snapshot (tool backup)

Advantages:

Almost hot standby (lock the table before wearing a snapshot and release immediately after creation)

Support for all engines

Backup speed is fast

There is no need to use expensive commercial software (it is operating system level)

Disadvantages:

May require cross-departmental collaboration (using operating system-level commands, DBA generally does not have permissions)

Unable to predict service stop time

It is troublesome for data to be distributed across multiple volumes (for storage level)

Principle of logical volume snapshot

Why choose lvm snapshot backup?

Reason: because the time of locking the table is inconsistent, it cannot be written at the moment of locking, so make a snapshot backup and unlock it immediately after the backup is completed, and then the service can use it normally (write and other operations), such as when the amount of data is large. Take a snapshot at once, and then unlock it immediately, which will not affect the write and other operations. If you use mysqldump backup, then when the amount of data is large, the table will be locked for a long time, which will affect the efficiency.

Operation flow

Flush table with read locak; 2, create snapshot 3, show master status; show slave status; [optional] 4, unlock tables;5, Copy files from the snapshot6, Unmount the snapshot. 7. Remove snapshot snapshot backup: 1. Migrate data to logical volumes (not required, as the case may be) 2. Lock the watch (time) 3. Take a snapshot of the logical volume where the database is located. Unlock 5. Mount the snapshot to the temporary mount point 6. Copy all the data on the snapshot to the corresponding backup directory (different hosts) 7. Unmount the snapshot and delete

Example of lvm backup

1. Data migration to logical volumes

Environment: the data file is not on the logical volume, so you need to migrate the data file to the logical volume

1. Create a logical volume

[root@Admin ~] # pvcreate / dev/sdb [root@Admin ~] # vgcreate vg01 / dev/sdb [root@Admin ~] # lvcreate-n lv_mysql-L 4G vg01 [root@Admin ~] # mkfs.ext4 / dev/mapper/vg01-lv_mysql

2. Migrate the current mysql database to logical volumes

1 > stop the application 2 > stop the mysql service [root@Admin ~] # service mysqld stop3 > back up all the data files to the specified place [root@Admin ~] # tar-czvf / tmp/backmysql/mysql.tar.gz / data/DB/*4 > mount the logical volume to the current mysql data directory [root@Admin ~] # mount / dev/mapper/vg01-lv_mysql / data/DB/5 > extract the data just backed up to the data directory [ Root@Admin ~] # tar xf / tmp/backmysql/mysql.tar.gz-C / data/DB/ [root@Admin ~] # mv / data/DB/data/DB/* / data/DB/ & & rm-rf / data/DB/data/6 > start the database [root@Admin ~] # service mysqld start where the cause of startup failure / the authority of the data/DB/ data directory becomes root Change permissions to restart [root@Admin ~] # chown mysql. -R / data/DB/ & & service mysqld start

Second, snapshot backup database

1. Add a read lock to the database

Mysql > flush table with read lock

2. Create a snapshot of the logical volume where mysql's database resides

[root@Admin ~] # lvcreate-n lv_mysql_s- L 50m-s / dev/vg01/lv_ MySQL [root @ Admin ~] # dmsetup-- tree lsvg01-lv_mysql (253├─ 0) └─ vg01-lv_mysql-real (253├─ 1) └─ (8:16) vg01-lv_mysql_s (253├─ ├─ 3) │ └─ (8:16) └─ vg01-lv _ mysql-real (253 1) └─ (8:16)

3. Unlock the database

[root@Admin ~] # unlock tables

The above 1-3 steps can be merged into one step

[root@Admin ~] # echo "flush tables with read lock; system lvcreate-n lv_mysql_s-L 50m-s / dev/vg01/lv_mysql;unlock tables;" | mysql-p123

4. Mount the snapshot to a temporary directory

[root@Admin] # mkdir / mnt/mysql & & mount / dev/vg01/lv_mysql_s / mnt/mysql/

5. Backup data

[root@Admin ~] # ls / mnt/mysql/ # you can see the data Admin.pid db01 ib_logfile0 mysql mysql-bin.000003 mysql-bin.000006 mysql-bin.000009 performance_schemaauto.cnf db02 ib_logfile1 mysql-bin.000001 mysql-bin.000004 mysql-bin.000007 mysql-bin.000010 testbinlog ibdata1 login mysql-bin.000002 mysql-bin.000005 mysql-bin.000008 mysql- in the new mount directory Bin.index [root@Admin ~] # mkdir / backup & & rsync-av / mnt/mysql / backup

6. Unmount the snapshot and delete

[root@Admin] # umount / mnt/mysql/ & & lvremove / dev/vg01/lv_mysql_s

7. Test and verify (delete everything in the data directory) and then restore the backed-up data directory

1 > Let's be ruthless and delete the data directory / data/DB/ of mysql directly. [root@Admin ~] # rm-rf / data/DB/* & & ls / data/DB/ 2 > after deletion, you can see that restart mysql directly reported an error [root@Admin ~] # service mysqld restart MySQL server PID file could not be found! [failure] Starting MySQL...The server quit without updating PID file [failure] / DB/Admin.pid). 3 > restore [root@Admin ~] # mv / backup/mysql/* / data/DB/ [root@Admin ~] # ls / data/DB/ auto.cnf db02 ib_logfile1 mysql-bin.000001 mysql-bin.000004 mysql-bin.000007 mysql-bin.000010 test binlog ibdata1 login mysql-bin.000002 mysql-bin.000005 mysql-bin.000008 mysql-bin according to the data backed up in / backup above. Index db01 ib_logfile0 mysql mysql-bin.000003 mysql-bin.000006 mysql-bin.000009 performance_schema 4 > restart [root@Admin ~] # chown mysql. / data/DB/-R [root@Admin ~] # service mysqld restart here permissions have changed. If startup still reports an error, check to see if the mysql process still exists. If it does, kill it and restart it. OK [root@Admin ~] # mysql-p123 mysql > show databases +-+ | Database | +-+ | information_schema | | binlog | | db01 | | db02 | | login | | mysql | | performance_schema | | | test | +-+ rows in set (0.00 sec) |

Organize the above backup into script + Crontab scheduled tasks to complete the backup on a regular basis

#! / bin/bash#LVM BackMysqlback_dir=/backup/ `date +% F` [- d $back_dir] mkdir-p $back_direcho "flush tables with read lock; system lvcreate-n lv_mysql_s-L 50m-s / dev/vg01/lv_mysql;unlock tables;" | mysql-p123mount / dev/vg01/lv_mysql_s / mnt/mysql/rsync-a / mnt/mysql/ $back_dirif [$?-eq 0]; then umount / mnt/mysql/ & & lvremove-f / dev/vg01/lv_mysql_sfi

Automatic implementation of snapshot backup mylvmbackup

1. Install the appropriate software

2. Two backup methods

1 > mylvmbackup xxx terminal backup

2 > modify the configuration file to specify the corresponding parameters

Install MySQL:1 normally. Install system 2. Prepare LVM, for example / dev/vg_back/lv-mysql,mount / usr/local/mysql3. Source code installation MySQL to / usr/local/mysql optional operation: migrate the current data to LVM1. Prepare lvm and file system # lvcreate-L 2G-n lv-mysql vg_back# mkfs.ext4 / dev/vg_back/lv-mysql 2. Migrate data to LVM# service mysqld stop# mount / dev/vg_back/lv-mysql / u01 / / temporary mount point # rsync-va / usr/local/mysql/ / u01 / / mirror MySQL raw data to temporary mount point # umount / u01 usr/local/mysql/ # mount / dev/vg_back/lv-mysql / usr/local/mysql/ / join fstab boot mount # df-Th/dev/mapper/vg_back-lv-mysql ext4 2.0G 274M 1. 7G 15% / usr/local/mysql# service mysqld start manually based on LVM snapshot to achieve backup: 1. Lock mysql > flush table with read lock;2. Create a snapshot # lvcreate-L 500m-s-n lv-mysql-snap / dev/vg_back/lv-mysql# mysql- uroot-P123-e'show master status' > / backup/ `date +% F` _ position.txt or mysql > show master status +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +- -+ | mysqld-bin.00003 | 135 | +- -+ 3. Release lock mysql > unlock tables;4. Back up # mount-o ro / dev/vg_back/lv-mysql-snap / u01 pick # mkdir / backup/ `date +% F` # rsync-a / u01 / / backup/2015-07-02 pick 5 from the snapshot. Remove snapshot # umount / u01back_dirmysql # lvremove-f / dev/vg_back/lv-mysql-snap script + Cron completed: #! / bin/bash#LVM backmysql...back_dir=/backup/ `date +% F` [- d $back_dir] | | mkdir-p $back_dirmysql-uroot-p123-e'flush table with read lock'lvcreate-L 500m-s-n lv-mysql-snap / dev/vg_back/lv-mysqlmysql-uroot-p123-e'show master status' | grep mysql > $back _ dir/position.txtmysql-uroot-P123-e'flush logs'mysql-uroot-P123-e'unlock tables'mount-o ro / dev/vg_back/lv-mysql-snap / u01rsync-a / u01 / $back_dirif [$?-eq 0] Thenumount / u01/lvremove-f / dev/vg_back/lv-mysql-snapfi===mylvmbackup function: use LVM snapshot to achieve physical backup, that is, the automatic version of LVM snapshot backup installs perl module 1. To install http://www.lenzg.net/mylvmbackup online, it depends on the perl module. You can install perl-MCPAN-e 'install Config::IniFiles'2 with the following command. Offline installation # rpm-ivh mylvmbackup-0.16-0.noarch.rpmwarning: mylvmbackup-0.16-0.noarch.rpm: Header V4 DSA/SHA1 Signature Key ID b27291f2: NOKEYerror: Failed dependencies:perl (Config::IniFiles) is needed by mylvmbackup-0.16-0.noarchperl (Date::Format) is needed by mylvmbackup-0.16-0.noarchperl (File::Copy::Recursive) is needed by mylvmbackup-0.16-0.noarch solution: # yum-y localinstall atrpms-77-1.noarch.rpm perl-File-Copy-Recursive-0.38-1.el6.rfx.noarch.rpm perl-IO-stringy-2.110-1.2.el6 .rfx.noarch.rpm perl-Config-IniFiles-2.56-1.el6.rf.noarch.rpm installs mylvmbackup package # yum-y install mylvmbackup-0.15-0.noarch.rpm solution dependency perl-TimeDate backup method 1: # mylvmbackup--user=root-- password=123-- host=localhost-- mycnf=/etc/my.cnf-- vgname=vg_back-- lvname=lv-mysql-- backuptype=tar-- lvsize=100M-- backupdir=/backup# tar xf backup-20140903_000236_mysql.tar.gz# lsbackup backup -cnf-20150702_000236_mysqlbackup-20150702_000236_mysql.tar.gz backup-pos backup method 2: # vim / etc/mylvmbackup.conf [mysql] # connection database configuration user=rootpassword=123456host=localhostport=3306socket=/tmp/mysql.sockmycnf=/etc/my.cnf [lvm] # LVM logical volume configuration vgname=vg_server # volume group name lvname=lv_mysql # logical volume name backuplv=mysql_snap # Snapshot volume name lvsize=500M [fs] # File system configuration xfs=0mountdir=/var/tmp/mylvmbackup/mnt/ # Mount directory backupdir=/backup # backup directory You can also back up to the travel host [misc] # define backup options backuptype=tar # define the type of backup backupretention=0prefix=backup # define the backup file name prefix suffix=_mysql # define the backup file name suffix tararg=cvf # define the tar parameter Default is cvftarfilesuffix=.tar.gz # define backup file suffix format datefmt=%Y%m%d_%H%M%S # define backup file name timestamp format keep_snapshot=0 # whether to retain snaphotkeep_mount=0 # whether to uninstall snaphotquiet=0 # define logging type Note: other configurations keep input and then directly execute mylvmbackup to mylvmbackup reference example

What are the details of the above physical backup lvm-snapshot of MySQL? is it helpful to you? If you want to know more about it, you can continue to follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.