Use LVM to back up and restore the MariaDB Database

Use LVM to back up and restore the MariaDB Database

Comparison with mysqldump to introduce LVM snapshot volumes
Mysqldump: Hot Backup cannot be performed, and the backup time is long when the database is large. The advantage is that the Backup recovery is very simple.
The advantages and disadvantages of LVM are as follows:
Advantages:
1. Almost hot backup. The database needs to be locked in order to generate data inconsistency during Backup.
2. All storage engines are supported because LVM only Copies files.
3. backup and recovery speed blocks, because the principle of backup and recovery is to copy files.
Disadvantages:
1. The database must be placed on the LVM logical volume.
2. If a user starts a transaction in the production environment, it may take a long time to lock the database, and the database stop time cannot be obtained accurately.
 
Back up Mariadb using snapshot volumes
Prerequisites for backup: mysql data must be stored on the LVS logical volume; otherwise, 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 for each 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 |
+ -------------------------- + ---------- +

Step 6 of backup:
1. Request to lock all tables:
The main purpose is to prevent the user from modifying data during the backup process, resulting in inconsistency between the backup and the original data. You need to lock the table before the backup to prevent the user from modifying the data and unlock the table after the backup is complete.
MariaDB [(none)]> flush tables with read lock;

Note: If a user starts a transaction in the production environment, it may take a long time to lock the database.
2. Record binary log files and event locations:
After the backup is complete, scroll the log and 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. Here, an additional terminal is used, because the lock is released once the database exits.
[Root @ MariaDB ~] # Lvcreate-L 200 M-n mydata-snap-s-p r/dev/myvg/mydata

4. Release the lock:
MariaDB [(none)]> unlock tables;

5. Mount the snapshot volume and copy the data for backup;
Mount snapshot volumes
[Root @ MariaDB ~] # Mkdir/snap
[Root @ MariaDB ~] # Mount/dev/myvg/mydata-snap/
[Root @ Mariadb ~] # Ls/snap/data/
Aria_log.00000001 ibdata1 Mariadb. err mysql-bin.000001 performance_schema
Aria_log_control ib_logfile0 Mariadb. pid mysql-bin.000002 test
Hellodb ib_logfile1 mysql mysql-bin.index.

Copy Data for backup
Only used to back up a single database
[Root @ MariaDB ~] # Rsync-a/snap/data/hellodb // backup/hellodb-'date + % F-% H-% M-% s'
[Root @ Mariadb ~] # Ls/backup/hellodb-2015-05-28-02-57-12/hellodb/
Classes. frm coc. MYD courses. MYI scores. MYI tb1.frm toc. frm
Classes. MYD coc. MYI db. opt students. frm teachers. frm toc. MYD
Classes. MYI courses. frm scores. frm students. MYD teachers. MYD toc. MYI
Coc. frm courses. MYD scores. MYD students. MYI teachers. MYI

Back up the entire database
12345 [root @ MariaDB ~] # Rsync-a/snap/data/*/backup/mariadb-all-'date + % F-% H-% M-% s'
[Root @ Mariadb ~] # Ls/backup/mariadb-all-2015-05-28-02-57-51/
Aria_log.00000001 ibdata1 Mariadb. err mysql-bin.000001 performance_schema
Aria_log_control ib_logfile0 Mariadb. pid mysql-bin.000002 test
Hellodb ib_logfile1 mysql mysql-bin.index.

6. After the backup is complete, delete the snapshot volume;
[Root @ Mariadb ~] # Umount/snap/
[Root @ Mariadb ~] # Lvremove/dev/myvg/mydata-snap
Do you really want to remove active logical volumemydata-snap? [Y/n]: y

Accidental deletion
After the backup is complete, some data is modified.
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)
After modification, the hellodb database is deleted by mistake.
MariaDB [hellodb]> drop database hellodb;

Then, the entire database is deleted by mistake (Note: if the binary log files and data files are stored separately in the production environment, however, if the binary log file is in the same directory as the data directory during the experiment installation, copy the binary log file to the backup directory first)
[Root @ MariaDB ~] # Rm-rf/mydata/data /*

Database fault recovery
When a major Mysql fault occurs, it is best to stop the database and kill the process if it cannot be stopped.
[Root @ MariaDB ~] # Service mysqld stop
MySQL server PID file cocould not be found! [FAILED]
[Root @ MariaDB ~] # Killall mysqld
Mysqld: no process killed

Export all operations related to the hellodb database from the binary log file.
Remove the accidental deletion statement before exporting the statement to view the record location of the statement 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: 1
End_log_pos: 729
Info: drop database hellodb
7 rows in set (0.00 sec)

After obtaining the statement record location, do not export the record deletion location
[Root @ Mariadb ~] # Mysqlbinlog -- stop-position = 729/mydata/data/mysql-bin.000002>/backup/lvm. SQL

Copy all backup files and directories to the data directory
[Root @ MariaDB ~] # Cp-a/backup/mariadb-all-2015-05-28-02-57-51/*/mydata/data/
[Root @ MariaDB ~] # Ll/mydata/data/
Total 28764
-Rw ---- 1 mysql 16384 May 27 aria_log.00000001
-Rw ---- 1 mysql 52 May 27 aria_log_control
Drwx ------ 2 mysql 4096 May 28 hellodb
-Rw ---- 1 mysql 18874368 May 28 00: 49ibdata1
-Rw ---- 1 mysql 5242880 May 28 ib_logfile0
-Rw ---- 1 mysql 5242880 May 27 ib_logfile1
-Rw-r ----- 1 mysql root 5674 May 28 MariaDB. err
-Rw ---- 1 mysql 5May 28 MariaDB. pid
Drwx ------ 2 mysql root 4096 May 27 mysql
-Rw ---- 1 mysql 632 May 27 mysql-bin.000001
-Rw ---- 1 mysql 8667 May 27 mysql-bin.000002
-Rw ---- 1 mysql 114 May 28 mysql-bin.index
Drwx ------ 2 mysql 4096 May 27 performance_schema
Drwx ------ 2 mysql root 4096 May 27 test

Start the mysqld service to view the data before the backup.
[Root @ MariaDB ~] # Mysql-u root-p
MariaDB [(none)]> use hellodb;
MariaDB [hellodb]> show tables;
+ ------------------- +
| Tables_in_hellodb |
+ ------------------- +
| Classes |
| Coc |
| Courses |
| Scores |
| Students |
| Tb1 |
| Teachers |
| Toc |
+ ------------------- +
MariaDB [hellodb]> select * from tb1;
+ ------ +
| Id |
+ ------ +
| 1 |
| 2 |
| 3 |
| 21 |
| 22 |
| 23 |
+ ------ +
6 rows in set (0.01 sec)

The recovered data is only the data before the backup, and the changed data after the backup has not been returned. At this time, you need to import the SQL file converted from the binary log.
[Root @ MariaDB ~] # Mysql-u root-p </backup/lvm. SQL

View the data to 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 |
+ ------ +

Install LAMP (Apache with MariaDB and PHP) in CentOS/RHEL/Scientific Linux 6)

Implementation of MariaDB Proxy read/write splitting

How to compile and install the MariaDB database in Linux

Install MariaDB database using yum in CentOS

Install MariaDB and MySQL

How to migrate MySQL 5.5 database to MariaDB 10 on Ubuntu

Install MariaDB on the Ubuntu 14.04 (Trusty) Server