MariaDB alongside MySQL

From SME Server
Revision as of 23:44, 11 January 2014 by Mmccarn (talk | contribs) (add 'medium' and 'howto')
Jump to navigation Jump to search
PythonIcon.png Skill level: Medium
The instructions on this page require a basic knowledge of linux.


Install MariaDB alongside MySQL

MariaDB is a drop in replacement for MySQL, but you can also install it alongside MySQL.

Installing another SQL database server 'alongside' the built-in SME MySQL server allows a SME admin to use web apps that are incompatible with the Centos/SME version of MySQL without making potentially destabilizing changes to core server components.

MariaDB or MySQL can both be installed in an alongside or side-by-side configuration, but MariaDB maintains binary executables compatible with CentOS 5 & 6 and MySQL does not (at least, I could not find any).

Installation

These installation notes are taken from https://mariadb.com/kb/en/installing-mariadb-alongside-mysql/

IMPORTANT: Do not use yum for an 'alongside' ('side-by-side') installation of mariadb.

Create service account

Create a user named mariadb in server-manager. This user account will be used by mariadb when running, and does not need to have a password set.

SME will create a group of the same name automatically.

Download MariaDB and extract into /opt

mkdir -p ~/addons
cd ~/addons
RELEASE=5.5.34
wget https://downloads.mariadb.org/interstitial/mariadb-$RELEASE/kvm-bintar-hardy-x86/mariadb-$RELEASE-linux-i686.tar.gz/from/http://mirror.jmu.edu/pub/mariadb
cd /opt
tar zxvf ~/addons/mariadb-$RELEASE-linux-i686.tar.gz

Create folders and links

RELEASE=5.5.34
cd /opt
ln -s mariadb-$RELEASE-linux-i686 mariadb
mkdir mariadb-data
#
mkdir -p /var/run/mariadb

Setup my.cnf and init.d

Important: On a SME server, 'pid-file' must be specified in /opt/mariadb-data/my.cnf to override the SME default value in /etc/my.cnf

/opt/mariadb-data/my.cnf 

'cp' -f /opt/mariadb/support-files/my-medium.cnf /opt/mariadb-data/my.cnf
sed -i -e '/^port.*3306$/ s/= 3306/= 3307/' /opt/mariadb-data/my.cnf
sed -i -e '/^socket.*= \/tmp\/mysql.sock/ s~= /tmp/mysql.sock~= /opt/mariadb-data/mariadb.sock~' /opt/mariadb-data/my.cnf
#
BASEDIR='basedir\t\t= /opt/mariadb'
DATADIR='datadir\t\t= /opt/mariadb-data'
USER='user\t\t= mariadb'
PIDFILE='pid-file\t= /var/run/mariadb/mariadb.pid'
sed -i -e "/^\[mysqld\]$/ s~\[mysqld\]~\[mysqld\]\n$DATADIR\n$BASEDIR\n$USER\n$PIDFILE~" /opt/mariadb-data/my.cnf

Note: The last 'sed' command will add lines for basedir, datadir, user, and pid into my.cnf every time it is run, even if these lines already exist.

/etc/rc.d/init.d/mariadb 

'cp' -f /opt/mariadb/support-files/mysql.server /etc/rc.d/init.d/mariadb
sed -i "s~^# Provides: mysql$~# Provides: mariadb~" /etc/rc.d/init.d/mariadb
sed -i "s~^basedir=$~basedir=/opt/mariadb~"          /etc/rc.d/init.d/mariadb
sed -i "s~^datadir=$~datadir=/opt/mariadb-data~"      /etc/rc.d/init.d/mariadb
sed -i "s~lockdir/mysql~lockdir/mariadb~"         /etc/rc.d/init.d/mariadb
sed -i "s~bindir/mysqld_safe\ --datadir~bindir/mysqld_safe\ --defaults-file=/opt/mariadb-data/my.cnf\ --datadir~" /etc/rc.d/init.d/mariadb

file and folder ownership

RELEASE=5.5.34
chown -R mariadb:mariadb mariadb-data mariadb mariadb-$RELEASE-linux-i686
chown mariadb:mariadb /var/run/mariadb

Initialize Environment

cd /opt/mariadb
scripts/mysql_install_db --defaults-file=/opt/mariadb-data/my.cnf

Start Server

/etc/rc.d/init.d/mariadb start

set mariadb root password

A freshly installed mariadb server has no password set for the root user.

In order secure your mariadb server yet easily manage mariadb set the root password for mariadb to match the SME mysql root password.

SME has a template-driven sql command specifically for setting the root password: 

CONNECT=--socket=/opt/mariadb-data/mariadb.sock
mysql $CONNECT -password="" < /var/service/mysqld/set.password

Testing

If you set the mariadb root password as shown above you can verify the versions of mysql and mariadb as described in the howto: 

# show mysql version
mysql -e "SELECT VERSION();"

# show mariadb version
CONNECT=--socket=/opt/mariadb-data/mariadb.sock
mysql -e "SELECT VERSION();" $CONNECT

Here are alternative ways to show the mariadb version: 

# show mariadb version using TCP on port 3307
CONNECT="--protocol=TCP --port=3307"
mysql -e "SELECT VERSION();" $CONNECT

# command lines to check mariadb versions
mysql -e "SELECT VERSION();" --protocol=TCP --port=3307
mysql -e "SELECT VERSION();" --socket=/opt/mariadb-data/mariadb.sock

Configure to start at boot

cd /etc/init.d
chkconfig --add mariadb 
chkconfig --levels 3 mariadb on
# SME Server uses runlevel 7...
cp /etc/rc3.d/S64mariadb /etc/rc7.d


Raw notes

#####################################################################################
# BEGIN
#####################################################################################
# yum repos aren't useful; use the side-by-side install
# Install mariadb side-by-side with mysql on SME Server v8
# From: https://mariadb.com/kb/en/installing-mariadb-alongside-mysql/
#
# create user 'mariadb' in server-manager
#
cd ~
mkdir -p addons
cd addons
RELEASE=5.5.34
wget https://downloads.mariadb.org/interstitial/mariadb-$RELEASE/kvm-bintar-hardy-x86/mariadb-$RELEASE-linux-i686.tar.gz/from/http://mirror.jmu.edu/pub/mariadb
cd /opt
tar zxvf ~/addons/mariadb-$RELEASE-linux-i686.tar.gz
ln -s mariadb-$RELEASE-linux-i686 mariadb
mkdir mariadb-data
cp mariadb/support-files/my-medium.cnf mariadb-data/my.cnf
#
# edit my.cnf as described on the mariadb-alongside-mysql howto (link above)
chown -R mariadb:mariadb mariadb-data mariadb mariadb-$RELEASE-linux-i686
#
cp mariadb/support-files/mysql.server /etc/init.d/mariadb
chmod +x /etc/init.d/mariadb
#
# edit /etc/init.d/mariadb as described in the howto
#
cd /opt/mariadb
scripts/mysql_install_db --defaults-file=/opt/mariadb-data/my.cnf
cd /etc/init.d
chkconfig --add mariadb 
chkconfig --levels 3 mariadb on
# SME Server uses runlevel 7...
mv /etc/rc3.d/S64mariadb /etc/rc7.d
#
# customizations not mentioned in the howto:
mkdir /var/run/mariadb
chown mariadb:mariadb /var/run/mariadb
#
# customize the execution line in /etc/init.d/mariadb
#
# version from howto:
#  $bindir/mysqld_safe --defaults-file=/opt/mariadb-data/my.cnf --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args >/dev/null 2>&1 &
#
# version I'm using:
# $bindir/mysqld_safe --defaults-file=/opt/mariadb-data/my.cnf --datadir="/opt/mariadb-data" --pid-file="/var/run/mariadb/mariadb.pid" $other_args >/dev/null 2>&1 &
#
# Finally, testing access requires asking for a password, then entering an empty password (SME feeds the 'root'@'localhost' password to mysql by default:
# mysql -e "SELECT VERSION();" --port=3307 --protocol=TCP -p
#
# set root password in mariadb same as root password in mysql
mysql --port=3307 --protocol=TCP -p < /var/service/mysqld/set.password
#
# Now the example version check from the howto works:    
mysql -e "SELECT VERSION();" --socket=/opt/mariadb-data/mariadb.sock

Gotchas

my.cnf

I have not copied the customization instructions for my.cnf from the mariadb howto page.

Be sure to make all of the changes to my.cnf suggested.

https://mariadb.com/kb/en/installing-mariadb-alongside-mysql/

/var/run/mariadb

I could not get mariadb to run until I manually created the folder for the process id file. 

mkdir /var/run/mariadb
chown mariadb:mariadb /var/run/mariadb

/etc/rc.d/init.d/mariadb

After adding pid-file = /var/run/mariadb/mariadb.pid in /opt/mariadb-data/my.cnf the howto command line worked.

I was unable to make the 'mysqld_safe' command line from the howto work. Where the howto wanted this command: 

$bindir/mysqld_safe --defaults-file=/opt/mariadb-data/my.cnf --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args >/dev/null 2>&1 &

I needed to specify the datadir instead of using the "$datadir" variable: 

$bindir/mysqld_safe --defaults-file=/opt/mariadb-data/my.cnf --datadir="/opt/mariadb-data" --pid-file="/var/run/mariadb/mariadb.pid" $other_args >/dev/null 2>&1 &

If I use the line suggested in the HOWTO, mariadb never starts (although it reports starting on the command line, it records an error in /opt/mariadb-data/<hostname>.err)

Backup & Restore

  • (RequestedDeletion) Nice notes. Would it be worth it to look at the pre-backup and pre-restore events (And config db settings) so MariaDB databases are being backup up ?

/etc/e-smith/events/actions/mariadb-dump-tables

  • Based on /etc/e-smith/events/actions/mysql-dump-tables
  • "CONNECT" options separated out for easy modification and potential movement into db variables
  • "-x" option added to the mysqldump command line to avoid an error about locking log files
#!/bin/sh
#CONNECT=--socket=/opt/mariadb-data/mariadb.sock
CONNECT="--protocol=TCP --port=3307"

if ! $(mysqladmin $CONNECT ping >/dev/null 2>&1)
then
    echo "mariadb is not running - no tables dumped" >&2
    exit 0
fi

mkdir -p /home/e-smith/db/mariadb
for db in $(mysql $CONNECT -BNre "show databases;")
do
    mysqldump $CONNECT  -x --add-drop-table  -QB "$db" -r /home/e-smith/db/mariadb/"$db".dump  || exit 1
done

/etc/e-smith/events/actions/mariadb-load-tables

  • based on /etc/e-smith/events/actions/mysql-load-tables
  • doesn't actually load the dbs
  • NEEDS EXAMINATION!
#!/bin/sh
#CONNECT=--socket=/opt/mariadb-data/mariadb.sock
CONNECT="--protocol=TCP --port=3307"
MARIADATA=/opt/mariadb-data

if ! $(mysqladmin $CONNECT ping >/dev/null 2>&1)
then    
    echo "mariadb is not running - no tables restored" >&2
    exit 0
fi

if [ ! -f $MARIADATA/mysql/user.frm ]
then
    mkdir -p /etc/e-smith/mariadb/init
    for db in $(ls /home/e-smith/db/mariadb/*.dump 2> /dev/null | grep -v '/mysql.dump')
    do
        mv $db /etc/e-smith/mariadb/init/01_$(basename $db .dump).sql
    done
fi

Automation

Backup
  • Link mariadb-dump-tables into the pre-backup event
  • Dumped tables should be included in backups as they are stored under /home/e-smith
cd /etc/e-smith/events/pre-backup
ln -s ../actions/mariadb-dump-tables S20mariadb-dump-tables
Restore - NEEDS WORK

The SME Server mysql restore is complicated by various factors that may not apply to a mariadb "alongside" installation. Here's what would need to be done to restore all mariadb databases:

  • Reinstall mariadb, including setting the password to match the mysql root password
  • Restore the 'dump' files created during pre-backup individually using:
cd /home/e-smith/db/mariadb
CONNECT=--socket=/opt/mariadb-data/mariadb.sock
mysql $CONNECT < <dbname>.dump
Retrieved from "https://wiki.koozali.org/index.php?title=MariaDB_alongside_MySQL&oldid=23223"