View source for 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: 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;