Database dump to large to import via PHPMyAdmin

[bluejay51]

Hi

My database dumps are getting too large to import using phpMyAdmin. If it were my host, it wouldn't be an issue but I also have an emergency backup server in-house and this is where I'm experiencing the problem. I've tried adjusting various parameters in the PHP and MySQL config files and have tried a number of other things like exporting only a handful of tables at a time and then importing them but even the SMF_Messages table is too large to import by itself and I'm not sure if there is a way to actually split individual tables (is there?). I've also tried compression, etc. I've heard of "Big Dump" and a few others but I'd rather not go that route. Any suggestions would be really appreciated.

- Thanks, BlueJay

[flapjack]

if you cannot change upload limits then BigDump or similar is your only hope. Unless you want to split the sql file into pieces and import it one by one

[Michele]

Check the "post_max_size" setting in your php.ini file. It defaults to 8M, so you may want to make it larger to upload a large SQL file.

HTH, Michele

[ziycon]

You can always import through the command line, most hosts will give you the option to use an ssh shell.

[Illori]

You can always import through the command line, most hosts will give you the option to use an ssh shell.

Actually most shared hosts do NOT give you the option to use an ssh shell and most people on shared hosting have no idea how to use an ssh shell.

[Norv]

IMHO, it definitely doesn't hurt to ask the host about shell access, and mysqldump use.

What exactly does phpmyadmin say when it fails? How big is the backup that you manage to download?

[feline]

Try mysqlDumper (http://www.mysqldumper.net/). With this you can split the backups into more smaller pices...

[bluejay51]

First, I want to thank you all for the advice. As I mentioned, this is not my host I'm dealing with an in-house server where I have physical access to the machine running the server itself including all of the configuration files, etc.

Maybe it will help if I include my current PHP config settings for this server (as you can see I have increased the values well above the PHP defaults)...

Maximum Script Execute Time (s.): 900

Maximum Memory Amount (MB): 512M

Maximum Post Size: 900M

Maximum Upload Size: 900M

Can I increase any of these values further and what should I increase them to?. Should I lower some and raise others for "balance"?. Are there any other values I can adjust that are not shown above (ie; in the my.ini or php.ini files?.

Thanks again!, BlueJay

[ziycon]

If you have access to the server then you can run the command line import, its formatted like below:

Code Select Expand

mysql -uusername -p database_name < database.dump

This can be run from the command line of the database server(you said you have access to it?), you'll also be prompted for your database password when you run the command.

- username needs to be replaced with your database username.
- database_name is the name of the database your importing into
- database.dump is the file your importing, it can be a .sql also.

If your running this on a different server you will have to add in the host parameter, replace 127.0.0.1 with the hostname or IP of your database server.

Code Select Expand

-h127.0.0.1

Code Select Expand

mysql -uusername -p -h127.0.0.1 database_name < database.dump

[bluejay51]

It's a WAMP on a Windows machine. I have no access to fancy tools or the use of a terminal in which to input various arguments. Also, everything is self-contained and while it comes with phpMyAdmin, there is really no way of installing any external tools through which to access the server or database. BigDump is not an option either.

Since I can easily import every file except smf_messages, what would be really helpful would be if there was a simple GUI tool out there that would allow me to either split this table or split the entire database into smaller pieces.

Does such a tool exist and if so, how do I then import each piece through phpMyAdmin?.

- Thank you, BlueJay

[SlammedDime]

Why isn't big dump an option?  It's a PHP script that you can place in any directory on the server with the sql dump and import the file.

Do you not have any command line access at all to the terminal?  If so, the mysql binaries installed on windows can still be used via the command line, or you could simply write a PHP script to utilize those binaries with the exec() command.

[bluejay51]

Why isn't big dump an option?  It's a PHP script that you can place in any directory on the server with the sql dump and import the file.

Do you not have any command line access at all to the terminal?  If so, the mysql binaries installed on windows can still be used via the command line, or you could simply write a PHP script to utilize those binaries with the exec() command.

Hi Slammed.

I don't have any "hard memory limits" or any other limits as far as the machine or PHP/MySQL settings are concerned. I can make the php memory limit 4 GB if I want. I can change the max execution time to 2 days, etc. So I really don't see BigDump as a solution since I don't have any limits that require me to use a script that allows me to get past limits set by a host.

I'm really just hoping to get some information on what values I can use for php.ini or my.ini, etc that will allow me to import a large file via phpMyAdmin. Right now, I have...

Maximum Script Execute Time (s.): 900

Maximum Memory Amount (MB): 512M

Maximum Post Size: 900M

Maximum Upload Size: 900M

Can I increase these values?. In what increments?. Is there a limit on how high I can set these values?. Are there any other settings in the config files I can edit that will allow me to import a 300+MB dump?.

- Thanks again, BlueJay