MySQL login fails under 11.4

dayfinger · June 6, 2011, 4:07am

Sirs!

I once had a hope that my first article here would be to help someone. Not so. My current problem seem so simple that I must have missed something. I have spent a lot of time fooling around and searching/reading the forums here (and www.mysql.com as well as the INET in general) without finding a solution, so here it goes:

I have been using Open SUSE (my first Linux ever) since shortly prior to the 11.3 release, so my first proper installation was using 11.3. I have since successfully been setting up several Open Suse 11.3 LAMP-ish) with Joomla! and some more, along with Desktop variants. (Not yet hard-core production sites, though). All have been working properly as far as I can see (Thanks everybody on the forums here, and - in my case - SWERDNA in particular, for your good tutorials! They are a fine mix of examples, explanations and concepts to make even a beginner making a few educated decisions!). There is, however, still some miles to go until I feel as home with (Open SUSE) Linux as I am with Windows (I have been doing M$ for years). As of now, I am mostly working through Yast, executing a command using BASH now and then. I am learning.

The problem:
I am unable to login to MySQL installed under 11.4. I did have some problems with 11.3 as well, but there I found that install it - then REMOVE it after activating the “Options” -> “Cleanup when deleting packages” inside Yast Software Manager - would allow me to reinstall MySQL and subsequently successfully run “mysql_install_db” and “mysql_secure_installation”. Following that, I could login to MySQL. The same procedure does not work with 11.4.

The installations are clean 11.4 installs from the DVD using KDE/EXT4. All installations are “minimum” installations. Which only mean I do not select anything at first, I install from Yast, and I do not modify or change any repositories. All installations seem to work fine apart from me not being able to login to MySQL.

The equipment used for testing 11.4 is:

VMWare ESXi 4.0. 64 bit, 50GB disk, 1GB RAM, 1 CPU core at 2.6GHz, two LAN adapters. Most Desktop-pattern entries and games were deselected during installation (Office and Graphics was deselected, and most of Multimedia).
A 6 year old Fujitsu-Siemens H230 laptop. 2GHz Intel Pentium Mobile single core, 32-bit, 2GB RAM, 12GB disk, two LAN adapters where the first is copper, the other is wireless (it works, but needs a delayed restart - which was not necessary under 11.3)) where 11.4 is dual-booting (GRUB) with Windows XP on a 80GB (total) disk.
Lenovo ThinkPad SL510. 64-bit, Intel dual-core at 2.2GHz, 4GB RAM, 120GB disk, two LAN adapters where the first is copper, the other is wireless (which doesn’t work, but this is not a 11.4 fault. There is no driver released for this WiFi adapter yet, and I cannot get the test-release to work. I’m not sure whether that is my fault or not. I’ll make a posting when I get it to work). Here, 11.4 is dual-booting with a 32-bit Windows 7 (using GRUB) on a 320GB (total) disk.

The behavior is consistent over all three environments.

After a (according to Yast) successful installation of MySQL, I execute “mysql_install_db” and start MySQL using “rcmysql start”. The deamon starts successfully, but I cannot login. I try the trick mentioned above, REMOVE MySQL and making sure the “Options” -> “Cleanup when deleting packages” inside the Yast Software Manager. Any dependent packages are modified to not use MySQL. (This may hamper some packages, but those I use are (to be) reinstalled after getting MySQL to work, the others are left as is. I do not check them.)

Here’s the details from executing “mysql_install_db”:

linux-host:/home/usr # **mysql_install_db**
Installing MySQL system tables...
110606  0:18:28 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.
OK
Filling help tables...
110606  0:18:29 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.
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 linux-host.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:
rcmysql start

You can test the MySQL daemon with mysql-test package

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

linux-host:/home/usr # **rcmysql start**
Will update MySQL now, if you encounter any problems, please read following file:
        /usr/share/doc/packages/mysql-community-server/README.SuSE
Running protected MySQL... 
Upgrading MySQL... 
Looking for 'mysql' as: /usr/bin/mysql
Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck
This installation of MySQL is already upgraded to 5.1.53, use --force if you still need to run mysql_upgrade
Starting service MySQL                                                          done
linux-host:/home/usr # **/usr/bin/mysql_secure_installation**




NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!


In order to log into MySQL to secure it, we'll need the current
password for the root user.  If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
Enter current password for root (enter for none): 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
Enter current password for root (enter for none): 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
Enter current password for root (enter for none): 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
Enter current password for root (enter for none): 
Aborting!

Cleaning up...
linux-host:/home/usr # **/usr/bin/mysqladmin -u root password zyka5f1ip**
/usr/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'

A similar thing happens if I try logging in using MySQL Workbench.

What am I missing here? Did MySQL as installed off the DVD get a default root-password I am not aware of? Can anybody help?

Thank you.

ken_yap · June 6, 2011, 4:21am

After a default install, the root password is blank. You should hit enter when mysql_secure_installation prompts you. You should not need to run mysql_install_db, the default installation is ready to go, just not secure.

dayfinger · June 6, 2011, 11:10am

Thank you, ken_yap.

I am entering no passwords, I hit <enter> directly (see “password: NO” in the error-messages from “mysql_secure_installation” in the code section). I also tried “root” as pwd where “password: YES” in similar message.

You should not need to run mysql_install_db
Well, I did, since I could not get MySQL to work without it in 11.2, and it continued to work (with the trick described above) in 11.3. But will “mysql_install_db” hurt the default installation? I have never executed “mysql_secure_installation” without a previous “mysql_install_db”.

I see two more tests here: 1. Install MySQL and execute “mysql_secure_installation” directly. If that does not do it, then 2. Do a full new install of Open SUSE 11.4, install MySQL and then execute “mysql_secure_installation” without “mysql_install_db”.

I’ll try and let you know how it goes.

ken_yap · June 6, 2011, 3:17pm

Should you decide to start afresh, make sure you also delete /var/lib/mysql because a reinstall of the package doesn’t not remove the database, and the passwords are stored in the database itself.

NB: To anybody else who happens to read this, DO NOT delete /var/lib/mysql if you have data in the database that you want to retain, but instead use another method to reset the root password involving starting mysqld with the skip-grant-tables option enabled (IIRC). Search for it.

dayfinger · June 8, 2011, 2:41am

delete /var/lib/mysql

>click<! I remembered it immediately when I read the recommendation. I have done this before! Both with 11.2 and 11.3. It was, however, just after starting off with 11.2/11.3 and my experimentation/logging wasn’t too systematic. I was mostly focused on getting things to work, so I forgot about it. Human memory is a slippery thing - well, mine is.

So, my trick from 11.2 and 11.3 seems to be back, and is valid for 11.4 too. It also appears that removing the /var/lib/mysql directory is a one-time-per-computer-operation only. As my VMWare installation is temporarily out of my reach, I have only been able to verify the operation using my Fujitsu-Siemens and my Lenovo laptops. Fortunately, this includes both 32-bit and 64-bit Open SUSE 11.4 - but only one instance of each.

I don’t like the solution, however, even though I have used it before. It is the only way I know to getting MySQl to work. I have not seen any problems with it (this, however, can’t be made to mean much. I am a beginner at Linux, and as such - my experience is limited).

This is, after all, a brute-force workaround that performs many operations that affect so many other applications, that it should only be performed immediately following a fresh installation of Linux. This is even more true with 64-bit 11.4 (see below). You should decide before installing 11.4 whether or not you need MySQL for your own work, and install it immediately after completing first-time installation and first-time software updates. As such, this workaround raises many questions as well:

I have not seen anyone mentioning a problem like this elsewhere in the forums. Am I the only one experiencing this?
If the answer to 1) is yes: How are everyone else doing it to get MySQL working?
Is mysql_install_db executed during the installation of MySQL from Yast?
On my Lenovo (64-bit 11.4) the /var/lib/mysql directory didn’t even exist after initial installation - but MySQL was installed none the less, I didn’t select it. Many of the default applications depend on MySQL. Does the system create databases elsewhere?
mysql_install_db creates the /var/lib/mysql directory. At first, this isn’t enough to get the mysql deamon started. I have to reinstall MySQL WITHOUT removing /var/lib/mysql. Then I rerun mysql_install_db. Now, the mysql deamon will start. Apparently, Yast’s installation of MySQL differs depending on whether the /var/lib/mysql directory do exist prior to installation or not - and it has an effect on my problem. What is the difference?
In other threads, I have seen mysql.conf (and .mysql.conf) being debated for localhost access, among other things. Are these files related to the problem I am experiencing?
In many ways, my problem can be seen to look very similar to rights-problems I have been seeing elsewhere. Is that all there is to it? Could a script correct the problem and circumvent my (nasty) workaround?

I know that some of my questions above are quite basic. They are, however, out of my reach to explore at the moment. I will look back at them when I dive into Linux packaging and installation. Eventually, I will get there. It would be nice to know the principles, though.

I have also noticed that on my Fujitsu-Siemens (32-bit 11.4), the /var/lib/mysql directory do get created during MySQL installation using Yast, but is not created following a fresh, default install from the DVD nor during the subsequent SW upgrade. This directory is owned by mysql:mysql. Running mysql_install_db does not change this. However, removing the directory and letting mysql_install_db re-create it, /var/lib/mysql is owned by root:root.

Here’s how I perform the (nasty) workaround:

Details are for Open SUSE 11.4 only, but the principle works for 11.2 and 11.3 too. So read on.

If you are a beginner, or want all the gory details, see my posting below.

If you do not belong to the beginners, here’s the short-track (assuming MySQL is installed, but not properly working - and it has not been working on that system/installation either):

Remove the /var/lib/mysql directory if it exists.
Execute mysql_install_db.
Start the mysql deamon. It will probably (but not always) refuse to start.
If it starts, execute /usr/bin/mysql_secure_installation and enter a (the) root password, or otherwise login to MySQL.
If that works too, you don’t have my problem. If it fails, continue with the next item.
Delete anything mysql-related using Yast Software Manager. Remember to activate “Options” -> “Cleanup when deleting packages”
Always delete packages to resolve conflicts related to deleting mysql.
Install mysql.
Execute mysql_install_db.
Start the mysql deamon. It should start now.
Execute /usr/bin/mysql_secure_installation or login to MySQL. It should work properly.
Install any packages you want to keep, that were deleted in item 4 due to resolution conflicts.

NOTES:

I have never done this on a system with any production data residing on it. So you should back up all your data prior to performing the steps below.
The procedure do break and make a lot of MySQL dependencies, and all are handled through Yast using default production DVD repositories. If your installation involves any kind of additional repositories or packages installed from elsewhere - and that depends on MySQL, there is a big risk that those applications will be broken. At least, you will need to reinstall these applications yourself after completing the procedure. Make sure you have them available for installation. Additional problems my arise.
I have always used fresh installations (as to Open SUSE Linux versions) when doing this. I have never done any version upgrades (e.g. 11.3 to 11.4), so I do not know whether this procedure will work or not for such a system, or whether my problem is valid at all for an upgraded system.
Everything assumes a local installation logging in to localhost. I have never tried remote login prior to getting this to work, so I do not know whether remote login works when these problems exists locally.

Thank you, ken_yap for pointing me in the right direction! Very helpful indeed!

dayfinger · June 8, 2011, 2:45am

This is the detaild description of the (nasty) workaround:

NOTES:

I have never done this on a system with any production data residing on it. So you should back up all your data prior to performing the steps below.
The procedure do break and make a lot of MySQL dependencies, and all are handled through Yast using default production DVD repositories. If your installation involves any kind of additional repositories or packages installed from elsewhere - and that depends on MySQL, there is a big risk that those applications will be broken. At least, you will need to reinstall these applications yourself after completing the procedure. Make sure you have them available for installation. Additional problems my arise.
I have always used fresh installations (as to Open SUSE Linux versions) when doing this. I have never done any version upgrades (e.g. 11.3 to 11.4), so I do not know whether this procedure will work or not for such a system, or whether my problem is valid at all for an upgraded system.
Everything assumes a local installation logging in to localhost. I have never tried remote login prior to getting this to work, so I do not know whether remote login works when these problems exists locally.

Where the behaviour of 32-bit and 64-bit 11.4 is noticeably different, I point it out in the text. Minor differences are ignored. The dumps in the examples (code blocks) below are from my 32-bit operation only. As for the examples, the difference between 32-bit and 64-bit behaviour is ignorable. I have tried to make the descriptions detailed enough for even the freshest beginner to follow.

If you have a ready installed system with my MySQL-problem, you can try skipping to item 3 below. If you make provisions according to the notes above, it should not hurt your system. I have done so myself with systems used for other purposes under 11.3 and where I later decided to install MySQL. But again, I have never had any of my systems up for hard-core production use yet.

Install Open SUSE 11.4 from the installation DVD using defaults. I have always used KDE desktop, and ext4 file system (ext3 on 11.2, if my memory serves me right). Forget about Apache, PHP, MySQL, printers etc. for the moment. Deselect Office-, Graphics- and Multimedia-systems as well as games if your disk space is limited. I have also adjusted disk/volume layouts according to what I think is the most appropriate, but I cannot see how such adjustments affects the problem. Any installation providing sufficient disk space should do. Again, if you have limited disk space, go for default values.
Install MySQL from Yast. I initially select the following modules (search for “mysql”): mysql-community-server, mysql-community-server-tools and mysql-workbench. Accept dependencies that are
resolved. If you are using 11.2 or 11.3, you shold install mysql-admin as well, but this module is integrated into mysql-workbench in 11.4.
Start a console and execute “su”. Enter (Linux) root’s password. This console will be open in this state for the remainder of this operation, and is what we use the phrase “From the console” is referenced.
Remove the /var/lib/mysql directory if it exists.
To check if it exists, from the console, enter (without the quotes): ‘ls /var/lib’. If you can find ‘mysql’ there, it must be removed. To remove it enter the following commands while still in the console:


cd /var/lib/mysql
rm *
cd ..
rd mysql

If the last command comes with an error, the mysql directory is not empty. Do as follows:


cd /var/lib/mysql
ls
rm x/*
rd x
rd mysql

where ‘x’ is the name of any directory seen after issuing ‘ls’. If the ‘rd x’ comes with an error, ‘x’ also has directories. Change to these directories as needed and repeat the pattern. “cd …” will put you into the directory above the one you are in (the parent directory). Repeat this as many times as you need until you can execute ‘rd mysql’ without receiving any error messages.

From the console, execute ‘mysql_install_db’ (without the quotes):

linux-host:/var/lib # mysql_install_db
Installing MySQL system tables...
110607  0:31:08 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.
OK
Filling help tables...
110607  0:31:10 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.
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 linux-host.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:
rcmysql start

You can test the MySQL daemon with mysql-test package

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

From the console, start the mysql server deamon issuing the command ‘rcmysql start’:

linux-host:/var/lib # rcmysql start
Starting service MySQL warning: /var/run/mysql/mysql.sock didn't appear within 30 seconds
chmod: cannot access /var/run/mysql/mysqld.pid': No such file or directory
                                                                                failed

The mysql deamon probably refuse to start as shown in 6’s example. If it starts, you could go on and check out item 10 below. Maybe you do not have the problem. If it doesn’t start it is time to remove MySQL from the system. Using Yast’s Software Management, make sure to tick (activate) “Options” → “Cleanup when deleting packages”. Again, search for “mysql” and select modules for removal (right-click on the module and select “delete”). The fastest way I’ve found is to select libmysqlqlient16 and libmysqlclient_r16 for deletion. Many modules depend on those, so these will remove several other modules as well when dependencies are resolved. Then make sure the remainder of the modules you chose (and those being auto-chosen for you) in item 2 are marked for deletion as well. If you jumped into this at item 3, you shold select anything related to mysql for deletion, making a note of which modules being installed to enable you to select the same modules for installation again in item 8 (below).

Here, the system will warn you that one or many other modules that depend on MySQL are to be broken. You need to tell it whether to keep them, break them or delete them. Always delete them! If you think you need the packages, or are reluctant to letting them go, make a note of those you want in order to install them again after this procedure is finished.

64-bit note: 11.4 is more a 64-bit system than its predecessors. Consequently, it will warn you of an architecture change (“inferiour architecture”, is the term, I think) when breaking MySQL dependencies. What this means, is that 32-bit versions of many modules will replace the (MySQL-dependent) 64-bit versions. This goes for Libre Office as one of many. Consequently, a lot of modules will be reloaded. Other modules need to be deleted, just as for 32-bit systems. Make sure to make a not of those you want to keep, so you can install them afterwards.

Let the deletion process finish. For 64-bit systems, it will take some time. On my Lenovo, more than 800MB was downloaded over the wire during the replacement of more than 120 packages! I also received an error message complaining about Java not being present. True, it wasn’t installed, and 32-bit Libre Office apparently need it (contrary to 64-bit Libre Office). If you get the same error, don’t bother - we’ll reinstall the 64-bit version later on.

Install mysql the same way you did in item 2, or the modules noted as installed in item 7, first paragraph.

64-bit note: Again, due to 11.4 being more a 64-bit system than its predecessors, it will warn you of an architecture change. Accept all of them. The notion “inferiour architecture”, is no longer used. This time, 64-bit (MySQL) dependent versions will replace the 32-bit versions we installed in item 7 above. This goes for Libre Office as one of many. Consequently, once again, a lot of modules will be reloaded. Due to Libre Oficce’s complaints above, about Java missing, I also selected the ‘java_1_6_0_sun’ module for installation. I’m going to need a Java module anyway. I received no complaints upon reinstallation.

Let the installation process finish. For 64-bit systems, it will take some time. On my Lenovo, more than 750MB was downloaded over the wire during the restoring of more than 120 64-bit packages!

From the console: Execute ‘mysql_install_db’ as you did in item 5.
From the console, start the mysql deamon by executing ‘rcmysql start’ (no quotes!). This time, the mysql deamon should start successfully:


linux-host:/var/lib # rcmysql start
Will update MySQL now, if you encounter any problems, please read following file:
        /usr/share/doc/packages/mysql-community-server/README.SuSE
Running protected MySQL... 
Upgrading MySQL... 
Looking for 'mysql' as: /usr/bin/mysql
Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/var/run/mysql/mysql.sock' '--socket=/var/tmp/mysql-protected.MBAeF0/mysql.sock' 
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/var/run/mysql/mysql.sock' '--socket=/var/tmp/mysql-protected.MBAeF0/mysql.sock' 
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log
Error    : You can't use locks with log tables.
status   : OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.servers                                      OK
mysql.slow_log
Error    : You can't use locks with log tables.
status   : 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                                                          done

Now, things should be working. Just one more command, and we’re through:
From the console, execute ‘/usr/bin/mysql_secure_installation’ (no quotes). See the example below for sensible answers. The term ‘’ should be replaced by YOUR secret password. Note that the MySQL root-user is different from Linux’s root-user. Consequently, for a sound, secure system, their passwords should be entirely different!


linux-host:/var/lib # /usr/bin/mysql_secure_installation




NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!


In order to log into MySQL to secure it, we'll need the current
password for the root user.  If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.

Set root password? [Y/n] y
New password: <secret password>
Re-enter new password: <secret password>
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y
 ... Success!

By default, MySQL comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...



All done!  If you've completed all of the above steps, your MySQL
installation should now be secure.

Thanks for using MySQL!


linux-host:/var/lib #

Finishing off:
Although MySQL should be working now, you should now take the time to install any packages you accepted for deletion in item 7.

Thank you, ken_yap for pointing me in the right direction! Very helpful indeed!

hsechovsky · September 27, 2011, 12:52am

Dayfinger,
thank you very much for all your tests and findings. And particularly for the time you devoted to your descriptions here.

It helped me very much, I experienced similar problems as you. If my understanding is right, the problem happens quite easily, when the installation of mysql and it’s setup and configuration are not finalized properly and at the same time period.
Re-installations of mysql add rubbish into /var/lib/mysql and the best way is to erase it. (Thank you ken for your remark).

The situation is more tricky, as the ways of the root password reset mentioned by ken_yap and recommended in MySQL :: MySQL 5.0 Reference Manual :: C.5.4.1 How to Reset the Root Password (reset the root password involving starting mysqld with the skip-grant-tables option enabled) are not functional in opensuse 11.4 :

(# mysqld restart --skip-grant-tables

110927 0:12:41 [Warning] ‘–skip-locking’ is deprecated and will be removed in a future release. Please use ‘–skip-external-locking’ instead.
110927 0:12:41 [ERROR] Fatal error: Please read “Security” section of the manual to find out how to run mysqld as root!

110927 0:12:41 [ERROR] Aborting)

It is also difficult for me to understand, why this problem is not described more frequently in forum and in HowTo documents.
Maybe the problem is caused by the fact, that most of people are concentrated to their particular problem and after finding the solution they do not share their experiences with others, you did it, thanks You as well as God.

Also the core of the problem is more related to MySQL and less to OpenSuse. The description of the persistency of files in /var/lib/mysql should be mentioned in dev.mysql.com at first of all, I think.

In spite of all the time I needed for the proper LAMP with SSL setup I love OpenSuse 11.4 for many other tools, look and functions.

Hynek

ken_yap · September 27, 2011, 1:17am

No, don’t run mysqld from the command line. You are meant to run it only from the init script in /etc/init.d/mysql

To add the skip-grant-tables option, edit /etc/my.cnf and add a line: skip-grant-tables in the [mysqld] section, then restart the mysql service. Comment out the line when you are done resetting the password.

The instructions on the MySQL site are generic and should be overridden by more specific methods for the OS in question. Remember that MySQL runs on Linux, *BSD, Unixes and Windows.

eng-int · September 27, 2011, 3:00am

To any non-expert PLEASE ignore this thread and dayfinger’s suggestions. Life is hard enough without these complications.

To dayfinger,
Did it not occur to you that the reason you have not seen references to others suffering from your problems might be because they were self-inflicted and unique to yourself?
I have been installing and administering SuSE/openSUSE/MySQL servers for twelve years, and have never come across such a torturous “method” before.

You appear have tried to mix the raw DIY methods (required when installing from a tarball and presumably by MS Windows people) with the easy package installation that is one of the reasons for using a distribution like openSuSE.
If you want to manually run scripts such as mysql_install_db you should run them using the account that runs mysqld; i.e. for openSuSE mysql:mysql. The mysql_secure_installation does not do much more than prompt for a root password and remove the* test* database.

To set up a LAMP server using openSuSE:
Start a clean install using either the DVD or NET CD.
It helps if you select the correct keyboard layout before selecting “Installation” from the first menu.
When asked to choose an installation type choose “Other”, then “Minimal Server - text mode”.
I always deselect the “auto-configure” check box – perhaps a beginner should leave it checked
There is a screen that shows and permits review of the configuration options before disc writing begins. At this stage you can modify the selected Software Packages. Add the pattern “Web and LAMP Server”. If you miss this do not worry as it can be added later with YaST.
When the installation is finished login and start the MySQL daemon – “sudo /usr/sbin/rcmysql start <enter>”.
Use Yast > System > System Services (Runlevel) to set mysql, apache, ntp etc to start automatically at boot.
Type “mysql -u root <enter>”
You will then be logged into MySQL as root – note the “mysql>” prompt.
N.B. the MySQL root account is local to MySQL, and not the same as the system root.
That,s pretty much it the rest is down to the typical DBadmin and Webmaster tasks.
Use mysql commands to change the root password, and create/load your database.

hsechovsky · September 27, 2011, 3:09pm

Ken,
thank you for your prompt response with help. I see, I am still green in spite of my grey hair. In the meantime (before I found your reply) I renamed /var/lib/mysql dir and tried to uninstall mysql via yast, which offered to replace mysql by mariadb. I accepted it and under mariadb I was able to reset the root password - first time after week of attempts. I hope now I am back on track. I am not sure now what was the root of my troubles. Maybe there was frozing communication with xen VM via nxfree client which I used and experienced long response times. Maybe the commands for mysql were distorted by it. Now I used standard ssh via terminal session for mariadb setup and it was fast and successful. Some crazy overload of xen vm (nothing started by me on purpose) is still pending.

I really appreciate your prompt as well as precise help. Your support is another amazing feature of Opensuse. My congratulations to all OpenSuse developers and supporters community. I have bad experience with support of IBM Lotus Notes, not mentioning MickySoft which motivated me to learn Linux. It was the reason why I started to learn and used open source software preferably.

Have a nice day,
Hynek