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


    Cloudron Forum

    • Register
    • Login
    • Search
    • Categories
    • Recent
    • Tags
    • Popular

    Solved mysql slow query log

    Support
    mysql
    3
    9
    371
    Loading More Posts
    • 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.
    • mmolivier
      mmolivier last edited by girish

      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 1 Reply Last reply Reply Quote 0
      • girish
        girish Staff @mmolivier last edited by 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 2 Replies Last reply Reply Quote 0
        • mmolivier
          mmolivier @girish last edited by

          @girish I'll try it out, thanks!

          1 Reply Last reply Reply Quote 0
          • mmolivier
            mmolivier @girish last edited by

            @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 girish 2 Replies Last reply Reply Quote 0
            • micmc
              micmc @mmolivier last edited by

              @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

              mmolivier 1 Reply Last reply Reply Quote 0
              • mmolivier
                mmolivier @micmc last edited by

                @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 1 Reply Last reply Reply Quote 0
                • girish
                  girish Staff @mmolivier last edited by

                  @mmolivier Let me try this tomorrow and get back.

                  1 Reply Last reply Reply Quote 0
                  • girish
                    girish Staff @mmolivier last edited by

                    @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 Reply Quote 2
                    • girish
                      girish Staff last edited by

                      (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 Reply Quote 0
                      • First post
                        Last post
                      Powered by NodeBB