Importing a .sql file

[filburt1]

I'm transitioning to a new host. I dumped my database into several .sql files. However I can't use phpmyadmin/Telnet/ssh yet to import them.

So what little script can I use to import the whole dealie, considering the entire file is just a bunch of SQL queries like this:

# phpMyAdmin MySQL-Dump
# version 2.2.5
# http://phpwizard.net/phpMyAdmin/
# http://phpmyadmin.sourceforge.net/ (download page)
#
# Host: localhost
# Generation Time: Jun 14, 2002 at 04:55 AM
# Server version: 3.23.48
# PHP Version: 4.0.6
# Database : `dh2255`
# --------------------------------------------------------

#
# Table structure for table `access`
#

CREATE TABLE access (
  userid int(10) unsigned NOT NULL default '0',
  forumid smallint(5) unsigned NOT NULL default '0',
  accessmask smallint(5) unsigned NOT NULL default '0',
  PRIMARY KEY  (userid,forumid)
) TYPE=MyISAM;

#
# Dumping data for table `access`
#

INSERT INTO access VALUES (2, 45, 1);
INSERT INTO access VALUES (12, 45, 0);
INSERT INTO access VALUES (61, 52, 1);
INSERT INTO access VALUES (119, 76, 1);
# --------------------------------------------------------

#
# Table structure for table `adminlog`
#

CREATE TABLE adminlog (
  adminlogid int(10) unsigned NOT NULL auto_increment,
  userid int(10) unsigned NOT NULL default '0',
  dateline int(10) unsigned NOT NULL default '0',
  script varchar(20) NOT NULL default '',
  action varchar(20) NOT NULL default '',
  extrainfo varchar(200) NOT NULL default '',
  ipaddress varchar(15) NOT NULL default '',
  PRIMARY KEY  (adminlogid)
) TYPE=MyISAM;

#
# Dumping data for table `adminlog`
#

INSERT INTO adminlog VALUES (1, 1, 1024023292, 'index.php', '', '', '68.54.85.254');
INSERT INTO adminlog VALUES (2, 1, 1024023294, 'options.php', '', '', '68.54.85.254');
INSERT INTO adminlog VALUES (3, 1, 1024023299, 'options.php', 'dooptions', '', '68.54.85.254');
.
.
.

[cpradio]

well you could make all of them valid php code and then run it as a script, or wait till you can use telnet, ssh, or phpMyAdmin.

[filburt1]

With all this work I think I'll just wait for SSH to be activated.

When it is, what do I do? mysqlimport dbname dbfile.sql?

[cpradio]

sorry I never have really used ssh or telnet for that type of thing. I really should but too little time to learn it.

Maybe scoutt will know.

[scoutt]

why do it the hard way. save the database in one big sql file and then use phpMyAdmin to load it all back in, why telnet?

you still have to have the database created so you can get the name then it should run no problem.

sorry I don't use telnet as most ISP disable it for security reasons.

[filburt1]

Originally posted by scoutt
why do it the hard way. save the database in one big sql file and then use phpMyAdmin to load it all back in, why telnet?

you still have to have the database created so you can get the name then it should run no problem.

sorry I don't use telnet as most ISP disable it for security reasons.

My old host doesn't have SSH/Telnet so I can't backup the whole thing at once. The problem is that the DB dump has a PHP operation timed out error which is obvious why it happens. Only the new host has SSH.

BTW I did mean SSH, not Telnet for the new host

[cpradio]

You said it timed out. Were you dumping it one table at a time or trying to dump the who database from phpMyAdmin?

I personally suggest clicking on the table name and dumping each individual table.

-Matt

[scoutt]

even if you did it table by table you can still use phpMyAdmin.

[filburt1]

There are tons of tables (55 or so). I did it like this:

Attachments
Posts
Everything else

No worries though, I was finally able to import everything via SSH.

[scoutt]

cool, but in the future you can get the table structor by itself then you can get the data in however many files it takes then you can load all the tables at once, then piece in the data. but if you prefer the SSH way then cool. FYI

[chrisjk]

*ignorant* what the hell is ssh?

[cpradio]

Read up:
http://www.ssh.com/products/ssh/

[chrisjk]

ahh, cheers

[filburt1]

Originally posted by chrisjk
*ignorant* what the hell is ssh?

Teh 1337 n0n-n00b unh4x0rable version of Telnet

[chrisjk]

all this talk of telnet and I just noticed my host now gives me telnet and SSH access

[filburt1]

Originally posted by scoutt
cool, but in the future you can get the table structor by itself then you can get the data in however many files it takes then you can load all the tables at once, then piece in the data. but if you prefer the SSH way then cool. FYI

The problem with that is that the structure probably only makes up a tiny sliver of the MySQL db size, the vast majority was in the actual data (attachments and posts).

But all is well in the land of SSH.

[Iamryan2002]

it would be possible to make a php script to do it for you... something like this:

<?
    $connect = mysql_connect("host", "user", "pass");
    mysql_select_db("db", $connect);

    // Open the sql file so it can be read and processed...
    $sql_file = fopen("file.sql", "r");  // Read Only..
    
    $query = fread($sql_file, filesize($sql_file); //Read the whole file into a variable
    fclose($sql_file);

    if(mysql_query($query, $connect)
    {
      echo "SQL File Finished Successfully!";
    }
    else
    {
      echo "Something Happened that wasen't good!";
    }
?>

No guarantee the syntax is perfect... working off of mind..