How do you programmatically reset the root password in MySQL, specifically on Ubuntu/Debian?
There are seemingly a lot of questions like this, but few, if any of the solutions presented work, and all assume you run the examples manually.
I'm trying to write an installation script for an application, and I'm finding it nearly impossible to automatically set the MySQL password. My target OS is Ubuntu 16, which comes with MySQL 5.7.17. The official docs list two methods for changing the root password, and unfortunately, neither of them work.
The first method presented is to put your password reset SQL in a temporary file, stop mysql, and then run:
mysqld_safe --init-file=/home/me/mysql-init &
Unfortunately, the MySQL 5.7.17 no longer lists a --init-file option, and although the command doesn't throw an error, it has no effect on the root password.
The second method presented is to stop mysql, and launch it in a mode that will let any local user to login and do anything, but running:
sudo mkdir -p /var/run/mysqld
sudo chown mysql /var/run/mysqld; mysqld_safe --skip-grant-tables &
sudo mysqld_safe --skip-grant-tables &
sleep 10
mysql --execute="FLUSH PRIVILEGES; ALTER USER 'root'@'localhost' IDENTIFIED BY 'mysupersecretpassword'; FLUSH PRIVILEGES;"
However, 9 times out of 10, this fails with the error:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock'
I can't quite tell what's causing this. At first, I thought it was because I wasn't giving mysqld_safe enough time to start up, but even if I raise the sleep value, I still routinely get the error. Every time I manually run the same commands, it always succeeds, but when I run my script, they fail, and the error.log shows nothing.
A method I used previously, was to pre-seed dpkg like:
sudo dpkg --configure -a
sudo debconf-set-selections <<< 'mysql-server mysql-server/root_password password mysupersecretpassword'
sudo debconf-set-selections <<< 'mysql-server mysql-server/root_password_again password mysupersecretpassword'
so it auto-fills the password when the mysql-server package is initially installed. However, the Ubuntu 16 package no longer seems to prompt for the root password when first installed.
How do I fix this? This there a more reliable way to programmatically reset MySQL's root password?
