MySQL Database Backup .MYI and .MYD

KeyWords: Mysql, backup, .myd, .myi, files, server, dump, mysqldump, tables, database

MySQL Database Backup .MYI and .MYD

Introduction
Hello everyone!

Long time no post, I know. Well really I have been busy with work, a wife and a new baby being on the way. Well anyways, this article is about how to restore a backup to MySQL from a .MYI and .MYD file that you may have. Our dedicated host backs up only these files and does not use the mysqldump utility to create a full SQL backup of our database. This is fine, but makes a bit extra work. So let us get started.

Determine Your Version
First we need to determine the version of your server's MySQL database. Mine was 3.23, old I know. I do not know if you can mix and match .MYD files so I just downloaded that and installed it on my local computer. I would suggest doing the same, this way you are messing with copies of the actual database instead of the real thing.

Creating a Database
Now we want to go into the MySQL console on your computer and create a new database, this database name can be anything. For this tutorial sake I will use "backup". Now that you have created a database "backup" it is time to get the table structure from your current database. The table structure is necessary for this process, if you do not know or do not have it, I am not sure if this will work for you. Try anyway you can.

Lucky for me, I was just trying to get a user's data back whose entries were accidentally deleted. So I had my full table structure online and just did a phpMyAdmin Export of the table structure and ran that through my personal computers MySQL command line tool. This creates 3 files inside of c:\mysql\data\backup a .frm, .myi and .myd. This is exactly what we want!

Restoring the Actual Files
Now what we are able to do is copy only the .MYI and .MYD files from your backup and paste them into the c:\mysql\data\backup directory overwriting any files that are in there. This will basically put our data into those tables.

To test that the data was input successfully, open the MySQL command line tool and use the database "backup" and do a "select * from `yourtablenamehere` LIMIT 1" If no results are returned then it did not work, try restarting your MySQL server and then try again, this generally works. If not, then post a comment and I will try and help you out.

If that worked, you now have a working backup of your database! Now to export this data.

Export the Backup Data
Exporting the data is rather easy. Just use the mysqldump tool and run a command like this to get all that data into a SQL statement you can import into your current DB via phpMyAdmin:

mysqldump -uroot desk yourtablenamehere > yourtablename.sql

This is assuming the user is root with no password (the reason I do not set a password was this is strictly on my localhost and I have a firewall setup to block all external connections to MySQL). You can replace any of that with the user information you use. That should now create a file yourtablename.sql with all of the SQL. This may have a create table at the top of the file, so manually edit the file and remove that portion to avoid any errors when trying to import via phpMyAdmin.

Now just copy that text file or upload it to your phpMyAdmin and that data should be re-inserted and you are up and running again!

Ending Notes
This tutorial was made to backup minimal data for a user, what I really did was ran a sql command to only pull at that user data and create a new able with it. To do that also just do this:

create table yourtablenamebup SELECT * FROM yourtablename WHERE conidtion=something

Then you can just do a dump of that table and have just the information you need and not every information!

This tutorial is meant as a reference, be sure to backup all data before attempting it as I am not responsible for any lost/corrupted data for mis-use or use of this tutorial. This tutorial as worked for me many times in the past following those instructions.

Please use at your own risk!

Posted by frost on Feb 1st, 2009 14:40 - Subscribe Bookmark and Share


Post a comment:


ReCaptcha:

Posting as anonymous Anonymous guest, why not register, or login now.



Posted by anonymous on April 15th, 2009

This is a great bit of information.



Posted by anonymous on April 14th, 2010

i am working on wampserver 2.0. And i am not able to access the database in phpmyadmin.i am not getting the path as c:\mysql\data\ there is a path as C:\wamp\bin\mysql\mysql5.1.36\data is there something wrong with that??



Posted by anonymous on April 12th, 2011

I have already done what you suggest, but it doesn't work. It does show my databases, but doesn't show the tables in them. What could it be?



Posted by frost on April 12th, 2011

What part are you stuck on?



Posted by anonymous on October 20th, 2011

This method works only when you have access and privileges on the server. In a shared enviroment you may not have these kind of permissions.
I had this problem before but I found this tool MySQLBackupFTP. The free version connects to MySQL through phpMyAdmin so you don't need privileges on the server to backup your database.



Posted by frost on October 20th, 2011

That tool sounds nice, if you only need to backup two databases (or want to pay). There might be other solutions as well, but yea. I use VPS's with root access, so this really does not pertain to me. I do not know that I would ever use shared hosting.



Posted by anonymous on November 25th, 2013

hey i didn't work at all



 

Widget

Hypersmash.com

Extras

  • Not Implemented