MySQL Database Administration Essentials

Introduction

In this project, you will learn how to create a new user in a MySQL database, set the maximum number of connections, and configure the binary log retention period. This project is designed to help you understand the basic database administration tasks and how to automate them using SQL scripts.

👀 Preview

🎯 Tasks

In this project, you will learn:

• How to start the MySQL service on your system
• How to create a new user with passwordless local login
• How to set the maximum number of connections to the database
• How to enable auto deletion of binary logs and set the log retention period

🏆 Achievements

After completing this project, you will be able to:

• Manage user accounts and permissions in a MySQL database
• Configure database settings to optimize performance and maintenance
• Automate common database administration tasks using SQL scripts

Start the MySQL Service

In this step, you will start the MySQL service on your system. Follow the steps below to complete this step:

1. Open a terminal on your system.
2. Run the following command to start the MySQL service:

sudo service mysql start

This will start the MySQL service on your system.

Save the Script

1. Create a new file named userBinLog.sql in the ~/project directory.

2. Copy the following code into the userBinLog.sql file:

3. Create a new user named 'Rong' with a blank password

   -- Create a new user named 'Rong' with a blank password
   CREATE USER 'Rong'@'localhost' IDENTIFIED BY '';
   

   This will create a new user named "Rong" with a blank password, and allow the user to log in from the local machine.

4. Set the maximum number of connections to 50

   -- Set the maximum number of connections to 50
   SET GLOBAL max_connections = 50;
   

   This will set the maximum number of connections to 50.

5. Set the binary log retention period to 15 days

   -- Set the binary log retention period to 15 days
   SET GLOBAL binlog_expire_logs_seconds = 15 * 24 * 60 * 60;
   

   This will set the binary log retention period to 15 days.

Run the Script

1. Open a MySQL command-line interface by running the following command:

   mysql -uroot
   

2. In the MySQL prompt, run the following command to execute the userBinLog.sql script:

   SOURCE ~/project/userBinLog.sql
   

   This will execute the SQL statements in the userBinLog.sql script.

3. You have now completed the project. You can verify the results by running the following SQL statements:

   SHOW VARIABLES LIKE "%max_connections%";
   SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';
   use mysql;
   SELECT user, host FROM user WHERE user = 'Rong';
   

   These statements will display the current values of the max_connections and binlog_expire_logs_seconds variables, as well as the user you have created.

You can check the following results:

MariaDB [(none)]> SHOW VARIABLES LIKE "%max_connections%";
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| extra_max_connections | 1     |
| max_connections       | 50    |
+-----------------------+-------+
2 rows in set (0.001 sec)

MariaDB [(none)]> SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';
+----------------------------+---------+
| Variable_name              | Value   |
+----------------------------+---------+
| binlog_expire_logs_seconds | 1296000 |
+----------------------------+---------+
1 row in set (0.000 sec)

MariaDB [(none)]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]> SELECT user, host FROM user WHERE user = 'Rong';
+------+-----------+
| User | Host      |
+------+-----------+
| Rong | localhost |
+------+-----------+
1 row in set (0.001 sec)

Summary

Congratulations! You have completed this project. You can practice more labs in LabEx to improve your skills.