How can I import a large 14 GB MySQL dump file into a new MySQL database

Question

How can I import a large  14 GB  MySQL dump file into a new MySQL database

User · Answer

I have made a PHP script which is designed to import large database dumps which have been generated by phpmyadmin or mysql dump (from cpanel) . It's called PETMI and you can download it here [project page] [gitlab page].

It works by splitting an. sql file into smaller files called a split and processing each split one at a time. Splits which fail to process can be processed manually by the user in phpmyadmin. This can be easily programmed as in sql dumps, each command is on a new line. Some things in sql dumps work in phpmyadmin imports but not in mysqli_query so those lines have been stripped from the splits.

It has been tested with a 1GB database. It has to be uploaded to an existing website. PETMI is open source and the sample code can be seen on Gitlab.

A moderator asked me to provide some sample code. I'm on a phone so excuse the formatting.

Here is the code that creates the splits.

                 //gets the config page
                  if (isset($_POST['register']) && $_POST['register'])
                {
                 echo " <img src=\"loading.gif\">";
        $folder = "split/";
        include ("config.php");
        
        $fh = fopen("importme.sql", 'a') or die("can't open file");
        $stringData = "-- --------------------------------------------------------";
        fwrite($fh, $stringData);
        fclose($fh);
        
        
        $file2 = fopen("importme.sql","r");
        
        //echo "<br><textarea class=\"mediumtext\" style=\"width: 500px; height: 200px;\">";
        $danumber = "1";
        while(! feof($file2)){
            //echo fgets($file2)."<!-- <br /><hr color=\"red\" size=\"15\"> -->"; 
            $oneline = fgets($file2); //this is fgets($file2) but formatted nicely
            //echo "<br>$oneline";
            
            $findme1  = '-- --------------------------------------------------------';
            $pos1 = strpos($oneline, $findme1);
            $findme2  = '-- Table structure for';
            $pos2 = strpos($oneline, $findme2);
            $findme3  = '-- Dumping data for';
            $pos3 = strpos($oneline, $findme3);
            $findme4  = '-- Indexes for dumped tables';
            $pos4 = strpos($oneline, $findme4);
            $findme5  = '-- AUTO_INCREMENT for dumped tables';
            $pos5 = strpos($oneline, $findme5);
            if ($pos1 === false && $pos2 === false && $pos3 === false && $pos4 === false && $pos5 === false) {

                // setcookie("filenumber",$i);
                // if ($danumber2 == ""){$danumber2 = "0";} else { $danumber2 = $danumber2 +1;}                 
                $ourFileName = "split/sql-split-$danumber.sql";
                // echo "writing danumber is $danumber";
                $ourFileHandle = fopen($ourFileName, 'a') or die("can't edit file. chmod directory to 777");

                $stringData = $oneline;
                $stringData = preg_replace("/\/[*][!\d\sA-Za-z@_='+:,]*[*][\/][;]/", "", $stringData);
                $stringData = preg_replace("/\/[*][!]*[\d A-Za-z`]*[*]\/[;]/", "", $stringData);
                $stringData = preg_replace("/DROP TABLE IF EXISTS `[a-zA-Z]*`;/", "", $stringData);
                $stringData = preg_replace("/LOCK TABLES `[a-zA-Z` ;]*/", "", $stringData);
                $stringData = preg_replace("/UNLOCK TABLES;/", "", $stringData);

                fwrite($ourFileHandle, $stringData);
                fclose($ourFileHandle);
            
            } else {
                    //write new file;
                    if ($danumber == ""){$danumber = "1";} else { $danumber = $danumber +1;}
                    $ourFileName = "split/sql-split-$danumber.sql"; 
                    //echo "$ourFileName has been written with the contents above.\n";
                    
                    $ourFileName = "split/sql-split-$danumber.sql";
                    $ourFileHandle = fopen($ourFileName, 'a') or die("can't edit file. chmod directory to 777");
                    $stringData = "$oneline";
                    fwrite($ourFileHandle, $stringData);
                    fclose($ourFileHandle);
            }
        }
        //echo "</textarea>";
        
    
    fclose($file2);

Here is the code that imports the split

<?php
ob_start();
// allows you to use cookies
include ("config.php");
//gets the config page
if (isset($_POST['register']))
{
echo "<div id**strong text**=\"sel1\"><img src=\"loading.gif\"></div>";

// the above line checks to see if the html form has been submitted
$dbname = $accesshost;
$dbhost = $username;
$dbuser = $password;
$dbpasswd = $database;
$table_prefix = $dbprefix;
//the above lines set variables with the user submitted information
    //none were left blank!  We continue...


//echo "$importme";

echo "<hr>";

$importme = "$_GET[file]";
$importme = file_get_contents($importme);
//echo "<b>$importme</b><br><br>";
$sql = $importme;
$findme1  = '-- Indexes for dumped tables';
$pos1 = strpos($importme, $findme1);
$findme2 = '-- AUTO_INCREMENT for dumped tables';
$pos2 = strpos($importme, $findme2);

$dbhost = '';
@set_time_limit(0);


if($pos1 !== false){
    $splitted = explode("-- Indexes for table", $importme);
    // print_r($splitted);
    for($i=0;$i<count($splitted);$i++){
        $sql = $splitted[$i];
        $sql = preg_replace("/[`][a-z`\s]*[-]{2}/", "", $sql);
        
        // echo "<b>$sql</b><hr>";
        if($table_prefix !== 'phpbb_') $sql = preg_replace('/phpbb_/', $table_prefix, $sql);
        $res = mysql_query($sql);
    }
    if(!$res) { echo '<b>error in query </b>', mysql_error(), '<br /><br>Try importing the split .sql file in phpmyadmin under the SQL tab.'; /* $i = $i +1; */ } else {
    echo ("<meta http-equiv=\"Refresh\" content=\"0; URL=restore.php?page=done&file=$filename\"/>Thank You! You will be redirected");
    }   
 
} elseif($pos2 !== false){
    $splitted = explode("-- AUTO_INCREMENT for table", $importme);
    // print_r($splitted);
    for($i=0;$i<count($splitted);$i++){
        $sql = $splitted[$i];
        $sql = preg_replace("/[`][a-z`\s]*[-]{2}/", "", $sql);
        
        // echo "<b>$sql</b><hr>";
        if($table_prefix !== 'phpbb_') $sql = preg_replace('/phpbb_/', $table_prefix, $sql);
        $res = mysql_query($sql);
    }
    if(!$res) { echo '<b>error in query </b>', mysql_error(), '<br /><br>Try importing the split .sql file in phpmyadmin under the SQL tab.'; /* $i = $i +1; */ } else {
    echo ("<meta http-equiv=\"Refresh\" content=\"0; URL=restore.php?page=done&file=$filename\"/>Thank You! You will be redirected");
    }   
} else {
    if($table_prefix !== 'phpbb_') $sql = preg_replace('/phpbb_/', $table_prefix, $sql);
    $res = mysql_query($sql);
    if(!$res) { echo '<b>error in query </b>', mysql_error(), '<br /><br>Try importing the split .sql file in phpmyadmin under the SQL tab.'; /* $i = $i +1; */ } else {
    echo ("<meta http-equiv=\"Refresh\" content=\"0; URL=restore.php?page=done&file=$filename\"/>Thank You! You will be redirected");
    }


 }




//echo 'done (', count($sql), ' queries).';

}

User · Answer

navigate to C  wamp64 alias phpmyadmin conf and change from   php admin value upload max filesize 128M php admin value post max size 128M   to   php admin value upload max filesize 2048M php admin value post max size 2048M   or more

User · Answer

On a recent project we had the challenge of working with and manipulating a large collection of data    Our client provided us with a 50 CSV files ranging from 30 MB to 350 MB in size and all in all containing approximately 20 million rows of data and 15 columns of data   Our end goal was to import and manipulate the data into a MySQL relational database to be used to power a front-end PHP script that we also developed   Now  working with a dataset this large or larger is not the simplest of tasks and in working on it we wanted to take a moment to share some of the things you should consider and know when working with large datasets like this    Analyze Your Dataset Pre-Import  I can   t stress this first step enough   Make sure that you take the time to analyze the data you are working with before importing it at all   Getting an understand of what all of the data represents  what columns related to what and what type of manipulation you need to will end up saving you time in the long run  LOAD DATA INFILE is Your Friend  Importing large data files like the ones we worked with  and larger ones  can be tough to do if you go ahead and try a regular CSV insert via a tool like PHPMyAdmin   Not only will it fail in many cases because your server won   t be able to handle a file upload as large as some of your data files due to upload size restrictions and server timeouts  but even if it does succeed  the process could take hours depending our your hardware   The SQL function LOAD DATA INFILE was created to handle these large datasets and will significantly reduce the time it takes to handle the import process   Of note  this can be executed through PHPMyAdmin  but you may still have file upload issues   In that case you can upload the files manually to your server and then execute from PHPMyAdmin  see their manual for more info  or execute the command via your SSH console  assuming you have your own server   LOAD DATA INFILE   mylargefile csv  INTO TABLE temp data FIELDS TERMINATED BY     ENCLOSED BY     LINES TERMINATED BY   n   MYISAM vs InnoDB  Large or small database it   s always good to take a little time to consider which database engine you are going to use for your project   The two main engines you are going to read about are MYISAM and InnoDB and each has their own benefits and drawbacks   In brief the things to consider  in general  are as follows   MYISAM   Lower Memory Usage Allows for Full-Text Searching Table Level Locking     Locks Entire Table on Write Great for Read-Intensive Applications   InnoDB   List item Uses More Memory No Full-Text Search Support Faster Performance Row Level Locking     Locks Single Row on Write Great for Read Write Intensive Applications  Plan Your Design Carefully  MySQL AnalyzeYour databases design structure is going to be a large factor in how it performs   Take your time when it comes to planning out the different fields and analyze the data to figure out what the best field types  defaults and field length   You want to accommodate for the right amounts of data and try to avoid varchar columns and overly large data types when the data doesn   t warrant it   As an additional step after you are done with your database  you make want to see what MySQL suggests as field types for all of your different fields   You can do this by executing the following SQL command   ANALYZE TABLE my big table   The result will be a description of each columns information along with a recommendation for what type of datatype it should be along with a proper length   Now you don   t necessarily need to follow the recommendations as they are based solely on existing data  but it may help put you on the right track and get you thinking To Index or Not to Index  For a dataset as large as this it   s infinitely important to create proper indexes on your data based off of what you need to do with the data on the front-end  BUT if you plan to manipulate the data beforehand refrain from placing too many indexes on the data   Not only will it will make your SQL table larger  but it will also slow down certain operations like column additions  subtractions and additional indexing   With our dataset we needed to take the information we just imported and break it into several different tables to create a relational structure as well as take certain columns and split the information into additional columns   We placed an index on the bare minimum of columns that we knew would help us with the manipulation   All in all  we took 1 large table consisting of 20 million rows of data and split its information into 6 different tables with pieces of the main data in them along with newly created data based off the existing content   We did all of this by writing small PHP scripts to parse and move the data around  Finding a Balance  A big part of working with large databases from a programming perspective is speed and efficiency   Getting all of the data into your database is great  but if the script you write to access the data is slow  what   s the point   When working with large datasets it   s extremely important that you take the time to understand all of the queries that your script is performing and to create indexes to help those queries where possible   One such way to analyze what your queries are doing is by executing the following SQL command   EXPLAIN SELECT some field FROM my big table WHERE another field  MyCustomField     By adding EXPLAIN to the start of your query MySQL will spit out information describing what indexes it tried to use  did use and how it used them   I labeled this point    Finding a balance    because although indexes can help your script perform faster  they can just as easily make it run slower   You need to make sure you index what is needed and only what is needed   Every index consumes disk space and adds to the overhead of the table   Every time you make an edit to your table  you have to rebuild the index for that particular row and the more indexes you have on those rows  the longer it will take   It all comes down to making smart indexes  efficient SQL queries and most importantly benchmarking as you go to understand what each of your queries is doing and how long it   s taking to do it  Index On  Index Off  As we worked on the database and front-end script  both the client and us started to notice little things that needed changing and that required us to make changes to the database   Some of these changes involved adding removing columns and changing the column types   As we had already setup a number of indexes on the data  making any of these changes required the server to do some serious work to keep the indexes in place and handle any modifications   On our small VPS server  some of the changes were taking upwards of 6 hours to complete   certainly not helpful to us being able to do speedy development   The solution   Turn off indexes   Sometimes it   s better to turn the indexes off  make your changes and then turn the indexes back on    especially if you have a lot of different changes to make   With the indexes off  the changes took a matter of seconds to minutes versus hours   When we were happy with our changes we simply turned our indexes back on   This of course took quite some time to re-index everything  but it was at least able to re-index everything all at once  reducing the overall time needed to make these changes one by one   Here   s how to do it    Disable Indexes  ALTER TABLE my big table DISABLE KEY Enable Indexes  ALTER TABLE my big table ENABLE KEY  Give MySQL a Tune-Up  Don   t neglect your server when it comes to making your database and script run quickly   Your hardware needs just as much attention and tuning as your database and script does   In particular it   s important to look at your MySQL configuration file to see what changes you can make to better enhance its performance   A great little tool that we   ve come across is the MySQL Tuner http   mysqltuner com     It   s a quick little Perl script that you can download right to your server and run via SSH to see what changes you might want to make to your configuration   Note that you should actively use your front-end script and database for several days before running the tuner so that the tuner has data to analyze   Running it on a fresh server will only provide minimal information and tuning options   We found it great to use the tuner script every few days for the two weeks to see what recommendations it would come up with and at the end we had significantly increased the databases performance  Don   t be Afraid to Ask  Working with SQL can be challenging to begin with and working with extremely large datasets only makes it that much harder   Don   t be afraid to go to professionals who know what they are doing when it comes to large datasets   Ultimately you will end up with a superior product  quicker development and quicker front-end performance   When it comes to large databases sometimes it   s take a professionals experienced eyes to find all the little caveats that could be slowing your databases performance

User · Answer

Use source command to import large DB  mysql -u username -p   gt  source sqldbfile sql   this can import any large DB

User · Answer

I found below SSH commands are robust for export import huge MySql databases  at least I m using them for years  Never rely on backups generated via control panels like cPanel WHM  CWP  OVIPanel  etc they may trouble you especially when you re switching between control panels  trust SSH always   EXPORT    mysqldump -u root -p example database  gzip  gt  example database sql gz    IMPORT    gunzip  lt  example database sql gz   mysql -u root -p example database

User · Answer

Have you tried just using the mysql command line client directly   mysql -u username -p -h hostname databasename  lt  dump sql   If you can t do that  there are any number of utilities you can find by Googling that help you import a large dump into MySQL  like BigDump

User · Answer

according to mysql documentation none of these works  People pay attention  so we will upload test sql into the test db type this into the shell  mysql --user user name --password yourpassword test db  lt  d  test sql

User · Answer

I m posting my finding in a few of the responses I ve seen that didn t mention what I ran into  and apprently this would even defeat BigDump  so check it   I was trying to load a 500 meg dump via Linux command line and kept getting the  Mysql server has gone away  errors   Settings in my conf didn t help   What turned out to fix it is   I was doing one big extended insert like       insert into table  fields  values  a record  a record  a record  500 meg of data     I needed to format the file as separate inserts like this       insert into table  fields  values  a record       insert into table  fields  values  a record       insert into table  fields  values  a record       Etc    And to generate the dump  I used something like this and it worked like a charm       SELECT          id          status          email     FROM contacts     INTO OUTFILE   tmp contacts sql      FIELDS TERMINATED BY         OPTIONALLY ENCLOSED BY         LINES STARTING BY  INSERT INTO contacts  id status email  values        TERMINATED BY     n

User · Answer

I ve searched around  and only this solution helped me   mysql -u root -p  set global net buffer length 1000000  --Set network buffer length to a large byte number  set global max allowed packet 1000000000  --Set maximum allowed packet size to a large byte number  SET foreign key checks   0  --Disable foreign key checking to avoid delays errors and unwanted behaviour  source file sql --Import your sql dump file  SET foreign key checks   1  --Remember to enable foreign key checks when procedure is complete    The answer is found here

User · Answer

Simple solution is to run this query   mysql -h yourhostname -u username -p databasename  lt  yoursqlfile sql  And if you want to import with progress bar  try this   pv yoursqlfile sql   mysql -uxxx -pxxxx databasename

[mysql] How can I import a large (14 GB) MySQL dump file into a new MySQL database?

Examples related to mysql

Examples related to large-files