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 thing 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;