Can I import a mysql dump to a laravel migration?

Laravel can't do that, but I think this will help: Laravel migration generator

It generate migrations based on existing tables.


This question is answered already, but recently in a project, the provided answers did not satisfy my needs any longer. It also does not import a whole database dump, but one (large) table. I felt I should share that with you.

The problem was, I wanted to import a quite large table (list of zipcodes) during my artisan:migrate operation. The solution with DB::unprepared($dump) took way to long and I found an alternative which is MUCH faster.

Just export your table as CSV and use the following Code in your migration's up() function.

    // i had to str_replace the backslash on windows dev system... but works on linux, too
    $filename = str_replace("\\", "/", storage_path('path/in/storage/to/your/file.csv'));

    $query = "LOAD DATA LOCAL INFILE '".$filename."' INTO TABLE yourtable
        FIELDS TERMINATED BY '\t'
        ENCLOSED BY ''
        LINES TERMINATED BY '\n'
        IGNORE 0 LINES
        (col1,col2,...);";

    DB::unprepared($query);

Just update the query as you need. And of course, you should make sure, that the table with the cols 'col1', 'col2' etc... exists. I created it just before the importing of the file. with Schema::create()...

If you run into following error message:

PDO::exec(): LOAD DATA LOCAL INFILE forbidden

There is a way you can get rid of this message: Although it's not really documented you can just add an 'options' key to your config/database.php file. For example mine looks like that:

        'mysql' => [
        'driver'    => 'mysql',
        'host'      => env('DB_HOST', 'localhost'),
        'database'  => env('DB_DATABASE', 'forge'),
        'username'  => env('DB_USERNAME', 'forge'),
        'password'  => env('DB_PASSWORD', ''),
        'charset'   => 'utf8',
        'collation' => 'utf8_unicode_ci',
        'prefix'    => '',
        'strict'    => false,
        'options'   => array(
            PDO::MYSQL_ATTR_LOCAL_INFILE => true,
        )

Note: i'm currently using laravel 5 but it should work with laravel 4, too.


I have a complete database and need to create migration. I guess there must be a way to do it from a dump but not sure. Is there any way automatically or at least easier to do this task?

Not automatically, but we run dumps in a migration using DB::unprepared(). You could use file_get_contents to import from a .sql file and thus not have to worry about escaping the entire dump's " marks...

<?php

use Illuminate\Database\Migrations\Migration;

class ImportDump extends Migration {

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        DB::unprepared("YOUR SQL DUMP HERE");
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {

    }

}

You can import dumps in Laravel like this:

DB::unprepared(file_get_contents('full/path/to/dump.sql'));

If I were to refactor an existing app, though, I'd take the time to write migrations from scratch, import the dump into different tables (or a different db, if table names are the same) then import the content to the new structure via seeds.

Tags:

Laravel