DEV Community

Waqar Ahmed
Waqar Ahmed

Posted on • Updated on • Originally published at waqar.org

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
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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

use database_name;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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

describe table_name;
DESC table_name;
Enter fullscreen mode Exit fullscreen mode

Similarly, describe user command displays user details and privileges.

describe user_name;
Enter fullscreen mode Exit fullscreen mode

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';
Enter fullscreen mode Exit fullscreen mode

2. Grant Database access to new user

GRANT ALL ON new_database.* TO 'new_user'@'localhost';
Enter fullscreen mode Exit fullscreen mode

3. Reload MySQL privileges

To activate newly assigned privileges run

FLUSH PRIVILEGES;
Enter fullscreen mode Exit fullscreen mode

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'
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

Discussion (0)