homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

Insert newest rows for all tables
Moving large MySQL database to a new server

Msg#: 4651293 posted 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.




Msg#: 4651293 posted 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.


Msg#: 4651293 posted 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.


Msg#: 4651293 posted 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?


Msg#: 4651293 posted 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.


Msg#: 4651293 posted 8:51 pm on Mar 9, 2014 (gmt 0)

Use Mysqldump to create scripts

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved