By beauregard on

Hello

I have a small site and do a backup regurarly. I updated from 4.7 to 5.x and had a problem which made it necessary to restore my DB again. And I found myself dealing with technologies I can only wonder about - or, did I something not understand? My problems I encountered:

1. I made with phpMyAdmin a backup in zip format. When I want to restore it, it does not recognize it. Hello?
2. So, I unzip the file on my PC (the txt file is ca. 4 MB) and split it up in smaller ones. This time I want to restore only with these smaller ones. I get an error, that the format is not correct. Hello again? I was saving and restoring with UTF8.
3. So I am trying to do it piece by piece with copy & paste. But with some bigger tables (for example locales-source) the phpMyAdmin hangs. Hello?

A few years ago I was working with SQL Server. There backup was simple with creating a file, restoring this file. Point.

So, did I something not understand? How you guys are doing backup/restore with sites which are for sure much larger than mine?

Thanks for any help to solve my frustration

André

⋅ Categories: Drupal 5.x

Comments

kpander’s picture

kpander commented

Hi Andre,

The way I do it (may not be the best but it seems to work for me) is to use the command-line program mysqldump. It can dump an entire database to a single sql file. You can then import that file later using mysql (command-line) or phpmyadmin. Mysqldump is part of the standard mysql install.

I believe older versions of phpmyadmin had file size limits and problems with timeouts -- I think that's been addressed in newer versions but can't really say (as I use the command line version).

e.g., To dump the contents of a database named 'mydatabase' to the file 'file.sql', using the user 'myuser' with the password 'mypassword':

mysqldump -umyuser -p mydatabase > file.sql

When you type this and hit 'enter', it wil ask you for your password.

To re-import this file into a database (and replace the contents of the database with the contents of this file!), use this command:

mysql -umyuser -p mydatabase < file.sql

This assumes the mysql server is found at 'localhost', otherwise you may need to specify it's location (e.g., if you're using Dreamhost you will). This would look like this:

mysqldump -umyuser -p -h your.mysqldomain.com mydatabase > file.sql

Anyways, hope that helps a bit. I'm no expert at this stuff by any means but this process works fairly well for me. Very painless!

Kendall
Abandoned Industry! :: http://invisiblethreads.com/galleries ::

beauregard’s picture

beauregard commented

Hi Kendall

Thanks for your information. I could not import my files with mysqldump, but will test mysqldump now. In the meantime I could import my files with splitting the backup file up into many small files.
There is a tool called BigDump, which solves the limitations of phpMyAdmin, but:It has problems with extended INSERTS.
So, I am now evaluating what is the best variant, still quite suprised that there is from MySQL no easy backup&restore tools.

Kind regards
André

beauregard’s picture

beauregard commented

I found an overview of sql tools, but it is in german:
http://www.phpbb.de/doku/kb/mysqlbackup

joachim’s picture

joachim commented

There's a backup drupal module, but it doesn't do restores.