42
loading...
MySQL database management using the terminal
To log in to MySQL server via terminal, use login credentials and hostname.
mysql -h localhost -u username -pMySQL 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;Before moving to the user and database creation command, there are a few things to remember.
; after every query.\c flag.\h flag.\q flag.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;42