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-replicati...
- http://www.sebastien-han.fr/blog/2012/04/08/mysql-galera-cluster-with-ha...
- 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-...
- hswong3i's blog
- Add new comment
- 4386 reads
Comments
Warning to new MariaDB Users
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!!!
Add new comment