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


Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Bookmarks
  • Search
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
Brand Logo

Cloudron Forum

Apps | Demo | Docs | Install
  1. Cloudron Forum
  2. Support
  3. mysql slow query log

mysql slow query log

Scheduled Pinned Locked Moved Solved Support
mysql
9 Posts 3 Posters 1.9k Views 3 Watching
  • 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
    • mmolivierM 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!

      girishG Offline
      girishG Offline
      girish
      Staff
      wrote 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
      • girishG 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).

        mmolivierM Offline
        mmolivierM Offline
        mmolivier
        wrote on last edited by
        #3

        @girish I'll try it out, thanks!

        1 Reply Last reply
        0
        • girishG 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).

          mmolivierM Offline
          mmolivierM Offline
          mmolivier
          wrote 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
          • mmolivierM 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.

            micmcM Offline
            micmcM Offline
            micmc
            wrote 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.

            Ignorance is not an excuse anymore!
            https://AutomateKit.com

            mmolivierM 1 Reply Last reply
            0
            • micmcM 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.

              mmolivierM Offline
              mmolivierM Offline
              mmolivier
              wrote 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
              • mmolivierM 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.

                girishG Offline
                girishG Offline
                girish
                Staff
                wrote on last edited by
                #7

                @mmolivier Let me try this tomorrow and get back.

                1 Reply Last reply
                0
                • mmolivierM 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

                  girishG Offline
                  girishG Offline
                  girish
                  Staff
                  wrote 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
                    Reply
                    • Reply as topic
                    Log in to reply
                    • Oldest to Newest
                    • Newest to Oldest
                    • Most Votes


                    • Login

                    • Don't have an account? Register

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