Tutorial ini menjelaskan cara mengatur dan mengkonfigurasi replikasi multi-master MySQL di Oracle Linux. Seperti yang mungkin sudah Anda ketahui, MySQL adalah produk database terkemuka yang telah terbukti siap digunakan untuk perusahaan. Karena data sangat penting untuk setiap organisasi, sebagian besar administrator database mencari solusi yang sesuai untuk menyiapkan ketersediaan tinggi guna memastikan pengguna dapat mengakses data mereka 24/7. Replikasi MySQL adalah solusi yang dapat memastikan kebijakan ketersediaan tinggi. Selain itu, replikasi MySQL juga dapat membantu administrator database untuk mendistribusikan beban pada beberapa server database dengan load balancing permintaan READ dan WRITE. Sayangnya, replikasi dasar hanya dapat menawarkan manfaat pada permintaan READ. Karena itu, replikasi multi-master MySQL diperkenalkan untuk menawarkan replikasi untuk permintaan MENULIS juga.
1. Catatan Awal
Untuk tutorial ini, saya menggunakan Oracle Linux 6.8 dalam versi 32bit. Harap dicatat bahwa meskipun konfigurasi dibuat di bawah Oracle Linux, namun langkah-langkah dan konfigurasi pada dasarnya sama untuk CentOS dan Red Hat Linux. Dalam tutorial ini, kita akan menggunakan 2 server. Pada masing-masing dari mereka, kami akan menyiapkan database MySQL dan mengonfigurasinya untuk replikasi multi-master. Di akhir tutorial ini, kita akan melihat bahwa semua permintaan READ atau WRITE termasuk permintaan DDL (Data Definition Language) dan DML (Data Manipulation Language) akan dijalankan di kedua server.
2. Tahap Instalasi
Untuk fase penginstalan, kami hanya memerlukan paket server MySQL untuk fase konfigurasi dan klien MySQL untuk mengakses lingkungan database. Kedua paket memang memerlukan beberapa dependensi untuk diinstal. Pertama, konfirmasikan versi Sistem Operasi kita dan catat alamat IP untuk pra-konfigurasi.
[[email protected] ~]# 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
[[email protected] ~]# 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)
Sekarang lakukan hal yang sama di server lain.
[[email protected] ~]# 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
[[email protected] ~]# 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)
Selanjutnya tambahkan alamat IP ke file host server. Lakukan hal yang sama pada kedua server seperti di bawah ini.
[[email protected] ~]# 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
Selanjutnya, saya akan mengkonfigurasi repositori baru untuk menginstal server MySQL dan paket klien MySQL melalui utilitas yum. Silakan lakukan ini di KEDUA server.
[[email protected] ~]# cd /etc/yum.repos.d/
[[email protected] yum.repos.d]# ls
OEL6.repo
[[email protected] 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]
[[email protected] yum.repos.d]# ls
OEL6.repo mysql-community-release-el6-5.noarch.rpm
[[email protected] yum.repos.d]# rpm -Uvh mysql-community-release-el6-5.noarch.rpm
Preparing... ########################################### [100%]
1:mysql-community-release########################################### [100%]
[[email protected] yum.repos.d]# ls
CentOS.repo mysql-community.repo
mysql-community-release-el6-5.noarch.rpm mysql-community-source.repo
Repositori baru untuk versi MySQL terbaru telah diinstal. Mari kita aktifkan.
[[email protected] 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
Setelah selesai, pastikan paket MySQL tersedia.
[[email protected] yum.repos.d]# rpm -qa|grep -i mysql
mysql-community-release-el6-5.noarch
Bagus, sekarang kita sudah setengah jalan. Karena tidak ada paket MySQL yang terinstal di server saat ini, mari kita mulai penginstalan paket. Berikut langkah-langkahnya.
[[email protected] yum.repos.d]#
[[email protected] 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!
Luar biasa, sekarang instalasi selesai. Jalankan daemon MySQL untuk pertama kalinya.
[[email protected] 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 ]
Bagus, sekarang layanan server MySQL kami sudah aktif. Mari konfirmasikan dengan membuat daftar port yang digunakan oleh layanan MySQL. Secara default, MySQL akan menggunakan port 3306 saat memulai layanan. Di bawah ini adalah perintahnya:
[[email protected] 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
Sekarang, mari siapkan sandi awal untuk pengguna root MySQL guna memastikan kita tidak melewatkan keamanan dasar untuk server MySQL kita.
[[email protected] yum.repos.d]# mysqladmin -u root password "Pass1234"
Warning: Using a password on the command line interface can be insecure.
[[email protected] 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() |
+----------------+
| [email protected] |
+----------------+
1 row in set (0.00 sec)
Selesai dengan tahap instalasi. Mari beralih ke konfigurasi penyiapan replikasi multi-master.
3. Tahap Konfigurasi
Mari kita masuk ke dalam file konfigurasi MySQL my.cnf dan membuat perubahan seperti di bawah ini pada server DB1.
[[email protected] ~]# vi /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
port=3306
innodb_file_per_table=ON
pid-file=/var/lib/mysql/mysqld.pid
server-id = 11
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = test_rep
Berikut penjelasan konfigurasinya:
- server-id ==> ID replikasi
- log_bin ==> File log yang akan digunakan untuk aktivitas replikasi
- binlog_do_db ==> Database yang terkait untuk proses replikasi
Setelah selesai, mari masuk ke dalam lingkungan server MySQL dan buat database terkait dan tetapkan pengguna untuk proses replikasi.
[[email protected] ~]# 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-log 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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.01 sec)
mysql> create database test_rep;
Query OK, 1 row affected (0.01 sec)
mysql> create user 'replicator'@'DB2' identified by 'Rep1234';
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave on *.* to 'replicator'@'DB2';
Query OK, 0 rows affected (0.00 sec)
Selesai, sekarang restart server MySQL dan lihat apakah konfigurasi sudah diaktifkan atau belum. Berikut langkah-langkahnya:
[[email protected] yum.repos.d]# service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
[[email protected] ~]# 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-log 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> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 854 | test_rep | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
Bagus, sekarang mari kita siapkan server DB2 menjadi server budak untuk master replikasi DB1 dan selain itu menyiapkan server DB2 juga sebagai master untuk server DB1. Berikut langkah-langkahnya:
[[email protected] ~]# vi /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
port=3306
innodb_file_per_table=ON
pid-file=/var/lib/mysql/mysqld.pid
server-id = 12
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = test_rep
Sama seperti konfigurasi di DB1, mari masuk ke dalam lingkungan server MySQL dan buat database terkait dan tetapkan pengguna untuk proses replikasi.
[[email protected] ~]# 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-log 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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.01 sec)
mysql> create database test_rep;
Query OK, 1 row affected (0.01 sec)
mysql> create user 'replicator'@'DB1' identified by 'Rep1234';
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave on *.* to 'replicator'@'DB1';
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status;
Empty set (0.01 sec)
Selesai, sekarang mari kita restart server DB2 MySQL dan lihat apakah konfigurasi sudah diaktifkan atau belum. Jika ya, maka kita akan melanjutkan ke pembuatan slave untuk server DB1.
[[email protected] ~]# service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
[[email protected] ~]# 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-log 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> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 553 | test_rep | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CHANGE MASTER TO MASTER_HOST = 'DB1', MASTER_PORT = 3306, MASTER_USER = 'replicator', MASTER_PASSWORD = 'Rep1234', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 854;
Query OK, 0 rows affected, 2 warnings (0.07 sec)
mysql> start slave;
Query OK, 0 rows affected (0.04 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: DB1
Master_User: replicator
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 854
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 284
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 854
Relay_Log_Space: 459
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 11
Master_UUID: 6e143d91-3635-11e7-b9ad-08002742c04c
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> show slave status;
+----------------------------------+---------------+-------------+-------------+---------------+-------------------+---------------------+--------------------------+---------------+-----------------------+------------------+-------------------+-----------
| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID | Master_Info_File | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | Auto_Position |
+----------------------------------+---------------+-------------+-------------+---------------+-------------------+---------------------+--------------------------+---------------+-----------------------+------------------+-------------------+-----------
| Waiting for master to send event | DB1 | replicator | 3306 | 60 | mysql-bin.000001 | 854 | mysqld-relay-bin.000002 | 284 | mysql-bin.000001 | Yes | Yes | | | | | | | 0 | | 0 | 854 | 459 | None | | 0 | No | | | | | | 0 | No | 0 | | 0 | | | 11 | 6e143d91-3635-11e7-b9ad-08002742c04c | /var/lib/mysql/master.info | 0 | NULL | Slave has read all relay log; waiting for the slave I/O thread to update it | 86400 | | | | | | | | 0 |
+----------------------------------+---------------+-------------+-------------+---------------+-------------------+---------------------+--------------------------+---------------+-----------------------+------------------+-------------------+-----------
1 row in set (0.00 sec)
Besar! Karena semuanya telah diatur untuk server DB2, kembali ke server DB1 dan buat konfigurasi slave untuk server DB2.
[[email protected] ~]# mysql -u root -p
Enter password:
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.6.36-log 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> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 854 | test_rep | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CHANGE MASTER TO MASTER_HOST = 'DB2', MASTER_PORT = 3306, MASTER_USER = 'replicator', MASTER_PASSWORD = 'Rep1234', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 553;
Query OK, 0 rows affected, 2 warnings (0.25 sec)
mysql> start slave;
Query OK, 0 rows affected (0.03 sec)
mysql> show slave status;
+----------------------------------+---------------+-------------+-------------+---------------+-------------------+---------------------+--------------------------+---------------+-----------------------+------------------+-------------------+-----------
| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID | Master_Info_File | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | Auto_Position |
+----------------------------------+---------------+-------------+-------------+---------------+-------------------+---------------------+--------------------------+---------------+-----------------------+------------------+-------------------+-----------
| Waiting for master to send event | DB2 | replicator | 3306 | 60 | mysql-bin.000001 | 553 | mysqld-relay-bin.000002 | 284 | mysql-bin.000001 | Yes | Yes | | | | | | | 0 | | 0 | 553 | 459 | None | | 0 | No | | | | | | 0 | No | 0 | | 0 | | | 12 | 14f5ab41-3c7b-11e7-a293-08002742c04c | /var/lib/mysql/master.info | 0 | NULL | Slave has read all relay log; waiting for the slave I/O thread to update it | 86400 | | | | | | | | 0 |
+----------------------------------+---------------+-------------+-------------+---------------+-------------------+---------------------+--------------------------+---------------+-----------------------+------------------+-------------------+-----------
1 row in set (0.01 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: DB2
Master_User: replicator
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 553
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 284
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 553
Relay_Log_Space: 459
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 12
Master_UUID: 14f5ab41-3c7b-11e7-a293-08002742c04c
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
ERROR:
No query specified
Nicely done, now as everything is ready in place, let's proceed with the testing phase to conclude all configuration was made correctly.
4. Tahap Pengujian
Before we start the test, let's make the assumptions for the final result expectations. For this test, we will create a table on DB1 MySQL server then on DB2 we will check if the table automatically exists or not. If yes then we will add a new data row into it and check again in DB1 server if new data is available on both servers.
[[email protected] ~]# mysql -u root -p test_rep
Enter password:
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.6.36-log 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 database();
+------------+
| database() |
+------------+
| test_rep |
+------------+
1 row in set (0.00 sec)
mysql> create table tbl1( id int(11) primary key auto_increment, fullname varchar(30));
Query OK, 0 rows affected (0.22 sec)
mysql> show tables in test_rep;
+--------------------+
| Tables_in_test_rep |
+--------------------+
| tbl1 |
+--------------------+
1 row in set (0.01 sec)
Done, as the table creation is a DDL (Data Definition Language) statement, there's no need to enter a commit command. Now let's go inside DB2 MySQL server and see if the newly table created exists.
[[email protected] ~]# mysql -u root -p test_rep
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.6.36-log 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> show tables in test_rep;
+--------------------+
| Tables_in_test_rep |
+--------------------+
| tbl1 |
+--------------------+
1 row in set (0.00 sec)
mysql> insert into tbl1 ( fullname ) values ('Shahril'), ('mark'), ('Allen'), ('Suzy'), ('Adam') ;
Query OK, 5 rows affected (0.05 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from tbl1;
+----+----------+
| id | fullname |
+----+----------+
| 1 | Shahril |
| 2 | mark |
| 3 | Allen |
| 4 | Suzy |
| 5 | Adam |
+----+----------+
5 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
Excellent, notice that the newly table created table in DB1 server now automatically exists in DB2 MySQL server. Then we also managed to insert 5 rows of data into the table. For the final check, let's see if the updated rows in table TBL1 can be seen in DB1 server as well.
[[email protected] ~]# mysql -u root -p test_rep
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.6.36-log 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 * from tbl1;
+----+----------+
| id | fullname |
+----+----------+
| 1 | Shahril |
| 2 | mark |
| 3 | Allen |
| 4 | Suzy |
| 5 | Adam |
+----+----------+
5 rows in set (0.01 sec)
mysql> delete from tbl1 where fullname like 'A%';
Query OK, 2 rows affected (0.07 sec)
mysql> select * from tbl1;
+----+----------+
| id | fullname |
+----+----------+
| 1 | Shahril |
| 2 | mark |
| 4 | Suzy |
+----+----------+
3 rows in set (0.00 sec)
Thumbs up! We've successfully created a MySQL multi-master replication between 2 servers.
nginx:Cara Memblokir Eksploitasi, Injeksi SQL, Injeksi File, Spam, Agen Pengguna, Dll.
Cara menginstal dan menggunakan alat forensik memori Volatilitas