Backing up and restoring MARIADB databases using LVM

Introduction to LVM snapshot volumes by comparison with mysqldump

Mysqldump: a hot backup is not possible, the backup time is longer when the database is large, and the advantage is that backup recovery is very simple.

The advantages and disadvantages of LVM are :

Advantages:

1, almost hot standby, only in the time of backup in order to generate data inconsistency problem, need to lock the database.

2, support all storage engines, because the principle of LVM backup is just copy files

3, backup and recovery speed block, because the principle of backup and recovery is just copy files.

Disadvantages:

1, the database must be placed on the LVM Logical Volume

2 . If a user initiates a transaction in a production environment, the process of locking the database may take a long time and will not be able to accurately obtain the database stop time

using snapshot volume backupMariadb

Prerequisites for backup: MySQL data must be placed above the LVS logical volume, otherwise the LVS backup cannot be implemented.

If you use LVS to back up a single database, you need to use the InnoDB storage engine because the InnoDB storage engine is a table file per table.

mariadb[(None)]> SHOW GLOBAL VARIABLES like ' innodb_file_% '; +--------------------------+----------+| variable_name | Value |+--------------------------+----------+|innodb_file_format | Antelope | | Innodb_file_format_check | On | | Innodb_file_format_max | Antelope | | innodb_file_per_table | On |+--------------------------+----------+

Backup six-Step walk:

1. Request Lock All tables:

The main purpose is to prevent users from modifying the data during the backup process, causing inconsistencies between the backup and the original data, locking the table before the backup, preventing user modification, and unlocking after the backup is complete.

mariadb[(None)]> FLUSH TABLES with READ LOCK;

Description: If a user initiates a transaction in a production environment, the process of locking the database may take a long time.

2. Record the binary log file and event location:

After the backup is complete, scroll through the log, and then record the location of the log file.

MariaDB [(none)]> flush logs; MariaDB [(None)]> Show Master status;+------------------+----------+--------------+------------------+| File | Position | binlog_do_db | binlog_ignore_db |+------------------+----------+--------------+------------------+|      mysql-bin.000002 |              245 |                  | |+------------------+----------+--------------+------------------+1 row in Set (0.00 sec)

3. Create a snapshot:

Create a snapshot volume, which uses an additional startup terminal, because once the database exits, the lock is released.

[[email protected] ~]# lvcreate-l 200m-n mydata-snap-s-P R/dev/myvg/mydata

4. Release Lock:

mariadb[(None)]> UNLOCK TABLES;

5, mount the snapshot volume, copy data for backup;

Mount a Snapshot volume

[[email protected] ~]# mkdir/snap[[email protected] ~]# mount/dev/myvg/mydata-snap/snap/[[email protected] ~]# ls/snap/ data/aria_log.00000001 ibdata1 mariadb.err mysql-bin.000001 performance_schemaaria_log_control ib_logfile0 Mariad B.pid mysql-bin.000002 testhellodb ib_logfile1 MySQL Mysql-bin.index

Copy data for backup

Just back up a single library using

[Email protected] ~]# rsync-a/snap/data/hellodb//backup/hellodb-' Date +%f-%h-%m-%s ' [[email protected] ~]# ls/backup/ Hellodb-2015-05-28-02-57-12/hellodb/classes.frm coc. MYD courses. MYI scores. MYI tb1.frm Toc.frmclasses.MYD coc. MYI db.opt students.frm teachers.frm TOC. Mydclasses.myi courses.frm scores.frm students. MYD teachers. MYD TOC. MYICOC.FRM courses. MYD scores. MYD students. MYI teachers. MYI

Backing up the entire library

[Email protected] ~]# rsync-a/snap/data/*/backup/mariadb-all-' Date +%f-%h-%m-%s ' [[email protected] ~]# ls/backup/ mariadb-all-2015-05-28-02-57-51/aria_log.00000001 ibdata1 mariadb.err mysql-bin.000001 performance_schemaaria_log_ Control Ib_logfile0 mariadb.pid mysql-bin.000002 testhellodb ib_logfile1 MySQL Mysql-bin.index

6, after the backup is complete, delete the snapshot volume;

[[email protected] ~]# umount/snap/[[email protected] ~]# lvremove/dev/myvg/mydata-snapdo you really want to remove acti ve logical Volumemydata-snap? [y/n]: Y

Analog Accidental deletion

Some data has been modified since the backup was completed

MariaDB [hellodb]> drop table tb1; MariaDB [hellodb]> CREATE TABLE TB2 (id int); MariaDB [hellodb]> INSERT INTO TB2 values (1), (2), (3); MariaDB [hellodb]> SELECT * FROM tb2;+------+|    ID |+------+|    1 | |    2 | | 3 |+------+

3 Rows in Set (0.00 sec)

the hellodb database was mistakenly deleted after the modification was completed.

MariaDB [hellodb]> drop Database hellodb;

And then the entire database was mistakenly deleted (note: If the production environment binary log files and data files must be stored separately, but when the experiment is installed in the binary log files and data directory together, you should first copy a binary log file to the backup directory)

[Email protected] ~]# rm-rf/mydata/data/*

Database Failure Recovery

Mysql is a big failure, it is best to stop the database, if you can not stop the process of killing

[[Email protected] ~]# service mysqld stopmysql server PID file could not being found! [FAILED] [Email protected] ~]# killall Mysqldmysqld:no process killed

All operations related to the HELLODB database are exported from the binary log file .

Remove the DELETE statement before exporting to see where the statement is recorded in the binary log file

MariaDB [(None)]> show Binlog events in ' mysql-bin.000002 ' \g;*************************** 7.       row*************************** log_name:mysql-bin.000003 pos:642 event_type:query server_id:1end_log_pos:729 Info:drop database hellodb7 rows in Set (0.00 sec)

After the statement record location is obtained, the record deletion location is not exported

[Email protected] ~]# Mysqlbinlog--stop-position=729/mydata/data/mysql-bin.000002 >/backup/lvm.sql

Copy all backed-up files and directories to the data directory

[[email protected] ~]# cp -a /backup/mariadb-all-2015-05-28-02-57-51/*/mydata/data/[[ EMAIL PROTECTED] ~]# LL /MYDATA/DATA/TOTAL 28764-RW-RW---- 1 mysql  MYSQL    16384 MAY 27 22:30 ARIA_LOG.00000001-RW-RW---- 1  Mysql mysql       52 may 27 22:30 aria_log_ CONTROLDRWX------ 2 mysql mysql     4096 may 28 00:49  HELLODB-RW-RW---- 1 mysql mysql 18874368 May 28  00:49IBDATA1-RW-RW---- 1 mysql mysql  5242880 may 28 00:49 ib_ LOGFILE0-RW-RW---- 1 mysql mysql  5242880 may 27 22:34 ib_ Logfile1-rw-r----- 1 mysql root      5674 May 28  00:43 MARIADB.ERR-RW-RW---- 1 mysql MYSQL        5MAY 28 00:43 MARIADB.PIDDRWX------ 2  MYSQL ROOT      4096 MAY 27 22:30 MYSQL-RW-RW----  1 mysql mysql      632 May 27 23:12  MYSQL-BIN.000001-RW-RW---- 1 mysql mysql     8667 may 27  23:22 MYSQL-BIN.000002-RW-RW---- 1 mysql mysql       114 MAY 28 00:50 MYSQL-BIN.INDEXDRWX------ 2 mysql mysql      4096 MAY 27 22:30 PERFORMANCE_SCHEMADRWX------ 2 mysql root       4096 may 27 22:30 test

this time start mysqld The service can see the data before the backup

[[Email protected] ~]# mysql -u root -pmariadb [(None)]> use  Hellodb; mariadb [hellodb]> show tables;+-------------------+| tables_in_hellodb |+-------- -----------+| classes          | |  coc              | |  courses          | |  scores           | |  students         | |  tb1              | |  teachers         | |  toc              |+--------------- ----+mariadb [hellodb]> select * from tb1;+------+| ID&NBsp;  |+------+|    1 | |     2 | |     3 | |    21 | |    22 | |    23 |+------+6 rows in set  (0.01 SEC)

The recovered data is just the data before the backup, the changed data after the backup has not returned, this time need to import the binary log converted into SQL file

[Email protected] ~]# mysql-u root-p </backup/lvm.sql

look at the data and you'll see tb2 .

mariadb [hellodb]> show tables;+-------------------+| tables_in_hellodb |+-------- -----------+| classes          | |  coc              | |  courses          | |  scores           | |  students         | |  tb2               | |  teachers         | |  toc              |+--------------- ----+mariadb [hellodb]> select * from tb2;+------+| id   |+--- ---+|    1 | |     2 | |     3 |+------+ 

This article from "Plum blossom fragrance from bitter cold" blog, please be sure to keep this source http://ximenfeibing.blog.51cto.com/8809812/1664768

Backing up and restoring MARIADB databases using LVM