How to realize Real-time online backup and recovery in MySQL

Today, I will talk to you about how to achieve real-time online backup and recovery in MySQL. Many people may not know much about it. In order to make you understand better, the editor has summarized the following for you. I hope you can get something according to this article.

1. The main library prepares test data

Mysql > create database cnfol; Query OK, 1 row affected (0.00 sec) mysql > create table cnfol.t (id int primary key); Query OK, 0 rows affected (0.02 sec) mysql > insert into cnfol.t select 1; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql > insert into cnfol.t select 2; Query OK, 1 row affected (0 sec) Records: 1 Duplicates: 0 Warnings: 0

Go to the standby database to confirm:

Mysql > show databases; +-+ | Database | +-+ | information_schema | | cnfol | | mysql | | test | +-+ 4 rows in set (0.00 sec) mysql > select * from cnfol.t +-+ | id | +-+ | 1 | | 2 | +-+ 2 rows in set (0.00 sec)

two。 Add a global read lock to the standby library

Mysql > flush tables with read lock; Query OK, 0 rows affected (0.00 sec)

3. Create a snapshot for the partition where the backup library is located

[root@localhost ~] # lvcreate-- size 1G-- snapshot-- name backup_mysql / dev/vg/mysql Logical volume "backup_mysql" created [root@localhost ~] # lvs LV VG Attr LSize Origin snap% Move Log Copy% Convert backup_mysql vg swi-a- 1.00G mysql 0.00 mysql vg owi-ao 2.00G

4. Get binary log coordinates

In preparing the library:

Mysql > show master status +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +- -+-+ | mysql-bin.000003 | 727 | +- -+ 1 row in set (0.00 sec)

Unlock in the standby library:

Mysql > unlock tables; Query OK, 0 rows affected (0.00 sec)

5. Mount a snapshot

[root@localhost] # mount / dev/vg/backup_mysql / mnt/backup [root@localhost ~] # cd / mnt/backup/mysql/data/cnfol/ & & ls-alh total 32K drwx- 2 mysql dba 4.0K 10-14 09:57. Drwx- 5 mysql dba 4.0K 10-14 09:57.. -rw-rw---- 1 mysql dba 61 10-14 09:57 db.opt-rw-rw---- 1 mysql dba 8.4K 10-14 09:57 t.frm-rw-rw---- 1 mysql dba 14 10-14 09:57 t.MYD-rw-rw---- 1 mysql dba 2.0K 10-14 10:06 t.MYI

6. Misoperation of an inexperienced DBA in the main library

Mysql > drop database cnfol; Query OK, 1 row affected (0.05sec)

Record the time: 2013-10-14 10:17:10

The standby database confirms whether the library cnfol exists:

Mysql > show databases; +-+ | Database | +-+ | information_schema | | mysql | | test | +-+ 3 rows in set (0.01 sec)

7. Backup snapshot

[root@localhost backup] # pwd / mnt/backup [root@localhost backup] # tar-jcv-f / mnt/snapshot/mysql.tar.bz2 *

There are two reasons for backup here * first, expensive IO, because the head has to run back and forth between the snapshot area and the system area * second, there is not enough space in the snapshot area, because it is the COW principle.

8. Delete snapshot

[root@localhost ~] # umount / mnt/backup [root@localhost ~] # lvremove-- force / dev/vg/backup_mysql Logical volume "backup_mysql" successfully removed

9. Format the partition where the library is located

[mysql@localhost ~] $mysqladmin-uroot-poracle shutdown 131014 10:32:40 mysqld_safe mysqld from pid file / mnt/lvm/mysql/data/localhost.localdomain.pid ended [1] + Done mysqld_safe [root@localhost ~] # umount / mnt/lvm [root@localhost ~] # mkfs-t ext3 / dev/vg/mysql [root@localhost] # mount / dev/vg/mysql / mnt/lvm [root@localhost ~] # lvs LV VG Attr LSize Origin snap% Move Log Copy% Convert mysql vg-wi-ao 2.00G [root@localhost ~] # vgs VG # PV # LV # SN Attr VSize VFree vg 4 1 0 wz--n- 3.81G 1.81G

10. Extract the snapshot to the partition where the standby library is located

# tar-jxv-f / mnt/snapshot/mysql.tar.bz2-C / mnt/lvm/ [root@localhost lvm] # pwd / mnt/lvm [root@localhost lvm] # ls lost+found mysql

11. Start MySQL

twelve。 Perform point-in-time recovery with binlog

[mysql@localhost ~] $mysqlbinlog-- stop-datetime= "2013-10-14 10:17:10" / mnt/lvm/mysql/data/mysql-bin.000003 | mysql- uroot-poracle

13. Confirm data

Mysql > show databases; +-+ | Database | +-+ | information_schema | | cnfol | | mysql | | test | +-+ 4 rows in set (0.00 sec) mysql > select * from cnfol.t +-+ | id | +-+ | 1 | | 2 | +-+ 2 rows in set (0.00 sec) after reading the above, do you have any further understanding of how to realize real-time online backup and recovery in MySQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.