copying mysql database (and permissions arghh!)

By basd on Jun 3, 2010 | In linux, opensuse, -arghhh!!

It turns out that duplicating a mysql database isn't all that difficult. I spent a lot of time programming with tools like dbase/foxbase, where copying a table or set of tables was a simple task. But, my mySQL experience has all been based on an ISP provided system, which left the inner workings somewhat mysterious.

...

Now that I have mySQL running on my local laptop, it was worthwhile to figure out how to duplicate -- and in fact, all I had to do was copy the database directory from /var/lib/mysql. Done.

Although, it wasn't quite as straightforward as all that because of permissions. The database files have "mysql" user privileges. I could copy via root access. EXCEPT, my remote drive that I wished to copy to must have root squash set, so I can't copy files to it with root permissions. Or something. I had this ridiculous approach of making a local copy, setting the permissions to my regular user, moving that to the remote partition, on the server, copying the database to /var/lib/mysql and then resetting the privileges.

What I really want to do is have a two-way sync, hopefully something that is reasonably well automated. So, given that I have iFolders working, that would be a tool -- except my mySQL server is also my iFolders server, so that is apparently out. phpMyAdmin offers a manual synchronization tool -- but I could not figure out the remote mySQL server connection information. It may be as simple a problem as opening up the firewall to the particular port, but I don't know -- especially considering I gave up.

I suppose I could write a couple of scripts that semi-automate the steps I mentioned above, but that seems rather cumbersome.

What to do, what to do ...