reset root (overarching) password for mysql


create a file called /var/lib/mysql/reset.txt file put the "alter user" statement inside it, like so:


echo "

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'abcd1234*;

 FLUSH PRIVILEGES;
" > /var/lib/mysql/reset.txt


run


service mysql stop

mkdir -p /var/run/mysqld

chown mysql:mysql /var/run/mysqld

chmod 777 /var/run/mysqld # you can make this 755 some other time

mysqld --init-file=/var/lib/mysql/reset.txt  --user=mysql


Now do this to make it remember that you can login as root, this will let you just run “mysql” to run it. Security risk obviously.


user=`whoami`

su -c "mysql_config_editor set --host=localhost --user=root --password" -s /bin/bash “$user"


When it asks for a password, give your mysql password, e.g. abcd1234*

Comments

Post a Comment

Popular posts from this blog

dump a specific table

Sometimes you just want a specific mysql table. mysqldump -u [username] -p --no-create-info source_database_name source_table_name > saved_database_content.sql You can then load that content into a new database: mysql -u [username] -p target_database_name < saved_database_content.sql
Read more

mysql says you can't login as root but you have definitely got the right password

If mysql says you can't login as root but you have definitely got the right password and you have just installed it for the first time, it can be a nuisance. Do the below as user root. My suggestion is: 1. If you have existing databases, and want to keep them, back them up with mysqldump.       mysqldump -u user_with_access -pPassword database_to_rescue > database_to_rescue.sql Assuming that you have at least one user account, e.g. "user_with_access", who can read the database called “database_to_rescue". 2. Erase the mysql directory and start over:       service mysql stop rm -rf /var/lib/mysql/* 3. Re-run mysqld initialisation to set the password to blank: mkdir -p /var/run/mysqld chown -R /var/run/mysqld chmod 777 /var/run/mysqld # you can change this back to 755 later mysqld --initialize-insecure --user=mysql If it says it can't find the socket in /var/run/mysqld, repeat the commands mkdir-chown above. 4. It will then set up mysql from scratch w...
Read more

completely erase and reinstall mysql script.

 Warning: this will delete ALL your databases. After running this script it will let you automatically run mysql without a password (as root). Note this  is a security risk so do not run this on a production server. #!/bin/bash set -e # Function to remove MySQL and clean up remove_mysql() {     echo "Removing MySQL server and packages..."     sudo apt-get remove -y mysql-server mysql-client mysql-common     echo "Removing MySQL directories..."     sudo rm -rf /etc/mysql /var/lib/mysql /var/log/mysql /var/run/mysqld     # Clean up any residual packages and update package info     sudo apt-get autoremove -y     sudo apt-get clean } # Function to ensure MySQL configuration file exists ensure_mysql_config() {     if [ ! -f /etc/mysql/mysql.cnf ]; then         echo "Creating MySQL configuration file..."         echo "[client]" | sudo tee /etc/mysql/mysql.cnf ...
Read more