How to run mysql command from terminal?

Question

I have the following sql command which I usually run in phpmyadmin by selecting the database, then running it in the command window.

DELETE FROM `wp_posts` WHERE `post_type` = "attachment"

However I've never done this via terminal before. My question is, how do I "point" this command to a specific database and then run it?

How can you run any command without login to MySQL? First in terminal you have to login using your MySQL username and password Eg: mysql -uroot -p then once loged in select your data base using use <yourdatabasename then you can run this command. — Saurav Kumar, Sep 05 '13 at 06:41
You can also put the database in front of the table like so: DELETE FROM {databasename}.wp_posts WHEREpost_type= "attachment" — Rinzwind, Sep 05 '13 at 07:24
See also related question: https://askubuntu.com/q/1077725/295286 — Sergiy Kolodyazhnyy, Nov 13 '18 at 17:36

score 44 · Answer 1 · answered Sep 05 '13 at 07:01

44

For you to run it from terminal, you need to be logged into mysql first and then you execute your program. The ideal code would be

mysql -u(username) -p(password) (the name of the database) -e "DELETE FROM `wp_posts` WHERE `post_type` = "attachment""

I`m sure this works, hope it helps

answered Sep 05 '13 at 07:01

sosytee

2,698
8
34
45

3

You need to escape the backticks using \ if executing from a normal terminal – myol Mar 14 '16 at 17:31
How about e.g. dropping a database and creating it again? – Jeppe Apr 13 '20 at 08:17

score 19 · Answer 2 · edited Aug 12 '15 at 18:36

19

I think you have forgot your user name for mySQL. so please enter the correct user name and password when you type mysql command. for eg

$ mysql   -u  root   -p 
Enter Password:

Note: Default user name and password is root

edited Aug 12 '15 at 18:36

muru

197,895
55
485
740

answered Nov 12 '14 at 10:52

Ashish Saini

541

1

Default root's access need sudo, sudo mysql -u root -p – Peter Krauss Oct 31 '17 at 12:26
Default root's access need sudo, sudo mysql -u root -p – Peter Krauss Oct 31 '17 at 12:27

score 10 · Answer 3 · edited Aug 12 '15 at 17:56

10

Just type the following command in terminal to use mysql interpreter:

mysql -u root -p database_name

Enter your password, then you can run your mysql commands.

Another way is to use:

mysql -u root -p database_name << eof
DELETE FROM `wp_posts` WHERE `post_type` = "attachment"
eof

edited Aug 12 '15 at 17:56

Pierre-Olivier Vares

113

answered Sep 05 '13 at 07:06

Radu Rădeanu

169,590

score 7 · Answer 4 · edited Jul 07 '16 at 09:37

7

Try this:

mysql --host *HOST_IP* --user *USER* --password=*PASSWORD* -D *DATABASE_NAME* -e "DELETE FROM `wp_posts` WHERE `post_type` = "attachment" ;

edited Jul 07 '16 at 09:37

Bruni

10,542

answered Jul 07 '16 at 08:05

tej18

83

score 2 · Answer 5 · answered Jul 07 '16 at 08:29

Using the MySQL CLI client.

$ mysql -u <user> -p
mysql> use <your-database>
mysql> DELETE FROM `wp_posts` WHERE `post_type` = "attachment"

Notice that I'm using -u and username, but in -p I'm not setting anything. MySQL will ask you for your password.

And if you're trying to use it inline, you can use

$ mysql -u <user> -p <your-database> -e "DELETE FROM `wp_posts` WHERE `post_type` = 'attachment'"

Here, notice that I'm using also -e argument. It stands for execute.

Pro tip

In MySQL client you can use:

mysql> show databases;

if you want to view all dabatases in your server. And you can use:

mysql> use <database>;
mysql> show tables;

if you want to view all tables into a database.

I'm using $ and mysql> as prompts, $ means your Linux terminal, and mysql>, MySQL client.

Parag Vidhate · Answer 6 · 2016-01-13T10:54:53.657

There is a series of steps to Execute MySQL in Linux Ubuntu Terminal.

Execute MySQL Client using the following command: mysql -u root -p
It is important to Create a New Database first using the command: create database demo_db;
Then you have to Authorize the Database using the command: grant all on demo_db.* to ‘testuser’ identified by ‘12345’;
Then Log in from the Database itself using the command: mysql -u testuser -p
Then, you can actually start with the actual SQL Commands.

I referred to the article http://www.codingalpha.com/run-mysql-database-linux-ubuntu/ where they have explained perfect steps to Execute MySQL in Linux Ubuntu! Hope I got your MySQL Running Perfectly.

Please note that mysql is the client, and not the server. Before starting the client, check that the server is running by issuing sudo status mysql and if it is not running, start it with sudo start mysql. — Guss, Dec 31 '15 at 20:37

score -2 · Answer 7 · answered Sep 05 '13 at 06:48

-2

Okay basically it was "SELECT databasename;" then executing my command.

answered Sep 05 '13 at 06:48

jc.yin

531
2
7
13

1

You can also specify the database name on the command line, such as mysql -u root -p databasename - enter password when prompted and you are using the database. – David Purdue Sep 05 '13 at 06:52

How to run mysql command from terminal?

7 Answers7

Linked