# Import large MySQL DB into a Galera cluster

[TOC]

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.

```sql
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:

```javascript
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.

```javascript
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):

```sql
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.

```sql
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:

```sql
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:

```sql
du -sh /var/lib/mysql/animerecoapp
```

- On another node, force a full sync (SST):

```sql
# 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:

```sql
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.

```sql
# 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