i am new here but i have to start with a really big prob. I have one old server running 6 simple but large tables. each table has around 1mio entries.
regarding the performance i would like to move the DB to a new more powerful server. i tried to use a sql dump with mysql administrator. well i got a 8GB txtfile. But i cant load this file into the new db. mysql admin is just reading a few lines and then it claims done. When looking into the db nothing happend. is there a file size limit???
Has anyone experience in moving such large databases? What is the preferred method of migration, any useful tools?
MySQL-Front and SQLyog (see related thread on Admin interfaces) both have the ability to copy databases directly from one mySQL server to another. I've done it frequently with a 500MB database, but never anything larger. Should work. Just be patient.
Before you start the transfer, open another connection to the destination database. Issue "select count(*) ..." queries to check progress; neither of the above tools do a good job of letting you know how far along they are.
Thx for the Tip with Frontend and SQLyog. Both tools worked well but not as fast as i needed. A simple copy of innodb files in a new subdir of DATA_HOME on the new server was even faster.
Anyway the mentioned admintools are now a fixed part of my mysql workbench.