How Can I Execute an SQL File Correctly Within MySQL in Windows 10 Command Prompt?

Question

I have been trying almost thirty minutes trying to figure out how to execute an sql file from a Win 10 command prompt and I still can't figure out how to do it.

I am in the mysql> directory and I can't figure out what the 'source' is before the path to the file. What is the 'source' that I need to type in before the path to the MySql file?

Here is my file path to the bookorama.sql file that I am trying to execute: C:\wamp64\bin\mysql\mysql5.7.23\bin

The bookorama.sql is within the file path at the last 'bin' folder.

Here's what I have tried to find so far, but I keep on trying a lot of the suggestions for the answers in these posts, but I still can't get it right.

Can't run MySQL from command prompt

Executing a SQL file

How to import an SQL file using the command line in MySQL?

execute .sql file using command line

https://tecadmin.net/run-sql-text-file-on-mysql-command-prompt/

Here's a copy of the command prompt tries to execute the sql file:

C:\wamp64\bin\mysql\mysql5.7.23\bin>mysql -hlocalhost -uroot -p
Enter password: *********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 5.7.23 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input 

mysql> use books
Database changed
mysql> -u bookorama -d books < bookoroma.sql
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual 
that corresponds to your MySQL server version for the right syntax to use 
near '-u bookorama -d books < bookoroma.sql' at line 1
mysql> mysql
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual 
that corresponds to your MySQL server version for the right syntax to use 
near 'mysql' at line 1
mysql> mysql -h localhost - u bookorama -p < bookorama.sql
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual 
that corresponds to your MySQL server version for the right syntax to use 
near 'mysql -h localhost - u bookorama -p < bookorama.sql' at line 1
mysql> cd..
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual 
that corresponds to your MySQL server version for the right syntax to use 
near 'cd..' at line 1
mysql> mysql -h localhost -u bookorama -D books -p < bookorama.sql
    ->
    -> CREATE TABLE Customers
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual 
that corresponds to your MySQL server version for the right syntax to use 
near 'mysql -h localhost -u bookorama -D books -p < bookorama.sql

CREATE TABLE Custom' at line 1
mysql> -u bookorama -D books -P < bookorama.sql
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual 
that corresponds to your MySQL server version for the right syntax to use 
near '-u bookorama -D books -P < bookorama.sql' at line 1mysql> -u root
    -> -p Oscar545* -D bookorama < bookorama.sql
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual 
that corresponds to your MySQL server version for the right syntax to use 
near '-u root
-p Oscar545* -D bookorama < bookorama.sql' at line 1
mysql> mysql -h localhose u-root -D bookorama < 
C:\wamp64\bin\mysql\mysql5.7.23\bin.bookorama.sql
Show warnings disabled.
ERROR:
Unknown command '\b'.
ERROR:
Unknown command '\m'.
ERROR:
Unknown command '\m'.
ERROR:
Unknown command '\b'.
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual 
that corresponds to your MySQL server version for the right syntax to use 
near 'mysql -h localhose u-root -D bookorama < 
C:amp64\bin\mysql\mysql5.7.23\bin.booko' at line 1
mysql> mysql -h localhose u-root -D bookorama < bookorama.sql
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual 
that corresponds to your MySQL server version for the right syntax to use 
near 'mysql -h localhose u-root -D bookorama < bookorama.sql' at line 1
mysql> mysql -u root -p books < bookorama.sql
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual 
that corresponds to your MySQL server version for the right syntax to use 
near 'mysql -u root -p books < bookorama.sql' at line 1
mysql> use books
Database changed
mysql> mysql -u root -p books < bookorama.sql
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual 
that corresponds to your MySQL server version for the right syntax to use 
near 'mysql -u root -p books < bookorama.sql' at line 1
mysql> C:\wamp64\bin\mysql\mysql5.7.23\bin bookorama.sql
Show warnings disabled.ERROR:
Unknown command '\b'.
ERROR:
Unknown command '\m'.
ERROR:
Unknown command '\m'.
ERROR:
Unknown command '\b'.
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual 
that corresponds to your MySQL server version for the right syntax to use 
near 'C:amp64\bin\mysql\mysql5.7.23\bin bookorama.sql' at line 1
mysql> source C:\wamp64\bin\mysql\mysql5.7.23\bin;
Show warnings disabled.
ERROR:
Unknown command '\b'.
ERROR:
Unknown command '\m'.
ERROR:
Unknown command '\m'.
ERROR:
Unknown command '\b'.
ERROR:
Failed to open file 'C:amp64\bin\mysql\mysql5.7.23\bin', error: 2
mysql> C:\wamp64\bin\mysql\mysql5.7.23\bin\bookorama.sql
Show warnings disabled.
ERROR:
Unknown command '\b'.
ERROR:
Unknown command '\m'.
ERROR:
Unknown command '\m'.
ERROR:
Unknown command '\b'.
ERROR:
Unknown command '\b'.
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual 
that corresponds to your MySQL server version for the right syntax to use 
near 'C:amp64\bin\mysql\mysql5.7.23\bin\bookorama.sql' at line 1
mysql> mysql -h localhost -u root
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual 
that corresponds to your MySQL server version for the right syntax to use 
near 'mysql -h localhost -u root' at line 1
mysql> -h localhost -root -pOscar545* books < bookorama.sql
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual 
that corresponds to your MySQL server version for the right syntax to use 
near '-h localhost -root -pOscar545* books < bookorama.sql' at line 1
mysql> mysql -u root -p books < bookorama.sql
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual 
that corresponds to your MySQL server version for the right syntax to use 
near 'mysql -u root -p books < bookorama.sql' at line 1
mysql>

shawn · Answer 1 · 2018-12-25T20:35:29.133

3

At the mysql> prompt you'd use:

use books;
source bookorama.sql;

At the command prompt you'd use:

mysql.exe -h localhost -root -pOscar545* books < bookorama.sql

edited Dec 25 '18 at 20:35

answered Dec 25 '18 at 18:50

shawn

383
2
8

sorry, yes, `source`. mixing languages. :) – shawn Dec 25 '18 at 20:35

score 0 · Answer 2 · answered Dec 25 '18 at 21:15

The MySQL CLI (command line interface) like other CLIs or shell programs including databases (Postgres' psql, SQLite's sqlite3, SQL Server's sqlcmd, Oracle's sqlplus), langauges (python.exe, perl.exe, php.exe, r.exe), and others are executables (.exe) that do not launch with a GUI like most software .exes but evokes a session inside a terminal window.

Command line interpreters such as Windows' PowerShell and Cmd.exe, or Unix's (MacOS/Linux) Bash terminal are used to start such CLI sessions. Essentially, there are two ways to run MySQL commands:

Call mysql.exe with arguments without opening prolonged session.
Launch a mysql session and run separate calls.

Your problem is the conflation of the above two where you are attempting a command line call of mysql.exe with arguments inside mysql.exe or running its top level arguments inside a session. Inside the CLI, the parser only interprets special MySQL commands like source and direct SQL queries not executable, mysql, calls or connection arguments (-h, -u) without its shell command caller: \!.

Simply use appropriate calls outside a CLI session:

cd C:\amp64\bin\mysql\mysql5.7.23\bin
mysql -h localhost -u bookorama -p < bookorama.sql

Or inside a CLI session:

cd C:\amp64\bin\mysql\mysql5.7.23\bin
mysql -h localhost -u bookorama -p
mysql> source bookorama.sql

With that said, you can do the opposite.

Running command queries outside:

mysql -h localhost -u bookorama -p -e "source bookorama.sql"

Calling terminal calls inside:

mysql> \! mysql -h localhost -u bookorama -pXXXXX < bookorama.sql

How Can I Execute an SQL File Correctly Within MySQL in Windows 10 Command Prompt?

2 Answers2

Linked

Related