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

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

Tablespaces error

If you are trying to dump a database in a mysql instance running inside a docker container and you get the following error, despite logging in as a user who has read permission on it, it means that the user you are using does not have the required  PROCESS  privilege to perform certain tasks during the database dump. This is common when attempting to dump information such as  tablespaces , which may require elevated permissions. mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces fix with: mysqldump -u <username> -p --no-tablespaces <database_name> > dump.sql The --no-tablespaces option in mysqldump prevents the tool from including tablespace information in the dump. Details: Tablespace Information : MySQL uses tablespaces to store table data on disk, especially when dealing with InnoDB tables. When dumping a database, mysqldump by default tries to include metadat...
Read more