How to automatically move specific MySQL tables from one machine to another?

Question

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

Answer 1

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

Answer 2

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

Answer 3

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.