Mysql – Can we back up thesql databases by copying files

backupinstallationMySQL

I tried to reinstall mysql on my machine, but it keeps failing. Everytime it fails, it does this locking and that locking and future effort would fail again.

Finally I decided to do truly clean install

  • Delete everything
  • Reinstall

The procedure is listed here : http://stuffthatspins.com/2011/01/08/ubuntu-10-x-completely-remove-and-clean-mysql-installation/

I do not want to lose data already there. Yea, it's backed up but I kind of like it there.

So, can I just mv content of /etc/mysql and then copy it back after I reinstall everything?

My programmer said it wouldn't work

If not, what else I should know?

I sort of forget where mysql store data. It's the default for ubuntu I guess.

Update: I tried that on some local databases using available computers. The Myisam table get transferred. However the innodb tables do not.

Also http://www.chriscalender.com/?tag=innodb-error-tablespace-id-in-file shows that innodb won't get transferred as easily by just copying files. Am I missing something?

Another Update: The answer is yes we can. We did it 😀

Update guys.

The short answer is yes we can. My programmer just did it. The key is to copy thew whole file and realize that innodb store database in one big file. Not sure why.

The other answer is better so I pick that (I don't think it's objective to pick my own answer). I just want to share that it works.

Best Answer

If you are able to shut your database server down (and my understanding is that you are indeed able to) - thereby disabling your application - then that's the easiest indeed.

You can, after shutdown, just copy+paste your data directory. Where is your data in the first place? Just issue:

SELECT @@datadir;

From within your MySQL server.

By the way, while you must backup your data someplace safe, I do not expect any of the apt-get or aptitude commands to actually touch your data.

NOTE that the page you've linked to has some pretty crazy ideas, like:

[!!DO NOT DO THIS!!] find / -iname ‘mysql*’ -exec rm -rf {} \;

I encourage you to look someplace else, given the above query. It will remove any and all mysql* file from your root filesystem. Including your own application's mysql-config.ini files (if any, of course), including libdbd-perl-mysql -- without clean uninstall... I mean, this is really BAD advice.

There's no point in backing up your my.cnf file, delete the file then put it back again. The same holds for your datadir.

Just uninstall MySQL and re-install is enough -- but that, too, is kinda funny, since both are done via apt-get -- so you basically reinstall the same version... At least get yourself newer version of MySQL.

Related Question