Insert newest rows for all tables

Forum Moderators: open

Message Too Old, No Replies

Insert newest rows for all tables

Moving large MySQL database to a new server

GoNC

10:08 am on Mar 5, 2014 (gmt 0)

I'm changing servers, and need to move a 6G MySQL database. The thing is, I don't want to make it live immediately; I want to take advantage of the new server being inactive to make some much needed updates to the tables that would have otherwise taken the database offline for several hours.

In an ideal world, I would be able to copy the database to the new server, make the database changes, then insert any rows that have been created on the old server after the original move.

I know that I could do this by PHP by logging in to the old server, selecting new rows, then logging in to the new server and inserting them. But, I'm working with 108 tables, and that's a pretty cumbersome task.

Before I go that route, can you guys suggest an easier way to do this?

FWIW, the old server is Redhat Linx, and the new server is CentOS. Both are running WHM/cPanel.

TIA!

DaveWave

4:43 pm on Mar 7, 2014 (gmt 0)

Why would it take the server of line for several hours to make the changes? If you script the changes it should be much quicker than that.

GoNC

3:17 am on Mar 8, 2014 (gmt 0)

I have 5 tables that take up the majority of the space. I need to remove two columns from each table, and add a FULLTEXT index to one column in each table.

I did these changes to one table, and it took just over an hour to complete everything. Based on that, I'm assuming it would take 4 hours to do the same to the other 4 tables.

DaveWave

10:33 am on Mar 8, 2014 (gmt 0)

Once you've done your migration why not copy these 5 tables, make the changes then check they're up to date then replace your old ones?

GoNC

10:45 am on Mar 8, 2014 (gmt 0)

How do I do that without overwriting the new table structure? The only way I know to copy the tables from one server to another is by using rsync, and AFAIK that would overwrite everything I change.

DaveWave

8:51 pm on Mar 9, 2014 (gmt 0)

Use Mysqldump to create scripts
[dev.mysql.com...]