Log inUsernamePassword
Log me on automatically each visit    
Register
Register
Log in to check your private messages
Log in to check your private messages
SMS Forum Index » SMS User Support

Post new topic   Reply to topic
Lost MySQL root password
View previous topic :: View next topic  
Author Message
riteman
Member


Joined: 01 Feb 2011
Posts: 27

PostPosted: Mon Feb 21, 2011 2:10 am    Post subject: Lost MySQL root password Reply with quote

Hi
as I set up a password for root it seems I made a mistake - at least I was unable to log into MySQL afterwards.
I have tried to restore the password by using the standard steps:

service mysql stop

wait until MySQL shuts down. Then run

mysqld_safe --skip-grant-tables &

then you will be able to login as root with no password.

mysql -uroot mysql

In MySQL command line prompt issue the following command:

UPDATE user SET password=PASSWORD("abcd") WHERE user="root";
FLUSH PRIVILEGES;

At this time your root password is reset to "abcd" and MySQL will now
know the privileges and you'll be able to login with your new password:

mysql -uroot -pabcd mysql

but when I start the Server in step 2 it stops almost immediately.

Any hints what is wrong? I have also stopped Mysql using the Mysql.PID
Back to top
View user's profile Send private message
gerasimos_h
Site Admin


Joined: 09 Aug 2007
Posts: 1757
Location: Greece

PostPosted: Mon Feb 21, 2011 6:58 am    Post subject: Reply with quote

If it's a password issue just stop mysql and do
Code:
echo "set password for root@localhost=password('toor');" > db.txt
mysqld_safe --init-file=db.txt
rm -rf db.txt
mysql -u root -p

If it's not a password issue, what /var/lib/mysql/sms.err says?

gerasimos_h

_________________
Superb! Mini Server Project Manager
http://sms.it-ccs.com
Back to top
View user's profile Send private message Visit poster's website
riteman
Member


Joined: 01 Feb 2011
Posts: 27

PostPosted: Mon Feb 21, 2011 10:59 am    Post subject: Reply with quote

My problem is that I cannot start Mysql as you indicate as it shuts down almost immediately but the sms.err has a statement which may tell you something:

Unknown collation: 'utf8'

before Mysql is shut down after I try to start it in Safe mode.
Back to top
View user's profile Send private message
gerasimos_h
Site Admin


Joined: 09 Aug 2007
Posts: 1757
Location: Greece

PostPosted: Mon Feb 21, 2011 11:04 am    Post subject: Reply with quote

edit /etc/my.cnf

collation-server=utf8
to
collation-server=utf8_general_ci

gerasimos_h

_________________
Superb! Mini Server Project Manager
http://sms.it-ccs.com
Back to top
View user's profile Send private message Visit poster's website
riteman
Member


Joined: 01 Feb 2011
Posts: 27

PostPosted: Mon Feb 21, 2011 12:38 pm    Post subject: Reply with quote

I tried and when entering the last statement I am asked for the password and enter "toor" - the answer is:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysql/mysql.sock' (111)
Back to top
View user's profile Send private message
gerasimos_h
Site Admin


Joined: 09 Aug 2007
Posts: 1757
Location: Greece

PostPosted: Mon Feb 21, 2011 12:51 pm    Post subject: Reply with quote

Stop mysql and verify it with
"ps aux|grep mysql"
You should see only your grep state.
Repeat procedure
Code:
echo "set password for root@localhost=password('toor');" > db.txt
mysqld_safe --init-file=db.txt
mysql -u root -p


Have you changed maybe the host or database permissions from webmin?

If not kill mysql , verify and run
mysqld_safe --skip-grant-tables &
and try changing the password from webmin..

gerasimos_h

_________________
Superb! Mini Server Project Manager
http://sms.it-ccs.com
Back to top
View user's profile Send private message Visit poster's website
riteman
Member


Joined: 01 Feb 2011
Posts: 27

PostPosted: Sat Feb 26, 2011 6:19 pm    Post subject: Reply with quote

I killed Mysql by using the PID file:

kill `cat mysql.pid`

Then I created a file called mysql-init with the following content


UPDATE mysql.user SET Password=PASSWORD('MyNewPassword') WHERE User='root';
FLUSH PRIVILEGES;

and placed it in /var/run/mysql

As you see in the screenshot Mysql shuts down immediately and I found the following error in sms.err:

110226 12:43:04 [ERROR] /usr/libexec/mysqld: File 'mysql-init' not found (Errcode: 2)

so it seems Mysql is looking for the file in that folder. Unfortunately there is a large file in /usr/libexec named mysqld so I cannot create a folder with that name.
I then started Mysql by pointing directly to the file and now sms.err says

110226 17:01:28 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
110226 17:01:28 InnoDB: Initializing buffer pool, size = 8.0M
110226 17:01:28 InnoDB: Completed initialization of buffer pool
110226 17:01:28 InnoDB: Started; log sequence number 0 21161275
110226 17:01:28 [Note] Event Scheduler: Loaded 0 events
110226 17:01:28 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.1.54-log' socket: '/var/run/mysql/mysql.sock' port: 3306 Source distribution

Putty then ends with the cursor on an empty line as if it waits for a command so I sent an "Enter" and Putty then showed the normal prompt.
Going to Webadmin I can now open the Mysql server info page Smile .

BUT I seem to have another problem: sms.err is 17 MB and contains lots of WARNINGS

110225 7:58:59 [Warning] Statement may not be safe to log in statement format. Statement: UPDATE `operators` SET `first_active`='1298617122',`password`='671bd9e030ea198431baf7b3ed15157d',`login_id`='00-30-1B-B7-1A-CE1',`visitor_file_sizes`='a:1:{s:10:\"356aec1650\";a:1:{s:10:\"79cc16bc6c\";N;}}',`typing`='0',`level`='0',`status`='0',`ip`='192.168.0.100',`last_active`='1298617139',`last_chat_allocation`=0 WHERE `id`='911cbb6' LIMIT 1

Any idea what causes this?



Mysql error.jpg
 Description:
Output from Putty
 Filesize:  37.56 KB
 Viewed:  44644 Time(s)

Mysql error.jpg


Back to top
View user's profile Send private message
gerasimos_h
Site Admin


Joined: 09 Aug 2007
Posts: 1757
Location: Greece

PostPosted: Sat Feb 26, 2011 7:21 pm    Post subject: Reply with quote

From "man mysqld"
Code:
  --init-file=file_name

          Read SQL statements from this file at startup. Each statement must
          be on a single line and should not include comments.

So error was correct, you should have run
mysqld_safe --init-file=/var/run/mysql/mysql-init

If you set your mysql root password stop mysql and start it with /etc/rc.d/rc.mysqld script, so it reads /etc/my.cnf and start with binary-logging

http://dev.mysql.com/doc/refman/5.1/en/binary-log-formats.html

gerasimos_h

_________________
Superb! Mini Server Project Manager
http://sms.it-ccs.com
Back to top
View user's profile Send private message Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic    SMS Forum Index » SMS User Support All times are GMT + 2 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You can attach files in this forum
You can download files in this forum

SMS - Superb! Mini Server Project © 2016
Powered by phpBB © 2001, 2002 phpBB Group
iCGstation v1.0 Template By Ray © 2003, 2004 iOptional