Copy a database with data in MySQL

Question

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.

Answer 1

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

Answer 2

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