Import large MySQL DB into a Galera cluster

Download Markdown

Table of Contents

Firstly, create your DB on one of the Galera nodes where you copied your .sql file:

  • You can run head -n 50 /tmp/backup.sql to determine what is the expected DB name in the dump, so that you can create the same name to avoid errors.
mysql -u root -p

CREATE DATABASE animerecoapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'animerecoapp_user'@'192.168.%' IDENTIFIED BY 'YourStrongPasswordHere';
GRANT ALL PRIVILEGES ON animerecoapp.* TO 'animerecoapp_user'@'192.168.%';
SHOW GRANTS FOR 'animerecoapp_user'@'192.168.%';
FLUSH PRIVILEGES;
EXIT;

Method 1 - Parallel replication

  • Works if the import is not very large (not larger than the RAM capacity of each node)
  • Increase your limits:
mysql -u root -p

SET GLOBAL wsrep_trx_fragment_size = 10000; -- Fragment every 10,000 bytes
SET GLOBAL wsrep_trx_fragment_unit = 'bytes';
SET GLOBAL wsrep_max_ws_size = 536870912;   -- Drop at 512MB

SET GLOBAL wsrep_max_ws_rows = 0;           -- Remove row limits
SET GLOBAL wsrep_slave_threads = 4;         -- Parallelize replication
  • Import command with a custom buffer:
    • You may need to increase swap or RAM on each node if you hit the error 1180.
mariadb -h 192.168.8.71 -u animerecoapp_user -p \
    --max-allowed-packet=128M \
    --net_buffer_length=16384 \
    animerecoapp < /tmp/backup-db.sql
  • During the import, connect to another node + to the node you are importing it to initially and run the following (pv helps to understand the progress):
sudo apt install pv  # If not already installed

pv /tmp/backup-db.sql | mariadb -u animerecoapp_user -p animerecoapp

Method 2: Off-line import, then replicate (Session Bypass)

  • This is safer for larger imports (> 1 GB).
  • Import it with wsrep_on=OFF - to not replicate (we will need to use the ‘root’ user because SUPER privileges are required for this). We will also throttle the RAM/IO usage to 10 MB/s.
sudo apt install pv  # If not already installed
# This tells MariaDB to write to the OS cache every row but only flushes to the physical SSD once per second. This usually increases speed by 10x-50x.
mariadb -u root -p -e "SET GLOBAL innodb_flush_log_at_trx_commit=2;"
# Give MariaDB enough RAM to hold the entire 1GB import in memory so it doesn't have to touch the SSD
mariadb -u root -p -e "SET GLOBAL innodb_buffer_pool_size = 1536 * 1024 * 1024;"
# Import it to just this instance and disable log for this operation
(echo "SET SESSION wsrep_on=OFF; SET SESSION sql_log_bin=OFF; SET SESSION foreign_key_checks=0; SET SESSION unique_checks=0;"; pv -L 30M /tmp/backup-db.sql) | mariadb -u root -p animerecoapp
# After the import is done, set it back to ensure data safety:
mariadb -u root -p -e "SET GLOBAL innodb_flush_log_at_trx_commit=1;"
  • Once the transfer is finished, verify the data is in there:
SELECT 
    table_schema AS "Database", 
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" 
FROM information_schema.tables 
GROUP BY table_schema 
ORDER BY SUM(data_length + index_length) DESC;
  • And on the disk directly:
du -sh /var/lib/mysql/animerecoapp
  • On another node, force a full sync (SST):
# Stop the service
sudo systemctl stop mariadb
# Move the grastate file to force a sync
sudo mv /var/lib/mysql/grastate.dat /var/lib/mysql/grastate.dat.bak
# Start it to force a sync (SST)
sudo systemctl start mariadb
# Check the logs
journalctl -u mariadb -f
  • Ensure that each node is synced:
mysql -u root -p
SHOW STATUS LIKE 'wsrep_cluster_size';
SHOW STATUS LIKE 'wsrep_local_state_comment';
SHOW STATUS LIKE 'wsrep_ready';
  • Rinse and repeat with other nodes on the same segment.
  • On ANOTHER segment, this approach may not work, as the first node on the other segment will be looking for a local donor only. We will need to force it. This is more aggressive, so it may be best to take a snapshot before proceeding. Also, ensure that enough resources are allocated, especially the required disk space and network bandwidth.
# Stop MariaDB
sudo systemctl stop mariadb

# Clear out the failed database directory specifically
# This ensures the SST has a empty target folder to write to
sudo rm -rf /var/lib/mysql/animerecoapp

# Remove ALL Galera state tracking files
sudo rm /var/lib/mysql/grastate.dat
sudo rm /var/lib/mysql/galera.cache

# Start MariaDB
sudo systemctl start mariadb

Method 3: Last resort

  • Take down the Galera cluster (this will bring downtime)
  • Create a new cluster on the node with the dump and start the mysql service on it.
  • Import the data first
  • Then set up replication

More in MariaDB & Galera