Learn How to Change the MySQL Error Log Location

Introduction

In this project, you will learn how to change the default error log location for the MySQL database server. By the end of this project, you will be able to:

Create a dedicated directory for storing MySQL error logs
Set the appropriate permissions and ownership for the log directory and file
Modify the MySQL configuration files to change the default error log location

👀 Preview

MySQL error log setup preview

🎯 Tasks

In this project, you will learn:

How to create a new directory for storing MySQL error logs
How to change the user, group, and permissions for the log directory and file
How to modify the MySQL configuration files to set the new error log location

🏆 Achievements

After completing this project, you will be able to:

Customize the location of the MySQL error logs to a more convenient directory
Ensure proper permissions and ownership for the MySQL error log files
Confidently navigate and modify MySQL configuration files to suit your needs

Create the MySQL Log Directory and Set Permissions

In this step, you will create the MySQL log directory and set the appropriate permissions.

Create the mysql directory under the ~/project directory and move the my.log file into this directory.
```
mkdir ~/project/mysql
touch ~/project/mysql/my.log
```
Change the user, user group, and mode of the ~/project/mysql/my.log file and the ~/project/mysql directory using the sudo command.
```
sudo chown mysql:mysql ~/project/mysql/my.log
sudo chown mysql:mysql ~/project/mysql
sudo chmod 755 /home/labex
```

This command sets the owner and group of the my.log file and the mysql directory to mysql:mysql, and sets the permissions on the home directory (/home/labex) to 755 (read, write, and execute for the owner, read and execute for the group and others).

Modify the MySQL Configuration Files

In this step, you will modify the MySQL configuration files to change the default error log location.

Navigate to the /etc/mysql/mariadb.conf.d directory.
```
cd /etc/mysql/mariadb.conf.d
```
Open the 50-server.cnf file using a text editor.
```
sudo vim 50-server.cnf
```
In the [mysqld] section, uncomment the log_error option and set the path to "/home/labex/project/mysql/my.log".
```
[mysqld]
log_error = /home/labex/project/mysql/my.log
```
Open the 50-mysqld_safe.cnf file using a text editor.
```
sudo vim 50-mysqld_safe.cnf
```
In the [mysqld_safe] section, comment out the skip_log_error option.
```
[mysqld_safe]
#skip_log_error
```

These changes will ensure that the MySQL error logs are written to the my.log file in the ~/project/mysql directory, and that the skip_log_error option is commented out.

Restart the MySQL Service and Verify the Changes

In this step, you will restart the MySQL service and verify that the changes have been applied correctly.

Start the MySQL service.
```
sudo service mysql start
```
Connect to the MySQL command-line interface.
```
mysql -uroot
```

Use the SQL statement to check the value of the log_error variable.

SHOW VARIABLES LIKE 'log_error';

MariaDB [(none)]> SHOW VARIABLES LIKE 'log_error';
+---------------+----------------------------------+
| Variable_name | Value                            |
+---------------+----------------------------------+
| log_error     | /home/labex/project/mysql/my.log |
+---------------+----------------------------------+
1 row in set (0.001 sec)

The output should show that the log_error variable is set to the correct path, /home/labex/project/mysql/my.log.

Congratulations! You have successfully changed the MySQL error log location to the my.log file in the ~/project/mysql directory.

Summary

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

Changing the Error Log Location