Mysql on linux terminal

How to run mysql command from terminal?

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

You can also put the database in front of the table like so: DELETE FROM .wp_posts WHERE post_type` = «attachment»`

7 Answers 7

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

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

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.

mysql -u root -p database_name 
mysql --host *HOST_IP* --user *USER* --password=*PASSWORD* -D *DATABASE_NAME* -e "DELETE FROM `wp_posts` WHERE `post_type` = "attachment" ; 
 
Using the MySQL CLI client.
 
$ mysql -u -p mysql> use 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 -p -e "DELETE FROM `wp_posts` WHERE `post_type` = 'attachment'" 
 
Here, notice that I'm using also -e argument. It stands for execute.
 
In MySQL client you can use:
 
if you want to view all dabatases in your server. And you can use:
 
mysql> use ; 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.
 
Источник
 
MySQL Database Commands Cheat Sheet for Linux
 
Both MySQL and MariaDB are attributed as open-source relational database management systems (RDBMS). Since MySQL is broken down to either community or enterprise release.
 
MariaDB became a drop-in replacement to parade all the structured query language (SQL) features offered by MySQL but at an open-source cost.
 
So whether you are using MySQL Enterprise Edition, MySQL Community Edition, or MariaDB, this article is for you. By the end of your read, you should be comfortable with the use of the powerful structured query language mimicked by these RDBMS.
 
Install MySQL Server in Linux
 
MySQL or MariaDB is available in the official repository and can be installed using the package manager as shown.
 
----------- MySQL ----------- $ sudo apt install mysql-server [On Debian, Ubuntu and Mint] $ sudo apt install mysql-server [On RHEL/CentOS/Fedora and Rocky Linux/AlmaLinux] ----------- MariaDB ----------- $ sudo apt install mariadb-server [On Debian, Ubuntu and Mint] $ sudo dnf install mariadb-server [On RHEL/CentOS/Fedora and Rocky Linux/AlmaLinux]
 
MySQL Pre-Configuration Setup
 
Provided that you have a MariaDB or MySQL installation, the first step is always to start, enable, and check on the status of your MySQL. Enabling your MySQL ensures that it is always running even after successfully restarting your Linux system.
 
To start MySQL, run the command:
 
$ sudo systemctl start mysql/mysqld OR $ sudo systemctl start mariadb
 
To enable MySQL, run the command:
 
$ sudo systemctl enable mysql/mysqld OR $ sudo systemctl enable mariadb
 
To check on MySQL status, run the command:
 
$ sudo systemctl status mysql/mysqld OR $ sudo systemctl status mariadb
 
 
Now that we are certain MySQL is up and running, it is time to configure a few things. For a fresh MySQL/MariaDB installation, you might want to run the following script to secure MySQL installation:
 
$ sudo mysql_secure_installation
 
 
You should then proceed with the prompted options to appropriately configure your MySQL installation.
 
MySQL Database Management Commands
 
To connect to your MySQL database, adhere to the following syntax rule.
 
First, time database access is via the root user as demonstrated below.
 
 
Now that we are inside the MySQL database shell, we can execute several SQL queries.
 
Create a New MySQL User
 
To create a new MySQL user, run the following SQL command.
 
MariaDB [(none)]> CREATE USER 'your_user'@'localhost' IDENTIFIED BY 'your_user_password';
 
To grant this user the same privileges as the root user, we will execute:
 
MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'your_user'@'localhost'; MariaDB [(none)]> EXIT;
 
To connect to the DB console with this user, first quit MySQL and Re-enter with the new user credentials.
 
 
Working with MySQL Databases
 
To show all active MySQL databases, run the command:
 
MariaDB [(none)]> SHOW DATABASES;
 
To create a new MySQL database, run:
 
MariaDB [(none)]> CREATE DATABASE IF NOT EXISTS database_name;
 
To delete a MySQL database you are no longer using, run:
 
MariaDB [(none)]> DROP DATABASE IF EXISTS database_name;
 
 
To switch to a specific MySQL database, run:
 
MariaDB [(none)]> USE database_name;
 
Working with MySQL Database Tables
 
Provided that you are connected to a specific database, you can list the MySQL tables associated with the database with the following command:
 
MariaDB [(none)]> USE database_name; MariaDB [(none)]> SHOW TABLES;
 
 
To create a new database table, you also need to provide its column definitions as demonstrated below.
 
CREATE TABLE IF NOT EXISTS table_name( column_list );
 
 
To delete an existing database table, refer to the following command syntax:
 
MariaDB [(none)]> DROP TABLE IF EXISTS table_name;
 
To add a new column to an existing database table, implement the command syntax:
 
MariaDB [(none)]> ALTER TABLE table_name ADD COLUMN column_name;
 
To delete a database table column, implement:
 
MariaDB [(none)]> ALTER TABLE table_name DROP COLUMN column_name;
 
If your database table needs a primary key, you can include one with the command:
 
MariaDB [(none)]> ALTER TABLE table_name ADD PRIMARY KEY (column_name);
 
 
To remove the primary key assigned to a database table, run:
 
MariaDB [(none)]> ALTER TABLE table_name DROP PRIMARY KEY;
 
To view the columns associated with a specific database table, run:
 
MariaDB [(none)]> DESCRIBE table_name;
 
 
To view specific column information, run:
 
MariaDB [(none)]> DESCRIBE table_name table_column;
 
 
Querying MySQL Database Data
 
Once you have identified the various tables under your database, you can perform several interesting queries.
 
To query all the data associated with a specific database table, run:
 
MariaDB [(none)]> SELECT * FROM table_name;
 
 
To query one or more table column data, run:
 
MariaDB [(none)]> SELECT column_name1, column_name2, column_name3 FROM table_name;
 
 
If your database table query has duplicate rows, you can exclude them with the command:
 
MariaDB [(none)]> SELECT DISTINCT COLUMN FROM table_name;
 
To insert a new database table record:
 
MariaDB [(none)]> INSERT INTO table_name(column_list) VALUES(value_list);
 
 
To Update existing database table record:
 
MariaDB [(none)]> Update table_name SET column1=value1;
 
 
Database Backup and Restore
 
Here, we have to acknowledge the use of the mysqldump command-line client program. The first step is to identify the databases and database tables you wish to backup or restore.
 
MariaDB [(none)]> show databases; MariaDB [(none)]> show tables;
 
To back up a single MySQL database, reference the syntax:
 
$ sudo mysqldump -u [db_username] -p[db_password] [database_name] > [generated_db_backup.sql]
 
The above command syntax implementation will be:
 
 
If you were dealing with more than one database, the command will look like the following:
 
$ sudo mysqldump -u root [email protected] --databases demodb mysql > demodb_mysql.sql
 
To back up all MySQL databases:
 
$ sudo mysqldump -u root [email protected] --all-databases > all_databases.sql
 
To back up a single database table called tasks:
 
$ sudo mysqldump -u root [email protected] demodb tasks > demodb_tasks.sql
 
To back up multiple database tables (user and host):
 
$ sudo mysqldump -u root [email protected] mysql user host > mysql_user+host.sql
 
To restore a MySQL database or database table(s), use the mysql command instead of the mysqldump command and switch the > symbol with the < symbol.
 
For instance, to restore a single MySQL database called demodb, implement the command:
 
To backup your database data from a remote server to a local machine, you need to know the IP address of the remote server hosting MySQL. For instance, to locally back up a single database on a remote server, the command to implement should be as follows:
 
$ sudo mysqldump -h 192.168.84.130 -u remote_user -p dbdemo > dbdemo.sql
 
You will be prompted for the database user password for the backup process to begin. Ensure that you have allowed remote access to the MySQL server.
 
 
 
Some useful MySQL related articles to read:
 
With this brief article, you should comfortably implement the needed MySQL commands to keep your database-driven projects going.
 
Источник
 
MySQL Commands Tutorial
 
 
MySQL is a database that is behind many applications nowadays. This relational database can be accessed from the terminal and helps dev-ops engineers and system administrators. In this article, we will try out some of the most common MySQL commands in the Linux terminal.
 
Connect to MySQL
 
If you have already installed MySQL on your Ubuntu machine, you can easily connect to the MySQL shell by issuing the following command in your Linux terminal. 
[cc lang=”bash” width=”100%” height=”100%” escaped=”true” theme=”blackboard” nowrap=”0″] 
$ sudo mysql -u root -p 
[/cce_bash] 
In the above command:
 
After running the above command, you will be asked to enter the password. Once you have entered the root user password, you will be logged in to the MySQL shell.
 
If you have recently installed MySQL, then you will not be asked for the password.
 
Set or Change Password
 
To set or change the password for any MySQL database user, issue the following command:
 
Replace the user_name and new_password with your username and desired password. For example:
 
 
View Databases
 
In MySQL, you can view the list of databases under the user’s granted privileges by running the simple command stated below:
 
The above command will list all the databases.
 
 
Create a Database
 
You can create a database by issuing the following command in the MySQL shell:
 
The above statement will not create a database if there already exists a database with the name that has been provided. For example:
 
 
As you can see in the screenshot, the db1 database has been created.
 
Select a Database
 
You can use any of the listed databases by running the following command in the MySQL shell:
 
 
Create a Table
 
A table can be created by using the CRETE TABLE command and defining the column names and their types in the parentheses, as follows:
 
 
tb1 is the name of the table
 
col1, col2 are the names of the columns in the tb1 table
 
INT and VARCHAR are the datatypes of the specified columns
 
col1 is defined as the primary key
 
 
 
View Tables
 
To list all the tables in the present database, run the simple SHOW TABLES command in the MySQL shell.
 
 
View Table Structure
 
The structure of the table, including column names, their types, and their default values, can be seen by running the DESC command.
 
 
Insert Data
 
Data can be inserted into the table by providing the column names into which you want to insert data, as well as the data or values, using the following syntax:
 
 
View Table Data
 
The SELECT statement is used to view the data in a table. You can either provide the asterisk symbol (*) to select all the columns, or you can provide the specific columns that you want to view.
 
 
 
Delete Data from Table
 
To delete a row from a table, provide the table name to the DELETE FROM command, along with the WHERE conditional clause.
 
 
Delete a Table
 
Deleting a table is as easy as saying it. You can delete any table in the present database by running the statement given below.
 
 
 
Delete a Database
 
Deleting or dropping a database is the same as deleting a table in MySQL. Simply use the DROP DATABASE command with the database name.
 
Источник
 
Читайте также:  Посмотреть все маршруты linux