I corrupted my MySQL database by doing a simple mistake. Took the sqldump from another database and loaded it without realizing that the dump had the mysql database of the source database. That seemed to have caused major issues for obvious reasons. After that it was going back to restoring the MySQL database. But wait, I did not have a backup of my target database since it was brand new and I had nothing to save. Now I have corrupted the default mysql database itself. No, you don't have to reinstall mysql at this point. Thankfully there is an option to just reset the data point to the beginning.
The mysql_install_db command is available to Initialize MySQL Data Directory. Assuming you want your data directory (in my case this is /var/lib/mysql) to be in the same place as it was before here is what you need to do:
# Make sure you are root or atleast mysql user # This set of commands was tested as root # Stop mysql /etc/init.d/mysql stop # Go to the mysql data directory -1 level cd /var/lib/ # The below is to view the mysql data directory permissions. It should be 700 ls -ld mysql # Save the current data dir for reference mv mysql mysql_old mkdir mysql # Use the permissions from above chown 700 mysql # Initialize the data directory mysql_install_db --user=mysql # Start mysql /etc/init.d/mysql start # Check data dir - should have a mysql directory and files (inside the mysql main data dir) ls -l mysql # Enter mysql to get prompt without asking for password
That should do it. But wait there could be more! If you check the mysql_old directory you might find the following files:
ib_data1 ib_logfile0 ib_logfile1 debian-5.0.flag mysql_upgrade_info
You can skip the ib* files as they are for innodb and at this point you don't have any! As for the other two files, I would copy them back. The mysql_upgrade_info file is used by mysql_upgrade command. Although I have not tried it, but I guess one could use the – – force command to see if mysql creates this file automatically while checking the tables (of which there are not many at this point!)
The debian-5.0.flag in my install is a zero byte file. I would simple copy it back. Check to see if there are any other files and you may have to do some research if there are.
Set your MySQL root password!!!
ALTER USER 'root'@'localhost' IDENTIFIED BY 'YOUR_NEW_PASSWORD'; ALTER USER 'root'@'%' IDENTIFIED BY 'YOUR_NEW_PASSWORD'; FLUSH PRIVILEGES;
This NO LONGER works!
mysql -u root mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'YOUR_NEW_PASSWORD'; mysql> FLUSH PRIVILEGES;
If root is only accessible from localhost, to allow remote access use these commands:
use mysql; select host, user from user; update user SET Host='%' WHERE Host='localhost' AND User='root'; FLUSH PRIVILEGES; select host, user from user;
Also you might need to make changes to the mysql.db table as well.
Update mysqld.cnf and comment out the bind-address = 127.0.0.1 line.