InnoDB cluster - it works, and it seems to be exactly as promised

I do ATSkami. And somehow it happened that from the very first order they wanted fault tolerance from me. One of the key components of a modern PBX (like any information system, probably) is the database, which stores both data on the current state of the system, as well as any configuration parameters. Naturally, the fall of the database leads to the breakdown of the entire system. It all started with MASTER-MASTER replication in MySQL (exclusively for switching speed), then there were experiments with MySQL over DRBD. It all lived in the pacemaker / corosync infrastructure. There went IP-addresses, gateways and other Labudi. In due course it even began to work somehow more or less steadily. But then I came across a couple of servers where DRBD couldn’t be done, I was disappointed with MASTER-MASTER for a long time (it always breaks at me, such replication), and without a fault-tolerant database the whole point of the solution was lost. I came across the name of the InnoDB cluster and I decided: “was-was-was”. What came out of it - look under the cut.

I do everything on Debian Jessie. In other systems, the differences will be, but not very significant.

We will need:

• MySQL APT repository
• MySQL Shell - for Debain users at the very bottom there is a link where you can download compiled binaries, for what reason they did not fall into the package just for Debain for me it remains a mystery
• patience
• patience
• and once again patience

Download (this can only be done manually, after registering on the Orakla website) and install the repository file, update our cache, install the MySQL Router (interesting beast, let's get acquainted below) and MySQL Client:

dpkg -i mysql-apt-config_0.8.6-1_all.deb apt-get update apt-get install mysql-router apt-get install mysql-client 

Unpack and decompose the MySQL Shell from the archive. Moving on, set the server:

 apt-get install mysql-server 

Here we install ALL the proposed components (note that the third component in the list is not selected by default, but it will be needed). And we stop the server started right away by the zealous installer:

 systemctl stop mysql 

Next, go to
/etc/mysql/mysql.conf.d/mysqld.cnf and write something like this:

 [mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql log-error = /var/log/mysql/error.log bind-address = 0.0.0.0 port = 3300 symbolic-links=0 # Replication part server_id=3 gtid_mode=ON enforce_gtid_consistency=ON master_info_repository=TABLE relay_log_info_repository=TABLE binlog_checksum=NONE log_slave_updates=ON log_bin=binlog binlog_format=ROW # Group replication part transaction_write_set_extraction=XXHASH64 loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" loose-group_replication_start_on_boot=off loose-group_replication_local_address= "1.1.1.1:33061" loose-group_replication_group_seeds= "1.1.1.1:33061,1.1.1.2:33061,1.1.1.3:33061" loose-group_replication_bootstrap_group= off 

Here it is necessary to stop in more detail.

The first option that deserves our attention is the port. I recommend setting its value to be different from 3306. Why - it will become clear below (we will hang something else at 3306)

Next: server_id . For each mysql server in the cluster, this value must be unique.

The option that ate a couple of hours of my time is innocuous (seemingly) group_replication_local_address . This option tells you on which address / port to listen to requests for replicas from the local database. The algorithm by which MySQL determines whether the IP address specified in this field is local, I could not guess. On a machine with 2 active interfaces and three IP addresses suspended on these interfaces, only one address arranged MySQL.

And the last: group_replication_group_seeds - it lists the sockets to which you need to apply (in the specified order) with attempts to order replicas of actual data when connecting.

For details, please refer to the official GROUP REPLICATION documentation page.

So, deploying on the MySQL machine and configuring it in this way we move on. Delete all contents of /var/lib/mysql I'm serious. Got themselves together, went and removed. If there is something that is dear to you as a memory - make a backup beforehand. Can. We start mysql:

 systemctl start mysql 

In general, all preparatory manipulations are completed. The described procedure will need to be repeated on all servers that are planned to be clustered.

Now features for the first server. First, we declare root:

 mysql > create user 'root'@'%' identified by 'ochen-strashniy-parol'; > grant all to 'root'@'%' on *.* with grant option; > flush privileges; > \q 

I know, I know, I know ... Seeky. But, believe me, the privilege set specified in the documentation cannot be trusted (checked: does not work), and it won't be possible to connect from the local host either. A more accurate option with the selection of an individual user the next time, as will be the "hour and time".

Then we load the module X. Honestly, that is exactly what his name is:

 mysqlsh --classic --dba enableXProtocol 

And at this moment fashionable pants are turning ... turning ... turning into elegant shorts. I mean, MySQL is not quite SQL, and even the Document Store

But this does not upset us in any way, so we move on:

 mysqlsh 

Now we can communicate with MySQL in javascript. First, let's connect to our server:

 \c root@1.1.1.1:3300 

We will enter the password and check whether everything is in order with our server, whether its configuration is suitable for clustering:

 dba.checkInstanceConfiguration('root@1.1.1.1:3300') 

If everything is done according to the instructions, then there should be no questions. If there are questions - correct, dock to help you. In principle, there is even an opportunity to generate the configuration yourself:

 dba.configureLocalInstance('localhost:3300', {password:'somePwd', mycnfPath:'some path'}) 

However, this option did not work right away, it took me to finish config file to the state specified in the beginning of the note. Naturally, after each file rewinding the config, the server must be restarted. For example, using systemctl restart mysq . Well, or whatever you like ...
Create a cluster (I assume that you are still in mysqlsh and the session did not terminate):

 var cl = dba.createCluster('moyCluster') 

Well, add to it the newly configured server:

 cl.addInstance('root@1.1.1.1:3300') 

From now on, I recommend to keep

 tail -f /var/log/mysql/error.log 

and when any errors appear there glances. The conclusion from mysqlsh is not very informative, but the log has everything you need. In the dock, they say that you can increase the level of debug output, but I have not tried.

If no errors were observed up to this point, then we have a cluster of one machine.
Last manipulation on this server:

 mysqldump --all-databases --triggers --routines --events > dump.sql 

Now let's do the rest. To do this, on all the machines we repeat the manipulations described from the beginning of the article to

 systemctl start mysql 

Do not forget to correct the server_id and group_replication_local_address . Thereafter:

 mysql > reset master; mysql < dump.sql 

Yes, yes, we upload a dump from a server running in a cluster into the machine. Now from the local machine (the one on which the mysql-server instance is going to be connected to the cluster) we do:

 mysql > set GLOBAL group_replication_allow_local_disjoint_gtids_join=ON; mysqlsh > dba.checkInstanceConfiguration('root@1.1.1.2:3300') > \c root@1.1.1.1:3300 > var cl = getCluster('moyCluster') > cl.addInstance('root@1.1.1.2:3300',{ipWhitelist: '1.1.1.0/24, 127.0.0.1/8'}) 

If we didn’t confuse anything, then at this moment we already have two cars in the cluster. Similarly, add the third.

At the moment we have a cluster that looks after itself, selects the master and replicates to the slaves. Each server can be accessed on its port 3300 and, if you contact the master, you can read and write, and if you go to the slave, you can only read. You can find out if the master is a server or a slave can be made from the output of cluster.status (). But this is not very convenient. I would like to always go to the same place, to the same ip / port and not to depend on the internal state of the cluster. For this we use the MySQL Router. Right in the dock there is an example of its initial configuration, which does almost everything we need. Change it a bit:

 mysqlrouter --bootstrap 1.1.1.1:3300 --user mysqlrouter 

Now we go to /etc/mysqlrouter/mysqlrouter.conf and fix the ports there somehow:

 [routing:moyCluster_default_rw] ... bind_port=3306 ... [routing:moyCluster_default_ro] ... bind_port=3307 ... 

After that you can do

 systemctl start mysqlrouter 

And now your port 3306 is answered by the usual mysql with read / write support, regardless of which of the cluster machines is now master and which is slave. On 3307 - always read-only. The number of instances of mysqlrouter is in no way limited, you can run your own instance on each client machine and hang it on the internal interface 127.0.0.1.73306. mysqlrouter itself keeps track of changes in the cluster (both adding and disappearing nodes in it) and updates the routing. This happens every five minutes (if you believe the logs). If an appeal occurs in this interval that cannot be processed (the node fell out or was deliberately removed from the cluster), then the router refuses to execute the transaction and rereads the state of the cluster in an extraordinary order.

By the way, if for some reason the node has fallen off from the cluster, it can be returned to the family by the command

 mysqlsh > \c root@<ip:port-_> > var cl = dba.getCluster('moyCluster') > cl.rejoinInstance('root@ip:port-_') 

Thank you for reading. Hope this will be useful. If you notice inaccuracies in the text - write in the comments, wrote on the hot, but from memory, so that could make a mess.