Cloudron makes it easy to run web apps like WordPress, Nextcloud, GitLab on your server. Find out more or install now.


  • Categories
  • Recent
  • Tags
  • Popular
  • Bookmarks
Skins
  • Light
  • Cerulean
  • Cosmo
  • Flatly
  • Journal
  • Litera
  • Lumen
  • Lux
  • Materia
  • Minty
  • Morph
  • Pulse
  • Sandstone
  • Simplex
  • Sketchy
  • Spacelab
  • United
  • Yeti
  • Zephyr
  • Dark
  • Cyborg
  • Darkly
  • Quartz
  • Slate
  • Solar
  • Superhero
  • Vapor

  • Default (No Skin)
  • No Skin
Collapse

Cloudron Forum

Apps | Demo | Docs | Install

mysql slow query log

Scheduled Pinned Locked Moved Solved Support
mysql
9 Posts 3 Posters 473 Views
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • mmolivierM Offline
    mmolivierM Offline
    mmolivier
    wrote on last edited by girish
    #1

    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!

    girishG 1 Reply Last reply
    0
  • girishG Offline
    girishG Offline
    girish Staff
    replied to mmolivier on last edited by girish
    #2

    @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).

    mmolivierM 2 Replies Last reply
    0
  • mmolivierM Offline
    mmolivierM Offline
    mmolivier
    replied to girish on last edited by
    #3

    @girish I'll try it out, thanks!

    1 Reply Last reply
    0
  • mmolivierM Offline
    mmolivierM Offline
    mmolivier
    replied to girish on last edited by
    #4

    @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.

    micmcM girishG 2 Replies Last reply
    0
  • micmcM Offline
    micmcM Offline
    micmc
    replied to mmolivier on last edited by
    #5

    @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.


    https://marketingtechnology.agency
    For cutting edge web technologies

    mmolivierM 1 Reply Last reply
    0
  • mmolivierM Offline
    mmolivierM Offline
    mmolivier
    replied to micmc on last edited by
    #6

    @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

    girishG 1 Reply Last reply
    0
  • girishG Offline
    girishG Offline
    girish Staff
    replied to mmolivier on last edited by
    #7

    @mmolivier Let me try this tomorrow and get back.

    1 Reply Last reply
    0
  • girishG Offline
    girishG Offline
    girish Staff
    replied to mmolivier on last edited by
    #8

    @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

    1 Reply Last reply
    2
  • girishG Offline
    girishG Offline
    girish Staff
    wrote on last edited by
    #9

    (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.

    1 Reply Last reply
    0

  • Login

  • Don't have an account? Register

  • Login or register to search.
  • First post
    Last post
0
  • Categories
  • Recent
  • Tags
  • Popular
  • Bookmarks
  • Login

  • Don't have an account? Register

  • Login or register to search.