Going from 5.5 to 5.7

(It’s difficult to format when you copy and paste from another media even Notepad or Word?)

I have:

Cluster 1:

(This cluster must be up as much as possible until the data is copied to the new cluster)

Debian 7

Percona XtraDB 5.5

XtraBackup 2.2

DB1 (I have existing data)

Cluster 2:

(This cluster can easily be down for several hours to start with, but after the data is copied to it, it must be up as much as possible)

Ubuntu 18

Percona XtraDB 5.7

XtraBackup 2.4

DB2 (I have existing data)

I want to remove cluster 1 and import its data into cluster 2:

cluster 1 cluster 2


d1 d2 d3 --> d1 d2 d3


Options:

A. Take down cluster 1 for several hours, do a “logical backup”, import that backup into cluster 2.

B. Use a temporary node beginning with XtraDB 5.5 and use that to upgrade and sync to the XtraDB 5.7 cluster.

A. is pretty simple, but it could mean a long window is required with cluster 1 down. Option B might allow me to get things done more quickly?

For B., I was thinking:

-Step 1: Add d4 to cluster 1

cluster 1


d1 d2 d3 +d4 (CentOS 7, XtraDB 5.5, XtraBackup 2.2) <–CentOS 7 seems to be the best choice of newest OS that supports all the latest XtraDB 5.5->5.7 versions


I understand I can add a node by simply telling d4 that it forms a cluster with d3. This way I don’t have to restart my entire cluster so I add the d4 information to d1,d2,d3.

-Step 2: Allow d4 to sync.

-Step 3: Take down entire cluster 1.

-Step 4: Upgrade d4 to XtraDB 5.6, then XtraDB 5.7 using “in-place” upgrade instructions.

-Step 5: I must have to take a full backup of cluster 2 here?

-Step 6: Add d4 to cluster 2

-Step 7: I probably have to make d4 my read-write master node.

-Step 8: Reconfigure cluster 2 so that d1, d2, d3 all know about d4 and try to do an SST.

-Step 9: I prefer to have d1 as my read-write master node, so once the SST is complete, make d1 my read-write master node again.

-Step 10: Restore the full backup from #5 to restore the original data in cluster 2.

-Step 11: At some point, reconfigure cluster 2 to remove d4.

Hi, may I suggest you there are a few methods were you don’t need to take down cluster 1 at all.

For example one way would be to run xtrabackup on one of the reader nodes in cluster 1 to seed the new nodes. Then setup async replication between the 2 clusters until you are ready for the cutover.

That’s interesting, but it would mean xtrabackup 2.2 (old cluster) having to talk to xtrabackup 2.4 (new cluster) and that is not supported?

Xtrabackup itself doesn’t need to talk to each other… just is used to backup/restore 5.5, then you can upgrade in place on the new servers and set up replication. Replication from 5.5 to 5.7 that’s not supported but it normally works fine most of the time (there might be edge cases so you need to test). Worst case you can set up an intermediate 5.6 server.

@marcoshaw As said above, this is fairly simple. Use xtrabackup 2.4 on cluster 1 to take a streaming backup of one of the readers, and stream it to cluster 2. Once that is done, bootstrap start cluster 2, 5.7, only start a single node. Set up async replication from cluster 1 node to cluster 2 node. Wait for catchup. Use pt-table-checksum to verify all data is the same between cluster 1 and cluster 2.

After verifying, start node 2 in cluster 2. This will SST. Wait for finish. Repeat for node 3 in cluster 2. You now have cluster 1 replicating to cluster 2, all data in sync. Shut down cluster 1 whenever you are ready.

1 Like

I think where I’m stuck (and don’t have enough experience) is the use of xtrabackup… I believe I was told xtrabackup 2.4 is built against xtradb 5.6+. So, xtrabackup 2.4 cannot be installed on my “cluster 1” which is running xtradb 5.5.

I haven’t quite figured out if you’re telling me I can run xtrabackup 2.4 on/in cluster 2 to “pull” (aka “stream”?) from cluster 1.

Newer versions of xtrabackup will work just fine with older versions of MySQL. So, xtrabackup 2.4 works with 5.5, 5.6, and 5.7.

2 Likes