MySQL multi-master replication is an excellent feature within MySQL.<\/strong>\u00a0However, 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\u00a0XtraDB cluster.<\/strong>\u00a0XtraDB features world class multi-master replication powered by Galera. So, what are we waiting for? Let’s get started.<\/p>\n

Prerequisites<\/strong><\/h3>\n

\n
• A Linux distro of your choice. In this guide, we will be using\u00a0Debian 7<\/strong>. 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<\/em>)<\/li>\n
• Two nodes running the same\u00a0OS<\/strong>.<\/li>\n
• Basic knowledge of the command line and\u00a0SSH<\/strong>.<\/li>\n<\/ul>\n

  Getting Started<\/strong><\/h3>\n

  SSH into your virtual machines.<\/strong><\/h6>\n

  VM 1:<\/p>\n

  ssh root@xxx.xxx.xxx.xxx\r\n<\/code><\/pre>\n
  VM 2:<\/p>\n
  ssh root@yyy.yyy.yyy.yyy\r\n<\/code><\/pre>\n
  Add Percona’s repositories.<\/strong><\/h6>\n
  On both nodes, execute the following command:<\/strong><\/p>\n
  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\r\n<\/code><\/pre>\n
  Now we need to update the sources:<\/strong><\/p>\n
  apt-get update\r\n<\/code><\/pre>\n
  Install Percona-XtraDB Cluster.<\/strong><\/h6>\n
  The installation is straightforward:<\/strong><\/p>\n
  apt-get install percona-xtradb-cluster-56\r\n<\/code><\/pre>\n
  If you are familiar with\u00a0MySQL,<\/strong>\u00a0then this next screen should look familiar. Simply follow the on-screen instructions for entering a password.<\/p>\n
  Configure the first node.<\/strong><\/h6>\n
  The configuration is quite simple.<\/strong>\u00a0You simply need to add a few lines to the\u00a0MySQL config file<\/strong>. In this case, I am going to be using Nano, but you can use your text editor of choice.<\/p>\n
  Use this to open the file on both machines:<\/p>\n
  nano \/etc\/mysql\/my.cnf\r\n<\/code><\/pre>\n
  Go down to\u00a0bind-address = 127.0.0.1<\/code>\u00a0and add a # before it.\u00a0Add the following lines immediately after the (commented-out)<\/strong>\u00a0bind-address<\/code>\u00a0line:<\/p>\n
  ### Galera library.\r\n\r\nwsrep_provider=\/usr\/lib\/libgalera_smm.so\r\n\r\n### IP addresses of your two nodes.\r\n\r\nwsrep_cluster_address=gcomm:\/\/xxx.xxx.xxx.xx,yyy.yyy.yyy.yyy\r\n\r\n### This will not work unless binlog is formatted to ROW.\r\n\r\nbinlog_format=ROW\r\n\r\n### This changes how InnoDB autoincrement locks are managed and is a requirement for Galera\r\n\r\ninnodb_autoinc_lock_mode=2\r\n### Node #1 address (The node you are ssh`d into now.)\r\n\r\nwsrep_node_address=xxx.xxx.xxx.xx\r\n\r\n### SST Method\r\n\r\nwsrep_sst_method=xtrabackup-v2\r\n\r\n### Cluster name\r\n\r\nwsrep_cluster_name=xtradb\r\n\r\n### Node Name, in this case we will just call it xtradb1\r\n\r\nwsrep_node_name=xtradb1\r\n\r\n### Authentication, REMEMBER THIS.\r\n\r\nwsrep_sst_auth=\"sstuser:yoursecretpass\"\r\n<\/code><\/pre>\n
  Save and exit the file (Ctrl+X).<\/strong><\/p>\n
  Now, let’s bootstrap the node:<\/strong><\/p>\n
  \/etc\/init.d\/mysql bootstrap-pxc\r\n<\/code><\/pre>\n
  Next, we need to create the user and give it privileges, this is why you need to remember that password.<\/strong>\u00a0You will need to login to the MySQL shell (mysql -u root -p<\/code>) and type in the following (Do not include the\u00a0mysql><\/code>):<\/p>\n
  mysql> CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 'yoursecretpass';\r\nmysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';\r\nmysql> FLUSH PRIVILEGES;\r\n<\/code><\/pre>\n
  Configure the second node.<\/strong><\/h6>\n
  The configuration for the second node is identical to the first<\/strong>, except for two values. Once again, comment out\u00a0bind-address = 127.0.0.1<\/code>\u00a0and paste the following immediately after it. Remember to edit the values accordingly.<\/p>\n
  ### Galera library.\r\n\r\nwsrep_provider=\/usr\/lib\/libgalera_smm.so\r\n\r\n### IP addresses of your two nodes.\r\n\r\nwsrep_cluster_address=gcomm:\/\/xxx.xxx.xxx.xx,yyy.yyy.yyy.yyy\r\n\r\n### This will not work unless binlog is formatted to ROW.\r\n\r\nbinlog_format=ROW\r\n\r\n### This changes how InnoDB autoincrement locks are managed and is a requirement for Galera\r\n\r\ninnodb_autoinc_lock_mode=2\r\n\r\n### Node #2 address (The node you are ssh`d into now.)\r\n\r\nwsrep_node_address=yyy.yyy.yyy.yyy\r\n\r\n### SST Method\r\n\r\nwsrep_sst_method=xtrabackup-v2\r\n\r\n### Cluster name\r\n\r\nwsrep_cluster_name=xtradb\r\n\r\n### Node Name, in this case we will just call it xtradb1\r\n\r\nwsrep_node_name=xtradb1\r\n\r\n### Authentication, REMEMBER THIS.\r\n\r\nwsrep_sst_auth=\"sstuser:yoursecretpass\"\r\n<\/code><\/pre>\n
  Next, type\u00a0\/etc\/init.d\/mysql start<\/code>. You will see the following output:<\/p>\n
  [....] Starting MySQL (Percona XtraDB Cluster) database server: mysqld . .\r\n[....] State transfer in progress [ok]\r\n<\/code><\/pre>\n
  That’s it!<\/strong>\u00a0You’ve configured a multi-master XtraDB cluster! This is a worry-free XtraDB cluster with great performance.<\/p>\n
  Testing the cluster.<\/strong><\/h6>\n
  On node 1, type in\u00a0mysql -u root -p<\/code>\u00a0then\u00a0create database demo;<\/code>. On the opposite node,\u00a0login to MySQL<\/strong>\u00a0with root, and type\u00a0show databases;<\/code>. If everything worked correctly, then you will see the database you created on the opposite node!<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"open","ping_status":"closed","template":"","format":"standard","manualknowledgebasecat":[110,233,242],"manual_kb_tag":[114],"_links":{"self":[{"href":"https:\/\/support.aklwebhost.com\/wp-json\/wp\/v2\/manual_kb\/2108"}],"collection":[{"href":"https:\/\/support.aklwebhost.com\/wp-json\/wp\/v2\/manual_kb"}],"about":[{"href":"https:\/\/support.aklwebhost.com\/wp-json\/wp\/v2\/types\/manual_kb"}],"author":[{"embeddable":true,"href":"https:\/\/support.aklwebhost.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/support.aklwebhost.com\/wp-json\/wp\/v2\/comments?post=2108"}],"version-history":[{"count":1,"href":"https:\/\/support.aklwebhost.com\/wp-json\/wp\/v2\/manual_kb\/2108\/revisions"}],"predecessor-version":[{"id":2109,"href":"https:\/\/support.aklwebhost.com\/wp-json\/wp\/v2\/manual_kb\/2108\/revisions\/2109"}],"wp:attachment":[{"href":"https:\/\/support.aklwebhost.com\/wp-json\/wp\/v2\/media?parent=2108"}],"wp:term":[{"taxonomy":"manualknowledgebasecat","embeddable":true,"href":"https:\/\/support.aklwebhost.com\/wp-json\/wp\/v2\/manualknowledgebasecat?post=2108"},{"taxonomy":"manual_kb_tag","embeddable":true,"href":"https:\/\/support.aklwebhost.com\/wp-json\/wp\/v2\/manual_kb_tag?post=2108"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}