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.