Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

Thanks

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

3 Answers 3

up vote 2 down vote accepted

You can dump the tables like this

#!/bin/bash

DATABASE="YourDatabaseName"
TNAME=shard_
TVAL="0"

while [ $TVAL -lt 10 ]
do
     mysqldump -u user -pPassword "$DATABASE" "$TNAME$TVAL" > "$TNAME$TVAL.sql"
    TVAL=$[$TVAL+2]
done

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`
do
   scp "$FILE" user@newhost:"$FILE"
done

Then import the files into your database on the new machine

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

You can use the following shell script:

table="shard_"
for i in `seq 0 10` ; do
        r=`expr $i % 2`
        if [ $r -eq 0 ] ; then
                table_name="$table$i"
                echo "Exporting Table: $table_name"
                mysqldump -u user -pPass database $table_name > "$table_name.sql"
                # Here, you can copy/transfer the file
        fi
done
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

 
discard

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.