Lvm-snapshot: LVM-based snapshot backup _ MySQL

Lvm-snapshot: Attention and Please for LVM snapshot-based backup! The beginning

3. LVM-based snapshot backup

Lvm-snapshot: LVM-based snapshot backup

(1) transaction logs and data files must be on the same volume;

(2) before creating a snapshot volume, request the MySQL global lock; release the lock after the snapshot is created;

(3) after the global lock is requested, a log scroll is performed, and binary log files and locations are marked (manually );

3.1 Request global lock and scroll logs

MariaDB [hellodb]> flush tables with read lock;

MariaDB [hellodb]> flush logs;

3.2 view and record binary log files and location tags (manually );

MariaDB [hellodb]> show master status;

+ ------------------ + ---------- + -------------- + ------------------ +

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+ ------------------ + ---------- + -------------- + ------------------ +

| Mysql-bin.000005 | 365 |

+ ------------------ + ---------- + -------------- + ------------------ +

1 row in set (0.00 sec)

In this case, do not exit Mysql. once you exit, the manually applied lock will be automatically released.

Mark binary log files and locations to specified files

# Mkdir/backups

# Mysql-e 'show master status'>/backups/binlog. pos

3.3 create a snapshot volume (-L, specify the snapshot volume size;-n, specify the snapshot volume name;-p, specify the snapshot volume attributes)

# Lvcreate-L 100 M-s-n mydata-snap-p r/dev/myvg/mydata

View lvm volume usage

[Root @ www ~] # Lvs

Lv vg Attr LSize Pool Origin Data % Move Log Cpy % Sync Convert

Mydata myvg owi-aos -- 10.00 GB

Mydata-snap myvg sri-a-s -- 100.00 m mydata 0.01

Root vg0-wi-ao --- 20.00g

Swap vg0-wi-ao --- 2.00g

Usr vg0-wi-ao --- 10.00g

Var vg0-wi-ao --- 20.00g

MariaDB [hellodb]> unlock tables;

Note: After creating a snapshot, you can unlock it from the MariaDB terminal.

(1) mount the snapshot volume in read-only mode to the/mnt Directory

[Root @ www ~] # Mount-o ro/dev/myvg/mydata-snap/mnt/

(2) Check whether the snapshot volume is mounted successfully (that is, check whether there is data backed up earlier under the mount point)

[Root @ www ~] # Cd/mnt/

[Root @ www mnt] # ls

Binlogs data

[Root @ www mnt] # cd data/

[Root @ www data] # ls

Aria_log.00000001 ibdata1 multi-master.info test

Aria_log_control ib_logfile0 mysql www.linuxidc.com. err

Hellodb ib_logfile1 performance_schema www.linuxidc.com. pid

Note that we have released the global lock before, so other users can perform write operations. therefore

What we simulate here is that after we complete the backup, another user performed the write operation experiment environment. Unfortunately, it was not long before we imported the data here, some "2" errors are reversed: the database service is accidentally disabled and all data under/mydata/data/is deleted by mistake.

3.6 import new data to simulate user write operations

MariaDB [mydb]> source/root/mydb. SQL;

3.7 back up the data in the snapshot to a specified location

# Cp-a/mnt/data // backups/data-$ (date + % F)

3.8 after the backup is complete, delete the snapshot volume

# Umount/mnt/unmount the snapshot volume

# Lvremove/dev/myvg/mydata-snap remove a snapshot volume

Recovery: binary logs must be saved. Otherwise, it is difficult to restore binary logs in real time.

4. accidentally stopped the database and deleted all files in the data directory.

[Root @ www ~] # Service mysqld stop

Shutting down MySQL... [OK]

[Root @ www ~] # Rm-rf/mydata/data /*

Fortunately, the binary log is still in progress. Otherwise, it cannot be restored in real time (binary log files are very important and must be backed up frequently)

[Root @ www ~] # Ls/mydata/binlogs/

Mysql-bin.000001 mysql-bin.000003 mysql-bin.000005 mysql-bin.state

Mysql-bin.000002 mysql-bin.000004 mysql-bin.index

After the data in the data directory is accidentally deleted, the data directory is empty.

[Root @ www ~] # Ls/mydata/data/

Restore all data in the data directory.-a keeps attributes such as the user's owner group unchanged.

[Root @ www ~] # Cp-a/backups/data-2014-04-12/*/mydata/data/

[Root @ www ~] # Cd/mydata/data/

Check whether all the data owner groups in the data directory are mysql. if not, modify the settings.

[Root @ www data] # ll

Total 110636

-Rw ---- 1 mysql 16384 Apr 12 aria_log.00000001

-Rw ---- 1 mysql 52 Apr 12 aria_log_control

Drwx ------ 2 mysql 4096 Apr 12 hellodb

-Rw ---- 1 mysql 12582912 Apr 12 ibdata1

-Rw ---- 1 mysql 50331648 Apr 12 ib_logfile0

-Rw ---- 1 mysql 50331648 Apr 12 ib_logfile1

-Rw ---- 1 mysql 0 Apr 12 multi-master.info

Drwx ------ 2 mysql 4096 Apr 12 mysql

Drwx ------ 2 mysql 4096 Apr 12 performance_schema

Drwx ------ 2 mysql 4096 Apr 12 test

-Rw-r ----- 1 mysql root 3660 Apr 12 www.linuxidc.com. err

-Rw ---- 1 mysql 5 Apr 12 www.linuxidc.com. pid

When you confirm that the data directory permission is correct, you can start mysql.

[Root @ www data] # service mysqld start

Check whether all data files have been recovered in the data directory.

[Root @ www data] # ls

Aria_log.00000001 ibdata1 multi-master.info test

Aria_log_control ib_logfile0 mysql www.linuxidc.com. err

Hellodb ib_logfile1 performance_schema www.linuxidc.com. pid

Check whether all data files have been restored in the data

MariaDB [(none)]> show databases;

+ -------------------- +

| Database |

+ -------------------- +

| Hellodb |

| Information_schema |

| Mysql |

| Performance_schema |

| Test |

+ -------------------- +

5 rows in set (0.01 sec)

At this point, the complete backup has been successfully restored, but the mydb. SQL file, generated mydb database, and data tables in the mydb. SQL file, which is imported during user write operations, have not been restored yet.

Since this write operation occurs after our complete backup, we do not have the database and data in the complete backup. Therefore, we can only use the binary log file. The binary log file we used for the last full backup was a mysql-bin.000005, and after we restored the full backup, we restarted the SQL service,

The mysql-bin.000006 binary log file is generated.

[Root @ www data] # ls/mydata/binlogs/

Mysql-bin.000001 mysql-bin.000003 mysql-bin.000005 mysql-bin.index

Mysql-bin.000002 mysql-bin.000004 mysql-bin.000006

View information about the binary log mysql-bin.000005 that we saved before the full backup

[Root @ www data] # cat/backups/binlog. pos

FilePositionBinlog_Do_DBBinlog_Ignore_DB

Mysql-bin.000005365

View the details of the binary log file mysql-bin.000005

# Mysqlbinlog -- start-position = 365/mydata/binlogs/mysql-bin.000005

# At 365

#140412 22:55:48 server id 1 end_log_pos 403 GTID 0-1-2176

/*! 100001 SET @ session. gtid_domain_id = 0 *//*! */;

/*! 100001 SET @ session. server_id = 1 *//*! */;

/*! 100001 SET @ session. gtid_seq_no = 2176 *//*! */;

... Omitted some unimportant information

# At 4857

#140412 22:55:51 server id 1 end_log_pos 4895 GTID 0-1-2200

/*! 100001 SET @ session. gtid_seq_no = 2200 *//*! */;

# At 4895

#140412 22:55:51 server id 1 end_log_pos 5009Querythread_id = 4exec_time = 0error_code = 0

Set timestamp = 1397314551 /*! */;

Drop table if exists 't5 '/* generated by server */

/*! */;

# At 5009

#140412 22:55:51 server id 1 end_log_pos 5047 GTID 0-1-2201

/*! 100001 SET @ session. gtid_seq_no = 2201 *//*! */;

# At 5047

#140412 22:55:51 server id 1 end_log_pos 5245Querythread_id = 4exec_time = 0error_code = 0

Set timestamp = 1397314551 /*! */;

Create table 't5 '(

'Id' int (10) unsigned not null default '0 ',

'Name' char (30) DEFAULT NULL

) ENGINE = InnoDB default charset = latin1

/*! */;

# At 5245

#140412 22:55:51 server id 1 end_log_pos 5283 GTID 0-1-2202

Incremental backup recovery using binary log files

Now we need to import the binary log information to the mysql server.

Method 1:

# Mysqlbinlog -- start-position = 365/mydata/binlogs/mysql-bin.000005>/tmp/incr. SQL

# MysqlSource/tmp/incr. SQL

Method 2:

# Mysqlbinlog -- start-position = 365/mydata/binlogs/mysql-bin.000005 | mysql

Check whether the mydb database has been restored in the database

MariaDB [(none)]> show databases;

+ -------------------- +

| Database |

+ -------------------- +

| Hellodb |

| Information_schema |

| Mydb |

| Mysql |

| Performance_schema |

| Test |

+ -------------------- +

6 rows in set (0.01 sec)

We can see that the mydb database has been successfully restored.

Note: after the database disaster recovery, the first thing we need to do is to make a complete backup of the current database for emergency purposes.

Mylvbackup: perl script to quickly back up mysql based on lvm