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.
john_hudson;1907274 Wrote:
> How far have you have in setting passwords? Normally mysql has no
> passwords set when it is installed.
>
As far as I know, not at all. Especially because all option on the
commandline fail.
john_hudson;1907274 Wrote:
>
> 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.
>
Not possible
>
> localhost:/etc # mysql -u root
> ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using
> password: NO)
>
john_hudson;1907274 Wrote:
>
> 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.
I do not even want to lock in. I have never ever logged in. 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?
> >
> mysqladmin will ask you for the root password. Leave the Enter
> password: empty If you haven’t changed root password before.
> Good luck.
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)’
>
msounhein;1907300 Wrote:
> 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?
I have been using this guide:
‘Linux - Vergessenes root-Kennwort neu festlegen’
(http://tinyurl.com/65kv7a)
(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!’ (http://www.mysql.com)
> 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.
ken_yap;1907344 Wrote:
> 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!’ (http://www.mysql.com)
> 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.
ken_yap;1907355 Wrote:
> 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
> …
>