search
HomeDatabaseMysql Tutorial MySQL 备份和恢复(二)基于LVM实现几乎热备

LVM(LogicalVolumeManagement逻辑卷管理)实现几乎热备的原理:利用LVM的快照卷达到可以保存数据库在某一时刻的瞬时状态,只需把当时的快照备份出来即可。LVM的

LVM(Logical Volume Management逻辑卷管理)实现几乎热备的原理:利用LVM的快照卷达到可以保存数据库在某一时刻的瞬时状态,只需把当时的快照备份出来即可。


LVM的快照原理:简单的讲就是lvm对某个卷创建快照卷后,快照卷则作为原卷的另一个访问入口,当原卷的数据发生变化时,则先把原卷的内容先复制一份到快照卷,然后才会继续对原卷数据的修改,这也就是之所以快照卷的容量大小应该大于整个备份过程中数据库的变化量的原因。所以当备份完成以后,快照卷也就可以拆除了,它的作用就是在备份过程中把发生变化的数据的快照时状态先拷贝过来一个副本。


用lvm对mysql备份的前提:数据文件和事务日志文件应该在同一个卷上,否则快照卷数据和事务日志时间点不一致,恢复后会引起数据库错乱。


原理明白了,操作起来就比较简单了:

当然要首先准备好lvm卷且保证mysql的datadir和innodb_log_group_home_dir在这个卷上


/* 创建一张新表tb1,并插入一些数据以做测试 */ MariaDB [hellodb]> CREATE TABLE tb1(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,Name VARCHAR(30) NOT NULL,Gender ENUM('F','M','O') NOT NULL); Query OK, 0 rows affected (0.11 sec) MariaDB [hellodb]> DESC tb1; +--------+-------------------+------+-----+---------+----------------+ | Field  | Type              | Null | Key | Default | Extra          | +--------+-------------------+------+-----+---------+----------------+ | id     | int(11)           | NO   | PRI | NULL    | auto_increment | | Name   | varchar(30)       | NO   |     | NULL    |                | | Gender | enum('F','M','O') | NO   |     | NULL    |                | +--------+-------------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) MariaDB [hellodb]> INSERT INTO tb1 (Name,Gender) VALUES ('Bob','M'),('Tina','F'),('Axx','O'); Query OK, 3 rows affected (0.03 sec) Records: 3  Duplicates: 0  Warnings: 0 /* 备份前为所有表加读锁,查看一下当前二进制日志位置 */ MariaDB [(none)]> FLUSH TABLES WITH READ LOCK; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000007 |      245 |              |                  | +------------------+----------+--------------+------------------+ 1 row in set (0.01 sec)

 

/* 再开一个终端,前面mysql会话不要退出 */ [root@node1 ~]# mkdir /backup/mydata-`date +%F` /* 也可以将当前二进制位置保存到文件,以备恢复时查看 */ [root@node1 ~]# mysql -e 'SHOW MASTER STATUS;' > /backup/mydata-2014-07-29/bin-pos-`date +%F-%T` /* 保证有可用的vg */ [root@node1 ~]# vgs   VG   #PV #LV #SN Attr   VSize  VFree   myvg   2   1   0 wz--n- 10.02g 7.02g   vg0    3   1   0 wz--n- 29.29g    0  /* 创建快照卷,-L | --size (LogicalVolumeSize) 指定快照卷大小,一定不可以比变化量小,否则会引起快照卷崩溃,备份失败; -n | --name 指定快照卷名字; -p | --permission 指定快照卷权限 r只读, -s --snap-shot /dev/myvg/mylv 指定要对/dev/myvg/mylv创建快照卷  如果此时数据库服务器比较繁忙,则需要等待正在提交的日志刷写到磁盘等,,可能会需要一定等待时间*/ [root@node1 ~]# lvcreate -L 100M -n mydata-snap -p r -s /dev/myvg/mylv    Logical volume "mydata-snap" created [root@node1 ~]# mkdir /mnt/snap [root@node1 ~]# mount /dev/myvg/mydata-snap /mnt/snap/ mount: block device /dev/mapper/myvg-mydata--snap is write-protected, mounting read-only [root@node1 ~]# cd /mnt/snap/ [root@node1 snap]# ls data  lost+found [root@node1 snap]# ls data/ aria_log.00000001  ib_logfile0  mysql-bin.000001  mysql-bin.000005  node1.bob.org.err aria_log_control   ib_logfile1  mysql-bin.000002  mysql-bin.000006  node1.bob.org.pid hellodb            mydb         mysql-bin.000003  mysql-bin.000007  performance_schema ibdata1            mysql        mysql-bin.000004  mysql-bin.index   test [root@node1 snap]# cp -ap /mnt/snap/data/ /backup/mydata-2014-07-29/ [root@node1 snap]# ls /backup/mydata-2014-07-29/data/ aria_log.00000001  ib_logfile0  mysql-bin.000001  mysql-bin.000005  node1.bob.org.err aria_log_control   ib_logfile1  mysql-bin.000002  mysql-bin.000006  node1.bob.org.pid hellodb            mydb         mysql-bin.000003  mysql-bin.000007  performance_schema ibdata1            mysql        mysql-bin.000004  mysql-bin.index   test

 

备份完成回到mysql会话,解锁表

MariaDB [(none)]> UNLOCK TABLES; Query OK, 0 rows affected (0.01 sec)

这样一个基于lvm快照的mysql备份就完成了,然后还可以结合二进制日志做增量备份和恢复等,可参照前一篇文章 MySQL 备份和恢复(一)mysqldump



本文出自 “不懂IT的中医不是好IT” 博客,请务必保留此出处

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
How does MySQL handle data replication?How does MySQL handle data replication?Apr 28, 2025 am 12:25 AM

MySQL processes data replication through three modes: asynchronous, semi-synchronous and group replication. 1) Asynchronous replication performance is high but data may be lost. 2) Semi-synchronous replication improves data security but increases latency. 3) Group replication supports multi-master replication and failover, suitable for high availability requirements.

How can you use the EXPLAIN statement to analyze query performance?How can you use the EXPLAIN statement to analyze query performance?Apr 28, 2025 am 12:24 AM

The EXPLAIN statement can be used to analyze and improve SQL query performance. 1. Execute the EXPLAIN statement to view the query plan. 2. Analyze the output results, pay attention to access type, index usage and JOIN order. 3. Create or adjust indexes based on the analysis results, optimize JOIN operations, and avoid full table scanning to improve query efficiency.

How do you back up and restore a MySQL database?How do you back up and restore a MySQL database?Apr 28, 2025 am 12:23 AM

Using mysqldump for logical backup and MySQLEnterpriseBackup for hot backup are effective ways to back up MySQL databases. 1. Use mysqldump to back up the database: mysqldump-uroot-pmydatabase>mydatabase_backup.sql. 2. Use MySQLEnterpriseBackup for hot backup: mysqlbackup--user=root-password=password--backup-dir=/path/to/backupbackup. When recovering, use the corresponding life

What are some common causes of slow queries in MySQL?What are some common causes of slow queries in MySQL?Apr 28, 2025 am 12:18 AM

The main reasons for slow MySQL query include missing or improper use of indexes, query complexity, excessive data volume and insufficient hardware resources. Optimization suggestions include: 1. Create appropriate indexes; 2. Optimize query statements; 3. Use table partitioning technology; 4. Appropriately upgrade hardware.

What are views in MySQL?What are views in MySQL?Apr 28, 2025 am 12:04 AM

MySQL view is a virtual table based on SQL query results and does not store data. 1) Views simplify complex queries, 2) Enhance data security, and 3) Maintain data consistency. Views are stored queries in databases that can be used like tables, but data is generated dynamically.

What are the differences in syntax between MySQL and other SQL dialects?What are the differences in syntax between MySQL and other SQL dialects?Apr 27, 2025 am 12:26 AM

MySQLdiffersfromotherSQLdialectsinsyntaxforLIMIT,auto-increment,stringcomparison,subqueries,andperformanceanalysis.1)MySQLusesLIMIT,whileSQLServerusesTOPandOracleusesROWNUM.2)MySQL'sAUTO_INCREMENTcontrastswithPostgreSQL'sSERIALandOracle'ssequenceandt

What is MySQL partitioning?What is MySQL partitioning?Apr 27, 2025 am 12:23 AM

MySQL partitioning improves performance and simplifies maintenance. 1) Divide large tables into small pieces by specific criteria (such as date ranges), 2) physically divide data into independent files, 3) MySQL can focus on related partitions when querying, 4) Query optimizer can skip unrelated partitions, 5) Choosing the right partition strategy and maintaining it regularly is key.

How do you grant and revoke privileges in MySQL?How do you grant and revoke privileges in MySQL?Apr 27, 2025 am 12:21 AM

How to grant and revoke permissions in MySQL? 1. Use the GRANT statement to grant permissions, such as GRANTALLPRIVILEGESONdatabase_name.TO'username'@'host'; 2. Use the REVOKE statement to revoke permissions, such as REVOKEALLPRIVILEGESONdatabase_name.FROM'username'@'host' to ensure timely communication of permission changes.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

PhpStorm Mac version

PhpStorm Mac version

The latest (2018.2.1) professional PHP integrated development tool

SecLists

SecLists

SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.

SublimeText3 English version

SublimeText3 English version

Recommended: Win version, supports code prompts!