<< Blog Index

Import MySQL Dump
June 12, 2021

A page from my notebook that you might find handy.

The mysql CLI is shipped with MySQL Server in the /bin folder.

ONENOTE_Ea2L1PbD5i.png

Once connected:

CREATE DATABASE new_database;
USE new_database;
SOURCE C:\path\to\file.sql

A source path with spaces doesn't need to be a quoted string.

Alternatively (and I don't really use this so IDK):

> mysql -u username -p database_name < file.sql

You will need to create the database_name first if it doesn't exist and the SQL dump doesn't have a CREATE DATABASE (such is the case when exported with --no-create-db).

For LARGE databases, you might want to have a look at Optimizing InnoDB Bulk Data Loading. Have a look in your SQL dump file to see what optimizations are already in place (it might already turn off unique checks for example).

Handy tips

  • If your database dump is particularly huge, the Less tool is useful for viewing gigantic files.
  • If you want to import (or re-import) a particular table only, you can grep for "LOCK TABLES" and then CUT a slice from the import.
    • For example, here is cutting out the "cases" table, including an extra 1000 chars or so on each end. Then you can clean up the smaller file manually. Working on a 100gb database file directly is otherwise impossible.

chrome_FY0yVWimWE.png

  • Also, watch out for versioning issues. A database exported with a different version of MySQL might not import properly. (If you hear console beeps during the import, that's likely why.)

<< Blog Index