从命令行备份和恢复 MySQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/823841/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL backup and restore from command line
提问by Ashok Gupta
I am using Command line to backup and restore MYSQL Database.
Let use I m having a Database Data1 having Views and Procedures in it.
When in Cmd line, I use mysqldumpi.e
我正在使用命令行来备份和恢复 MYSQL 数据库。让我们使用我有一个包含视图和过程的数据库 Data1。当 in 时Cmd line，我使用mysqldumpie
..>bin> mysqldump -u root -proot Data1 > Datafile.mysql
When I use above Cmd, It creates a Backup file on bin Folder of Mysql with Dtafile.mysqlName.
当我使用上面的 Cmd 时，它会在 Mysql 的 bin 文件夹上创建一个带有Dtafile.mysql名称的备份文件。
but the thing is it creates Back Up of Only Tables, Not Procedures.
但问题是它只创建表的备份，而不是过程。
And when I m restoring it in a Blank Database "Data2"
当我在空白数据库“Data2”中恢复它时
..bin> mysql - u root -proot Data2 < Dataafile.mysql
What it does is, It creates all the Tables and Convert Views into Tables and No Procedures has been restored.
它的作用是，它创建所有表并将视图转换为表，并且没有恢复任何过程。
Means I am not able to Restore my full database backup with All tables, views and Procedures.
意味着我无法使用所有表、视图和过程恢复我的完整数据库备份。
Can any of you guys help it.. I would be extremely Thankfull.
你们中的任何人都可以帮忙吗..我会非常感谢。
回答by Anand Shah
Include the "--routines" parameter for mysqldump and it will dump the procedures.
EDIT #1 :
Your mysqldump command should now look like,
包括 mysqldump 的“--routines”参数，它将转储程序。
编辑 #1：你的 mysqldump 命令现在应该看起来像，
mysqldump -u root -proot --routines Data1 > Datafile.mysql
EDIT #2:
You need to add the --add-drop-table switch as well if you want to preserve your Views. The reason is Views are created in two steps (first as dummy tables, then as real views). Also please take a note that Tables and Views share the same name space, so don't get misled by the output given by "Show Tables" command in the mysqlclient. Use "Show Create View vw_test" instead if that command shows you the create view query then bingo, also to make sure that the views have been restored correctly insert some data into the relevant tables in your other database and then run a select query on the view, if the data ties up than you hit a bulls eye, pat yourself on the back :) else you can always return to this wonderful community at Stackoverflow.
编辑 #2：
如果您想保留您的视图，您还需要添加 --add-drop-table 开关。原因是视图分两步创建（首先作为虚拟表，然后作为真实视图）。另请注意，表和视图共享相同的名称空间，因此不要被 mysqlclient 中“显示表”命令给出的输出所误导。如果该命令显示创建视图查询，然后显示宾果游戏，请改用“显示创建视图 vw_test”，还要确保视图已正确恢复，将一些数据插入其他数据库的相关表中，然后在看来，如果数据比你的靶心更紧密，请拍拍自己的背:) 否则你总是可以回到 Stackoverflow 这个美妙的社区。
So (to maintain views) your mysqldump command should now look like this
所以（为了维护视图）你的 mysqldump 命令现在应该是这样的
mysqldump -u root -proot --routines --add-drop-table Data1 > Datafile.mysql
HTH
HTH
回答by john
there is three best ways to backup database use command line,phpmyadmin or mysql administrator mysql database backup and restore in easy way, use mysql database backup
备份数据库的三种最佳方法使用命令行，phpmyadmin 或 mysql 管理员 mysql 数据库备份和恢复简单，使用 mysql 数据库备份
