How to configure MySQL Multi-Master Replication on Oracle Linux
This tutorial explains how to setup and configure MySQL multi-master replication on Oracle Linux. As you all may already know, MySQL is a well known top notch database product that has proven to be enterprise ready. As data is crucial for every organization, most database administrators are looking for a suitable solution to setup high availability to ensure user are able to access their data 24/7. MySQL replication is a solution that can ensure a high availability policy. On top of that, MySQL replication is also able to help database administrators to distribute the load on multiple database servers by load balancing the READ and WRITE requests. Unfortunately, basic replication can only offer benefits on READ requests. Due to that, MySQL multi-master replication was introduced to offer replication for WRITE request as well.
1. Preliminary Note
For this tutorial, I am using Oracle Linux 6.8 in the 32bit version. Please note that even though the configuration is made under Oracle Linux, yet the steps and configuration are mainly the same to CentOS and Red Hat Linux. In this tutorial, we will use 2 servers. On each of them, we will set up a MySQL database and configure it for multi-master replication. At the end of this tutorial, we will see that any READ or WRITE request including DDL (Data Definition Language) and DML (Data Manipulation Language) requests will be run on both servers.
2. Installation Phase
For the installation phase, we do only require the MySQL server package for the configuration phase and MySQL client for accessing the database environment. Both packages do require some dependencies to be installed. First, let's confirm the version of our Operating System and note down the IP address for pre-configuration.
[root@DB1 ~]# lsb_release -a
LSB Version: :base-4.0-ia32:base-4.0-noarch:core-4.0-ia32:core-4.0-noarch:graphics-4.0-ia32:graphics-4.0-noarch:printing-4.0-ia32:printing-4.0-noarch
Distributor ID: OracleServer
Description: Oracle Linux Server release 6.8
Release: 6.8
Codename: n/a
[root@DB1 ~]# ifconfig
eth0 Link encap:Ethernet HWaddr 08:00:27:42:C0:4C
inet addr:192.168.43.11 Bcast:192.168.43.255 Mask:255.255.255.0
inet6 addr: fe80::a00:27ff:fe42:c04c/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:544 errors:0 dropped:0 overruns:0 frame:0
TX packets:79 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:51274 (50.0 KiB) TX bytes:9474 (9.2 KiB)
lo Link encap:Local Loopback
inet addr:127.0.0.1 Mask:255.0.0.0
inet6 addr: ::1/128 Scope:Host
UP LOOPBACK RUNNING MTU:65536 Metric:1
RX packets:4 errors:0 dropped:0 overruns:0 frame:0
TX packets:4 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:0
RX bytes:240 (240.0 b) TX bytes:240 (240.0 b)
Now do the same on the other server.
[root@DB2 ~]# lsb_release -a
LSB Version: :base-4.0-ia32:base-4.0-noarch:core-4.0-ia32:core-4.0-noarch:graphics-4.0-ia32:graphics-4.0-noarch:printing-4.0-ia32:printing-4.0-noarch
Distributor ID: OracleServer
Description: Oracle Linux Server release 6.8
Release: 6.8
Codename: n/a
[root@DB2 ~]# ifconfig
eth0 Link encap:Ethernet HWaddr 09:00:30:42:C1:5D
inet addr:192.168.43.12 Bcast:192.168.43.255 Mask:255.255.255.0
inet6 addr: fe80::a00:27ff:fe42:c04c/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:544 errors:0 dropped:0 overruns:0 frame:0
TX packets:79 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:51274 (50.0 KiB) TX bytes:9474 (9.2 KiB)
lo Link encap:Local Loopback
inet addr:127.0.0.1 Mask:255.0.0.0
inet6 addr: ::1/128 Scope:Host
UP LOOPBACK RUNNING MTU:65536 Metric:1
RX packets:4 errors:0 dropped:0 overruns:0 frame:0
TX packets:4 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:0
RX bytes:240 (240.0 b) TX bytes:240 (240.0 b)
Next add the IP address to the server's hosts file. Do the same on both server like below.
[root@DB1 ~]# vi /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.43.11 DB1
192.168.43.12 DB2
Next, I will configure a new repository to install the MySQL server and MySQL client packages via yum utility. Please do this on BOTH servers.
[root@DB1 ~]# cd /etc/yum.repos.d/
[root@DB1 yum.repos.d]# ls
OEL6.repo
[root@DB1 yum.repos.d]# wget http://repo.mysql.com/mysql-community-release-el6-5.noarch.rpm
--2017-05-22 09:43:59-- http://repo.mysql.com/mysql-community-release-el6-5.noarch.rpm
Resolving repo.mysql.com... 23.8.231.210
Connecting to repo.mysql.com|23.8.231.210|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 5824 (5.7K) [application/x-redhat-package-manager]
Saving to: "mysql-community-release-el6-5.noarch.rpm"
100%[==================================================>] 5,824 --.-K/s in 0s
2017-05-22 09:44:00 (264 MB/s) - "mysql-community-release-el6-5.noarch.rpm" saved [5824/5824]
[root@DB1 yum.repos.d]# ls
OEL6.repo mysql-community-release-el6-5.noarch.rpm
[root@DB1 yum.repos.d]# rpm -Uvh mysql-community-release-el6-5.noarch.rpm
Preparing... ########################################### [100%]
1:mysql-community-release########################################### [100%]
[root@DB1 yum.repos.d]# ls
CentOS.repo mysql-community.repo
mysql-community-release-el6-5.noarch.rpm mysql-community-source.repo
The new repository for the latest MySQL version have been installed. Let's enable them.
[root@DB1 yum.repos.d]# vi mysql-community.repo
# Enable to use MySQL 5.6
[mysql56-community]
name=MySQL 5.6 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.6-community/el/6/$basearch/
enabled=1
gpgcheck=1
gpgkey=file:/etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
Once done, let's ensure that the MySQL packages are available.
[root@DB1 yum.repos.d]# rpm -qa|grep -i mysql
mysql-community-release-el6-5.noarch
Great, now we've half way done. As no MySQL packages are installed in the current server, let's start the package installation. Below are the steps.
[root@DB1 yum.repos.d]#
[root@DB1 yum.repos.d]# yum install mysql-server mysql-client
Loaded plugins: fastestmirror, refresh-packagekit, security
Setting up Install Process
Repository 'OEL' is missing name in configuration, using id
Determining fastest mirrors
epel/metalink | 6.2 kB 00:00
* epel: epel.mirror.angkasa.id
* remi-php56: remi.mirror.wearetriple.com
* remi-safe: remi.mirror.wearetriple.com
OEL | 3.7 kB 00:00
epel | 4.3 kB 00:00
epel/primary_db | 5.9 MB 00:00
mysql-connectors-community | 2.5 kB 00:00
mysql-connectors-community/primary_db | 15 kB 00:00
mysql-tools-community | 2.5 kB 00:00
mysql-tools-community/primary_db | 35 kB 00:00
mysql56-community | 2.5 kB 00:00
mysql56-community/primary_db | 183 kB 00:00
remi-php56 | 2.9 kB 00:00
remi-php56/primary_db | 218 kB 00:01
remi-safe | 2.9 kB 00:00
remi-safe/primary_db | 725 kB 00:02
Package mysql-server is obsoleted by mysql-community-server, trying to install mysql-community-server-5.6.36-2.el6.i686 instead
No package mysql-client available.
Resolving Dependencies
--> Running transaction check
---> Package mysql-community-server.i686 0:5.6.36-2.el6 will be installed
--> Processing Dependency: mysql-community-common(i686) = 5.6.36-2.el6 for package: mysql-community-server-5.6.36-2.el6.i686
--> Processing Dependency: mysql-community-client(i686) >= 5.6.10 for package: mysql-community-server-5.6.36-2.el6.i686
--> Processing Dependency: perl(DBI) for package: mysql-community-server-5.6.36-2.el6.i686
--> Running transaction check
---> Package mysql-community-client.i686 0:5.6.36-2.el6 will be installed
--> Processing Dependency: mysql-community-libs(i686) >= 5.6.10 for package: mysql-community-client-5.6.36-2.el6.i686
---> Package mysql-community-common.i686 0:5.6.36-2.el6 will be installed
---> Package perl-DBI.i686 0:1.609-4.el6 will be installed
--> Running transaction check
---> Package mysql-community-libs.i686 0:5.6.36-2.el6 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
============================================================================================
Package Arch Version Repository Size
============================================================================================
Installing:
mysql-community-server i686 5.6.36-2.el6 mysql56-community 55 M
Installing for dependencies:
mysql-community-client i686 5.6.36-2.el6 mysql56-community 18 M
mysql-community-common i686 5.6.36-2.el6 mysql56-community 308 k
mysql-community-libs i686 5.6.36-2.el6 mysql56-community 1.9 M
perl-DBI i686 1.609-4.el6 CentOS 705 k
Transaction Summary
============================================================================================
Install 5 Package(s)
Total download size: 76 M
Installed size: 338 M
Is this ok [y/N]: y
Downloading Packages:
(1/5): mysql-community-client-5.6.36-2.el6.i686.rpm | 18 MB 00:01
(2/5): mysql-community-common-5.6.36-2.el6.i686.rpm | 308 kB 00:00
(3/5): mysql-community-libs-5.6.36-2.el6.i686.rpm | 1.9 MB 00:00
(4/5): mysql-community-server-5.6.36-2.el6.i686.rpm | 55 MB 00:02
(5/5): perl-DBI-1.609-4.el6.i686.rpm | 705 kB 00:00
--------------------------------------------------------------------------------------------
Total 18 MB/s | 76 MB 00:04
warning: rpmts_HdrFromFdno: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Retrieving key from file:/etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
Importing GPG key 0x5072E1F5:
Userid : MySQL Release Engineering <[email protected]>
Package: mysql-community-release-el6-5.noarch (installed)
From : file:/etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
Is this ok [y/N]: y
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Warning: RPMDB altered outside of yum.
Installing : mysql-community-common-5.6.36-2.el6.i686 1/5
Installing : mysql-community-libs-5.6.36-2.el6.i686 2/5
Installing : mysql-community-client-5.6.36-2.el6.i686 3/5
Installing : perl-DBI-1.609-4.el6.i686 4/5
Installing : mysql-community-server-5.6.36-2.el6.i686 5/5
Verifying : perl-DBI-1.609-4.el6.i686 1/5
Verifying : mysql-community-server-5.6.36-2.el6.i686 2/5
Verifying : mysql-community-libs-5.6.36-2.el6.i686 3/5
Verifying : mysql-community-common-5.6.36-2.el6.i686 4/5
Verifying : mysql-community-client-5.6.36-2.el6.i686 5/5
Installed:
mysql-community-server.i686 0:5.6.36-2.el6
Dependency Installed:
mysql-community-client.i686 0:5.6.36-2.el6 mysql-community-common.i686 0:5.6.36-2.el6
mysql-community-libs.i686 0:5.6.36-2.el6 perl-DBI.i686 0:1.609-4.el6
Complete!
Excellent, now the installation is done. Start the MySQL daemon for the first time.
[root@DB1 yum.repos.d]# service mysqld restart
Stopping mysqld: [ OK ]
Initializing MySQL database: 2017-05-22 09:55:53 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-05-22 09:55:53 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.
2017-05-22 09:55:53 0 [Note] /usr/sbin/mysqld (mysqld 5.6.36) starting as process 18645 ...
2017-05-22 09:55:53 18645 [Note] InnoDB: Using atomics to ref count buffer pool pages
2017-05-22 09:55:53 18645 [Note] InnoDB: The InnoDB memory heap is disabled
2017-05-22 09:55:53 18645 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-05-22 09:55:53 18645 [Note] InnoDB: Memory barrier is not used
2017-05-22 09:55:53 18645 [Note] InnoDB: Compressed tables use zlib 1.2.3
2017-05-22 09:55:53 18645 [Note] InnoDB: Using Linux native AIO
2017-05-22 09:55:53 18645 [Note] InnoDB: Using CPU crc32 instructions
2017-05-22 09:55:53 18645 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2017-05-22 09:55:53 18645 [Note] InnoDB: Completed initialization of buffer pool
2017-05-22 09:55:53 18645 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!
2017-05-22 09:55:53 18645 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB
2017-05-22 09:55:53 18645 [Note] InnoDB: Database physically writes the file full: wait...
2017-05-22 09:55:53 18645 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB
2017-05-22 09:55:53 18645 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB
2017-05-22 09:55:53 18645 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2017-05-22 09:55:53 18645 [Warning] InnoDB: New log files created, LSN=45781
2017-05-22 09:55:53 18645 [Note] InnoDB: Doublewrite buffer not found: creating new
2017-05-22 09:55:53 18645 [Note] InnoDB: Doublewrite buffer created
2017-05-22 09:55:53 18645 [Note] InnoDB: 128 rollback segment(s) are active.
2017-05-22 09:55:53 18645 [Warning] InnoDB: Creating foreign key constraint system tables.
2017-05-22 09:55:53 18645 [Note] InnoDB: Foreign key constraint system tables created
2017-05-22 09:55:53 18645 [Note] InnoDB: Creating tablespace and datafile system tables.
2017-05-22 09:55:53 18645 [Note] InnoDB: Tablespace and datafile system tables created.
2017-05-22 09:55:53 18645 [Note] InnoDB: Waiting for purge to start
2017-05-22 09:55:53 18645 [Note] InnoDB: 5.6.36 started; log sequence number 0
2017-05-22 09:55:54 18645 [Note] Binlog end
2017-05-22 09:55:54 18645 [Note] InnoDB: FTS optimize thread exiting.
2017-05-22 09:55:54 18645 [Note] InnoDB: Starting shutdown...
2017-05-22 09:55:55 18645 [Note] InnoDB: Shutdown completed; log sequence number 1625977
2017-05-22 09:55:55 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-05-22 09:55:55 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.
2017-05-22 09:55:55 0 [Note] /usr/sbin/mysqld (mysqld 5.6.36) starting as process 18667 ...
2017-05-22 09:55:55 18667 [Note] InnoDB: Using atomics to ref count buffer pool pages
2017-05-22 09:55:55 18667 [Note] InnoDB: The InnoDB memory heap is disabled
2017-05-22 09:55:55 18667 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-05-22 09:55:55 18667 [Note] InnoDB: Memory barrier is not used
2017-05-22 09:55:55 18667 [Note] InnoDB: Compressed tables use zlib 1.2.3
2017-05-22 09:55:55 18667 [Note] InnoDB: Using Linux native AIO
2017-05-22 09:55:55 18667 [Note] InnoDB: Using CPU crc32 instructions
2017-05-22 09:55:55 18667 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2017-05-22 09:55:55 18667 [Note] InnoDB: Completed initialization of buffer pool
2017-05-22 09:55:55 18667 [Note] InnoDB: Highest supported file format is Barracuda.
2017-05-22 09:55:55 18667 [Note] InnoDB: 128 rollback segment(s) are active.
2017-05-22 09:55:55 18667 [Note] InnoDB: Waiting for purge to start
2017-05-22 09:55:55 18667 [Note] InnoDB: 5.6.36 started; log sequence number 1625977
2017-05-22 09:55:55 18667 [Note] Binlog end
2017-05-22 09:55:55 18667 [Note] InnoDB: FTS optimize thread exiting.
2017-05-22 09:55:55 18667 [Note] InnoDB: Starting shutdown...
2017-05-22 09:55:57 18667 [Note] InnoDB: Shutdown completed; log sequence number 1625987
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h vdevknime1 password 'new-password'
Alternatively you can run:
/usr/bin/mysql_secure_installation
which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.
See the manual for more instructions.
Please report any problems at http://bugs.mysql.com/
The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at http://shop.mysql.com
Note: new default config file not created.
Please make sure your config file is current
WARNING: Default config file /etc/my.cnf exists on the system
This file will be read by default by the MySQL server
If you do not want to use this, either remove it, or use the
#NAME?
[ OK ]
Starting mysqld: [ OK ]
Great, now our MySQL server service is up. Let's confirm it by listing the port used by the MySQL service. By default, MySQL will use port 3306 when starting up the service. Below are the commands:
[root@DB1 yum.repos.d]# netstat -apn|grep -i mysql
tcp 0 0 :::3306 :::* LISTEN 2139/mysqld
unix 2 [ ACC ] STREAM LISTENING 16018 2139/mysqld /var/lib/mysql/mysql.sock
Now, let's setup an initial password for the MySQL root user to ensure we didn't miss out the basic security for our MySQL server.
[root@DB1 yum.repos.d]# mysqladmin -u root password "Pass1234"
Warning: Using a password on the command line interface can be insecure.
[root@DB1 yum.repos.d]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.36 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
Done with the installation phase. Let's move on to the configuration of the multi-master replication setup.
3. Configuration Phase
Let's go inside the MySQL my.cnf configuration file and make the changes like below on server DB1.
[root@DB1 ~]# vi /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
