Recently I screwed my server up by changing a permission and then it got worse and worse with my tinkering. Anyway, I am going to reinstall Linux (needed to get newer mandrake anyway), but ran into a problem backing up mySql databases. I cannot connect properly to use mysqldump (in fact, I cannot connect to any user besides root via ssh). I found my database directory (/var/lib/mysql/the_dir_I_need). I tarred that directory and was wondering if that would be good enough for a backup? Can I just untar the directory after clean install? Any input would be great.

Bif

It should work but never a garauntee doing it that way. I've had to do it myself that way before, didn't run into any problems.

Hello,

Just untarring will not help you.

There must be some other way to do that. Check

http://dev.mysql.com/doc/mysql/en/Backup.html

The best way of backing up any SQL database, is by exporting it. If you are doing a filesystem dump, you must make sure that the database isn't running, or you are getting a copy of a possible inconsistent database. (not that mysql really does much to make sure it is consistent in the first place, some will claim that mysql isn't really a RDBMS at all)

The backup command for mysql is named mysqldump

In its simplest form it works something like this:

mysqldump -a -u user -p database > export_file.sql

It generates a bunch of SQL queries that can be used to recreate the database as it was, so you can simply use the mysql command to restore it. You can also, just by editing it, restore only parts of it.

And if you read his post, he states clearly that the given command to do it properly isn't working, he's trying to find alternatives.

Thanks for the replies. I know the correct way to backup my databases, but seeing I can't connect it is useless. I'll definitely hope that just backing up the directory will work this time (and now I know to make daily backups hehe). Thanks, everyone!

Whoops, i must have skipped the middle part :-)

As already stated above, it should work simply untarring it if you have everything, it probably wont even know you did it :-)

Like I said, just grabbing the db directories out of /var/lib/mysql should work, though I've never grabbed the default mysql db, that I just recreated with a new install/new host, etc.

The worst that can happen is you lose some data or some is corrupt, easily just removing and rebuilding, etc.

It worked fine, thanks again.

Bif