A page from my notebook that you might find handy.
The mysql
CLI is shipped with MySQL Server in the /bin folder.
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.
- 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.)