How to install, configure, create user and database with permissions – MySQL

Introduction
MySQL is one of the most popular open-source relational database management systems used by developers worldwide. Whether you’re a beginner or an experienced user, understanding how to install, configure, create users, and databases with permissions in MySQL is essential for effective database management. In this blog post, we’ll guide you through the step-by-step process of installing MySQL, configuring it, creating users, and databases with appropriate permissions.

Table of Contents:

1. Installing MySQL
2. Configuring MySQL
3. Creating Users and Granting Permissions
4. Creating Databases
5. Managing User Privileges
6. Conclusion

Section 1: Installing MySQL
To install MySQL on a Linux distribution, you can use the package manager specific to your distribution. Here are the commands for popular Linux distributions:

• Ubuntu and Debian:

  sudo apt update
  sudo apt install mysql-server

• CentOS and Fedora:

  sudo dnf install mysql-server

• Arch Linux:

  sudo pacman -Syu mysql
  sudo mysql_install_db --user=mysql --basedir=/usr --datadir=/var/lib/mysql
  sudo systemctl enable --now mysqld

Section 2: Configuring MySQL
Once MySQL is installed, you need to configure it. The configuration file may vary depending on the distribution. Here are the commonly used commands to modify the MySQL configuration file:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Within the configuration file, you can modify settings such as bind-address, max_connections, character_set_server, and more. Save the changes and restart the MySQL service for the changes to take effect.

Section 3: Creating Users and Granting Permissions
MySQL allows you to create users and assign specific permissions to them. Here are the commands to create a user and grant privileges:

1. Log in to the MySQL shell as the root user:

   sudo mysql -u root -p

2. Create a new user and set a password:

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

3. Grant privileges to the user:

   GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost';

4. Flush the privileges for the changes to take effect:

   FLUSH PRIVILEGES;

Section 4: Creating Databases
To create a new database in MySQL, use the following command:

CREATE DATABASE database_name;

You can replace database_name with the desired name for your database.

Section 5: Managing User Privileges
To modify user privileges or revoke them, use the following commands:

• Grant privileges to a user:

  GRANT privilege_type ON database_name.table_name TO 'username'@'localhost';

• Revoke privileges from a user:

  REVOKE privilege_type ON database_name.table_name FROM 'username'@'localhost';

Replace privilege_type with the specific privilege, such as SELECT, INSERT, UPDATE, DELETE, or ALL.

Section 6: Conclusion
In conclusion, this blog post has provided you with a step-by-step guide on installing, configuring, creating users, and databases with permissions in MySQL. By following these instructions and using the commands specific to your Linux distribution, you can effectively manage your MySQL environment. Remember to always prioritize security and follow best practices for optimal performance and data integrity in your MySQL projects.