Copy database with data in MySQL

Question

I have a basic set of data stored in a database on my server. When a user subscribes to my service, I want to be able to copy this database to another created database. Is there a simple and efficient way to do this using PHP / MySQL? Pure MySQL would be preferable.

I thought about sorting through all the tables in the database, but I don't know how to create a table with columns in a new database.

Running PHP 5.1 and MySQL 5.

Thanks.

Answer 1

Here's an article with ten ways to back up and restore a database. Each one uses a different method, most of which probably work in your situation, but some of them 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 restores it again. You can use this to upload data, and then you can restore it to a new database.

Another option here is to make a physical copy of the databases. If you store databases in different places, this may be an option. It would not be so simple, but it should work fine.

Finally, you can run the script from PHP, which will make the MySql dump command for you. This will allow you to copy the entire database and set it up somewhere new, so you would not even need to have a database for this:

MySQL to MySQL clone with PHP

Answer 2

If you have permission to use the 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, and then flush all tables and data from $ EXISTING_DB to $ NEW_DB

Denial of responsibility. It is generally not recommended to pass your mysql password on the command line. I'm not sure, but I would suggest that this could probably be seen by someone with root privileges, who has the ability to view all the processes and command line options that started them.

In addition, from the point of view of your other question on how to create a table in a new database with columns corresponding to others, you can use the following SQL

 CREATE TABLE new_database.new_table LIKE old_database.old_table 

Answer 3

Several parameters:

• http://www.ivorde.ro/How_to_copy_a_mysql_database_using_mysqldump-22.html or MySQL-Utilities / Tomakeacopyoflivedata.htm "> http://www.java2s.com/Tutorial/MySQL/0520_MySQL-Utilities/Tomakeachofmivedm execute using php system call.

• phpMyAdmin does this. You can see this code here

Answer 4

Here's another tutorial on backing up and restoring data (from a backup) of a MySQL database.

http://www.devshed.com/c/a/MySQL/Backing-up-and-restoring-your-MySQL-Database/

Answer 5

I don't think copying a database for every post is a good chioce. You must allow each subscribed user to share the base database and request the necessary data, rather than making so many duplicates.

And if you know the schema of your database, I don’t understand why you have problems creating the appropriate tables.

The more customers you have, the more you should think about sharing, not copying. The Althou database is intended for transactions, you should avoid unnecessary records as much as possible, which takes too much time and resources.

Answer 6

If I understand correctly, you want to take some kind of snapshot every time a user subscribes. This is not something that will scale well. My recommendations;

MySQL: use a trigger. MySQL: use replication MySQL PHP: Write the same expression in two different places.

Replication is probably the best way to achieve the desired result, and this will allow you to run adhoc reports on the data without adding load to your main server.

Answer 7

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 additional functions, for example: indexes, triggers, sp, etc., you also need to create them.

Step 2. is still inside your php code, connect to the database, as well as to the new one, and execute your SQL statements to copy (select..inert) the base data to the new database.

Answer 8

My preferred way is to use the Migration Wizard from MySQL Workbench . After some uses / practices, it is very easy and quick to use (~ 5 minutes after some uses).

Hint: The most difficult part is “Object Migration” → “Manual Editing”. You need to switch to "View: All Objects" in order to set up a new scheme.

Hint 2. You can also transfer / copy and backup at the same time. The old database, of course, will be preserved.

Another useful good tool is the synchronization function in phpMyAdmin . This is a bit hacky and not very intuitive, but may work if you cannot use Workbench.