Tag Archives: rsync

Problem transferring a mysql database with rsync

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:

  2. < ?php
  3. $db_host="localhost";$db_user="root";$db_pass="";
  4. $dbname="dev_db";
  5. $db_link=mysql_connect($db_host,$db_user,$db_pass);
  6. mysql_query("use $dbname");
  7. $res=mysql_query("show tables");
  8. if(!$res)die(mysql_error());
  9. while($arr=mysql_fetch_array($res)){
  10. echo "repairing table: ${arr[0]}\n";
  11. $res2=mysql_query("repair table ${arr[0]}");
  12. while($arr2=mysql_fetch_array($res2)){
  13. print_r($arr2);
  14. }
  15. }
  16. ?>

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.

MacOSX case sensitivity

I just got the biggest slap from MacOSX. Yes, I was punished for my ignorance.  I had not idea that the HFS filesystem defaults to case insensitive. I thought it would be case sensitive like in Unix but no.

When I first saw it I didn't believe it. The first clue was when I ran mysql_install_db an I got some warnings about about the filesystem being case insensitive. I didn't believe it because I knew I was able to create files with lower and upper cases so I just ignored it.

The confusion comes from the fact that the HFS+ filesystem is  MyFile.txt would be the same as myfile.txt .  The filesystem preserves cases so it will remember the original file name and a ls will show that but you would be able to access the file with different names. At the first glance this seems like a neat feature but it can actually create big problems when you're interacting with files from case sensitive filesystems.

The slap came from using rsync to backup files from a Linux machine to a macmini machine.   Just imagine a directory with many files split over directories from a to z and A to Z on Linux, something like files/a , files/A, files/b, files/B .

Now rsync -avz -e ssh  files/  backup@macmini:~/files/   and you'll end up with a folder named files on macmini but it will only have the upper case subfolders. The files in the lower case subfolders would be put in the upper case subfolders. This if really bad, it can really break consistency and mess up the data ( imagine different files with the same name one in A and the other in a as just one of the possibilities, there are other like this)

So now, what ? Reformat and change the filesystem to be case sensitive and journaled for easier recovery.
Be careful the next operation will remove all data on your disk:

  2. diskutil eraseDisk "Case-sensitive Journaled HFS+" DiskName YourDiskId

YourDiskId is the disk that you want to erase.It can be disk1, disk2,etc... Look for it using diskutil list