question about MySQL database migration

Question

Hi there: If I have a MySQL database with several tables on a live server, now I would like to migrate this database to another server. Of course, the migration I mean here involves some database tables, for example: add some new columns to several tables, add some new tables etc.. Now, the only method I can think of is to use some php/python(two scripts I know) script, connect two databases, dump the data from the old database, and then write into the new database. However, this method is not efficient at all. For example: in old database, table A has 28 columns; in new database, table A has 29 columns, but the extra column will have default value 0 for all the old rows. My script still needs to dump the data row by row and insert each row into the new database.

Using MySQLDump etc.. won't work. Here is the detail. For example: I have FOUR old databases, I can name them as 'DB_a', 'DB_b', 'DB_c', 'DB_d'. Now the old table A has 28 columns, I want to add each row in table A into the new database with a new column ID 'DB_x' (x to indicate which database it comes from). If I can't differentiate the database ID by the row's content, the only way I can identify them is going through some user input parameters.

Is there any tools or a better method than writing a script yourself? Here, I dont need to worry about multithread writing problems etc.., I mean the old database will be down (not open to public usage etc.., only for upgrade ) for a while.

Thanks!!

Answer 1

I don't entirely understand your situation with the columns (wouldn't it be more sensible to add any new columns after migration?), but one of the arguably fastest methods to copy a database across servers is mysqlhotcopy. It can copy myISAM only and has a number of other requirements, but it's awfully fast because it skips the create dump / import dump step completely.

Answer 2

Sure there are tools that can help you achieving what you're trying to do. Mysqldump is a premier example of such tools. Just take a glance here: http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

What you could do is:

1) You make a dump of the current db, using mysqldump (with the --no-data option) to fetch the schema only

2) You alter the schema you have dumped, adding new columns

3) You create your new schema (mysql < dump.sql - just google for mysql backup restore for more help on the syntax)

4) Dump your data using the mysqldump complete-insert option (see link above)

5) Import your data, using mysql < data.sql

This should do the job for you, good luck!

Answer 3

Use mysqldump to dump the data, then echo output.txt > msyql. Now the old data is on the new server. Manipulate as necessary.

Answer 4

Generally when you migrate a database to new servers, you don't apply a bunch of schema changes at the same time, for the reasons that you're running into right now.

MySQL has a dump tool called mysqldump that can be used to easily take a snapshot/backup of a database. The snapshot can then be copied to a new server and installed.

You should figure out all the changes that have been done to your "new" database, and write out a script of all the SQL commands needed to "upgrade" the old database to the new version that you're using (e.g. ALTER TABLE a ADD COLUMN x, etc). After you're sure it's working, take a dump of the old one, copy it over, install it, and then apply your change script.

Answer 5

Adding extra rows can be done on a live database:

ALTER TABLE [table-name] ADD [row-name] MEDIUMINT(8) default 0;

MySql will default all existing rows to the default value.

So here is what I would do:

1. make a copy of you're old database with MySql dump command.

2. run the resulting SQL file against you're new database, now you have an exact copy.

3. write a migration.sql file that will modify you're database with modify table commands and for complex conversions some temporary MySql procedures.

4. test you're script (when fail, go to (2)).

5. If all OK, then goto (1) and go live with you're new database.

Answer 6

These are all valid approaches, but I believe you want to write a sql statement that writes other insert statements that support the new columns you have.