MariaDB Galera Master/Master Replication on Ubuntu 12.04 HOWTO

When building a LAMP cluter usually we will face bottleneck at MySQL: traditional MySQL only support master/slave replication, so slave server can only use as scale-out for read access. Therefore how to make use of this read access scale-out will depend on application implementation, e.g. in Drupal we must specify which "read safe" SQL can be send to slave server, individually. This result as very limited improvement in overall performance.

Using MariaDB Galera can simply solve this problem: it is in master/master replication style so most likely application don't need to change any code in cluster environment for share loading, e.g. Drupal can even keep as using "localhost" as target database host among number of cluster member servers, where also works for both Drupal 5.x/6.x/7.x with none of code change. This also means we can scale-out for most LAMP-based application without code changes, too.

This HOWTO will guide you though installing MariaDB Galera on Ubuntu 12.04, plus setup master/master replication between 2 server.

Server Requirement

In this example let's assume we have 2 servers: dev6c1 and dev6c2, with domain name "localdomain" and IP information as below:

172.24.145.25 dev6c1.localdomain dev6c1
172.24.145.26 dev6c2.localdomain dev6c2

Where we will first setup dev6c1 as donor, then setup dev6c2 as new comer and request replication from dev6c1.

Install MariaDB Galera

Refer to https://downloads.mariadb.org/mariadb/repositories/, let setup APT as below and install MariaDB Galera:

cat >> /etc/apt/sources.list.d/mariadb.list <<-EOF
# MariaDB 5.5 repository list - created 2013-01-08 10:40 UTC
# http://downloads.mariadb.org/mariadb/repositories/
deb http://ftp.osuosl.org/pub/mariadb/repo/5.5/ubuntu precise main
deb-src http://ftp.osuosl.org/pub/mariadb/repo/5.5/ubuntu precise main
EOF
apt-key adv --recv-keys --keyserver keyserver.ubuntu.com 0xcbcb082a1bb943db
aptitude update && \
aptitude -y full-upgrade && \
aptitude -y install mariadb-galera-server galera && \
aptitude update && aptitude -y full-upgrade && aptitude autoclean && aptitude clean

Little hack: let's setup root account info so we can operate in password-less mode for following steps:

cat > /root/.my.cnf <<-EOF
[client]
host     = localhost
user     = root
password = your_secret_password
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host     = localhost
user     = root
password = your_secret_password
socket   = /var/run/mysqld/mysqld.sock
basedir  = /usr
EOF
chmod 600 /root/.my.cnf

Now restart MariaDB and test initial connection:

/etc/init.d/mysql restart
mysql

It should now show you the MariaDB terminal as below:

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 31
Server version: 5.5.28a-MariaDB-a1~precise mariadb.org binary distribution, wsrep_23.7rc1.rXXXX

Copyright (c) 2000, 2012, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

Ok, up to this part MariaDB should already successfully installed. Next we will focus on configuration.

First Time Setup

First of all let's ensure MariaDB can function well in standalone mode. Let's tweak some parameters for MariaDB and so Galera, too:

cat > /etc/mysql/my.cnf <<-EOF
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

[mysqld_safe]
nice = 0
socket = /var/run/mysqld/mysqld.sock

[mysqld]
basedir = /usr
bind-address = 0.0.0.0
binlog_format = ROW
character_set_server = utf8
collation_server = utf8_general_ci
datadir = /var/lib/mysql
default-storage-engine = InnoDB
expire_logs_days = 10
innodb_autoinc_lock_mode = 2
innodb_buffer_pool_size = 256M
innodb_doublewrite = 1
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 60
innodb_locks_unsafe_for_binlog = 1
innodb_stats_on_metadata = 0
key_buffer = 256M
lc-messages-dir = /usr/share/mysql
lock_wait_timeout = 300
max_allowed_packet = 128M
max_binlog_size = 128M
max_connections = 64
myisam-recover = BACKUP
myisam_sort_buffer_size = 64M
net_buffer_length = 8K
open-files-limit = 65535
pid-file = /var/run/mysqld/mysqld.pid
port = 3306
query_cache_limit = 8M
query_cache_size = 16M
read_buffer_size = 8M
read_rnd_buffer_size = 8M
skip-external-locking
socket = /var/run/mysqld/mysqld.sock
sort_buffer_size = 16M
table_cache = 2M
table_definition_cache = 65535
table_open_cache = 65535
thread_cache_size = 8
thread_concurrency = 8
tmpdir = /tmp
user = mysql

[mysqldump]
max_allowed_packet = 16M
quick
quote-names

[mysql]

[isamchk]
!includedir /etc/mysql/conf.d/
key_buffer = 256M
read_buffer = 16M
sort_buffer_size = 256M
write_buffer = 16M
EOF
cat > /etc/mysql/conf.d/mariadb.cnf <<-EOF
[mysqld]
wsrep_cluster_address = 'gcomm://'
wsrep_provider = /usr/lib/galera/libgalera_smm.so
wsrep_retry_autocommit = 0
wsrep_sst_method = rsync
EOF

Don't forget restart MariaDB and double confirm if able to start it up:

/etc/init.d/mysql restart
mysql -e "SHOW STATUS LIKE 'wsrep_%';"

If looks good we should have similar result:

+----------------------------+--------------------------------------+
| Variable_name              | Value                                |
+----------------------------+--------------------------------------+
| wsrep_local_state_uuid     | 73b0a955-611a-11e2-0800-6125101ce499 |
| wsrep_protocol_version     | 4                                    |
| wsrep_last_committed       | 0                                    |
| wsrep_replicated           | 0                                    |
| wsrep_replicated_bytes     | 0                                    |
| wsrep_received             | 2                                    |
| wsrep_received_bytes       | 131                                  |
| wsrep_local_commits        | 0                                    |
| wsrep_local_cert_failures  | 0                                    |
| wsrep_local_bf_aborts      | 0                                    |
| wsrep_local_replays        | 0                                    |
| wsrep_local_send_queue     | 0                                    |
| wsrep_local_send_queue_avg | 0.000000                             |
| wsrep_local_recv_queue     | 0                                    |
| wsrep_local_recv_queue_avg | 0.000000                             |
| wsrep_flow_control_paused  | 0.000000                             |
| wsrep_flow_control_sent    | 0                                    |
| wsrep_flow_control_recv    | 0                                    |
| wsrep_cert_deps_distance   | 0.000000                             |
| wsrep_apply_oooe           | 0.000000                             |
| wsrep_apply_oool           | 0.000000                             |
| wsrep_apply_window         | 0.000000                             |
| wsrep_commit_oooe          | 0.000000                             |
| wsrep_commit_oool          | 0.000000                             |
| wsrep_commit_window        | 0.000000                             |
| wsrep_local_state          | 4                                    |
| wsrep_local_state_comment  | Synced                               |
| wsrep_cert_index_size      | 0                                    |
| wsrep_causal_reads         | 0                                    |
| wsrep_incoming_addresses   | 10.1.0.13:3306                       |
| wsrep_cluster_conf_id      | 1                                    |
| wsrep_cluster_size         | 1                                    |
| wsrep_cluster_state_uuid   | 73b0a955-611a-11e2-0800-6125101ce499 |
| wsrep_cluster_status       | Primary                              |
| wsrep_connected            | ON                                   |
| wsrep_local_index          | 0                                    |
| wsrep_provider_name        | Galera                               |
| wsrep_provider_vendor      | Codership Oy <info@codership.com>    |
| wsrep_provider_version     | 23.2.2(r137)                         |
| wsrep_ready                | ON                                   |
+----------------------------+--------------------------------------+

Ok so standalone mode looks work as expected.

Database Privileges

Before we setup Master/Master replication need to ensure no empty users, or else will confuse MySQL authentication matching rules:

mysql -e "SET wsrep_on = OFF; DELETE FROM mysql.user WHERE user = '';"
mysql -e "SET wsrep_on = OFF; GRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY 'password';"

Connect to Cluster

Before start connect to clutser, let's double confirm the setup:

  • dev6c1 will act as donor, so it should always start first, and shutdown last
  • dev6c2 will join the cluster created by dev6c1, so its wsrep_cluster_address will point to dev6c1

In dev6c2, execute following command and replace previous Galera setup and point to donor:

cat > /etc/mysql/conf.d/mariadb.cnf <<-EOF
[mysqld]
wsrep_cluster_address='gcomm://dev6c1'
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_sst_method=rsync
EOF

Now restart dev6c2 mysql:

/etc/init.d/mysql restart

In between let's monitor the sync status at dev6c1 with:

mysql -e "SHOW STATUS LIKE 'wsrep_%';"

which should now show:

+----------------------------+--------------------------------------+
| Variable_name              | Value                                |
+----------------------------+--------------------------------------+
| wsrep_local_state_uuid     | 73b0a955-611a-11e2-0800-6125101ce499 |
| wsrep_protocol_version     | 4                                    |
| wsrep_last_committed       | 0                                    |
| wsrep_replicated           | 0                                    |
| wsrep_replicated_bytes     | 0                                    |
| wsrep_received             | 8                                    |
| wsrep_received_bytes       | 679                                  |
| wsrep_local_commits        | 0                                    |
| wsrep_local_cert_failures  | 0                                    |
| wsrep_local_bf_aborts      | 0                                    |
| wsrep_local_replays        | 0                                    |
| wsrep_local_send_queue     | 0                                    |
| wsrep_local_send_queue_avg | 0.000000                             |
| wsrep_local_recv_queue     | 0                                    |
| wsrep_local_recv_queue_avg | 0.000000                             |
| wsrep_flow_control_paused  | 0.000000                             |
| wsrep_flow_control_sent    | 0                                    |
| wsrep_flow_control_recv    | 0                                    |
| wsrep_cert_deps_distance   | 0.000000                             |
| wsrep_apply_oooe           | 0.000000                             |
| wsrep_apply_oool           | 0.000000                             |
| wsrep_apply_window         | 0.000000                             |
| wsrep_commit_oooe          | 0.000000                             |
| wsrep_commit_oool          | 0.000000                             |
| wsrep_commit_window        | 0.000000                             |
| wsrep_local_state          | 4                                    |
| wsrep_local_state_comment  | Synced                               |
| wsrep_cert_index_size      | 0                                    |
| wsrep_causal_reads         | 0                                    |
| wsrep_incoming_addresses   | 10.1.0.13:3306,10.1.0.14:3306        |
| wsrep_cluster_conf_id      | 4                                    |
| wsrep_cluster_size         | 2                                    |
| wsrep_cluster_state_uuid   | 73b0a955-611a-11e2-0800-6125101ce499 |
| wsrep_cluster_status       | Primary                              |
| wsrep_connected            | ON                                   |
| wsrep_local_index          | 0                                    |
| wsrep_provider_name        | Galera                               |
| wsrep_provider_vendor      | Codership Oy <info@codership.com>    |
| wsrep_provider_version     | 23.2.2(r137)                         |
| wsrep_ready                | ON                                   |
+----------------------------+--------------------------------------+

Ok now dev6c2 already connect to cluster setup by dev6c1.

Trouble Shooting

When you now restart dev6c2 MariaDB, you will found error message similar as:

ERROR 1045 (28000): Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)

This is because now both mysql.user table are in sync after SST, but individul server's /etc/mysql/debian.cnf coming with different values. The simplest solution is to copy and paste the dev6c1's /etc/mysql/debian.cnf to dev6c2:

scp root@dev6c1:/etc/mysql/debian.cnf root@dev6c2:/etc/mysql/debian.cnf

Now during server reboot or MariaDB restart it will works as expected.

Reference

  • https://kb.askmonty.org/en/getting-started-with-mariadb-galera-cluster/
  • https://launchpadlibrarian.net/121183486/README
  • https://launchpadlibrarian.net/121183470/README-MySQL
  • https://launchpadlibrarian.net/124348862/README-wsrep
  • http://www.sebastien-han.fr/blog/2012/04/01/mysql-multi-master-replication-with-galera/
  • http://www.sebastien-han.fr/blog/2012/04/08/mysql-galera-cluster-with-haproxy/
  • http://www.mysqlsupport.cn/how-to-mysql-load-veryhigh/
  • http://dinglin.iteye.com/blog/1575840
  • http://www.mysqlperformanceblog.com/2013/02/01/implications-of-metadata-locking-changes-in-mysql-5-5/

Comments

Skaag Argonius's picture

BIG WARNING to you all: Assume your databases on the secondary nodes will be erased during sync, and replaced with whatever databases are on the first database node.

So if you are going to setup a cluster on nodes that already have some MySQL databases, make sure you back them up BEFORE you even install MariaDB. You have been warned!!!

Jose Carlos's picture

you missed rsync in the list of packages to install

Add new comment

Restricted HTML

  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd> <h4> <h5> <h6>
  • Lines and paragraphs break automatically.
  • Web page addresses and e-mail addresses turn into links automatically.
  • HTML tags will be transformed to conform to HTML standards.

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.
  • Web page addresses and e-mail addresses turn into links automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.