Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a back up-of a MySQL database which is contained in a .tar.gz file - I need to restore it.

I've never had to do this before. The only way I can access the database is through phpmyadmin. There is an 'import' feature in phpmyadmin, but this doesn't accept files in the tar.gz format, only sql.

Is there any way I can convert from .tar.gz to sql? I know I can upload from an sql file without a problem.

I don't have access to any 'back-end' stuff on the web-server, so I'm limited with what I can do locally on my PC.

Any help is much appreciated.

share|improve this question
    
Where is the .tar.gz file located? In the server or your pc? –  ypercube Jun 30 '13 at 15:30
    
Its on my pc, but I could upload it to the server. I can run my own php scripts on the server if that helps. –  user2536626 Jun 30 '13 at 15:57
    
Then upload it to the server, untar/unzip (I guess it's a Linux machine so it would be easy to do that if you have access to shell) and then "import". Or untar/unzip in your pc and then upload/import. There are various programs that can unzip in Windows. See http://www.gzip.org/ –  ypercube Jun 30 '13 at 15:59
    
I've tried extracting it on my PC through two stages: 1) FILENAME.tar.gz > extracts to one file: FILENAME.tar 2) FILENAME.tar > extracts to 3 sub folders: "PaxHeader", "mysql" and "HPM" (the database name) all containing various files. The "HPM" folders contain files named after the DB table names with extensions like ".MYD" and ".FRM" and ".MYI" I was hoping for 1 sql file. –  user2536626 Jun 30 '13 at 16:18
    
It sounds like someone sent you a copy of the MySQL data directory instead of a backup taken with mysqldump. This isn't a valid way to back up MySQL unless it's stopped or flushed and globally read-locked first, so the backup might be corrupt. Depending on the MySQL setup on the source, you might be able to just copy the HPM folder to your MySQL data directory. This could fail for a lot of reasons, though, for example if the backup was taken while MySQL was running, or if the source database has InnoDB tables without innodb_file_per_table enabled. –  James Jun 30 '13 at 18:57
add comment

1 Answer

Actually it seems a backup by copy files (as tables are myisam tables) not a mysqldump. So you just need to untar this compressed file by below command on linux-

$ untar -zxvf your_backup.tar.gz . 

Note: Please run this command from the directory where your backup file exist and it will provide you normal backup also in same directory.

Now you can simply copy these files (*.frm, *.MYI, *.MYD) to your database directory in which Database you want to restore them and it should work.

Note: if there is any permission related issue then use below command to make sure mysql can use these files/tables.

$ chown -R mysql:mysql /path_till_your_db_directory/* 
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.