asked on

MySQL InnoDB cluster installation guide

hi,

any clear to understand and follow installation guide to quickly setup MySQL InnoDB cluster? 3 x DB node and 1 x nodes for MySQL router and MySQL Shell ?

we are told to use InnoDB cluster but not NDB cluster.

test guide are also good.

David Favor

https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-in-mysql provides one simple guide. The also have a master-master setup guide.

Setup is simple.

Getting your setup to work initially + when ever one instance fails, will require a good bit of time.

And, when I run replicated servers I target doing a failover once ever month, just to make sure the entire system is working.

Rarely is replication actually required, for load balancing or for backup integrity.

If this is your first time working with replication, likely good to hire someone to assist you with setup + ongoing, daily tuning/maintenance.

ASKER CERTIFIED SOLUTION

theGhost_k8

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

David Favor

Likely good to mention a couple more considerations also.

1) Use a Kernel 4.15+ for fastest + most stable network stack, so Ubuntu Bionic provides a good OS starting point. Using common CentOS offerings provide old Kernels (3.10.x last I checked).

2) Use latest MariaDB packages directly from the MariaDB stable repository. Many Linux Distros have plans to completely remove MySQL from their repositories, as MariaDB is faster, more stable, adding in new storage engines as soon as they become stable.

This will avoid you hitting problems hitting old Kernel bugs + slowness. Using latest MariaDB tends to provide a different experience than trying to get MySQL to work.

skullnobrains

mysql does not provide an actual cluster. mariadb does.

mariadb debian packages do not contain the galera cluster part so cannot be used without lots of pain
mariadb provides ppas for ubuntu : http://mariadb.mirrors.ovh.net/MariaDB/repo/...

mariadb is faster and more stable on native bsd than in any out-of-the-box linux ( from my personal experience )

--

the best doc is online on the mariadb site, but basically :

configure all hosts using the same config. here is an example

binlog_format=ROW
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
innodb_doublewrite=1
wsrep_on = on
wsrep_provider=/usr/lib/galera/libgalera_smm.so 
wsrep_sst_method=rsync	# not compatible with ssl if required
wsrep_cluster_address=gcomm://`printf "$sqld_cluster_members" | tr "[:space:]" ,`
wsrep_cluster_name=$sqld_cluster_name
wsrep_node_address=`hostname -s`
wsrep_node_name=`hostname -s`

Open in new window

you are expected to understand `hostname -s` is to be replaced with the hostname, $sqld_cluster_name is a variable ( pick anything as long as all members agree ), and the mess after gcomm:// simply produces a comma-separated list of hosts. it is not required but best practice to configure all hosts in every member's config.

... and bootstrap the cluster. on ubuntu, use the "galera_new_cluster" command which circumvents idiocies made in systemd. on other oses there is usually a flag ( --newcluster or --bootstrap or --wsrep_cluster_address=gcomm:// without any member listed )

theGhost_k8

Hello Marrow,

Apart from my previous comment I have a non-technical tip here.
Consider sticking to the question here and not to get diverted to other topics, that will change the course of this question. If you're interested for comparison, raise a separate question for mysql vs mariadb.

Thanks.