MySQL can't connect after system udpate

Discussion in 'Installation/Configuration' started by schwim, Apr 7, 2022.

  1. schwim

    schwim Member

    I'm not sure how to progress but at recovery level 6, most of my search results involve completely wiping mysql and starting over again by dumping the tables using a script and then rebuilding the users and databases after recovery. Is this the path I need to take? If so, what's the best way of going about it?

    https://stackoverflow.com/questions...-only-accessible-with-innodb-force-recovery-6

    Code:
    set -eux
    edir=export
    function ensure_mysqld() {
           mysql -e 'select 1' && return
           service mysql start
           timeout=300
           while [[ $timeout -gt 0 ]]
           do
                   mysql -e 'select 1' && return
                   sleep 1
                   timeout=$(( $timeout - 1 ))
           done
           echo "failed to start MySQL"
           exit 1
    }
    for d in $(cat databases)
    do
           mkdir -p $edir/$d
           set +e
           ensure_mysqld
           set -e
           for t in $(mysql -S $socket -NBe "select TABLE_NAME from information_schema.TABLES WHERE TABLE_SCHEMA='$d' AND ENGINE = 'InnoDB' AND TABLE_TYPE = 'BASE TABLE'")
           do
                   ensure_mysqld
                   mysqldump --skip-lock-tables $d $t > $edir/$d/$t.sql || echo "$d.$t" >> failed.txt
           done
    done
    
     
  2. schwim

    schwim Member

    If it helps determine the next step, I can log into applications like PHPMyAdmin and access the databases. I just routinely see errors about the pma tables being read-only.
     
  3. schwim

    schwim Member

    If no further assistance can be given, could someone tell me where I might find help with this, location of another forum perhaps?
     
  4. Th0m

    Th0m ISPConfig Developer Staff Member ISPConfig Developer

    Have you ran this:
    Code:
    mysql_upgrade
    ?
     
  5. schwim

    schwim Member

    I tried but it doesn't work because I can only start in mode 6 and the tables are read-only.
     
  6. schwim

    schwim Member

    Any other thoughts for the pickle I'm in?
     
  7. Taleman

    Taleman Well-Known Member HowtoForge Supporter

    I tried the error message you got with Internet Search engines. Results suggest that
    innodb_force_recovery > 0 in my.cnf causes the read only state.
     
  8. schwim

    schwim Member

    That's my problem though. I have to start it in recovery mode 6 or it won't start at all. That's the whole issue. I can't figure out how to get to the point where I can start it regularly. All of the errors that I know how to retrieve are completely vague and useless to me for the purposes of troublehooting. I don't know where to go from here.
     
  9. Th0m

    Th0m ISPConfig Developer Staff Member ISPConfig Developer

    Maybe it's best to get in touch with someone to get direct support?
     
  10. schwim

    schwim Member

    Who? A mariadb developer? The host of the machine I ran the apt upgrade on? I'm not exaggerating when I say I literally don't know who to ask or where to go site-wise to find out.

    For such a widely used system, I'm shocked that there's so little stuff out there to find for help. The only thing I've found is to wipe all the databases and start over again.
     
    Last edited: Apr 12, 2022
  11. Jesse Norell

    Jesse Norell ISPConfig Developer Staff Member ISPConfig Developer

    I would try to dump your databases, stop mysqld, rename /var/lib/mysql (as a backup), then reinstall mariadb server and load your databases from the SQL dumps. Looking at that link above, it seems something similar is what is being suggested (ie dump and recreate).
     
  12. schwim

    schwim Member

    To clarify what you're suggesting:
    1) mv /var/lib/mysql /var/lib/mysql-bak
    2) sudo apt-get install --reinstall mariadb-server
    --------------- repeat for 37 databases ------------------
    3) mysql -u root -p
    4) CREATE DATABASE databasename;
    5) mysql -u username -p databasename < dump.sql

    I'm confused as to at which point I am to rebuild all the database users. Do I do that before importing the dump files, so they have permissions to the database from the getgo by using their credentials when logging into mysql for the import?
     
  13. Jesse Norell

    Jesse Norell ISPConfig Developer Staff Member ISPConfig Developer

    Did you make sql dumps with the script you posted above? It looks like those will be one sql file per table ... you could probably simplify things dumping entire databases into a file, or even dump all databases into one file and see how that works (restore with the root mysql user).
    These users and their permissions are defined in the mysql database, so start with restoring that first.
     
  14. schwim

    schwim Member

    Is there an easy way for me to determine all users stored in the system? I know as soon as I make my changes, it will break ISPC, PHPMyAdmin, etc so I won't have access to that after the install. I'm pretty sure I can dump the databases with mysql in it's current state but I'm not sure how to figure out what all the users are as well as their passwords.
     
  15. till

    till Super Moderator Staff Member ISPConfig Developer

    The mysql users are a mysql database, so if you do what @Jesse Norell suggested and dump the whole database server means use mysqldump to dump all databases incl the 'mysql' database and then restore that dump later, then your dump contains the users too. No need to know any passwords etc.
     
  16. till

    till Super Moderator Staff Member ISPConfig Developer

    What you do is:

    1) Stop mysql and then make a backup of the whole /var/lib/mysql folder by copying it to a safe location. This first step is important as it enables you to redo the restore attempt in case it fails on first try.
    2) Start MySQL again, then run e.g.:

    mysqldump --defaults-file=/etc/mysql/debian.cnf -cCeQ --hex-blob --quote-names --routines --events --triggers --all-databases -r all_databases.sql

    to dump all databases into the file all_databases.sql.

    3) Now you must remove the old databases and reinstall mariadb.
    4) Finally, you restore all databases again with:

    mysql --defaults-file=/etc/mysql/debian.cnf < all_databases.sql

    That's untested and you'll definitely find more detailed instructions on the net if you search for it. The above is just that you get an idea of what needs to be done to restore it. Maybe @Th0m might help you with that remotely, he provides support for ISPConfig. You might want to write him a PM if you don't want to try it yourself.
     
  17. schwim

    schwim Member

    Thank you very much Till. I messaged him and will wait 24 hours to see if he responds before moving forward. I'm wary to try it myself for fear of breaking it worse but have to balance that with how long it's been down.
     

Share This Page