MariaDB Installation & Configuration settings for protecting it using NetBackup

 Agenda:

1) About MariaDB

2) MariaDB installation on Linux

3) MariaDB installation on Windows

4) Create large dummy data for testing

5) Protection using NetBackup


1) About MariaDB

MariaDB Server is one of the most popular open-source relational databases. It’s made by the original developers of MySQL and guaranteed to stay open source. It is part of most cloud offerings and the default in most Linux distributions.

We could say it's a wrapper over MySQL or compatible drop-in replacement for the widely used MySQL database technology. MariaDB meets the same standard enterprise requirements as MySQL, often with additional features, capabilities and options, and by implementing the MySQL protocol and maintaining compatibility with common MySQL data types and SQL syntax, it's easy to migrate from MySQL to MariaDB without modifying applications.

When it comes to performing queries or replication, MariaDB is faster than MySQL. So if you need a high-performance relational database solution, MariaDB is a good choice. In addition, MariaDB also easily supports a high concurrent number of connections without much performance degradation.

It is built upon the values of performance, stability, and openness, and MariaDB Foundation ensures contributions will be accepted on technical merit. Recent new functionality includes advanced clustering with Galera Cluster 4, compatibility features with Oracle Database and Temporal Data Tables, allowing one to query the data as it stood at any point in the past.

Real businesses relies on MariaDB. Here are some top customers who uses MariaDB to manage huge data. Samsung, RedHat, Nokia, SuSE, Deutsche Bank, DBS Bank, Nasdaq, ServiceNow, Verizon, Walgreens, PhonePe are some of the leading giants who uses MariaDB.

Samsung Cloud team authenticates 10 Billion+ requests daily with MariaDB xpand.

2) MariaDB installation on Linux

The below are the steps to install MariaDB on any Linux platform like RedHat, SuSE, etc.

1) Download the tar file of the MariaDB version you're interested in from: https://mariadb.org/download/ As of today while writing this post the 10.6.8 is the latest version recommended for production systems.

2) Unzip the above file. tar -xvf mariadb-10.6.8-linux-systemd-x86_64.tar.gz

3) (Optional) create mariadb conf file (/etc/my.cnf) with below content:

/etc/my.cnf:

[mysqld]

datadir=<data_directory_path>

user=<user_name>

basedir=<installation_location>

general_log_file=/var/log/mysql/mysql.log

general_log=1

[client]

user=<user_name>

password=<password>


4) Run: <$installation_loc>/scripts/mysql_install_db --user=$user --basedir=$installation_loc --datadir=$datadir

    E.g: /usr/local/mariadb-10.6.8-linux-systemd-x86_64/scripts/mysql_install_db --user=root --basedir=/usr/local/mariadb-10.5.13-linux-systemd-x86_64 --datadir=/home/MariaDB_Data_Dir


5) Start mariaDB service: $installation_loc/bin/mysqld_safe --user=$user --datadir=$datadir &

    E.g: /usr/local/mariadb-10.6.8-linux-systemd-x86_64/bin/mysqld_safe --user=root --datadir='/home/MariaDB_Data_Dir' &


6) cp <$installation_loc>/support-files/mysql.server /etc/init.d/mysql

    e.g: cp /usr/local/mariadb-10.6.8-linux-systemd-x86_64/support-files/mysql.server /etc/init.d/mysql


7) chmod +x /etc/init.d/mysql && chkconfig --add mysql


8) cp $installation_loc/support-files/systemd/mariadb.service /usr/lib/systemd/system/mariadb.service

    e.g: cp /usr/local/mariadb-10.5.13-linux-systemd-x86_64/support-files/systemd/mariadb.service /usr/lib/systemd/system/mariadb.service


9) systemctl enable mariadb.service


10) systemctl start mysql


11) Check the MariaDB service status:

    [Windows] sc queryex type= service state= all | findstr /i maria

    systemctl is-active mysql


12) Set PATH environment variable:

    export PATH=$PATH:<$installation_loc>/bin

    e.g: export PATH=$PATH:/usr/local/mariadb-10.5.13-linux-systemd-x86_64/bin

    you could update .bashrc file to avoid updating env variable repeatedly.


13) Set the password using below query.create my.sql file with below query :

    ALTER USER '$user'\@'localhost' IDENTIFIED BY '<your_password>';

    FLUSH PRIVILEGES;


14) Remove anonymous user from DB:

    DELETE FROM mysql.user WHERE user=''


15) Run below mariadb commands to verify it's up & running:

FLUSH PRIVILEGES;

show databases;

How to stop MariaDB:

    systemctl stop mysql 

    /etc/init.d/mysql stop

    systemctl stop mariadb.service

   Now stop the mariadb and mysql deamons. Their names would be mysqld_safe and mariadbd. we could retreive process id using "ps -ef | grep -i mysql"

    kill -9 <pid>

3) MariaDB installation on Windows

There are couple of methods for windows like msi, zip file. MSI method is easy, you just have to follow the gui instructions carefully. Below you could find the steps for ZIP file installation method.

1) Download the zip file of version you're interested in from here: https://mariadb.org/download/

2) unzip/extract the above file you downloaded.

3) set permissions: icacls "<install_loc>" /grant:r "LOCAL SERVICE":(OI)(CI)F /T
    e.g: icacls "C:\Program Files\mariadb-10.5.13-winx64" /grant:r "LOCAL SERVICE":(OI)(CI)F /T

4) Run: mysql_install_db.exe --service=Mariadb --password=<password>

5) start MariaDB service: net start Mariadb

6) check status: sc query "Mariadb" | find "RUNNING"

7) mysql -u $user --password="<password>" -e "show databases"

Command to stop MariaDB:
    net stop Mariadb

4) Create large dummy data for testing

We could use the MariaDB's FOR loop to create dummy data and once the huge data is created then we could clone the table any number of times.

create table t1(id int, name char(255));

DELIMETER //

FOR i IN 1..1000000000
DO
    INSERT into t1 VALUES (i, 'you can insert any dummy string of long length here');
END FOR;
//

DELIMITER ;

CREATE TABLE t2 LIKE t1;

INSERT INTO t2 SELECT * FROM T1;

5) Protection using NetBackup

The MariaDB could be protected using NetBackup via DataStore policy for On-premise servers. For MariaDB servers in cloud the one can use the Cloud protection plan. The below method is for DataStore policy. Which would be out in the upcoming NetBackup release may be it would be 10.0.1. The NetBackup versions 10.0 and prior have only single method i.e DataStore policy but using out-of-band NB-MariaDB-agent.

# Linux:
========
Backup command:
nbmariadb -o backup -S <primary_server_name> -P <policy_name> -s <schedule_name> -l <mariadb_library_path>
E.g: /usr/openv/netbackup/bin/nbmariadb -o backup -S r7515-117-vm51.sbs.com -P mariadb_bkp -s Default-Application-Backup -l /usr/local/mariadb-10.5.13-linux-systemd-x86_64/lib/

Query command:
nbmariadb -o query -S <primary_server_name>
E.g: /usr/openv/netbackup/bin/nbmariadb -o query -S r7515-117-vm51.sbs.com

Restore command:
nbmariadb -o restore -S <primary_server_name> -id <backup_id> -t <target_dir_path> -portnum <port_number>
E.g: /usr/openv/netbackup/bin/nbmariadb -o restore -S r7515-117-vm51.sbs.com -id 1650553962 -t /home/rest/ -portnum 3306


# Windows:
==========
Backup command:
nbmariadb -o backup -S <primary_server_name> -P <policy_name> -s <schedule_name> -l <mariadb_library_path>
E.g: nbmariadb.exe -o backup -S r7515-117-vm51.sbs.com -P mariadb_bkp -s Default-Application-Backup -l "C:\Program Files\mariadb-10.5.13-winx64\lib"

Query command:
nbmariadb -o query -S <primary_server_name>
E.g: nbmariadb.exe -o query -S r7515-117-vm51.sbs.com

Restore command:
nbmariadb -o restore -S <primary_server_name> -id <backup_id> -t <target_dir_path> -portnum <port_number>
E.g: nbmariadb.exe -o restore -S r7515-117-vm51.sbs.com -id  -t "C:\Program Files\mariadb-10.5.13-winx64\data1" -portnum 3306

Comments

Post a Comment

Popular posts from this blog

KMP Algorithm: Pattern Searching in Text

Agenda: Quick Intro Input Output Complexity Algo Logic Code Real-Life Use Cases Conclusion Sources for further reading 1. Quick Intro The Knuth-Morris-Pratt (KMP) algorithm is a classic string-searching algorithm used to find occurrences of a "pattern" string within a "text" string.  It was devised by Donald Knuth , James H. Morris , and Vaughan Pratt , hence the name KMP .  The key feature of the KMP algorithm is its ability to perform the search efficiently by avoiding unnecessary comparisons . 2. Input The KMP algorithm takes two inputs: Text (T) : The string in which we are searching for a pattern. Pattern (P) : The string we are searching for within the text. 3. Output Indices (I): Starting indices of all occurrences of the pattern in the text. If the pattern is not found, it returns an empty list. 4. Complexity Time Complexity :  O(n + m)   n ---> Length of the text. m ---> Length of the pattern. Sp...
Read more

Z-Function Algorithm: Substring Search

Agenda: Quick Intro Input Output Complexity Algo Logic C++ Code Real-Life use cases Practice Problem Conclusion Sources for further reading Quick Intro The Z-function is a powerful string-matching algorithm used for pattern matching and searching within a given string. Its primary purpose is to find all occurrences of a specified pattern in the text. Definition: Given a string s of length n, the Z-function produces an array Z where Z[i] represents the length of the longest substring starting from s[i] that is also a prefix of s. Input Text(T): The input to the Z-function algorithm is a single string T of length n . Output Z-Array(z): The output of the Z-function algorithm is an array Z of length n where each element Z[i] represents the length of the longest substring starting from the position i in T that matches a prefix of T . Complexity Time: O(n) Space: O(n) Where, n ---> length of input Text....
Read more

Back of the envelope estimations

# Agenda: Overview Common inputs in a requests-per-second (RPS) calculation Example of RPS / QPS calculation Techniques to simplify the calculations Handy conversion Table Capacity or Storage Planning Conclusion # Overview: Back-of-the-envelope math is a very  useful tool in our system design toolbox. In this article, we will go over how and when to use  it, and share some tips on using it effectively. Let’s dive right in. Experienced developers use back-of-the-envelope  math to quickly sanity-check a design. In these cases, absolute accuracy is not that important . Usually, it is good enough to get  within an order of magnitude or two of the actual numbers we are looking for. For example, if the math says at our scale our web  service needs to handle 1M requests per second, and each web server could only handle about 10K  requests per second, we learn two things quickly:     1) W e learn that we will need to cluster of web  servers, with a l...
Read more