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.

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

What is the best way to do that? There are many tables so ideally I wouldn't have to type out each table name individually but do something automatically. Perhaps something like:

# pseudo code    
for i in range(n):
    tablename = "shard_"+str(2*i)
    # Move tablename to new machine

Thanks

Answer 1

I'm not sure I see the problem, but if I got it right, you can use Python to generate the export SQL script, and the import one for the other machine.

That'll save you the trouble of doing it manually. As for your code snippet, I think the best way to go about migrating a database from a server to another one is using the engine's own capabilities.

Answer 2

I'd create a single (or perhaps multiple) mysqldump invocations, like so

print "mysqldump database",
for i in range(n):
    print "shard_"+str(2*i),

Run this command in a shell, and move the dump file to the new machine, then run it there through mysql. Then generate and run the "drop table" statements for the tables you have moved.