Solved mysql slow query log

[mmolivier]

Any recommendations on how to enable mysql slow query logging?

Before using Cloudron I'd just temporarily elevate the MySQL user's permissions and set the global variables within a SQL query.

Is there any way to:

1. Get root access to temporarily elevate the MySQL user's permissions?
2. Set some variable somewhere? The variables I need to set are slow_query_log, log_queries_not_using_indexes and slow_query_log_file
3. Access the my.cnf?

Thanks!

[girish]

@mmolivier The second option is possible with something like below:

• docker exec -ti mysql /bin/bash
• You can then edit /run/mysql/my.cnf
• supervisorctl restart mysql

Note that the changes will be lost when we update the mysql container (happens when we make a new Cloudron release).

[mmolivier]

@girish I'll try it out, thanks!

[mmolivier]

@girish I tried to modify the variables using this approach but unfortunately MySQL doesn't log anything after doing this. Seems like these variables are overridden at the database level: If I open the 'Variables' section for my app database in Phpmyadmin the logging is disabled.

Is there any way to get root/admin access to the MySQL CLI? I'd like to try this approach: https://ma.ttias.be/mysql-slow-query-log-without-restart/

This approach would be much better because I want to enable logging on a single database, not on all of them.

[micmc]

@mmolivier said in mysql slow query log:

Is there any way to get root/admin access to the MySQL CLI? I'd like to try this approach: https://ma.ttias.be/mysql-slow-query-log-without-restart/

Actually yes, you need to open terminal of your app and you should able to get access to the MySQL CLI using the following command:

mysql --user=${CLOUDRON_MYSQL_USERNAME} --password=${CLOUDRON_MYSQL_PASSWORD} --host=${CLOUDRON_MYSQL_HOST} ${CLOUDRON_MYSQL_DATABASE}

Hope that helps.

[mmolivier]

@micmc Doesn't work unfortunately:

mysql> SET slow_query_log = 'ON';

ERROR 1229 (HY000): Variable 'slow_query_log' is a GLOBAL variable and should be set with SET GLOBAL

...meaning it has to be set as a global variable. Logging a single database isn't possible apparently. The app level user doesn't have access to set global variables:

mysql> SET GLOBAL slow_query_log = 'ON';
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation

[girish]

@mmolivier Let me try this tomorrow and get back.

[girish]

@mmolivier that took forever... Generally, we don't allow the database containers to have debugging values, it makes it hard for us to maintain arbitrary configs on selfhosted servers.

With that warning out of the way, here is how to do it. The mysql addon is a container of it's own. The root password is in the environment variable of the container.

• docker inspect mysql | grep ROOT_PASSWORD . Note down the password
• docker exec -ti mysql /bin/bash to get a shell into the mysql container.
• Inside, the container:

root@mysql:/# mysql -uroot -pTHE_PASSWORD_NOTED_ABOVE
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 302
Server version: 8.0.27-0ubuntu0.20.04.1 (Ubuntu)
...

mysql> SET GLOBAL slow_query_log = 'ON';
Query OK, 0 rows affected (0.00 sec)

MySQL logs are located inside the container at /tmp/mysqld.err

[girish]

(please read warning in above comment before following instructions below).

If you want to make config file changes:

• docker exec -ti mysql /bin/bash
• Edit /run/mysql/my.cnf as needed
• supervisorctl restart mysql

It is important to note that the changes to config files are not persistent across container restarts and server reboot. In general, we have not envisioned Cloudron as a development environment, but we do have many devs using it as such. So, if there is interest, maybe we need to improve things for such use cases.