Restore Database from backed up .sql file

[lamoose]

Newbie here, I'm trying to upload a database using PhpMyAdmin but he server times out afet it is processed for a while. If I break it up into tons of little pieces it'll work.

(going from personal comp to hosted server)

1) How do I use Command Line? If that the issue to the time out prob.

2) What is the path to database from personal comp to server if on sever its localhost? (http://localhost.domainname.com?)

3) What is a good 'free' software to edit Sql files and such?

[RonPK]

Hi & welcome lamoose!

1) Upload the backup file. On the command line, type:

mysql -u [username] -p [database] < yourbackup.sql

You will be prompted for [username]'s password.

2) are we talking about the server name in the mysql_connect command? Usually something like www.domain.kom (without the [)...]

3) any good text editor should do. I guess search and replace would be an important feature. I'm using UltraEdit: not free, but very good.

[ergophobe]

PhpMyAdmin is so slow and cumbersome... but if you are having trouble from the command line, you can try uploading a zipped file. You should be able to get more uploaded without timing out.

Long term, though, the command line is so much more convenient for any large operations.

[lamoose]

Sorry guys, not understanding th e whole command line thing. So I should add this line to the sql file, upload to somewhere on the server and execute script from browser window? not sure

mysql -u [username] -p [database] < yourbackup.sql

[RonPK]

Upload the backup file as it is. (I assume it contains lines like DROP TABLE, CREATE TABLE and lots of INSERTs). Upload it with an SSH copy program, or otherwise ordinary FTP.

Open a shell connection to the server, with an SSH tool or, horror, telnet.
cd to the directory where you've put the backup file.
On the command line, type:

mysql -u myusername -p databasename < yourbackupfile

This calls the mysql program on the server ('mysql'), passes some parameters (username, databasename, -p to prompt for password) and feeds (< ) all the commands in yourbackupfile to the mysql client.
You will be prompted for [username]'s password. Username and password are the ones you use for mysql, not for the shell connection or the FTP upload.

This assumes that you are allowed to use mysql from the command line. And that mysql is in your path. If you get an error like "Command not found", you'll need to supply the full path to the mysql client. Often that's something like
/usr/local/mysql/bin/mysql

You can also run the command from within a PHP script, with a system command like exec().
exec("mysql -u myusername -pmypassword databasename < yourbackup.sql");
Note that there is no whitespace between -p and mypassword.

HTH. Please let us know how this worked out!

[ergophobe]

Nope.

You need shell access. So you telnet in or use SSH or something like that until you have a command-line window (unix shell I assume, but perhaps a windows DOS window if one a Win server).

So you would

- create a file with the SQL commands you need to execute.

- upload that file to somewhere on your server. root will be easiest.

- log in via SSH, telnet or whatever.

- use the following to move around and find your file
>pwd (shows present working directory)
>ls (lists files in dir)
>cd <dirname> (change location to dir <dirname>)
>cd .. (go to parent dir, ie up one level

When you locate your file that you FTPed to the site, THEN you TYPEON THE COMMAND LINE

mysql -u [username] -p [database] < yourbackup.sql

This sends the list of commands to the sql server.

Tom

[lamoose]

Thanks man, will let you know. Woon't be able to try till this evening.

I just a webdesigner trying to learn more programming.

So far. I have Dreamweaver, AceFtp and Notpad.

What tools do you suggests?

[ergophobe]

Whoops. RonPK posted while I was writing. The "Nope" referred to the previous post, not Ron's instructions.

Tom