Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2005
    Posts
    5

    Unanswered: Moving 8GB Database to new server

    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

  2. #2
    Join Date
    Feb 2005
    Posts
    8
    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.

  3. #3
    Join Date
    Feb 2005
    Posts
    5

    Thumbs up

    Quote Originally Posted by rbstern
    ... Should work. Just be patient. ...
    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.

  4. #4
    Join Date
    Feb 2005
    Posts
    8
    Quote Originally Posted by monsteradmin
    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.
    Or, you could just do it like that.

  5. #5
    Join Date
    Feb 2005
    Posts
    1
    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]
    Last edited by Dnyt; 02-10-05 at 05:46.

  6. #6
    Join Date
    Feb 2004
    Location
    Bucharest
    Posts
    37

    re

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •