View previous topic :: View next topic |
Author |
Message |
riteman Member
Joined: 01 Feb 2011 Posts: 27
|
Posted: Mon Feb 21, 2011 2:10 am Post subject: Lost MySQL root password |
|
|
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 |
|
gerasimos_h Site Admin
Joined: 09 Aug 2007 Posts: 1757 Location: Greece
|
Posted: Mon Feb 21, 2011 6:58 am Post subject: |
|
|
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 |
|
riteman Member
Joined: 01 Feb 2011 Posts: 27
|
Posted: Mon Feb 21, 2011 10:59 am Post subject: |
|
|
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 |
|
gerasimos_h Site Admin
Joined: 09 Aug 2007 Posts: 1757 Location: Greece
|
Posted: Mon Feb 21, 2011 11:04 am Post subject: |
|
|
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 |
|
riteman Member
Joined: 01 Feb 2011 Posts: 27
|
Posted: Mon Feb 21, 2011 12:38 pm Post subject: |
|
|
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 |
|
gerasimos_h Site Admin
Joined: 09 Aug 2007 Posts: 1757 Location: Greece
|
Posted: Mon Feb 21, 2011 12:51 pm Post subject: |
|
|
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 |
|
riteman Member
Joined: 01 Feb 2011 Posts: 27
|
Posted: Sat Feb 26, 2011 6:19 pm Post subject: |
|
|
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 .
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?
Description: |
|
Filesize: |
37.56 KB |
Viewed: |
45278 Time(s) |
![Mysql error.jpg](files/mysql_error_100.jpg)
|
|
|
Back to top |
|
gerasimos_h Site Admin
Joined: 09 Aug 2007 Posts: 1757 Location: Greece
|
Posted: Sat Feb 26, 2011 7:21 pm Post subject: |
|
|
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 |
|
|