The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

How to copy sql database with over 170,000 tables in it.

Discussion in 'Database Discussions' started by Daniel Artes, Mar 25, 2006.

  1. Daniel Artes

    Daniel Artes Well-Known Member

    Joined:
    Jun 21, 2003
    Messages:
    52
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    USA - Baltimore
    Hello everyone, I hope somebody with more knowledge than me knows what to do with this problem I got....I have a database with more than 170,000 tables, and WHM wont copy the complete data base when I use:

    /scripts/pkgacct user

    So I though I could do it Manually using:

    mysql -u user -p database < file.sql

    But it has been procesing for over 10 hours and nothing.


    So now I think the best would be to copy all the files inside /var/lib/mysql/mydatabase/ manually. But when I do cp, it keeps procesing for ever, same when I try to list the folder's content (ls).


    Here my question:

    Is there a way I can copy all files that start with letter a, I know it will work if I copy smaller amounts of tables, and not 170,000 tables all at once.

    Or do you know any other way of copying huge databases like the one I have..

    I have been searching in forums and google all day long, Mots of the time I find the answers I need but now im really lost. I NEED HELP!! THANK YOU!!
     
  2. Spiral

    Spiral BANNED

    Joined:
    Jun 24, 2005
    Messages:
    2,023
    Likes Received:
    7
    Trophy Points:
    0
    If the MySQL version of the source and the destination are similiar,
    you can just simply archive the database files directly and drop them
    in the same path in the new database:

    tar zcvf mydb.tgz ~mysql/(dbname)


    Works with:
    ===========
    3.xx to 3.xx
    4.0.x to 4.0.x
    4.1.x to 4.1.x
    5.x to 5.x

    Don't try to directly transport the files though if MySQL versions are different series.
    They can be slightly different versions though as long as both the source and destination
    servers are from the same version series (In example v4.1.14 to a v4.1.18 server)

    Doing it this way, there would be no need to export to SQL queries or spend forever
    trying to load the data into a new database from a massive SQL file. Skips all that.
     
    #2 Spiral, Mar 25, 2006
    Last edited: Mar 25, 2006
  3. Daniel Artes

    Daniel Artes Well-Known Member

    Joined:
    Jun 21, 2003
    Messages:
    52
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    USA - Baltimore
    dude I love you!!!
    PERFECT!!! WOW :D THAT EASY? :D :D

    Thanks so much,
     
Loading...

Share This Page