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


Problem :

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



Solution :

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.


Mysql Tutorials

Mysql Howto..

How would I add a foreign key in mysql for a column that has unrelated data?

How to import Production Data from Development Database?

how to download file from file's path stored in MySql by using servlet,JSP

How to sort products in Magento by best selling from the last month, mysql query based style?

How to update mysql row where timestamp field is today?

how to improve mysql query?

How can I set COLLATION of a field to utf8_general_ci with GORM?

How To assign null values in MySql using VB.NET

how to get next/previous records in mysql based on timestamp

How to join these 2 mysql queries?

How to write information from html form to MySQL Database

Inserting TEXT in mysql database showing error

How to figure out if mysql index fits entirely in memory

how to insert an empty value in mysql date type field?

How to map a function call in MySQL select for a specific table cell?

How properly select data from DB according to filter in Eloquent?

How to make a dropdownlist using mysql query with foreach?

Mysql: In the world database how is the code if you want to see the largest city (chosen by population) in each continent? [closed]

How to get affected rows after update statement in mysql?

how to join post meta and taxonomy table wpdb mysql

how to display multiple table base on user login name

How to update MySQL db from two associative array in php

How to insert data from an EXECUTE statement in mySql?

Advice on how to approach a live feed, containg data from a mysql databse

How to properly use mysql MONTH() and YEAR() in codeigniter active record

how to link tables together using timestamp sql, mysql

Pythonistas, how do I move data from top to bottom to left to right in MySQL (think multiple values for each ID)?

Displaying results from a mySQL database …how to add a table after X results?

How can I cleanly output current mysql variables to a my.cnf?

How can i update the Records included in another query using SUM and GROUP By in mysql