Run a MySQL script file in Linux

We commonly use scripts to automate and ease our work. MySQL provides a feature to create schema structure using scripts.

A MySQL script file(.sql) is as good as a normal text file.Just put your queries in a text file and your script is ready to run.In this post i explain the procedure to run a mysql script through the command line as well as mysql shell

The script file

I created my script using vi editor in Linux(you may use any text editor :) )
Here is my script file

I placed my script file in the root directory.The location of the script is important.Why?? We will get to know it soon.

 

Executing the script

We can execute our script in two ways.

• From the Linux/Unix command line with the help of the redirection operator(<)
• From the mysql shell. 

 To do it the first way,from the command-line execute this command
  mysql -u -p < script  

Note: Execute this command from the location where you placed your script.

Replace the with the mysql username and with the password you use to login into mysql.For instance,if the username is root and password is xyz the script filename is scpt the command would be 
mysql -u root -pxyz < scpt

< is the redirection operator in Unix/linux.

If the command execution is successful,you won't get any confirmation message.The control returns to the next line of the shell prompt.

Now to confirm the changes made by the script,login to mysql and use the show databases and show tables command.

Below is an illustration of this method.

             
To execute your script from the mysql shell.Follow these steps

1. Login to mysql with root privileges(Note:Login into mysql from the same directory where you placed your script)     

   2.  Once in the mysql shell,enter this command to execute your script
         source scriptname
        
Replace scriptname with the script you earlier created and want to execute. 

   3.  Once the mysql shell finds your script file,it will execute it and the results can be instantly seen on your screen.

   

   

 




























Now to confirm the changes made by the script, use the show databases and show tables command.

In case the mysql is unable to locate the file,it will throw up an error something like this