How to execute a MySQL command from a shell script

0 votes

How can I execute an SQL command through a shell script so that I can make it automated?

I want to restore data I have collected in a SQL file using a shell script. I want to connect to a server and restore data. The command works when executed separately via SSH command line.

This is the command I use:

mysql -h "server-name" -u root "password" "database-name" < "filename.sql"

This is the shell script code that creates the file ds_fbids.sql and pipes it into mysql.

perl fb_apps_frm_fb.pl
perl fb_new_spider.pl ds_fbids.txt ds_fbids.sql
mysql -h dbservername -u username -ppassword dbname < ds_fbids.sql

What is the correct way to do this?

Aug 20, 2020 in PHP by kartik
• 37,490 points
643 views

1 answer to this question.

+1 vote

Hello @kartik,

You need to use the -p flag to send a password. And it's tricky because you must have no space between -p and the password.

$ mysql -h "server-name" -u "root" "-pXXXXXXXX" "database-name" < "filename.sql"

If you use a space after -p it makes the mysql client prompt you interactively for the password, and then it interprets the next command argument as a database-name:

$ mysql -h "server-name" -u "root" -p "XXXXXXXX" "database-name" < "filename.sql"
Enter password: <you type it in here>
ERROR 1049 (42000): Unknown database 'XXXXXXXX'

Actually, I prefer to store the user and password in ~/.my.cnf so I don't have to put it on the command-line at all:

[client]
user = root
password = XXXXXXXX

Then:

$ mysql -h "server-name" "database-name" < "filename.sql"

Hope it helps!!

Thank you!!

answered Aug 20, 2020 by Niroj
• 82,680 points

Related Questions In PHP

0 votes
1 answer

How to delete a certain row from mysql table with same column values?

Hello @kartik, Add a limit to the delete query delete from orders where ...READ MORE

answered Aug 26, 2020 in PHP by Niroj
• 82,680 points
108 views
0 votes
1 answer

How to trigger XDebug profiler for a command line PHP script?

Hello @kartik, You can pass INI settings with ...READ MORE

answered Sep 17, 2020 in PHP by Niroj
• 82,680 points
287 views
0 votes
1 answer

How to execute PHP code from the command line?

Hello @kartik, the php command offers two switches to ...READ MORE

answered Oct 1, 2020 in PHP by Niroj
• 82,680 points
97 views
0 votes
1 answer

How to create and download a csv file from php script?

Hello @kartik, You can use the built in fputcsv() for ...READ MORE

answered Oct 27, 2020 in PHP by Niroj
• 82,680 points
1,099 views
+1 vote
1 answer

How to make anchor tag with routing using Laravel?

Hey @kartik, First you have to go to ...READ MORE

answered Mar 18, 2020 in Laravel by Niroj
• 82,680 points
6,707 views
0 votes
1 answer

What is redirection in Laravel?

Named route is used to give specific ...READ MORE

answered Mar 18, 2020 in Laravel by Niroj
• 82,680 points
780 views
0 votes
1 answer

How to install Laravel via composer?

Hello, This is simple you just need to ...READ MORE

answered Mar 23, 2020 in Laravel by Niroj
• 82,680 points
583 views
+1 vote
1 answer

What are named routes in Laravel and How can specify route names for controller actions?

Hey @kartik, Named routing is another amazing feature of ...READ MORE

answered Mar 23, 2020 in Laravel by Niroj
• 82,680 points
7,129 views
0 votes
1 answer

How to create a database from shell command?

Hello @kartik, Try: cat filename.sql | mysql -u username ...READ MORE

answered Aug 26, 2020 in PHP by Niroj
• 82,680 points
60 views
0 votes
1 answer

How to get a list of user accounts using the command line in MySQL?

Hello @kartik, Use this query: SELECT User FROM mysql.user; Which ...READ MORE

answered Aug 17, 2020 in PHP by Niroj
• 82,680 points
129 views