HOWTO install and run a private MariaDB server instance on Opalstack

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.

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!

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!

pecus

This single step solved a massive performance issue on one of our sites, so I encourage anyone struggling with db performance inconsistencies to setup a private instance.

One piece of advice: this instance will not be backed up by Opalstack daily process, so you need to take care of it yourself (we have a daily cron job that dumps the db, gzips it and rotates it every week)

sean

pecus One piece of advice: this instance will not be backed up by Opalstack daily process

That's not entirely correct.

We back up your entire home directory, so that will include the private MariaDB instance and all of its data. What we don't do is create individual DB dumps for you.

flarios

Helo @sean , I hope you are very well

I would like to comment you that the MariaDB server has experienced an unexpected outage. It has been operational for almost a couple of years without any incidents, but now we are facing this issue.

$ mysqladmin -P 3333 -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/xxxx/apps/maria/var/mysql.sock' (111)'
Check that mariadbd is running and that the socket: '/home/xxx/apps/maria/var/mysql.sock' exists!

Our main goal is to bring the server back up without risking the loss of data stored in the database. what can I do for resolving this issue safely and effectively?

I can assure you that the file that mentions the error exists

$ ll
total 0
srwxrwxrwx. 1 user user 0 Oct 28  2022 mysql.sock

I appreciate your attention and assistance.

Best regards,

peter

flarios We upgraded the system MariaDB software in April 2023, and you wouldn't have noticed any issues if your private instance had been running before the system upgrade. Trying to start your private MariaDB instance after the recent crash you mentioned, you are likely to see the following error in your "maria.log" file:

[ERROR] InnoDB: Upgrade after a crash is not supported. The redo log was created with MariaDB 10.3.31. You must start up and shut down MariaDB 10.4 or earlier on the data directory.

You should be able to correct the problem as follows (replace port 3333 with the actual port used by your MariaDB server):

Run the following commands to start the instance:

scl enable rh-mariadb103 bash
mysqld_safe --defaults-file=$HOME/apps/maria/etc/my.cnf --socket=$HOME/apps/maria/var/mysql.sock --nowatch

# then after the instance is fully running...
mysqladmin -u root -P 3333 -S $HOME/apps/apps/var/mysql.sock -p shutdown
exit

Start your instance as you normally would, then run the following command:

mysql_upgrade --defaults-file=$HOME/apps/maria/etc/my.cnf --socket=$HOME/apps/maria/var/mysql.sock --port=3333 -p -u root

At that point, your instance should be fully upgraded and operational.