Shulou(Shulou.com)05/31 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.