MySQL cli commands

June 13, 2023 /Database

MySQL is a widely used open-source database management system (DBMS) that helps store and manage data in web applications. It provides a reliable and efficient way to organize, retrieve, and manipulate data for various purposes. MySQL Command-Line Interface (CLI) is a tool that allows users to interact with the MySQL database through the command line. It provides a text-based interface where you can execute commands and queries to manage databases, tables, and data directly from the terminal or command prompt.

The MySQL CLI is a handy tool for performing administrative tasks and executing SQL statements without the need for a graphical interface.

Here, we will look into some of the MySQL cli commands that will come handy as a developer while working with MySQL database.

Connecting to Mysql Server
Show currently running processes/threads
Show current databases
Create Database
Create database user
Assign user to database
Select database to work with
Export Database
Import Database

1. Connecting to MySQL server

Before we can run any command, we must first connect to the MySQL Server with following command:

mysql -u root -p

2. Show currently running processes/threads

Display information about the currently running processes or threads on the database server using SHOW PROCESSLIST statement. It retrieves information about each process or thread, including details such as the connection ID, user, host, database, command type and execution time.

SHOW PROCESSLIST;

3. Show current databases

Then we can show existing databases in the MySQL server using SHOW DATABASES command:

SHOW DATABASES;

4. Create Database

To create a new database in MySQL, you use the CREATE DATABASE statement with the following syntax:

CREATE DATABSE  <dbname>
[CHARACTER SET charset_name]
[COLLATE collation_name];

5. Create database user

To use database, we first need to a user that have access to database. For that, we can create database user with following statement and assign password as well.

CREATE USER  '<db_user>'@'<db_host>' IDENTIFIED BY '<db_password>';

6. Assign user to database

Database user should have privililages to be able to access and update database. So, we first need to grant required privileges to database user on our database.

GRANT ALL PRIVILEGES ON <dbname>.* TO '<db_user>'@'<db_host>';
FLUSH PRIVILEGES;

7. Select database to work with

To work further with database, we must first choose on which database we want to work on unsing USE statement.

USE <dbname>;

8. Export Database

To export dump of our database, we can use following command. This will export tables of database defined as <dbname> to db_dump.sql file.

mysqldump -u <dbuser>  -p <dbname> <tablenames> > db_dump.sql

9. Import Database

We can use followind command to import MySQL database using a MySQL dump file.

mysql -u [dbuser] -p [dbname] < [dump_file].sql

Or we can also import database after logging into mysql server following these steps:

mysql -u [dbuser] -p

Create database and/or use existing database

// If database doesnot exist
CREATE DATABASE [dbname];

// Choose database
USE [dbname];

Import SQL file using the source command

source [dump_file].sql;

These are just few MySQL CLI commands and statements that comes in handy as a developer while working with MySQL databases.

cli

command line