A little more then a year ago I wrote a post presenting three different methods to transfer a mysql database. The third method suggested in that post was copying the mysql database files directly from one server ( or location ) to another. This involved locking the tables with a read lock or even shutting down the mysql before the actual copy.
For my work I usually have a main system and a development system and each system have their own database so there is a need from time to time to copy the main database over the dev database but because the database is very big ( a lot of tables and some with a large size ) and not every table is changed I like using rsync to transfer only the changes especially when I'm transferring to remote locations because it saves bandwidth and is faster.
In this case where I found the problem I actually use the same mysql server to hold both main and dev database but I still use rsync to transfer just because it still is faster then cp.
So here is what I do: I stop the mysql server , run rsync -av /var/lib/mysql/main_db/ /var/lib/mysql/dev_db/ , the differences are transferred, I start the mysql server look at the dev_db and Boom! some of the tables are corrupt. The main database was fully functional before shutting down mysql , no tables were corrupt or needed a repair, and still don't need starting up mysql.
Maybe something even more interesting is that it's very likely noone was using any of the databases before mysql was shut down.
It seems that after the transfer I just have to "repair table table_name" for some of the tables in dev_db and the repair statements returns some message saying that the number of rows has changed. Of course since I don't want to go over each db and see if it actually needs a repair I chose to just repair all of them and I wrote a script for that.So I just run the bellow script after each transfer, just to make sure everything is ok:
This script should also show you the messages returned by the repair statements. So you can see if there really was a problem. Make sure you set the correct db connection parameters and database name before you try it.
When observing this problem I was using rsync version 2.6.9 and mysql 5.0.44 on gentoo x86_64. The problem doesn't come up on every transfer and not on all tables. Could this be a problem with rsync or mysql?
I'm thinking that if this is a problem with rsync then... wow...that is a big problem. I was relying on rsync for transferring a lot of stuff ... what if it didn't transfer something and who knows what else it didn't transfer?.
If it's a mysql problem, maybe mysql doesn't update the row counts on the tables correctly before shutting down so the files were actually correctly transferred just not correctly stored by mysql. If the row count is the only problem here then it's not such a bit problem. I'm hopping this is the case ...
I wonder if this problem would show up when using something like cp for the transfer. If that would happen then it's clearly a mysql problem but I cannot test with cp at the moment as my db is very large and that means I would have to keep the tables locked too much which is just not an option on a system that was just "promoted" to production.
I'll come back with another post once I find out more about this problem but until then just make sure to check your tables after the transfer if you are using something like rsync to transfer the files directly.