[thelist] MySQL PHPMyadmin

[Max Schwanekamp]

A Maynes wrote:
> Hi Max, I have heard that the command loine is the best method but
> runnig one from a remote machine to the server is the problem although I
> do have remote access.  Any suggestions? Examples of a command line
> import?

I just remembered - looks like you're hitting phpMyAdmin's ExecTimeLimit 
config value.  The default is 300 seconds.  It's set in [phpmyadmin 
root]/config.inc.php.

For doing a command-line import, here's a tip to do that, assuming 
you're using Windows.  The process in Unix and OSX is similar though.

<tip type="Howto: MySQL import to remote host from local Windows 
machine" author="Max Schwanekamp">
Some on the list will find this pedantic, but I thought some other list 
members may find this useful.  So, you have a 300MB MySQL export file, 
and you try to upload to your shared host via phpMyAdmin or similar app, 
and after waiting an amazingly long time you get only a timeout error 
message.  You could break up the file into smaller chunks and import it, 
but often it's a big pain to find what size "bite" phpMyAdmin can 
handle, and it varies according to server load.  Instead do the import 
via the command line.  It's fast and not difficult.  Since most shared 
hosts don't allow shell access, you need to it with a local MySQL 
installation.

You need MySQL running on both the destination host and your local 
machine. If you don't have it already, download and run Ye Olde Windows 
Installer for MySQL.  You need to have permission to connect to the 
destination database from your local machine.  This may require adding a 
new access host.  In a cPanel-based host, this is done on the MySQL page 
in cPanel (not in phpMyAdmin), at the bottom.  Use your local IP or %. 
Or contact the host provider and have them add this for you.  If you're 
not sure, just go to the next step and see if it works.

Once this is established, open a command prompt (Start > Accessories > 
Command Prompt) and start the MySQL executable to connect to the 
destination host.  For example:
C:\Program Files\MySQL\MySQL Server 4.1\bin\mysql --host=example.com 
--user=myusername --password
Enter password: *********
Welcome to the MySQL monitor [snip /]
mysql>

To import your local file to the remote database, select the destination 
database (important!!!) and use the \. command with the path to your 
local SQL file:
mysql> use mydatabase;
Database changed
mysql> \. C:\tmp\my_big_database.sql

The import will then start going.  Depending on you exported the file, 
you may get a success response for every row in each table.  Hint: when 
exporting via phpMyAdmin, check the "Extended Inserts" box.  This can 
speed things up when doing the import, as it will make each table a 
single query.  On the other hand if you have really giant tables, you 
might not want to do it that way, to avoid hitting memory limits.
</tip>

-- 
Max Schwanekamp
http://www.neptunewebworks.com/