MySQL management-almost Hot Backup Based on LVM

Implementation of almost hot standby MySQL based on LVM

Almost Hot Standby:

For mysql, if we want to create a snapshot using lvm, the innodb Transaction Log content may be written to the disk at the moment the snapshot is created, therefore, we must ensure that the transaction log and data file must be on the same snapshot. If we create a snapshot for the question or path separately, it is likely that the data is inconsistent with the time point, therefore, it cannot be recovered even if it is backed up.

 

Preparations

Export the entire data, just in case that the data is not stored on a logical volume, then export the data to backup and delete the data directory, and create a partition using lvm and create a new one, overwrite the data to the initial database under/mydata/data.

 

Before creating an lvm, you must back up all mysql Data and restore it after creating an lvm.

[Root @ test ~] # Mysqldump-uroot -- lock-all-tables -- all-databases -- events>/tmp/alldb. SQL

Delete the data directory and create a logical volume

[Root @ test ~] #/Etc/init. d/mysqld stop

Shutting downMySQL... SUCCESS!

[Root @ test ~] # Rm-fr/mydata/

Partition first, skip this step

Device Boot Start End Blocks Id System

/Dev/sdb1 1 393 3156741 8e Linux LVM

/Dev/sdb2 394 786 3156772 + 8e Linux LVM

Create LVM

Root @ test ~] # Pvcreate/dev/sdb

Sdb sdb1 sdb2

[Root @ test ~] # Pvcreate/dev/sdb {1, 2}

Physical volume "/dev/sdb1" successfully created

Physical volume "/dev/sdb2" successfully created

[Root @ test ~] # Vgcreate myvg/dev/sdb {1, 2}

Volume group "myvg" successfullycreated

[Root @ test ~] # Lvcreate-L 3G-n mydata myvg

Logical volume "mydata" created

[Root @ test ~] # Mke2fs-t ext4-l mydata/dev/

Add lvm to startup Item

[Root @ test ~] # Echo 'label = MYDATA/mydata ext4 defaults0 0'>/etc/fstab

[Root @ test ~] # Mount-

[Root @ test ~] # Df-h

Filesystem Size Used Avail Use % Mounted on

/Dev/sda3 6.3 GB 4.2G 1.8G 72%/

Tmpfs 245 M 0 245 M 0%/dev/shm

/Dev/sda1 194 M 28 M 156 M 16%/boot

/Dev/mapper/myvg-mydata

3.0G 69 M 2.8G 3%/mydata

Create a data directory and assign a value again

[Root @ test ~] # Mkdir/mydata/data/

[Root @ test ~] # Chown mysql. mysql-R/mydata/

Initialize and start mysql

[Root @ test ~] # Cd/usr/local/mysql

[Root @ test mysql] # scripts/mysql_install_db -- user = mysql -- datadir =/mydata/data/

 

[Root @ test mysql] #/etc/init. d/mysqld start

Starting MySQL... SUCCESS!

 

Import Data

First, we need to disable binlog so that the recovery operation is not recorded in the binary log file.

Mysql> setsession SQL _log_bin = 0;

Query OK, 0 rowsaffected (0.00 sec)

Import Database

Mysql> source/tmp/alldb. SQL;

Mysql> showdatabases;

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

| Database |

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

| Information_schema |

| Mydb |

| Mysql |

| Performance_schema |

| Test |

| Wpdb |

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

6 rows in set (0.00sec)

At this time, our binary log file can be enabled, because it is the current session settings, you can exit directly before entering

Mysql> setsession SQL _log_bin = 1;

Query OK, 0 rowsaffected (0.00 sec)

 

Achieve almost hot backup

From the moment a snapshot is created, the data must not be modified. Therefore, to implement lvm backup, you must open a remote mysql session to lock all tables and record the binary location;

If a transaction is being executed on the mysql database, the request lock may wait for a period of time. We do not know how long it will take. We may not know how long it takes to wait for half an hour to an hour, but the steps are indispensable.

Once the lock is detected, start the terminal and use the lvcreate command to create a snapshot for the volume where the data is located.

For mysql, if we want to create a snapshot using lvm, the innodb Transaction Log content may be written to the disk at the moment the snapshot is created, therefore, we must ensure that the transaction log and data file must be on the same snapshot. If we create a snapshot for the question or path separately, it is likely that the data is inconsistent with the time point, therefore, it cannot be recovered even if it is backed up.

1 2 Next Page