Migrate segments and contacts from M4 to M5, clean installation

[fkrafft]

Your software
My Mautic version is: M4
My PHP version is: 7
My Database type and version is: Maria

Your problem
My problem is:
I want to create a clean installation with the exception of importing the contacts and segments for these contacts.

Is there some convenient way of doing this in bulk instead of exporting each segment and then importing it?

FYI, I dont want to make a full upgrade.
Instead I’ll do a clean M5 installation.

These errors are showing in the log:
N/A

Steps I have tried to fix the problem:
N/A

[joeyk]

The moist clean and convenient way is to export and import via Mautic UI.
Anything else is less convenient.

[fkrafft]

Assume you refer to the GUI and not an existing text command?

We are talking about more than 400 segments with different contacts. That’s a lot of manual clicking in the gui. Assume each export / import takes at least 2 min, equal to 800 min of work if it’s done manually for each segment.

Was thinking of writing a script to export each segment to a csv file and then have a script to import them again. Thoughts?

[joeyk]

Yes, you can do that as well.
You have to make sure you

1. export ALL your contacts from Mautic mysql
2. import all your contacts into the new DB, make sure you import all contacts including contact ID!!!
3. export (via sql) segment information from the lead_lists table, import into the new db
4. import the lead_lists_leads table via mysql. This is the table where segment membership is stored.

[marcus42]

To answer your questions;

“Convenient”: That is quite subjective. Speaking for myself, it means 100% from a CLI

Copy the data from one installation to a second one;
The API will be your friend. It’s possible to bulk export/import using the API.

It will avoid doing direct SQL tx and risk corrupting the DB.

Documentation about the API:
https://developer.mautic.org/#segments

How I would do it:

1. creating a loop to fetch all segments IDs
2. export all the subs for each segments using the IDs from first step
3. create the all segments on the new install (using the data fetch from the first step)
4. Add contacts to segments (fetched on the second step)

[fkrafft]

I check both the API solution and the db solution.

DB solution:
For the db it looks like the segments “lead list” and contacts are linked through the table “lead_list_leads”. See attached picture.
So I would need to export these 3 tables, though the question is if the db structure for these 3 tables have changed from M4 to M5?

API solution:
The segment API doesn’t seem to have a option to list associated contacts.
https://developer.mautic.org/#segments

Instead I would have to use get segment membership for each contact.
https://developer.mautic.org/#get-segment-memberships

Would had been way more convenient if the API can list the contacts associated with each segment.

Will post what I end up doing.

[joeyk]

As alternative you can just export the DB, set up a new Mautic 5, overwrite db with the freshly exported M4 db, and run migrations.
Done.

[fkrafft]

Thanks, that would be neat but the old db is over 120GB.
Wanna start over with a clean installation, only save the contacts and segments.

[joeyk]

Delete your emails, audit logs, pageviews, notifications, and optimize DB.
You’ll have a small DB.
Now do the migration.

[fkrafft]

Done.

How to clean up your mautic db:
Here is a summary for others of what I ended up doing.

Combining Joeyk’s solution, and Housekeeping plugin:

1. Check the size of the db and identify what table that need some cleaning
   Credit: Joeyk:
   The great Mautic weight control – Joey Keller

sudo mysql;
use mymauticdb;
SELECT table_schema AS “Database”, SUM(data_length + index_length) / 1024 / 1024 / 1024 AS “Size (GB)” FROM information_schema.TABLES GROUP BY table_schema;

SELECT table_name AS “Table”,
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS “Size (MB)”
FROM information_schema.TABLES
WHERE table_schema = “mauticdb”
ORDER BY (data_length + index_length) DESC;

2. Clean the select tables that needed cleaning:
   In my case the SQL query showed that “email_stats” table was huge and needed some cleaning.
   Now we know what table to clean up and I prefer not to touch the db my self so I used the housekeeping plugin.
   Credit: leuchtfeuer
   Tutorial - Housekeeping Plugin for Mautic

a. Download the .zip file from their github:

GitHub - Leuchtfeuer/mautic-housekeeping-bundle

b. Transfer the .zip file to your mautic plugins folder …/docroot/plugins
c. Unpack
d. Rename the unzipped folder to "LeuchtfeuerHousekeepingBundle”
e. Cache clear
f. Reload plugins
g. If necessary, activate the plugin from the webadmin tool in mautic.

3. Run Leuchtfeuer’s housekeeping plugin for the specific table you need cleaning.
   I needed to clean the “email_stats”

bin/console leuchtfeuer:housekeeping -m -d [clean for days older than this]

-m is for email_stats
-d is for what days to clean up. It only removed data older than this value.

If you never cleaned the database, then the cleaning can take a long time.
My tip is to start with a few days close to when you made the installation and test.

NOTE:
Cleaning easily crash if you try to clean too many days in a row, recommend to use the (.sh) script found on the github page for housekeeping.
It run one day at the time, avoiding the crash.

The script from the readme at the github also need slight modification to run.

4. Free up the space
   Credit: Joeyk:
   The great Mautic weight control – Joey Keller

When Housekeeping plugin is done, your database is still taking up the previous space but it’s filled with empty information.
To resolve this and free up diskspace by making the database smaller, run the sql query:
“OPTIMIZE table email_stats;”

Good luck!