Local YUM Repository and a MariaDB Cluster
In this example I will set up a local YUM repository in an AWS instance and use it to install/configure a 2-node MariaDB cluster implementing Standard Replication.
In the AWS console I will select the Red Hat Enterprise Linux 8 64-bit, t2.micro (Free tier eligible) instance.
Additional Configuration:
- Under the Add Tags tab with create (i.e., Add Tag) the Name, MariaDB key/value pair.
- Under the Configure Security Group tab we will add the HTTP (or HTTPS if configuring SSL) rule.
Once configured, we will Launch the instance selecting an existing key pair certificate I created earlier.
Once the instance is up an running (as verified in the AWS console) we are ready to now connect through an SSH-enabled terminal to your instance by running the command:
ssh -i /<path-to-pem>/<mycert>.pem ec2-user@ec2-xxx-xxx-xxx-xxx.compute-1.amazonaws.com
The Public DNS above can be obtained from the instance Description (and it will change every time the instance is re-started). I will from here on generically reference it using 'xxx' for each 8-bit field.
Create and configure your user id by running the following commands (passwd set optional) and log in using your user:
sudo su
useradd <your_user>
passwd <your_user>
mkdir /home/<your_user>/.ssh
cp /home/ec2-user/.ssh/authorized_keys /home/<your_user>/.ssh/
chown -R <your_user>.<your_user> /home/<your_user>/.ssh
Grant root access to your user by running as root the visudo command and adding the line below the ec2-user line:
<your_user> ALL=(ALL) NOPASSWD: ALL
Run the following commands as root user (last to verify that is is up an running):
yum install nginx
systemctl start nginx
systemctl enable nginx
systemctl status nginx
You can now verify that it is running by typing your instance public DNS in the browser (i.e., http://ec2-xxx-xxx-xxx-xxx.compute-1.amazonaws.com/) and verifying that you get the "Welcome to nginx on Red Hat Enterprise Linux!" page (if it fails to start, the journalctl -xe command, as indicated in the output can help troubleshoot the cause)
While I have used yum for this installation, as of RHEL 8 it is being replaced by the dnf installer (either one can be used). Run the following commands as root user to support the YUM repository creation:
yum install createrepo yum-utils
mkdir -p /var/www/html/repos/mariadb
and edit a newly created /etc/yum.repos.d/mariadb.repo file to include the following:
[mariadb]
name=mariadb
baseurl=file:///var/www/html/repos/mariadb
enabled=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-MariaDB
gpgcheck=1
The gpgcheck can be disabled initially but, if you want to set this up as I did, you can run the below commands as root:
cd /etc/pki/rpm-gpg
wget https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
Edit the newly created /etc/nginx/conf.d/repos.conf to include the following (this configuration should automatically be imported by the /etc/nginx/nginx.conf main configuration file):
server {
listen 80;
server_name ec2-xxx-xxx-xxx-xxx.compute-1.amazonaws.com; #change to your real domain
root /var/www/html/repos;
location / {
autoindex on; #enable listing of directory index
}
}
Run the following commands as root to download the specific version 10.4.12 MariaDB (and non-MariaDB) rpms compatible with RHEL-8 (last rm includes any non-rpm files). For this exercise we will only install the Server/Client RPMS and the galera dependency:
cd /var/www/html/repos/mariadb/
yum install wget
wget -r --no-parent --no-directories --accept-regex 'MariaDB.*10.4.12.*.rpm' http://mariadb.mirror.globo.tech//mariadb-10.4.12/yum/rhel8-amd64/rpms/
wget -r --no-parent --no-directories --reject-regex 'MariaDB.*.rpm' http://mariadb.mirror.globo.tech//mariadb-10.4.12/yum/rhel8-amd64/rpms/
rm index.html
createrepo .
Note: If you add RPMS to the repository you can run yum clean all followed createrepo --update /var/www/html/repos/mariadb
Run the below commands (first one will enable the directory files listing/access):
restorecon -R /var/www/html/repos
service restart nginx
Visiting our url http://ec2-xxx-xxx-xxx-xxx.compute-1.amazonaws.com/ will now show the mariadb index. Clicking on the /mariadb folder will display the list of rpms as shown below.
As root user (or sudo) run yum list | grep MariaDB to get a listing like below:
MariaDB-backup.x86_64 10.4.12-1.el8 mariadb
MariaDB-backup-debuginfo.x86_64 10.4.12-1.el8 mariadb
MariaDB-client.x86_64 10.4.12-1.el8 mariadb
MariaDB-client-debuginfo.x86_64 10.4.12-1.el8 mariadb
MariaDB-common.x86_64 10.4.12-1.el8 mariadb
MariaDB-common-debuginfo.x86_64 10.4.12-1.el8 mariadb
MariaDB-connect-engine.x86_64 10.4.12-1.el8 mariadb
MariaDB-connect-engine-debuginfo.x86_64 10.4.12-1.el8 mariadb
MariaDB-cracklib-password-check.x86_64 10.4.12-1.el8 mariadb
MariaDB-cracklib-password-check-debuginfo.x86_64 10.4.12-1.el8 mariadb
MariaDB-devel-debuginfo.x86_64 10.4.12-1.el8 mariadb
MariaDB-gssapi-server.x86_64 10.4.12-1.el8 mariadb
MariaDB-gssapi-server-debuginfo.x86_64 10.4.12-1.el8 mariadb
MariaDB-rocksdb-engine.x86_64 10.4.12-1.el8 mariadb
MariaDB-rocksdb-engine-debuginfo.x86_64 10.4.12-1.el8 mariadb
MariaDB-server-debuginfo.x86_64 10.4.12-1.el8 mariadb
MariaDB-shared.x86_64 10.4.12-1.el8 mariadb
MariaDB-shared-debuginfo.x86_64 10.4.12-1.el8 mariadb
MariaDB-test-debuginfo.x86_64 10.4.12-1.el8 mariadb
MariaDB-tokudb-engine.x86_64 10.4.12-1.el8 mariadb
MariaDB-tokudb-engine-debuginfo.x86_64 10.4.12-1.el8 mariadb
Note that all RPMS shown in the file listing screenshot are not visible (for instance, MariaDB-server is missing). This is because RHEL 8 already comes with its own conflicting RPM distribution named using a lowercase naming convention:
[root@ip-xxx-xxx-xxx-xxx ~]# yum list | grep -i mariadb-server
MariaDB-server-debuginfo.x86_64 10.4.12-1.el8 mariadb
mariadb-server.x86_64 3:10.3.17-1.module+el8.1.0+3974+90eded84 rhel-8-appstream-rhui-rpms
mariadb-server-galera.x86_64 3:10.3.17-1.module+el8.1.0+3974+90eded84 rhel-8-appstream-rhui-rpms
mariadb-server-utils.x86_64 3:10.3.17-1.module+el8.1.0+3974+90eded84 rhel-8-appstream-rhui-rpms
If I temporarily disable that repo when performing the listing, the version I want install is now visible:
[root@ip-172-31-39-159 ~]# yum list --disablerepo=rhel-8-appstream-rhui-rpms | grep -i mariadb-server
MariaDB-server.x86_64 10.4.12-1.el8 mariadb
MariaDB-server-debuginfo.x86_64 10.4.12-1.el8 mariadb
As root user, create the /etc/yum.repos.d/mariadb.repo with contents below (for now, disabling gpgcheck though it can easily be enabled as shown earlier) in a remote VM:
[mariadb]
name=mariadb
baseurl=http://ec2-xxx-xxx-xxx-xxx.compute-1.amazonaws.com/mariadb
enabled=1
gpgcheck=0
Assuming no access issues, the yum listing command above should produce the same output as the local run.
As root user (or using sudo) run the following commands:
yum clean metadata
yum install galera-4
yum install --disablerepo=rhel-8-appstream-rhui-rpms MariaDB-server MariaDB-client
Once installation is complete, start the server by running service mariadb start (and service mariadb status to verify that it is running)
Access the database by running mariadb -u root and once in, run the test command shown below:
[root@ip-xxx-xxx-xxx-xxx ~]# mariadb -u root
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.4.12-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.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.000 sec)
MariaDB [(none)]>
Set Up the secure installation by running the command mysql_secure_installation and entering information as shown below:
[root@ip-xxx-xxx-xxx-xxx ~]# 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 [Y/n] n
... skipping.
You already have your root account protected, so you can safely answer 'n'.
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? [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? [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 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 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!
As root user (or sudo) run below commands:
mkdir /var/log/mariadb
chown -R mysql. /var/log/mariadb
cp /etc/my.cnf.d/server.cnf /etc/my.cnf.d/server.cnf.orig
Edit the /etc/my.cnf.d/server.cnf file to include the below configuration and run service mariadb restart after:
[mysqld]
server-id=1
log_bin=/var/log/mariadb/mariadb-bin.log
Run mariadb -u root -p (enter the password set during the secure installation) and run commands as shown below:
[root@ip-***-***-***-*** yum.repos.d]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.4.12-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.
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO replication_user IDENTIFIED BY '********';
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.002 sec)
MariaDB [(none)]> SHOW MASTER STATUS;
+--------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000001 | 647 | | |
+--------------------+----------+--------------+------------------+
1 row in set (0.000 sec)
MariaDB [(none)]> UNLOCK TABLES;
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]>
and then run service mariadb restart
Before starting the installation we will create another RHEL 8 AWS instance (same flavor) and give it the tag Name MariaDB-Slave. No need to assign any security groups (other than leave the default SSH access).
Once up, we will connect to the instance as ec2-user (we can skip adding our user).
As root user we will create an /etc/yum.repos.d/mariadb.repo file to include the following:
[mariadb]
name=mariadb
baseurl=http://ec2-xxx-xxx-xxx-xxx.compute-1.amazonaws.com/mariadb/
enabled=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-MariaDB
gpgcheck=1
and run the below commands to add the GPG file:
cd /etc/pki/rpm-gpg
yum install wget
wget https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
As root user (or using sudo) run the following commands (note the modified disablerepo name for this instance):
yum clean metadata
yum install galera-4
yum install --disablerepo=rhui-rhel-8-appstream-rhui-rpms MariaDB-server MariaDB-client
As before, starup the instance (service mariadb start), test log on to the database (mariadb -u root), and run the mysql_secure_installation script setting the same options as in the master instance.
As before we will modify the configuration (but use a different value for server-id) and restart the service (service mariadb restart):
mkdir /var/log/mariadb
chown -R mysql. /var/log/mariadb
cp /etc/my.cnf.d/server.cnf /etc/my.cnf.d/server.cnf.orig
Edit the /etc/my.cnf.d/server.cnf file to include the below configuration and run service mariadb restart after:
[mysqld]
server-id=2
log_bin=/var/log/mariadb/mariadb-bin.log
On the AWS Console create a custom security group named MariaDB for Outbound/Inbound traffic on port 3306 (i.e., Go to EC2 Dashboard -> Security groups -> Create security group and select the built-in TCP rule MYSQL/Aurora on the desired source IPs).
Once the security group has been created, attach it to the MariaDB master instance (i.e., on the Console that shows the Running Instances, select the master instance and go to Actions -> Networking -> Change Security Groups, check the MariaDB group, and click on Assign Security Groups)
As mentioned, we will configure the 2-node cluster to implement Standard Replication from Master -> Slave.
Log on to MariaDB (mariadb -u root -p) on the slave and run the commands shown below.
MariaDB [(none)]> SHOW MASTER STATUS;
+--------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000004 | 344 | | |
+--------------------+----------+--------------+------------------+
1 row in set (0.000 sec)
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='ec2-xxx-xxx-xxx-xxx.compute-1.amazonaws.com', MASTER_USER='replication_user', MASTER_PASSWORD='*******', MASTER_PORT=3306, MASTER_LOG_FILE='mariadb-bin.000004', MASTER_LOG_POS=344, MASTER_CONNECT_RETRY=5;
Query OK, 0 rows affected (0.006 sec)
MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> SHOW SLAVE STATUS;
+----------------------------------+-------------------------------------------+------------------+-------------+---------------+--------------------+---------------------+-----------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+----------------+--------------------+------------+-------------+-------------------------+-----------------------------+---------------+-----------+---------------------+-----------------------------------------------------------------------------+------------------+--------------------------------+----------------------------+
| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_SSL_Crl | Master_SSL_Crlpath | Using_Gtid | Gtid_IO_Pos | Replicate_Do_Domain_Ids | Replicate_Ignore_Domain_Ids | Parallel_Mode | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State | Slave_DDL_Groups | Slave_Non_Transactional_Groups | Slave_Transactional_Groups |
+----------------------------------+-------------------------------------------+------------------+-------------+---------------+--------------------+---------------------+-----------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+----------------+--------------------+------------+-------------+-------------------------+-----------------------------+---------------+-----------+---------------------+-----------------------------------------------------------------------------+------------------+--------------------------------+----------------------------+
| Waiting for master to send event | ec2-xxx-xxx-xxx-xxx.compute-1.amazonaws.com | replication_user | 3306 | 5 | mariadb-bin.000008 | 652 | ip-xxx-xxx-xxx-xxx-relay-bin.000006 | 953 | mariadb-bin.000008 | Yes | Yes | | | | | | | 0 | | 0 | 652 | 1970 | None | | 0 | No | | | | | | 0 | No | 0 | | 0 | | | 1 | | | No | | | | conservative | 0 | NULL | Slave has read all relay log; waiting for the slave I/O thread to update it | 8 | 0 | 0 |
+----------------------------------+-------------------------------------------+------------------+-------------+---------------+--------------------+---------------------+-----------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+----------------+--------------------+------------+-------------+-------------------------+-----------------------------+---------------+-----------+---------------------+-----------------------------------------------------------------------------+------------------+--------------------------------+----------------------------+
1 row in set (0.000 sec)
MariaDB [(none)]>
If the slave is listening without errors, you will see the Slave_IO_State set to "Waiting for master to send event" as shown above.
You can now test the replication Master -> Slave by logging onto MariaDB on the Master (i.e., mariadb -u root -p) and running:
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)
MariaDB [(none)]> CREATE DATABASE replication_test;
Query OK, 1 row affected (0.000 sec)
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| replication_test |
+--------------------+
4 rows in set (0.000 sec)
and verify that the database has been created on the slave by running on the slave:
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| replication_test |
+--------------------+
4 rows in set (0.000 sec)
Likewise, dropping the database on the Master (i.e., DROP DATABASE replication_test) will make it disappear on the slave.
Problem: Cannot access the database when I issue command mariadb -u root -p and when attempting a start or restart (i.e., service mariadb start) the conmmand just hangs.
Solution: Issue the command service mariadb status and this might highlight the issue. A common solution might be to explicitly kill the daemon and then issue the restart:
[root@ip-xxx-xxx-xxx-xxx ~]# ps -ef | grep mysqld
mysql 10638 1 0 19:51 ? 00:00:00 /usr/sbin/mysqld
...
[root@ip-xxx-xxx-xxx-xxx ~]# kill -9 10638
[root@ip-xxx-xxx-xxx-xxx ~]# service mariadb restart
As mentioned, you can run SHOW SLAVE STATUS to troubleshoot any issues with the replication. For instance, one error I got initially was:
Got fatal error 1236 from master when reading data from binary log: 'log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master; the first event 'mariadb-bin.000001' at 634, the last event read from 'mariadb-bin.000001' at 634, the last byte read from 'mariadb-bin.000001' at 653.'
This was easily resolved by modifying the configuration in /etc/my.cnf.d/server.cnf on the master and adding the max_allowed_packet property as shown below:
# this is only for the mysqld standalone daemon
[mysqld]
server-id=1
log_bin=/var/log/mariadb/mariadb-bin.log
max_allowed_packet=32M
Note that a configuration change like the one above will require a daemon restart on the master (i.e., service mariadb restart) followed by a mariadb configuration change on the slave (i.e., mariadb -u root -p followed by the sequence of SQL commands STOP SLAVE, SHOW MASTER STATUS, CHANGE MASTER TO... (include the new FILE/POS values), START SLAVE, and SHOW SLAVE STATUS)