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!
|
1 Comments
Mood: dizzy
KeyWords: Mysql, backup, .myd, .myi, files, server, dump, mysqldump, tables, database
|
