Importing database over 50mb in cPanel?

by 14 replies
21
I'm trying to upload a database to one of my wordpress websites, and the database is over 50mb (about 250mb). I currently use HostGator hosting with cPanel, and the limit on the upload is 50mb.

How would I go about this?

Thanks in advance!
#programming #50mb #cpanel #database #importing
  • 1. do you still have access to the original DB?

    Can you export single tables instead of the entire DB

    if you have one table that is huge (over 50mb), then I would export as CSV and then write a php script to insert the data again. Export just the DB scheme, no data so you can create all the tables on the new DB. then export all the data as CSV and use the function "fgetcsv" to loop through each row and insert into new DB. you can split up the CSV into manageable counts to make it process faster
  • Splitting it up is the answer.

    Your DB export as a text file that consists of two things - the CREATE TABLE statements for creating the tables and a number of INSERT statements to populate the tables with data.

    If your export is over 50 MB then chances are the bulk of it is the INSERT statements.

    Just split that file (using a text editor that can actually open such a large file) and upload it in the same sequence as the original file.

    Otherwise get a host without such a limitation! One of the MySQL databases I'm maintaining right now is approaching 1.5 GB and I have no problems transferring that data back and forth.

    Bill
  • there are different options
    1) compress the database scheme as much as possible.
    I think phpMyAdmin supports zip,gzip,bz2 in addition to non-compresses .sql file.

    2) connect using ssh and import it using
    mysql -u username -p -h localhost dbname < data.sql
    • [1] reply
  • Don't import it via any PHP based script. Just use SSH like lordspace suggested, if you don't have access to it just place the DB in a non public area of your server via FTP and ask your host to import it.
  • I think the ssh option will take forever but you can give it a go. Otherwise, just tar.gz it, write a php script to extract it on the server, then import it from there.
    • [1] reply
    • Why would it take forever? It works much faster than any php based importer...

  • Since the sql backup files are just a list of commands, I figured it would be running the individual commands one at a time over the connection which would surely add up to a ton of lag time with 50mb worth of commands. If this is not the case then I'm completely wrong.
    • [1] reply
  • If it is really a large database file after compress.I think it is better to upload it into the root of the web hosting by ftp with a clearn name.
    After you do it, you should set up a new cron job which you can find in the main control panel in the bottom.
    then:
    Enter an email address in the Email box and update it.
    Click one of the Common Settings menus and select the appropriate interval, OR enter an interval using the Minute, Hour, Day, Month, and Weekday boxes.
    Enter the command or script in the Command box like this:
    mysql -u databse username -p password locahost < /home/public_html/yourdata.sql
    then clink add.after your sql file is restored into your batabse.please remove this command in time.if not, it will restore again at that time you set.lol
  • Ok, so you backed up your current databases correct? I would do that first before doing any other procedure.

    Using the terminal can be tedious and somewhat advanced for those without experience, but is probably the most direct route. 1 command will handle your import regardless of size. If you do go that route, be sure you understand what the command is doing and have a look at your db file that you are importing. Be sure everything looks correct.

    Another easier, but more time consuming route would be to export 1 table at a time from the original db and import 1 table at a time into the new one using phpmyadmin. Assuming all tables are under the limit that should work as well.
  • Recently I have a huge database of 2.1GB for an article site and have to moved from another hosting to a Hostgator VPS. I understand your problem. Having to split the files and then importing the records. Big job and headache.

    Luckily mine was a new add-on VPS and I was able to get Hostgator to transfer the big database and all my Wordpress sites to the VPS free of charge. It was done within 24 hours. Great job, Hostgator.

    If yours is a new account and within 30 days of sign-up, you can get free transfer by using this link: http support.hostgator.com/articles/pre-sales-policies/hostgator-website-transfers (sorry not enough posts to enter links).

    If over 30 days of sign-up, you can request for a quote for the transfer.

    Edwin
  • Banned
    [DELETED]
  • Hi, If you communicated with the technical support of Hostgator and if you are using VPSserver then they'll do it for free. You just need to upload the database to any directory. If you know a little bit of shell programming, you can do this by yourself. Thanks :-)
  • Don't waste your time and contact Hostgator support because by default upload limit from phpmyadmin is 50mb, therefore upload the database file ( i.e .sql file) under your hosting account and ask Hostgator tech to store it under your database

    If they can't help you then its time to change Hosting

Next Topics on Trending Feed