MySQL or MariaDB on RPi4 with Buster?

[Bosse_B]

I was going to set up LAMP on my new RPi4 and looked up some howto pages.
It seems like the MySql part is most often referencing MariaDB rather than MySql, why?
If I install MariaDB will things work out of the box anyway or are there new commands like maria rather than mysql on the command line etc?
Confused....

I want to have a server where I can load db backups from the main Linux server at my ISP taken with PhpMyAdmin.
In the end I would like to set up some automation function that can pull a backup off the main server to the RPi4 nightly so as to keep a working copy available locally.
Is that possible?

[rpdom]

MariaDb has replaced MySQL in most Linux systems. It is a fork of the original MySQL code.

Mostly it is a drop-in replacement for MySQL. You still use the "mysql" command in the terminal and still get a "MySQL [$database]> " prompt. The same SQL statements work.

The common issues that some people have reported with using MariaDB are actually nothing to do with differences between MySQL and MariaDB, but due to the way that Debian has tightened up security on the database. It would have been the same if they had stayed with MySQL. (The usual issue is that you are no longer asked to set a DB root password. The reason for that is that by default only local root access is allowed, and that is without password. A password can be set if remote root access is required, but it is not recommended).

The compatibility between MySQL and MariaDB is so good that I have an old MySQL server that replicate the data to two much newer MariaDB servers with no problems.

[CypherOz]

MariaDB all the way. The original MySQL developers forked to MariaDB due to Oracle getting control of MySQL

[Bosse_B]

Thanks!

Is there an up-to-date HOWTO for installing LAMP + PhpMyAdmin on an RPi4 with Buster?

[rpdom]

Pretty much an Debian-based install procedure will work. It doesn't have to be Pi specific.

[nigelbartlett1]

Please don’t ask the same question in multiple places.
https://www.raspberrypi.org/forums/view ... 6&t=248062

[Bosse_B]

Please don’t ask the same question in multiple places.
https://www.raspberrypi.org/forums/view ... 6&t=248062

Why?

[Bosse_B]

Pretty much an Debian-based install procedure will work. It doesn't have to be Pi specific.

OK so I found this how-to that said it is fully updated 2019.
Followed it for all steps until phpadmin and it worked fine!

[rpdom]

Please don’t ask the same question in multiple places.
https://www.raspberrypi.org/forums/view ... 6&t=248062

Why?

Because it is against the forum rules.

Duplicate posts usually get deleted or locked.
Keep doing it and you will get a warning from the mods and maybe a temporary ban if you persist.

Not saying that you will, but that is what happens if you do it again.

[trejan]

Please don’t ask the same question in multiple places.
https://www.raspberrypi.org/forums/view ... 6&t=248062

Why?

It wastes peoples time. Multiple people may give you the same advice because they didn't realise somebody already replied to you on a different post. It also clutters up the forum.

[Bosse_B]

Is it OK if I continue my questioning for help in this thread which I created myself?
Or is that too considered spamming?
I have tried to include as much relevant info I could think of in this post...

As I wrote some posts above I seemingly succeeded in installing LAMP using the instructions in the howto page:
Setup LAMP Server on Raspberry Pi 3 – [Updated 2019] Complete DIY Guide
When I wrote that I had managed to bring up the PhpMyAdmin, which I did not get to on my Ubuntu 18.04 attempts.
So I thought I was good.
But today when I wanted to use my RPi4 database system I got into trouble using PhpMyAdmin...
I opened the tab "Databases" in order to create my first user database.
Here the "Create database" function is disabled and there is a red note saying "No privileges" below it.
So I tried the command line instead since I have read some solutions on StackOverflow, but:

Code: Select all

 $ mysql
ERROR 1045 (28000): Access denied for user 'pi'@'localhost' (using password: NO)
pi@rpi4-test:~ $ mysql -u root
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
pi@rpi4-test:~ $ mysql -u root -p
Enter password:
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
pi@rpi4-test:~ $ mysql -u phpmyadmin
ERROR 1045 (28000): Access denied for user 'phpmyadmin'@'localhost' (using password: NO)
pi@rpi4-test:~ $ mysql -u phpmyadmin -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 95
Server version: 10.3.15-MariaDB-1 Raspbian testing-staging

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SHOW GRANTS FOR 'phpmyadmin'@'localhost';
+-------------------------------------------------------------------------------------------------------------------+
| Grants for phpmyadmin@localhost                                                                                   |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'phpmyadmin'@'localhost' IDENTIFIED BY PASSWORD '*BBF4F933A98C952FBD00091541F1098F9C7E53FF' |
| GRANT ALL PRIVILEGES ON `phpmyadmin`.* TO 'phpmyadmin'@'localhost'                                                |
+-------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

Obviously something is not working OK to actually use the newly installed database server via the PhpMyAdmin web interface...
It looks like phpmyadmin has full privileges but still I get the errors when trying to use PhpMyAdmin to create a database...
So since I was able to log on via command line using phpmyadmin user:

Code: Select all

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| phpmyadmin         |
+--------------------+
2 rows in set (0.001 sec)

MariaDB [(none)]> CREATE DATABASE bossedb;
ERROR 1044 (42000): Access denied for user 'phpmyadmin'@'localhost' to database 'bossedb'

Something regarding permissions is not set correctly, but how do I fix it, please?
And why is the prompt showing this:

Code: Select all

MariaDB [(none)]>

Seems like "none" should be the name of the logged on MariaDB user?

Confused, this is my first time setting up a database server on an RPi box, I have successfully built and used about a dozen RPi units for various web related and other purposes not using a database on the RPi...

[rpdom]

The phpmyadmin user doesn't have permissions to create databases, only to change and use databases that it has permissions enabled for.

From the local command line you should be able to use the root user to update permissions. Use "sudo mysql" to log on as root.

As for the prompt

Code: Select all

MariaDB [(none)]>

The "(none)" refers to the currently selected database, not the user name.

If you "CREATE DATABASE kevin;", then "USE kevin;" you will see the prompt change to

Code: Select all

MariaDB [kevin]>

I usually create databases from the command line interface, then GRANT ALL on $dbname.* to phpmyadminuser@%; to give the phpmyadminuser (or whatever I called it) permissions to create tables and do anything else on that database.
I sometimes use different users for different databases, occasionally with restricted permissions.

[Bosse_B]

Thanks!

Code: Select all

sudo mysql

was the key, I did not know that you could do that in order to log on as MariaDB root.
Could create a new database as needed.
I have also granted phpmyadin the needed priviliges and the datanases show up as they should.
Thanks again.

Now I just have to figure out how to load a backup of a database taken by PhpMyAdmin in the ISP hosted website into this new server. The nbackup is in sql format and it contains CREATE DATABASE <name> easly on (the second SQL statement).
Should I comment out that statement so the restore from backup targets the existing database?
If I keep the statement it means I have to drop the existing database manually before running the backup script, right?
Cannot create a new database with the same name as an existing one, I believe.

[rpdom]

This should do it

Code: Select all

sudo mysql < YOUR_SQL_FILE.sql

Replace "YOUR_SQL_FILE.sql" with the name of the sql backup file.

Yes, you would have to drop the database if it already exists, or remove/comment out the CREATE DATABASE statement in the sql file.

[Bosse_B]

Thanks again!
I think I have to drop the database then because the SQL contains a whole bunch of insert statements so if the existing database contained data then the backup execution would duplicate data that already exist.
Right now the database is empty, but still the CREATE statement contains additional parameters.

Now just experimenting anyway.
But later I would need to make some procedure to extract incremental data from the website database to fill in on the existing backup database.

[rpdom]

Is the backup database on the same server? If not, you could use database replication to automatically update the backup database any time a change was made to the main database.

MySQL supported a Master->Slave(s) database system.

MariaDB has that too, as well as a Master(s)<->Master(s) system (which I've experienced occasional hiccups with when data is being created and deleted rapidly).

I have a MySQL master running on a Pi 2B that has MariaDB slaves on a couple of x86-64 virtual servers. The servers have read-only access to the databases, so if I want to make a change I just update the databases on the Pi at home and the servers are updated almost immediately.