?
Solved

restore mysql database from files in data directory.

Posted on 2004-10-20
4
Medium Priority
?
5,545 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 26

Accepted Solution

by:
Umesh earned 1000 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:Umesh
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

800 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