Importing Data

This procedure describes how to import data to a MariaDB datastore located in CCX.

• The MariaDB Datastore on CCX is denoted as the ‘replica’
• The source of the data is denoted as the ‘source’

note:

If you do not want to setup replication, then you can choose to only apply the sections:

• Create a database dump file
• Apply the dumpfile on the replica

Limitations of MariaDB

MariaDB does not offer as fine grained control over privileges as MySQL. Nor does it have the same level of replication features.

The following properties must be respected in order to comply with the SLA:

• There must be no user management happening on the source, while the data is imported and the replication link is active. This is to avoid corruption of the mysql database and possibly other system databases.
• It is recommended to set binlog-ignore-db on the source to ‘mysql, performance_schema, and sys’ during the data import/sync process.

Preparations

Ensure that the source is configured to act as a replication source.

• Binary logging is enabled.
• server_id is set to non 0.

Also, prepare the replica with the databases you wish to replicate from the source to the replica:

• Using the CCX UI, go to Databases, and issue a Create Database for each database that will be replicated.

Ensure the CCX Firewall is updated:

• Add the replication source as a Trusted Source in the Firewall section of the CCX UI.

It is recommended to keep the CCX datastore standalone (no replicas) until the cutover. Once the migration is complete, you may scale up the CCX datastore by adding more replicas if needed.

note: At the end of the migration, RESET SLAVE ALL is used to detach the replica from the source. This command requires the RELOAD privilege, which the ccxadmin user does not have by default. Contact Elastx support to have the privilege granted on the datastore(s) before you begin the migration — a single ticket may cover several datastores. Ask for the privilege to be revoked once the migration is complete.

Create a replication user on the source

Create a replication user with sufficient privileges on the source:

CREATE USER 'repluser'@'%' IDENTIFIED BY '<SECRET>';
GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'%';

Prepare the replica to replicate from the source

The replica must be instructed to replicate from the source. Make sure to change <SOURCE_IP>, <SOURCE_PORT>, and <SECRET>. Run the following on the replica:

CHANGE MASTER TO MASTER_HOST=<SOURCE_IP>, MASTER_PORT=<SOURCE_PORT>, MASTER_USER='repluser', MASTER_PASSWORD='<SECRET>', MASTER_SSL=0;

note: MASTER_SSL=0 is used since the source typically does not support SSL in this scenario. If your source supports SSL, MASTER_SSL=1 may be used instead. Using MASTER_SSL=1 against a source that does not support SSL results in the error SSL connection error: SSL is required, but the server does not support it.

Create a database dump file of the source

The database dump contains the data that you wish to import into the replica. Only partial dumps are possible. The dump must not contain any mysql or other system databases.

danger: The dump must not contain any mysql or other system databases.

On the source, issue the following command. Change ADMIN, SECRET and DATABASES:

mysqldump -uADMIN -p<SECRET>   --master-data --single-transaction --triggers --routines --events --databases DATABASES > dump.sql

If your database dump contains SPROCs, triggers or events, then you must replace DEFINER. This may take a while:

sed 's/\sDEFINER=`[^`]*`@`[^`]*`//g' -i dump.sql

Apply the dumpfile on the replica

cat dump.sql | mysql -uccxadmin -p -h<REPLICA_PRIMARY>

Start the replica

On the replica do:

SET GLOBAL gtid_strict_mode=0;
START SLAVE;

note: SET GLOBAL gtid_strict_mode=0; is required when replicating from an external source that does not use GTID. With gtid_strict_mode=ON (the default on CCX), replication fails with an out-of-order GTID sequence error. It is safe to turn this setting off while the external replication is active, but it must be turned back on after the cutover (see ‘When the migration is ready’ below).

followed by

SHOW SLAVE STATUS;

And verify that:

             Slave_IO_State: Waiting for source to send event
	     ..
  	     Slave_IO_Running: Yes
             Slave_SQL_Running: Yes

When the migration is ready

STOP SLAVE;
RESET SLAVE ALL;
SET GLOBAL gtid_strict_mode=1;

SET GLOBAL gtid_strict_mode=1; restores strict GTID mode, which is recommended for safer replication between the database nodes on CCX moving forward.

note: RESET SLAVE ALL requires the RELOAD privilege, which the ccxadmin user does not have by default — see the note under Preparations above. Remember to ask Elastx support to revoke the privilege once the migration is complete.

Troubleshooting

If the replication fails to start then verify:

• All the steps above has been followed.
• Ensure that the replication source is added as a Trusted Source in the Firewall section of the CCX UI.
• Ensure that you have the correct IP/FQDN of the replication source.
• Ensure that users are created correctly and using the correct password.
• Ensure that the dump is fresh.