MySQL 5.5 Backup tool LVM

MySQL's first backup tool mysqldump, we have talked to you in the last article, if you still have doubts, please click Mysqldump

MySQL's second backup tool LVM snapshot, so today we'll talk about it. LVM Snapshot Volume Win Bei

Mysqldump is able to implement hot standby for InnoDB storage engine, but it can't implement hot standby for MyISAM engine;

And today we're going to introduce LVM to achieve almost hot provisioning of the MyISAM engine

When you use LVM snapshots for backup of MySQL, the following conditions are required:

1. The data file to be on the logical volume

2. The volume group that contains this logical volume must have sufficient space to use the snapshot volume

3. Data files and transaction logs to be on a logical volume

The process of implementing an LVM snapshot for MySQL backup

1. Open session, apply read lock, lock all tables mysql> flush TABLES with read lock;mysql> flush logs;2, through another terminal, save binary log files and related location information; # MYSQL-E ' Show Mater status ' >/proc/master.info3, create snapshot Volume # lvcreate-l #-s-p r-n lv_name/path/to/source_lv4, Release lock mysql> UNLOCK tabl Es;5, hanging on snapshot volume, backup # mount# CP-A6, deleting snapshot volume and useless data # Lvremove--force lv_name# rm-rf mysql-bin.*7, incremental backup binary log # CP8, failure occurred, recovery data

MySQL Database LVM snapshot backup

1. Open the session, apply a read lock, lock all tables, and refresh the log;

Mysql> flush TABLES with READ lock;mysql> flush LOGS;

2, through another terminal, save the binary log file and information of the Linden;

# Mysql-uroot-p-E ' show Master Status\g; ' >/backup/master-' Date +%f '. info# cat/backup/master-' Date +%f '. Info

3. Create a Snapshot volume

# lvcreate-l 3g-s-P r-n mydata_snap/dev/myvg/mydata

4. Release the lock

Mysql> UNLOCK TABLES;

5. Hanging on snapshot volume, backup

# mkdir/mnt/mydata_snap# mount/dev/myvg/mydata_snap/mnt/mydata_snap/-o ro# mkdir/backup/full-backup-' date +%F ' # cp-a /mnt/mydata_snap/*/backup/full-backup-2015-03-05/

6. Deleting snapshot volumes and useless data

Deleting a snapshot Volume # umount/mnt/mydata_snap/# lvremove--force/dev/myvg/mydata_snap because the log files backed up here love you is useless, so you can delete # rm-rf/backup/ full-backup-2015-03-05/data/mysql-bin.*

7. Incremental backup binary Log

(1) First do some write operations in MySQL database:

mysql> flush logs;mysql> use mydb;mysql>  insert into tutor (tname)  values (' it '); Mysql> insert into tutor (Tname)  values (' 3dmax ');mysql> show master status; #日志文件变化, Data shift change +------------------+------- ---+--------------+------------------+| file              | position | binlog_do_db | binlog_ignore_db |+--------------- ---+----------+--------------+------------------+| mysql-bin.000005 |       584 |              |                   |+------ ------------+----------+--------------+------------------+1 row in set  (0.00 sec) 

(2) Backup incremental log: It is important to note that since I used the flush logs scrolling log, it is theoretically necessary to back up two log incremental data after referencing the LVM snapshot volume backup as mysql-bin.000004 and mysql-bin.000005

(3) Check the log location at the beginning of the backup:

[Email protected] ~]# cat/backup/master-2015-03-05.info *************************** 1. Row ***************************file:mysql-bin.000004position:107binlog_do_db:binlog_ignore_db:

(4) Export log:

# Mysqlbinlog--start-position=107/mydata/data/mysql-bin.000004 >/backup/04.sql# mysqlbinlog/mydata/data/ mysql-bin.000005 >/backup/05.sql

8. Restore the database

1) Analog Damage:

I still use this method of deleting data files and directories directly:

# rm-rf/mydata/data*

2) Restore full backup: direct CP

# cp-a/backup/full-backup-2015-03-05/data/*/mydata/data/

3) Start the MySQL service

# service Mysqld Start

4) Import Incremental backup

# Mysql-uroot-p </backup/04.sql# mysql-uroot-p </backup/05.sql

5) Use LVM Snapshot volume backup considerations: If you need to back up a single library, the InnoDB needs to be set as a stand-alone table space

Set each table to use a table space independently mysql> SHOW GLOBAL VARIABLES like '%innodb_file_per_table% '; +-----------------------+-------+| variable_name | Value |+-----------------------+-------+| innodb_file_per_table | On |+-----------------------+-------+1 row in Set (0.00 sec)

• Personal humble Opinion:

• This kind of hot preparation work I use is not many, may be the company's environment is different! I will introduce you to the third Open Source Backup tool in the next section xtrabackup, this backup tool personally feels very good, in terms of performance far beyond mysqldump, then we chat next.

• Directions for the next few MySQL articles:

  • Third Backup tool Xtrabackup

  • Performance Debugging Tools

  • Pressure test

This article is from the "Zheng" blog, make sure to keep this source http://467754239.blog.51cto.com/4878013/1621252

MySQL 5.5 Backup tool LVM