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
9
Answers
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 useImport -> 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.
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. 😀
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:
And Import:
after you have created the new dev database 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
, changingphp.ini
orini_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 environments with mod_php and also can be controlled by your hoster):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 theconfig.default.php
for your PHPMyAdmin installation:Replace
with
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 :
NOTE: I have just read your comment, and as I can understand you don’t have access to command line. Please check
Solution Two
, this will definitely work.The only solution that will work for you (which work for me at 12GB database) is directly from the command line:
Solution One
If you have root access you can create bash script:
Source
Solution Two
Also, there is another option which is very good for those who are on shared hosting and don’t have command line access. This solution worked for me on 4-5GB files:
$linespersession = 3000;
Change to$linespersession = 30000;
)Solution Three:
This solution definitely works, it is slow but works.
Download Trial version of (32 or 64 bit): Navicat MySQL Version 12
Install -> and RUN as Trial.
After that Add your Computer IP (Internet IP, not local IP), to the MySQL Remote in cPanel (new database/hosting). You can use wildcard IP in cPanel to access MySQL from any IP.
Goto Navicat MySQL: click on Connection put a connection name.
In next “Hostname/IP” add your “Hosting IP Address” (don’t use localhost).
Leave port as it is (if your hosting defined a different port put that one here).
add your Database Username and Password
Click Test Connection, If it’s successful, click on “OK”
Now on the Main Screen you will see all the database connected with the username on the left side column.
Double click on your database where you want to import SQL file:
Icon color of the database will change and you will see “Tables/views/function etc..”.
Now right click on database and select “Execute SQL file” (http://prntscr.com/gs6ef1).
choose the file, choose “continue on error” if you want to and finally run it. Its take some time depending on your network connection speed and computer performance.
You can use
mysqldump
as followYou can also make use of my shell script, which actually wrote long back for creating back-up of MySQL database on regular basis using cron job.
I have already written an article on Schedule MySQL Database backup on CPanel or Linux.
Here’s how I handled that problem when I faced it… Unfortunately this only works for Mac OS.
I hope this helps. It’s a little work around, but it’s worked really well for me, especially when PMA has failed.
Since the requirements include PHPMyAdmin, my suggestion is to:
Then try to import the generated file in the new Database you have (if you have sufficient resources – this should be possible).
Note: My previous experience shows that using compression allows larger DB exports/import operations but have not tested what is the upper limit in shared hosting environments (assuming this by your comment for cPanel).
Edit: When your export file is created, select the new database (assuming it is already created), go to the “Import” tab, select the file created from the export and start the import process.
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.
If you have you database in your local server, you can export it and use BigDump to inserting to new database on the global server BigDump