Executing MySQL Queries and Commands from the Command Line to a Remote Server

Following are a couple of ways to execute SQL on a remote MySQL server via the shell.

Executing SQL directly from the command line:

$ mysql -u uid -p -h remote.host database -e ‘SQL query here;’

Executing SQL from a file on the local host on the remote server:

$ mysql -u uid -p -h remote.host database < file.sql

You can also connect to the remote mysql server and from the command line execute SQL from files on the local drive as such (as long as the file is in the same directory from which the mysql connection is made

mysql> \. file.sql

Notice NO ‘;’ at the end of the command.

Moreover, you can use the following to run commands in your local shell

mysql> \! ls

Will give you the directly listing of the dir from which you invoked the mysql remote connection.

mysql > \! vi file.sql

Will allow you to edit the file on your local drive without having to close the mysql cli.

Leave a Reply