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
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.
If no further assistance can be given, could someone tell me where I might find help with this, location of another forum perhaps?
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.
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.
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.
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).
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?
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.
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.
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.
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.
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.
