How to execute large MySQL data-insert script file using command line in Windows 7?

Question

I'm new to MySQL. I am interested how to do this via command line; I've tried but I get NO logging in the console output and the character set is not utf-8.

Gonadotropin

Answer 1

Since your SQL script doesn't contain any character set configuration directives, mysql just runs in its default character set Latin1. To change the default character set, start mysql like this:

mysql -e "source /path-to-backup/backup-file.sql" db_name --default-character-set=UTF8

Sansom

Answer 2

mysql -e "source /path-to-backup/backup-file.sql" db_name

or

mysql db_name < backup-file.sql

You probably need -u username -p, too in your command.

mysqldump doc

Crore

I've tried mysql db_name < backup-file.sql but utf-8 data are lost – Gonadotropin

Did you try both, is the correct data in the SQL file to begin with? – Crore

Unicode? UTF-8? What's it going to be? Anyway, how does the sql file start? Files created with mysqldump contain all the relevant parameters to set the necessary encoding options, I believe; is your file a straight dump file? – Sansom

@Kerrek This is my sample script in utf8 db.tt/IyNbFAj. Have tried successful using Navicat execute sql filebut running from commandline failed – Gonadotropin

Ah, your script is missing any character set configuration commands... (take a look at the result of mysqldump to see what I mean). You could try passing --default-character-set=UTF8 to mysql maybe. – Sansom

@Kerrek Adding the --default-character-set=UTF8 to the command line solve it. Please add it as a separate answer so as I'll pick it up as accepted answer. Thanks a lot! – Gonadotropin

Answer 3

C:\<installation_path>>\bin>mysql -u<<user>> -p  database_name < your_sql_file.sql

could be used if you are getting access issues like ERROR 1045 (28000): Access denied for user 'ODBC'@'localhost' (using password: NO)

hope that helps !!

Fca