我想导出一个数据库并在另一个实例中导入
在GCP上，我们在1个MySQL实例下有多个数据库。我想从GCP MySQL导出一个数据库（应该包括SP，触发器，视图，表，数据），并在另一个实例中导入它。我的数据库将具有100GB的数据，例如，我尝试了GCLOUD导出，但这并未导入SP。 MySqlDump将是相同的选项，但不确定如何使用它将其导出在存储桶中并将其导入新数据库。你能帮我吗？
We've multiple databases under 1 mysql instance on gcp. I want to export a single database (that should include SP, Triggers, views, tables, data) from gcp mysql and import it in another instance. My database would have 100gb of data e.g, i tried gcloud export but that does not import SP. mysqldump would be the option for the same but not sure how to use it to export it in a bucket and import it as new database. Can you please help me here?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
云SQL实例必须启用二进制记录，才能复制和创建备份。启用二进制记录后，触发创建需要MySQL Server上的超级特权。 > 1 。在调查了这个问题后，我发现存储程序也是如此。
在Cloud SQL中，您不能拥有具有超级特权的用户
解决此问题的一种方法是设置log_bin_trust_function_creators flag 3 在云SQL实例。为了安全地使用此标志，您应该确保存储的过程/触发器在主和副本中的结果将具有相同的结果。如果您对此充满信心，则不应有任何问题。
如果您想在使用mySqlDump命令时将触发器与存储过程一起导出，则应添加-triggers flag 5 。
总结：
要从Cloud SQL实例导出存储过程，请包括-ROUTINES标志。要从云SQL实例导出触发器，请确保未指定-SKIP-Tigggers。如果启用了二进制记录，并且要导出触发器和存储过程，则还必须在mySQL数据库中设置log_bin_trust_function_creators flag
Cloud SQL instances must have Binary logging enabled in order to replicate and create Backups. When Binary logging is enabled, Trigger creation requires SUPER privileges on the MySQL server 1. Upon investigating this issue I found that this is also the case for Stored Procedures.
In Cloud SQL, you can't have users with SUPER privileges 2.
One way to workaround this is to set the log_bin_trust_function_creators flag 3 in the Cloud SQL instance 4. In order to use this flag safely you should be sure that the stored procedures/triggers will have the same result in the master as well as in the replicas. If you are confident about that, you should not have any issue.
If you wanted to export the triggers alongside the stored procedures when using the mysqldump command, you should add the --triggers flag 5.
To sum up:
To export stored procedures from a Cloud SQL instance, include the --routines flag. To export triggers from a Cloud SQL instance, make sure that --skip-triggers is not specified. If binary logging is enabled and you want to export triggers and stored procedures, you must also set the log_bin_trust_function_creators flag in your MySQL database