Move data with schema change between MariaDB servers

Question

I am tasked with the challenge to move data between two MariaDB servers. The source database is a MariaDB 5.x, and the destination database is a MariaDB 10.x. The problem is that I need to add a column to the target.

We are splitting a monolith service into microservices. The new database needs an additional column (found in a separate table in the source database).

Is the below the way to go or are there any nicer way of moving the data?

SELECT CONCAT("insert into table4(id,level,name,levelt2) VALUES(",   quote(table1.id),   ",",    quote(table1.level),   ",",    quote(table2.name),   ",",    quote(table2.level),    ");") as q
       from table1 join table2 on table1.id = table2.table1_id 
       join table3 on table3.id = table2.table3_id
       where table3.name in ('fee', 'fi', 'fo', 'fum');

cat query.sql|mysql --skip-column-names --raw > table4.sql

Answer 1

1. Write a SELECT statement that JOIN the two tables together. Specify exactly the target columns desired (most coming from one table, one from the other table).
2. Then do

Something like

INSERT INTO new (a,b,c,d)
    SELECT x.a, x.b, x.c, y.d
        FROM x
        JOIN y ON ...

For reading from one server and writing to another, see FEDERATEDx.