Currently, Ubuntu and Debian’s MariaDB servers use a special maintenance user to do routine maintenance. Some tasks that fall outside of the maintenance category also are run as this user, including important functions like stopping MySQL.

With our cluster environment being shared between the individual nodes, the maintenance user, who has randomly generated login credentials on each node, will be unable to execute commands correctly. Only the initial server will have the correct maintenance credentials, since the others will attempt to use their local settings to access the shared cluster environment.

We can fix this by simply copying the contents of the maintenance file to each individual node:

On one of your servers, open the Debian maintenance configuration file:

sudo nano /etc/mysql/debian.cnf

You will see a file that looks like this:

[client]
host     = localhost
user     = debian-sys-maint
password = 03P8rdlknkXr1upf
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host     = localhost
user     = debian-sys-maint
password = 03P8rdlknkXr1upf
socket   = /var/run/mysqld/mysqld.sock
basedir  = /usr

We simply need to copy this information and paste it into the same file on each node.

On your second and third nodes, open the same file:

sudo nano /etc/mysql/debian.cnf

Delete the current information and paste the parameters from the first node’s configuration file into these other servers’ files:

[client]
host     = localhost
user     = debian-sys-maint
password = 03P8rdlknkXr1upf
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host     = localhost
user     = debian-sys-maint
password = 03P8rdlknkXr1upf
socket   = /var/run/mysqld/mysqld.sock
basedir  = /usr

They should be exactly the same now. Save and close the files.

To begin, we need to stop the running MariaDB service so that our cluster can be brought online.

This is easily done by typing this on each of the nodes:

sudo service mysql stop

When all processes have ceased running, you must start up your first node again with a special parameter:

sudo service mysql start --wsrep-new-cluster

With our cluster configuration, each node that comes online tries to connect to at least one other node specified in its configuration file to get its initial state. Without the --wsrep-new-cluster parameter, this command would fail because the first node is unable to connect with any other nodes.

On each of the other nodes, you can now start MariaDB as you normally would. They will search for any member of the cluster list that is online. When they find the first node, they will join the cluster.

sudo service mysql start

Your cluster should now be online and communicating.

We’ve gone through the steps up to this point so that our cluster can perform master-master replication. We need to test this out to see if the replication is working as expected.

On one of our our nodes, we can create a database and table like this:

<pre> mysql -u root -p<span class=“highlight”>mariadb_admin_password</span> -e ‘CREATE DATABASE playground;’ mysql -u root -p<span class=“highlight”>mariadb_admin_password</span> -e ‘CREATE TABLE playground.equipment ( id INT NOT NULL AUTO_INCREMENT, type VARCHAR(50), quant INT, color VARCHAR(25), PRIMARY KEY(id));’ </pre>

This will create a database called playground and a table inside of this called equipment.

We can then insert our first item into this table by executing:

<pre> mysql -u root -p<span class=“highlight”>mariadb_admin_password</span> -e ‘INSERT INTO playground.equipment (type, quant, color) VALUES (“slide”, 2, “blue”)’ </pre>

We now have one value in our table.

From another node, we can read this data by typing:

<pre> mysql -u root -p<span class=“highlight”>mariadb_admin_password</span> -e ‘SELECT * FROM playground.equipment;’ </pre>

+----+-------+-------+-------+
| id | type  | quant | color |
+----+-------+-------+-------+
|  1 | slide |     2 | blue  |
+----+-------+-------+-------+

From this same node, we can write data to the cluster:

<pre> mysql -u root -p<span class=“highlight”>mariadb_admin_password</span> -e ‘INSERT INTO playground.equipment (type, quant, color) VALUES (“swing”, 10, “yellow”);’ </pre>

From our third node, we can read all of this data by querying the again:

<pre> mysql -u root -p<span class=“highlight”>mariadb_admin_password</span> -e ‘SELECT * FROM playground.equipment;’ </pre>

+----+-------+-------+--------+
| id | type  | quant | color  |
+----+-------+-------+--------+
|  1 | slide |     2 | blue   |
|  2 | swing |    10 | yellow |
+----+-------+-------+--------+

Again, we can add another value from this node:

<pre> mysql -u root -p<span class=“highlight”>mariadb_admin_password</span> -e ‘INSERT INTO playground.equipment (type, quant, color) VALUES (“seesaw”, 3, “green”);’ </pre>

Back on the first node, we can see that our data is available everywhere:

<pre> mysql -u root -p<span class=“highlight”>mariadb_admin_password</span> -e ‘SELECT * FROM playground.equipment;’ </pre>

+----+--------+-------+--------+
| id | type   | quant | color  |
+----+--------+-------+--------+
|  1 | slide  |     2 | blue   |
|  2 | swing  |    10 | yellow |
|  3 | seesaw |     3 | green  |
+----+--------+-------+--------+

As you can see, all of our servers can be written to. This means that we have master-master replication functioning correctly.

At this point, you should have a Galera cluster configured on your servers. This can help quite a bit with balancing load in write-intensive application environments.

If you plan on using a Galera cluster in a production situation, you may want to take a look at some of the other state snapshot transfer (sst) agents like “xtrabackup”. This will allow you to set up new nodes very quickly and without large interruptions to your active nodes. This does not affect the actual replication, but is a concern when nodes are being initialized.

<div class=“author”>By Justin Ellingwood</div>