We help IT Professionals succeed at work.

Convert standalone MySQL to 3x nodes MySQL innodb cluster

Hi,
Any procedure for me to, once build the first standalone MySQL, to convert standalone MySQL to a 3x nodes MySQL innodb cluster?
Comment
Watch Question

David FavorFractional CTO
Distinguished Expert 2019

Commented:
These guide may help...

https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-in-mysql - master + slave replication.

https://www.digitalocean.com/community/tutorials/how-to-configure-mysql-group-replication-on-ubuntu-16-04 - master + master replication.

There are many other great guides around for this also.

Search for - mysql master master replication - or just - mysql replication - for your search terms.

Tip: Do your initial setup work with each replicant/instance in an LXD container on the same machine. This makes initial work very fast + far easier to debug.

After replication is working, do a simple lxc move of 2x of your containers off to other machines.
Distinguished Expert 2019
Commented:
If this is a practice run, look at https://dev.mysql.com/doc/refman/8.0/en/mysql-innodb-cluster-introduction.html

define a cluster, add the existing instance with data as a member, principle member.
Then add the other members

in any case, changes to the softwre to point at the cluster name versus the instance name ..... will still be required.
in such a case I would setup a two node INNODB cluster.
During outage window backup/restore the data into the cluster
repoint the software to use the cluster
then deal with adding the third member into the cluster.
marrowyungSenior Technical architecture (Data)

Author

Commented:
David Favor,

"Tip: Do your initial setup work with each replicant/instance in an LXD container on the same machine

My initial setup is just one MySQL 8.0 , for example. not replication at all.

"After replication is working, do a simple lxc move "

what is lxc move ? container move command ?

what if we just use lInux VM ?

"https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-in-mysql - master + slave replication.

https://www.digitalocean.com/community/tutorials/how-to-configure-mysql-group-replication-on-ubuntu-16-04 - master + master replication."

I am sorry both of your link is about manual replication setup ! but in innoDB cluster we do not do it any more as the cluster setup already do it for us if we add nodes one by one.

 InnodB cluster is group replication, no need to add replication one by one .

arnold,

"define a cluster, add the existing instance with data as a member, principle member.

so just install MySQL on that primary node(first node) and then CREATE cluster and that node will be the first primary nodes? then add instance for second and third node using MySQL shell command  ?

"in any case, changes to the softwre to point at the cluster name versus the instance name ..... will still be required."

via the MySQL router!

"in such a case I would setup a two node INNODB cluster.
During outage window backup/restore the data into the cluster"

why 2x nodes first ? you mean a separate DB alias for the INNODB cluster?
why do not create the 3 x nodes together as the cluster directly ?

when we add the second node to the cluster it will sync all data to the second node of the cluster but it just takes time.

and you want the replicate faster so you restore to it?  but add new nodes and let it sync makes no down time. so just wait for it to finish, right? we will already  have existing data when add 2nd nodes.

"repoint the software to use the cluster
then deal with adding the third member into the cluster."

so i am not sure why we have to add 3rd node AFTER repoint the SW To the new cluster...... you add 3 rd nodes to it and we need to backup and restore again to the 3rd nodes again?
Distinguished Expert 2019
Commented:
two nodes, because you are working on the third one.
If you have the resources, then sure, create a three node cluster onto which you will restore the data from the existing one.....
marrowyungSenior Technical architecture (Data)

Author

Commented:
"If you have the resources, then sure, create a three node cluster onto which you will restore the data from the existing one....."

yeah for me, imagine that we ALREADY have one node have all data we need, then we just want to transform that DB to a 3 x nodes InnodB cluster, so I think only the sync of the FULL set of data from primary node is slow, other than that I am not sure why I need to backup and restore!

I will just let it sync and the next day USUALLY all data in secondary and third node, but just need to inform user that during this moment the DB will perform slowly ! depends on how many nodes we all. the stream replication should do a good job on that.

"two nodes, because you are working on the third one."

i am sorry, which one are you answering ?
Distinguished Expert 2019

Commented:
You had two questions.
1 create a complete three node cluster if you have the resources, then restore data.
2) if you do not have the resources, create a two node cluster, restore the data
Then convert the current instance by adding it into the cluster.
marrowyungSenior Technical architecture (Data)

Author

Commented:
hi,

"2) if you do not have the resources, create a two node cluster, restore the data
Then convert the current instance by adding it into the cluster.
Is this your solution?"

so you mean create a separate brand new InnoDB cluster first with 2 x node with no data. but the current nodes we are using has latest data, I am not sure if I restore data to the new cluster can make data sync well if I add current node with latest data as the third node.

once add current nodes ,not sure if it still primary as the new cluster has its own primary.
Distinguished Expert 2019

Commented:
Cluster can designate primary.

Not sure I understand what you are after.

Hopefully, you are testing this to avoid ..the safest approach is new three node cluster, during outage window repoint apps following data restore.


Test first.
Setup a single instNce, then define a cluster, add the current instance as a node in the cluster (single node cluster)
This will be primary.
Add another node. Into the cluster and repeat.

The point being I think the applications relying on the instance, have to be updated to reference the cluster......
marrowyungSenior Technical architecture (Data)

Author

Commented:
"Hopefully, you are testing this to avoid ..the safest approach is new three node cluster, during outage window repoint apps following data restore.
"

has to test it.

"Test first.
Setup a single instNce, then define a cluster, add the current instance as a node in the cluster (single node cluster)
This will be primary.
Add another node. Into the cluster and repeat."

I knew, but the point is , I will have an standalone instance with latest data BEFORE I create the cluster. and the backup and restore of data to NEW cluster is not latest than the existing standalone MySQL so when I add existing MySQL standalone nodes to the NEW cluster there are data lag between existing cluster and existing nodes ( which I just added). this is my concern.

other than that we are fine.
Distinguished Expert 2019

Commented:
This is the reason, bavkup/restore occurs in an outage window when no data is being added.
marrowyungSenior Technical architecture (Data)

Author

Commented:
so you are saying during the backup and restore to the NEW cluster and existing MySQL join in period, no data can be insert and the whole maintenance window just for this single node to cluster transform ?
Distinguished Expert 2019
Commented:
Yes. And update to the application to now use the cluster reference versus the MySQL server...
Every transition occurs in a downtime.
marrowyungSenior Technical architecture (Data)

Author

Commented:
So your method, down time is needed.

what's wrong if I just use the existing one from a new cluster and then keep adding new node has secondary and third slave, no down time right?
Distinguished Expert 2019

Commented:
You have to test, but you would still need to update the application config to use the cluster ref.

Changed in this case no matter how you think it might go, shoukd still be down during down window.
The other deals with the impact on the system when a node is added overhead, performance hit.
marrowyungSenior Technical architecture (Data)

Author

Commented:
"You have to test, but you would still need to update the application config to use the cluster ref."

yes! you mean connect via MySQL router ?

"Changed in this case no matter how you think it might go, shoukd still be down during down window."

for me it just slow down during data replicate to slave ! or down just because application change connection?

"The other deals with the impact on the system when a node is added overhead, performance hit."

during replication is replicating data from primary to slave, as I said.
Distinguished Expert 2019
Commented:
Yes.

Users usually notice if the slowdown is significant and depending on the nature of the slowdown ......and the reliance on ...

whenever dealing with a production environment, caution is always warranted no matter how remote or unlikely is a possible issue that might bring down the ....

things have to be planned out.
Precautions such as data backup in place.

in a complex environment, going from self containted mysql and to try to get it into a 3 node cluster, might be a shortcut you do not want to take.
marrowyungSenior Technical architecture (Data)

Author

Commented:
"whenever dealing with a production environment, caution is always warranted no matter how remote or unlikely is a possible issue that might bring down the ....
"

that's why from time to time we see people keep arranging for down time even no down time is needed, political thinking. User can't blame on us.

"Precautions such as data backup in place.
"

that is daily job.

"going from self containted mysql and to try to get it into a 3 node cluster, might be a shortcut you do not want to take."

you mean I should initially setup the cluster instead of ONLY build a single node ?
David FavorFractional CTO
Distinguished Expert 2019

Commented:
For me, when I setup clusters, I setup all cluster database instances in LXD containers on one machine as a starting point.

This avoids dealing with any network problems, as all traffic flows over the host/machine level lo0 pseudo interface.

Once the cluster is working, then I issue an "lxc move" command to move each instance to the production machine where they will run.

This approach allows highly complex cluster setup/testing to be done with one machine, rather than having X number of machines being taken up by installing clustering software at the host level.

This also means each machine running a cluster node in an LXD container can run other non-cluster containers, to use any additional free CPU cycles available.

Remember database systems end up yielding CPU cycles when I/O is in progress, so you'll likely have much free CPU time on machines running your cluster nodes.
marrowyungSenior Technical architecture (Data)

Author

Commented:
"This also means each machine running a cluster node in an LXD container can run other non-cluster containers, to use any additional free CPU cycles available."

is a good method to save resource.

but can I do it for MS SQL server 2019 cluster?

"Remember database systems end up yielding CPU cycles when I/O is in progress, so you'll likely have much free CPU time on machines running your cluster nodes.
"
Sorry  I do not understand this.
marrowyungSenior Technical architecture (Data)

Author

Commented:
tks bothj.