mysql - mysqldump 如何工作？
我有一个使用 mysql 作为后端的 java 应用程序，每天晚上我们使用 mysqldump 备份 mysql，并且应用程序在该时间段（应用程序 20 分钟）停止工作。
用于进行备份的命令。
$MYSQLDUMP -h $HOST --user=$USER --password=$PASS $database > \
$BACKDIR/$SERVER-mysqlbackup-$database-$DATE.sql
gzip -f -9 $BACKDIR/$SERVER-mysqlbackup-$database-$DATE.sql
这是正常的还是我做错了什么导致数据库在此期间停止？
谢谢， K
I have a java application that uses mysql as back end, every night we take a backup of mysql using mysqldump and application stops working for that time period(app 20 min).
Command used for taking the backup.
$MYSQLDUMP -h $HOST --user=$USER --password=$PASS $database > \
$BACKDIR/$SERVER-mysqlbackup-$database-$DATE.sql
gzip -f -9 $BACKDIR/$SERVER-mysqlbackup-$database-$DATE.sql
Is this normal or am I doing something wrong that is causing the DB to stall during that time ?
Thanks,
K
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
请参阅https://serverfault .com/questions/224711/backing-up-a-mysql-database-while-it-is-still-in-use/224716#224716
我怀疑您正在使用 MyISAM，并且表正在锁定。我建议你切换到 InnoDB 并使用单事务标志。这将允许更新继续，并且还将保持一致的状态。
See https://serverfault.com/questions/224711/backing-up-a-mysql-database-while-it-is-still-in-use/224716#224716
I suspect you are using MyISAM, and the table is locking. I suggest you switch to InnoDB and use the single-transaction flag. That will allow updates to continue and will also preserve a consistent state.
mysqldump必须获得表上的读锁并在备份期间保持它，以确保备份的一致性。但是，如果在其间发生写入（即读取 -> 写入 -> 读取），则读取锁可能会阻止后续读取：第一个读取锁会阻塞写入锁，而写入锁会阻塞第二个读取锁。这部分取决于您的表类型。如果您使用MyISAM，锁会应用于整个表，因此整个表将被锁定。我相信InnoDB中的锁的工作方式不同，并且这不会锁定整个表。
mysqldumphas to get a read lock on the tables and hold it for the duration of the backup in order to ensure a consistent backup. However, a read lock can stall subsequent reads, if a write occurs in between (i.e.read -> write -> read): the first read lock blocks the write lock, which blocks the second read lock.This depends in part on your table type. If you are using MyISAM, locks apply to the entire table and thus the entire table will be locked. I believe that the locks in InnoDB work differently, and that this will not lock the entire table.
这可能会有所帮助...特别是 --single-transaction 选项，而不是 --all-databases 选项...（来自 mysqldump 联机帮助页）
This may help... specifically the --single-transaction option, and not the --all-databases one... (from the mysqldump manpage)
您可以声明
--skip-lock-tables但这可能会导致数据在备份时被修改。这可能意味着您的数据不一致并引发各种错误。最好在最少人使用的时候进行备份。You can state
--skip-lock-tablesbut this could lead to data being modified as you backup. This could mean your data is inconsistent and throw all sorts of errors. Best to just do your backup at the time when least people will be using it.