Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2005
    Location
    Sheffield, UK
    Posts
    782

    Moving a MySQL DB with 88525 tables to a new server - Help?

    Hi all,

    i need to move one of my sites to a new server and it has a database with 88525 tables (quite a few). As i cant even access the database in phpmyadmin, i know there is a way you can backup via ssh and then move it over.

    Does anybody have any guides or tips on how i can do this?

    Thanks in advance,

    Rick.M
    WHSuite - Billing, Automation and Client Management Software.

  2. #2
    Join Date
    Oct 2002
    Location
    State of Disbelief
    Posts
    22,947
    Tables or rows/records? That's a huge number of tables, if so. Never had to move one that large, but this is a typical backup / restore via SSH.

    Backup [drop table if exists]:
    mysqldump --opt -u username -p database_name > dumpfile.sql

    Restore:
    mysql -u username -p database_name < dumpfile.sql
    Having problems, or maybe questions about WHT? Head over to the help desk!

  3. #3
    Join Date
    Sep 2005
    Location
    Sheffield, UK
    Posts
    782
    Great, thanks Bear i will try that (and its tables...very big, i know)
    WHSuite - Billing, Automation and Client Management Software.

  4. #4
    Join Date
    Jul 2003
    Location
    Kuwait
    Posts
    5,099
    That seems to be an aweful lot of tables. Warning bells about DB design should be going off, unless its a highly specialized database.

    Anyhow, you can also directly transfer the database to the remote server if you have remote DB access at both servers (not likely, but hey why not ask).

    In addition, for future considerations -- try the excellent MySQL Administrator program from MySQL AB. It allows for more 'managed' backups and has no limit on the size of databases. You would need to have remote access to your database, since its a Windows application and you probably want to run it from a remote client. In addition, it also allows you to manage your database server (for example, setup replication and slaves, add users, etc.) Give it a shot, its free.
    In order to understand recursion, one must first understand recursion.
    If you feel like it, you can read my blog
    Signal > Noise

  5. #5
    Join Date
    Sep 2005
    Location
    Sheffield, UK
    Posts
    782
    Thanks fyrestrtr, i will give it a shot.

    I cant help the size of the database as its a free php nuke hosting service and every user takes up around 25-30 tables each...with around 700 users its quite a shove on the server & database.
    WHSuite - Billing, Automation and Client Management Software.

  6. #6
    Join Date
    Jul 2003
    Location
    Kuwait
    Posts
    5,099
    Good grief. You are a brave soul if you are dealing with free php-nuke hosting service.

    Nevermind what I said about highly specialized. I didn't realize you were talking about nuke.

    Forgot to mention on my earlier post, there is a way to run MySQL Administrator (and really, any MySQL frontend) if you have SSH access, but not remote MySQL access. You can tunnel the MySQL port to your local computer. By default, MySQL runs on port 3306. So in your favorite SSH client, forward that port to your local computer, then just give 'localhost' as the server in your MySQL client program. Of course, ensure nothing else is running on 3306 on your local box (like a MySQL server).

    Other ways to do the same is to add your static IP in the 'access hosts' option in the MySQL settings of cpanel -- you would not need to do the tunneling then.

    I really should write up a tutorial on this.
    Last edited by Burhan; 11-06-2005 at 08:42 AM.
    In order to understand recursion, one must first understand recursion.
    If you feel like it, you can read my blog
    Signal > Noise

  7. #7
    Join Date
    Sep 2005
    Location
    Sheffield, UK
    Posts
    782
    I know...i like going out on the limb :p

    I have fortress installed and all sites are monitored...there shouldnt be too much of a problem
    WHSuite - Billing, Automation and Client Management Software.

Posting Permissions

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