LVM(LogicalVolumeManagement逻辑卷管理)实现几乎热备的原理:利用LVM的快照卷达到可以保存数据库在某一时刻的瞬时状态,只需把当时的快照备份出来即可。LVM的
LVM(Logical Volume Management逻辑卷管理)实现几乎热备的原理:利用LVM的快照卷达到可以保存数据库在某一时刻的瞬时状态,只需把当时的快照备份出来即可。
LVM的快照原理:简单的讲就是lvm对某个卷创建快照卷后,快照卷则作为原卷的另一个访问入口,当原卷的数据发生变化时,则先把原卷的内容先复制一份到快照卷,然后才会继续对原卷数据的修改,这也就是之所以快照卷的容量大小应该大于整个备份过程中数据库的变化量的原因。所以当备份完成以后,快照卷也就可以拆除了,它的作用就是在备份过程中把发生变化的数据的快照时状态先拷贝过来一个副本。
用lvm对mysql备份的前提:数据文件和事务日志文件应该在同一个卷上,否则快照卷数据和事务日志时间点不一致,恢复后会引起数据库错乱。
原理明白了,操作起来就比较简单了:
当然要首先准备好lvm卷且保证mysql的datadir和innodb_log_group_home_dir在这个卷上
/* 再开一个终端,前面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” 博客,请务必保留此出处

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.

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.

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

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.

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.

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

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 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.


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

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

Hot Article

Hot Tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Zend Studio 13.0.1
Powerful PHP integrated development environment

PhpStorm Mac version
The latest (2018.2.1) professional PHP integrated development tool

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
Recommended: Win version, supports code prompts!
