Installing MariaDB (2017)

Instruction here in part after https://downloads.mariadb.org/mariadb/repositories/#mirror=netcologne&distro=Debian&distro_release=wheezy--wheezy&version=10.2 (instructions for newer Debian versions also exist!.

# add to repository list:
sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8
sudo nano /etc/apt/sources.list

# MariaDB 10.2 repository list - http://downloads.mariadb.org/mariadb/repositories/
deb [arch=amd64,i386] http://mirror.netcologne.de/mariadb/repo/10.2/debian wheezy main
deb-src http://mirror.netcologne.de/mariadb/repo/10.2/debian wheezy main

# do a sql dump of all DBs:
sudo mysqldump -u root -p --all-databases --skip-lock-tables --verbose  --log-error=/mnt/dump/var/backups/manually/MySql-AllDBs-TIMESTAMP.log > /mnt/dump/var/backups/manually/MySql-AllDBs-TIMESTAMP.sql
#backup binaries: 
sudo cp -pr /mnt/storage/var-lib-mysql /mnt/storage/var-lib-mysql-TIMESTAMP-DELETE-LATER

If possible, create a VM snapshot.

# remove mysql:
sudo apt-get remove libdbd-mysql-perl libmysqlclient-dev libmysqlclient16 libmysqlclient18:amd64 dbconfig-common mysql-client mysql-client-5.6  mysql-client-core-5.6 mysql-common mysql-common-5.6 mysql-server-5.1 mysql-server mysql-server-5.5 mysql-server-5.6  mysql-server-core-5.6 php5-mysql php5-mysqlnd python-mysqldb
# Note: in our case remove --purge was necessary.

#install mariadb, phpmyadmin, mediawiki:
sudo apt-get update; sudo apt-get install software-properties-common mariadb-server mariadb-client dbconfig-common phpmyadmin  php5-intl 
# DO NOT INSTALL php5-apcu = APC with php 5.5/5.6, use OpCodeCache instead!
# Note: It may be that php5-mysql causes a problem, we ended using native driver php5-mysqlnd

(If just migrating to innodb 1 file per table, to reduce the size of the main table space, drop all existing databases: EITHER user phpmyadmin, OR: run mysql -uroot -pPASS -e "show databases" | grep -v Database | grep -v mysql| grep -v information_schema| grep -v test | gawk '{print "drop database " $1 ";"}' | mysql -uroot -pPASS, stop mysql/mariadb with sudo service mysql stop, then move ibdata1, ib_logfile0 and ib_logfile1 to a temp place to delete later, consider increasing logfile size in my.cnf, )

# when errors are displayed, display the relevant lines (e.g.  76736 to 76800) with
sed -n '76736,76800p' filename
# if necessary to continue import, modify mysql dump to "insert-ignore" syntax should some inserts cause errors: 
sudo cat original.sql | sed s/"^INSERT"/"INSERT IGNORE"/g > updated.sql
sudo mysql -u root -p --force < updated.sql

## Note: check quality / completeness of update with 
sudo mysql_upgrade --force -u root -p

---

NOTE: when importing data again (sudo mysql -u root -p --force < INSERTIGNORE.sql) we encountered problems with ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.

All errors referred to template_parameter_usage tables. As a result of failing to create the tables, the old (cached) data could not be imported. Specifically: Table 'infoflora_ch_de.template_parameter_usage' doesn't exist Table 'infoflora_ch_fr.template_parameter_usage' doesn't exist Table 'k2n_wiki.template_parameter_usage' doesn't exist Table 'metawiki.template_parameter_usage' doesn't exist Table 'metawiki_bak2015-02-27.template_parameter_usage' doesn't exist Table 'onwiki.template_parameter_usage' doesn't exist Table 'onwiki_test.template_parameter_usage' doesn't exist Table 'specieswiki.template_parameter_usage' doesn't exist Table 'testwiki.template_parameter_usage' doesn't exist Table 'testwiki2.template_parameter_usage' doesn't exist Table 'testwiki20.template_parameter_usage' doesn't exist Table 'testwiki_plusaccountanduser.template_parameter_usage' doesn't exist Table 'wikibasewiki.template_parameter_usage' doesn't exist Table 'zsm_entomology.template_parameter_usage' doesn't exist

---

Note: If you setup a new instance or copy over an existing binary one (loosing user accounts), you might encounter "ERROR 1045 (28000): Access denied for user 'debian-sys-maint'@'localhost'". Enter mysql as root (mysql -uroot -p) and execute "GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY 'PASSWORD';" where PASSWORD is the on defined in /etc/mysql/debian.cnf.

---

NOTE: Query to show how much data is stored in MyISAM and InnoDB (from https://dba.stackexchange.com/questions/8982/what-is-the-best-way-to-reduce-the-size-of-ibdata-in-mysql):

SELECT IFNULL(B.engine,'Total') "Storage Engine",
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(
FORMAT(B.ISize/POWER(1024,pw),3),',',),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(
FORMAT(B.TSize/POWER(1024,pw),3),',',),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Table Size"
FROM (SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')
AND engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
(SELECT 3 pw) A ORDER BY TSize;