MySQL备份和同步时使用LVM
原文作者: Peter Zaitsev 原文来源: http://www.mysqlperformanceblog.com/2006/08/21/using-lvm-for-mysql-backup-and-replication-setup 译者:叶金荣(Email: If someone asks me about MySQL Backup advice my first question would be if they have L
原文作者: Peter Zaitsev
原文来源: http://www.mysqlperformanceblog.com/2006/08/21/using-lvm-for-mysql-backup-and-replication-setup
译者:叶金荣(Email:
If someone asks me about MySQL Backup advice my first question would be if they have LVM installed or have some systems with similar features set for other operation systems. Veritas File System can do it for Solaris. Most SAN systems would work as well.
如果有人问我关于MySQL备份的建议,我会先问操作系统是否安装了LVM或者其他相似功能的软件。Solaris下的Veritas文件系统也可以做到同样的功能。大多数SAN系统也可以。
What is really needed is ability to create atomic snapshot of the volume, which can be later mounted same as original file system
Why snapshot based MySQL backups are great ?
为什么基于MySQL快照的备份很好?
There are number of reasons:
原因如下几点:
Almost Hot backup In most cases you can perform this type of backup while your application is running. No need to shut down server, make it read only or anything like it.
几乎是热备 在大多数情况下,可以在应用程序仍在运行的时候执行备份。无需关机,只需设置为只读或者类似只读的限制。
Support for all local disk based storage engines It works with MyISAM and Innodb and BDB, It also should work with Solid, PrimeXT and Falcon storage engines.
支持所有基于本地磁盘的存储引擎 它支持MyISAM, Innodb, BDB,还支持 Solid, PrimeXT 和 Falcon。
Fast Backup You simply do file copy in the binary form so it is hard to beat in speed.
快速备份 只需拷贝二进制格式的文件,在速度方面无以匹敌。
Low Overhead It is simply file copy so overhead to the server is minimal.
低开销 只是文件拷贝,因此对服务器的开销很细微。
Easy to Integrate Do you want to compress backup ? Backup it to tape, FTP or any network backup software - it is easy as you just need to copy files.
容易保持完整性 想要压缩备份文件吗?把它们备份到磁带上,FTP或者网络备份软件 -- 十分简单,因为只需要拷贝文件即可。
Fast Recovery Recovery time is as fast as putting data back and standard MySQL crash recovery, and it can be reduced even further. More on this later.
快速恢复 恢复的时间和标准的MySQL崩溃恢复或数据拷贝回去那么快,甚至可能更快,将来会更快。
Free No extra commercial tools as Innodb Hot Backup are required to perform backup.
免费 无需额外的商业软件,只需Innodb热备工具来执行备份。
Are there any downsides ?
有什么缺点吗?
Need to have snapshot campatibility - this is obvious one.
需要兼容快照 -- 这是明显的。
May need root access In some organizations DBA and System Administrator are different people from different departmnents which might not like to trade access rights between each other.
需要超级用户(root) 在某些组织,DBA和系统管理员来自不同部门不同的人,因此权限各不一样。
Hard to predict downtime I mentioned this solution is often hot backup, but bad thing it is hard to estimate when it is hot and when it is not - FLUSH TABLES WITH READ LOCK may take quite a while to complete on systems with long queries.
停工时间无法预计 我提到的这个方法通常指热备,但是谁也无法预料到底是不是热备 -- FLUSH TABLES WITH READ LOCK 可能会需要执行很长时间才能完成。
Problems with data on multiple volumes If you have logs on separate devices or just your database spanning across multiple volumes you will be in trouble as you will not get consistent snapshot across all the database. Some systems may be able to do atomic snapshot of many volumes.
多卷上的数据问题 如果你把日志放在独立的设备上或者你的数据库分布在多个卷上,这就比较麻烦了,因为无法得到全部数据库的一致性快照。不过有些系统可能能自动做到多卷快照。
Lets speak a bit about how LVM and snapshotting in general works. Really there are different implementations but the sake of them is to provide you with volume which consistently matches state of the volume at the time storage is created. In LVM it is implementeed as copy on write. Special storage area allocated on device where old version of changed pages are stored. You can think about it as about simplified form of versioning like in Innodb if it is closer to you. In other cases snapshot may be implemented by tripple-mirroring. Ie you have RAID1 volume but there are 3 copies of data rather than 2. So you can move one devices out of mirror and use it as snapshot while still having your data safe and secure.
现在讲讲LVM和通常情况下的快照。确实,它们的实现的方式不同,但是目的都是让卷和存储卷创建的数据保持一致。LVM是边写边读。设备中专门分配了一个专用的区域用于保存老版本的内存页变化情况。你可以把它想象成为一个简单的版本管理形式,就像Innodb。另一方面,快照实现的方式就像三重镜像。如果你有RAID1卷,不过数据拷贝的分数是3而不是2。因此可以把一个设备从镜像中去掉,把它作为数据快照,并且还能保持数据安全。
There are two types of snapshots - some of them are read-only while others can be read-write. read-only snapshots may sound good enough as you're only going to read data anyway, but in reality read-write snapshots have number of benefits. First no extra handling is needed for journaling file sytems - you can simply do journal recovery on snapshot. With read-only snapshot you need to make sure filesystem synchronizes device before snapshot is taken so no journal replay is needed.
快照有2种方式 -- 一种是只读另一种是可读写。如果你只须拷贝数据,那么只读快照看起来不错,不过可读写快照则有好几个优点。首先是无需额外处理日至文件系统 -- 你可以快照上简单地实现日志恢复。只读快照则必须保证文件系统在开始快照之前就得和设备同步,因此需要日志重现。
The other benefit of read-write snapshot is you can actually start MySQL Server on it and perform recovery, check tables or do whatever else you might need to do to ensure your backup is consistent. Backing up database which was already corrupted is very nasty problem you want to avoid.
可读写快照的另一个好处是可以启动MySQL服务器,执行恢复、检察数据表或者其他任何要保证备份一致性所需的操作。必须避免备份已经损坏的数据库。
Let's now see what exactly you need to do to perform backup of MySQL Database (or create slave) using LVM2 on Linux.
现在让我们来看看Linux上用LVM2备份MySQL数据库(或者它的slave)所需执行的操作:
1) Connect to MySQL and run FLUSH TABLES WITH READ LOCK
Note - this command may take a while to complete if you have long running queries. The catch here is FLUSH TABLES WITH READ LOCK actually waits for all statements to complete, even selects. So be careful if you have any long running queries. If you're using only Innodb tables and do not need to synchronize binary log position with backup you can skip this step.
1) 连接到MySQL上,运行 FLUSH TABLES WITH READ LOCK
注意 -- 如果你当前正在执行一个较长时间的查询,那么这个命令可能需要较长时间才能完成。在这里 FLUSH TABLES WITH READ LOCK 需要捕获所有的语句都完成了,甚至是 SELECT。因此如果有较长时间的查询时要小心。如果你只使用到Innodb表,就无需同步二进制日志可直接略过这个步骤。
2) While holding connection open run: lvcreate -L16G -s -n dbbackup /dev/Main/Data - This will create snapshot named dbbackup for Logical Volume Main/Data . You should specify enough of undo space to hold modifications during backup process - I've specified 16GB in this case. If your undo size is not large enough snapshot will get invalidated and backup will be aborted.
2) 保持连接,运行 lvcreate -L16G -s -n dbbackup /dev/Main/Data -- 它会创建本地卷 Main/Data 的快照,命名为 dbbackup。备份过程中务必指定足够大的撤销空间用于保存发生变化的东西 -- 我指定了16GB。如果撤销空间不够大,快照就会无效并且备份就终止了。
Sometimes you might run into the errors on this step, The most common one I've resently seen is: snapshot: Required device-mapper target(s) not detected in your kernel - This means snapshot module is not loaded in your kernel by default and you need to load it, which is done by running modprobe dm-snapshot
在这个步骤中,你有时候会碰到错误,最近我经常碰到的一个是:snapshot: Required device-mapper target(s) not detected in your kernel -- 意思是内核默认没有加载快照模块,需要你自己加载,运行命令 modprobe dm-snapshot 即可。
3) Now you have created logical volume and can unlock the tables, but before that you should probably record binary log position which is done by running SHOW MASTER STATUS - This is binary log position you'll need to point your MySQL Slaves created from this snapshot.
3) 现在已经创建完本地逻辑卷,可以释放表锁了,不过在这之前,需要记录一下二进制日志的位置,运行 SHOW MASTER STATUS 可以看到 -- 这个位置在MySQL slave上创建快照时需要用到。。
4) Snapshot created, now you want to let MySQL Server to continue, which is done by running UNLOCK TABLES or simply closing connection.
4) 快照创建完毕,运行 UNLOCK TABLES 释放锁或者关闭连接,让MySQL服务器继续运行。
5) Mount backup Filesystem: mount /dev/Main/dbbackup /mnt/backup
5) 挂载备份文件系统:mount /dev/Main/dbbackup /mnt/backup
6) Copy data to backup. Normally you can skip slow query logs and error log while taking backup. You also can skip most of binary logs - however if some of your slaves are far behind you might want to keep some of last binary logs just in case, or you can assume in case of recovery from the backup you will need to restore slaves as well and skip binary logs in your backup process.
6) 拷贝备份数据。通常备份时可以略过慢查询日志和错误日志。也可以略过大部分的二进制日志 -- 然而如果有些slave远远落后于master的话,就必须保留所需的二进制日志了,或者你可以假设这种情况下在slave上从备份上恢复数据也可以忽略掉二进制日志。
7) Unmount filesystem umount /mnt/backup
7) 卸载文件系统:umount /mnt/backup
8) Remove snapshot: lvremove -f /dev/Main/dbbackup
8) 删除快照:lvremove -f /dev/Main/dbbackup
If you want to create slave based on such snapshot you need to perform couple of more simple steps
如果你想创建基于slave的快照,就需要多做2个步骤。
9) Extract/Copy database to the slave database directory.
9) 提取/拷贝数据库到slave的数据库目录下。
10) Start MySQL Server. Wait for it to perform recovery.
10) 启动MySQL服务器,等待执行恢复。
11) Use CHANGE MASTER TO to point slave to saved binary log position:
11) 用 CHANGE MASTER TO 告诉slave要保存的二进制日志位置:
PLAIN TEXT
SQL:
<span>CHANGE</span> master <span>TO</span> master_host=<span>"master"</span>, master_user=<span>"user"</span>, master_password=<span>"password"</span>, master_log_file=<span>"host-bin.000335"</span>, master_log_pos=<span>401934686</span>;
12) Run SLAVE START to restart replication.
12) 运行 SLAVE START 重启复制。
With slightly modified process you can clone slaves from the slaves without stopping them - you just need to use SHOW SLAVE STATUS instead of SHOW MASTER STATUS to find out appropriate binary log position. Be careful however - cloning slave from the slave also clones inconsistences in data which slave could have accomulated - especially if you use slave_skip_errors or sql_slave_skip_counter. Cloning master you're starting from consistent copy.
使用稍微修改过的进程,就能无需停止slave的情况下克隆它 -- 运行 SHOW SLAVE STATUS 而不是 SHOW MASTER STATUS,找出合适的二进制日志位置。不过要小心 -- 克隆slave的时候也会把它积累的不一致的数据也克隆了 -- 尤其是使用 slave_skip_errors 或 sql_slave_skip_counter时。克隆master就可以拷贝保持一致的数据。
If you're interested in ready script you can try mylvmbackup by Lenz Grimmer
如果你对上面的过程有兴趣,可以试试 Lenz Grimmer 的 mylvmbackup。

熱AI工具

Undresser.AI Undress
人工智慧驅動的應用程序,用於創建逼真的裸體照片

AI Clothes Remover
用於從照片中去除衣服的線上人工智慧工具。

Undress AI Tool
免費脫衣圖片

Clothoff.io
AI脫衣器

Video Face Swap
使用我們完全免費的人工智慧換臉工具,輕鬆在任何影片中換臉!

熱門文章

熱工具

記事本++7.3.1
好用且免費的程式碼編輯器

SublimeText3漢化版
中文版,非常好用

禪工作室 13.0.1
強大的PHP整合開發環境

Dreamweaver CS6
視覺化網頁開發工具

SublimeText3 Mac版
神級程式碼編輯軟體(SublimeText3)

MySQL在Web應用中的主要作用是存儲和管理數據。 1.MySQL高效處理用戶信息、產品目錄和交易記錄等數據。 2.通過SQL查詢,開發者能從數據庫提取信息生成動態內容。 3.MySQL基於客戶端-服務器模型工作,確保查詢速度可接受。

在 Docker 中啟動 MySQL 的過程包含以下步驟:拉取 MySQL 鏡像創建並啟動容器,設置根用戶密碼並映射端口驗證連接創建數據庫和用戶授予對數據庫的所有權限

Laravel 是一款 PHP 框架,用於輕鬆構建 Web 應用程序。它提供一系列強大的功能,包括:安裝: 使用 Composer 全局安裝 Laravel CLI,並在項目目錄中創建應用程序。路由: 在 routes/web.php 中定義 URL 和處理函數之間的關係。視圖: 在 resources/views 中創建視圖以呈現應用程序的界面。數據庫集成: 提供與 MySQL 等數據庫的開箱即用集成,並使用遷移來創建和修改表。模型和控制器: 模型表示數據庫實體,控制器處理 HTTP 請求。

在開發一個小型應用時,我遇到了一個棘手的問題:需要快速集成一個輕量級的數據庫操作庫。嘗試了多個庫後,我發現它們要么功能過多,要么兼容性不佳。最終,我找到了minii/db,這是一個基於Yii2的簡化版本,完美地解決了我的問題。

優雅安裝 MySQL 的關鍵在於添加 MySQL 官方倉庫。具體步驟如下:下載 MySQL 官方 GPG 密鑰,防止釣魚攻擊。添加 MySQL 倉庫文件:rpm -Uvh https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm更新 yum 倉庫緩存:yum update安裝 MySQL:yum install mysql-server啟動 MySQL 服務:systemctl start mysqld設置開機自啟動

在 CentOS 上安裝 MySQL 涉及以下步驟:添加合適的 MySQL yum 源。執行 yum install mysql-server 命令以安裝 MySQL 服務器。使用 mysql_secure_installation 命令進行安全設置,例如設置 root 用戶密碼。根據需要自定義 MySQL 配置文件。調整 MySQL 參數和優化數據庫以提升性能。

文章摘要:本文提供了詳細分步說明,指導讀者如何輕鬆安裝 Laravel 框架。 Laravel 是一個功能強大的 PHP 框架,它 упростил 和加快了 web 應用程序的開發過程。本教程涵蓋了從系統要求到配置數據庫和設置路由等各個方面的安裝過程。通過遵循這些步驟,讀者可以快速高效地為他們的 Laravel 項目打下堅實的基礎。

MySQL和phpMyAdmin是強大的數據庫管理工具。 1)MySQL用於創建數據庫和表、執行DML和SQL查詢。 2)phpMyAdmin提供直觀界面進行數據庫管理、表結構管理、數據操作和用戶權限管理。
