HOWTO install and run a private MariaDB server instance on Opalstack

sean

Opalstack's shared MariaDB instance is optimized for multi-tenant shared hosting, and individual customers aren't able to adjust the DB server configuration. If you do need that kind of control, or just want to learn more about running your own database server instance, then the following procedure is for you!

Here's how to install and run your own private instance of MariaDB on Opalstack:

Create a new shell user. The rest of this procedure will use myuser as the shell user name - be sure to replace that with your own shell user name. You can use an existing shell user if you want to.
Create a new "proxy port" application for the new shell user. Make a note of the app name and port assignment. The rest of this procedure will use maria as the name and 55555 as the port - be sure to replace them with your own app name and port.
SSH to your server as the shell user you created in step 1.

Run the following commands as your shell user to populate the app directory:

cd ~/apps/maria
mkdir -p {etc,var,tmp}

Create ~/apps/maria/etc/my.cnf with the following contents, replacing the shell user name, app name, and port with your own:

[client]
port = 55555
socket = /home/myuser/apps/maria/var/mysql.sock
[mysqld]
port = 55555
socket = /home/myuser/apps/maria/var/mysql.sock
tmpdir = /home/myuser/apps/maria/tmp
datadir = /home/myuser/apps/maria/data
[mysqld_safe]
log-error = /home/myuser/logs/apps/maria/maria.log
pid-file = /home/myuser/apps/maria/var/pid

Run the following commands as your shell user to initialize and start the instance. You'll see a couple of errors in the output of mysql_install_db but you can ignore them:

cd ~/apps/maria
mysql_install_db --defaults-file=$HOME/apps/maria/etc/my.cnf --datadir=$PWD/data --tmpdir=$PWD/tmp --user=$USER 
mysqld_safe --defaults-file=$HOME/apps/maria/etc/my.cnf --socket=$HOME/apps/maria/var/mysql.sock --nowatch

Run the following commands as your shell user to log into the private instance and create your first DB and DB user. Be sure to replace name_of_db, name_of_db_user, and some_password_here with your own values.

mysql -P 55555 -S $HOME/apps/maria/var/mysql.sock
# then, in the console:
create database name_of_db;
create user 'name_of_db_user'@'localhost' identified by 'some_password_here';
grant usage on *.* to 'name_of_db_user'@'localhost' identified by 'some_password_here';
grant all on name_of_db.* to 'name_of_db_user'@'localhost';
# now press CTRL-D to exit

If you have an application that you want to connect to your new database on your private instance then configure it with the following values:
- DB server or hostname: localhost or 127.0.0.1
- DB port: your assigned port from step 2, eg 55555 as shown above.
- DB socket: /home/myuser/apps/maria/var/mysql.sock
- DB name: the name of your database from step 7, eg name_of_db
- DB user: the name of your database user from step 7, eg name_of_db_user
- DB password: the password you specified for your database user in step 7, eg some_password_here

At this point you're up and running on your new private MariaDB instance. You can control the instance with the following commands:

Start: mysqld_safe --defaults-file=$HOME/apps/maria/etc/my.cnf --socket=$HOME/apps/maria/var/mysql.sock --nowatch
Stop: mysqladmin -P 55555 -S $HOME/apps/maria/var/mysql.sock shutdown

If you need to run other MariaDB/MySQL administrative commands you can do so, just be sure to include the port and socket options. Here's an example for mysqladmin status:

[myuser@opalN ~]$ mysqladmin -P 55555 -S $HOME/apps/maria/var/mysql.sock status
Uptime: 1370  Threads: 7  Questions: 6  Slow queries: 0  Opens: 17  Flush tables: 1  Open tables: 11  Queries per second avg: 0.004

That's it! If you need a private MariaDB instance then please give this a try and let me know how it goes here in the comments. :-)

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!