Views: 212

We will use Command-Line Tools (Shell) to import large database SQL file.

1. Open the XAMPP Control Panel

Make sure Apache and MySQL are running and click the Shell button in the XAMPP Control Panel.

2. To import database, use the following command

mysql -u username -p database_name < "/path/file.sql"

Where:

• username – your MySQL username. e.g. root
• database_name – database name you are importing to
• /path/file.sql – full path to your .sql file

when you haven’t created a MySQL username. In that case, you need to type “root” for the username.

Note: On the below screen command, you will define own define own database name. The database name will be the one that you created in the phpMyAdmin and would like to import to SQL file. In our case, we have database name “the_code_developer”.

Enter password: (leave it blank, it’s not set by default) and click enter to continue process.

3. If you want to see import process to screen then use the following command

mysql -u username -p -v database_name < "/path/file.sql"

Once your database SQL file successfully import then you will get following response like in the below image.

Source : The Code Developer

Skip Existing Data While Restoring mysqldump File?

SUGGESTION #1 :
Recreate dump with INSERT IGNORE

Use –insert-ignore

Write INSERT IGNORE statements rather than INSERT statements.

What happens on reload ???

• New data gets inserted.
• Already existing primary keys are skipped

SUGGESTION #2 :
Change INSERT to INSERT IGNORE on reload

Instead of loading normally

mysql -u root -p database_name < database_name.sql

just pipe the dump into sed and reload like this:

cat database_name.sql|sed 's/^INSERT/INSERT IGNORE/'|mysql -u root -p database_name

SUGGESTION #3 :
Recreate dump with REPLACE INTO

Use –replace

Write REPLACE statements rather than INSERT statements.

What happens on reload ???

• New data gets inserted.
• Already existing primary keys will be deleted and re-inserted

SUGGESTION #4 :
Change INSERT INTO to REPLACE INTO on reload

Instead of loading normally

mysql -u root -p database_name < database_name.sql

just pipe the dump into sed and reload like this:

cat database_name.sql|sed 's/^INSERT INTO/REPLACE INTO/'|mysql -u root -p database_name

SUMMARY

• Use suggestions 1 or 3 if you can recreate the dump
• Use suggestions 2 or 4 if you cannot recreate the dump

Good answer. Just one thing: cat is to conCATenate multiple files, you don’t need it to pipe a single file to a command perfectly able to take a single file as input. sed 's/^INSERT/INSERT IGNORE/' database_name.sql | mysql -u root -p database_name will do the trick just fine – Ilario

Source : DBA.Stack Exchange