MySQL database management using the terminal

To log in to MySQL server via terminal, use login credentials and hostname.

mysql -h localhost -u username -p

MySQL server will prompt for the password. The -h hostname flag is optional use only when the host is different from the default value localhost.

Once logged in, you can run any SQL query in the MySQL console. However, select the database first.

show databases;

The above command will display a list of all databases. Run the use command to select a database.

use database_name;

Now you can run any SELECT, UPDATE, DELETE query on the table. You can CREATE and DROP the tables.

To see the list of all tables, run this command.

show tables;

Describe table command shows the columns and their attributes like names, data types, collation, primary key, index nullability.

describe table_name;
DESC table_name;

Similarly, describe user command displays user details and privileges.

describe user_name;

Things to remember in MySQL command-line

Before moving to the user and database creation command, there are a few things to remember.

• Use a terminator; after every query.
• For a multi-line query, hit enter to go to the new line.
• To clear the SQL query without running it, use the clear \c flag.
• To get more help, use the help \h flag.
• To exit from MySQL, use the quit \q flag.

Root user VS non-root user

The MySQL root user has full access to the database server. Using root can seriously mess things up or delete critical stuff on the server without even warnings. It would be insecure to access MySQL as a root user from a website.

To create a non-root new MySQL user and give it only needed privileges and access to a database, follow these steps.

1. Create a new MySQL user

Create a new MySQL user with a password.

CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'user_password';

2. Grant Database access to new user

GRANT ALL ON new_database.* TO 'new_user'@'localhost';

3. Reload MySQL privileges

To activate newly assigned privileges run

FLUSH PRIVILEGES;

To give privileges to a table only, use database.table_name notation. To see the privileges of new user run,

SHOW GRANTS FOR 'new_user'@'localhost'

Create a new MySQL Database

Log into MySQL shell using your credentials and run the following command to create a new database.

create database new_database;