MySQL - Problems and Solutions

InnoDB

First, backup, backup...

Before you attempt to repair corrupted tables, you should back your database files first. Yes, it’s already broken but this is to minimize the risk of possible further damage which may be caused by a recovery operation. There is no guarantee that any action you take will not harm untouched data blocks. Forcing InnoDB recovery with values greater than 4 can corrupt data files, so make sure you will do it with prior backup and ideally on a separate physical copy of the database.

To back up all of the files from all of your databases, follow these steps:

1. Stop the MySQL server

service mysqld stop

Type the following command for your datadir.

cp -r /var/lib/mysql /var/lib/mysql_bkp

After we have a backup copy of the data directory, we are ready to start troubleshooting.

Data Corruption Identification

The error log is your best friend. Usually, when data corruption happens, you will find relevant information (including links to documentation) in the error log. If you don't know where it's located, check my.cnf and variable log_error, for more details check this article https://dev.mysql.com/doc/refman/8.0/en/error-log-destination-configuration.html.

The main tools/commands to diagnose issues with data corruption are CHECK TABLE, REPAIR TABLE, and myisamchk. The mysqlcheck client performs table maintenance: It checks, repairs (MyISAM), optimizes or analyzes tables while MySQL is running.

mysqlcheck -uroot -p <DATABASE>

Replace DATABASE with the name of the database, and replace TABLE with the name of the table that you want to check:

mysqlcheck -uroot -p <DATABASE> <TABLE>

Mysqlcheck checks the specified database and tables. If a table passes the check, mysqlcheck displays OK for the table.

Recovering InnoDB table

If you are using the InnoDB storage engine for a database table, you can run the InnoDB recovery process. To enable auto recovery MySQL needs innodb_force_recovery option to be enabled. Innodb_force_recovery forces InnoDB to start up while preventing background operations from running, so that you can dump your tables.

To do this open my.cnf and add the following line to the [mysqld] section:

[mysqld]
innodb_force_recovery=1

service mysql restart

You should start from innodb_force_recovery=1 save the changes to my.cnf file, and then restart the MySQL server using the appropriate command for your operating system. If you are able to dump your tables with an innodb_force_recovery value of 3 or less, then you are relatively safe. In many cases you will have to go up to 4 and as you already know that can corrupt data.

[mysqld]
innodb_force_recovery=1

service mysql restart

If needed change to the higher value, six is the maximum and most dangerous.

Once you are able to start your database, type the following command to export all of the databases to the databases.sql file:

mysqldump --all-databases --add-drop-database --add-drop-table > dump.sql

Start mysql, and then try to drop the affected database or databases using the DROP DATABASE command. If MySQL is unable to drop a database, you can delete it manually using the steps below after you stop the MySQL server.

service mysqld stop

If you were unable to drop a database, type the following commands to delete it manually.

cd /var/lib/mysql
rm -rf <DATABASE>

Make sure you do not delete the internal database directories. After you are done, comment out the following line in the [mysqld] to disable InnoDB recovery mode.

#innodb_force_recovery=...

Save the changes to the my.cnf file, and then start the MySQL server

service mysqld start

Type the following command to restore the databases from the backup file you created in step 5:

mysql> tee import_database.log
mysql> source dump.sql

Repairing MyISAM

If mysqlcheck reports an error for a table, type the mysqlcheck command with -repair flag to fix it. The mysqlcheck repair option works while the server is up and running.

mysqlcheck -uroot -p -r <DATABASE> <TABLE>

If the server is down and for any reason mysqlcheck cannot repair your table, you still have an option to perform recovery directly on files using myisamchk. With myisamchk, you need to make sure that the server doesn't have the tables open.

Stop the MySQL

service mysqld stop
cd /var/lib/mysql

Change to the directory where the database is located.

cd /var/lib/mysql/employees
myisamchk <TABLE>

To check all of the tables in a database, type the following command:

myisamchk *.MYI

If the previous command does not work, you can try deleting temporary files that may be preventing myisamchk from running correctly. To do this, change back to the data dir directory, and then run the following command:

ls */*.TMD

If there are any .TMD files listed, delete them:

rm */*.TMD

Then re-run myisamchk.

To attempt repair a table, execute the following command, replacing TABLE with the name of the table that you want to repair:

myisamchk --recover <TABLE>

Restart the MySQL server

service mysqld start

MySQL fails on: mysql ERROR 1524 (HY000): Plugin ‘auth_socket’ is not loaded

First, run these bash commands

sudo /etc/init.d/mysql stop # stop mysql service
sudo mysqld_safe --skip-grant-tables & # start mysql without password
# enter -> go
mysql -uroot # connect to mysql

Then run mysql commands => copy paste this to cli manually

use mysql; # use mysql table
update user set authentication_string=PASSWORD("") where User='root'; # update password to nothing
update user set plugin="mysql_native_password" where User='root'; # set password resolving to default mechanism for root user

flush privileges;
quit;

Run more bash commands

sudo /etc/init.d/mysql stop 
sudo /etc/init.d/mysql start # reset mysql
# try login to database, just press enter at password prompt because your password is now blank
mysql -u root -p

Unable to start mysql in safe mode

I was able to reset the root password with following steps.

mysqld --skip-grant-tables  # this will run mysql
mysql -u root

mysql> FLUSH PRIVILEDGES;
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';

Can't reset root password with --skip-grant-tables on ubuntu 16

I found that the mysql.sock is deleted when the mysql service is stopped and mysqld_safe can't create it (I couldn't find the reason), so my solution was back up the sock folder and restore before start mysqld_safe

Start server

sudo service mysql start

Go to sock folder

cd /var/run

Back up the sock

sudo cp -rp ./mysqld ./mysqld.bak

Stop server

sudo service mysql stop

Restore the sock

sudo mv ./mysqld.bak ./mysqld

Start mysqld_safe

sudo mysqld_safe --skip-grant-tables --skip-networking &

Init mysql shell

mysql -u root

Change password

FLUSH PRIVILEGES;

SET PASSWORD FOR root@'localhost' = PASSWORD('my_new_password');

For Ubuntu 19 with MySQL 8.0.17-0ubuntu2, what ended up working for me was a combination of many answers:

In the MySQL's configuration file (/etc/mysql/mysql.conf.d/mysqld.cnf on my machine), under [mysqld], add:

skip-grant-tables = 1
plugin-load-add = auth_socket.so

Restart the MySQL Service;

Connect to MySQL: mysql -uroot;

Run: ~~~sql UPDATE mysql.user SET authentication_string=null WHERE User='root'; FLUSH PRIVILEGES;

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'pass123';

Stop MySQL and comment `skip-grant-tables` in the configuration file;

Start MySQL again and this should now work:
~~~bash
mysql -u root -ppass123.