Backup MySQL database with snapshot mode and exampleAuthor: Red Bean Kill Date: March 17, 2011 Comments 7 Comments View comments
First, create a logical volume
Create a logical volume according to the attached document as followsHTTP://WWW.178-GO.COM/TAGS/LVMRequirements: Physical volume 20G, Volume group name vg0, logical Volume 2G, logical volume named MyData Create Logical Volume mount point, and Mount the # mkdir/mydata# Mount/dev/vgo/mydata/mydata second, Install MySQL database if the machine is already loaded with MySQL, then this step can be omitted just modify the MySQL configuration file in the DataDir value to/mysata and then copy the original database data files all to/mydata file, that is, the Transfer Database location modified/ MyData directory and its contents the genus group then restart the MYDQL service if you need to reinstall, you can install it according to the following documentation:http://www.178-go.com/archives/mysql.html
Once the installation is complete, simply change the value of DataDir in the MySQL configuration file to the/mydata operation as follows: # sed-i ' s/^datadir.*$/datadir=\/mydata/g '/etc/my.cnf# service mysqld Restart modify/mydata directory and its contents belong to the main group # chown-r mysql:mysql/mydata/*# chown mydql:mysql/mydql start MySQL Service # service mysqld START III, create Snapshot lock All Tables first # Mydql>flush TABLES with READ lock;>\q Create snapshot # pvvreat-l 20m-s-N mysnap/dev/vgo/mydata Note: This command is explained in detail in the following documents:http://www.178-go.com/archives/lvm.htmlUnlock Table # Mysql>unlock tables;>\q Create a snapshot mount point and mount it # mkdir/mysnap# mount/dev/vgo/mysnap# ls found at this time/mysnap directory file and/ MyData files under the same time, any changes made on the original volume will not affect the files in/mysnap. Test to make some changes to the database # Mysql>create DB mydb;>use mydb;>create TABLE sanptest (>name char (+), >id int); >\q now copy the MyDB file from the snapshot directory to a different directory for physical backup note: The entire database should be backed up in production applications # cd/mysanp# CP-RP m ydb/tmp/unmount the snapshot volume # umount/mysnap# lvremove/dev/vgo/mysnap the database to do some delete operation # Mydql>flush Logs;>drop database mydb;> \q at this time, there is no MyDB file under the/mydata directory now copy the MyDB file from the physical backup back # copy-rp/tmp/mydb/mydata# service mysqld Restart view Database # Mysql>show DATABASES the same as the database before the change, there is no mydb library just created, that is, the snapshot backup only saves the data at the moment of the snapshot.
At this point, if you want to retrieve the data lost after the snapshot, just need to use
Binary Log
View binary Log # mysql>show BINLOG EVENTS in ' mysql-bin.0000x '; here x is data, numbers are different, log content is not the same, the larger the number the more close to now these logs are displayed under the/mydata file roughly as follows:
If we want to restore an insert operation from the binary log file mysql-bin.0000013, we can use the following command: # mysqlbinlog–start-position 106–end-position 220 mysql-bin.0000013 >/tmp/a.mysql# mysql-uroot-p </tmp/a.sql show the results as follows:
