Advertisement:

Author Topic: backup/restore via phpMyAdmin  (Read 13909 times)

Offline Deprecated

  • SMF Hero
  • ******
  • Posts: 3,499
backup/restore via phpMyAdmin
« on: September 08, 2008, 07:07:17 PM »
I want to download my database and then upload it to a different database, then switch my SMF over to the new database.

There is one problem. The name of the database is encoded into the backup. When you try to import it into the new database the name is wrong and pMA squawks, won't upload it.

I've handled that before by just manually editing the name inside the SQL file. But this file is way too big to be edited by any programs I have: 460 MB.

How do I get around this?
« Last Edit: October 06, 2008, 07:26:14 PM by Deprecated »

Offline ccbtimewiz

  • SMF Hero
  • ******
  • Posts: 5,300
  • Gender: Male
  • OXEN
Re: backup/restore via phpMyAdmin
« Reply #1 on: September 08, 2008, 07:15:24 PM »
With a database that large, I'd use BigDump.

Offline Deprecated

  • SMF Hero
  • ******
  • Posts: 3,499
Re: backup/restore via phpMyAdmin
« Reply #2 on: September 08, 2008, 08:28:55 PM »
But how do you change the database name? The download has the database name encoded inside. If you try to upload it to a different database server it barfs.

riker

  • Guest
Re: backup/restore via phpMyAdmin
« Reply #3 on: September 09, 2008, 10:28:14 AM »
But how do you change the database name? The download has the database name encoded inside. If you try to upload it to a different database server it barfs.

I had this problem once.  I got around it by creating the name of the DB in a newly created DB and it then imported

Offline Deprecated

  • SMF Hero
  • ******
  • Posts: 3,499
Re: backup/restore via phpMyAdmin
« Reply #4 on: September 09, 2008, 10:30:31 AM »
My cPanel won't let me do that. All I pick is 4.0 or 5.0 and it generates the database, user name and password. I have no idea why they won't let me pick my own. I've seen other hosting services where you can.

babjusi

  • Guest
Re: backup/restore via phpMyAdmin
« Reply #5 on: September 09, 2008, 10:34:05 AM »
I want to download my database and then upload it to a different database, then switch my SMF over to the new database.

There is one problem. The name of the database is encoded into the backup. When you try to import it into the new database the name is wrong and pMA squawks, won't upload it.

I've handled that before by just manually editing the name inside the SQL file. But this file is way too big to be edited by any programs I have: 460 MB.

How do I get around this?

Would the databases be at the same host or the new database would be in a new host?


Offline Deprecated

  • SMF Hero
  • ******
  • Posts: 3,499
Re: backup/restore via phpMyAdmin
« Reply #6 on: September 09, 2008, 12:25:59 PM »
Different database name, different user name, different password, and let's just assume the host is "localhost."

But don't assume "localhost" means the same server.

riker

  • Guest
Re: backup/restore via phpMyAdmin
« Reply #7 on: September 09, 2008, 12:45:22 PM »
Different database name, different user name, different password, and let's just assume the host is "localhost."

But don't assume "localhost" means the same server.

Maybe a silly question but have you tried downloading it from your forum at your existing host ?

Offline Deprecated

  • SMF Hero
  • ******
  • Posts: 3,499
Re: backup/restore via phpMyAdmin
« Reply #8 on: September 09, 2008, 01:25:01 PM »
I've got it downloaded. Downloading is not the problem. Uploading is the problem because the database has a different name and it refuses to upload.

babjusi

  • Guest
Re: backup/restore via phpMyAdmin
« Reply #9 on: September 09, 2008, 01:30:05 PM »
I've got it downloaded. Downloading is not the problem. Uploading is the problem because the database has a different name and it refuses to upload.

What error message do you get. Can you post here the exact error?

It can be restored in a another database with a different name as well. I have done it like 100''s of times. Matter of fact yesterday I duplicated someone''s site here. I created a new database at the cp of her host and transferred there the database of her live forum without a problem what so ever.

Offline H

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 21,662
  • Gender: Male
Re: backup/restore via phpMyAdmin
« Reply #10 on: September 09, 2008, 02:03:33 PM »
Most likely there are a few lines at the start of the backup which are trying to force the restore to go into a DB which doesn't exist.

You might want to have a poke around for CREATE DATABASE or similar commands, alternatively I can take a look if you want to send me a copy (e-mail or direct download) :)
-H
Former Support Team Lead
                              I recommend:
Namecheap (domains)
Fastmail (e-mail)
Linode (VPS)
                             

Offline Deprecated

  • SMF Hero
  • ******
  • Posts: 3,499
Re: backup/restore via phpMyAdmin
« Reply #11 on: September 09, 2008, 02:28:20 PM »
That's exactly what it is. I've done that before just manually editing the file in MSFT Visual Studio, but this file is a whopper, over 450 MB, and VS won't open files that big. Even worse, it overtemps my CPU and before I can get Task Manager to respond my CPU goes into thermal shut down! Wow, there's a real crash let me tell you! ;)

If worse comes to worse I think I'll write a PHP script to strip out the database name. IIRC SMF doesn't even bother putting the name in, a good choice. Unfortunately phpMyAdmin is more fastidious, too much so for me.

Well I got a better chance than many others to resolve this. I can just throw code at it until I get it working.

This is for my 950 member forum with only about 450,000 posts. I think those guys post too much, don't you? I should probably just comment out all of Post.php to solve that one. <snicker>

Offline H

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 21,662
  • Gender: Male
Re: backup/restore via phpMyAdmin
« Reply #12 on: September 09, 2008, 03:09:27 PM »
Try opening it in Notepad++ or something as this is more lightweight than VS and should work on large files.

You might want to get that CPU looked at too :P
-H
Former Support Team Lead
                              I recommend:
Namecheap (domains)
Fastmail (e-mail)
Linode (VPS)
                             

Offline Deprecated

  • SMF Hero
  • ******
  • Posts: 3,499
Re: backup/restore via phpMyAdmin
« Reply #13 on: September 09, 2008, 03:22:15 PM »
Oh my CPU is teh sux!!! ;) I've been to busy to drive over to Fry's. :P

I didn't know that Notepad++ worked on big files. I use VC++ (Visual Studio) because it's able to handle Unix style newlines, and because of context highlighting and line numbering.

Well I'll have a luck at N++. Does it come with an operating manual? ;) ;) ;) :P :P :P

Offline Deprecated

  • SMF Hero
  • ******
  • Posts: 3,499
Re: backup/restore via phpMyAdmin
« Reply #14 on: September 10, 2008, 01:40:38 PM »
Try opening it in Notepad++ or something as this is more lightweight than VS and should work on large files.

You might want to get that CPU looked at too :P

Notepad++ barfed when I tried to open the 460 MB file, said it couldn't open files that large.


Edit: corrected left out ++
« Last Edit: September 11, 2008, 04:26:27 PM by Deprecated »

Offline H

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 21,662
  • Gender: Male
Re: backup/restore via phpMyAdmin
« Reply #15 on: September 11, 2008, 04:19:37 PM »
Notepad++: http://notepad-plus.sourceforge.net/uk/site.htm, not ordinary notepad :)
-H
Former Support Team Lead
                              I recommend:
Namecheap (domains)
Fastmail (e-mail)
Linode (VPS)
                             

Offline Deprecated

  • SMF Hero
  • ******
  • Posts: 3,499
Re: backup/restore via phpMyAdmin
« Reply #16 on: September 11, 2008, 04:26:03 PM »
Come on H. I'm a professional developer. It wouldn't even occur to me to try to open a half-GB file using MSFT Notepad. Please give me a bit more credit than that. :)

Offline Popsikle

  • Semi-Newbie
  • *
  • Posts: 34
Re: backup/restore via phpMyAdmin
« Reply #17 on: September 11, 2008, 10:59:42 PM »
Textpad works for this.  You might have to let it sit for a while to load the file but I have used textpad with files that are just under 2GB before.

http://www.textpad.com/

Offline Deprecated

  • SMF Hero
  • ******
  • Posts: 3,499
Re: backup/restore via phpMyAdmin
« Reply #18 on: September 12, 2008, 06:11:46 AM »
I'll keep Textpad in mind next time I need to open a large file.

Actually the problem is much easier to solve. I just didn't realize it until the last day or two. In phpMyAdmin you check "Disable foreign key checks" and it just leaves out that stupid line that makes it impossible to import to a different database name. It's that simple! :)

Offline H

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 21,662
  • Gender: Male
Re: backup/restore via phpMyAdmin
« Reply #19 on: September 12, 2008, 03:22:43 PM »
Come on H. I'm a professional developer. It wouldn't even occur to me to try to open a half-GB file using MSFT Notepad. Please give me a bit more credit than that. :)

Sorry. There are quite a few notepad spinoffs (notepad+, notepad++, notepad plusTM) so just wanted to ensure we were thinking about the same one rather than the ordinary or a similar notepad plus.
-H
Former Support Team Lead
                              I recommend:
Namecheap (domains)
Fastmail (e-mail)
Linode (VPS)