Batch file to connect mysql and run commands

Question

I want to run a batch file from windows, which connect to mysql server on different machine, and run a procedure from database or run a sql file which is sitting in my local machine.

is there's a way to do it. I know that I need the below script in my batch file to run sql commands but I believe it's only work when you run the batch file in mysql server enviornment.

do I have to define the server info (e.g IP address & port) how do I do that

any help would be appricated

Thanks

mysql --user=XXX --password=XXXX --database=XXX < XXX.sql

Konerak · Accepted Answer · 2011-03-23 08:59:56Z

8

if your MySQL Server (mysqld) is running on the same host as your MySQL client application (mysql), your command

mysql --user=XXX --password=XXXX --database=XXX < XXX.sql

works.

If your server is on another host (as in your case), you have to add the hostname:

mysql --host=IP.ADDR.HERE --port=3306 --user=XXX --password=XXXX --database=XXX < XXX.sql

The XXX.sql file is on the same host as your MySQL Client.

Offcourse your server has to accept connections from other hosts (bind-address defined, no skip-networking, and the correct user@host privileges defined) so check your server configuration.

answered Mar 23, 2011 at 8:59

Konerak

39.8k13 gold badges102 silver badges121 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

MRQ Over a year ago

do I have to specify the path of .sql file, sql file sits in the same windows folder where the batch file is and server is on another host.

Konerak Over a year ago

Yeah, you have to specify the path. If it is in the same folder, and your batch file is called from that folder (or the start in parameter is set correctly, which it is by default), it will work. If not, you can add the complete path C:\Windows\XXX.SQL or use %WINDIR%\XXX.SQL.

MRQ Over a year ago

Hi Konerak, this is what is in batch file mysql --host=XXX.XXX.XXX.XX --port=3306 --user=XXX --password=XXX --database=XXX < C:\Batchfile\XXX.sql but its not working, do I have to write something else in the batch file, because my batch file conatin the above line and that's it I have tried putting qouation marks with path but didn't work

Konerak Over a year ago

Does it work without a batchfile? If you just open a command prompt and use the mysql ... , can you connect? What happens when you do mysql ... <XXX.sql, does that work? We need more info :)

MRQ Over a year ago

'mysql' is not recognize as an internal or external command,operable program or batch file

|

street hawk · Accepted Answer · 2015-06-22 06:59:55Z

1

This way the sql statement can be given inside the batch file.

mysql --host=ipaddress --port=3306 -u root -ppassword dbname -e "insert into emp ('id', 'name') values (1, 'hawk')"

edited Jun 22, 2015 at 6:59

answered Jun 22, 2015 at 3:44

street hawk

6251 gold badge8 silver badges12 bronze badges

Comments

Devart · Accepted Answer · 2011-03-23 08:58:33Z

0

do I have to define the server info (e.g IP address & port) how do I do that

You need to set port if it is not 3306. You need to set host if you want to specify user you want to connect, as you know MySQL user has a name and host - 'user1'@'host_name'.

See the manual - The MySQL Command-Line Tool

answered Mar 23, 2011 at 8:58

Devart

120k23 gold badges172 silver badges189 bronze badges

Collectives™ on Stack Overflow

Batch file to connect mysql and run commands

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related