Copy a database with data in MySQL

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 c开发者_JS百科reated. Is there a simple and effective way to do this using PHP / MySQL? Pure MySQL would be preferable.

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.

Thanks.

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

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:

MySQL to MySQL clone with PHP

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

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

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.

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

CREATE TABLE new_database.new_table LIKE old_database.old_table

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.

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.

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.

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.

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.

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

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.

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.