Problem on creating root password

[satimis]

Hi folks,

CentOS 4.4 amd64

I have been stuck on creating root password;

# mysqladmin -u root password yourrootsqlpassword[code]
Access denied for user ‘root’@'localhost’ (using password: NO)
[/code]

# su -
did not help.

Pls advise how to locate mysql’s hostname.pid file?

Then to run;
# kill `cat /mysql-data-directory/host_name.pid`
mysqld_safe –skip-grant-tables &
mysqladmin -u root flush-privileges password “newpassword”

OR is there any other way to fix this problem.  TIA


B.R.
satimis

[Maciej Dobrzanski]

In news:9709718.post@...,
satimis <satimis@...> wrote:

> Pls advise how to locate mysql=E2=80=99s hostname.pid file?

Try looking in standard directories such as:

/var/run/mysqld or /var/run/mysql or /var/run
The filename can be in form of 'hostname.pid', but it will rather be named
'mysqld.pid'.

Alternatively, if you have slocate package installed and the database is
running for some time now (a day or so), you may try to run

# locate mysqld.pid
/var/run/mysqld/mysqld.pid

The tool will tell you where the file should be.

> Then to run;
> # kill `cat /mysql-data-directory/host_name.pid`
> mysqld_safe =E2=80=93skip-grant-tables &
> mysqladmin -u root flush-privileges password
> =E2=80=9Cnewpassword=E2=80=9D

The other way to find out the process ID of MySQL is looking at the process
list with 'ps' or 'pstree'.

# ps axf | grep "[m]ysqld"
 9238 ?        Ssl    0:01 /usr/sbin/mysqld --defaults-file=...

# pstree -p mysql
mysqld(9238)-+-{mysqld}(9245)
             |-{mysqld}(9246)
             |-{mysqld}(9247)
             |-{mysqld}(9248)
...

In this case 9238 is the PID you would need to send TERM signal to in order
to stop mysql server:
# kill 20868

Maciek


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=lists@...

[satimis]

Hi Maciej Dobrzanski,

This is a new installation.  I'm now tuning MySQL.

[quote]
Try looking in standard directories such as:

/var/run/mysqld or /var/run/mysql or /var/run
The filename can be in form of 'hostname.pid', but it will rather be named
'mysqld.pid'.
[/quote]

[root@server /]# ls /var/run/mysqld/
mysqld.pid

I got it.


Re running
kill `cat /var/run/mysqld/mysqld.pid`
mysqld_safe –skip-grant-tables &
mysqladmin -u root flush-privileges password mysqlroopassword


I suppose they are 3 lines.


[root@server /]# kill `cat /var/run/mysqld/mysqld.pid`
(No complaint)


[root@server /]# mysqld_safe –skip-grant-tables &
[1] 5119
[root@server /]# Starting mysqld daemon with databases from /var/lib/mysql
STOPPING server from pid file /var/run/mysqld/mysqld.pid
070328 19:39:12  mysqld ended

(It hung here)

Pls advise how to proceed.  TIA

B.R.
satimis

[Maciej Dobrzanski]

In news:9712041.post@...,
satimis <satimis@...> wrote:

> [root@server /]# mysqld_safe =E2=80=93skip-grant-tables &
> [1] 5119
> [root@server /]# Starting mysqld daemon with databases from
> /var/lib/mysql STOPPING server from pid file
> /var/run/mysqld/mysqld.pid 070328 19:39:12  mysqld ended

See the error log for details of why the server stopped. It is usually
located either in /var/log (or its subdirectories, such us /var/log/mysql)
or in mysql data directory, which in your case is /var/lib/mysql. The error
log filename pattern is 'something.err' (hostname.err, mysqld.err, etc.).
The mysqld_safe script should by executed with --skip-grant-tables option:

# mysqld_safe --skip-grant-tables &

Maciek


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=lists@...

[satimis]

Hi Maciej,

[quote]
See the error log for details of why the server stopped. It is usually
located either in /var/log (or its subdirectories, such us /var/log/mysql)
or in mysql data directory, which in your case is /var/lib/mysql. The error
log filename pattern is 'something.err' (hostname.err, mysqld.err, etc.).
[/quote]
# find / -name *.err
find: /proc/4132/task: No such file or directory
find: /var/named/chroot/proc/4132/task: No such file or directory
" end *

# find / -name .err
same result

# ls /var/lib/mysql/
ibdata1  ib_logfile0  ib_logfile1  mysql  mysql.sock  test

# ls /var/lib/mysql/mysql
columns_priv.frm  help_category.frm  help_topic.frm   time_zone.frm              time_zone_transition.frm
columns_priv.MYD  help_category.MYD  help_topic.MYD   time_zone_leap_second.frm  time_zone_transition.MYD
columns_priv.MYI  help_category.MYI  help_topic.MYI   time_zone_leap_second.MYD  time_zone_transition.MYI
db.frm            help_keyword.frm   host.frm         time_zone_leap_second.MYI  time_zone_transition_type.frm
db.MYD            help_keyword.MYD   host.MYD         time_zone.MYD              time_zone_transition_type.MYD
db.MYI            help_keyword.MYI   host.MYI         time_zone.MYI              time_zone_transition_type.MYI
func.frm          help_relation.frm  tables_priv.frm  time_zone_name.frm         user.frm
func.MYD          help_relation.MYD  tables_priv.MYD  time_zone_name.MYD         user.MYD
func.MYI          help_relation.MYI  tables_priv.MYI  time_zone_name.MYI         user.MYI
* end *

# ls /var/log/
acpid            boot.log.1  lastlog    messages.1            rpmpkgs.1         spooler.1       yum.log
anaconda.log     cron        mail       mysqld.log            samba             squid
anaconda.syslog  cron.1      maillog    nvidia-installer.log  scrollkeeper.log  vbox
anaconda.xlog    cups        maillog.1  ppp                   secure            wtmp
audit            dmesg       mcelog     prelink.log           secure.1          Xorg.0.log
boot.log         httpd       messages   rpmpkgs               spooler           Xorg.0.log.old
 * end *


Read /var/log/mysql.log
Can't find anything related.


satimis

[Maciej Dobrzanski]

In news:9712041.post@...,
satimis <satimis@...> wrote:

> [root@server /]# mysqld_safe =E2=80=93skip-grant-tables &
> [1] 5119
> [root@server /]# Starting mysqld daemon with databases from
> /var/lib/mysql STOPPING server from pid file
> /var/run/mysqld/mysqld.pid 070328 19:39:12  mysqld ended

Hmm, I haven't used mysqld_safe script for a long time, but from what I can
see the message "STOPPING server from pid file" only appears if the server
process dies and pid file is not found (it is removed by mysqld as it closes
cleanly). Try running mysqld_safe with additional
parameter: --log-error=/var/lib/mysql/my.err. Then check what has been
written into that file.

Maciek


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=lists@...

[Lucas.CTR.Heuman]

Has anyone found a way to get a result set with the current row return
number in a select statement without using vars?

select concat('Test row ',currentreturnrownum()) as testdata , mytestdata
from mytestdatatable;


+------------------------+-------------------+
|Test row 1             | testdata          |
|Test row 2             | testdata          |
|Test row 3             | testdata          |
|Test row 4             | testdata          |
|Test row 5             | testdata          |
|Test row 6             | testdata          |
|Test row 7             | testdata          |
|Test row 8             | testdata          |
|Test row 9             | testdata          |
+------------------------+-------------------+
(9 rows returned)




Wishing you the best you know you deserve,

______________________
Lucas Heuman
CM Web Developer
SRA International, Inc.
FAA, WJHTC/Bldg 300, 2nd Fl., H33
Atlantic City Int'l Airport, NJ  08405
Phone [phone]

[satimis]

Hi Maciej,

[quote]
Try running mysqld_safe with additional
parameter: --log-error=/var/lib/mysql/my.err. Then check what has been
written into that file.[/quote]

[root@server ~]# kill `cat /var/run/mysqld/mysqld.pid`
No complaint

[root@server ~]# /etc/init.d/mysqld restart
Stopping MySQL:                                          [FAILED]
Starting MySQL:                                            [  OK  ]

# kill `cat /var/run/mysqld/mysqld.pid`
No complaint

[root@server ~]# /etc/init.d/mysqld status
mysqld dead but subsys locked
* end *

[root@server ~]# mysqld_safe --skip-grant-tables & --log-error=/var/lib/mysql/my.err
[1] 4579
bash: --log-error=/var/lib/mysql/my.err: No such file or directory
[root@server ~]# Starting mysqld daemon with databases from /var/lib/mysql

* end *
Hung here.

# find / -name my.err
find: /proc/4175/task: No such file or directory
/var/lib/mysql/my.err
find: /var/named/chroot/proc/4175/task: No such file or directory
* end *

# cat /var/lib/mysql/my.err
A mysqld process already exists at  Wed Mar 28 23:53:04 HKT 2007
070328 23:54:07  mysqld started
070328 23:54:07  InnoDB: Started; log sequence number 0 43634
/usr/libexec/mysqld: ready for connections.
Version: '4.1.20'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  So
urce distribution
070328 23:56:06 [Note] /usr/libexec/mysqld: Normal shutdown

070328 23:56:06  InnoDB: Starting shutdown...
070328 23:56:09  InnoDB: Shutdown completed; log sequence number 0 436
34
070328 23:56:09 [Note] /usr/libexec/mysqld: Shutdown complete

070328 23:56:09  mysqld ended
* end *


satimis

[J.R. Bullington]

                This may sound mundane, but why not use a subselect COUNT?

SELECT COUNT(  
                select concat('Test row ',currentreturnrownum()) as testdata , mytestdata from mytestdatatable;) FROM mytestdatatable;

(I can't test this as I don't have currentreturnrownum()  and cannot find this function in v.5).

You may also want to try the SQL_CALC_FOUND_ROWS  and then FOUND_ROWS()  to get the result.

SELECT SQL_CALC_FOUND_ROWS,  
                 concat('Test row ',currentreturnrownum()) as testdata , mytestdata from mytestdatatable;
SELECT FOUND_ROWS();

J.R.

----------------------------------------

                                From: Lucas.CTR.Heuman@...
Sent: Wednesday, March 28, 2007 10:46 AM
To: mysql@...
Subject: Counting Row Results inside the select

Has anyone found a way to get a result set with the current row return
number in a select statement without using vars?

select concat('Test row ',currentreturnrownum()) as testdata , mytestdata
from mytestdatatable;

+------------------------+-------------------+
|Test row 1             | testdata          |
|Test row 2             | testdata          |
|Test row 3             | testdata          |
|Test row 4             | testdata          |
|Test row 5             | testdata          |
|Test row 6             | testdata          |
|Test row 7             | testdata          |
|Test row 8             | testdata          |
|Test row 9             | testdata          |
+------------------------+-------------------+
(9 rows returned)

Wishing you the best you know you deserve,

______________________
Lucas Heuman
CM Web Developer
SRA International, Inc.
FAA, WJHTC/Bldg 300, 2nd Fl., H33
Atlantic City Int'l Airport, NJ  08405
Phone [phone]

[Lucas.CTR.Heuman]

I'm sorry, currentreturnrownum() doesn't exist .. I was looking for a
function like that so I can make a result set show what row it is in the
return set.

so I can get select currentrownumber , * from anytable;

then I get
1 (rest of date from the *)
2 (rest of date from the *)
3 (rest of date from the *)
4 (rest of date from the *)
5 (rest of date from the *)
6 (rest of date from the *)
7 (rest of date from the *)
8 (rest of date from the *)

I only need to use it for exporting data to another database.





Wishing you the best you know you deserve,

______________________
Lucas Heuman
CM Web Developer
SRA International, Inc.
FAA, WJHTC/Bldg 300, 2nd Fl., H33
Atlantic City Int'l Airport, NJ  08405
Phone [phone]



"J.R. Bullington" <bullijr@...>
03/28/2007 12:31 PM
Please respond to
bullijr@...


To
<mysql@...>
cc

Subject
re: Counting Row Results inside the select







                                 This may sound mundane, but why not use a
subselect COUNT?

SELECT COUNT(
                                 select concat('Test row
',currentreturnrownum()) as testdata , mytestdata from mytestdatatable;)
FROM mytestdatatable;

(I can't test this as I don't have currentreturnrownum()  and cannot find
this function in v.5).

You may also want to try the SQL_CALC_FOUND_ROWS  and then FOUND_ROWS() to
get the result.

SELECT SQL_CALC_FOUND_ROWS,
                                  concat('Test row
',currentreturnrownum()) as testdata , mytestdata from mytestdatatable;
SELECT FOUND_ROWS();

J.R.

----------------------------------------

                                                                 From:
Lucas.CTR.Heuman@...
Sent: Wednesday, March 28, 2007 10:46 AM
To: mysql@...
Subject: Counting Row Results inside the select

Has anyone found a way to get a result set with the current row return
number in a select statement without using vars?

select concat('Test row ',currentreturnrownum()) as testdata , mytestdata
from mytestdatatable;

+------------------------+-------------------+
|Test row 1             | testdata          |
|Test row 2             | testdata          |
|Test row 3             | testdata          |
|Test row 4             | testdata          |
|Test row 5             | testdata          |
|Test row 6             | testdata          |
|Test row 7             | testdata          |
|Test row 8             | testdata          |
|Test row 9             | testdata          |
+------------------------+-------------------+
(9 rows returned)

Wishing you the best you know you deserve,

______________________
Lucas Heuman
CM Web Developer
SRA International, Inc.
FAA, WJHTC/Bldg 300, 2nd Fl., H33
Atlantic City Int'l Airport, NJ  08405
Phone [phone]

[satimis]

Hi Maciej,

I found that I made a mistake from the beginning

# mysqladmin -u root password yourrootsqlpassword
leaving out "--" before "password"


Retried as follows;

[root@server ~]# mysqladmin -u root --password mypassword
Enter password:


Typing either "mypassword" or "YES" prompted;
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: YES)'
* end *

Still failed


satimis

[chris smith-9]

> Retried as follows;
>
> [root@server ~]# mysqladmin -u root --password mypassword
> Enter password:
>
>
> Typing either "mypassword" or "YES" prompted;
> mysqladmin: connect to server at 'localhost' failed
> error: 'Access denied for user 'root'@'localhost' (using password: YES)'
> * end *

That is asking for your *old* password.

mysqladmin --help tells you this.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=lists@...

[satimis]

--- Chris <dmagick@...> wrote:

>
> > Retried as follows;
> >
> > [root@server ~]# mysqladmin -u root --password mypassword
> > Enter password:
> >
> >
> > Typing either "mypassword" or "YES" prompted;
> > mysqladmin: connect to server at 'localhost' failed
> > error: 'Access denied for user 'root'@'localhost' (using password:
> YES)'
> > * end *
>
> That is asking for your *old* password.

I did not create any password before.  This is a new installation.  I'm
tuning it.


B.R.
Stephen Liu

Send instant messages to your online friends http://uk.messenger.yahoo.com 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=lists@...

[satimis]

> Typing either "mypassword" or "YES" prompted;
> mysqladmin: connect to server at 'localhost' failed
> error: 'Access denied for user 'root'@'localhost' (using password: YES)'
> * end *

That is asking for your *old* password.
[/quote]

I did not create password before.  This is a new installation.  I'm tuning it.

Stephen Liu

[chris smith-9]

satimis wrote:

>
>> Typing either "mypassword" or "YES" prompted;
>> mysqladmin: connect to server at 'localhost' failed
>> error: 'Access denied for user 'root'@'localhost' (using password: YES)'
>> * end *
>
> That is asking for your *old* password.
> [/quote]
>
> I did not create password before.  This is a new installation.  I'm tuning
> it.

Try this:

mysqladmin -u root password xyz

That will change your password to 'xyz'.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=lists@...

[satimis]

--- Chris <dmagick@...> wrote:

- snip -

> Try this:
>
> mysqladmin -u root password xyz
>
> That will change your password to 'xyz'.
>


Before received this email I tried;

[root@server ~]# mysqladmin -u root -p
mysqladmin  Ver 8.41 Distrib 4.1.20, for redhat-linux-gnu on x86_64
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Administration program for the mysqld daemon.
Usage: mysqladmin [OPTIONS] command command....
  -c, --count=#       Number of iterations to make. This works with -i
                      (--sleep) only.
  -#, --debug[=name]  Output debug log. Often this is 'd:t:o,filename'.
  -f, --force         Don't ask for confirmation on drop database; with
                      multiple commands, continue even if an error
occurs.
  -C, --compress      Use compression in server/client protocol.
  --character-sets-dir=name
                      Directory where character sets are.
  --default-character-set=name
                      Set the default character set.
  -?, --help          Display this help and exit.
  -h, --host=name     Connect to host.
  -p, --password[=name]
.....
.....
.....
[root@server ~]#


[root@server ~]# mysqladmin -u root password xyz
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'
* end *


Stephen

Send instant messages to your online friends http://uk.messenger.yahoo.com 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=lists@...

[chris smith-9]

> [root@server ~]# mysqladmin -u root password xyz
> mysqladmin: connect to server at 'localhost' failed
> error: 'Access denied for user 'root'@'localhost' (using password: NO)'
> * end *


So you *have* set the root password before.

 From your previous emails:

# mysqladmin -u root password yourrootsqlpassword

That set the password to "yourrootsqlpassword".



If you need to reset it, see documentation:

http://dev.mysql.com/doc/refman/4.1/en/resetting-permissions.html


As a final recourse, remove & re-install mysql from your server.

If you have no data, then that might be the quickest & easiest.

If you do have data, that's something else entirely.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=lists@...

[Maciej Dobrzanski]

In news:9717062.post@...,
satimis <satimis@...> wrote:

> [root@server ~]# /etc/init.d/mysqld restart
> Stopping MySQL:                                          [FAILED]
> Starting MySQL:                                            [  OK  ]

Ok, lets try it a different way, using only the above script to start/stop
the database. Find your MySQL configuration file. It's called 'my.cnf' and
usually is located in either /etc or /etc/mysql directory. Edit the file and
in [mysqld] section add:

skip-grant-tables

Then, execute the init script:

# /etc/init.d/mysqld restart

The database should start without privileges.
After setting up password, remove 'skip-grant-tables' parameter from the
config file and restart MySQL again.

Maciek


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=lists@...

[satimis]

--- Chris <dmagick@...> wrote:

- snip -

> So you *have* set the root password before.
>
>  From your previous emails:
>
> # mysqladmin -u root password yourrootsqlpassword
>
> That set the password to "yourrootsqlpassword".
>
>
>
> If you need to reset it, see documentation:
>
> http://dev.mysql.com/doc/refman/4.1/en/resetting-permissions.html

I tried learning resetting the password according to above URL.  I
can't find "set" command on this box.  I don't know which package
provides it so finally I surrendered.



> As a final recourse, remove & re-install mysql from your server.
>
> If you have no data, then that might be the quickest & easiest.

I have no data as this is a new installation for learning.


# yum groupremove mysql
It went throught w/o complaint.


# yum install mysql mysql-devel mysql-server
Also went through w/o complaint


# nano /etc/init.d/mysqld
changed;
restart(){
    stop
    start
}
*end*


to;
restart(){
    stop
         sleep 3
    start
}
*end*


# chkconfig --levels 235 mysqld on
No complaint

# /etc/init.d/mysqld start
Starting MySQL:         [  OK  ]

# netstat -tap
tcp        0      0 *:mysql      *:*
.....
*end*


# mysqladmin -u root password myrootpassword
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'
*end*


Still the same


B.R.
satimis

Send instant messages to your online friends http://uk.messenger.yahoo.com 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=lists@...

[Duncan Hill-3]

On Thursday 29 March 2007 11:38:21 Stephen Liu wrote:

> > http://dev.mysql.com/doc/refman/4.1/en/resetting-permissions.html
>
> I tried learning resetting the password according to above URL.  I
> can't find "set" command on this box.  I don't know which package
> provides it so finally I surrendered.

'set' is a mysql command, used inside the mysql command line tool, not from
your shell prompt.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=lists@...