如何最好地优化备份 MySQL 数据库的方式？
我有几个网站托管在 VPS 上，目前正在通过 cron 运行 shell 脚本来执行数据库备份，如下所示：
mysqldump -uusername1 -prootpassword dbname1 > /backup/dbname1.bak
mysqldump -uusername2 -prootpassword dbname2 > /backup/dbname2.bak
mysqldump -uusername3 -prootpassword dbname3 > /backup/dbname3.bak
我对此过程有一些担忧。
首先，我使用根服务器密码来执行 mysqldump，并且该文件以明文形式存储在服务器上（不可公开访问或任何其他内容，但如果我出于某种原因授予其他用户访问服务器的权限，显然会出现问题或其他）。我使用 root 是因为它比跟踪创建数据库的每个人并询问他们特定的数据库密码更简单。
其次，只有当人们告诉我他们已经添加了一个数据库时，这个过程才有效（这在大多数情况下都很好，我们在这里没有做任何超级复杂的事情）。我更愿意备份所有内容，而不必担心我忽略了某些内容。
I have several websites hosted on a VPS and am currently performing database backups by running a shell script via cron that looks something like this:
mysqldump -uusername1 -prootpassword dbname1 > /backup/dbname1.bak
mysqldump -uusername2 -prootpassword dbname2 > /backup/dbname2.bak
mysqldump -uusername3 -prootpassword dbname3 > /backup/dbname3.bak
I have a couple of concerns about this process.
Firstly, I'm using the root server password to perform mysqldump, and the file is being stored in clear text on the server (not publicly accessible or anything, but there are obviously concerns if I grant other users access to the server for one reason or another). I'm using root because it's simpler than tracking everybody that creates a database down and asking them for their specific db passwords.
Secondly, this process only works if people inform me that they've added a database (which is fine for the most part, we're not doing anything super complicated over here). I would prefer to have a backup of everything without worrying that I've overlooked something.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您始终可以转储所有数据库：
这样您就不必跟踪有哪些数据库。缺点是恢复变得有点复杂。
至于使用 root，您没有理由不能创建另一个有权进行备份的帐户 - 除了初始设置之外，您不应该使用 root 帐户进行任何其他操作。
You could always just dump ALL the databases:
That'd free you from having to keep track of which dbs there are. The downside is that restoring gets a bit more complicated.
As for using root, there's no reason you couldn't create another account that has permissions to do backups - you should never use the root account for anything other than initial setup.
我使用这个脚本： http://sourceforge.net/projects/automysqlbackup/ 它工作得很好。此外，您还应该添加一个具有全局 SELECT 和 LOCK TABLES 权限的备份 MySQL 用户。这样你就不需要每个人的用户名和密码/
I use this script: http://sourceforge.net/projects/automysqlbackup/ It works perfectly. Also, you should add a backup MySQL user that has global SELECT and LOCK TABLES permissions. That way you don't need everyone's username and password/