Learn, Share, Build

151
September 28, 2017, at 4:23 PM

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

Thanks in advance!

Answer 1

The easiest way is to try exporting the data from phpmyadmin. It will create the backup of your data.

But Sometimes, transferring large amount of data via import/export does result into errors.

You can try mysqldump to backup the data as well.

I found a few links for you here and here.

This is the mysqldump database backup documentation.

Hope it helps. :D

Answer 2

Create an export of your database. This should be easily done thru PhpMyAdmin interface. Once you downloaded the DB export, you need to create a new DB where you will put your exported data. This, too, should be easily done thru PhpMyAdmin user interface.

To upload it, we cannot use Import -> Browse your computer because it has a limit of 2MB. One solution is to use Import -> Select from the web server upload directory /var/lib/phpMyAdmin/upload/. Upload your exported data in this directory. After that, your uploaded data should be listed in the dropdown next to it.

If this fails too, you can use the command line import.

mysql -u user -p db_name < /path/to/file.sql
Answer 3

I suspect that PHPmyadmin will handle databases of that size (php upload/download limits, memory constraints, script execution time). If you have acccess to the console, i would recommend doing export/import via the mysql command line:

Export:

    $ mysqldump -u <user> -p<pass> <liveDatabase> | gzip > export.sql.gz

And Import:

    $ gunzip < export.sql.gz | mysql -u <user> -p<pass> <devDatabase>

after you have created the new devDatabase in e.g. PHPmyadmin or via command line.

Otherwise, if you only have access to an apache/php environment, i would look for an export utility that splits export in smaller chunks. MySQLDumper comes to mind, but it's a few years old and AFAIK it is no longer actively maintained and is not compatible with PHP 7+. But i think there is at least a pull request out there that makes it work with PHP7 (untested).

Edit based on your comment:

If the export already exists and the error occurs on import, you could try to increase the limits on your php environment, either via entries in .htaccess, changing php.ini or ini_set, whatever is available in your environment. The relevant settings are e.g. for setting via .htaccess (keep in mind, this will work only for apache evironments with mod_php and also can be controlled by your hoster):

      php_value max_execution_time 3600
      php_value post_max_size 8000M
      php_value upload_max_filesize 8000M
      php_value max_input_time 3600

This may or may not work, depending on x32/x64 issues and/or your hosters restrictions. Additionally, you need to adjust the PHPmyadmin settings for ExecTimeLimit - usually found in the config.default.php for your PHPmyadmin installation: Replace

      $cfg['ExecTimeLimit'] = 300;

with

      $cfg['ExecTimeLimit'] = 0;

And finally, you probably need to adjust your mysql config to allow larger packets and get rid of the 'lost connection' error: [mysqld] section in my.ini :

      max_allowed_packet=256M
READ ALSO
Learn, Share, Build

Learn, Share, Build

I'm performing sql requests on table interim_19 with 67500 rows

180
Learn, Share, Build

Learn, Share, Build

There are a lot of answers out there which explains nicely how to read hierarchical data from parent-child relationsI am using mySQL and I have created a query which reads all parents (parent_id) (concatenated through comma) for a given id:

159
Learn, Share, Build

Learn, Share, Build

I am working on a project which uses JDBCI wanted to fetch some data from a MySQL database to a JTable which is exactly what this code I found online does

179
Learn, Share, Build

Learn, Share, Build

I'm a noob in cakephpWorking on an opensource project

181