从命令行进行MySQL备份和还原 [英] MySQL backup and restore from command line

问题描述

我正在使用命令行备份和还原MYSQL数据库. 让我使用一个包含视图和过程的数据库数据1. 在Cmd line中时，我使用mysqldump即

..>bin> mysqldump -u root -proot Data1 > Datafile.mysql

当我使用上述Cmd时，它会使用Dtafile.mysql在Mysql的bin文件夹中创建一个备份文件 名称.

但事实是它创建了仅表而不是过程的备份".

当我将其还原到空白数据库"Data2"中时

..bin> mysql - u root -proot Data2 < Dataafile.mysql

它的作用是，它创建所有表并将视图转换为表，并且没有过程被还原.

意味着我无法使用所有表，视图和过程还原完整的数据库备份.

你们中的任何人都可以帮忙吗.. 我将非常感谢.

解决方案

包括mysqldump的"--routines"参数，它将转储过程.

编辑#1: 您的mysqldump命令现在应如下所示，

mysqldump -u root -proot --routines Data1 > Datafile.mysql

编辑#2:
如果要保留视图，还需要添加--add-drop-table开关.原因是视图分两个步骤创建(首先是虚拟表，然后是真实视图).另外请注意，表和视图共享相同的名称空间，因此不要被mysqlclient中显示表"命令给出的输出所迷惑.如果该命令向您显示创建视图查询，然后使用bingo，则使用显示创建视图vw_test"代替，还可以确保视图已正确还原，将一些数据插入其他数据库的相关表中，然后在数据库中运行select查询.查看，如果数据阻塞了，而不是您靶心了，请反省:)否则，您随时可以在Stackoverflow上回到这个美好的社区.

因此(为了维护视图)，您的mysqldump命令现在应该看起来像这样

mysqldump -u root -proot --routines --add-drop-table Data1 > Datafile.mysql

HTH

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 mysqldump i.e

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

but the thing is it creates Back Up of Only Tables, Not Procedures.

And when I m restoring it in a Blank Database "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.

解决方案

Include the "--routines" parameter for mysqldump and it will dump the procedures.

EDIT #1 : Your mysqldump command should now look like,

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.

So (to maintain views) your mysqldump command should now look like this

mysqldump -u root -proot --routines --add-drop-table Data1 > Datafile.mysql

HTH

这篇关于从命令行进行MySQL备份和还原的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！