How to.. SQL 13.1/13.2

English Applications
1

Hello!
I use to buy services/consultans to set up this with databases, handle data base managers, connections and so on. I decided during the work week why dont I have a look at it to understand a little more? I mean (The guys at Top Gear UK use to ask “how hard can it be”)?

hw1, my openSUSE server 13.1(uppgraded from 12.x and are on its way to 13.1 Evergreen)
hw2, my main DE, 13.2
hw3, my laptop 13.2 (upgraded from 13.1)
and (havent got there yet) a number of WM’s.

Check upon my server and it had mysql, not mariadb(but the clients have). Not so big deal I think. I mean I use VirtualBox (the oracle version). I start to search om the net and found:
https://en.opensuse.org/SDB:MySQL_installation

http://www.linkapps.com/programming/21-database/44-setting-up-mysql-on-opensuse.html among others. I was thinking why do not test with digikam and have the server SQL instance to handle info and thumbnails. According to digikam it has support for mysql (exprimental). Of course my knowledge as a noob on this side made me checked out administrations tools (GUI ones).

What a heck! Ok. Why not install webadmin that I have used a couple a years ago. Ended up whit the bug https://bugzilla.mozilla.org/show_bug.cgi?id=588479 Still not solved. Installed Chromium and did connect to the webmin after several tries (not using https).

I got a little bit wiser and could start to see my mysql setup. The tools in webmin was not on my level but I manage to do some things. I hate to get ended with this. Asked my Laptop openSUSE 13.2(upgrade from 13.1) and it suggested the 12.3 package(mysql-workbench) on software.openSUSE.org and 1-click install. Of course I tried. No success. I tried on my 13.1 server as well… Grrrrr…

I went to ftp://fr2.rpmfind.net/linux/opensuse/distribution and downloaded mysql-workbench-6.1.7-3.2.7.x86_64.rpm and
mysql-workbench-5.2.44-2.1.5.x86_64.rpm and installed on different (server(clients)). Works ok on both server and clients but I have a lot to learn and understand. Whenever you readers have stop LOL I asking for some advice:

-is mysql-workbench the best tool in GUI for a newbie to manage a SQL-database today? Please comment.
-Later on when I getting a better grip I will transfer to mariadb (I welcome it) to learn even more. Anything special to think on there?

regards

2

Probably Work bench does all you need to do to administer a DB. You do have to have a basic working understanding of SQL DB administration though. But there is no magic tool that will side step the need to learn some SQL basics. :wink:

3

I am using OpenSUSE 13.2 as a server platform for my home and small business server. It is running on an Intel i3 3.0ghz Dual Core with HT, has 6gb of memory and a 500gb main drive, with 6tb of RAID5 storage. The MariaDB (OpenSUSE’s choice of MySQL database engine) is pretty painless to set up, and as you have discovered there are many “management” tools out there. I tried several before I decided to use phpMyAdmin mostly because it runs on any platform, is “Noobishly” user friendly and has tons of online resources if help is needed. You may want to pick up a book or two on Linux Administration that cover LAMP, and phpMyAdmin for desk references.

I also use LibreOffice Base to work with the data, both entry, edit and update as I have created “forms” for data entry. This allows users on my network to safely enter / update data from remote workstations. I have even been able to have one Windows 7 user connect to their own DB files served up by MariaDB (MySQL) via Microsoft Office 2010 Access software. For Base to work with the server, I had to install the MySQL connector extension then log in using the MySQL user created for the purpose of data entry.

For all of this work (about one weekend of my time) I was quoted between $1500.00us to $4000.00us by four local shops who also wanted to sell me a monthly service plan on top of it. I went with Linux, and OpenSUSE on the recommendations from my local LUG here in Portland, and have never looked back.

Good luck!

4

The easiest way to move mysql and mariadb sdatabases is:

mysqldump -u root -p --all-databases > backup.sql

After you have completed your fresh install:

mysql -u root
source backup.sql;
flush privileges;

Note the absence of a password after the install; obviously you may have a long path to backup.sql.
You may also have to change the Host in mysql.users since openSUSE normally generates a random hostname during installation which will be different from the hostname of your previous installation.

Obviously, I am assuming you have a fairly straightforward setup. The mariabd documentation covers all the mysqldump options very well and mariadb recommend this as the way of moving between different versions of mysql and mariadb.

5

I have a similar problem. I had a localhost server running under openSUSE 12.2. I upgraded to 12.3 and then to 13.1. Somewhere along the way I lost the LAMP server at localhost and all local development websites (I have akeeba backups so they can be restored). I installed Web and LAMP server under 13.1. using instructions at http://en.opensuse.org/SDB:LAMP_setup.

Now I cannot access the database with phpMyAdmin. I do not know if this is a password problem, if the old mySQL database is still there but inaccessible or if there is something wrong with the new installation. When I run phpMyAdmin, i get their manual only - no database connection - with no password, the old password and the new password I thought I set.

Sadly, I have fiddled with this not knowing what I am doing and probably made things worse then they need to be.

How do I fix this? What information do you need to help me?

Cordially,
TwoHoot

6

Check whether mysql is running anyway: Yast - System - Servicesmanager. The mysql service should be enabled and started.

7

Thank you for the prompt reply.

mysql is active and running in Services Manager

Cordially,
TwoHoot

8

Then you should be able to access it.

Can you connect using “mysql -u username”?

Please post the output of “sudo systemctl mysql”.

9

In Konsol as su,

mysql -u root yields the following error:
ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: NO)

sudo systemctl mysql yields the following error:

Unknown operation ‘mysql’.

Cordially,
TwoHoot

10

Then you have indeed set a password.
Try “mysql -u root -p” then and enter the password when asked.

And you don’t need to and shouldn’t run mysql as root (su).

sudo systemctl mysql yields the following error:

Unknown operation ‘mysql’.

It’s “sudo systemctl status mysql”.
That “status” somehow got lost in my reply, sorry… :X

11 
linux-ikia:/home/jch # mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
linux-ikia:/home/jch # sudo systemctl mysql
Unknown operation 'mysql'.
linux-ikia:/home/jch # Unknown operation 'mysql'.
If 'Unknown' is not a typo you can use command-not-found to lookup the package that contains it, like this:
    cnf Unknown
linux-ikia:/home/jch # mysql -u root -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
linux-ikia:/home/jch # mysql -u root -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
linux-ikia:/home/jch # exit
exit
jch@linux-ikia:~> sudo systemctl status mysql

We trust you have received the usual lecture from the local System
Administrator. It usually boils down to these three things:

    #1) Respect the privacy of others.
    #2) Think before you type.
    #3) With great power comes great responsibility.

root's password:
Sorry, try again.
root's password:
Sorry, try again.
root's password:

The first password I tried was just a return for no pw at all. The second was the pw I thought I set during earlier.

12

You enter the wrong password apparently, both for mysql and for sudo.

Where/how exactly have you set the password?

The password for mysql’s root user is not the same as the system’s root password.

13

I set the password as shown in Section 4 of SDB:LAMP setup

http://en.opensuse.org/SDB:LAMP_setup#Configuring_the_MariaDB.2FMySql_server

It is not the same as the system root pw.

If I entered it wrong or don’t remember it correctly, is there a way to remove or reset it?

14

Ok

It is not the same as the system root pw.

But your sudo password was not accepted as well.
Is there a problem with your keyboard maybe? :wink:

If I entered it wrong or don’t remember it correctly, is there a way to remove or reset it?

Never tried that, but see here:
http://ubuntu.flowconsult.at/en/mysql-set-change-reset-root-password/

Because mysql has a systemd unit file on openSUSE nowadays, you’ll have to run “sudo systemctl stop mysql” instead of “sudo /etc/init.d/mysql stop” though.

15

The problem with my keyboard is persistent. Errors happen whenever I use it. It couldn’t be me that makes the errors, could it? (no answer wanted. It is rhetorical question)

Do you recommend I try this? Would it be helpful to open SUSE users to post the code here if I do?

My hard experience over the years is to get a clean installation of all software rather than patch it up. Would it be better to remove the entire LAMP installation and start over? How would I go about doing that? The old db and server seem to have been lost in the upgrade and I do have backups off-site.

16

As I recall there was a change in data structures at some point. This meant you had to export then reimport the data in the new version.

17

Well, it could be caused by a wrong keyboard layout setting as well…
I hope sudo does work and accept to (right) password?
Otherwise you won’t be able to do much to solve that problem at all.

Do you recommend I try this? Would it be helpful to open SUSE users to post the code here if I do?

Well, if you don’t know the password to connect to the database, I see no other way really.

Btw, this is actually taken from the official mysql reference manual: http://dev.mysql.com/doc/refman/5.5/en/resetting-permissions.html#resetting-permissions-generic

So to summarize, do this:

sudo systemctl stop mysql
sudo mysqld --skip-grant-tables
mysql -u root

Then set a new root password with the following SQL statements:

UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
FLUSH PRIVILEGES;

If your MySQL server is also accessible via the network (this is disabled by default on openSUSE), it would be safer to user “sudo mysqld --skip-grant-tables --skip-networking” as second line, to prevent clients being able to log in as root without password for a short while.

My hard experience over the years is to get a clean installation of all software rather than patch it up. Would it be better to remove the entire LAMP installation and start over? How would I go about doing that? The old db and server seem to have been lost in the upgrade and I do have backups off-site.

This is no patch.
This only sets back the user tables to the defaults, i.e. resets the root password.

Uninstalling and reinstalling mysql will not do that (there’s no need to reinstall Apache or PHP anyway, as they are not related at all here), so you’ll still have the (forgotten?) root password and won’t be able to connect.

You could of course wipe out your complete database as well, but then reinstalling mysql shouldn’t be necessary either.
To do this, stop mysql, delete all files in /var/lib/mysql, and start mysql again:

sudo systemctl stop mysql
sudo rm -r /var/lib/mysql/*
sudo systemctl start mysql

The root password will be empty again then, but you’ll also lose all your data of course. So only do that if you don’t care about your databases, or have a recent backup!

18

Thank you for the reply.

I intend to install Joomla and restore my old development websites on localhost with the Akeeba backups when I get LAMP running. Do you think that will work?

Cordially,
TwoHoot

19

No clue what those backups are but if it is just a file backup no. You need to export from an old system then import the data to the new

20

That should not be necessary.
MySQL/MariaDB should upgrade the databases automatically at first start after an upgrade.

I’m running mysql (now mariadb) on (open)SUSE since at least 10 years here and never had to export/reimport my tables…
I even successfully switched from MySQL 5.6 to MariaDB 5.5 without doing that (although that didn’t “just work”). :wink:

You can try to run “sudo touch touch /var/lib/mysql/.force_upgrade” (after stopping mysql) to force the table upgrade on next start though.
But I doubt that will help here. If that would be necessary, mysql would not start at all I think.