11. Entrust your Galera Cluster with data

• Let’s export data from your existing database (assuming you have a single Maria DB and want to migrate a database or more into this HA galera cluster.
• In this case, we will consider migrating a WordPress instance, as those are quite common still.
• Log into the web server via SSH and find the wp-config.php file.

# The exact path may differ
cd /opt/www/html/bachelor-tech
nano wp-config.php

• Copy paste information about the DB:
  • DB user
  • DB password
  • DB name
• Head to the new galera cluster and SSH into ANY of the nodes.

mysql -u root -p

-- Creates a new database for WordPress. You can skip this if the import file creates it.
CREATE DATABASE wordpress_db;

-- This is the main command. It grants a user full rights to the wordpress_db 
-- ONLY when connecting from your app's specific IP address. Replace the IP with your web server's. 
GRANT ALL PRIVILEGES ON wordpress_db.* TO 'wordpress_user'@'1.2.3.4' IDENTIFIED BY 'strong_db_password';

-- Applies the new permissions immediately.
FLUSH PRIVILEGES;

• Connect via SSH to your original MariaDB database and run an MySQL dump command to export its data.

mysqldump -u root -p wordpress_db > /tmp/wordpress_backup.sql

• Then from that machine, assuming you have rsync installed, you can rsync that file to one of the nodes in the cluster. For a quick transfer, you can temporarily disable ufw (or you can temporarily allowlist it).
• We do not need to switch off the firewall on the Galera cluster, since we have previously allowed it for the same subnet (unless you set it up differently). If you do get blocked and need to quickly copy it over, then on the Galera node, you can run sudo systemctl stop ufw and then start it back on afterwards.

# On the original Maria DB single instance, change the following to suite your SSH port, file names and IP:
rsync -rvz -e 'ssh -p 2222' --progress /tmp/your_exported_db.sql [email protected]:/tmp

• Let’s import it into your new cluster – connect to the node in your cluster where you copied the data over and import it.

mysql -u root -p your_galera_db < /path/to/db.sql

• Advanced cases only: for DBs that are larger than 50 GB in size and if your cluster does not contain much data otherwise, you could consider running mysql -u root -p -e "SET wsrep_on=OFF; SOURCE /path/to/your/db.sql;" db_dump.sql . The additional command turns off Galera’s replication for each INSERT command, as Galera must replicate and get approval (certify) for each of these transactions across all nodes in the cluster. So essentially, we say ‘just execute all of the following commands locally without replicating them one-by-one’ and the replication will start only after it is all done. However, then we would need to perform a full State Snapshot Transfer (SST), such as by logging into each node, switching mariadb off, removing the content of /var/lib/mysql/and then switching it back on. In most cases, this approach is not needed.
  • Let’s verify that the data is available on other nodes. Connect to another node and run this command:

# On another Galera node:
mariadb -u root -p

-- OPTION 1 - list the size of all the DBs in your instance:
SELECT 
    table_schema 'DB Name', 
    SUM(data_length + index_length) 'Size in Bytes', 
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) 'Size in MiB' 
FROM information_schema.tables 
GROUP BY table_schema;

-- OPTION 2 - Examine the size of a particular DB
SELECT 
    SUM(data_length + index_length) 'Size in Bytes', 
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) 'Size in MiB' 
FROM information_schema.tables
WHERE table_schema = 'your_db';

Troubleshooting Import

• If the DB size on the other nodes differs from the one where you imported it slight variations are ok. However, if we are talking about several or more MBs of difference, then be on alert. Re-importing will not help.

Option 1 – InnoDB tables are being used

• Likely, you have some MyISAM or MEMORY tables while Galera transfers only InnoDB tables. Let’s verify that by comparing the fully imported DB versus another one that the DB was supposed to replicate to but did not fully:

# On the original node where you imported the DB into:
mysql -u root -p

USE your_db;

SELECT
    TABLE_NAME,
    ENGINE,
    TABLE_ROWS AS 'Rows',
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS 'Total MiB'
FROM
    information_schema.TABLES
WHERE
    TABLE_SCHEMA = 'iriskayan_com_db'
    AND ENGINE IN ('MyISAM', 'MEMORY');

• The output will show the size of each table for engines MyISAM and MEMORY . See below for a comparison between the original and another node to which it was suppose to replicate:

• As you can see, the tables of this engine type were not replicated. This is by design. Galera’s “synchronous” replication (its certification-based replication) relies on the storage engine being transactional. This means the engine must support ACID properties, especially the ability to rollback a transaction.
  • InnoDB: Is fully transactional and supports rollback. This makes it suitable for Galera’s replication mechanism.
  • MyISAM: Is not transactional. It doesn’t support rollbacks. Once a change is made, it’s final. If a conflict occurs during certification, Galera has no way to undo the changes already made locally on the MyISAM table, leading to data inconsistency. See MariaDB’s official guide.
  • MEMORY: Is also not transactional and shares the same limitations as MyISAM regarding replication consistency.
• So how do we remedy the situation? Thankfully, it is relatively simple. We will need to change the engine type in the dump and re-import it back. Let’s start by removing the DB from our cluster:

# Login and remove the DB from the cluster:
mysql -u root -p

# Remove it and create a fresh new one:
DROP DATABASE your_db;
CREATE DATABASE your_db;

# Grant privileges as before:
GRANT ALL PRIVILEGES ON wordpress_db.* TO 'wordpress_user'@'X.Y.I.Z' IDENTIFIED BY 'strong_db_password';
FLUSH PRIVILEGES;
EXIT;

# Modify the table properties from the MySQL dump:
sed \\
  -e 's/ENGINE=MyISAM/ENGINE=InnoDB/g' \\
  -e 's/ENGINE=MEMORY/ENGINE=InnoDB/g' \\
  /tmp/mydb_backup.sql > /tmp/mydb_fixed.sql

# Re-import it:
mysql -u root -p your_db < /tmp/tour_db_fixed.sql
#mysql -u root -p iriskayan_com_db < /tmp/iriskayan_db_fixed.sql

Option 2 – low memory limit in the MariaDB configuration (less likely the case). How to check? On any Galera cluster node:

mysql -u root -p

SELECT
    VARIABLE_NAME,
    VARIABLE_VALUE AS 'Value in Bytes',
    ROUND(VARIABLE_VALUE / 1024 / 1024, 2) AS 'Value in MB'
FROM information_schema.GLOBAL_VARIABLES
WHERE VARIABLE_NAME IN ('wsrep_max_ws_size', 'max_allowed_packet');

• This will reveal the values in megabytes. We have configured the max_allowed_packet before, but in case you skipped this step or sticked to your own config – if the value is anything smaller than 256 MB, you could change it in the MariaDB config file:

nano /etc/mysql/mariadb.conf.d/60-galera.cnf

# Locate or add these rows:
max_allowed_packet = 512M
wsrep_max_ws_size = 512M

• Congratulations on your data import! Now you need to change the configuration in your web application to point to the virtual IP address that your load balancer operates with. That is also where you can observe where the traffic is going.
• For example, in WordPress, that means going to the wp-config.php file and modifying the DB information there.

Go back to course overview: Deploy MariaDB Galera Cluster on Proxmox