mysql help

[root@ns bin]# ./mysqldump -u root -p dcb
Enter password:
./mysqldump: Got error: 1045: Access denied for user: 'root@localhost' (Using password: YES) when trying to connect
[root@ns bin]#


I can't figure out what my root is for mysql I used to could backup mysql databases and I can't figure out any root to get it to work any idea of how I can recover a root pass?

OK:

first you need to kill mySQL.

I'm not a ensim guy so, I can only tell you how I would do it under linux. Your going to have to fill in the blanks for ensim specific commands;


shell> kill `cat /mysql-data-directory/hostname.pid';

#this kills the mysql process id
#You must be either the Unix root user or the same user mysqld runs as to do this

#Restart mysqld with the --skip-grant-tables option.

shell> /path/to/mysql/mysql --skip-grant-tables;

#this will start mysql but the process will be in the shell not in the background so you will need another shell to login to the process you started.

another shell> mysql -u root

# at this this point you should be in the mysql shell

mysql> use mysql;
mysql> UPDATE user SET Password=PASSWORD('mynewpassword') WHERE User='root';
mysql> FLUSH PRIVILEGES;

#All done;

Now go to the other shell and hold cntrl down and hit "c" a couple times fast.

Then start mysql the "normal way" and use root to create a user for each database you need for each project!

Example:

login to mysql

create a database called "project1"

mysql> create database project1;
mysql> GRANT ALL PRIVILEGES ON project1.* TO projectuser@localhost IDENTIFIED BY 'some_pass' WITH GRANT OPTION;

This way only projectuser can access the project1 database, this is better for security. You don't want all projects connecting as root!!

but I don't use ensim this specific box is a plesk box.

ttt

Originally posted by Zac2003 but I don't use ensim this specific box is a plesk box.

What ever I use plesk. Same difference. You just have to use "/usr/local/psa/mysql/bin/mysql/" as the paths and by default plesk install the root user is in some cases gone and it's "admin".

Mz

oh sweet just changed root to admin w00t w00t and it worked thx guys

your velcome

Hi guys,

When I type mysql on the shell as root , I am getting the above error "1045: Access denied for user: 'root@localhost' (Using password: YES) "

I have tried the above steps by restarting the mysql with the --skip-grant-tables access :

root@abc [/var/lib/mysql/mysql]# /etc/rc.d/init.d/mysql start --skip-grant-tables;

root@abc [/var/lib/mysql/mysql]# mysql -u root
ERROR 1045: Access denied for user: 'root@localhost' (Using password: YES)

I have checked the /.my.cnf file and the file is on the root folder.

Any advice will be appreciate .

Well I'm not sure about using init.d for sending flag such as --skip-grant-tables; Does this really work?

I think you have to kill the proess init.d creates and then start the mysql server from a root shell. Reset the pass and then kill that shell and start again via init.d.

And if your stiil getting this error for root make sure you have a root user in mySQL! Is this the default install?

Micxz

Hi,

Found the cause of the problem !We have included "Special Character ( &%#@ ) "in our MYSQL root password and this had caused the problem . We reset the password without any Special character and it resolved the issue. Thank God !

Steps :

Make sure root pass is in /root/.my.cnf
This is usually the first root pass the box was given once cpanel is installed.
If it still does not work, you will need to reset the mysqld root pass.
First, stop the mysqld, usually /etc/rc.d/init.d/mysql stop
Then stop chkservd to keep it from interfering with mysqld while you work on it with /etc/rc.d/init.d/chkservd stop
Start up mysqld , but, without the grant tables,
mysqld --skip-grant-tables -u mysql &
Then change the pass..

mysql -u root mysql UPDATE user SET Password=PASSWORD('new_password') WHERE user='root';
FLUSH PRIVILEGES;
Now you just killall -9 mysqld and start it up normally with it's safe_mysqld script..

and update your password in /root/.my.cnf

Remember NOT to include "Special Character" in the password !

Hope this help others !

I wouldn't leave that pass in there just for security reasons. But that's me.