As any database administrator trying to keep the data clean, up to date, optimized, and also safe by taking backups and transfer these backups to another machine.
And all we know that we have more than backup plan depends on the database engine used, and I mentioned these strategies mysql-backup-techniques early.
Most of us use mysqlhotcopy perl script to take backups for 100% myisam database, which is perfect script, and better than mysqldump utility in some cases.
Mysqlhotcopy will create version of you database folder based on Numofversions parameter, let’s have an example of database “world” with 5 versions
drwxr-x--- 2 buser buser 12288 Jan 9 02:00 world.1
drwxr-x--- 2 buser buser 12288 Jan 8 02:00 world.2
drwxr-x--- 2 buser buser 12288 Jan 7 02:00 world.3
drwxr-x--- 2 buser buser 12288 Jan 6 02:00 world.4
drwxr-x--- 2 buser buser 12288 Jan 5 02:00 world.5
As we can see here we have 5 versions of backup and the oldest backup is before 5 days, fine for me, so if explore inside the any folder we’ll see all the tables in your database, each table has 3 files (.frm, .MYD, and .MYI) for (table definition file, data file, and index file) which mysql create for every table.
So let’s explore another version, aha as I expect same files because I didn’t create new tables, but I can see the differences between data file for one table and same table in another version. So there was some insertion on this table today, catch ya ;) !!
So I decided to move my backups to another machine using rsync utility, it’s a amazing tool in fact, because I can transfer the differences only to remote machine after I have the versions there, which will save me time and bandwidth.
But what I found later, something surprises me, when I delete records in the local server it’ll removed from the data file, and when I took backups the new version will be clean from the deleted data, no problem and everything is ok till now, but when I transfer these backups to the remote machine it’s just transfer the new and updated data, and keep the deleted rows in the file, so after two weeks I have different copy from what I have in the local server, I have all the new data and all the deleted data also, which means that I don’t have clean copies in other machine except the local.
Although I’m using rsync command with these options ‘z’,’a’,’v’,’r’,’e’ the shell considered the update ‘u’ just the updated and inserted data, because it’s check on the timestamp and the size of the file.
so be aware from unclean data in your backups, because you may want it clean and update to date, like i know, so in the next post i'll tell you the ways to fix that problem.
