HOWTO install and run a private MariaDB server instance on Opalstack

leehinde

It seems that the recommended pattern is to use a distinct shell user for each application. I'm trying that, but if I make an app for my mariadb and then, under a different user, create my django app, it wouldn't be able to reach the db using a socket connection. (I also tried using local host to connect and that didn't work either).

Seems like the django app and the database would need to be installed under the same shell account. Since I have two django apps wanting to use the same database instance (each with its own schema), then I'd want the whole kit and caboodle in the same shell account.

Does that make sense? Or did I do something wrong trying to connect to the database?

sean

leehinde You could try granting access to the MariaDB socket directory, eg log in as your Maria app shell user and do this (change appuser to your Django app shell user):

setfacl -m u:appuser:rwx $HOME/apps/maria/var
setfacl -m d:u:appuser:rwx $HOME/apps/maria/var

Please try it out and let me know how it goes, thanks!

leehinde

sean
[leehinde@vps59 ~]$ setfacl -m u:burlingame:rwx $HOME/apps/mariadb/var [leehinde@vps59 ~]$ setfacl -m d:u:burlingame:rwx $HOME/apps/mariadb/var
(no complaints)
then, from the burlingame ssh account:
[burlingame@vps59 ~]$ mysql -P 5608 -S $HOME/apps/mariadb/var/mysql.sock -u root -pand/or
[burlingame@vps59 apps]$ mysql -P 5608 -S /home/leehinde/apps/mariadb/var/mysql.sock -u root -p
Both resulted in:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/home/leehinde/apps/mariadb/var/mysql.sock' (13)
(path adjusted based on how I tried.)

I saw your post about running multiple django apps under the same uwsgi instance and that feels like a good fit. That's closer to what I was doing on WF (except everything in the stack is different. :-) ) I'm going to proceed that way and see how I do.

sean

leehinde sorry, i missed your last reply! If you're still having trouble with this then please email support for assistance.

flarios

sean Hello Sean, how are you?

I hope you are very well.
I couple years ago I had the opportunity to activate a MariaDB server with your help and this tutorial but today I want to replicate the steps I see the behavior is different, change the way to mount a server with Mariadb in opalstack recently?

I let you the errors so you can see what I want to say.

mysqld_safe --defaults-file=$HOME/apps/user/etc/my.cnf --socket=$HOME/apps/user/var/mysql.sock --nowatch
230831 17:48:33 mysqld_safe Logging to '/home/user/logs/apps/maria/maria.log'.
230831 17:48:33 mysqld_safe Starting mariadbd daemon with databases from /home/user/apps/maria/data

 mysql -P 5555 -S $HOME/apps/maria/var/mysql.sock
ERROR 2002 (HY000): Can't connect to local server through socket '/home/user/apps/maria/var/mysql.sock' (2)

mysqladmin -P 5555 -S $HOME/apps/maria/var/mysql.sock status
mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local server through socket '/home/user/apps/maria/var/mysql.sock' (2)'
Check that mariadbd is running and that the socket: '/home/user/apps/maria/var/mysql.sock' exists!

it seems this '/home/user/apps/maria/var/mysql.sock' it is not being created.

Thanks for your help!

kurt

@sean Thanks for this tutorial. I'd just like to clarify the first step.

Is the idea here to have a shell user under which only mariadb runs, and nothing else? if so, could you explain the reasoning?

Is there a problem with skipping the first step and following the rest of the steps using an existing shell user that owns other applications?

sean

kurt The idea is that if you need custom server settings for MariaDB that aren't configured on our managed shared database instance then you can run your own MariaDB instance configured as you need it.

You can use a dedicated shell user for this if you want. My preference is to use a single shell user to group apps by project.

I've edited the post to mention that you can use an existing shell user.

kurt

Great, thanks! I get the purpose of setting up a private instance. Just wasn't sure on the shell user.

I had a private instance at WebFaction, as well. Following your tutorial plus this one on migrating data from a private mysql instance on Webfaction, I was able to migrate 10 schemas over from my private instance at WF to a new one on OS this afternoon.

Thanks for all of the work you and the rest of the team are putting into support, automated tools and tutorials for your customers. Reminds me of the old days at WebFaction.

kurt

You might want to add the following line to the [mysqld] section of the my.cnf file:

ignore-db-dir = '#rocksdb'

Without it, show schemas; lists a schema named #mysql50##rocksdb. I believe this is related to the rocksdb engine, which creates a #rocksdb directory in the data directory.

https://jira.mariadb.org/browse/MDEV-12451

etienneh

To grant proper permissions I had to do the following from my database-user account:

setfacl -m u:django-user:--x $HOME
setfacl -R -m u:django-user:rwx $HOME/apps/mariadb-app/var
setfacl -R -m d:u:django-user:rwx $HOME/apps/mariadb-app/var
setfacl -m d:u:database-user:rwx $HOME/apps/mariadb-app/var
chmod g+s $HOME/apps/mariadb-app/var

instead of the above commands.

Then I could set my django database HOST to be /home/database-user/apps/mariadb-app/var/mysql.sock

natebeaty

Can you add the cronjobs to ensure service uptime? e.g.:

*/5 * * * * /usr/bin/mysqld_safe --defaults-file=$HOME/apps/maria/etc/my.cnf --socket=$HOME/apps/maria/var/mysql.sock --nowatch

Is there any downside to running a start command like this every 5 minutes?

sean

natebeaty You should check to ensure the app is actually stopped before you attempt to start it again.

Here's a shell script you can use for that:

#!/bin/bash

# edit these two lines
SHELLUSER=username
APPNAME=appname

pid=$( /bin/pgrep -f "/bin/mysqld_safe --defaults-file=/home/$SHELLUSER/apps/$APPNAME/etc/my.cnf" )
if [ -f /home/$SHELLUSER/apps/$APPNAME/var/pid  -a -n "$pid" ]
then
    echo "MySQL PID file exists and is running properly"
else
    echo "Restarting MySQL as PID File was not found or MySQL is dead"
    pkill -f "/bin/mysqld_safe --defaults-file=/home/$SHELLUSER/apps/$APPNAME/etc/my.cnf"
    sleep 2
    mysqld_safe --defaults-file=/home/$SHELLUSER/apps/$APPNAME/etc/my.cnf --socket=/home/$SHELLUSER/apps/$APPNAME/var/mysql.sock --nowatch
fi

Save that as ~/apps/appname/cron, then make it executable: chmod 700 ~/apps/appname/cron

Then you can run it via cron like:

*/5 * * * * ~/apps/appname/cron

natebeaty

Awesome, thanks! I did have to change the pid check to:

pid=$( /bin/pgrep -f "/usr/sbin/mysqld --defaults-file=/home/$SHELLUSER/apps/$APPNAME/etc/my.cnf" )

as it wasn't matching the process and kept restarting mysqld. The full process shows as this from ps aux:

/usr/sbin/mysqld --defaults-file=/home/username/apps/appname/etc/my.cnf --basedir=/usr --datadir=/home/username/apps/appname/data --plugin-dir=/usr/lib64/mysql/plugin --log-error=/home/username/logs/apps/appname/appname.log --pid-file=/home/username/apps/appname/var/pid --socket=/home/username/apps/appname/var/mysql.sock --port=xxxx

flarios

Hello!

I have already followed the steps indicated in the manual and I have even managed to create a new user for the database, the system already generated a port for me, now my question is the following:

With the ports, users and passwords generated, how do I import the database from the console?

My database is somewhat large

[user@opalxx ~]$ mysql --port=3333 -p -uroot name_db < db.sql
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Thanks for your help!

sean

flarios please try including the socket in your connection like:

mysql --port=3333 --socket=/home/user/apps/maria/var/mysql.sock -p -uroot name_db < db.sql

etienneh

@sean How does one upgrade this instance? Security fixes, staying up to date, all that.

sean

etienneh this procedure runs in instance of the MariaDB software that's already installed on your server, ie it is running the same /usr/sbin/mysqld but with separate configuration and as your own shell user instead of the system user.

Since you're running the system software it will be upgraded automatically whenever we upgrade the system software.

sean

flarios it seems something has changed with the MariaDB upgrade back in May that broke the setup procedure. In the past, the mysql_install_db step would work prior to creating the private instance my.cnf file but that no longer works.

I've swapped steps 5 and 6 in the procedure to correct this. If you start over from step 1 then everything should work as expected.

flarios

sean Hello sean, it works!!

Thanks for your help!