By following these steps, you can manage and maintain your MariaDB instance efficiently. From basic setup to advanced configurations, this guide equips you with the necessary tools and commands.

By following these steps, you can manage and maintain your MariaDB instance efficiently. From basic setup to advanced configurations, this guide equips you with the necessary tools and commands to ensure your database runs smoothly and securely.

Install MariaDB

1. Install MariaDB Server:

2. yum -y install MariaDB-server

   1. Start MariaDB Service:

3. systemctl start mariadb

   1. Secure Installation:

mysql_secure_installation

1. Enable General Log:

SQL

SET GLOBAL general_log = 'ON';

1. Check Current Version:

SQL

SELECT VERSION();

1. Create Database:

SQL

CREATE DATABASE sales;

Task base guide for basic DBA tasks

MariaDB DBA task-based user guide

• Install MariaDB

• Enable login

• Check version of MariaDB

• Create database

• Creat backup

• Show binary logs

• Flush log files

• Configure swap file

• Database performance testing

• Setup replication

• Advanced binary log point in time recovery.

• Patch database

1. Install Password Plugin:

SQL

INSTALL SONAME 'simple_password_check';

1. Create Backup:

mariadb-dump -u root -p sales > sales_backup.sql

1. Create Backup:

mariabackup --backup --target-dir=/data/backup/

1. Prepare Backup:

mariabackup --prepare --target-dir=/data/backup/

Managing Binary Logs

SHOW BINARY LOGS

The SHOW BINARY LOGS statement lists all the binary log files on the server. This command is useful for identifying the log files that are currently being used and those that can be purged. It requires the SUPER, REPLICATION_CLIENT, or BINLOG MONITOR privilege (from MariaDB 10.5.2 onwards).

Syntax:

SHOW BINARY LOGS;

Example Output:

+--------------------+-----------+

| Log_name           | File_size |

+--------------------+-----------+

| mariadb-bin.000001 |     19039 |

| mariadb-bin.000002 |    717389 |

| mariadb-bin.000003 |       300 |

| mariadb-bin.000004 |       333 |

| mariadb-bin.000005 |       899 |

| mariadb-bin.000006 |       125 |

| mariadb-bin.000007 |     18907 |

| mariadb-bin.000008 |     19530 |

| mariadb-bin.000009 |       151 |

| mariadb-bin.000010 |       151 |

| mariadb-bin.000011 |       125 |

| mariadb-bin.000012 |       151 |

| mariadb-bin.000013 |       151 |

| mariadb-bin.000014 |       125 |

| mariadb-bin.000015 |       151 |

| mariadb-bin.000016 |       314 |

+--------------------+-----------+

This output shows the names and sizes of the binary log files1.

FLUSH LOGS

The FLUSH LOGS statement closes and reopens all the log files. This is particularly useful when you want to start a new binary log file without stopping the server. It ensures that the current binary log file is closed and a new one is created.

Syntax:

FLUSH LOGS;

Procedure Example:

1. Show Current Binary Logs:

2. SHOW BINARY LOGS;

This command lists all the current binary log files.

1. Flush Logs:

2. FLUSH LOGS;

This command closes the current binary log file and opens a new one.

1. Show Updated Binary Logs:

2. SHOW BINARY LOGS;

This command lists the binary log files again, showing the new log file created after flushing2.

By following these steps, you can manage and maintain your MariaDB binary logs efficiently.

1: MariaDB Knowledge Base - SHOW BINARY LOGS 2: MariaDB Knowledge Base - FLUSH LOGS

System and Configuration Management

Memory and Swap Management

1. Check Memory Usage:

2. free -h

This command displays the amount of free and used memory in the system in a human-readable format.

1. Check Current Swappiness Value:

2. cat /proc/sys/vm/swappiness

This command shows the current swappiness value, which controls the tendency of the kernel to move processes out of physical memory and onto the swap disk.

1. Set Swappiness Value Temporarily:

2. echo 1 > /proc/sys/vm/swappiness

This command sets the swappiness value to 1 temporarily.

1. Set Swappiness Value Permanently:

2. nano /etc/sysctl.conf

Add the following line to the file:

vm.swappiness=1

1. Disable Swap Temporarily:

2. swapoff -a

   1. Enable Swap Again:

3. swapon -a

MariaDB Configuration

1. Edit MariaDB Configuration:

2. nano /etc/my.cnf.d/server.cnf

Add or modify the following settings under the [mariadb] section:

[mariadb]

key_buffer_size=8M

table_open_cache=1200

table_definition_cache=512

sort_buffer_size=16M

1. Restart MariaDB Service:

2. systemctl restart mariadb

   1. Check MariaDB Service Status:

3. journalctl -u mariadb

or

systemctl status mariadb.service -l

Performance Testing with mariadb-slap

1. Basic Load Test:

2. mariadb-slap -a

   1. Load Test with Iterations:

3. mariadb-slap -a -i 10

   1. Load Test with Specific Engines:

4. mariadb-slap -a -i 10 -e InnoDB,MyISAM

   1. Load Test with Concurrency:

5. mariadb-slap -a -i 10 -e InnoDB,MyISAM -c 4

   1. Advanced Load Test:

6. mariadb-slap --delimiter=";" --create-schema=slappy --create="CREATE TABLE t (a int);INSERT INTO t VALUES (5)" --query="SELECT * FROM t" --concurrency=100 --iterations=150

   1. Save Load Test Results:

7. mariadb-slap --delimiter=";" --create-schema=slappy --create="CREATE TABLE t (a int);INSERT INTO t VALUES (5)" --query="SELECT * FROM t" --concurrency=100 --iterations=150 > /tmp/load_test.txt

By following these steps, you can effectively manage system memory, configure MariaDB settings, and perform load testing to ensure optimal performance.

Setting Up the Master Server

1. Edit Configuration File:

2. nano /etc/my.cnf.d/server.cnf

Add the following lines:

[mariadb]

server_id=1

log_basename=master1

1. Grant Replication Privileges:

2. GRANT REPLICATION SLAVE ON . TO repuser@'%' IDENTIFIED BY 'aBcd123%';

3. SELECT * FROM mysql.user WHERE user='repuser'\G

   1. Restart MariaDB:

4. exit

5. systemctl restart mariadb

   1. Check MariaDB Status:

6. journalctl -u mariadb

7. # or

8. systemctl status mariadb.service -l

   1. Get Master Status:

9. SHOW MASTER STATUS;

10. exit

    1. Dump Databases:

11. mariadb-dump --master-data=2 --all-databases --single-transaction > /root/fulldump.sql

Setting Up the Slave Server

1. Transfer Dump File:

2. ip a

3. # Assuming IP is 192.168.0.147

4. scp /root/fulldump.sql root@192.168.0.147:~/fulldump.sql

5. # or

6. rsync -av --progress /root/fulldump.sql centos@192.168.0.147:~/fulldump.sql

   1. Prepare Log Directory:

7. mkdir /var/log/mariadb

8. chown -R mysql:mysql /var/log/mariadb

   1. Edit Configuration File:

9. nano /etc/my.cnf.d/server.cnf

Add the following lines:

[mariadb]

read_only

server_id=2

log_basename=slave1

log_bin=/var/log/mariadb/slave1-bin

log-bin-index=/var/log/mariadb/slave1-bin.index

1. Restart MariaDB:

2. systemctl restart mariadb

   1. Import Dump File:

3. ls -l

4. mariadb < fulldump.sql

   1. Verify Databases:

5. show databases;

6. exit

   1. Set GTID Position:

7. SET GLOBAL gtid_slave_pos = '0-1-1';

   1. Configure Slave:

8. CHANGE MASTER TO

9. MASTER_HOST='192.168.0.146',

10. MASTER_USER='repuser',

11. MASTER_PASSWORD='aBcd123%',

12. MASTER_USE_GTID = slave_pos;

    1. Start Slave:

13. START SLAVE;

14. SHOW SLAVE STATUS\G

Verification and Testing

1. Create Database on Master:

2. CREATE DATABASE mordor;

3. show databases;

   1. Check Databases on Slave:

4. show databases;

   1. Check Process List:

5. SHOW PROCESSLIST;

   1. Verify Slave Status:

6. SHOW SLAVE STATUS\G

Managing Binary Logs Advanced

1. Show Binary Logs:

2. SHOW BINARY LOGS;

   1. Flush Logs:

3. FLUSH LOGS;

   1. Verify Binary Logs:

4. SHOW BINARY LOGS;

Database Operations

1. Select Data:

2. SELECT Name, Population FROM City WHERE ID=456;

3. Drop Table:

4. DROP TABLE City;

5. Update Data:

6. UPDATE Country SET Population=82000000 WHERE Code='DEU';

7. Verify Update:

8. SELECT Name, Population FROM City WHERE ID=456;

Log Analysis and Recovery

1. View Dump File:

2. cat /root/dump.sql | less

3. Stop MariaDB:

4. systemctl stop mariadb

5. Navigate to Log Directory:

6. cd /var/log/mariadb/

7. Search for DROP Statements:

8. mariadb-binlog master1-bin.000002 | grep -i DROP

9. mariadb-binlog master1-bin.000003 | grep -i DROP

10. mariadb-binlog master1-bin.000004 | grep -i DROP

11. mariadb-binlog master1-bin.000004 | grep -B 12 -A 4 -i DROP

12. Recovery Steps:

13. nano RECOVER

14. Add the following steps:

1. Recover from backup

2. Replay master1-bin.000001 from position 330

3. Stop replaying master1-bin.000004 at position 431

4. Start replaying master1-bin.000004 from position 551

1. Edit Configuration File:

2. nano /etc/my.cnf.d/server.cnf

3. Add the following line:

[mariadb]

read-only

1. Start MariaDB:

2. systemctl start mariadb

3. Check MariaDB Status:

4. journalctl -u mariadb

5. # or

6. systemctl status mariadb.service -l

7. Import Dump File:

8. mariadb < /root/dump.sql

9. Replay Binary Logs:

10. mariadb-binlog --start-position=330 /var/log/mariadb/master1-bin.000001 | mariadb

11. mariadb-binlog --stop-position=431 /var/log/mariadb/master1-bin.000004 | mariadb

12. mariadb-binlog --start-position=551 /var/log/mariadb/master1-bin.000004 | mariadb

Verification and Testing

1. Select Data from City:

2. SELECT Name, Population FROM City WHERE ID=456;

3. Select Data from Country:

4. SELECT Name, Population FROM Country WHERE Code='DEU';

5. Edit Configuration File:

6. nano /etc/my.cnf.d/server.cnf

7. # or

8. vi /etc/my.cnf.d/server.cnf

9. Comment out the read-only line:

[mariadb]

# read-only

1. Restart MariaDB:

2. systemctl restart mariadb

3. Check MariaDB Status:

4. journalctl -u mariadb

5. # or

6. systemctl status mariadb.service -l

Patching MariaDB Database on Linux

Prerequisites

• Root or sudo access to the Linux server.

• Backup of the current MariaDB database.

• MariaDB repository configured on your system.

Step-by-Step Guide

1. Update System Packages

2. sudo apt update && sudo apt upgrade -y

   1. Backup MariaDB Database

3. mysqldump -u root -p --all-databases > all_databases_backup.sql

   1. Stop MariaDB Service

4. sudo systemctl stop mariadb

   1. Install MariaDB Patch

      • Download the patch from the official MariaDB repository.

      • Apply the patch:

   2. sudo apt install mariadb-server

   3. Restart MariaDB Service

5. sudo systemctl start mariadb

   1. Verify MariaDB Version

6. mysql -V

   1. Create a New User

7. CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

   1. Grant Privileges to the New User

8. GRANT ALL PRIVILEGES ON . TO 'newuser'@'localhost' WITH GRANT OPTION;

9. FLUSH PRIVILEGES;

   1. Test the New User

10. mysql -u newuser -p

Additional Tips

• Regular Backups: Schedule regular backups to prevent data loss.

• Security: Regularly update passwords and review user privileges.

This guide ensures a smooth patching process for your MariaDB database on a Linux system, maintaining both functionality and security.