Not having had huge experience working with mysql dumping and rsync. This afternoon I was faced with the task of taking a backup of one our busy, very database intensive games using mysqldump command, but I also needed to exclude some rather over sized tables from the dump. After which I wanted to pipe over the mysqldumps to our remote secure testing server using rsync which id also never used before.
So the first things first I let our users know the site is going down in 5 mins I then sabotage our db_connect functions so that users a directed to a page with no connection attempts or db calls on it.
I then write the command to dump data and tables ignoring a few tables which id already flagged as potentially large and time consuming to dump.
My command looked a little like this:-
mysqldump -u username -p --ignore-table=database.tableA, database.tableB -B database -K > /path/to/outputfile.sql
With quite a few more ignores hehe but hopefully you'll get the gist.
I then just wanted to dump the structure of the troublesome tables.
So I did something like the below.
mysqldump -u username -p database table1 table2 -d > /path/to/outputfile.sql
Notice the -d, this is very important as it signifies no data just structure.
Now I have my two files I want to sync them accross the our test area located on another server, this time using a rsync
I used ssh with rysnc and my final command looked like this.
rsync -a -e ssh backups/ username@server:/home/google/backups
Which simply took my backups directory on the server i was connected to already and synced it with the credentials entered here. Seemlessly and with all permissions etc carried over.
Now thus I had achieved what I needed to and all that was left was to load the files into the database for the test area. By simply:
mysql -u username -p database < /path/to/inputfile.sql