Exporting web site data from one server into another existing Matomo instance

I want to export site data from one Matomo instance into another existing Matomo instance with the https://plugins.matomo.org/Migration plugin, exporting from one servers database into another servers database.

I got the command ready, but it times out and says it can’t connect. So I think I need to use SSH tunneling, but am not sure how to use the example. Specifically the command at the end:

Both Matomo instances may be on different servers with proper firewall rules that restrict database access on target instance. In such case, the easiest way for source server to access target database is to create a ssh tunnel on new port (e.g. 3307) in another terminal. Then, execute to the above command with --target-db-port=3307 instead to access port 3306 on target host. Example:

ssh -NL 3307:localhost:3306 targetuser@targethost

Where should it be run? On my machine or Source server or Target server?

Alternative solution?
Maybe I could export the source code base AND database, import them on the target server temporarily, as a separate Matomo instance, and do the import operation directly on the target server?

I ended up copying the database to the target server, restoring it in its own temporary Matomo instance, and importing directly on the target server. Basically, following these steps first:

How can I move Matomo from one server to another, also migrating the data from one mysql server to another?

… and installing the plugins present in the target Matomo instance. Then I was able to import the site into the existing database with this:

sudo -u www-data ./console migration:measurable --source-idsite=1 --target-db-host= --target-db-username=piwik --target-db-password=MYPASSWORD --target-db-name=piwik_db --target-db-prefix=piwik_ --dry-run

It looked good, so I removed --dry-run and did the import. I did a test run with a single record first, so I knew it was going to end with a horrible error message, even after completing successfully:

Processed ArchiveMigration at 2022-11-12 18:10:27
ERROR [2022-11-12 18:10:27] 19920 Uncaught exception: /var/www/html/website/public_html/tempmatomo/matomo/libs/Zend/Db/Adapter/Pdo/Abstract.php(314): There is no active transaction [Query: , CLI mode: 1]

There is no active transaction

I wasn’t sure about importing a site with siteid 1, since it already existed. But the Migration plugin intelligently gave it a new siteid 5, the next available site ID.

I hope this helps somebody.

1 Like