Tech:MariaDB

MariaDB is the chosen database software used in production. Currently we run 10.0.x on database servers.

MediaWiki
All wikis and some necessary databases (centralauth) are stored in MariaDB.

Phabricator
Phabricator database is stored on Tech:Db3.

Configuration
Database configuration is in puppet and managed automatically with restarts when new changes are made.

Freeing up space
On the database server you may run into a situation where there is only 10% space left, although the actual MariaDB data directory is not that big. In that case the server may be the victim of an issue where a large, deleted mysql-slow.log file is still kept open. Even while the file technically doesn't exist anymore, 'df -h' won't show it as free space.

root@db4:~# df -h Filesystem     Size  Used Avail Use% Mounted on udev            7.9G     0  7.9G   0% /dev tmpfs          1.6G  169M  1.5G  11% /run /dev/vda1      377G  267G   92G  75% / tmpfs          7.9G   16K  7.9G   1% /dev/shm tmpfs          5.0M     0  5.0M   0% /run/lock tmpfs          7.9G     0  7.9G   0% /sys/fs/cgroup tmpfs          1.6G     0  1.6G   0% /run/user/1009 tmpfs          1.6G     0  1.6G   0% /run/user/1001 root@db4:~# lsof +L1 | grep mysql-slow mysqld 5550 mysql  413w   REG  254,1 10162498924     0 14680511 /var/log/mysql/mysql-slow.log.1 (deleted) In this example the deleted file is 10162498924 bytes (= +/- 10GB) big, and the server has 92GB left.

Take the pid (in this case '5550') and FD (in this case '413' - strip the letter 'w' from it). Then run the following command: root@db4:~# :>/proc/5550/fd/413 root@db4:~# df -h Filesystem     Size  Used Avail Use% Mounted on udev            7.9G     0  7.9G   0% /dev tmpfs          1.6G  169M  1.5G  11% /run /dev/vda1      377G  258G  101G  72% / tmpfs          7.9G   16K  7.9G   1% /dev/shm tmpfs          5.0M     0  5.0M   0% /run/lock tmpfs          7.9G     0  7.9G   0% /sys/fs/cgroup tmpfs          1.6G     0  1.6G   0% /run/user/1009 tmpfs          1.6G     0  1.6G   0% /run/user/1001

As you can see there is now 101GB available.

Double Processes
Sometimes mysql starts double processes that are not controlled by the sudo service mysql command.

This causes high load/high cpu.

Run this first:

ps aux | grep mysql

Try this first:

systemctl stop mysql.service && /etc/init.d/mysql stop && service mysql stop

If that fails to stop all mysql processes then do:

sudo killall -u mysql

Restoring Database from a backup
To restore a database from a .idb file following these steps.

1. Follow Restore backup first. (Make sure the backup is not a diff rather it needs to be a full backup).

2. Setup mysql on test1 temporarily as we doin't want to use a production db system for this.

3. Copy over the .idb files from db4 to test1.

4. Create a fake db on test1 "create database testdb".

5. Create the table matching the contents of the idb file.

6. After creating the table, run "ALTER TABLE table_name DISCARD TABLESPACE;".

7. Then copy the .idb file to /var/lib/mysql// 2. 3.