Differences

This shows you the differences between two versions of the page.

Link to this comparison view

tech:mysql:mysql_fresh_start [2018/01/06 06:26]
tech:mysql:mysql_fresh_start [2022/03/01 06:28] (current)
Line 1: Line 1:
 +====== 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:
 +<code bash>
 +# 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
 +</​code>​
 +
 +That should do it. But wait there could be more! If you check the mysql_old directory you might find the following files:
 +<​code>​
 +ib_data1
 +ib_logfile0
 +ib_logfile1
 +debian-5.0.flag
 +mysql_upgrade_info
 +</​code>​
 +
 +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 [[http://​dev.mysql.com/​doc/​refman/​5.0/​en/​mysql-upgrade.html|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!!!
 +
 +<​code>​
 +ALTER USER '​root'​@'​localhost'​ IDENTIFIED BY '​YOUR_NEW_PASSWORD';​
 +ALTER USER '​root'​@'​%'​ IDENTIFIED BY '​YOUR_NEW_PASSWORD';​
 +FLUSH PRIVILEGES;
 +</​code>​
 +
 +This NO LONGER works!
 +<​code>​
 +mysql -u root
 +mysql> ALTER USER '​root'​@'​localhost'​ IDENTIFIED WITH mysql_native_password BY '​YOUR_NEW_PASSWORD';​
 +mysql> FLUSH PRIVILEGES;
 +</​code>​
 +
 +===== Updating root access =====
 +If root is only accessible from localhost, to allow remote access use these commands:
 +<​code>​
 +use mysql;
 +select host, user from user;
 +update user SET Host='​%'​ WHERE Host='​localhost'​ AND User='​root';​
 +FLUSH PRIVILEGES;
 +select host, user from user;
 +</​code>​
 +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)