MySQL multi-master replication is an excellent feature within MySQL. However, there is only one problem; standard multi-master replication seems to never be as stable as something like master-slave replication. It is always in need of attention. That is where Percona comes into play. The Percona team has developed an amazing product dubbed Percona XtraDB cluster. XtraDB features world class multi-master replication powered by Galera. So, what are we waiting for? Let’s get started.
Prerequisites
- A Linux distro of your choice. In this guide, we will be using Debian 7. You can use a different distro if you would like. (Note that you may need to adapt this guide to work with the distro of your choice)
- Two nodes running the same OS.
- Basic knowledge of the command line and SSH.
Getting Started
SSH into your virtual machines.
VM 1:
ssh root@xxx.xxx.xxx.xxx
VM 2:
ssh root@yyy.yyy.yyy.yyy
Add Percona’s repositories.
On both nodes, execute the following command:
echo -e "deb http://repo.percona.com/apt wheezy main\ndeb-src http://repo.percona.com/apt wheezy main" >> /etc/apt/sources.list.d/percona.list && apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A
Now we need to update the sources:
apt-get update
Install Percona-XtraDB Cluster.
The installation is straightforward:
apt-get install percona-xtradb-cluster-56
If you are familiar with MySQL, then this next screen should look familiar. Simply follow the on-screen instructions for entering a password.
Configure the first node.
The configuration is quite simple. You simply need to add a few lines to the MySQL config file. In this case, I am going to be using Nano, but you can use your text editor of choice.
Use this to open the file on both machines:
nano /etc/mysql/my.cnf
Go down to bind-address = 127.0.0.1 and add a # before it. Add the following lines immediately after the (commented-out) bind-address line:
### Galera library.
wsrep_provider=/usr/lib/libgalera_smm.so
### IP addresses of your two nodes.
wsrep_cluster_address=gcomm://xxx.xxx.xxx.xx,yyy.yyy.yyy.yyy
### This will not work unless binlog is formatted to ROW.
binlog_format=ROW
### This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2
### Node #1 address (The node you are ssh`d into now.)
wsrep_node_address=xxx.xxx.xxx.xx
### SST Method
wsrep_sst_method=xtrabackup-v2
### Cluster name
wsrep_cluster_name=xtradb
### Node Name, in this case we will just call it xtradb1
wsrep_node_name=xtradb1
### Authentication, REMEMBER THIS.
wsrep_sst_auth="sstuser:yoursecretpass"
Save and exit the file (Ctrl+X).
Now, let’s bootstrap the node:
/etc/init.d/mysql bootstrap-pxc
Next, we need to create the user and give it privileges, this is why you need to remember that password. You will need to login to the MySQL shell (mysql -u root -p) and type in the following (Do not include the mysql>):
mysql> CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 'yoursecretpass';
mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';
mysql> FLUSH PRIVILEGES;
Configure the second node.
The configuration for the second node is identical to the first, except for two values. Once again, comment out bind-address = 127.0.0.1 and paste the following immediately after it. Remember to edit the values accordingly.
### Galera library.
wsrep_provider=/usr/lib/libgalera_smm.so
### IP addresses of your two nodes.
wsrep_cluster_address=gcomm://xxx.xxx.xxx.xx,yyy.yyy.yyy.yyy
### This will not work unless binlog is formatted to ROW.
binlog_format=ROW
### This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2
### Node #2 address (The node you are ssh`d into now.)
wsrep_node_address=yyy.yyy.yyy.yyy
### SST Method
wsrep_sst_method=xtrabackup-v2
### Cluster name
wsrep_cluster_name=xtradb
### Node Name, in this case we will just call it xtradb1
wsrep_node_name=xtradb1
### Authentication, REMEMBER THIS.
wsrep_sst_auth="sstuser:yoursecretpass"
Next, type /etc/init.d/mysql start. You will see the following output:
[....] Starting MySQL (Percona XtraDB Cluster) database server: mysqld . .
[....] State transfer in progress [ok]
That’s it! You’ve configured a multi-master XtraDB cluster! This is a worry-free XtraDB cluster with great performance.
Testing the cluster.
On node 1, type in mysql -u root -p then create database demo;. On the opposite node, login to MySQL with root, and type show databases;. If everything worked correctly, then you will see the database you created on the opposite node!