Topic: backup/restore via phpMyAdmin

[Deprecated]

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.

[Rumbaar]

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.

[Deprecated]

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.

[Rumbaar]

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.

[Deprecated]

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!

[Rumbaar]

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

[Deprecated]

The secret is to use a directory on your server rather than trying to download it to local.

[Rumbaar]

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.

[Deprecated]

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.

[Rumbaar]

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

[SgtMic]

I've just dropped the "new" tables. Then imported the "old" tables and ran repair_settings.php.

[Rumbaar]

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.

[Deprecated]

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.

[Rumbaar]

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.

[Deprecated]

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.

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.