The normal way of setting a password for mysql does not work on opensuse 11.0
localhost:/etc # mysqladmin -u root password xxx
mysqladmin: connect to server at ‘localhost’ failed
error: ‘Access denied for user ‘root’@‘localhost’ (using password: YES)’
Suse is using some strange /etc/my.cnf file which has the following section
The following options will be passed to all MySQL clients
[client] #password =xxx
port = 3306
socket = /var/lib/mysql/mysql.sock
Using this password entry changes the “password: YES” or “password: NO” answer.
Now I am totally lost on configuring mysql and doing such simple tasks as setting of root password.
Any help appreaciated, also any introduction into the how and why suse does it the way it works (which is different to usually).
How far have you have in setting passwords? Normally mysql has no passwords set when it is installed. It also comes with an anonymous user which needs a password adding and both root and the anonymous user have accounts on both hosts.
So you should simply start with -u root and add passwords for both root and the anonymous user or delete the anonymous user on both hosts.
By the way how you log on is not recommended by mysql. Using -u <username> -p
and then typing the password in at the prompt is the recommended way of logging in.
As far as I know, not at all. Especially because all option on the commandline fail.
Not possible
localhost:/etc # mysql -u root
ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: NO)
I do not even want to log in. I have never ever logged in. I do not know a single sql command. I only want to set the password so that I can use phpmyadmin and install websystems such as wordpress.
If it is totally messed - does it help to uninstall mysql?
localhost:/etc # mysqladmin -u root -p password
Enter password:
mysqladmin: connect to server at ‘localhost’ failed
error: ‘Access denied for user ‘root’@‘localhost’ (using password: YES)’
Did you have any data already in /var/lib/mysql from a previous install?
If you have never put any data in mysql before, a faster way to fix the root password is to delete /var/lib/mysql and reinstall the mysql package. Then set the password as documented, using
mysqladmin -u root password new-password
The non-italic words are typed as-is. Don’t use -p this first time. -p is actually -p’’ and implies you have already set a password, which you haven’t, with a fresh install. It’s ok to set the password on the command line this first time.
It sounds like the permissions on /var/lib/mysql might be messed up, possibly from running mysqld manually as root. mysqld is meant to run as the mysql user. Again, to fix this I would delete /var/lib/mysql and reinstall the package. Just reinstalling the package does not touch /var/lib/mysql for the reason that installing the package will not remove existing databases.
I desinstalled mysql, deleted the lib directory and reinstalled mysql. Now I cannot start mysql. The error is the same as it appeared when I started mysql for the first time on this system:
localhost:/var/lib/mysql # /etc/init.d/mysql start
Creating MySQL privilege database…
Installing MySQL system tables…
ERROR: 1062 Duplicate entry ‘localhost-’ for key 1
081212 22:36:07 [ERROR] Aborting
Examine the logs in /var/lib/mysql for more information.
You can try to start the mysqld daemon with:
/usr/sbin/mysqld --skip-grant &
and use the command line tool
/usr/bin/mysql to connect to the mysql
database and look at the grant tables:
shell> /usr/bin/mysql -u root mysql
mysql> show tables
Try ‘mysqld --help’ if you have problems with paths. Using --log
gives you a log in /var/lib/mysql that may be helpful.
The latest information about MySQL is available on the web at MySQL 5.1 is Here!
Please consult the MySQL manual section: ‘Problems running mysql_install_db’,
and the manual section that describes problems on your OS.
Another information source is the MySQL email archive.
Please check all of the above before mailing us!
And if you do mail us, you MUST use the /usr/bin/mysqlbug script!
failed
That was probably the original cause of the problem. Either it’s a bug, or something else was carried over from before. Try deleting /etc/my.cnf in addition to /var/lib/mysql and reinstalling mysql (by --force if necessary) and see if the startup is any better.
It could also be due to duplicate localhost entries elsewhere as the message says. What does your /etc/hosts look like? Check this first before my first suggestion.
#
# hosts This file describes a number of hostname-to-address
# mappings for the TCP/IP subsystem. It is mostly
# used at boot time, when no name servers are running.
# On small systems, this file can be used instead of a
# "named" name server.
# Syntax:
#
# IP-Address Full-Qualified-Hostname Short-Hostname
#
127.0.0.1 localhost
# special IPv6 addresses
::1 localhost ipv6-localhost ipv6-loopback
fe00::0 ipv6-localnet
ff00::0 ipv6-mcastprefix
ff02::1 ipv6-allnodes
ff02::2 ipv6-allrouters
ff02::3 ipv6-allhosts
127.0.0.2 matthias.site matthias
# Router
192.168.0.1 router router
# Printer
192.168.0.120 printer printer
# Netdisk
192.168.0.125 netdisk netdisk
# Local Web
127.0.0.1 www.local-wordpress.de
127.0.0.1 www.local-cqa.de
127.0.0.1 www.local-wp.de
127.0.0.1 www.local-dokuwiki.de
127.0.0.1 www.local-mediawiki.de
127.0.0.1 www.local-matthias.de
127.0.0.1 www.local-wpcqa.de
127.0.0.1 www.local-mysql.de
Strictly speaking, the hosts file should be read left-to-right as:
IP address 127.0.0.1 has the name localhost and aliases something and something and something
not right-to-left as:
The name localhost maps to 127.0.0.1. The name something maps to 127.0.0.1.
Most of the time you will get away with it but there may be the odd program and utility that tries to get a line containing 127.0.0.1 from /etc/hosts and gets the wrong one, or more than one, if you didn’t have a unique line.
I commented all these section, then un- and reinstalled mysql.
Now I still deal with this problem: (see end)
localhost:/var/lib # /etc/init.d/mysql start
Creating MySQL privilege database…
Installing MySQL system tables…
OK
Filling help tables…
OK
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password ‘new-password’
/usr/bin/mysqladmin -u root -h matthias.site password ‘new-password’
Alternatively you can run:
/usr/bin/mysql_secure_installation
which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.
See the manual for more instructions.
You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe &
You can test the MySQL daemon with mysql-test-run.pl
cd mysql-test ; perl mysql-test-run.pl
Please report any problems with the /usr/bin/mysqlbug script!
The latest information about MySQL is available on the web at MySQL 5.1 is Here!
Support MySQL by buying support/licenses at http://shop.mysql.com
Updating MySQL privilege database…
Looking for ‘mysql’ in: /usr/bin/mysql
Looking for ‘mysqlcheck’ in: /usr/bin/mysqlcheck
Running ‘mysqlcheck’…
mysql.columns_priv OK
mysql.db OK
mysql.func OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.host OK
mysql.proc OK
mysql.procs_priv OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
Running ‘mysql_fix_privilege_tables’…
OK
Starting service MySQL
warning: /var/lib/mysql/mysql.sock didn’t appear within 30 seconds
done
which means that mysql is not started:
localhost:/var/lib # /etc/init.d/mysql status
Checking for service MySQL: unused
It could be that /etc/my.cnf specifies that you are using a TCP socket only instead of both a TCP and a Unix socket for communication. Was /etc/my.cnf edited sometime in the past? Try the first part of my suggestion, remove /etc/my.cnf and /var/lib/mysql and forcibly reinstall mysql to get a fresh copy.