SOLVED: Re: Issue importing a large MYSQL database.
On Sat, 2014-03-01 at 23:41 +0000, Karl E. Jorgensen wrote:
> Hi
>
> On Sat, Mar 01, 2014 at 01:33:44PM -0600, John W. Foster wrote:
> > Yep this is Debian specific. I have a system running Debian Wheezy; up
> > to date; AMD 6 core processor; 4GB ram. Terabytes of disk space. This
> > computer is in my office, not a remote. I am trying to use command line
> > instructions to restore/import a database that I exported a few weeks
> > back from a remote server. I was able to do this on the remote server
> > using command lines, but after finishing the import the server admins
> > decided I was using too much of the VPS servers resources and shut it
> > down with no notice.I decided to bring it in house again.The Database is
> > 2.28 Gb & is from a Mediawiki site. I'm not using any GUI such as
> > PhpMyAdmin, though I have those available. The php limitations will not
> > allow it to finish using PhpMyAdmin or Mediawiki's import structures.
> > Since I did this on a Debian remote host I figured no prob here. Not so.
> > I logged in as root, not connected to the internet, to do the import.
> >
> > issued this command;
> > mysql -v -u root -p MyDBName < MyDump.sql
>
> Looks pretty vanilla. No compression? Usually worth the trouble, but
> this is obviously not causing the problem you're seeing.
>
> > put in root password, & watched some screen output that resembled the
> > Matrix for 2 days. System was accidentally interrupted & quit. I
> > restarted it and waited 2 more days, server just quit.
>
> If the server "just quit" (I assume without any error messages as you
> do not list any) then there should be clues in mysql's log (usually
> routed via syslog to /var/log/daemon.log).
>
> I have been batting something similar with the mysql client - also
> suffering under long-running import jobs: Re-sizing the terminal
> window seems to cause the mysql client to die. Just like that. REALLY
> annoying. I suspect that is http://bugs.mysql.com/bug.php?id=62578
I suspect that was part of the issue
> > Now for the differences;
> > On my remote server I created a completely empty database then did the
> > import. Then reinstalled the upgraded Mediawiki and managed it as a
> > mediawiki upgrade. Here I tried to use the existing installation which
> > was new and do the restore as an upgrade. Now this is my question; Any
> > one know why this doesn't work.
>
> Which errors/symptoms do you see?
well there were no error logs at all. I now have that fixed by editing
mysql config files. seems that was another thing I neglected & the
output helped me isolate the issue. I was trying to "import" a "dump"
file. Basically using the wrong command syntax.
http://www.lullabot.com/blog/news/importexport-large-mysql-databases
this gave me the correct tools and a working solution: Thanks to the
authors for posting this!!
>
> > I'm now trying the new empty db here to
> > see if I can get it to work. However even if I do, i want tosolve this
> > issue so I don't repeat it. There are some serious benefits to restoring
> > to a populated database that I want to preserve. Also I have
> > successfully used PhpMyAdmin to do that with much smaller databases.
>
> There are faster ways of copying MySQL databases about - if
> /var/lib/mysql is on a LVM logical volume, you can create a snapshot
> and copy *that* across (the resulting instance will do crash recovery
> upon startup). Similiar things can probably be achieved with btrfs
> subvolume snapshots or zfs - although those file systems would
> probably not be your first choice to store a database on.
>
> Hope this helps
Reply to: