Tuesday, September 29, 2020

MySql Export / Import Large Database


In this blog I am going to explain a trick which I recently used for Export / Import large database. 

Let me first explain the issue I faced. The database was over 250 MB. I was using MySql WorkBench to export in single self contained file. But it was breaking in between as some tables were really huge. 

So here is what I did. First in MySql WorkBench, in stead of exporting it to self contained single file, export it to Dump project folder. Here it will create sql file for each table in the folder. So in my case it created approximately 180 sql files. 

Now open terminal and go to that folder. First step here is we will concat all those files and make a single file.

Run the following command in terminal from folder where you have all the sql files.

cat *.sql  > .single_file.sql

Above command will concat all the files into one file and create that file in same folder. Now we will use this file to import into our database.

/Applications/MAMP/Library/bin/mysql -u DB_USER -p DB < .single_file.sql

That's it. With this trick you can Import / Export large database. Please note that, for this you should have terminal access to your MySql server.

No comments:

Post a Comment