asked on

How To Import MySQL Database using phpMyAdmin

I am attempting to upload a MySQL Database from my local machine using phpMyAdmin on a new Amazon instance.

In a compressed form, using Zip, the file is 10.2 MB.

The error that myPHPAdmin is showing is "You probably tried to upload a file that is too large. Please refer to documentation for a workaround for this limit."

I have followed the directions listed in the link for the FAQ (found at http://34.233.110.231/phpMyAdmin/doc/html/faq.html#faq1-16)

and I've normalziaed according to this set of instructions: https://www.webtrickshome.com/forum/how-to-fix-phpmyadmin-error-incorrect-format-parameter-that-appeared-while-importing-a-database

E.g.

max_execution_time = 3000
max_input_time = 60
memory_limit = 128M
post_max_size = 200M
upload_max_filesize = 200M

Is there something I'm missing? How can I import this database using phpMyAdmin?

Thanks for your help.

Robert Granlund

You may need to upload it to the root and then import it that way.

Tessando

ASKER

I can't seem to find a decent set of directions on WHERE to actually upload the database too. In other words, I have the Secure File Copy command ready to go I just don't know where to find the location of the existing database(s). Anyone got any tips on how to find out? Armed with that I can proceed with Robert's suggestion.

Thx

Dave Baldwin

Is the uncompressed file in SQL (text) format? phpMyAdmin doesn't know what to do with MySQL native format files.

Tessando

ASKER

The uncompressed role is in SQL format. I’ve uploaded this exact database before on different machines through phpMyAdmin.

Thx for the suggestion.

Dave Baldwin

Try it in uncompressed format.

Tessando

ASKER

Thanks David - In the uncompressed format it's over 240 MB and times out at about 40% using the phpMyAdmin UI.

Once inside of MySQL

mysql -u root -p

Open in new window

I used this command:

select @@datadir;

Open in new window

and found that the databases are at /var/lib/mysql

I attempted to upload again using the phpMyAdmin UI on a different internet connection, but no luck.

Now that I know the location of where my databases are, does anyone have a command that I can use in order to propagate that database? In other words, I've already made the "new" database using phpMyAdmin but it's empty. I'd like to import the tables from my local machine to this newly created database.

Thanks for all your help. I appreciate your contribution.

....

Dave Baldwin

The databases are at /var/lib/mysql are Not in SQL text format, they are in the native formats for MySQL MyIsam and InnoDB.

The 240 MB uncompressed file size probably explains it. When you upload a compressed file, it first has to be uncompressed to import it. I increased my PHP memory limit to 1560M.

When I have had SQL files that were too big for phpMyAdmin to import, I used a text editor to split them into smaller files. You have to split it in places that allow the SQL statements to be complete in each smaller file.

In recent versions of MySQL export files, there is a section at the end that goes thru and recreates indexes for all the tables. In my experience, that section takes as long as the entire data import before it because it has to go thru the tables to recreate the indexes.

ASKER CERTIFIED SOLUTION

Tessando

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dave Baldwin

That's a good solution. phpMyAdmin is slow compared to MySQL's native code.