If this is your first visit, check our documentation. To post, register or login.
Forums / Installing on a Linux/Unix Server / Moving Servers - Database Export Issue

Moving Servers - Database Export Issue

Started by patf on 22 Mar 2011, 12:13 PM · Views: 2,227

Results 1 to 13 of 13
22 Mar 2011, 12:13 PM
#1
patf avatar

patf

Zen Follower

Join Date:
May 2008
Posts:
200
Plugin Contributions:
0

Moving Servers - Database Export Issue

Hi guys,

We're in the process of moving servers from a Windows VPS server to a Linux VPS.

I'm having trouble though with one of our sites which has quite a large database. PhpMyAdmin seems unable to handle this size of database backup / load.

The windows servers control panel (plesk) has its own database backup utility but this only fuctions for backing up and restoring on a windows server only - there is no such function on the linux one. So I can pull a backup down from the windows server through plesk but this can't be used / restored on the new linux server.

Is there any other utility which can handle large database backups / restoring ??

Thanks in advance for any help.

22 Mar 2011, 12:17 PM
#2
kuroi avatar

kuroi

Totally Zenned

Join Date:
Apr 2006
Location:
London, UK
Posts:
10,475
Plugin Contributions:
11

Re: Moving Servers - Database Export Issue

You could try the unfortunately named "bigdump" (Google it). This is a utility that breaks up the transfer into chunks to work around size limitation.

ozerov.de/bigdump.php

Kuroi Web Design and Development | Twitter

(Questions answered in the forum only - so that any forum member can benefit - not by personal message)

22 Mar 2011, 12:27 PM
#3
patf avatar

patf

Zen Follower

Join Date:
May 2008
Posts:
200
Plugin Contributions:
0

Re: Moving Servers - Database Export Issue

Thanks will do now :)

edit FFS - do not go into bigdump.com ;)

22 Mar 2011, 1:24 PM
#4
dgent avatar

dgent

Totally Zenned

Join Date:
Nov 2009
Location:
UK
Posts:
1,117
Plugin Contributions:
0

Re: Moving Servers - Database Export Issue

There is a mod on here that you can add to Zencart, that can back up all the files and the database SQL file if needed into a zip folder on your server. Simply extract it and upload it to the new server.

http://www.zen-cart.com/index.php?main_page=product_contrib_info&cPath=40_41&products_id=1646

25 Mar 2011, 8:21 AM
#5
misty avatar

misty

Inactive

Join Date:
Apr 2004
Location:
UK
Posts:
5,752
Plugin Contributions:
1

Re: Moving Servers - Database Export Issue

Official site for Big Dump is currently
http://www.ozerov.de/bigdump.php

Dezina ZenCart Templates
Please do not PM for support queries..Post in forums..help others

26 Mar 2011, 12:45 PM
#6
cybagirl avatar

cybagirl

Zen Follower

Join Date:
Jul 2009
Posts:
119
Plugin Contributions:
0

Re: Moving Servers - Database Export Issue

MySQLDumper is your best friend and also the best and most easiest program to use when it comes to backing up a database.

Once you have used it you will wonder how you ever lived without it!

It's feature packed and simply the best! I cannot recommend it enough. I have it installed on all the forums and websites that use a database that I manage. Recently I moved a forum with a 1.4GB database to another server and MySQLDumper just made the whole process so simple and easy! As it allowed me to backup the database in multipart backups which made the restoration so much faster and quicker than any other method.

Plus it's completely FREE and the support from the people who created it is outstanding.

mysqldumper.net/

:clap:

26 Mar 2011, 2:51 PM
#7
website_rob avatar

website_rob

Inactive

Join Date:
Oct 2006
Location:
Alberta, Canada
Posts:
4,572
Plugin Contributions:
0

Re: Moving Servers - Database Export Issue

If you don't have Shell access then contact your Hoster. They can Import it for you without any problems.

The learning is in the doing.

Potent Products

26 Mar 2011, 4:09 PM
#8
patf avatar

patf

Zen Follower

Join Date:
May 2008
Posts:
200
Plugin Contributions:
0

Re: Moving Servers - Database Export Issue

Hi Rob,

We do have shell access - but am I right in saying that the windows server created backup file can't be restored onto a linux server ?

26 Mar 2011, 5:08 PM
#9
website_rob avatar

website_rob

Inactive

Join Date:
Oct 2006
Location:
Alberta, Canada
Posts:
4,572
Plugin Contributions:
0

Re: Moving Servers - Database Export Issue

I had presumed the database was already put into mysql format as that will have to be done no matter what Import method is used.

The learning is in the doing.

Potent Products

29 Mar 2011, 3:59 PM
#10
patf avatar

patf

Zen Follower

Join Date:
May 2008
Posts:
200
Plugin Contributions:
0

Re: Moving Servers - Database Export Issue

Hi Rob,

Forgive me - I am not experienced at all with this.

The plesk backup utility on the windows server saves the file as an

application/octet-stream

which I didn't realise could still be opened with a text editor and was in sql format. :oops:

That's half the problem sorted :smile:

29 Mar 2011, 4:07 PM
#11
website_rob avatar

website_rob

Inactive

Join Date:
Oct 2006
Location:
Alberta, Canada
Posts:
4,572
Plugin Contributions:
0

Re: Moving Servers - Database Export Issue

Please note: sql format is "not" the same as mysql format. I guess the easiest way to find out is to try and Import the database onto your Linux hosting account.

The learning is in the doing.

Potent Products

29 Mar 2011, 4:46 PM
#12
patf avatar

patf

Zen Follower

Join Date:
May 2008
Posts:
200
Plugin Contributions:
0

Re: Moving Servers - Database Export Issue

Website Rob:

Please note: sql format is "not" the same as mysql format. I guess the easiest way to find out is to try and Import the database onto your Linux hosting account.

Sorry again Rob - that was a typo on my pary leaving out the "my"

This is a sample of what is being generated ....

-- MySQL dump 10.9

-- Host: localhost Database: gnlive


-- Server version 5.0.45-community-nt

/*!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 /;
/
!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 /;
/
!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 /;
/
!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' /;
/
!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table address_book

DROP TABLE IF EXISTS address_book;
CREATE TABLE address_book (
address_book_id int(11) NOT NULL auto_increment,
customers_id int(11) NOT NULL default '0',
entry_gender char(1) NOT NULL default '',
entry_company varchar(64) default NULL,
entry_firstname varchar(32) NOT NULL default '',
entry_lastname varchar(32) NOT NULL default '',
entry_street_address varchar(64) NOT NULL default '',
entry_suburb varchar(32) default NULL,
entry_postcode varchar(10) NOT NULL default '',
entry_city varchar(32) NOT NULL default '',
entry_state varchar(32) default NULL,
entry_country_id int(11) NOT NULL default '0',
entry_zone_id int(11) NOT NULL default '0',
PRIMARY KEY (address_book_id),
KEY idx_address_book_customers_id_zen (customers_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table address_book

LOCK TABLES address_book WRITE;
/*!40000 ALTER TABLE address_book DISABLE KEYS /;
/
!40000 ALTER TABLE address_book ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table address_format

The 401** messages aren't there in a phpmyadmin mysql backup. Do you know off hand if these would cause issues ?

29 Mar 2011, 5:56 PM
#13
website_rob avatar

website_rob

Inactive

Join Date:
Oct 2006
Location:
Alberta, Canada
Posts:
4,572
Plugin Contributions:
0

Re: Moving Servers - Database Export Issue

Have you discussed this issue yet with your Hoster? They can provide the best help in this type situation.

The learning is in the doing.

Potent Products