Re: Issue importing a large MYSQL database.
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
> 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?
> 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
--
Karl E. Jorgensen
Reply to: