Mysql backup?
 

Hi

I was wondering if there is a way to backup the whole mysql database. Are there files I could copy and put them back in the next install? Or some other method of transporting all data to other machine?

Thanks!

All you should need to backup is your mysql data directory and perhaps your my.cnf config file.

I'll try that and see what happens :) Oh, where is the mysql data directory?

The default on redhat 9 is:
/var/lib/mysql

Your exact location may vary depending on your install method and configure options.

Thanks!

This helped me very much!

Thanks for the very quick response too!

I did it. I transplanted the data from one machine to the other. But I got problems transplanting data from one database to another. I copied the content of the directory named after the database. However the mysql command did write the content of the new database. But the PHP couldn't connect to it. Does anybody have any idea why? Teoretically it should work. Unless the data is somehow connected to that particular database name. Which I doubt.

Are the database files readable and writeable my the user that mysql runs as?

Yes they are readable and writeable by the user that mysql runs as. That is what bothers me. It should work right? Or do tables in MySQL save which database they belong to? I don't know.

Thanks for the quixk response though.

If you didn't copy the privilages did you reassign them?

I copied the files in a different directory. Under the mysql base directory. I have changed privileges with chown and chmod. What's good is that when running the command mysql and connecting to this new database, works great. But using this new database from PHP pages, doesn't work for some reason. I have no idea why this doesn't work.

Actually I have discovered one thing. I cant connect to the database with other user than root. Can I change some other privileges in mysql? All I have changed were the rwx privileges in linux command line. If I login as some other user, I can't connect to the new databse even in mysql. So this is probably the reason that this is not working, as I am running apache as a different user and not root as this is not recommended.

Is there some way to change privileges in MySQL?

Yes, that is what I was refering to, sorry if it wasn't clear. Login to your mysql server s root:
mysql -u root -p

Then at the "mysql>" prompt:
grant all on DATABASE_NAME.* to USER@localhost identified by 'PASSWORD';

You will need o change the items in capitals depending on your setup.

Ok this is what I did :)

I used the command you told me and that locked me out for good. The command should change all the privileges for the user in question, right? I went into the database mysql and checked the database. All privileges to all users were set to "N". So I went and changed the privileges to users I want to have privileges. It works now. Thanks for help. I will have to review the mysql manual on commands regarding privileges for users.