Solved

restore mysql database from files in data directory.

Posted on 2004-10-20
4
5,527 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

744 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now