a In case you have a request to migrate clickhouse data between 2 systems, there can be few reasons for this, you can do so by following the below guide,
First, we will dump all data from tables into a file,
SELECT * FROM db.table INTO OUTFILE 'FileName.txt' FORMAT CSV;
The tables that should be migrated depend on what customer uses, if they only use it for Queues, then table dump is not necessary which you will see below why, if they are using ergs and extension statistics, then you should dump erg and extension table content into 2 separate files.
So as an example,
SELECT * FROM default.ext_log INTO OUTFILE 'ext.txt' FORMAT CSV;
This will create a ext.txt file on the above-specified path.
Before moving to the new instance, we also need to check the ID of the last replication for queue log, ext log and erg log, to compare to the new instance once everything is completed.
An example below,
mysql> select * from mysqlreplications; +-----------+--------+ | tname | syncid | +-----------+--------+ | erg_log | 278 | | ext_log | 700 | | queue_log | 61127 | +-----------+--------+
At this point, we will stop mysqlreplicator,
/opt/pbxware/sh/pbxware stop mysqlreplicator
We will then set queue_log syncid to 0
/opt/pbxware/sh/mysql -e "update pbxware.mysqlreplications set syncid='0' where tname='queue_log';"
After this, we need to navigate to the GUI of this PBXware and connect it to new instance with the provided user and password, this will automatically create the DB and tables required for us to sync and reimport the data.
Once done, you will then move the exported files to the new CH instance and reimport this back,
/opt/pbxware/sh/clickhouse-client -q "INSERT INTO pbxware_pandora.ext_log FORMAT CSV" < ext.txt
Do the same for erg.txt file if you also have it.
Once completed, confirm syncid for erg and ext that the numbers are the same as the IDs on the new CH instance.
After you confirmed this, start mysqlreplicator on the PBXware side to have it replicate data from queue_log, after it is completed confirm the syncid is the same as before.