PhpMyAdmin File too large error when trying to import sql

Question

Hey folks, I am trying to import a database (.sql) file using phpmy admin on a clients server.

I get a file too large error even thought I have the option to break the file into pieces etc but I still get the error. The upload size is 50MiB and the file I am trying to import is 90mb (for the db.)

I had the same error in MAM and know I could change the maz_size_limit and similar things, but I am not sure what to do on the clients server. I have access to the cpanel but can not see any options.

I can import the same file on my server, even though its set to 64MB,as it is able to chop up and import.

What are my options here?

I tried to use mysequal pro as it worked for MAMP on my local comp but am not able to do it for this clients server.

Any ideas?

Answer 1

Your best bet is to FTP it to the server and then ssh in (command line) and import the database that way. The resulting command will look something like:

mysql -hlocalhost -uUser -pPassword database_name < file_name

That way you can completely bypass any file upload or processing time restrictions.

Answer 2

What kind of access do you have to your client's server?

If you do have terminal access then the above answers will work.

Otherwise, a very ugly way to get it done is that you could create an .htaccess file to change PHP settings to get the sql file uploaded through PHPmyAdmin.

In your .htaccess file you would put:

php_value upload_max_filesize 80M
php_value post_max_size 80M
php_value max_execution_time 300
php_value max_input_time 300

Make sure you delete your .htaccess file as soon as you are done. Also, this will not work if your client's server is using Apache set not to allow overrides.

Answer 3

Another option:

/.../phpmyadmin3.2.0.1/config.inc.php

Find the line with $cfg['UploadDir'] on it and update it to:

$cfg['UploadDir'] = 'upload';

Create a directory called 'upload' within the phpmyadmin directory.

Place your file in this new upload directory, then you'll see a new dropdown on the Import page.

Source: http://daipratt.co.uk/importing-large-files-into-mysql-with-phpmyadmin/

Answer 4

Is the file you are trying to import in raw .sql form? If not, then try zipping/gzipping it. For text files, you can see HUGE reductions in file size, which might allow you to upload it without changing any PHP configuration files.

Answer 5

You can use the command line to import any uncompressed file, no matter the size.

mysql -h <host> -u <user> -p <database> < path/file.sql

• if you are using your localhost you don't need the -h and
• if the user do not have a password you don't need the -p

Answer 6

PHP usually has a file size restriction for uploads. Your best bet is to FTP the file and login through ssh and import the sql script through the mysql command line

Answer 7

You can do it in following ways;

1. You can go to control panel/cpanel and add host % It means now the database server can be accessed from your local machine. Now you can install and use MySQL Administrator or Navicat to import and export database with out using PHP-Myadmin, I used it several times to upload 200 MB to 500 MB of data with no issues

2. Use gzip, bzip2 compressions for exporting and importing. I am using PEA ZIP software (free) in Windows. Try to avoid Winrar and Winzip

3. Use MySQL Splitter that splits up the sql file into several parts. In my personal suggestion, Not recommended

   4. Using PHP INI setting (dynamically change the max upload and max execution time) as already mentioned by other friends is fruitful but not always.

   5. Also, you can use SQLDUMP via terminal, if SSH is available

Answer 8

Try compressing the .SQL file before uploading it. I've compressed 100MB+ down to ~10MB but phpMyAdmin fails with this error:

Fatal error: Allowed memory size of 94371840 bytes exhausted (tried to allocate 106954753 bytes)

If your uncompressed .SQL file is less than or equal to 94MB it should work just fine to zip it and upload.