Skip to content

Latest commit

 

History

History
130 lines (96 loc) · 3.5 KB

mysql-reset.md

File metadata and controls

130 lines (96 loc) · 3.5 KB

mysql-server Complete Re-Installation Steps

Just in case I mess up again 🥲

  1. Make sure MySQL isn't running and kill all running processes
# to stop process with sytemd init system
sudo systemctl stop mysql
      
# or stop without init system
sudo /etc/init.d/mysql stop

# On Linux
# if that doesn't work manually kill all running processes
ps -aux | awk '$1 == "mysql" {print $2}' | xargs -I % sudo kill -9 %

# On Windows
# open TaskManager and any processes names 'mysql' or 'mysqld'
  1. Purge all of the MySQL packages On Ubuntu:
sudo apt purge -y mysql-server
sudo apt autoremove -y

# alternatively use nala (a much better front-end for apt)
sudo nala purge -y mysql-server
sudo nala autoremove -y

# reconfigure dpkg
sudo dpkg --configure -a
  1. Delete all related files
sudo rm -rf /etc/mysql /var/lib/mysql /var/log/mysql
  1. Re-install mysql-server and dependencies
sudo nala update
sudo apt --fix-broken install
sudo nala install mysql-server -y
  1. Pray that it doesn't mess up and check if it's installed
mysql --version

Reset Root User Credentials

  1. Start the database without loading the grant tables or enabling networking
# make sure to add '&' at the end to run process in background
sudo mysqld_safe --skip-grant-tables --skip-networking &
  1. Login as root
mysql -u root
  1. Once logged in, create a password for root user.
    • Using caching_sha2_password over sha256_password and mysql_native_password as it provides the best combination of security and performance.
    • Use mysql_native_password if database is to be used with PHP application(e.g. phpMyAdmin) as caching_sha2_password is know to cause problems.
use mysql;
flush privileges;

ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'new_password';

flush privileges;
quit;
  1. Kill the running process of mysql and start new instance
# find and kill running process
sudo /etc/init.d/mysql stop
ps -aux | awk '$12 == "mysqld_safe" {print $2}' | xargs -I % sudo kill -9 %

# start new process
sudo /etc/init.d/mysql start

# try login with new password
mysql -u root -p
  1. Create a new default user
# sign in as root
mysql -u root -p

# create user
CREATE USER 'kevin'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'password';

# grant privileges
GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'kevin'@'localhost' WITH GRANT OPTION;

flush privileges;

For Windows

  1. Install mysql as service. Start and stop with set net start MySQL
# user elevation using gsudo
sudo 'mysqld --install MySQL --defaults-file="C:\Users\kevin\scoop\apps\mysql\current\my.ini"



links/references: