MySQL n00b


#1

Guys, I’m no n00b to the 'net itself, however I have the chore of restoring a database to my site and don’t really know how to go about it. I have a backup.sql file on my hard drive that is just under 7MB and tried uploading it through myphpadmin here, however it times out. I read that I needed to SFTP to my created database hostname, and cannot with CuteFTP nor PuTTY. All I’m trying to do right now is upload this local file and put it up on my database host so that I can run Impex to merge the databases, but my problem is that I can’t find info as to how to get my local backup file from my HDD to the internet. Can somebody help me and give me guidance as to what programs to use and what username and password to use when I’m prompted? Thanks.


#2

Hmm well the best I can tell you is to do it manually through the phpmyadmin interface (databasename.yoursite.com). Go there and select your database from the drop down menu. Then click on sql and down at the bottom it will let you browse for your .sql file and hit go. Should it error on you, you might want to remove any comments from the file (the stuff with – infront of it etc) and then do it.

As for your username and password, I assume you created the database yourself right? Its the username and password you created for it. If you go to your control panel==>mysql you will see all the databases you have made at the bottom and the username for them but you are on your own with the password. If you just cannot remember you can create another user for the database.

The Insane Cabbit
My Blog http://www.sounanda.com
My web store:
http://www.marciesgifts.com
Send me a pm if you want cms or forum software installed (for a fee)


#3

I have tried it via the phpmyadmin interface just like you suggested, and I have got the ‘fatal time-out’ error.

I’ve heard of a php script called ‘BigDump’ to cut up the database into smaller files… any suggestions on that or another? I couldn’t figured out BigDump on my own to configure it, but I’m guessing that it’s not accepting it due to its size.

Thanks for the info on the username and password; I just wanted to be sure that that was the case.


#4

You can ‘cut up’ the file yourself you know. If your database is very huge just be sure not to cut off part of a command. Try like 10 or 20 commands at a time if necessary. the .sql file is just a text file. Copy and paste is your friend.

The Insane Cabbit
My Blog http://www.sounanda.com
My web store:
http://www.marciesgifts.com
Send me a pm if you want cms or forum software installed (for a fee)


#5

Roger that, however where would I cut? I’m so new to this, I don’t know what commands I could be potentially cutting off. My database is just under 8MB, so just cutting it in half and pasting the balance into a new text file is giving me errors on the second half, so obviously I’m cutting something off where it shouldn’t be. I really appreciate all you help, but I’m super new to this area.


#6

There’s no point in cutting up a 7mb file. Youd do that for a file in the hundreds of megabytes. If you’re getting an error with phpmyadmin, the next easiest way will be to upload the file and run the command from the shell.

This Wiki article should be of some help. But here’s the basics.

  1. log into FTP. Upload the backup.sql file, just drop it into that first folder you get when you log into FTP. That’s your home folder.
  2. next you’re going to log into ssh. You’ll have to use a program like hyper terminal in windows or a program called putty. You have to log into ssh or telnet (same thing) with a user/pass that has shell access. You can check this in the Panel > Users > Manage Users > Edit.
  3. Now you’ll need to type in the command to dump your database. This is where that wiki article or the k-base will come in handy. Look there for the command to type. Just remember to update the relevant parts like your database info and the user/pass.

Becuase you uploaded the to your home directory, when you type that command, you don’t have to worry about giving it a direcotry path to your file. Let me know if somethign isn’t making sense.

-Matttail


#7

Ok, here’s where we come again back to one of my original questions re: username and password. I tried using PuTTY before and typed in the hostname of the database I created; not the domain itself. I then used the username and password to the database hostname with PuTTY, and it tells me that my information is incorrect, yet that same information logs me just fine with the CP here with Dreamhost through phpmyadmin. Now my backup sql file is uploaded to the root directory via FTP, but I can’t use the shell to execute the file dump. Help!


#8

just connect to you your regular domain. for putty, just give it example.com to log into. You’ll issue the command from there, not the acual mysql server.

-Matttail


#9

Ok, got it. That clears things up except for one last detail; when logged in with PuTTY, what command should I run? That wiki is assuming a database is already installed and there to be moved; in my case, I’d be uploading my database and dumping it, so how would I run that command to install my file to the new database I’ve created to migrate?


#10

Anyone? I’m assuming this is the last thing to do before it’s migrated completely. If my backup file is now uploaded to the root directory, there is no username/password to it; it’s just sitting there as a file, so I’m guessing that all I gotta do is the the ‘scp’ command, however when I do, PuTTY asks for a password to the new database I’m attempting to install to, and it gives me a ‘Permission denied’ error even though I know the password is correct. Any hints or tips at this point?


#11

You have to creat the database through the panel first. Then fill it with the data from your SQL file.

thats Panel > goodies > Mysql. Create your database - filling in the hostname, database name, user, and pass. Once that’s active that’s the info you’ll use for the command to fill the database with your data - from the sql backup.

-Matttail


#12

Did that already; created the database on the web panel sometime ago (called toezphpbb). Now, the file backup is uploaded via FTP to the root home directory. I ran the follow command in PuTTY “mysql -h host -u username -p password dbname < backupfile.sql” and now replaced all pertinent information to the database and host that I’ve created already (toezphpbb) and pointed the sql in the root directory to it. I then get a bunch of stuff on the screen (i.e. options, descriptions, and variables), but no tables were in fact imported when I checked them on the panel. If I run “scp backupdb.sql username@host:~” and I think it successfully took it as I got this : 100% 6890KB 6.7MB/s 00:00. Following the wiki and this : mysql -h localhost -u ‘‘newusername’’ -p ‘‘newpassword’’ ‘‘newdbname’’ < outfile.sql , nothing happens. What’s next?