Tech:MariaDB

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

Configuration
Database configuration is in Puppet.

Master-slave replication
As of April 2020, we are trying replication between db6 and db7 (cluster for larger wikis).

Dump data from master to slave
mariabackup allows you to create a point-in-time dump of the database cluster. In this case, data is streamed to another server and extracted in /home/dbcopy/backup-db6-10april. The destination directory must exist and be writable before the command is executed! mariabackup --backup --slave-info --safe-slave-backup --stream=xbstream | ssh -i /home/dbcopy/.ssh/id_ed25519 dbcopy@db6.miraheze.org "mbstream -x -C /home/dbcopy/backup-db6-10april/"

Replicate from master
(change the values as needed, but keep MASTER_SSL and MASTER_SSL_VERIFY_SERVER_CERT as-is) CHANGE MASTER TO   MASTER_HOST="db7.miraheze.org", MASTER_PORT=3306, MASTER_USER="replica", MASTER_PASSWORD="REDACTED", MASTER_LOG_FILE='MYSQLBINLOG', MASTER_LOG_POS=POSITION, MASTER_SSL=1, MASTER_SSL_CA='/etc/ssl/certs/Sectigo.crt', MASTER_SSL_CERT='/etc/ssl/certs/wildcard.miraheze.org.crt', MASTER_SSL_KEY='/etc/ssl/private/wildcard.miraheze.org.key', MASTER_SSL_VERIFY_SERVER_CERT=1; START SLAVE;

Revoke Grants / Drop User
To revoke grants do the following:

1. Run.

2. Run.

3. Run

Example:

To drop a user:

1. Run.

Example:

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// .idb (the file name your copying should match the table name in /var/lib/mysql//).

8. After that run "ALTER TABLE table_Name IMPORT TABLESPACE;".

9. Now you can generate an sql file and import into db4.