仅 mysql 转储表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/709000/
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 dump tables only
提问by dole doug
In my database I have some tables and views. How can I export all the tables (and not the views) from my database from command line?
在我的数据库中，我有一些表和视图。如何从命令行从我的数据库导出所有表（而不是视图）？
采纳答案by soulmerge
You can use mysqldump with the option --ignore-tableto exclude the views individually. Or use mysqldump and remove the views with an application/manually. grep might be an option:
您可以使用带有选项--ignore-table 的mysqldump来单独排除视图。或者使用 mysqldump 并通过应用程序/手动删除视图。grep 可能是一个选项：
grep -v "CREATE VIEW" db.dump > db-without-views.dump
回答by tylerl
The current implementation mysqldump won't create dumps without views -- and furthermore, (last time I checked) views are actually created twice -- once as a table, then the table is dropped and replaced with a view. So you can't just filter out the "CREATE VIEW" command, unless that behavior has been modified.
当前的实现 mysqldump 不会创建没有视图的转储——此外，（上次我检查过）视图实际上创建了两次——一次作为表，然后表被删除并替换为视图。因此，您不能只过滤掉“CREATE VIEW”命令，除非该行为已被修改。
However, mysqldump will take a list of tables as parameters following the database name. Something like this:
但是，mysqldump 将采用表列表作为数据库名称后面的参数。像这样的东西：
mysqldump -ujoe -pmysecret joesdb posts tags comments users
回答by naveen_sfx
To ignore a single view from your DB for Dump:
要忽略数据库中的单个视图进行转储：
mysqldump -uusrname -ppwd -h hostname --ignore-table=db.view_name db > db.sql
mysqldump -uusrname -ppwd -h hostname --ignore-table=db.view_name db > db.sql
To ignore multiple view from your Db for Dump:
要从 Db for Dump 忽略多个视图：
mysqldump -uusrname -ppwd -h hostname --ignore-table=db.view1 --ignore-table=db.view2 db > db.sql
mysqldump -uusrname -ppwd -h hostname --ignore-table=db.view1 --ignore-table=db.view2 db > db.sql
NOTE: to ignore multiple views for dump use --ignore-tableoption multiple times.
注意：要多次忽略转储使用--ignore-table选项的多个视图。
回答by Green Card
Backuping a single table from a database
从数据库备份单个表
mysqldump -uUSERNAME -pPASWORD DATABASE TABLE_NAME --host=HOST_NAME > c:\TABLE_NAME.sql
Restoring a single table from a database dump
从数据库转储恢复单个表
mysql -uUSERNAME -pPASSWORD DATABASE --host=HOST_NAME < c:\TABLE_NAME.sql
回答by programaths
Updated @ladieu's script:
更新了@ladieu 的脚本：
- Shell arguments are properly escaped permitting passwords with shell caracters
- Using PDO instead of mysql specific functions (sometime an IP seems to be resolved against DNS with the mysql_* functions and thus trigger an error)
- Removed assumption that the script is used locally (added host parameter)
- Using the "-r" options to avoid encoding issues
- name the output file .sql
- rearranged parameters so it does fit in "I want to connect to with user identified by and dump database>
- 外壳参数被正确转义，允许带有外壳字符的密码
- 使用 PDO 而不是 mysql 特定函数（有时似乎使用 mysql_* 函数针对 DNS 解析 IP，从而触发错误）
- 删除了脚本在本地使用的假设（添加了主机参数）
- 使用“-r”选项避免编码问题
- 将输出文件命名为 .sql
- 重新排列参数，使其适合“我想连接到用户标识的用户并转储数据库>
<?php
if (is_array($argv) && count($argv)>3) {
$host=($argv[1]);
$user=($argv[2]);
$password=($argv[3]);
$database=($argv[4]);
}
else {
echo "Usage php mysqdump.php <host> <user> <password> <database>\n";
exit;
}
echo 'connecting to'.$host;
$pdo=new PDO("mysql:host=$host;dbname=$database",$user,$password);
//$link = mysql_connect($host, $user, $password);
//$source = mysql_select_db($database, $link);
$sql = "SHOW FULL TABLES IN `$database` WHERE TABLE_TYPE LIKE 'VIEW';";
$result = $pdo->query($sql);
$views=array();
while ($row = $result->fetch()) {
$views[]="--ignore-table={$database}.".$row[0];
}
//no views or triggers please
$host=escapeshellarg($host);
$user=escapeshellarg($user);
$password=escapeshellarg($password);
$database=escapeshellarg($database);
echo passthru("mysqldump -h $host -u $user --password=\"$password\" $database --skip-triggers ".implode(" ",$views)." -r $database.sql");
Enjoy !
享受 ！
回答by ladieu
Usage
用法
php mysqldump.php mydatabase myusername mypassword > myoutputfile.sql
This is a pretty old script of mine. Someone could easily adapt this to use PDO if you do not have access to the mysql functions.
这是我的一个相当古老的脚本。如果您无权访问 mysql 函数，有人可以轻松地将其调整为使用 PDO。
<?php
if (is_array($argv) && count($argv)>3) {
$database=$argv[1];
$user=$argv[2];
$password=$argv[3];
}
else {
echo "Usage php mysqdump.php <database> <user> <password>\n";
exit;
}
$link = mysql_connect('localhost', $user, $password);
if (!$link) {
die('Could not connect: ' . mysql_error());
}
$source = mysql_select_db('$database', $link);
$sql = "SHOW FULL TABLES IN `$database` WHERE TABLE_TYPE LIKE 'VIEW';";
$result = mysql_query($sql);
$views=array();
while ($row = mysql_fetch_row($result)) {
$views[]="--ignore-table={$database}.".$row[0];
}
//no views or triggers please
echo passthru("mysqldump -u root --password=\"$password\" $database --skip-triggers ".implode(" ",$views));
?>
回答by Sandip Ghosh
Try the following:
请尝试以下操作：
mysqldump -h drupdbsvr -u elt_drupal_dba -pdrupdev-654! clms_admin views_display views_view view_argument view_exposed_filter view_filter view_sort view_tablefield view_view > /opt/dbdump/clms_admin_view.sql
Where the syntax is:-
语法是：-
mysqldump -h hostname -u database_user_name database_password database_name table_name1 table_name2 > path/sql_file.sql
