使用 Mysql Dump 备份视图
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1658204/
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
Backing Up Views with Mysql Dump
提问by Charles Faiga
I want to back up only the Views with mysqldump.
我只想用 mysqldump 备份视图。
Is this possible?
这可能吗？
If so, how?
如果是这样，如何？
回答by Andomar
NOTE: This answer from Kenmoved from suggested edit to own answer.
注意：Ken 的这个答案从建议的编辑变成了自己的答案。
here's a full command line example using a variant of the above
这是使用上述变体的完整命令行示例
mysql -u username INFORMATION_SCHEMA
--skip-column-names --batch
-e "select table_name from tables where table_type = 'VIEW'
and table_schema = 'database'"
| xargs mysqldump -u username database
> views.sql
This extracts all of the view names via a query to the INFORMATION_SCHEMA database, then pipes them to xargs to formulate a mysqldump command. --skip-column-names and --batch are needed to make the output xargs friendly. This command line might get too long if you have a lot of views, in which case you'd want to add some sort of additional filter to the select (e.g. look for all views starting with a given character).
这通过对 INFORMATION_SCHEMA 数据库的查询提取所有视图名称，然后将它们通过管道传输到 xargs 以制定 mysqldump 命令。--skip-column-names 和 --batch 需要使输出 xargs 友好。如果您有很多视图，此命令行可能会变得太长，在这种情况下，您需要向选择添加某种额外的过滤器（例如，查找以给定字符开头的所有视图）。
回答by Ross Smith II
I modified Andomar's excellent answerto allow the database (and other settings) to only be specified once:
我修改了Andomar 的优秀答案，只允许指定一次数据库（和其他设置）：
#!/bin/bash -e
mysql --skip-column-names --batch -e \
"select table_name from information_schema.views \
where table_schema = database()" $* |
xargs --max-args 1 mysqldump $*
I save this as mysql-dump-views.shand call it via:
我将其另存为mysql-dump-views.sh并通过以下方式调用它：
$ mysql-dump-views.sh -u user -ppassword databasename >dumpfile.sql
回答by olliiiver
Backing up views over multiple databases can be done by just using information_schema:
只需使用 information_schema 即可备份多个数据库的视图：
mysql --skip-column-names --batch -e 'select CONCAT("DROP TABLE IF EXISTS ", TABLE_SCHEMA, ".", TABLE_NAME, "; CREATE OR REPLACE VIEW ", TABLE_SCHEMA, ".", TABLE_NAME, " AS ", VIEW_DEFINITION, "; ") table_name from information_schema.views'
回答by Artem Russakovskii
By backup, I'm assuming you mean just the definition without the data.
通过备份，我假设您的意思只是没有数据的定义。
It seems that right now mysqldump doesn't distinguish between VIEWs and TABLEs, so perhaps the best thing to do is to either specify the VIEWs explicitly on the command line to mysqldump or figure out this list dynamically before mysqldump and then passing it down like before.
似乎现在 mysqldump 不区分 VIEWs 和 TABLEs，所以也许最好的办法是在命令行上明确指定 VIEWs 到 mysqldump 或在 mysqldump 之前动态找出这个列表，然后像以前一样传递它.
You can get all the VIEWs in a specific database using this query:
您可以使用以下查询获取特定数据库中的所有 VIEW：
SHOW FULL TABLES WHERE table_type='view';
SHOW FULL TABLES WHERE table_type='view';
回答by gerrit_hoekstra
I would stick as closely as possible to the output of mysqldumplike the OP asked, since it includes a slew of information about the view that can't be reconstructed with a simple query from the INFORMATION_SCHEMA.
我会尽可能地像 OP 所要求的那样坚持mysqldump的输出，因为它包含了大量关于无法通过来自 INFORMATION_SCHEMA 的简单查询重建的视图的信息。
This is how I create a deployment view script from my source database:
这是我从源数据库创建部署视图脚本的方式：
SOURCEDB="my_source_db"
mysql $SOURCEDB --skip-column-names -B -e \
"show full tables where table_type = 'view'" \
| awk '{print }' \
| xargs -I {} mysqldump $SOURCEDB {} > views.sql
回答by RedScourge
In terms of answering this question, olliiiver's answeris the best for doing this directly. For my answer I will try to build that into a comprehensive full backup and restore solution.
在回答这个问题方面，olliiiver的回答是最好的直接做这个。对于我的回答，我将尝试将其构建为一个全面的完整备份和恢复解决方案。
With the help of the other answers in this question, and a few other resources, I came up with this script for easily replacing the database on my development server with a live copy from the production server on demand. It works on one database at a time, rather than all databases. While I do have a separate script for that, it is not safe to share here as it basically drops and recreates everything except for a select few databases, and your environment may vary.
在这个问题的其他答案和其他一些资源的帮助下，我想出了这个脚本，可以根据需要轻松地用生产服务器的实时副本替换我的开发服务器上的数据库。它一次适用于一个数据库，而不是所有数据库。虽然我确实有一个单独的脚本，但在这里共享并不安全，因为它基本上删除并重新创建了除少数几个数据库之外的所有内容，并且您的环境可能会有所不同。
The script assumes root system and MySQL user on both machines (though that can be changed), working passwordless SSH between servers, and relies on a MySQL password file /root/mysqlroot.cnf on each machine, which looks like this:
该脚本假定两台机器上的 root 系统和 MySQL 用户（尽管可以更改），在服务器之间使用无密码 SSH，并依赖于每台机器上的 MySQL 密码文件 /root/mysqlroot.cnf，如下所示：
[client]
password=YourPasswordHere
File: synctestdb.sh, optionally symlinked to /usr/sbin/synctestdb for ease of use
文件：synctestdb.sh，可选择符号链接到 /usr/sbin/synctestdb 以方便使用
Usage: synctestdb DBNAME DESTSERVER
Run it from the production server.
从生产服务器运行它。
Here it is:
这里是：
#!/bin/bash
if [ "" != "" ] && [ "" != "--help" ] && [ "" != "" ] ; then
DBNAME=
DESTSERVER=
BKDATE=$( date "+%Y-%m-%d" );
SRCHOSTNAME=$( /bin/hostname )
EXPORTPATH=/tmp
EXPORTFILE=/tmp/${SRCHOSTNAME}_sql_${BKDATE}_devsync.sql
CREDSFILE=/root/mysqlroot.cnf
SSHUSER=root
DBEXISTS=$( echo "SHOW DATABASES LIKE '${DBNAME}'" \
| mysql --defaults-extra-file=${CREDSFILE} -NB INFORMATION_SCHEMA )
if [ "${DBEXISTS}" == "${DBNAME}" ] ; then
echo Preparing --ignore-tables parameters for all relevant views
echo
#build --ignore-table parameters list from list of all views in
#relevant database - as mysqldump likes to recreate views as tables
#we pair this with an export of the view definitions later below
SKIPVIEWS=$(mysql --defaults-extra-file=${CREDSFILE} \
-NB \
-e "SELECT \
CONCAT( '--ignore-table=', TABLE_SCHEMA, '.', TABLE_NAME ) AS q \
FROM INFORMATION_SCHEMA.VIEWS \
WHERE TABLE_SCHEMA = '${DBNAME}';" )
if [ "$?" == "0" ] ; then
echo Exporting database ${DBNAME}
echo
mysqldump --defaults-extra-file=${CREDSFILE} ${SKIPVIEWS} \
--add-locks --extended-insert --flush-privileges --no-autocommit \
--routines --triggers --single-transaction --master-data=2 \
--flush-logs --events --quick --databases ${DBNAME} > ${EXPORTFILE} \
|| echo -e "\n\nERROR: ${SRCHOSTNAME} failed to mysqldump ${DBNAME}"
echo Exporting view definitions
echo
mysql --defaults-extra-file=${CREDSFILE} \
--skip-column-names --batch \
-e "SELECT \
CONCAT( \
'DROP TABLE IF EXISTS ', TABLE_SCHEMA, '.', TABLE_NAME, \
'; CREATE OR REPLACE VIEW ', TABLE_SCHEMA, '.', TABLE_NAME, ' AS ', \
VIEW_DEFINITION, '; ') AS TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS \
WHERE TABLE_SCHEMA = '${DBNAME}';" >> ${EXPORTFILE} \
|| echo -e "\n\nERROR: ${SRCHOSTNAME} failed to mysqldump view definitions"
echo Export complete, preparing to transfer export file and import
echo
STATUSMSG="SUCCESS: database ${DBNAME} synced from ${SRCHOSTNAME} to ${DESTSERVER}"
scp \
${EXPORTFILE} \
${SSHUSER}@${DESTSERVER}:${EXPORTPATH}/ \
|| STATUSMSG="ERROR: Failed to SCP file to remote server ${DESTSERVER}"
ssh ${SSHUSER}@${DESTSERVER} \
"mysql --defaults-extra-file=${CREDSFILE} < ${EXPORTFILE}" \
|| STATUSMSG="ERROR: Failed to update remote server ${DESTSERVER}"
ssh ${SSHUSER}@${DESTSERVER} \
"rm ${EXPORTFILE}" \
|| STATUSMSG="ERROR: Failed to remove import file from remote server ${DESTSERVER}"
rm ${EXPORTFILE}
echo ${STATUSMSG}
else
echo "ERROR: could not obtain list of views from INFORMATION_SCHEMA"
fi
else
echo "ERROR: specified database not found, or SQL credentials file not found"
fi
else
echo -e "Usage: synctestdb DBNAME DESTSERVER \nPlease only run this script from the live production server\n"
fi
So far it appears to work, though you may want to tweak it for your purposes. Be sure that wherever your credentials file is, it is set with secure access rights, so that unauthorized users cannot read it!
到目前为止，它似乎有效，尽管您可能想根据自己的目的对其进行调整。请确保无论您的凭据文件在哪里，它都设置了安全访问权限，以便未经授权的用户无法读取它！
As it seems to be difficult to export views properly, I adapted olliiiver's answerto make it so that first we delete any tables or views with the exact names of valid views on the database we are importing into in case they exist, then importing all tables, which may erroneously create those views as tables, then delete those tables and define those views properly.
由于似乎很难正确导出视图，我调整了olliiiver的答案以使其首先删除具有我们正在导入的数据库上的有效视图的确切名称的任何表或视图，以防它们存在，然后导入所有表，这可能会错误地将这些视图创建为表，然后删除这些表并正确定义这些视图。
Basically here is how it works:
基本上这里是它的工作原理：
- verify existence of the database you specified on the command line
- use MYSQLDUMP to create a dump file
- SCP the dump file from production to the specified test server
- issue import commands on the specified test server over SSH and return output
- remove dump file from both servers after complete
- issue some reasonable output for most steps along the way
- 验证您在命令行中指定的数据库是否存在
- 使用 MYSQLDUMP 创建转储文件
- 将转储文件从生产环境 SCP 到指定的测试服务器
- 通过 SSH 在指定的测试服务器上发出导入命令并返回输出
- 完成后从两台服务器中删除转储文件
- 为沿途的大多数步骤发出一些合理的输出
回答by Thorstein
Thanks for this - very useful.
谢谢你 - 非常有用。
One hiccup though - perhaps as I have a slightly convoluted set of views that reference other views etc:
不过有一个小问题 - 也许是因为我有一组稍微复杂的视图，这些视图引用了其他视图等：
I found that the "definer" user needs to exist and have the right permissions on the target schema, otherwise mysql will not generate the views that reference other views as it things definitions are insufficient.
我发现“定义者”用户需要存在并且对目标模式具有正确的权限，否则 mysql 将不会生成引用其他视图的视图，因为它的定义不足。
In the generated:
在生成的：
/*!50013 DEFINER=<user>@<host>SQL SECURITY DEFINER */
/*!50013 DEFINER= <user>@ <host>SQL 安全定义器 */
--> ensure <user>@<host>is ok on your target instance, or replace this string with a user that does.
--> 确保<user>@<host>在您的目标实例上正常，或者用一个用户替换这个字符串。
Thanks Thorstein
谢谢托尔斯坦
