Tech:MariaDB

From Meta
Jump to navigation Jump to search

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

Configuration[edit | edit source]

Database configuration is in Puppet.

Master-replica replication[edit | edit source]

As of January 2021, we are using replication for database backups (dbbackup1 for c2 and c4, dbbackup2 for c3).

Dump data from master to replica[edit | edit source]

mariabackup allows you to create a point-in-time dump of the database cluster. In this case, data is streamed to another server (destination) and extracted in /home/dbcopy/backup-db6-10april. The destination directory must exist and be writable before the command is executed!

In this example, we're going to clone db12 (c3) to dbbackup1. dbbackup1 is a server running multiple instances of MariaDB (multi-instance setup). First, it is mandatory to raise the soft limit for open files: [at the source] ulimit -Sn 1000000

Then you can start the mariabackup process:

[at the destination] mkdir /home/dbcopy/backup-db12-10january2020
[at the destination] chown -R mysql:mysql /home/dbcopy/backup-db12-10january2020
[at the destination] chmod 0750 /home/dbcopy/backup-db12-10january2020
[at the source] mariabackup --backup --slave-info --safe-slave-backup --stream=xbstream | ssh -i /home/dbcopy/.ssh/id_ed25519 dbcopy@dbbackup1.miraheze.org "mbstream -x -C /home/dbcopy/backup-db12-10january2020/"

An alternative (might be faster in some situations):

[at the source] mariabackup --open-files-limit=150000 --parallel=2 --backup --slave-info --safe-slave-backup --stream=xbstream | pigz -p 2 | ssh -i /home/dbcopy/.ssh/id_ed25519 dbcopy@dbbackup1.miraheze.org "pigz -dc -p 2 | mbstream -x --parallel=2 --directory=/home/dbcopy/backup-db12-10january2020/"

(the directory at the destination must be created before, please specify the correct date as well)

At the destination, the backup must be prepared, since the data is not consistent yet. You can do this using mariabackup:

[again, raise the soft open files limit] ulimit -Sn 1000000
mariabackup --prepare --open-files-limit=900000 --target-dir=/home/dbcopy/backup-db12-10january2020 --use-memory=2G

Finally, copy the directory to the appropriate location in /srv:

systemctl stop mariadb@c3
rm -rf /srv/mariadb.c3
mv /home/dbcopy/backup-db12-10january2020 /srv/mariadb.c3
chown -R mysql:mysql /srv/mariadb.c3
systemctl start mariadb@c3

Replicate from master[edit | edit source]

You need to change MASTER_HOST, MASTER_PASSWORD, MASTER_LOG_FILE and MASTER_LOG_POS. Never set up replication without TLS (MASTER_SSL) and never disable verification (MASTER_SSL_VERIFY_SERVER_CERT).

Look at the xtrabackup_binlog_info file fir the binlog positions. The first field (e.g. mysql-bin.000150) is the value for MASTER_LOG_FILE, the second field is MASTER_LOG_POS (large digit number). The third field is a global transaction ID, but we don't use GTID for replication yet.

CHANGE MASTER TO 
   MASTER_HOST="<the db master>.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_VERIFY_SERVER_CERT=1;
START SLAVE;

Revoke Grants / Drop User[edit | edit source]

To revoke grants do the following:

  1. Run SELECT User,Host FROM mysql.user;
  2. Run SHOW GRANTS FOR <User_column>@<Host_column>;
  3. Run REVOKE <Text_after_`GRANT`_and_before_ON> on <Table> from <User_column>@<Host_column>;

Example: REVOKE SELECT, PROCESS, REPLICATION CLIENT on *.* from exporter@81.4.127.174;

To drop a user:

  1. Run drop user <User_column>@<Host_column>;

Example: drop user exporter@81.4.127.174;

Restoring Database from a backup[edit | edit source]

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 test3 temporarily as we doin't want to use a production db system for this.
  3. Copy over the .idb files from db4 to test3.
  4. Create a fake db on test3 "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/<db>/<table>.idb (the file name your copying should match the table name in /var/lib/mysql/<db>/).
  8. After that run "ALTER TABLE table_Name IMPORT TABLESPACE;".
  9. Now you can generate an sql file and import into db4.

See also[edit | edit source]

  1. English Wikipedia article: MariaDB