How to set mysql password

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.

Try this:


mysqladmin -u root -p password <new password here>

mysqladmin will ask you for the root password. Leave the Enter password: empty If you haven’t changed root password before.
Good luck.

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?

Not possible:

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)’

Where did you install mysql from? What version of openSUSE are you running?
have you run the command “/etc/init.d/mysql status” as root?

Installed is

localhost:/etc # rpm -qa | grep mysql
libmysqlclient15-5.0.51a-27.1
libqt4-sql-mysql-4.4.3-28.1
mysql-tools-5.0.51a-27.1
libmysqlclient_r15-5.0.51a-27.1
perl-DBD-mysql-4.006-41.1
mysql-gui-tools-5.0r12-195.1
mysql-client-5.0.51a-27.1
mysql-5.0.51a-27.1
mysql-query-browser-5.0r12-195.1
mysql-workbench-5.0r12-195.1
php5-mysql-5.2.6-0.6

mysql is installed from Main Repository (11.0)
and ‘status’ results in

localhost:/etc # /etc/init.d/mysql status
Checking for service MySQL: running

If (however) a password for root would be set, how could I reset that?

If nothing helps, maybe something irregular happened…
Please copy and paste this text:
lost mysql root password
into Google.

I have been using this guide:
Linux - Vergessenes root-Kennwort neu festlegen

(german, but the commands should be clear)

It worked, but now I cannot start mysql:

localhost:/var/lib/mysql # /etc/init.d/mysql start
Starting service MySQL warning: /var/lib/mysql/mysql.sock didn’t appear within 30 seconds
done

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

081212 22:36:07 [Note] /usr/sbin/mysqld: Shutdown complete

Installation of system tables failed!

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.

Code of hosts:


#
# 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

Is there anything problematic with this file?

I think all those local- lines should be folded into one 127.0.0.1 entry:

127.0.0.1 localhost www.local-wordpress.de etc etc

That has not been an issue for as long as I use Linux, which is since suse 6.x. But I can disable/change them.

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.

I had deleted /etc/my.cnf

it now (reinstalled fresh from mysql) looks like this:

localhost:/var/lib # cat /etc/my.cnf

Example MySQL config file for medium systems.

This is for a system with little memory (32M - 64M) where MySQL plays

an important part, or systems up to 128M where MySQL is used together with

other programs (such as a web server)

You can copy this file to

/etc/my.cnf to set global options,

mysql-data-dir/my.cnf to set server-specific options (in this

installation this directory is /var/lib/mysql) or

~/.my.cnf to set user-specific options.

In this file, you can use all long options that a program supports.

If you want to know which options a program supports, run the program

with the “–help” option.

The following options will be passed to all MySQL clients

[client]
#password = your_password
port = 3306
socket = /var/lib/mysql/mysql.sock

Here follows entries for some specific programs

The MySQL server

[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

Don’t listen on a TCP/IP port at all. This can be a security enhancement,

if all processes that need to connect to mysqld run on the same host.

All interaction with mysqld must be made via Unix sockets or named pipes.

Note that using this option without enabling named pipes on Windows

(via the “enable-named-pipe” option) will render mysqld useless!

#skip-networking

Replication Master Server (default)

binary logging is required for replication

log-bin=mysql-bin

required unique id between 1 and 2^32 - 1

defaults to 1 if master-host is not set

but will not function as a master if omitted

server-id = 1

Replication Slave (comment out master section to use this)

To configure this host as a replication slave, you can choose between

two methods :

1) Use the CHANGE MASTER TO command (fully described in our manual) -

the syntax is:

CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,

MASTER_USER=<user>, MASTER_PASSWORD=<password> ;

where you replace <host>, <user>, <password> by quoted strings and

<port> by the master’s port number (3306 by default).

Example:

CHANGE MASTER TO MASTER_HOST=‘125.564.12.1’, MASTER_PORT=3306,

MASTER_USER=‘joe’, MASTER_PASSWORD=‘secret’;

OR

2) Set the variables below. However, in case you choose this method, then

start replication for the first time (even unsuccessfully, for example

if you mistyped the password in master-password and the slave fails to

connect), the slave will create a master.info file, and any later

change in this file to the variables’ values below will be ignored and

overridden by the content of the master.info file, unless you shutdown

the slave server, delete master.info and restart the slaver server.

For that reason, you may want to leave the lines below untouched

(commented) and instead use CHANGE MASTER TO (see above)

required unique id between 2 and 2^32 - 1

(and different from the master)

defaults to 2 if master-host is set

but will not function as a slave if omitted

#server-id = 2

The replication master for this slave - required

#master-host = <hostname>

The username the slave will use for authentication when connecting

to the master - required

#master-user = <username>

The password the slave will authenticate with when connecting to

the master - required

#master-password = <password>

The port the master is listening on.

optional - defaults to 3306

#master-port = <port>

binary logging - not required for slaves, but recommended

#log-bin=mysql-bin

Point the following paths to different dedicated disks

#tmpdir = /tmp/
#log-update = /path-to-dedicated-directory/hostname

Uncomment the following if you are using BDB tables

#bdb_cache_size = 4M
#bdb_max_lock = 10000

Uncomment the following if you are using InnoDB tables

#innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql/
#innodb_log_arch_dir = /var/lib/mysql/

You can set …_buffer_pool_size up to 50 - 80 %

of RAM but beware of setting memory usage too high

#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M

Set …_log_file_size to 25 % of buffer pool size

#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

The safe_mysqld script

[safe_mysqld]
log-error=/var/lib/mysql/mysqld.log

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

Remove the next comment character if you are not familiar with SQL

#safe-updates

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
log = /var/log/mysqld_multi.log

user = multi_admin

password = secret

If you want to use mysqld_multi uncomment 1 or more mysqld sections

below or add your own ones.

WARNING