MYSQL Root password

You need to login as root to change root's password. Since you don't specify a username you don't have sufficient privileges.

Therefore, try:

/usr/local/mysql/bin/mysql -u root

My (fink) MySQL installation says the following:
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/sw/bin/mysqladmin -u root password 'new-password'
/sw/bin/mysqladmin -u root -h big.mac password 'new-password'

Mihalis.

(1) If you already gave your root user a password, you need to type:
<pre>/usr/local/mysql/bin/mysql -u root -p</pre>to be prompted to give a password.

(2) You're not using a Fink package, so you'd type:
<pre>/usr/local/mysql/bin/mysqladmin -u root password 'new-password'</pre>(3) Typing /usr/local/mysql/bin/mysql and getting into MySQL means that you can get in, at least as an anonymous user. Most likely, if you type "show databases;", you will see one listing: "test". (When you're at the MySQL prompt (->), you need to type a semicolon to end a command. That is why you keep getting "->" -- once you type a semicolon, MySQL will try to execute the command you've given, which can be broken up over many lines.

OK, I'm a little confused. A default installation of MySQL comes with a root user that has no password assigned to it. If you just installed MySQL, you should be able to log into MySQL as root with no password and change the password. Or, you should be able to use mysqladmin with no password and change the password.

Or, you've already changed root's password once before and you're trying to do it again now. In which case you need to supply a password in able to change it.

If you continue to run into problems and it seems like you can't log into MySQL as root, you have two options. One option is to start MySQL without the privilege tables, which would allow you to log in anonymously and give your root user a password. The other option is to scrap this installation and reinstall it from the package (just delete /usr/local/mysql and /usr/local/mysql-<version>, as well as, I think, the Receipt package from /Library/Receipts/mysql-<version>.pkg). Then, first thing after you reinstall it, do the mysqladmin -u root password 'new-password' bit. It should work.

To start without the privileges table, first you need to shut down MySQL. Then, restart it with:
<pre>/usr/local/mysql/bin/mysqld_safe --skip-grant-tables -u root &</pre>After it starts, just enter:<pre>/usr/local/mysql/bin/mysql</pre>and it should drop you into MySQL with administrative privileges (if I'm wrong about that, then add "-u root" to the end of it -- I'm going from memory here). You can confirm this by typing "SHOW DATABASES;". You want to make sure you can see the "mysql" database in the list. If you can, then you can do the initial query you entered above ("UPDATE mysql.user SET Password = PASSWORD(' ****') WHERE User = 'root';"), then type "FLUSH PRIVILEGES;" and quit MySQL "quit;".

Then, restart MySQL normally and your root user should have the password you specified and administrative privileges.

OK, so you already removed your MySQL installation (the answer to your privileges problem by the way would have been perhaps " sudo /usr/local/mysql/bin/mysqld_safe --skip-grant-tables -u root &").

It just looks like MySQL is not currently started. At this point though, you don't need to start without grant tables -- just start it up normally, like this:

"sudo /Library/StartupItems/MySQLCOM/MySQLCOM start"


Then you should be able to log into MySQL as root:

"/usr/local/mysql/bin/mysql -u root"


Then you should be able to change root's password:

"UPDATE mysql.user SET Password = PASSWORD( 'new-password' ) WHERE User = 'root';"
"FLUSH PRIVILEGES;"
"quit;"


Now you should be able to log into MySQL as root, but with a password:

"/usr/local/mysql/bin/mysql -u root -p"

Wesley, your advice has been the best i've come across so far.
Everyone else assumes too much.
Thanks

I've tried to follow the above advice.

I've completely deleted my MySQl on OS X Server 10.4.7 and reinstalled using the lastest package "mysql-standard-5.0.24a-osx10.4-powerpc.dmg".

I just cannot get mysql to work properly, to set my root password.
See my first attempts with Terminal below (No.1)...

MySQL Adminsitaror and CoCoa SQL connecs with no username or password.

I've also tried to follow marcs advice at http://www.entropy.ch/software/macosx/mysql/ but to no avail.
See my second attemps with Terminal below (No.2)...

If I can crack how to reinstall and setup MySQL on OS X Server 10.4.7 then I'll have a good Disaster Recovery Plan, I hope you can help, thanks.

----
No.1
---------------------------------------------------------------
Last login: Fri Sep 22 17:26:02 on ttyp1
Welcome to Darwin!
Server:~ admin$
Server:~ admin$ sudo /usr/local/mysql/bin/mysqld_safe --skip-grant-tables -u root &
[1] 1014
Server:~ admin$ A mysqld process already exists

[1]+ Exit 1 sudo /usr/local/mysql/bin/mysqld_safe --skip-grant-tables -u root
Server:~ admin$ sudo /Library/StartupItems/MySQLCOM/MySQLCOM stop
Stopping MySQL database server
Server:~ admin$ sudo /usr/local/mysql/bin/mysqld_safe --skip-grant-tables -u root &
[1] 1066
Server:~ admin$ Starting mysqld daemon with databases from /usr/local/mysql/data

Server:~ admin$ sudo /Library/StartupItems/MySQLCOM/MySQLCOM start
Starting MySQL database server
Server:~ admin$ /usr/local/mysql/bin/mysql -u root
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/mysql/mysql.sock' (13)
Server:~ admin$ /usr/local/mysql-standard-5.0.24a-osx10.4-powerpc/bin/mysql -u root
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/mysql/mysql.sock' (13)
Server:~ admin$



----
No.2
---------------------------------------------------------------

Last login: Fri Sep 22 17:26:12 on ttyp1
Welcome to Darwin!
Server:~ admin$ sudo find /usr/local/mysql/data -type f -exec rm {} ';'
Password:
Server:~ admin$
Server:~ admin$ sudo hostname 127.0.0.1
Server:~ admin$ cd /usr/local/mysql
Server:/usr/local/mysql admin$ sudo ./scripts/mysql installdb
Installing all prepared tables
060922 17:35:30 [Warning] Setting lower case_tablenames=2 because file system for /usr/local/mysql-standard-5.0.24a-osx10.4-powerpc/data/ is case insensitive
060922 17:35:30 [Warning] No argument was provided to --log-bin, and --log-bin-index was not used; so replication may break when this MySQL server acts as a master and has his hostname changed!! Please use '--log-bin=127-bin' to avoid this problem.
Fill help tables
060922 17:35:31 [Warning] Setting lower case_tablenames=2 because file system for /usr/local/mysql-standard-5.0.24a-osx10.4-powerpc/data/ is case insensitive
060922 17:35:31 [Warning] No argument was provided to --log-bin, and --log-bin-index was not used; so replication may break when this MySQL server acts as a master and has his hostname changed!! Please use '--log-bin=127-bin' to avoid this problem.

To start mysqld at boot time you have to copy support-files/mysql.server
to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
./bin/mysqladmin -u root password 'new-password'
./bin/mysqladmin -u root -h 127.0.0.1 password 'new-password'
See the manual for more instructions.

You can start the MySQL daemon with:
cd . ; ./bin/mysqld_safe &

You can test the MySQL daemon with the benchmarks in the 'sql-bench' directory:
cd sql-bench ; perl run-all-tests

Please report any problems with the ./bin/mysqlbug script!

The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at http://shop.mysql.com
Server:/usr/local/mysql admin$ sudo chown -R mysql data/
Server:/usr/local/mysql admin$ /usr/local/mysql/bin/mysqladmin -u root password operate
/usr/local/mysql/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/var/mysql/mysql.sock' (13)'
Check that mysqld is running and that the socket: '/var/mysql/mysql.sock' exists!
Server:/usr/local/mysql admin$

So what's your current state on the Server? You still have Apple's version of MySQL installed, yes? You didn't delete any of that out? Is it set to try to start on boot? You can see this in the /etc/hostconfig file -- if "MYSQL" = "-YES-", then Apple's version of MySQL will try to boot on startup. Switch that to NO if you're not interested in using Apple's version.

Next, since you don't have any data in place that you need to worry about right now, let's start fresh. Trash your MySQL installation in /usr/local/mysql-<version> and the symlink /usr/local/mysql. (If you do have data, move the "data" directory out before you delete everything.)

Download/install the latest version from MySQL, along with the StartupItem they provide.

NOTE: Using this version means two things: (1) any Apple-provided MySQL tools won't work with this; and (2) you have to type the full path for your MySQL commands, "/usr/local/mysql/...".

After it's installed, and the startup item is installed, you can start MySQL. Unless you are doing something special on startup, you can start it quite simply with "sudo /Library/StartupItems/MySQLCOM/MySQLCOM start".

Now that it's running, it's running with a root user with no password. On the command line, type
"/usr/local/mysql/bin/mysql -u root"

That should get you into MySQL. Then, at the MySQL prompt, type:
"UPDATE mysql.user SET Password = PASSWORD( 'whatever-you-want-your-password-to-be' ) WHERE User = 'root';"
After that runs, type (still at the MySQL prompt):
"FLUSH PRIVILEGES;"
Then "quit" out of MySQL. Now try logging into MySQL again like this:
"/usr/local/mysql/bin/mysql -u root -p"
It should prompt you for a password and then let you in as root.

At this point, I'm thinking that the MySQL socket file will be residing in "/tmp/mysql.sock", not in "/var/mysql/mysql.sock". If you're planning on using Apple's version of PHP, you might have to change the MySQL socket location in PHP's INI file to match the /tmp location. Otherwise, you should be good to go.

You should also see "MySQLCOM=-YES-" in the /etc/hostconfig file now as well, so MySQL should start on boot.