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
Once logged in, you can run any SQL query in the MySQL console. However, select the database first.
The above command will display a list of all databases. Run the
use command to select a database.
Now you can run any
DELETE query on the table. You can
DROP the tables.
To see the list of all tables, run this command.
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.
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
- To get more help, use the help
- To exit from MySQL, use the quit
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
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;