Solved

restore mysql database from files in data directory.

Posted on 2004-10-20
4
5,541 Views
Last Modified: 2010-06-02
Hi all experts.
I have operated mysql database on linux server for a while and the server seems damaged from power supply problem.(not sure exactly)
Nothing is quite working well. I am not able to start any major damons like apache, mysql, proftpd.... So I am going to setup a new machine.
The problem is that I can't run mysql damon any more which means I can't get the most recent backup files, but I can access the mysql directory. I do have some backup files, however I would like to restore the data as much as possible. So I am wondering if I can restore the database by creating a database on a new server and put all the data files(*.frm, *.MYD, *.MYI) from the old server to the new server's mysql data directory.
Please give me some advise on it.
0
Comment
Question by:soweyoung
  • 2
4 Comments
 
LVL 26

Accepted Solution

by:
ushastry earned 250 total points
ID: 12366494
Hi,

[Taken from NET]

When it comes to backing up MySQL databases, don't ignore the most obvious solution: making copies of the database files themselves.

Because MySQL uses the same table format on different platforms, it's actually possible to copy MySQL table and index files from one platform and use them on another without any difficulties (assuming, of course, that you're using the same version of MySQL on both platforms).

So which files should you copy? MySQL stores all its databases in a special data/ directory, which is further divided into subdirectories, one for each database. Tables and table indexes are represented as files, with the file name equal to the table name plus an extension.

The easiest approach is to just copy the entire data/ directory to backup media, and archive it so that you can get it back at any time. You might want to write a script to automate the copy. You can then pop the script into your crontab so it can run on a daily or weekly basis, depending on how frequently your databases get updated.

If you're moving files between Windows and UNIX platforms, there is one thing that can trip you up. UNIX file names are case-sensitive, while Windows names are not. Therefore, mixed-case MySQL table names are likely to get corrupted when moved between Windows and UNIX (though the data in the tables will be fine). If your SQL code uses these mixed-case table names, it might not work correctly until you check and fix the names and/or the code. For best results, always use lowercase table names, so that you don't encounter this problem.



Take a look at here..

http://builder.com.com/5100-6388_14-5259660.html



0
 

Author Comment

by:soweyoung
ID: 12381130
Everything worked good.
I just copied all database files to the new server and worked.
Just one thing I want to add for other user, when I first run the database query from the new server, I only had read-only access to all tables even though I connected to it as root. So I simple dump all tables in the database to a sql file and dropped the database, recreated it and reloaded all data from the file.
After that, everything went back to normal and worked like charming.
I want to ask you just one more thing.
Is there a way to run a script that dump all database tables in a file and email it to me everyday?
I don't have no mail server program installed on my server.
0
 
LVL 26

Expert Comment

by:ushastry
ID: 12392096
Hi ,

>>I want to ask you just one more thing.
>>Is there a way to run a script that dump all database tables in a file and email it to me everyday?
>>I don't have no mail server program installed on my server.

Yes you can dump all database tables in a single file,

This is for single database dump..
mysqldump -uusername -p --add-drop-table databasename > mydump.sql

And to dump all the databases on the system, use the --all-databases

mysqldump -uusername -p --all-databases databasename > mydump.sql


And you can send email but for this you need a mail/sendmail installed on your box and also that supports huge file attachment..
better you can get a reminder after the backup which should be enough for you..

0
 

Expert Comment

by:stephaneeybert
ID: 23274599
Hello,

I also have a similar problem.

I had to copy the data files back to a mysql/data/mydb directory.

I can see all the list of table names with a command:
show tables;

But I can't access any table.

A command:
desc mytable;

give the message:
ERROR 1146 (42S02): Table 'mydb.mytable' doesn't exist

I checked the rights on the files and they are all to 777.
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How do I call MySQL Stored Procedure from oracle using HS link ? 5 58
MySQL Memory Keeps Increasing 4 47
mysql db 3 69
check mysql insert 12 26
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question