Introduction
This guide covers the installation and configuration of MariaDB on Fedora 40 using the 5W1H approach. We will explore the What, Who, Where, When, Why, How, Consequences, and Conclusion of setting up MariaDB.
Overview
What
MariaDB is a popular open-source relational database management system (RDBMS) forked from MySQL, known for its performance, reliability, and ease of use.
Who
This guide is intended for system administrators and developers who need to set up a reliable database server to manage and store data on Fedora 40.
Where
The setup process can be carried out on any server running Fedora 40, whether it's a physical machine, virtual machine, or cloud instance.
When
Setting up MariaDB should be done during a planned maintenance window to avoid potential disruptions to existing services.
Why
Implementing MariaDB on your server offers several advantages:
| Pros | Cons |
|---|---|
| High performance and reliability | Complexity in initial setup |
| Open-source and free to use | Learning curve for new users |
| Strong community support | Requires regular maintenance and updates |
How
Follow these steps to set up Apache HTTPD on Fedora 40:
| Step 1 | Update your system: sudo dnf update -y |
| Step 2 | Install MariaDB: sudo dnf install -y mariadb-server |
| Step 3 | Start and enable the MariaDB service: (``sudo systemctl start mariadb sudo systemctl enable mariadb``) |
| Step 4 | Run the secure installation script: (``sudo mysql_secure_installation``) Follow the prompts to secure your MariaDB installation. |
| Step 5 | Configure the firewall to allow MariaDB traffic: sudo firewall-cmd --permanent --add-service=mysql sudo firewall-cmd --reload |
Consequences
Setting up MariaDB on Fedora 40 can have the following consequences:
| Positive |
|
| Negative |
|
Conclusion
Setting up MariaDB on Fedora 40 is essential for achieving robust, high-performance data management. While the initial setup can be complex and demands regular maintenance, the benefits of a scalable, reliable, and feature-rich database system make MariaDB a valuable addition to any IT infrastructure. By following this guide, system administrators can ensure their database servers are ready to deliver high-quality data management services effectively.
Install : MariaDB
Install MariaDB to configure Database Server.
Step [1]Install MariaDB Server.
[root@bizantum ~]# dnf -y install mariadb-server
[root@bizantum ~]# vi /etc/my.cnf.d/charset.cnf
# create new
# set default charaset
# if not set, default is [latin1]
# for the case of 4 bytes UTF-8, specify [utf8mb4]
# for the case of 3 bytes UTF-8, specify [utf8mb3] (deprecated)
# [utf8] ⇒ alias of [utf8mb3]
[mysqld]
character-set-server = utf8mb4
[client]
default-character-set = utf8mb4
[root@bizantum ~]# systemctl enable --now mariadb
Step [2]If Firewalld is running and also you allow to access MariaDB Server from remote Hosts, allow service. MariaDB uses [3306/TCP].
[root@bizantum ~]# firewall-cmd --add-service=mysql
success
[root@bizantum ~]# firewall-cmd --runtime-to-permanent
success
Step [3]Initial Settings for MariaDB.
[root@bizantum ~]# mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
haven't set the root password yet, you should just press enter here.
Enter current password for root (enter for none):
OK, successfully used password, moving on...
Setting the root password or using the unix_socket ensures that nobody
can log into the MariaDB root user without the proper authorisation.
You already have your root account protected, so you can safely answer 'n'.
# Switch to [unix_socket] authentication or not
# [unix_socket] authentication is already enabled by default, so it's OK with [No]
Switch to unix_socket authentication [Y/n] n
... skipping.
You already have your root account protected, so you can safely answer 'n'.
# set MariaDB root password or not
# [unix_socket] authentication is enabled by default, but
# if you set root password, it's also possible to login with password authentication.
# if not set root password, only OS root user can login as MariaDB root user
Change the root password? [Y/n] n
... skipping.
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
# remove anonymous users
Remove anonymous users? [Y/n] y
... Success!
Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
# disallow root login remotely
Disallow root login remotely? [Y/n] y
... Success!
By default, MariaDB comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
# remove test database
Remove test database and access to it? [Y/n] y
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
# reload privilege tables
Reload privilege tables now? [Y/n] y
... Success!
Cleaning up...
All done! If you've completed all of the above steps, your MariaDB
installation should now be secure.
Thanks for using MariaDB!
# connect to MariaDB with root
[root@bizantum ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.11.6-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
# [Unix_Socket] authentication is enabled by default
MariaDB [(none)]> show grants for root@localhost;
+-----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+-----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION |
+-----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)
# show user list
MariaDB [(none)]> select user,host,password from mysql.user;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.000 sec)
# show database list
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)
# create test database
MariaDB [(none)]> create database test_database;
Query OK, 1 row affected (0.000 sec)
# create test table on test database
MariaDB [(none)]> create table test_database.test_table (id int, name varchar(50), address varchar(50), primary key (id));
Query OK, 0 rows affected (0.108 sec)
# insert data to test table
MariaDB [(none)]> insert into test_database.test_table(id, name, address) values("001", "Fedora", "Hiroshima");
Query OK, 1 row affected (0.036 sec)
# show test table
MariaDB [(none)]> select * from test_database.test_table;
+----+--------+-----------+
| id | name | address |
+----+--------+-----------+
| 1 | Fedora | Hiroshima |
+----+--------+-----------+
1 row in set (0.000 sec)
# delete test database
MariaDB [(none)]> drop database test_database;
Query OK, 1 row affected (0.111 sec)
MariaDB [(none)]> exit
Bye
Step [4]If you'd like to delete all data of MariaDB and initialize it, run like follows.
[root@bizantum ~]# systemctl stop mariadb
[root@bizantum ~]# rm -rf /var/lib/mysql/*
[root@bizantum ~]# mysql_install_db --datadir=/var/lib/mysql --user=mysql
[root@bizantum ~]# systemctl start mariadb
SSL/TLS Setting
Configure SSL/TLS Setting on MariaDB.
Step [1] Get SSL/TLS certificate or Create self signed certificate first. It uses self signed certificate on this example.
Step [2]Configure MariaDB for SSL/TLS.
# copy certificates gotten in [1]
[root@bizantum ~]# mkdir /var/lib/mysql/pki
[root@bizantum ~]# cp /etc/pki/tls/certs/{server.crt,server.key} /var/lib/mysql/pki/
[root@bizantum ~]# chown -R mysql:mysql /var/lib/mysql/pki
[root@bizantum ~]# vi /etc/my.cnf.d/mariadb-server.cnf
# add under [mysqld] section
[mysqld]
ssl-cert=/var/lib/mysql/pki/server.crt
ssl-key=/var/lib/mysql/pki/server.key
[root@bizantum ~]# systemctl restart mariadb
# verify settings
[root@bizantum ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.11.6-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
# OK if status is like follows
MariaDB [(none)]> show variables like '%ssl%';
+---------------------+-------------------------------+
| Variable_name | Value |
+---------------------+-------------------------------+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | |
| ssl_capath | |
| ssl_cert | /var/lib/mysql/pki/server.crt |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_key | /var/lib/mysql/pki/server.key |
| version_ssl_library | OpenSSL 3.2.1 30 Jan 2024 |
+---------------------+-------------------------------+
10 rows in set (0.001 sec)
Step [3]To connect with SSL/TLS from Clients, connect with specifying [ssl] option.
[root@bizantum ~]# mysql --ssl
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.11.6-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
# show status
MariaDB [(none)]> show status like 'ssl_cipher';
+---------------+------------------------+
| Variable_name | Value |
+---------------+------------------------+
| Ssl_cipher | TLS_AES_256_GCM_SHA384 |
+---------------+------------------------+
1 row in set (0.000 sec)
MariaDB [(none)]> exit
Bye
# for no SSL/TLS connection
[root@bizantum ~]# mysql --skip-ssl
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.11.6-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
# value is empty
MariaDB [(none)]> show status like 'ssl_cipher';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Ssl_cipher | |
+---------------+-------+
1 row in set (0.000 sec)
Step [4]To force require users to connect with SSL/TLS, set like follows.
[root@bizantum ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 10.11.6-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
# create a user who is required SSL/TLS
MariaDB [(none)]> create user redhat identified by 'password' require ssl;
Query OK, 0 rows affected (0.00 sec)
# SSL/TLS required users are set [ssl_type] = [ANY]
MariaDB [(none)]> select user,host,ssl_type from mysql.user;
+-------------+-----------+----------+
| User | Host | ssl_type |
+-------------+-----------+----------+
| mariadb.sys | localhost | |
| root | localhost | |
| mysql | localhost | |
| fedora | % | |
| redhat | % | ANY |
+-------------+-----------+----------+
5 rows in set (0.001 sec)
# set SSL/TLS required to an existing user
MariaDB [(none)]> grant usage on *.* to 'fedora'@'%' require ssl;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> select user,host,ssl_type from mysql.user;
+-------------+-----------+----------+
| User | Host | ssl_type |
+-------------+-----------+----------+
| mariadb.sys | localhost | |
| root | localhost | |
| mysql | localhost | |
| fedora | % | ANY |
| redhat | % | ANY |
+-------------+-----------+----------+
5 rows in set (0.001 sec)
MariaDB : Backup
For Backup and Restore MariaDB Data, it's possible to run with [mariabackup].
Step [1]Generally [mariabackup] tool is installed with MariaDB Server for dependency, but if it has not been installed, Install it first.
[root@bizantum ~]# dnf -y install mariadb-backup
Step [2]Run Backup. For example, get backup under [/home/mariadb_backup].
[root@bizantum ~]# mkdir /home/mariadb_backup
[root@bizantum ~]# mariabackup --backup --target-dir /home/mariadb_backup -u root
[00] 2024-05-15 09:16:41 Connecting to MariaDB server host: localhost, user: root, password: not set, port: not set, socket: not set
[00] 2024-05-15 09:16:41 Using server version 10.11.6-MariaDB
mariabackup based on MariaDB server 10.11.6-MariaDB Linux (x86_64)
[00] 2024-05-15 09:16:41 uses posix_fadvise().
[00] 2024-05-15 09:16:41 cd to /var/lib/mysql/
[00] 2024-05-15 09:16:41 Loading plugins
[00] 2024-05-15 09:16:41 open files limit requested 0, set to 1024
[00] 2024-05-15 09:16:41 mariabackup: using the following InnoDB configuration:
[00] 2024-05-15 09:16:41 innodb_data_home_dir =
.....
.....
[00] 2024-05-15 09:16:42 ...done
[00] 2024-05-15 09:16:42 Writing xtrabackup_info
[00] 2024-05-15 09:16:42 ...done
[00] 2024-05-15 09:16:42 Redo log (from LSN 49855 to 49871) was copied.
[00] 2024-05-15 09:16:42 completed OK!
[root@bizantum ~]# ll /home/mariadb_backup
total 12744
-rw-r-----. 1 root root 417792 May 15 09:16 aria_log.00000001
-rw-r-----. 1 root root 52 May 15 09:16 aria_log_control
-rw-r-----. 1 root root 363 May 15 09:16 backup-my.cnf
-rw-r-----. 1 root root 12582912 May 15 09:16 ibdata1
-rw-r-----. 1 root root 12304 May 15 09:16 ib_logfile0
drwx------. 2 root root 4096 May 15 09:16 mysql
drwx------. 2 root root 20 May 15 09:16 performance_schema
drwx------. 2 root root 8192 May 15 09:16 sys
drwx------. 2 root root 64 May 15 09:16 test_database
-rw-r-----. 1 root root 73 May 15 09:16 xtrabackup_checkpoints
-rw-r-----. 1 root root 432 May 15 09:16 xtrabackup_info
Step [3] For restoring data from backup on another host, run like follows. Before restoring, transfer backup data to the target host with [rsync] or [scp] and so on.
# stop MariaDB and remove existing data
[root@node01 ~]# systemctl stop mariadb
[root@node01 ~]# rm -rf /var/lib/mysql/*
# transferred backup data
[root@node01 ~]# ll mariadb_backup.tar.gz
-rw-r--r--. 1 root root 689544 May 15 09:19 mariadb_backup.tar.gz
[root@node01 ~]# tar zxvf mariadb_backup.tar.gz
# run prepare task before restore task (OK if [completed OK])
[root@node01 ~]# mariabackup --prepare --target-dir /root/mariadb_backup
mariabackup based on MariaDB server 10.11.6-MariaDB Linux (x86_64)
[00] 2024-05-15 09:21:33 cd to /root/mariadb_backup/
[00] 2024-05-15 09:21:33 open files limit requested 0, set to 1024
[00] 2024-05-15 09:21:33 Loading plugins from provider_lz4=provider_lz4
[00] 2024-05-15 09:21:33 Loading plugins
[00] 2024-05-15 09:21:33 Plugin parameter : '--plugin_load=provider_lz4=provider_lz4'
[00] 2024-05-15 09:21:33 Plugin parameter : '--prepare'
[00] 2024-05-15 09:21:33 Plugin parameter : '--target-dir'
[00] 2024-05-15 09:21:33 Plugin parameter : '/root/mariadb_backup'
[00] 2024-05-15 09:21:33 This target seems to be not prepared yet.
[00] 2024-05-15 09:21:33 mariabackup: using the following InnoDB configuration for recovery:
[00] 2024-05-15 09:21:33 innodb_data_home_dir = .
[00] 2024-05-15 09:21:33 innodb_data_file_path = ibdata1:12M:autoextend
[00] 2024-05-15 09:21:33 innodb_log_group_home_dir = .
[00] 2024-05-15 09:21:33 InnoDB: Using Linux native AIO
[00] 2024-05-15 09:21:33 Starting InnoDB instance for recovery.
[00] 2024-05-15 09:21:33 mariabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
2024-05-15 9:21:33 0 [Note] InnoDB: Compressed tables use zlib 1.3.0.zlib-ng
2024-05-15 9:21:33 0 [Note] InnoDB: Number of transaction pools: 1
2024-05-15 9:21:33 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
2024-05-15 9:21:33 0 [Note] InnoDB: Using Linux native AIO
2024-05-15 9:21:33 0 [Note] InnoDB: Initializing buffer pool, total size = 100.000MiB, chunk size = 100.000MiB
2024-05-15 9:21:33 0 [Note] InnoDB: Completed initialization of buffer pool
2024-05-15 9:21:33 0 [Note] InnoDB: Buffered log writes (block size=512 bytes)
2024-05-15 9:21:33 0 [Note] InnoDB: End of log at LSN=49871
[00] 2024-05-15 09:21:33 Last binlog file , position 0
[00] 2024-05-15 09:21:33 completed OK!
# run restore
[root@node01 ~]# mariabackup --copy-back --target-dir /root/mariadb_backup
mariabackup based on MariaDB server 10.11.6-MariaDB Linux (x86_64)
[01] 2024-05-15 09:23:35 Copying ./aria_log_control to /var/lib/mysql/aria_log_control
[01] 2024-05-15 09:23:35 ...done
[01] 2024-05-15 09:23:35 Copying ./aria_log.00000001 to /var/lib/mysql/aria_log.00000001
[01] 2024-05-15 09:23:35 ...done
[01] 2024-05-15 09:23:35 Copying ib_logfile0 to /var/lib/mysql/ib_logfile0
[01] 2024-05-15 09:23:35 ...done
[01] 2024-05-15 09:23:35 Copying ibdata1 to /var/lib/mysql/./ibdata1
[01] 2024-05-15 09:23:35 ...done
[01] 2024-05-15 09:23:35 Copying ./mysql/innodb_table_stats.ibd to /var/lib/mysql/mysql/innodb_table_stats.ibd
[01] 2024-05-15 09:23:35 ...done
[01] 2024-05-15 09:23:35 Copying ./mysql/innodb_index_stats.ibd to /var/lib/mysql/mysql/innodb_index_stats.ibd
.....
.....
[01] 2024-05-15 09:23:35 Copying ./sys/x@0024session.frm to /var/lib/mysql/sys/x@0024session.frm
[01] 2024-05-15 09:23:35 ...done
[01] 2024-05-15 09:23:35 Copying ./sys/session_ssl_status.frm to /var/lib/mysql/sys/session_ssl_status.frm
[01] 2024-05-15 09:23:35 ...done
[01] 2024-05-15 09:23:35 Copying ./xtrabackup_info to /var/lib/mysql/xtrabackup_info
[root@node01 ~]# chown -R mysql:mysql /var/lib/mysql
[root@node01 ~]# systemctl start mariadb
MariaDB : Replication
Configure MariaDB Replication settings. This configuration is general Primary-Replica settings.
Step [1]On all Hosts, Install and Start MariaDB Server, refer to here.
Step [2]Change settings and create a user for replication on MariaDB Primary Host.
[root@bizantum ~]# vi /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
# add follows in [mysqld] section : get binary logs
log-bin=mysql-bin
# define server ID (uniq one)
server-id=101
[root@bizantum ~]# systemctl restart mariadb
[root@bizantum ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.11.6-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
# create user : set any password for 'password' section
MariaDB [(none)]> grant replication slave on *.* to repl_user@'%' identified by 'password';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> exit
Bye
Step [3]Change settings on MariaDB Replica Hosts.
[root@node01 ~]# vi /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
# add follows in [mysqld] section : get binary logs
log-bin=mysql-bin
# define server ID (uniq one)
server-id=102
# read only yes
read_only=1
# define own hostname
report-host=node01.bizantum.lab
[root@node01 ~]# systemctl restart mariadb
Step [4]Get Dump-Data on MariaDB Primary Host. After getting Data, transfer it to Replica Hosts with [sftp] or [rsync] and so on.
# create a directory and get Backup Data
[root@bizantum ~]# mkdir /home/mariadb_backup
[root@bizantum ~]# mariabackup --backup --target-dir /home/mariadb_backup -u root
[00] 2024-05-15 09:29:27 Connecting to MariaDB server host: localhost, user: root, password: not set, port: not set, socket: not set
[00] 2024-05-15 09:29:27 Using server version 10.11.6-MariaDB-log
mariabackup based on MariaDB server 10.11.6-MariaDB Linux (x86_64)
[00] 2024-05-15 09:29:27 uses posix_fadvise().
[00] 2024-05-15 09:29:27 cd to /var/lib/mysql/
[00] 2024-05-15 09:29:27 Loading plugins
[00] 2024-05-15 09:29:27 open files limit requested 0, set to 1024
[00] 2024-05-15 09:29:27 mariabackup: using the following InnoDB configuration:
[00] 2024-05-15 09:29:27 innodb_data_home_dir =
.....
.....
[00] 2024-05-15 09:29:29 ...done
[00] 2024-05-15 09:29:29 Redo log (from LSN 49855 to 49871) was copied.
[00] 2024-05-15 09:29:29 completed OK!
Step [5]On Replica Host, Copy back Backup Data of Primary Host and Configure replication settings. After starting replication, confirm the replication works normally to create test database or insert test data and so on.
# stop MariaDB and remove existing data
[root@node01 ~]# systemctl stop mariadb
[root@node01 ~]# rm -rf /var/lib/mysql/*
# transferred backup data
[root@node01 ~]# ll mariadb_backup.tar.gz
-rw-r--r--. 1 root root 689798 May 15 09:30 mariadb_backup.tar.gz
[root@node01 ~]# tar zxvf mariadb_backup.tar.gz
# run prepare task before restore task : OK if [completed OK]
[root@node01 ~]# mariabackup --prepare --target-dir /root/mariadb_backup
mariabackup based on MariaDB server 10.11.6-MariaDB Linux (x86_64)
[00] 2024-05-15 09:32:33 cd to /root/mariadb_backup/
[00] 2024-05-15 09:32:33 open files limit requested 0, set to 1024
[00] 2024-05-15 09:32:33 Loading plugins from provider_lz4=provider_lz4
[00] 2024-05-15 09:32:33 Loading plugins
[00] 2024-05-15 09:32:33 Plugin parameter : '--plugin_load=provider_lz4=provider_lz4'
[00] 2024-05-15 09:32:33 Plugin parameter : '--prepare'
[00] 2024-05-15 09:32:33 Plugin parameter : '--target-dir'
[00] 2024-05-15 09:32:33 Plugin parameter : '/root/mariadb_backup'
[00] 2024-05-15 09:32:33 This target seems to be not prepared yet.
[00] 2024-05-15 09:32:33 mariabackup: using the following InnoDB configuration for recovery:
[00] 2024-05-15 09:32:33 innodb_data_home_dir = .
[00] 2024-05-15 09:32:33 innodb_data_file_path = ibdata1:12M:autoextend
[00] 2024-05-15 09:32:33 innodb_log_group_home_dir = .
[00] 2024-05-15 09:32:33 InnoDB: Using Linux native AIO
[00] 2024-05-15 09:32:33 Starting InnoDB instance for recovery.
[00] 2024-05-15 09:32:33 mariabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
2024-05-15 9:32:33 0 [Note] InnoDB: Compressed tables use zlib 1.3.0.zlib-ng
2024-05-15 9:32:33 0 [Note] InnoDB: Number of transaction pools: 1
2024-05-15 9:32:33 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
2024-05-15 9:32:33 0 [Note] InnoDB: Using Linux native AIO
2024-05-15 9:32:33 0 [Note] InnoDB: Initializing buffer pool, total size = 100.000MiB, chunk size = 100.000MiB
2024-05-15 9:32:33 0 [Note] InnoDB: Completed initialization of buffer pool
2024-05-15 9:32:33 0 [Note] InnoDB: Buffered log writes (block size=512 bytes)
2024-05-15 9:32:33 0 [Note] InnoDB: End of log at LSN=49871
[00] 2024-05-15 09:32:33 Last binlog file , position 0
[00] 2024-05-15 09:32:33 completed OK!
# run restore
[root@node01 ~]# mariabackup --copy-back --target-dir /root/mariadb_backup
mariabackup based on MariaDB server 10.11.6-MariaDB Linux (x86_64)
[00] 2024-05-15 09:32:33 cd to /root/mariadb_backup/
[00] 2024-05-15 09:32:33 open files limit requested 0, set to 1024
[00] 2024-05-15 09:32:33 Loading plugins from provider_lz4=provider_lz4
[00] 2024-05-15 09:32:33 Loading plugins
[00] 2024-05-15 09:32:33 Plugin parameter : '--plugin_load=provider_lz4=provider_lz4'
[00] 2024-05-15 09:32:33 Plugin parameter : '--prepare'
[00] 2024-05-15 09:32:33 Plugin parameter : '--target-dir'
[00] 2024-05-15 09:32:33 Plugin parameter : '/root/mariadb_backup'
.....
.....
[01] 2024-05-15 09:33:02 Copying ./xtrabackup_info to /var/lib/mysql/xtrabackup_info
[01] 2024-05-15 09:33:02 ...done
[00] 2024-05-15 09:33:02 completed OK!
[root@node01 ~]# chown -R mysql:mysql /var/lib/mysql
[root@node01 ~]# systemctl start mariadb
# confirm [File] and [Position] value of master log
[root@node01 ~]# cat /root/mariadb_backup/xtrabackup_binlog_info
mysql-bin.000002 521 0-101-1
# set replication
[root@node01 ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.11.6-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
# master_host=(Master Host IP address)
# master_user=(replication user)
# master_password=(replication user password)
# master_log_file=([File] value confirmed above)
# master_log_pos=([Position] value confirmed above)
MariaDB [(none)]> change master to
master_host='10.0.0.31',
master_user='repl_user',
master_password='password',
master_log_file='mysql-bin.000002',
master_log_pos=521;
Query OK, 0 rows affected (0.191 sec)
# start replication
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
# show status
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.31
Master_User: repl_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 521
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 555
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Rewrite_DB:
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 521
Relay_Log_Space: 866
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 101
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: optimistic
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
1 row in set (0.000 sec)
Install phpMyAdmin
Install phpMyAdmin to operate MariaDB on web browser from Clients.
Step [1]Install and start Apache httpd, refer to here.
Step [2]Install PHP, refer to here.
Step [3]Install phpMyAdmin.
[root@bizantum ~]# dnf -y install phpMyAdmin php-mysqlnd php-mcrypt php-php-gettext
[root@bizantum ~]# vi /etc/httpd/conf.d/phpMyAdmin.conf
# line 13 : add access permission for your internal network if you need
Require ip 127.0.0.1 10.0.0.0/24
# line 19 : add access permission for your internal network if you need
Require ip 127.0.0.1 10.0.0.0/24
[root@bizantum ~]# systemctl reload httpd
Step [4]If SELinux is enabled, change policy.
[root@bizantum ~]# setsebool -P httpd_can_network_connect on
[root@bizantum ~]# setsebool -P httpd_execmem on
Step [5] Access to [http://(your hostname or IP address)/phpmyadmin/] with web browser from any Clients which are in the Network you set to allow. Then phpMyAdmin Login form is shown, login with a MariaDB user. It needs you login as a user that password is set because [Unix_Socket] authentication is enabled by default and users with no password are not login.
Step [6]If successfully passed authentication, you can operate MariaDB on here.
- Get link
- X
- Other Apps
Bizantum
Fajar Vicky
Posts
Comments
Post a Comment
Thank you for your comment! We appreciate your feedback, feel free to check out more of our articles.
Best regards, Bizantum Blog Team.