Advertisement:

Author Topic: backup/restore via phpMyAdmin  (Read 13880 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)
                             

Offline Deprecated

  • SMF Hero
  • ******
  • Posts: 3,499
Re: backup/restore via phpMyAdmin
« Reply #20 on: September 13, 2008, 01:59:51 PM »
Actually I prefer my Microsoft Visual Studio which is what I normally edit forum files with. It's too bad it won't open such a large forum's database like it does with small ones.

Offline Rumbaar

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 15,805
  • Gender: Male
  • Inherent Omniscience
    • Rumbaar.net
Re: backup/restore via phpMyAdmin
« Reply #21 on: October 06, 2008, 07:22:59 PM »
Well you export from phpMyAdmin shouldn't put in any db name info, just the hard coded table names if that.

Just make sure you include Add DROP TABLE to the structure and remove Complete inserts & Extended inserts for the data.  Then in BigDump you define the correct db details and access for the insert into the new mySQL server.

It should be pretty straight forward from there.  No need to edit sql dumps.
"An important reward for a job well done is a personal sense of worthwhile achievement."

[ Themes ]

Offline Deprecated

  • SMF Hero
  • ******
  • Posts: 3,499
Re: backup/restore via phpMyAdmin
« Reply #22 on: October 06, 2008, 07:28:54 PM »
Nope, that won't work if you have a new database name, and most of the time from what I've seen hosting services don't give you free reign in deciding what you call your database name. At 1&1 you don't get any choice at all.

What you do in that case is set Options -> Disable foreign key checks. That leaves out the database name for the backup, and then you can upload it to any database name.


Also, you don't need to set Add DROP TABLE or Extended inserts. Database restores work fine without that. Ask me how I know. :)

I suspect there are several of those settings that don't matter for SMF.
« Last Edit: October 06, 2008, 07:30:48 PM by Deprecated »

Offline Rumbaar

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 15,805
  • Gender: Male
  • Inherent Omniscience
    • Rumbaar.net
Re: backup/restore via phpMyAdmin
« Reply #23 on: October 06, 2008, 09:00:40 PM »
I'm not sure how you're doing it but it works that way from phpMyAdmin.

A typical sql dump from phpMyAdmin using my above settings:
Code: [Select]
-- phpMyAdmin SQL Dump
-- version 2.11.9.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Oct 07, 2008 at 10:20 AM
-- Server version: 4.1.22
-- PHP Version: 5.2.6

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `rumbaar_smf3`
--

-- --------------------------------------------------------

--
-- Table structure for table `smf_attachments`
--

DROP TABLE IF EXISTS `smf_attachments`;
CREATE TABLE IF NOT EXISTS `smf_attachments` (
  `ID_ATTACH` int(10) unsigned NOT NULL auto_increment,
  `ID_THUMB` int(10) unsigned NOT NULL default '0',
  `ID_MSG` int(10) unsigned NOT NULL default '0',
  `ID_MEMBER` mediumint(8) unsigned NOT NULL default '0',
  `attachmentType` tinyint(3) unsigned NOT NULL default '0',
  `filename` tinytext NOT NULL,
  `size` int(10) unsigned NOT NULL default '0',
  `downloads` mediumint(8) unsigned NOT NULL default '0',
  `width` mediumint(8) unsigned NOT NULL default '0',
  `height` mediumint(8) unsigned NOT NULL default '0',
  PRIMARY KEY  (`ID_ATTACH`),
  UNIQUE KEY `ID_MEMBER` (`ID_MEMBER`,`ID_ATTACH`),
  KEY `ID_MSG` (`ID_MSG`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

The database name is only in comments and doesn't affect the target db name or importation procedure.  You need drop tables as if you are importing into an already populated db or you'll get duplicate key issues.  You need to turn off extended inserts as it will not work with BigDump.

I've performed dumps and re-importation without issue into differently named databases using this method on a number of occasions.  Are you sure you're defining the correct attributes in the BigDump.php settings?

Also you should be able to call your db anything you want, though it might be prefixed with your account name, etc.
"An important reward for a job well done is a personal sense of worthwhile achievement."

[ Themes ]

Offline Deprecated

  • SMF Hero
  • ******
  • Posts: 3,499
Re: backup/restore via phpMyAdmin
« Reply #24 on: October 06, 2008, 09:58:36 PM »
You don't need the drops if you are importing into a brand new empty database.

My experience is different. The database name does matter. I've done it both ways and it won't import if the database name is wrong.

I've never used BigDump and have no idea about how it works.

And yes I should be able to call it whatever I want, but 1&1 gives no options to name your database. Another site I have on HostDime lets you give a suffix name but your prefixed name is fixed.

It doesn't matter anyway. I know what works for me, you know what works for you. And BTW you've given me some great support help and I thank you for that! :)

Offline Rumbaar

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 15,805
  • Gender: Male
  • Inherent Omniscience
    • Rumbaar.net
Re: backup/restore via phpMyAdmin
« Reply #25 on: October 06, 2008, 10:33:37 PM »
I'm just surprised that your phpMyAdmin was able to take a 460mb dump and import without the use of BigDump.  I know my max is 64mb, but yes what works for you is what is the best.  That is for sure :)
"An important reward for a job well done is a personal sense of worthwhile achievement."

[ Themes ]

Offline Deprecated

  • SMF Hero
  • ******
  • Posts: 3,499
Re: backup/restore via phpMyAdmin
« Reply #26 on: October 06, 2008, 10:37:45 PM »
The secret is to use a directory on your server rather than trying to download it to local.

Offline Rumbaar

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 15,805
  • Gender: Male
  • Inherent Omniscience
    • Rumbaar.net
Re: backup/restore via phpMyAdmin
« Reply #27 on: October 06, 2008, 10:41:19 PM »
I'll have to look into that, interesting, though I always though it was a file size limitation/setting and it didn't really mater where it was getting it from.
"An important reward for a job well done is a personal sense of worthwhile achievement."

[ Themes ]

Offline Deprecated

  • SMF Hero
  • ******
  • Posts: 3,499
Re: backup/restore via phpMyAdmin
« Reply #28 on: October 06, 2008, 10:48:39 PM »
I think it's more of a timeout setting although that is speculation.

I have nothing but the greatest respect for your expertise, and I assume you me too, so I think the real explanation is that none of us really knows for sure what is required and what is optional in regard to the actual PMA settings.

Offline Rumbaar

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 15,805
  • Gender: Male
  • Inherent Omniscience
    • Rumbaar.net
Re: backup/restore via phpMyAdmin
« Reply #29 on: October 07, 2008, 12:50:53 AM »
Yes, it's a very host by host setting.  I know of some that have only 4mb limits to importation.  So it a very varying world of options and availability.

I wont profess to being an expert in anything, but thank you and dido.  That is why I like this quote "Every human mind is a great slumbering power, until awakened by a keen desire and definite resolution to do!"  I think that desire has been awakened in us both :)
"An important reward for a job well done is a personal sense of worthwhile achievement."

[ Themes ]

Offline SgtMic

  • Sophist Member
  • *****
  • Posts: 1,083
  • Gender: Male
    • -{FAST}- Squad
Re: backup/restore via phpMyAdmin
« Reply #30 on: October 07, 2008, 01:44:45 AM »
I've just dropped the "new" tables. Then imported the "old" tables and ran repair_settings.php.

There is nothing more deadly than a US Marine and his rifle.
A close second is a US Marine and his K-BAR.
2/5 Fox Co.  (BlackHearts)
FAST Co. 5th Plt. (FIDO)

Offline Rumbaar

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 15,805
  • Gender: Male
  • Inherent Omniscience
    • Rumbaar.net
Re: backup/restore via phpMyAdmin
« Reply #31 on: October 07, 2008, 02:04:48 AM »
I've just dropped the "new" tables. Then imported the "old" tables and ran repair_settings.php.
Are you sure you've posted in the correct thread, you've not posting in this one before.  You might want to create your own thread, that hasn't been solved.
"An important reward for a job well done is a personal sense of worthwhile achievement."

[ Themes ]

Offline Deprecated

  • SMF Hero
  • ******
  • Posts: 3,499
Re: backup/restore via phpMyAdmin
« Reply #32 on: October 07, 2008, 10:20:06 AM »
Yes, it's a very host by host setting.  I know of some that have only 4mb limits to importation.  So it a very varying world of options and availability.

By the way I would like to point out something. I am NOT using my hosting service phpMyAdmin. I dumped that worthless POS months ago. Instead I went to the phpMyAdmin site and downloaded my own PMA and installed it on my shared server.

When you install your own you can configure it differently than your hosting service thinks it should be configured. In fact I've learned some really spiffy tricks!

For one, add the following to your config.inc.php file:

Code: [Select]
/*
 * Directories for saving/loading files from server
 */
$cfg['UploadDir'] = '/path_to_your_directory/sql_transfer/';
$cfg['SaveDir'] = '/path_to_your_directory/sql_transfer';

That adds the ability to backup and restore your database dumps to your server rather than having to download them to your local HDD. Not only does the backup go more quickly, not only can you then download the file to local anyway, but when you restore a backup the process goes more quickly. (That's the reason to make them the same directory, although they could be different directories if you wanted.)

People should understand that phpMyAdmin is open source just like SMF and you can install it yourself if you like, or at least I've installed it on two different hosting services with no problem. All you do is unzip the files into a folder, then you have to hand edit your config.inc.php file and upload that too (passwords, database names, and a few other settings like UploadDir and SaveDir. It's easy to install your own PMA.

Offline Rumbaar

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 15,805
  • Gender: Male
  • Inherent Omniscience
    • Rumbaar.net
Re: backup/restore via phpMyAdmin
« Reply #33 on: October 07, 2008, 05:35:08 PM »
Nice tip Deprecated, though I'm sure a few people might have issues if their mySQL server isn't at localhost.  But I'm not 100% sure of this.
"An important reward for a job well done is a personal sense of worthwhile achievement."

[ Themes ]

Offline Deprecated

  • SMF Hero
  • ******
  • Posts: 3,499
Re: backup/restore via phpMyAdmin
« Reply #34 on: October 07, 2008, 05:45:36 PM »
Mine isn't at localhost. :)

If I grok my hosting service right, the database server is nearby but not running on the same server. It has a different host name so that might be a clue. :P

If your database isn't on local host all you need is a valid domain name for the database server. For example I'm at 1&1 hosting and the database server names are like dbNNNN.perfora.net where NNNN is a 4 digit number.

I think it would work fine localhost or not.


Yes indeed that idea to use a transfer directory IS a good idea, and it has worked very well for me. One thing though, anybody doing that should download the database dumps at least from time to time, because if your database crashes that will be no problem, but if your server HDD crashes AND your database crashes, your backups will be lost too.

Actually I'm pretty sure my database and database backups are on different servers so I think I'm in good shape even if I don't download my backups.