Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a MySQL database with tables in the form of "shard_0", "shard_1", "shard_2", etc.

I want to add another DB server and move the even-numbered shards ("shard_0", "shard_2", "shard_4", ...) to the new machine.

How do I do that? There are many tables so ideally I wouldn't have to type out each table name individually but do something automatically.


share|improve this question
Bravo for recognizing that this is is an excellent opportunity for scripting; a definable and repeatable process – gWaldo Oct 29 '10 at 17:16
up vote 2 down vote accepted

You can dump the tables like this



while [ $TVAL -lt 10 ]
     mysqldump -u user -pPassword "$DATABASE" "$TNAME$TVAL" > "$TNAME$TVAL.sql"

You'll end up with files shard_0.sql ... shard_8.sql. Transfer them to the new machine however you want but something like the code below should work

for FILE in `ls shard_*.sql`
   scp "$FILE" user@newhost:"$FILE"

Then import the files into your database on the new machine

for FILE in `ls shard_*.sql`
   mysql -u user -pPassword YourDataBaseName <"$FILE"
share|improve this answer

You can use the following shell script:

for i in `seq 0 10` ; do
        r=`expr $i % 2`
        if [ $r -eq 0 ] ; then
                echo "Exporting Table: $table_name"
                mysqldump -u user -pPass database $table_name > "$table_name.sql"
                # Here, you can copy/transfer the file
share|improve this answer

Syntax will vary depending on what language you want to use, but since you want the even-numbered tables, you'll iterate through the tables in the database and add a conditional statement (if) to check the name using the modulus operator (remainder of a divide, in this case dividing by 2) to determine whether or not it is an even number. If it's even, do the mysql dump, otherwise skip.

in Pseudocode:

  • for each table in database
  • parse out the number from the table name
    • if %2 == 0 # %2 is "modulus 2 equals 0"; "modulus is sometimes called "mod"
      • {mysqldump [options];}
    • end if
  • next
  • rsync/scp to other server

Then on the other server, import your dumpfiles.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.