php 在 MySQL 中复制包含数据的数据库

Copy a database with data in MySQL

提问by webnoob

I have a base set of data held in a database on my server. When a user signs up for my service, I want to be able to copy this database to another database that has been created. Is there a simple and effective way to do this using PHP / MySQL? Pure MySQL would be preferable.

我的服务器上的数据库中保存了一组基本数据。当用户注册我的服务时，我希望能够将此数据库复制到另一个已创建的数据库中。有没有一种简单有效的方法可以使用 PHP/MySQL 来做到这一点？纯 MySQL 会更可取。

I thought about looping through all the tables in the base database but I wouldn't know how to then create that table with columns to the new database.

我想过遍历基本数据库中的所有表，但我不知道如何创建包含新数据库列的表。

Running PHP 5.1 and MySQL 5.

运行 PHP 5.1 和 MySQL 5。

Thanks.

谢谢。

采纳答案by IAmTimCorey

Here is an article with ten ways to back up a database and restore it. Each uses a different method, most of which probably work in your situation but a few apply:

这是一篇包含十种备份和恢复数据库方法的文章。每种方法都使用不同的方法，其中大部分方法可能适用于您的情况，但有一些适用：

http://www.noupe.com/how-tos/10-ways-to-automatically-manually-backup-mysql-database.html

http://www.noupe.com/how-tos/10-ways-to-automatically-manually-backup-mysql-database.html

Number six talks about creating a dump file and then restoring it again. You could use this to dump the data out and then you could restore it to the new database.

第六个讨论创建转储文件，然后再次恢复它。您可以使用它来转储数据，然后您可以将其恢复到新数据库。

The other option here would be to make a physical copy of the databases. If you are storing the databases in different locations, this might be an option. It wouldn't be quite this simple but it should work fine.

这里的另一个选择是制作数据库的物理副本。如果您将数据库存储在不同的位置，这可能是一个选项。它不会这么简单，但它应该可以正常工作。

Finally, you could run a script from PHP that would do the MySql dump command for you. This would allow you to copy the entire database and set it up somewhere new so you wouldn't even have to have a database in place yet to accomplish this:

最后，您可以从 PHP 运行一个脚本，该脚本将为您执行 MySql 转储命令。这将允许您复制整个数据库并将其设置在新的地方，因此您甚至不必在适当的位置建立数据库来完成此操作：

MySQL to MySQL clone with PHP

MySQL 到 MySQL 克隆与 PHP

回答by thefreeman

If you have permission to use make "exec" system calls you can do something like the following

如果您有权使用 make "exec" 系统调用，您可以执行以下操作

exec("mysql -u".$DB_USER." --password='".$DB_PASS."' -e 'DROP DATABASE IF EXISTS `".$NEW_DB."`; CREATE DATABASE `".$NEW_DB."`;'");
exec("mysqldump -u".$DB_USER." -p'".$DB_PASS."' ".$EXISTING_DB." | mysql -u ".$DB_USER." --password='".$DB_PASS."' ".$NEW_DB);

This will drop $NEW_DB if present, and recreate it, then dump all tables and data from $EXISTING_DB into $NEW_DB

这将删除 $NEW_DB（如果存在），并重新创建它，然后将 $EXISTING_DB 中的所有表和数据转储到 $NEW_DB

Disclaimer: It is generally not a good idea to pass your mysql password on the command line. I am not sure, but I would guess that this could probably be viewed by someone with root access who has the ability to view all processes and the command line options that started them.

免责声明：在命令行上传递您的 mysql 密码通常不是一个好主意。我不确定，但我猜想这可能会被具有 root 访问权限的人查看，他有能力查看所有进程和启动它们的命令行选项。

Also, in terms of your other question about how to create a table in a new database with columns matching another, you can use the following SQL

另外，关于如何在新数据库中创建表的其他问题，其列与另一个匹配，您可以使用以下 SQL

CREATE TABLE new_database.new_table LIKE old_database.old_table

回答by Adrian

My preferred way is to use the Migration Wizardfrom MySQL Workbench. After some uses/practice it is really easy and fast to use (~ 5 min after some uses).

我的首选方法是使用迁移向导从MySQL工作台。经过一些使用/练习后，使用起来非常简单快捷（一些使用后约 5 分钟）。

Hint: The most tricky part is "Object Migration" -> "Manual Editing". The you should switch to "View: All Objects" to adjust you new schema.

提示：最棘手的部分是“对象迁移”->“手动编辑”。您应该切换到“查看：所有对象”以调整您的新架构。

Hint 2: You also can migrate/copy and make a backup at the same time. The old database will of course be preserved.

提示 2：您还可以同时迁移/复制和进行备份。旧数据库当然会被保留。

Another relative good tool is the synchronization feature in phpMyAdmin. It's a little hacky and not so intuitive but may work if you can't use the Workbench.

另一个相对不错的工具是phpMyAdmin 中的同步功能。它有点老套，不那么直观，但如果您不能使用 Workbench，它可能会工作。

回答by Derrick Zhang

I don't think copying a database for each sign up is a good chioce. You should let each signed user to share the base database and query for the required data as needed, rather than making so much duplications.

我不认为为每个注册复制一个数据库是一个好的选择。您应该让每个签名用户共享基础数据库并根据需要查询所需的数据，而不是进行如此多的重复。

And if you know the schema of your base database, I don't see why you have problems creating corresponding tables.

如果您知道基础数据库的架构，我不明白为什么您在创建相应的表时会遇到问题。

The more customers you have, the more you should think of sharing rather than copying. Althou database is designed for transactions, you should avoid unnecessary writes as much as possible coz that takes way too much time and resource.

您拥有的客户越多，您就越应该考虑共享而不是复制。尽管数据库是为事务设计的，您应该尽可能避免不必要的写入，因为这会占用太多时间和资源。

回答by eroomydna

If I've understood correctly you want to perform some type of snapshot each time a user signs up. This isn't something that will scale well. My recommendations;

如果我理解正确，您希望在每次用户注册时执行某种类型的快照。这不是可以很好地扩展的东西。我的建议；

MySQL: Use a trigger. MySQL: Use MySQL Replication PHP: Write the same statement to two different places.

MySQL：使用触发器。MySQL：使用 MySQL 复制 PHP：将相同的语句写到两个不同的地方。

Replication is probably the best way to achieve the desired result plus it will allow you to perform adhoc reporting on the data without adding load to your primary server.

复制可能是实现所需结果的最佳方式，而且它允许您对数据执行临时报告，而不会增加主服务器的负载。

回答by slashmais

Step 1. You will need to write SQL-statements to create each database and its (empty) tables. You can do this in your php-code. If there are any extras, e.g.: indices, triggers, sp's etc, you will have to likewise create them.

步骤 1. 您需要编写 SQL 语句来创建每个数据库及其（空）表。您可以在 php 代码中执行此操作。如果有任何额外内容，例如：索引、触发器、sp 等，您也必须同样创建它们。

Step 2. still within your php-code, connect to your base-database as well as the new one and execute your SQL-statements to copy (select..insert) the base-data into the new database.

步骤 2. 仍然在您的 php 代码中，连接到您的基本数据库以及新数据库并执行您的 SQL 语句以将基本数据复制（选择..插入）到新数据库中。