9

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.

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.

webnoob
  • 15,747
  • 13
  • 83
  • 165
  • 1
    Not to be presumptuous... but are you sure you want to do this? – Dave Kiss Jun 05 '11 at 05:04
  • 1
    Well yes. Whats the reason behind that question? I have a database that will always be updated with the latest changes to the program. It makes sense in my mind to use that as a set of base data ... – webnoob Jun 05 '11 at 05:07
  • 1
    you can try mysql dump command then again import it :P – Gaurav Shah Jun 05 '11 at 05:09
  • If you're looking for a way to version your web application in terms of the database, this is a poor way to do it. You're going to end up with tons of databases, and a lot of redundant data. – SamT Jun 05 '11 at 05:13
  • 2
    The database changes very often as the product is being updated constantly. I am just looking for ways to copy a set of default data, Not criticism behind the structure of my application of which I have given no information on. – webnoob Jun 05 '11 at 05:50
  • @webnoob: read ["Is it unreasonable to assign a MySQL database to each user on my site?"](http://stackoverflow.com/questions/327787/is-it-unreasonable-to-assign-a-mysql-database-to-each-user-on-my-site) and ["Should I use a single or multiple database setup for a multi-client application?"](http://stackoverflow.com/questions/255616/should-i-use-a-single-or-multiple-database-setup-for-a-multi-client-application). A separate database per user is less performant when you have too many users, as MySQL will have to keep track of more resources. – outis Jun 05 '11 at 10:59
  • 2
    ... Try [partitioning](http://dev.mysql.com/doc/refman/5.1/en/partitioning.html) and set appropriate [indices](http://dev.mysql.com/doc/refman/5.1/en/optimization-indexes.html) if you're concerned about performance. As for "criticism behind the structure of [your] application", sometimes the correct answer is the immediate task is the wrong thing to do and you should achieve your [overall goal](http://www.catb.org/~esr/faqs/smart-questions.html#goal) in a different way. – outis Jun 05 '11 at 11:02
  • Maybe there is something like http://www.liquibase.org/ for PHP or maybe just write the database setup and update task in Java. We use Liquibase for updating several client databases at once. – Adrian Jun 07 '13 at 08:17

6 Answers6

9

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

Community
  • 1
  • 1
IAmTimCorey
  • 16,412
  • 5
  • 39
  • 75
6

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
Rein Baarsma
  • 1,466
  • 13
  • 22
thefreeman
  • 1,035
  • 11
  • 13
0

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.

Adrian
  • 2,233
  • 1
  • 22
  • 33
  • Okay, just saw you want to do it on the fly when a customer, when it signs up. So my answer not really fits, but it is still a good way to copy a database quite fast and secure. – Adrian Jun 07 '13 at 08:15
0

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.

Derrick Zhang
  • 21,201
  • 18
  • 53
  • 73
  • I have over 150 customers, rows can grow into the millions. One database isn't a good idea and not a very good suggestion imo. – webnoob Jun 05 '11 at 05:42
  • If your database is designed properly (with indexes etc) then millions of rows shouldn't cause any problems and you do not have to copy your database for each user. If the database is not properly designed, you'll run into problems that you cannot solve by copying the database for each user. – Arjan Jun 05 '11 at 10:13
0

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.

eroomydna
  • 1,261
  • 9
  • 4
0

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.

slashmais
  • 7,069
  • 9
  • 54
  • 80