Hi,
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?

Thx

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.

Or, you could just do it like that.

If you are on Linux server and have shell access you can use this command:

mysqldump -u [Username] -p [password] [databasename] | gzip > [backupfile.sql.gz]

[username] - this is your database username
[password]- this is the password for your database
[databasename] - the name of your database
[backupfile.sql.gz] - the filename for your database backup

The "gzip" will compress your database size about 50%

Restoring MySQL Database:
mysql -u[Username] -p[password] [databasename] < [backupfile.sql.gz]


If you are on Windows server, you can do this command too. For example like at MS-DOS command prompt:

C:/mysql/bin/mysqldump -u [Username] -p [password] [databasename] > [backupfile.sql]

mysqldump -u [user] --password=[password] [database] | bzip2 -c > /path/to/database.sql.bz2

bzip compressed me a 90mb sql file into a 5 mb archive