• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5557
  • Last Modified:

restore mysql database from files in data directory.

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
soweyoung
Asked:
soweyoung
  • 2
1 Solution
 
UmeshCommented:
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
 
soweyoungAuthor Commented:
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
 
UmeshCommented:
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
 
stephaneeybertCommented:
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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now