| Line 1: |
Line 1: |
| − | ==Install mariadb 'alongside' mysql==
| + | Mariadb notes moved to [[MariaDB_alongside_MySQL]] |
| − | MariaDB is a drop in place 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====
| |
| − | | |
| − | <nowiki>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</nowiki>
| |
| − | | |
| − | ====Create folders and links====
| |
| − | | |
| − | <nowiki>RELEASE=5.5.34
| |
| − | cd /opt
| |
| − | ln -s mariadb-$RELEASE-linux-i686 mariadb
| |
| − | mkdir mariadb-data
| |
| − | #
| |
| − | mkdir -p /var/run/mariadb</nowiki>
| |
| − | | |
| − | ====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'''
| |
| − | <nowiki>'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</nowiki>
| |
| − | 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'''
| |
| − | <nowiki>'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</nowiki>
| |
| − | | |
| − | ====file and folder ownership====
| |
| − | <nowiki>RELEASE=5.5.34
| |
| − | chown -R mariadb:mariadb mariadb-data mariadb mariadb-$RELEASE-linux-i686
| |
| − | chown mariadb:mariadb /var/run/mariadb</nowiki>
| |
| − | | |
| − | ====Initialize Environment====
| |
| − | | |
| − | <nowiki>cd /opt/mariadb
| |
| − | scripts/mysql_install_db --defaults-file=/opt/mariadb-data/my.cnf</nowiki>
| |
| − | | |
| − | ====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:
| |
| − | <nowiki>CONNECT=--socket=/opt/mariadb-data/mariadb.sock
| |
| − | mysql $CONNECT -password="" < /var/service/mysqld/set.password</nowiki>
| |
| − | | |
| − | ====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:
| |
| − | <nowiki># show mysql version
| |
| − | mysql -e "SELECT VERSION();"</nowiki>
| |
| − | | |
| − | <nowiki># show mariadb version
| |
| − | CONNECT=--socket=/opt/mariadb-data/mariadb.sock
| |
| − | mysql -e "SELECT VERSION();" $CONNECT</nowiki>
| |
| − | | |
| − | Here are alternative ways to show the mariadb version:
| |
| − | <nowiki># 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</nowiki>
| |
| − | | |
| − | ====Configure to start at boot====
| |
| − | <nowiki>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</nowiki>
| |
| − | | |
| − | | |
| − | ====Raw notes====
| |
| − | <nowiki>
| |
| − | #####################################################################################
| |
| − | # 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
| |
| − | </nowiki>
| |
| − | | |
| − | ===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.
| |
| − | <nowiki>mkdir /var/run/mariadb
| |
| − | chown mariadb:mariadb /var/run/mariadb</nowiki>
| |
| − | | |
| − | ====/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.
| |
| − | | |
| − | <strike>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)</strike>
| |
| − | | |
| − | ===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
| |
| − | <nowiki>#!/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</nowiki>
| |
| − | ====/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!
| |
| − | <nowiki>#!/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</nowiki>
| |
| − | ====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
| |
| − | <nowiki>cd /etc/e-smith/events/pre-backup
| |
| − | ln -s ../actions/mariadb-dump-tables S20mariadb-dump-tables</nowiki>
| |
| − | =====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:
| |
| − | <nowiki>cd /home/e-smith/db/mariadb
| |
| − | CONNECT=--socket=/opt/mariadb-data/mariadb.sock
| |
| − | mysql $CONNECT < <dbname>.dump</nowiki>
| |
| | | | |
| | ==Install Moodle 2.6 using git== | | ==Install Moodle 2.6 using git== |
