I finally managed to migrate my wordpress DB to the new server after several weeks of making multiple db dumps and restorations which would fail to load all the content from the old DB. I tried different tools that would export the DB as sql files and used same tools to import even straight command line dumps, but somehow my posts would not display correctly the titles and dates would display but not the post body content.
While trying to trouble shoot the cause I opened the sql file and started running each tables insert query one at a time as you can expect this sucked really bad, but I was ready to do it this way as nothing else was working. When I tried to paste the insert statements for the posts table something weird happened only one character would paste into the sql console which was weird as I expected the several hundred lines I had copied to show up. To look at this some more I posted the text in an editor that could format sql and half way down the file the SQL highlighting changed and displayed half the post as text. This normally happens when there is a character that isn’t escaped properly, it also explained why the backup imports would fail. The weird part with the backup failure is none of the tools would throw an error as you would expect if there was an error with the insert queries. While the curious part of me wanted to investigate this further the rest of me was tired with several weeks of trying to migrate the wordpress data and tried to find another way of moving the data without having to export to SQL files.
The solution I went with was DBeaver which has a data export method of type database which will let you copy a table from one DB to another without having to rely on SQL dumps. This worked like a charm even though it still took a good 10 minutes to copy over everything and keep validating as I went. Like most sites I have an DB backup scripts but since it looks like the backups created in this method may not be of use if I needed them in the future am thinking of just having another offline DB and keep that synced with my main db and use the offline DB for any restorations I might need.