CODEWITHSUNDEEP
mysql
user435421
user435421

Reputation: 927

How change MySQL instance root user password?

I used to have a single MySQL server instance on Ubuntu but for security reasons I decided to use two instances since both test and prod applications will be open for web users. Therefore I configured two MySQL server instances and ran mysqld_multi start to setup the databases. My /etc/mysql/my.cnf:

[mysqld0]
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
user=mysql
pid-file=/var/run/mysqld/mysqld0.pid
socket=/var/run/mysqld/mysqld0.sock
port=3306
datadir=/home/prod/data
log_error=/var/log/mysql/error0.log

[mysqld1]
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
user=mysql
pid-file=/var/run/mysqld/mysqld1.pid
socket=/var/run/mysqld/mysqld1.sock
port=3307
datadir=/home/test/data
log_error=/var/log/mysql/error1.log

Using mysqld_multi start 0,1 I start both instances but can not access them with root user - geting access denied for root on localhost.

I have tried to follow these instructions (providing additional --socket parameter) https://support.rackspace.com/how-to/mysql-resetting-a-lost-mysql-root-password/ as they did help me before but for no use.

When connected to MySQL in safe mode I also see previous users that were set up in the original instance.

Maybe I understand multi MySQL server instances and how they should work in the wrong way? Does all instances share same users? How do I get access to the newly created instances?

Upvotes: 0

Views: 901

Answers (2)

user435421
user435421

Reputation: 927

Had to run mysqld_safe with additional parameters - providing dirs for each instance mysqld_safe --mysqld=/usr/sbin/mysqld --basedir=/home/test/data/mysql --datadir=/home/test/data --skip-grant-tables. After that everything went as expected

Upvotes: 1

NickDim
NickDim

Reputation: 17

Here is how you can do it in SQL

For MySQL 5.7.6 or later you can do

ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPassword';

Or for MySQL 5.7.5 or earlier you can do

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPassword');

Upvotes: 0

Related Questions