mysql 安装后无法登陆mysql的 shell 那mysql&经验：ERROR ): Access denied for user
'root'@'localhost‘
时间： 10:28:20
&&&& 阅读：83
&&&& 评论：
&&&& 收藏：0
标签：&&&&&&&&&&&&&&&&&&&&&&&&&&&
[ ~]# mysql
ERROR ): Access denied for user
(using password: NO)
遇到：ERROR ): Access denied for user
(using password: NO)
网上找了非常多的文章还是没有解决我的问题。
1.首先能够确认的是mysql 命令行 sql命令keyword是不区分大写和小写；在mysql 5.1.51版本号；
2.先将正常启动的mysqld stop掉；
3.再以绕过root口令检查的方式&启动 mysqld。
4.查询 user 表，发现user表是空的，insert 一个 root 用户， 关闭mysqld，正常启动mysqld，用root用户，新的password newpass 就能够登录 mysql shell 了
下面是解决经过：
1.先将正常启动的mysqld stop掉
[ ~]# service mysqld stop
Stopping mysqld:&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& [& OK& ]
2.用绕过root口令检查的方式&启动 mysql server，而且让其在后台执行。
[ ~]# mysqld_safe --user=mysql --skip-grant-tables --skip-networking &
[ ~]# :00:16 mysqld_safe Logging to ‘/var/log/mysqld.log‘.
:00:16 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
3. mysql回车，进入mysql shell，依照前人留下的方法， update user表中的 root 用户的password，结果发现 0 rows affected，再select 细看 user 表是空的；
[ ~]# mysql
Welcome to the MySQL monitor.& C or \g.
Your MySQL connection id is 1
Server version: 5.1.51 Source distribution
mysql& UPDATE user SET PASSWORD=PASSWORD(‘123456‘) where USER=‘root‘;&&&&&&&
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0& Changed: 0& Warnings: 0
mysql&& use mysql
Database changed
+---------------------------+
| Tables_in_mysql&&&&&&&&&& |
+---------------------------+
| columns_priv&&&&&&&&&&&&& |
| db&&&&&&&&&&&&&&&&&&&&&&& |
| event&&&&&&&&&&&&&&&&&&&& |
| func&&&&&&&&&&&&&&&&&&&&& |
| general_log&&&&&&&&&&&&&& |
| help_category&&&&&&&&&&&& |
| help_keyword&&&&&&&&&&&&& |
| help_relation&&&&&&&&&&&& |
| help_topic&&&&&&&&&&&&&&& |
| host&&&&&&&&&&&&&&&&&&&&& |
| ndb_binlog_index&&&&&&&&& |
| plugin&&&&&&&&&&&&&&&&&&& |
| proc&&&&&&&&&&&&&&&&&&&&& |
| procs_priv&&&&&&&&&&&&&&& |
| servers&&&&&&&&&&&&&&&&&& |
| slow_log&&&&&&&&&&&&&&&&& |
| tables_priv&&&&&&&&&&&&&& |
| time_zone&&&&&&&&&&&&&&&& |
| time_zone_leap_second&&&& |
| time_zone_name&&&&&&&&&&& |
| time_zone_transition&&&&& |
| time_zone_transition_type |
| user&&&&&&&&&&&&&&&&&&&&& |
+---------------------------+
23 rows in set (0.00 sec)
mysql& select *
Empty set (0.00 sec)
4.在user表 insert 一个名称为root的用户。&
mysql& insert into user(Host,User,Password,Select_priv,Insert_priv) VALUES(‘localhost‘,‘root‘,PASSWORD(‘pass3‘),‘Y‘,‘Y‘);
Query OK, 1 row affected, 3 warnings (0.00 sec)
5.看到有个root用户了；
mysql& select * from user&&&&&&&&&&&&&&&&
+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
| Host&&&&& | User | Password&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv
| Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | ssl_type
| ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections |
+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
| localhost | root | *35B5E90BC4F5AE5D02ED515DF6B61141F24EDA02 | Y&&&&&&&&&& | Y&&&&&&&&&& | N&&&&&&&&&& | N&&&&&&&&&& | N&&&&&&&&&& | N&&&&&&&& | N&&&&&&&&&& | N&&&&&&&&&&&& | N&&&&&&&&&&& | N&&&&&&&& | N&&&&&&&&& | N&&&&&&&&&&&&&& | N&&&&&&&&& | N&&&&&&&&&
| N&&&&&&&&&&& | N&&&&&&&&& | N&&&&&&&&&&&&&&&&&&&& | N&&&&&&&&&&&&&&& | N&&&&&&&&&&& | N&&&&&&&&&&&&&& | N&&&&&&&&&&&&&&& | N&&&&&&&&&&&&&&& | N&&&&&&&&&&&&& | N&&&&&&&&&&&&&&&&&& | N&&&&&&&&&&&&&&&&& | N&&&&&&&&&&&&&&& | N&&&&&&&&& | N&&&&&&&&&&& |&&&&&&&&&
|&&&&&&&&&&& |&&&&&&&&&&&& |&&&&&&&&&&&&& |&&&&&&&&&&&& 0 |&&&&&&&&&& 0 |&&&&&&&&&&&&&& 0 |&&&&&&&&&&&&&&&&&&& 0 |
+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
1 row in set (0.00 sec)
6 再更新 root 用户的password，也能返回成功了；
mysql& UPDATE user SET Password = PASSWORD(‘newpass‘) WHERE user = ‘root‘;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1& Changed: 1& Warnings: 0
mysql& FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql& exit
7.stop 刚才的 mysql服务，以正常方式又一次启动mysql server。mysql -u root -p& password是newpass
[ ~]# service mysqld stop
:12:24 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
Stopping mysqld:&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& [& OK& ]
[1]+& Done&&&&&&&&&&&&&&&&&&& mysqld_safe --user=mysql --skip-grant-tables --skip-networking
[ ~]# service mysqld start
Starting mysqld:&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& [& OK& ]
[ ~]# mysql
ERROR ): Access denied for user
(using password: NO)
[ ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.& C or \g.
Your MySQL connection id is 4
Server version: 5.1.51 Source distribution
Copyright (c) , Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type ‘‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.
标签：&&&&&&&&&&&&&&&&&&&&&&&&&&&原文：/mengfanrong/p/4590266.html
教程昨日排行
&&国之画&&&& &&&&&&
&& &&&&&&&&&&&&&&
鲁ICP备号-4
打开技术之扣，分享程序人生！Description:mysqld randomly repeatedly crashes or stops responding under heavy testing on ourdual processor alpha box.
The mysqd.log doesn't contain any error messages, and thelast couple of queries aren't any different from the thousands before them.Running the last couple of queries on a restarted server works ok.The program that is doing the testing is connected through Perl DBI and doesn't produceany error messages.
The only error message I have came from a already connected mysql clientafter the server died:mysql& insert into index_words values(&isbn&,5);ERROR 5: Out of memory (Needed
bytes)while the box does have a fairly large amount of ram, it doesn't have quite this much :)The program we're running follows this general processCreates a temporary table:create temporary table temp_sort (itemkey bigint(20) unsigned not null,merchant_id bigint(20) unsigned not null,price decimal(12,2) not null,domain varchar(50) not null,itemname varchar(150) not null,index (price)) type=Selects from several other tables into this one:insert into temp_sort select itemkey, 21, price, domain, itemname from item_21where itemkey in ( *list of primary keys* );Selects from the temporary table with an order by clause:select itemkey, merchant_id, price, domain, itemname from temp_sortorder by (price = 0),And then a drop table on temp_sort is performed.We've tried recompiling using both the installed egcs and Compaq's linux Alpha compiler (the one usedin this bug report).Please respond directly to my email address (), I'm not a list subscriber.ThanksHow-To-Repeat:No fixed pattern.
Lots of random queries from several clients tends to do the trick.Fix: Submitter-Id:Originator:Organization:&organization of PR author (multiple lines)&MySQL support: [none | licence | email support | extended email support ]Synopsis: mysqld crashes or stops respondingSeverity:Priority:Category: mysqlClass:Release: mysql-3.23.14-alpha (Official MySQL RPM)Server: /usr/bin/mysqladmin
Ver 8.3 Distrib 3.23.14-alpha, for unknown-linux-gnu on alphaev6TCX Datakonsult AB, by MontyServer version
3.23.14-alphaProtocol version 10Connection
Localhost via UNIX socketUNIX socket
/data1/mysql/mysql.sockUptime:
17 min 32 secThreads: 1
Questions: 41
Slow queries: 0
Flush tables: 1
Open tables: 2Environment:System: Linux deadbeef1.surfari.int 2.2.14 #7 SMP Thu Jul 2 15:47:39 CST 2150 alpha unknownArchitecture: alphaSome paths:
/usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/ccGCC: Reading specs from /usr/lib/gcc-lib/alpha-redhat-linux/egcs-2.91.66/specsgcc version egcs-2.91.66 /Linux (egcs-1.1.2 release)Compilation info: CC='ccc'
CFLAGS=' -O6 -fomit-frame-pointer'
CXX='cxx'
CXXFLAGS=' -O6 -fomit-frame-pointer
-felide-constructors -fno-exceptions -fno-rtti'
LDFLAGS=''LIBC:lrwxrwxrwx
2020 /lib/libc.so.6.1 -& libc-2.1.3.so-rwxr-xr-x
8070122 Mar 27
2000 /lib/libc-2.1.3.so-rw-r--r--
2000 /usr/lib/libc.a-rw-r--r--
180 Mar 27
2000 /usr/lib/libc.soConfigure command: ./configure
--disable-shared --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --enable-assembler --with-mysqld-user=mysql --with-unix-socket-path=/data1/mysql/mysql.sock --prefix=/ --with-extra-charsets=complex --exec-prefix=/usr --libexecdir=/usr/sbin --sysconfdir=/etc --datadir=/usr/share --localstatedir=/data1/mysql --infodir=/usr/info --includedir=/usr/include --mandir=/usr/man --disable-shared '--with-comment=Official MySQL RPM -- Spec Modified by Maverick for Alpha'Perl: This is perl, version 5.005_03 built for alpha-linux
Search Discussions
Hi!&Maverick& == Maverick
&& writes:Description:Maverick& mysqld randomly repeatedly crashes or stops responding under heavy testing on ourMaverick& dual processor alpha box.
The mysqd.log doesn't contain any error messages, and theMaverick& last couple of queries aren't any different from the thousands before them.Maverick& Running the last couple of queries on a restarted server works ok.Maverick& The program that is doing the testing is connected through Perl DBI and doesn't produceMaverick& any error messages.
The only error message I have came from a already connected mysql clientMaverick& after the server died:mysql& insert into index_words values(&isbn&,5);Maverick& ERROR 5: Out of memory (Needed
bytes)Maverick& while the box does have a fairly large amount of ram, it doesn't have quite this much :)Maverick& The program we're running follows this general processMaverick& Creates a temporary table:Maverick& create temporary table temp_sort (Maverick&
itemkey bigint(20) unsigned not null,Maverick&
merchant_id bigint(20) unsigned not null,Maverick&
price decimal(12,2) not null,Maverick&
domain varchar(50) not null,Maverick&
itemname varchar(150) not null,Maverick&
index (price)) type=Maverick& Selects from several other tables into this one:Maverick& insert into temp_sort select itemkey, 21, price, domain, itemname from item_21Maverick&
where itemkey in ( *list of primary keys* );Maverick& Selects from the temporary table with an order by clause:Maverick& select itemkey, merchant_id, price, domain, itemname from temp_sortMaverick&
order by (price = 0),Maverick& And then a drop table on temp_sort is performed.Maverick& We've tried recompiling using both the installed egcs and Compaq's linux Alpha compiler (the one usedMaverick& in this bug report).Maverick& Please respond directly to my email address (), I'm not a list subscriber.Maverick& ThanksHow-To-Repeat:Maverick&
No fixed pattern.
Lots of random queries from several clients tends to do the trick.Fix:&cut&If you are not using a lot of reconnects, then you can probably get abacktrace for this problem by compiling mysqld with debugging andstart it under gdb (You must have a recent gdb snapshot to get threadsto work on Linux-A
You can find this at:/pub/gdb/snapshots/Some information from our manual:* Try the following:1. Start `mysqld' from `gdb' (or in another debugger).2. Run your test scripts.3. Print the backtrace and the local variables at the 3 lowestlevels. In gdb you can do this with the following commandswhen `mysqld' has crashed inside gdb:backtraceinfo localupinfo localupinfo localWith gdb you can also examine which threads there exists with`info threads' and switch to a specific thread with `thread#', where `#' is the thread id.Regards,Monty
On Tue, 9 May 2000, Michael Widenius wrote:&snip&&cut&If you are not using a lot of reconnects, then you can probably get abacktrace for this problem by compiling mysqld with debugging andstart it under gdb (You must have a recent gdb snapshot to get threadsto work on Linux-A
You can find this at:does the threads debugging package still have the problem that it stopsthe threads from dying?/pub/gdb/snapshots/Some information from our manual:* Try the following:1. Start `mysqld' from `gdb' (or in another debugger).2. Run your test scripts.3. Print the backtrace and the local variables at the 3 lowestlevels. In gdb you can do this with the following commandswhen `mysqld' has crashed inside gdb:backtraceinfo localupinfo localupinfo localWith gdb you can also examine which threads there exists with`info threads' and switch to a specific thread with `thread#', where `#' is the thread id.Some day I hope to be able to find out what the problem is with MySQLhanging under load. I have not tried the latest version but I think I willgive it a spin in the next few days.Alvin Starr
voice: (416)585-9971Interlink Connectivity
(416)585-9974
&Alvin& == Alvin Starr writes:Alvin& On Tue, 9 May 2000, Michael Widenius wrote:Alvin& &snip&&cut&If you are not using a lot of reconnects, then you can probably get abacktrace for this problem by compiling mysqld with debugging andstart it under gdb (You must have a recent gdb snapshot to get threadsto work on Linux-A
You can find this at:Alvin& does the threads debugging package still have the problem that it stopsAlvin& the threads from dying?Yes.
Thats why you only can practically use MySQL with gdb withpersistent connections.(Note that there may be some way to avoid this to happen within gdb, but Ihaven't had time to investigate this...)/pub/gdb/snapshots/&cut&Alvin& Some day I hope to be able to find out what the problem is with MySQLAlvin& hanging under load. I have not tried the latest version but I think I willAlvin& give it a spin in the next few days.Please do!
We would really to want to know more about this!
Sashahas lately run a lot of testing under load on Linux-Intel and the onlyproblem he has found is that under some circumstances, when you havemany threads, Linux 2.2 is really slow to create a new thread.Have you tried to use the Linux-Alpha binary distribution that we havecompiled?
It would be nice to know if this also has this problem!Regards,Monty
On Tue, 9 May 2000, Michael Widenius wrote:&Alvin& == Alvin Starr && writes:&sinp&Alvin& Some day I hope to be able to find out what the problem is with MySQLAlvin& hanging under load. I have not tried the latest version but I think I willAlvin& give it a spin in the next few days.Please do!
We would really to want to know more about this!
Sashahas lately run a lot of testing under load on Linux-Intel and the onlyproblem he has found is that under some circumstances, when you havemany threads, Linux 2.2 is really slow to create a new thread.Just rebuilt and retested 3.23.14. It seems to have a problem when I run anumber of concurrent connections. I ran 25 simple perl programs that do aloop of [ connect, select, disconnect ]. This caused the server to exit. Igot no errors
in the error log just a Shutdown Complete message.Have you tried to use the Linux-Alpha binary distribution that we havecompiled?
It would be nice to know if this also has this problem!I built this one from the SRPM. I will try and grab your binary today andgive it a try also.Regards,MontyAlvin Starr
voice: (416)585-9971Interlink Connectivity
(416)585-9974
&Alvin& == Alvin Starr writes:Alvin& On Tue, 9 May 2000, Michael Widenius wrote:&Alvin& == Alvin Starr && writes:Alvin& &sinp&Alvin& Some day I hope to be able to find out what the problem is with MySQLAlvin& hanging under load. I have not tried the latest version but I think I willAlvin& give it a spin in the next few days.Please do!
We would really to want to know more about this!
Sashahas lately run a lot of testing under load on Linux-Intel and the onlyproblem he has found is that under some circumstances, when you havemany threads, Linux 2.2 is really slow to create a new thread.Alvin& Just rebuilt and retested 3.23.14. It seems to have a problem when I run aAlvin& number of concurrent connections. I ran 25 simple perl programs that do aAlvin& loop of [ connect, select, disconnect ]. This caused the server to exit. IAlvin& got no errors
in the error log just a Shutdown Complete message.Any change you can give us the test scripts so that we can test this onour Linux-alpha ?Have you tried to use the Linux-Alpha binary distribution that we havecompiled?
It would be nice to know if this also has this problem!Alvin& I built this one from the SRPM. I will try and grab your binary today andAlvin& give it a try also.Regards,Monty
On Wed, 10 May 2000, Michael Widenius wrote:&snip&Any change you can give us the test scripts so that we can test this onour Linux-alpha ?You will need to change it to talk to a table of your choice but here itis.
usualy what I do is just keep running test.pl& 25 or 30 times as abit of a torture test.#!/usr/bin/perluse IO::Huse DBI;$DSN = 'dbi:mysql:cos';$count=0;while (1) {;$dbh = DBI-&connect($DSN,'root') or die &Cannot connect to '$DSN'&;$sql = &select * from employee where LAST='Dombey'&;$sth = $dbh-&prepare($sql) or die &DBI Error: $DBI::errstr\n& ;$sth-&execute or die &DBI Error: $DBI::errstr\n&;$myrow = $sth-&fetchrow_$sth-&$dbh-&disconnect();$count
= $count + 1;if(($count % 1000) == 0){print &$count \n&;}}Alvin Starr
voice: (416)585-9971Interlink Connectivity
(416)585-9974
Hi!&Alvin& == Alvin Starr && writes:Alvin& On Wed, 10 May 2000, Michael Widenius wrote:Alvin& &snip&Any change you can give us the test scripts so that we can test this onour Linux-alpha ?Alvin& You will need to change it to talk to a table of your choice but here itAlvin& is.
usualy what I do is just keep running test.pl& 25 or 30 times as aAlvin& bit of a torture test.Alvin& #!/usr/bin/perlAlvin& use IO::HAlvin& use DBI;Alvin&
$DSN = 'dbi:mysql:cos';Alvin&
$count=0;Alvin&
while (1) {;Alvin&
$dbh = DBI-&connect($DSN,'root') or die &Cannot connect to '$DSN'&;Alvin&
$sql = &select * from employee where LAST='Dombey'&;Alvin&
$sth = $dbh-&prepare($sql) or die &DBI Error: $DBI::errstr\n& ;Alvin&
$sth-&execute or die &DBI Error: $DBI::errstr\n&;Alvin&
$myrow = $sth-&fetchrow_Alvin&
$sth-&Alvin&
$dbh-&disconnect();Alvin&
$count
= $count + 1;Alvin&
if(($count % 1000) == 0){Alvin&
print &$count \n&;Alvin&
}Hi!I have now run the above on our Linux Alpha machine (A ds20 fromCompaq):Just to make a table that you also can test, I generated some datawith the ATIS test:test-ATIS --skip-delete --small-testAnd modifed the query to be:select * from flight where flight_code=101908;I saved the program as bench.pl and started is as follows:#!/usr/bin/perlfor ($i=0 ; $i & 40 ; $i++){last if (fork() == 0);}print &starting child $i\n&;exec(&bench.pl&);I did run this for a while, but I couldn't get MySQL to die with this.(I did actually once get mysqld to die, but in this case I had anerror in the above code where it forked +1000 processes which causedthe memory to end in my system and then mysqld died.I did also changed the above to 40 -& 100, but this also workedwithout any problems :(Alvin, if you want, I can give you access to our development machineso that you can try to take down mysqld in it..Regards,Monty
Hi!What MySQL version are you using and what is your Operating System.If it is Linux, what is kernel and glibc version.If it is OSF, what version it is and how have you built MySQL.Regards,Sinisa+----------------------------------------------------------------------+TcX
mysql@tcx.se
|/*/\*\/\*\
/*/ \*\ /*/ \*\ |*|
Sinisa Milivojevic
|/*/ /*/ /*/
|/*/ /*/ /*/\*\/*/
Larnaka, Cyprus
/*/\*\_/*/ \*\_/*/ |*|____
|^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^
Developers Team
|+----------------------------------------------------------------------+
Related Discussions
viewthread |
categories
user style
4 users in discussion
site design / logo & 2017 Grokbase