Inserting data in mysql with shell script

Question

I have a problem with a shell script:

I am trying to inject data from a source file containing MySQL queries into a data base. Here are the relevant lines of my shell script:

mysql -u root
source /usr/local/insert.sql;
quit;

For example I am running the file as ./insertfile and it is running smoothly but when it comes to data insertion in MySQL it is logging into MySQL using the mysql -u root command but the remaining operations (source /usr/local/insert.sql; and quit;) are not being executed. When I quit MySQL manually it tries to execute the rest of the command from my insert.sql file.

So please help me use the right shell script so that I can insert the queries from the source file.

do you have full rights on the file on operation system level - and make sure that you run 'USE DB_NAME;' before running it — Up_One
– Up_One, Commented Mar 28, 2014 at 19:49
ok ! go inside your mysql and run this commnad '\! head /usr/local/insert.sql' -- show me the output ! — Up_One
– Up_One, Commented Mar 28, 2014 at 20:59

zbrunson · Accepted Answer · 2014-03-28 19:49:10Z

1

One way to do that would be

mysql -u root --execute="source /usr/local/insert.sql; quit;"

answered Mar 28, 2014 at 19:49

zbrunson

1,7871 gold badge13 silver badges13 bronze badges

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

1 Comment

Shah9il Over a year ago

Dear zshooter sir, Thanks for your valuable comment but after adding this link in my script the following error is showing in mysql

ERROR 1064 (42000) at line 1: 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 'quit' at line 1

Please help me with that also

Up_One · Accepted Answer · 2014-03-28 21:12:55Z

0

It seems that your import hangs !
Check for lock on your database.

show processlist;

Run FLUSH TABLES to release any possible locks and then run your import command.
if source command hangs again :

Enter your myslq server
drop database insert;
create database insert
exit the mysql server and run:

mysqldump -u -p database-name < dump.sql

answered Mar 28, 2014 at 21:12

Up_One

5,2813 gold badges38 silver badges66 bronze badges

1 Comment

Shah9il Over a year ago

The issue is not solved after flusing and also after dropping the database and creating again

Shah9il · Accepted Answer · 2014-03-29 18:32:49Z

0

Thanks for your help. I have tried adding your line in my script and it was primarily giving some errors then I changed the command like below -

mysql -u root --execute="source /usr/local/insert.sql; \q"

Above line helped me to execute my command.

Thanks to all for being this much helpful.

Regards, Shah9il

answered Mar 29, 2014 at 18:32

Shah9il

611 silver badge8 bronze badges

Collectives™ on Stack Overflow

Inserting data in mysql with shell script

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related