CodeSlinger Posted October 2, 2005 Posted October 2, 2005 I'm doing some experimental stuff and I'd like to make an exact copy of an existing database. I tried the "Copy database to:" operation in phpMyAdmin but it doesn't work. If the target database exists, the create fails because the database already exists. If the target database does not exist, the create fails because phpMyAdmin doesn't have permission to create a database. It seems misconfigured for this operation to attempt to create a database. Does this operation simply not work at TCH? If it does work, how is that done? P.S. I know I could do the export / import sequence, but it seems like a lot of work when phpMyAdmin (should) support the operation directly, not to mention the upload / download of all that data over a not completely reliable connection. P.P.S. I found this old discussion but it seemed to indicate that this operation used to work, which is not helpful for my situation.
TCH-Andy Posted October 3, 2005 Posted October 3, 2005 Personally, I always use either the backup and restore function, or a short script which does a mysql dump, and then emails my backup every day.
CodeSlinger Posted October 3, 2005 Author Posted October 3, 2005 Personally, I always use either the backup and restore function, or a short script which does a mysql dump, and then emails my backup every day. I don't want to backup / restore, I want to make a duplicate. I want to to have a database B which is an exact copy of database A, so that I can test / check / upgrade software on B while A is still supporting a live website. E.g., I'm upgrading MovableType and I want to verify the successful operation of the new version without taking down the live, operational current version. There is very little documentation on the backup / restore of MySQL from CPanel, but what little I could find described only a restore operation with no provision for putting the data in a different database from whence it came. The "Copy Database To:" operation in PhpMyAdmin would do exactly what I want, if it worked. If I try to dump an SQL dump and upload that, there's a 50M limit which is not really all that big (not to mention the joy of waiting while 50M of data is uploaded).
TweezerMan Posted October 3, 2005 Posted October 3, 2005 I don't want to backup / restore, I want to make a duplicate. The "Copy Database To:" operation in PhpMyAdmin would do exactly what I want, if it worked. Copying / duplicating a database just isn't an option. The only way to create a new database is by adding one on the "MySQL Databases" page in CPanel. TCH servers are set up so that scripts do not have the ability to create databases on their own, and this includes phpMyAdmin functions that create databases. To effectively duplicate a database, you'll need to 1) create the new MySQL database in CPanel, 2) perform an export of your existing MySQL database, then 3) import the MySQL export into the new database. If I try to dump an SQL dump and upload that, there's a 50M limit which is not really all that big (not to mention the joy of waiting while 50M of data is uploaded). Assuming you're performing the export with phpMyAdmin, you should select the "gzipped" option under "Compression". This will reduce the size of the export file by 80-90%, which should allow you to export and import databases that are up to 250-500MB in size. A 50MB database should be compressed to into something like a 5-10MB file. I get about 83% compression with my MT 3.2 database when it is exported and gzipped.
CodeSlinger Posted October 4, 2005 Author Posted October 4, 2005 Yes, it looks like I will have to do that. I did some more investigating and have determined that PhpMyAdmin 2.6.1-pl2, which is what is in use at TCH, doesn't support copying without creating. However, PhpMyAdmin 2.6.3-pl1 has an extra checkbox labeled "CREATE DATABASE before copying" so that one can disable the database create and copy even with no create privileges. I had used the 2.6.3 version elsewhere, which is what lead to my confusion here.
CodeSlinger Posted October 5, 2005 Author Posted October 5, 2005 I tried the SQL export / import but that doesn't work either. I get the following error message on a 9M gzipped file: Fatal error: Maximum execution time of 300 seconds exceeded in /usr/local/cpanel/base/3rdparty/phpMyAdmin/libraries/read_dump.lib.php on line 91 Are there any other recommended procedures for duplicating a database?
Recommended Posts