Getting Started with MySQL: Installation and Basic Operations

Install MySQL

Start by updating your package repository and installing MySQL server on a Linux system:

sudo apt update
sudo apt install mysql-server

Access MySQL & Create a User

To access MySQL, you can use the MySQL command-line tool. If you haven't set up a specific user yet, you can access it as the root user:

sudo mysql

Alternatively, if you have set up a MySQL user, you can access MySQL with:

mysql -u username -p

Replace username with your actual MySQL username.

Create a MySQL User

Inside the MySQL shell, you can create a new user by executing:

CREATE USER 'dbUsername'@'localhost' IDENTIFIED BY 'password';

CREATE USER: This statement is used to create a new MySQL user. 'dbUsername'@’localhost’: Specifies the username and the hostname. Replace dbUsername with your desired username. The localhost indicates that this user can only connect from the local machine. Use % to allow connections from any host. IDENTIFIED BY 'password': Sets the password for the user. Replace password with your desired password.

Grant Privileges

After creating the user, you might need to grant them necessary permissions:

GRANT ALL PRIVILEGES ON *.* TO 'dbUsername'@'localhost' WITH GRANT OPTION;

This grants all privileges to the user on all databases and tables.

Create Database

To create a new database, use the following command:

CREATE DATABASE tutorial_database;

To verify the creation of your database, list all databases:

SHOW DATABASES;

Import .sql file to Database

Navigate to the directory containing your .sql file. Then, use the following command to import it into your database:

mysql -u username -p database_name < file.sql

Replace username, database_name, and file.sql with your MySQL username, the name of your database, and the .sql file name, respectively.

Manage Your Database

Once logged into MySQL, you can manage your databases. List all databases:

SHOW DATABASES;
USE your_database_name;
SHOW TABLES;
DESCRIBE table_name;
SELECT * FROM table_name;

Restart MySQL Service

To apply changes, you may need to restart the MySQL service:

sudo systemctl restart mysql

Check the status of the MySQL service to ensure it's running:

sudo systemctl status mysql

Alternatively, you can check if MySQL is listening for connections:

sudo ss -tulnp | grep mysqld

Drop a Database

To delete a database, use the DROP DATABASE command:

DROP DATABASE helios;

Replace database_name with the name of the database you wish to delete.

By following these steps, you should be able to manage MySQL databases effectively on your server. Remember to replace placeholder values with your actual database names, usernames, and passwords as appropriate.