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 import MySQL database via SSH

Discussion in 'Database Discussions' started by maxie, Dec 1, 2010.

  1. maxie

    maxie Registered

    Joined:
    Dec 1, 2010
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    1
    hello every one

    i want to import my database to my cpanel but i dont know if this is exact command

    this is i found some guide
    but when i try to apply i got error
    here is the image (see attached screenshot)

    somebody can guide me ?

    very much appreciated
     

    Attached Files:

    #1 maxie, Dec 1, 2010
    Last edited by a moderator: Dec 2, 2010
  2. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Hello,

    In the attachment provided, I do not see the right command being used. First of all, the sql file must already be on the machine, so please ensure it is on the actual machine you are importing it into.

    Next, the command you've noted doesn't have mysql at the beginning of it. From what I can see, the command you should have used would be:

    You'd want to do the command once the sql file is on the machine and be in the directory where that sql file is placed. Replace passwordhere with the actual password and do not place a space after the -p before the password, it must be directly next to the -p with no space.

    Of note, you don't even need to enter the password at all nor the MySQL user if you run this command as root, you could just do:

    This assumes the database already exists and is called gamsolut_new12012010, which is hard to believe it would be called that under cPanel since it's is beyond the 16 character limit for MySQL database names. You might want to re-check the database name in cPanel > MySQL databases area. If you haven't even created the database, you would need to first create it in cPanel > MySQL databases area.

    Thanks!
     
    Rafael Alfaro likes this.
  3. freakqnc

    freakqnc Registered

    Joined:
    Sep 22, 2011
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Having the same problem: "PhpMyAdmin would not import large file"

    The documentation states as follows:
    [...]
    1.16 I cannot upload big dump files (memory, HTTP or timeout problems).
    Starting with version 2.7.0, the import engine has been re–written and these problems should not occur. If possible, upgrade your phpMyAdmin to the latest version to take advantage of the new import features.

    The first things to check (or ask your host provider to check) are the values of upload_max_filesize, memory_limit and post_max_size in the php.iniconfiguration file. All of these three settings limit the maximum size of data that can be submitted and handled by PHP. One user also said that post_max_size and memory_limit need to be larger than upload_max_filesize.

    There exist several workarounds if your upload is too big or your hosting provider is unwilling to change the settings:

    Look at the $cfg['UploadDir'] feature. This allows one to upload a file to the server via scp, ftp, or your favorite file transfer method. PhpMyAdmin is then able to import the files from the temporary directory. More information is available in the Configuration section of this document.
    Using a utility (such as BigDump) to split the files before uploading. We cannot support this or any third party applications, but are aware of users having success with it.
    If you have shell (command line) access, use MySQL to import the files directly. You can do this by issuing the "source" command from within MySQL: sourcefilename.sql.
    [...]

    Unfortunately after increasing the values of upload_max_filesize to 128M, memory_limit to 256M and post_max_size to 256M as well, the process fails therefore it's irrelevant whether the import engine has been re-written since v2.7.0 of PMA. On v3.4.4 is not working even applying the time-wasting suggestions of increasing memory limit and post size in php.ini

    - The $cfg['UploadDir'] part is explained like sh!t in the documentation and I'd prefer to use the SSH method to this utter idiocy of having to upload to an UploadDir and then have to do a few somersaults to find out where that "Dir" should live and having to deduce it via remote mind reading of whomever wrote the PMA documentation... I am not say it should have been a "for dummies" edition but a simple example would have sufficed for those who haven't got the gift of a science-infused brain;

    - The bigDump is true to its name and it's an unreliable hit-or-miss hack which I wouldn't want to use on production DBs;

    - The command line is no help either since issuing (as root) the command:
    mysql username_dbname < mydbbackupfile.sql
    what I get is
    ERROR 1049 (42000): Unknown database 'username_dbname'

    Since it's unknown (because it has not been created) then perhaps one would think that creating a DB named 'username_dbname' would allow the command to find that DB and import the darn data in it... No love there either! Creating a DB with that name will just return
    ERROR 1007 (HY000) at line 22: Can't create database 'username_dbname'; database exists
    (the 'username_dbname' is contained in the 'mydbbackupfile.sql').

    Doesn't like it raw, doesn't like it cooked... Whadda...?!

    It's extremely frustrating having to waste so much time for something that should be so simple and straight forward. The limit on uploading large files is utterly ridiculous especially since the definition of that "large" it's now obsolete and the times when something over 64M was a "large" file are long gone... apparently though PMA is still stuck in a time-warp and keeps thinking we are still networking with 56K modems to 1Mbps LANs saving data on 1.44MB floppies. ;P

    I mean it's just importing

    I am no expert by any stretch of the word and may be doing something wrong... nevertheless I still think that this kind of masonic handshakes required to accomplish the simplest of things (importing a DB over 50MB is just one of the items in a very long list) is one of the most ridiculous things on the face of the planet.

    If someone would be so nice to share the answer to life, the universe and everything (which would include importing "large" DBs in MySQL) without replying 42, but providing info that would help accomplish the task of importing a large DB into MySQL that would be much appreciated... Vogonians please abstain ;)

    Cheers :)
     
  4. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    First of all, did you increase the values in these files, since as a cPanel application, phpMyAdmin does not use the user-level php.ini but the cPanel ones:

    /usr/local/cpanel/3rdparty/etc/phpmyadmin/php.ini
    /usr/local/cpanel/3rdparty/etc/php.ini

    Next, is the database name being used the actual username with the database name appended after an underscore, or are you using the one you noted instead of username_dbname ?
     
  5. nobodyk

    nobodyk Well-Known Member

    Joined:
    Aug 1, 2010
    Messages:
    90
    Likes Received:
    0
    Trophy Points:
    6
Loading...

Share This Page