Mysql – Extract MySQL information from a compressed tar.gz file

MySQL

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.

Best 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/*

Related Solutions

Mysql – How and where do I upload a thesql database

If you have downloaded the database as an SQL file then you can just import it using the command prompt and the mysql command.

Do it in these simple steps:

Make sure the SQL file is unpacked as a text file with SQL commands inside
Verify you're able to login to MySQL using the command prompt. You can simply type # mysql -p -u username database_name. You'll be asked for password and, if correct, you'll be accepted to your database.
If you haven't created any new database to unpack your backup file in you can do it now (inside the MySQL command prompt) by typing create database MyNewDatabase;
Now that you know you can login to the database you can "feed" the downloaded SQL file by changing the command in this way: # mysql -p -u username database_name < /tmp/download/db_backup.sql. Make sure you're not inside the MySQL command prompt but out in the normal command prompt when typing this command. You can simply type exit or quit to leave the MySQL command prompt.
The database will then be imported into your local database. If it's a big database running this last command might take quite some time.

Some things to think of:

If you have a new installation and haven't added any users to MySQL then root is the username to use. But it's always recommended to add a separate user account in MySQL that you can use from scripts ect.
In my examples above I specified a database name. But if your backup script starts with use someotherdb; then someotherdb will be used instead of the database name you specify in the mysql command. To get around this, either open up the SQL backup file and remove the first line with use, or, when you create your backup, untick the box saying Include using (or something similar).
This is just how to install the database. You talked about "web site" also and that's a different thing. However, you need to update your website scripts to point to the new local database that you've created now.

MySQL Import – Accessing Data After Importing from File

Connect using mysql cli:

mysql -u <username> -p<pass> db_name

The following command will show you the imported tables:

show tables;

Then, access the data:

select * from <table_name>;

where *table_name* is one of the tables in the show tables' output.

Best Answer

Related Solutions

Mysql – How and where do I upload a thesql database

MySQL Import – Accessing Data After Importing from File

Related Question