How to make a copy of large database from phpmyadmin?

Question

I want to create a dev environment of my website on the same server. But I have a 7Gb of database which contains 479 tables and I want to make a copy of that database to the new DB.

I have tried this with the help of PHPmyadmin >> Operations >> copy database to functionality. But every time it will fail and return the error

Error in processing request Error code: 500 Error text: Internal Error. 

Please let me know there is any other method/ solution to copy this database to a new database from cpanel please advise

Answer 1

Limited to phpMyAdmin? Don't do it all at once

Large data-sets shouldn't be dumped (unless it's for a backup), instead, export the database without data, then copy one table at a time (DB to DB directly).

Export/Import Schema

First, export only the database schema via phpMyAdmin (uncheck data in the export options). Then import that onto a new database name.

Alternatively, you could use something like below to generate statements like below, once you've created the DB. The catch with this method is that you're likely to lose constraints, sprocs, and the like.

CREATE TABLE [devDB].[table] LIKE [prodDB].[table]

Copy data, one table at a time.

Use a good editor to create the 470 insert statements you need. Start with a list of table names, and use the good old find-and-replace.

INSERT INTO [devDB].[table] SELECT * FROM [prodDB].[table];

This may choke, depending on your environment. If it does, drop and recreate the dev database (or empty all tables via phpMyAdmin). Then, run the INSERT commands a few tables at a time.

Database Administration requires CLI

The real problem you're facing here is that you're trying to do database administration without access to the Command Line Interface. There are significant complicated details to migrating large sets of data efficiently, most of which can only be solved using tools like mysqldump.