Thursday, April 18, 2013

How to take backup of sharded tables in mysql

There is an immense need of taking backup of tables which are sharded when you are working on critical servers.
Shard tables are an important part of any database and therefore system administrator should be able to provide the backup of these table at any given time.

Usage : To backup shard tables

mysql <databasename> -h <host> -u <user> -p <password> -e 
'show tables like <shard_table>%"' | grep -v Tables_in | xargs mysqldump --skip-lock-tables <databasename>  --host=<host> --user=<user> --password=<password> > table.sql