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.

Restoring mySQL

Discussion in 'General Discussion' started by apc1, May 18, 2005.

  1. apc1

    apc1 Well-Known Member

    Joined:
    Oct 18, 2004
    Messages:
    123
    Likes Received:
    0
    Trophy Points:
    16
    Hello,

    I need to restore a large mySQL database.

    The cPanel restore function will not work because the database, which is hundreds of megabytes always times out about a quarter of the way into the restore process.

    I have going to the Linux command line, having root access to the server, and using the command:

    mysql -u account_user -p account_dbname < backupfile.sql

    I get the prompt for a password but the password is always rejected with:

    ERROR 1045: Access denied for user: 'xxx@localhost' (Using password: YES)

    I have used various combinations of commands, I have also created a blank database with no passwprd set at all, and I always get the access denied. I am logged in as root.

    Any ideas please?

    Thank you very much!
     
  2. chirpy

    chirpy Well-Known Member

    Joined:
    Jun 15, 2002
    Messages:
    13,475
    Likes Received:
    20
    Trophy Points:
    38
    Location:
    Go on, have a guess
    Does the MySQL user account_user already exist? If not, you may need to create it first, or use the root user and password to restore the database.
     
  3. apc1

    apc1 Well-Known Member

    Joined:
    Oct 18, 2004
    Messages:
    123
    Likes Received:
    0
    Trophy Points:
    16
    Thanks for your response.

    I am still getting nowhere with this.

    I have used the root password in the following format:

    mysql -u root -p databasename < backupname.sql

    But all I get after ebtering the password is: ERROR 1045 Access denied for user "root@localhost" (Using password: YES)

    Can I create a new database and restore it to that with the new user and password or is the user and password actually stored in the backup dump of the database itself so I have to use the same database name, user and password to restore?

    Thanks very much.
     
  4. racomnet

    racomnet BANNED

    Joined:
    Oct 6, 2004
    Messages:
    68
    Likes Received:
    0
    Trophy Points:
    0
    .

    try first from WHM to setup an root password for mysql, from mysql section. After that u can bakup the database with mysql user_database < original_database.sql
    or mysql -u user -ppassword user_database < original.sql
    -ppassword without space.
    Hope this will help
     
  5. apc1

    apc1 Well-Known Member

    Joined:
    Oct 18, 2004
    Messages:
    123
    Likes Received:
    0
    Trophy Points:
    16
    Hello,

    Thanks, I have already done that.

    I am now using the following restore syntax:

    mysql -u root -p account_dbname < backupname.sql

    And getting:

    ERROR 1064 at line 20: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT CHARSET=latin1' at line 7

    The mySQL version is: 4.0.22

    And yes I can login to the mySQL command line with the root password.

    Any suggestions would be much appreciated.

    Thank you very much.
     
  6. Infopro

    Infopro cPanel Sr. Product Evangelist
    Staff Member

    Joined:
    May 20, 2003
    Messages:
    14,474
    Likes Received:
    202
    Trophy Points:
    63
    Location:
    Pennsylvania
    cPanel Access Level:
    Root Administrator
    Twitter:
    mysql -u account_dbuser -p account_dbname < /path/to/DB/backupname.sql
    enter pass when asked

    Should work fine.
     
  7. apc1

    apc1 Well-Known Member

    Joined:
    Oct 18, 2004
    Messages:
    123
    Likes Received:
    0
    Trophy Points:
    16
    Thanks for the suggestion, but it results in exactly the same error.

    Best regards.
     
  8. racomnet

    racomnet BANNED

    Joined:
    Oct 6, 2004
    Messages:
    68
    Likes Received:
    0
    Trophy Points:
    0
    what corespond to line 7 ?
    are u trying to restore from a diferent mysql version ?
     
Loading...

Share This Page