Installation process for MySQL database on Linux systems

1. Install the database

1. Use the command: yum -y install mysql-server (simple method) This command automatically searches for the MySQL service resource from the internet, downloads it to the local system, and completes the installation.

2. Alternatively, download the MySQL service from the internet, transfer it to the Linux system using xftp, and install it manually (typically installed in /usr or /opt directories).

2. Start the database After installation, run the command: service mysqld start

3. Access the database Run the command: mysql -u root -p, then enter the password (the default user is root, and the password is empty by default).

The latest version available is 8.0.

Configure user access and remote connectivity

In MySQL 8.0, remote access for the root user is disabled by default. To enable remote access, follow these steps:

1. Connect to the MySQL server using the MySQL command-line client or a database management tool.
2. Log in as root using the following command:

sudo mysql -u root -p

Enter the root user password when prompted.

3. Run the following commands to modify the root user's remote access permissions:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password';
CREATE USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'your_password';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Replace 'your_password' with the desired password for the root user.

4. Edit the MySQL configuration file to allow remote connections. Locate and open the configuration file (usually found at /etc/mysql/mysql.conf.d/mysqld.cnf or /etc/mysql/my.cnf).
5. Find the line bind-address and either comment it out or change it to:

# bind-address = 127.0.0.1

This allows MySQL to accept connections from all IP addresses.

6. Save and close the file.
7. Restart the MySQL service to apply the changes:

sudo service mysql restart

or

sudo systemctl restart mysql

At this point, the root user should have remote access capabilities. However, granting root user remote access may pose security risks. For better security, consider using a user with limited access rights and restrict remote access to specific IP addresses only.

Adjust DDL and DML permissions

To modify DDL (Data Definition Language) and DML (Data Manipulation Language) permissions in MySQL 8.0, follow these steps:

Step 1: Log in to MySQL as root

Open a terminal and run the following command:

mysql -u root -p

Enter the root password and press Enter to log in to MySQL.

Step 2: Check current user permissions

Use the flolowing command to view the current user's privileges:

SHOW GRANTS FOR '<your_username>';

Replace <your_username> with the username you want to modify.

Step 3: Modify DDL permissions

a. Adjust DDL permissions for an existing user:

In the previous step, you will see the current user's permission details. Look for lines starting with GRANT ALTER, CREATE, DROP, INDEX, ..., which indicate DDL permissions. If you want to revoke a specific DDL permission, use the following example command:

REVOKE DROP ON your_database_name.* FROM '<your_username>';

Replace your_database_name with the database name and <your_username> with the user whose permissions you want to adjust.

b. Set default DDL permissions for a new user:

Use the following commands to create a new user and assign DDL permissions:

CREATE USER 'your_username'@'localhost';
GRANT CREATE, ALTER, DROP, INDEX, ... ON your_database_name.* TO 'your_username'@'localhost';

Replace your_username and your_database_name with appropriate values and add other DDL permissions as needed.

Step 4: Modify DML permissions

To modify DML permissions, follow the same pattern as in Step 3, but replace DDL with DML. For example, to grant or revoke the INSERT permission, use:

Grant INSERT permission:

GRANT INSERT ON your_database_name.* TO 'your_username'@'localhost';

Revoke INSERT permission:

REVOKE INSERT ON your_database_name.* FROM 'your_username'@'localhost';

Adjust the database name, username, and permission based on your requirements.

Step 5: Refresh privileges

After making any changes to the permissions, refresh the MySQL privilege cache to ensure the changes take effect:

FLUSH PRIVILEGES;

Step 6: Exit MySQL

Once you've completed all the permission modifications, exit MySQL using the following command:

EXIT;

By following these steps, you can modify DDL and DML permissions in MySQL 8.0. Be cautious when modifying permissions, as they can affect database security and data integrity.

Common DDL and DML Permissions

Below is a list of common DDL and DML permissions in MySQL:

DDL Permissions (Data Definition Language):

• CREATE: Create new databases, tables, views, functions, stored procedures, etc.
• ALTER: Modify database structures, such as adding or removing columns, changing column properties, etc.
• DROP: Delete databases, tables, views, functions, stored procedures, etc.
• INDEX: Create, modify, or delete indexes.
• TRIGGER: Create, modify, or delete triggers.
• VIEW: Create, modify, or delete views.
• SHOW VIEW: View the definition information of views.
• GRANT OPTION: Grant permissions to other users.

DML Permissions (Data Manipulation Language):

• SELECT: Retrieve data from tables.
• INSERT: Insert new rows into tables.
• UPDATE: Update existing rows in tables.
• DELETE: Delete data from tables.
• EXECUTE: Execute stored procedures or functions.

These permissions can be granted or revoked individually for specific users or groups. In practice, you can further refine DDL and DML permissions based on specific needs.