LinuxQuestions.org

LinuxQuestions.org (/questions/)
-   Slackware (http://www.linuxquestions.org/questions/slackware-14/)
-   -   Where can i find the MySQL database file in Linux Filesystem (http://www.linuxquestions.org/questions/slackware-14/where-can-i-find-the-mysql-database-file-in-linux-filesystem-551858/)

dc_eros 05-07-2007 01:15 AM

Where can i find the MySQL database file in Linux Filesystem
 
I'm an MS Access user for several years, then I switch to MySQL. I know how to backup and restore the database in MySQL in a Linux environment, but it only works when the MySQL daemon is running. One time, our server (SuSE Linux 9.3 Prof) crashes, and I can't run all the services, and one of them is MySQL, so I have trouble making backup of our School's Forum.

I tried to search the entire filesystem to find the exact file where my tables are located but I'm not sure what i'm doing. I noticed, in Windows, MySQL tables are saved as .frm files, ex. msgboard.frm.

So here are my questions:

1. What directory the MySQL database is stored (in Linux of course)?
2. If ever i find those files, is there any commands to restore those database file to another system? In windows, I copied and paste it, but it doesn't work of course. is there a way on restoring those files? (not mysqldump files)

Another: I have some trouble in restoring my backups. When I make backup, i used this command.

mysqldump db_name -u root -p > our_db_backup.sql

And when i need to restore the database, i need to append the name of the database to all tables of the entire SQL script like this:

create table 'table_name' ...

to

create table 'db_name'.'table_name' ....

so that this restoration works:

mysql -u root -p < our_db_backup.sql


Is there any way so that I don't have to edit the SQL script evertime i need to restore the database? I think there's a switch lacking.

I really appreciate your help. Thank you.

j-ray 05-07-2007 03:16 AM

to restore the database, create it first then
mysql yourDATABASENAME < yourSQL.sql -u root -p

Your data reside in a directory that you may find reading
/etc/my.cnf
look for data directory entry. Could be somewhere in /var/lib/mysql but depending on how you installed mysql it can be anywhere.
Why dont you start the server and make a dump?

dc_eros 05-09-2007 05:27 AM

Making automatic backup
 
Ok, i have now learned how to restore from my dump file. thank you very much dude.

I want to make an automatic backup of my database, that is i want to make backup weekly.

I know how to make script, but i want to know what is the name of the program that run applications scheduled, even if the user is not logged in. is it "cron" ???

tnx in advance.

j-ray 05-09-2007 07:03 AM

maybe there are directories for cron jobs on your box already. If so you can place your script there and have it executed automatically.

i. e.
/etc/cron.daily
/etc/cron.hourly

BCarey 05-09-2007 12:54 PM

Quote:

Originally Posted by dc_eros
1. What directory the MySQL database is stored (in Linux of course)?

Check the file that starts the service. On Slack it is /etc/rc.d/rc.mysqld. You will see that mysql is launched with a "--datadir=" option which specifies the location of the database files.

Brian


All times are GMT -5. The time now is 11:36 AM.