» » [Lab 9.6] Database replication pada MariaDB di CentOS 7
Edit

[Lab 9.6] Database replication pada MariaDB di CentOS 7

[Lab 9.6] Database replication pada MariaDB di CentOS 7 - Hallo sahabat Another Stuff, Pada Artikel yang anda baca kali ini dengan judul [Lab 9.6] Database replication pada MariaDB di CentOS 7, kami telah mempersiapkan artikel ini dengan baik untuk anda baca dan ambil informasi didalamnya. mudah-mudahan isi postingan Artikel Admin Server, yang kami tulis ini dapat anda pahami. baiklah, selamat membaca.

Judul : [Lab 9.6] Database replication pada MariaDB di CentOS 7
link : [Lab 9.6] Database replication pada MariaDB di CentOS 7

Baca juga


[Lab 9.6] Database replication pada MariaDB di CentOS 7

Pada database ada istilah replication yang artinya kita dapat membuat lebih dari satu server database yang isinya sama, yang satu menjadi server utama (master) dan sisanya merupakan server cadangan (slave), tujuan dibuatnya server master dan slave adalah agar bersifat redudant, ketika salah satu server mati maka masih ada pengganti lainnya.

Cara kerja master slave ini, antara server master dengan slave akan melakukan transfer database sehingga kedua server memiliki isi yang sama persis. Pada server database MariaDB kita dapat melakukan konfigurasi replication. Berikut ini adalah sedikit informasi server yang akan saya konfigurasi :

Master
    OS:Linux CentOS 7 x64
    IP Address (enp0s3):192.168.56.50
    Domain:data.centos.dz
Slave
    OS:Linux CentOS 7 x64
    IP Address (enp0s3):192.168.56.51
    Domain:data2.centos.dz
Kita mulai konfigurasi pada kedua server tersebut :

Master

    Pertama yang kita lakukan adalah konfigurasi IP Address agar server dapat terhubung dengan jaringan dan juga terhubung dengan Internet. Edit file konfigurasi /etc/sysconfig/network-scripts/ifcfg-enp0s3 
    [root@dz-mariadb ~]# vi /etc/sysconfig/network-scripts/ifcfg-enp0s3
    • Edit dan tambahkan beberapa opsi, dan sesuaikan IP Address dengan jaringan yang anda gunakan.
      • ONBOOT=yes
      BOOTPROTO=static
      IPADDR=192.168.56.50
      NETMASK=255.255.255.0
      GATEWAY=192.168.56.1
      DNS1=192.168.56.50
      DNS2=8.8.8.8

    Repo SCLo

    Kita akan mengaktifkan repo SCLo pada server ini. Langsung saja install repo sclo pada server dengan perintah ini : 
    [root@dz-mariadb ~]# yum -y install centos-release-scl-rh centos-release-scl

    Install MariaDB

    Pertama kita install paket aplikasi MariaDB yang ada direpositori centos SCLo. 
    [root@dz-mariadb ~]# yum --enablerepo=centos-sclo-rh -y install rh-mariadb101-mariadb-server
    Load variable dari aplikasi MariaDB karena lokasinya ada di directory /opt agar dapat digunakan. 
    [root@dz-mariadb ~]# scl enable rh-mariadb101 bash
    Untuk melihat versi dari MariaDB yang terinstall. 
    [root@dz-mariadb ~]# mysql -V
    mysql  Ver 15.1 Distrib 10.1.19-MariaDB, for Linux (x86_64) using  EditLine wrapper
    Kemudian untuk mengetahui lokasi binary dari mariadb. 
    [root@dz-mariadb ~]# which mysql
    /opt/rh/rh-mariadb101/root/usr/bin/mysql
    Agar mariadb dapat berjalan saat server dinyalakan kita buat script dibawah ini. 
    [root@dz-mariadb ~]# vi /etc/profile.d/rh-mariadb101.sh
    • Masukan script ini.
      • #!/bin/bash

      source /opt/rh/rh-mariadb101/enable
      export X_SCLS="`scl enable rh-mariadb101 'echo $X_SCLS'`"
    Edit file konfigurasi mariadb, untuk menentukan character set. 
    [root@dz-mariadb ~]# nano /etc/opt/rh/rh-mariadb101/my.cnf.d/mariadb-server.cnf
    • Tambahkan script ini pada bagian [mysqld].
      • [mysqld]
      datadir=/var/opt/rh/rh-mariadb101/lib/mysql
      socket=/var/lib/mysql/mysql.sock
      log-error=/var/opt/rh/rh-mariadb101/log/mariadb/mariadb.log
      pid-file=/var/run/rh-mariadb101-mariadb/mariadb.pid
      character-set-server=utf8
    Kemudian kita jalankan layanan Database MariaDB. 
    [root@dz-mariadb ~]# systemctl start rh-mariadb101-mariadb
    [root@dz-mariadb ~]# systemctl enable rh-mariadb101-mariadb
    Buka firewall untuk layanan database mariadb. 
    [root@dz-mariadb ~]# firewall-cmd --add-service=mysql --permanent
    [root@dz-mariadb ~]# firewall-cmd --reload

    Database setup

    Jalankan perintah ini untuk melakukan setup database server. 
    [root@dz-mariadb ~]# mysql_secure_installation
    • Pertama kosongkan saja karena kita belum mengatur root password.
      • NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
            SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

      In order to log into MariaDB to secure it, we'll need the current
      password for the root user.  If you've just installed MariaDB, and
      you haven't set the root password yet, the password will be blank,
      so you should just press enter here.

      Enter current password for root (enter for none): #ENTER
    • Tekan y untuk mengatur root password kemudian ketikan root
      • OK, successfully used password, moving on...

      Setting the root password ensures that nobody can log into the MariaDB
      root user without the proper authorisation.

      Set root password? [Y/n] y
      New password: #password
      Re-enter new password: #password
      Password updated successfully!
      Reloading privilege tables..
       ... Success!
    • Tekan y untuk mengahapus user anonymous.
      • By default, a MariaDB installation has an anonymous user, allowing anyone
      to log into MariaDB without having to have a user account created for
      them.  This is intended only for testing, and to make the installation
      go a bit smoother.  You should remove them before moving into a
      production environment.

      Remove anonymous users? [Y/n] y
       ... Success!
    • Tekan y agar user root hanya dapat diakses lewat localhost saja.
      • Normally, root should only be allowed to connect from 'localhost'.  This
      ensures that someone cannot guess at the root password from the network.

      Disallow root login remotely? [Y/n] y
       ... Success!
    • Tekan y untuk menghapus database test.
      • By default, MariaDB comes with a database named 'test' that anyone can
      access.  This is also intended only for testing, and should be removed
      before moving into a production environment.

      Remove test database and access to it? [Y/n] y
       - Dropping test database...
       ... Success!
       - Removing privileges on test database...
       ... Success!
    • Kemudian kita reload privileges dari mariadb. Dan konfigurasi selesai.
      • Reloading the privilege tables will ensure that all changes made so far
      will take effect immediately.

      Reload privilege tables now? [Y/n] y
       ... Success!

      Cleaning up...

      All done!  If you've completed all of the above steps, your MariaDB
      installation should now be secure.

      Thanks for using MariaDB!

    Master setup

    Edit file konfigurasi mariadb server. 
    [root@dz-mariadb ~]# nano /etc/opt/rh/rh-mariadb101/my.cnf.d/mariadb-server.cnf
    • Tambahkan script ini di bagian server untuk menentukan server-id.
      • [server]
      log-bin=mysql-bin
      server-id=101
    Restart layanan mariadb. 
    [root@dz-mariadb ~]# systemctl restart rh-mariadb101-mariadb
    Kita masuk pada terminal database mariadb. 
    [root@dz-mariadb ~]# mysql -u root -p
    Enter password: 
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MariaDB connection id is 3
    Server version: 10.1.19-MariaDB MariaDB Server

    Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

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

    MariaDB [(none)]>
    Kita buat sebuah user yang akan digunakan sebagai keperluan replication. 
    MariaDB [(none)]> grant replication slave on *.* to tiruan@'%' identified by 'password';
    Query OK, 0 rows affected (0,00 sec)

    MariaDB [(none)]> flush privileges;
    Query OK, 0 rows affected (0,00 sec)

    MariaDB [(none)]> exit
    Bye

Slave

    Pertama yang kita lakukan adalah konfigurasi IP Address agar server dapat terhubung dengan jaringan dan juga terhubung dengan Internet. Edit file konfigurasi /etc/sysconfig/network-scripts/ifcfg-enp0s3 
    [root@dz-slave1-mariadb ~]# vi /etc/sysconfig/network-scripts/ifcfg-enp0s3
    • Edit dan tambahkan beberapa opsi, dan sesuaikan IP Address dengan jaringan yang anda gunakan.
      • ONBOOT=yes
      BOOTPROTO=static
      IPADDR=192.168.56.50
      NETMASK=255.255.255.0
      GATEWAY=192.168.56.1
      DNS1=192.168.56.50
      DNS2=8.8.8.8

    Repo SCLo

    Kita akan mengaktifkan repo SCLo pada server ini. Langsung saja install repo sclo pada server dengan perintah ini : 
    [root@dz-slave1-mariadb ~]# yum -y install centos-release-scl-rh centos-release-scl

    Install MariaDB

    Pertama kita install paket aplikasi MariaDB yang ada direpositori centos SCLo. 
    [root@dz-slave1-mariadb ~]# yum --enablerepo=centos-sclo-rh -y install rh-mariadb101-mariadb-server
    Load variable dari aplikasi MariaDB karena lokasinya ada di directory /opt agar dapat digunakan. 
    [root@dz-slave1-mariadb ~]# scl enable rh-mariadb101 bash
    Untuk melihat versi dari MariaDB yang terinstall. 
    [root@dz-slave1-mariadb ~]# mysql -V
    mysql  Ver 15.1 Distrib 10.1.19-MariaDB, for Linux (x86_64) using  EditLine wrapper
    Kemudian untuk mengetahui lokasi binary dari mariadb. 
    [root@dz-slave1-mariadb ~]# which mysql
    /opt/rh/rh-mariadb101/root/usr/bin/mysql
    Agar mariadb dapat berjalan saat server dinyalakan kita buat script dibawah ini. 
    [root@dz-slave1-mariadb ~]# vi /etc/profile.d/rh-mariadb101.sh
    • Masukan script ini.
      • #!/bin/bash

      source /opt/rh/rh-mariadb101/enable
      export X_SCLS="`scl enable rh-mariadb101 'echo $X_SCLS'`"
    Edit file konfigurasi mariadb, untuk menentukan character set. 
    [root@dz-slave1-mariadb ~]# nano /etc/opt/rh/rh-mariadb101/my.cnf.d/mariadb-server.cnf
    • Tambahkan script ini pada bagian [mysqld].
      • [mysqld]
      datadir=/var/opt/rh/rh-mariadb101/lib/mysql
      socket=/var/lib/mysql/mysql.sock
      log-error=/var/opt/rh/rh-mariadb101/log/mariadb/mariadb.log
      pid-file=/var/run/rh-mariadb101-mariadb/mariadb.pid
      character-set-server=utf8
    Kemudian kita jalankan layanan Database MariaDB. 
    [root@dz-slave1-mariadb ~]# systemctl start rh-mariadb101-mariadb
    [root@dz-slave1-mariadb ~]# systemctl enable rh-mariadb101-mariadb
    Buka firewall untuk layanan database mariadb. 
    [root@dz-slave1-mariadb ~]# firewall-cmd --add-service=mysql --permanent
    [root@dz-slave1-mariadb ~]# firewall-cmd --reload

    Database setup

    Jalankan perintah ini untuk melakukan setup database server. 
    [root@dz-slave1-mariadb ~]# mysql_secure_installation
    • Pertama kosongkan saja karena kita belum mengatur root password.
      • NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
            SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

      In order to log into MariaDB to secure it, we'll need the current
      password for the root user.  If you've just installed MariaDB, and
      you haven't set the root password yet, the password will be blank,
      so you should just press enter here.

      Enter current password for root (enter for none): #ENTER
    • Tekan y untuk mengatur root password kemudian ketikan root
      • OK, successfully used password, moving on...

      Setting the root password ensures that nobody can log into the MariaDB
      root user without the proper authorisation.

      Set root password? [Y/n] y
      New password: #password
      Re-enter new password: #password
      Password updated successfully!
      Reloading privilege tables..
       ... Success!
    • Tekan y untuk mengahapus user anonymous.
      • By default, a MariaDB installation has an anonymous user, allowing anyone
      to log into MariaDB without having to have a user account created for
      them.  This is intended only for testing, and to make the installation
      go a bit smoother.  You should remove them before moving into a
      production environment.

      Remove anonymous users? [Y/n] y
       ... Success!
    • Tekan y agar user root hanya dapat diakses lewat localhost saja.
      • Normally, root should only be allowed to connect from 'localhost'.  This
      ensures that someone cannot guess at the root password from the network.

      Disallow root login remotely? [Y/n] y
       ... Success!
    • Tekan y untuk menghapus database test.
      • By default, MariaDB comes with a database named 'test' that anyone can
      access.  This is also intended only for testing, and should be removed
      before moving into a production environment.

      Remove test database and access to it? [Y/n] y
       - Dropping test database...
       ... Success!
       - Removing privileges on test database...
       ... Success!
    • Kemudian kita reload privileges dari mariadb. Dan konfigurasi selesai.
      • Reloading the privilege tables will ensure that all changes made so far
      will take effect immediately.

      Reload privilege tables now? [Y/n] y
       ... Success!

      Cleaning up...

      All done!  If you've completed all of the above steps, your MariaDB
      installation should now be secure.

      Thanks for using MariaDB!

    Slave setup

    Kita edit file konfigurasi mariadb server. 
    [root@dz-slave1-mariadb ~]# nano /etc/opt/rh/rh-mariadb101/my.cnf.d/mariadb-server.cnf
    • Kemudian tambahkan script ini untuk menentukan server-id dari server slave.
      • [server]
      log-bin=mysql-bin
      server-id=102
      read_only=1
      report-host=data2.centos.dz
    Restart layanan mariadb. 
    [root@dz-slave1-mariadb ~]# systemctl restart rh-mariadb101-mariadb
    Kita berpindah kembali pada server master untuk mendapatkan dump data. 
    [root@dz-mariadb ~]# mysql -u root -p
    Enter password: 
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MariaDB connection id is 4
    Server version: 10.1.19-MariaDB MariaDB Server

    Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

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

    MariaDB [(none)]> flush tables with read lock;
    Query OK, 0 rows affected (0,00 sec)

    MariaDB [(none)]> show master status;
    +------------------+----------+--------------+------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +------------------+----------+--------------+------------------+
    | mysql-bin.000001 |      608 |              |                  |
    +------------------+----------+--------------+------------------+
    1 row in set (0,00 sec)

    Kemudian ini perintah untuk membuat dump data. 
    [root@dz-mariadb ~]# mysqldump -u root -p --all-databases --lock-all-tables --events > mysql_dump.sql
    Enter password:
    Masuk kembali pada mariadb terminal untuk melakukan unlock table. 
    root@dz-mariadb ~]# mysql -u root -p
    Enter password: 
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MariaDB connection id is 6
    Server version: 10.1.19-MariaDB MariaDB Server

    Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

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

    MariaDB [(none)]> unlock tables;
    Query OK, 0 rows affected (0,00 sec)

    MariaDB [(none)]> exit
    Kirim dump data ke server slave via scp. 
    [root@dz-mariadb ~]# scp mysql_dump.sql data2.centos.dz:/tmp/
    root@data2.centos.dz's password: 
    mysql_dump.sql                                           100%  467KB 466.8KB/s   00:00
    Kembali lagi pada server slave untuk mengimpor dump data yang tadi dari server master. 
    [root@dz-slave1-mariadb ~]# mysql -u root -p </tmp/mysql_dump.sql 
    Enter password:
    Masuk pada terminal mariadb, dan ubah beberapa informasi master server. 
    [root@dz-slave1-mariadb ~]# mysql -u root -p
    Enter password: 
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MariaDB connection id is 5
    Server version: 10.1.19-MariaDB MariaDB Server

    Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

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

    MariaDB [(none)]> change master to
        -> master_host='192.168.56.50', #alamat IP Server master.
        -> master_user='tiruan', #user replication 
        -> master_password='password', #passwordnya
        -> master_log_file='mysql-bin.000001', #log file di server master
        -> master_log_pos=608; #position yang tadi di server master
    Query OK, 0 rows affected (0,01 sec)
    Kita start replication. 
    MariaDB [(none)]> start slave;
    Query OK, 0 rows affected (0,00 sec)
    Untuk melihat status slave. 
    MariaDB [(none)]> show slave status\G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.56.50
                      Master_User: tiruan
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000001
              Read_Master_Log_Pos: 608
                   Relay_Log_File: mariadb-relay-bin.000002
                    Relay_Log_Pos: 537
            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: 608
                  Relay_Log_Space: 837
                  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: 101
                   Master_SSL_Crl: 
               Master_SSL_Crlpath: 
                       Using_Gtid: No
                      Gtid_IO_Pos: 
          Replicate_Do_Domain_Ids: 
      Replicate_Ignore_Domain_Ids: 
                    Parallel_Mode: conservative
    1 row in set (0,00 sec)

    Pastikan tulisannya yes, yes.
Untuk mengujinya kita buat sebuah database pada server master. 
MariaDB [(none)]> create database testingdb;
Query OK, 1 row affected (0,00 sec)
Lalu pada server slave kita lihat isi databasenya apakah sudah singkron dengan master. 
[root@dz-slave1-mariadb ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.1.19-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| dzdata             |
| information_schema |
| mysql              |
| performance_schema |
| testingdb          |
+--------------------+
5 rows in set (0,00 sec)


Demikianlah Artikel [Lab 9.6] Database replication pada MariaDB di CentOS 7

Sekianlah artikel [Lab 9.6] Database replication pada MariaDB di CentOS 7 kali ini, mudah-mudahan bisa memberi manfaat untuk anda semua. baiklah, sampai jumpa di postingan artikel lainnya.

Anda sekarang membaca artikel [Lab 9.6] Database replication pada MariaDB di CentOS 7 dengan alamat link https://anothers-stuff.blogspot.com/2017/05/lab-96-database-replication-pada.html
close
==Close==