How to reset MySQL back to install point

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.

mysql_install_db — Initialize MySQL Data Directory

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.

Post operations

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;

Updating root access

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.

Allow connectivity from other hosts

Update mysqld.cnf and comment out the bind-address = 127.0.0.1 line.


QR Code
QR Code tech:mysql:mysql_fresh_start (generated for current page)