» » [Lab 9.9] Database server replication MySQL
Edit

[Lab 9.9] Database server replication MySQL

[Lab 9.9] Database server replication MySQL - Hallo sahabat Another Stuff, Pada Artikel yang anda baca kali ini dengan judul [Lab 9.9] Database server replication MySQL, 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.9] Database server replication MySQL
link : [Lab 9.9] Database server replication MySQL

Baca juga


[Lab 9.9] Database server replication MySQL

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 MySQL 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-mysql ~]# 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-mysql ~]# yum -y install centos-release-scl-rh centos-release-scl

    Install MySQL

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

      source /opt/rh/rh-mysql57/enable
      export X_SCLS="`scl enable rh-mysql57 'echo $X_SCLS'`"
    Kemudian kita edit file konfigurasi dari mysql. 
    [root@dz-mysql ~]# nano /etc/opt/rh/rh-mysql57/my.cnf.d/rh-mysql57-mysql-server.cnf
    • Tambahkan script ini dibagian [mysqld] untuk menentukan character set.
      • [mysqld]
      datadir=/var/opt/rh/rh-mysql57/lib/mysql
      socket=/var/lib/mysql/mysql.sock
      log-error=/var/opt/rh/rh-mysql57/log/mysql/mysqld.log
      pid-file=/var/run/rh-mysql57-mysqld/mysqld.pid
      character-set-server=utf8
    Jalankan dan enable layanan mysql. 
    [root@dz-mysql ~]# systemctl start rh-mysql57-mysqld
    [root@dz-mysql ~]# systemctl enable rh-mysql57-mysqld
    Buka firewall agar server mysql ini dapat diakses dari luar. 
    [root@dz-mysql ~]# firewall-cmd --add-service=mysql --permanent
    [root@dz-mysql ~]# firewall-cmd --reload
    Kita jalankan setup mysql untuk mengatur beberapa pengaturan dasar. 
    [root@dz-mysql ~]# mysql_secure_installation
    • Tekan y untuk mengaktifkan pengecekan kerumitan password.
      • Securing the MySQL server deployment.

      Connecting to MySQL using a blank password.

      VALIDATE PASSWORD PLUGIN can be used to test passwords
      and improve security. It checks the strength of password
      and allows the users to set only those passwords which are
      secure enough. Would you like to setup VALIDATE PASSWORD plugin?

      Press y|Y for Yes, any other key for No: y
    • Pilih level kerumitan password yang ingin dibuat. 0, 1 ,2
      • There are three levels of password validation policy:

      LOW    Length >= 8
      MEDIUM Length >= 8, numeric, mixed case, and special characters
      STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file

      Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2
    • Ketikan password sesuai kriteria kerumitan yang anda pilih sebelumnya. Dan tekan y untuk meyakinkannya.
      • Please set the password for root here.

      New password: #password

      Re-enter new password: #password

      Estimated strength of the password: 100 
      Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
    • Tekan y untuk menghapus user anonim.
      • By default, a MySQL installation has an anonymous user,
      allowing anyone to log into MySQL 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? (Press y|Y for Yes, any other key for No) : y
      Success.
    • Tekan y agar user root hanya dapat diakses oleh localhost.
      • 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? (Press y|Y for Yes, any other key for No) : y
      Success.
    • Tekan y untuk menghapus database test.
      • By default, MySQL 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? (Press y|Y for Yes, any other key for No) : y
       - Dropping test database...
      Success.

       - Removing privileges on test database...
      Success.
    • Tekan tombol y untuk mereload privileges.
      • Reloading the privilege tables will ensure that all changes
      made so far will take effect immediately.

      Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
      Success.

      All done!

    Master Setup

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

    Copyright (c) 2000, 2016, 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>
    Kita buat sebuah user yang akan digunakan sebagai keperluan replication. 
    mysql> grant replication slave on *.* to tiruan@'%' identified by 'Password123!';
    Query OK, 0 rows affected, 1 warning (0,00 sec)

    mysql> flush privileges
        -> ;
    Query OK, 0 rows affected (0,00 sec)

    mysql> 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-mysql-slave ~]# 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.51
      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-mysql-slave ~]# yum -y install centos-release-scl-rh centos-release-scl

    Install MySQL

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

      source /opt/rh/rh-mysql57/enable
      export X_SCLS="`scl enable rh-mysql57 'echo $X_SCLS'`"
    Kemudian kita edit file konfigurasi dari mysql. 
    [root@dz-mysql-slave ~]# nano /etc/opt/rh/rh-mysql57/my.cnf.d/rh-mysql57-mysql-server.cnf
    • Tambahkan script ini dibagian [mysqld] untuk menentukan character set.
      • [mysqld]
      datadir=/var/opt/rh/rh-mysql57/lib/mysql
      socket=/var/lib/mysql/mysql.sock
      log-error=/var/opt/rh/rh-mysql57/log/mysql/mysqld.log
      pid-file=/var/run/rh-mysql57-mysqld/mysqld.pid
      character-set-server=utf8
    Jalankan dan enable layanan mysql. 
    [root@dz-mysql-slave ~]# systemctl start rh-mysql57-mysqld
    [root@dz-mysql-slave ~]# systemctl enable rh-mysql57-mysqld
    Buka firewall agar server mysql ini dapat diakses dari luar. 
    [root@dz-mysql-slave ~]# firewall-cmd --add-service=mysql --permanent
    [root@dz-mysql-slave ~]# firewall-cmd --reload
    Kita jalankan setup mysql untuk mengatur beberapa pengaturan dasar. 
    [root@dz-mysql-slave ~]# mysql_secure_installation
    • Tekan y untuk mengaktifkan pengecekan kerumitan password.
      • Securing the MySQL server deployment.

      Connecting to MySQL using a blank password.

      VALIDATE PASSWORD PLUGIN can be used to test passwords
      and improve security. It checks the strength of password
      and allows the users to set only those passwords which are
      secure enough. Would you like to setup VALIDATE PASSWORD plugin?

      Press y|Y for Yes, any other key for No: y
    • Pilih level kerumitan password yang ingin dibuat. 0, 1 ,2
      • There are three levels of password validation policy:

      LOW    Length >= 8
      MEDIUM Length >= 8, numeric, mixed case, and special characters
      STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file

      Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2
    • Ketikan password sesuai kriteria kerumitan yang anda pilih sebelumnya. Dan tekan y untuk meyakinkannya.
      • Please set the password for root here.

      New password: #password

      Re-enter new password: #password

      Estimated strength of the password: 100 
      Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
    • Tekan y untuk menghapus user anonim.
      • By default, a MySQL installation has an anonymous user,
      allowing anyone to log into MySQL 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? (Press y|Y for Yes, any other key for No) : y
      Success.
    • Tekan y agar user root hanya dapat diakses oleh localhost.
      • 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? (Press y|Y for Yes, any other key for No) : y
      Success.
    • Tekan y untuk menghapus database test.
      • By default, MySQL 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? (Press y|Y for Yes, any other key for No) : y
       - Dropping test database...
      Success.

       - Removing privileges on test database...
      Success.
    • Tekan tombol y untuk mereload privileges.
      • Reloading the privilege tables will ensure that all changes
      made so far will take effect immediately.

      Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
      Success.

      All done!

    Slave setup

    Kita edit file konfigurasi mysql server. 
    [root@dz-mysql-slave ~]# nano /etc/opt/rh/rh-mysql57/my.cnf.d/rh-mysql57-mysql-server.cnf
    • Kemudian tambahkan script ini untuk menentukan server-id dari server slave.
      • [mysqld]
      log-bin=mysql-bin
      server-id=102
      read_only=1
      report-host=data2.centos.dz
    Restart layanan mysql. 
    [root@dz-mysql-slave ~]# systemctl restart rh-mysql57-mysqld
    Kita berpindah kembali pada server master untuk mendapatkan dump data. 
    [root@dz-mysql ~]# 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.7.16-log MySQL Community Server (GPL)

    Copyright (c) 2000, 2016, 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> flush tables with read lock;
    Query OK, 0 rows affected (0,00 sec)

    mysql> show master status;
    +------------------+----------+--------------+------------------+-------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +------------------+----------+--------------+------------------+-------------------+
    | mysql-bin.000001 |      877 |              |                  |                   |
    +------------------+----------+--------------+------------------+-------------------+
    1 row in set (0,00 sec)


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

    Copyright (c) 2000, 2016, 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> unlock tables;
    Query OK, 0 rows affected (0,00 sec)


    Kirim dump data ke server slave via scp. 
    [root@dz-mysql ~]# scp mysql_dump.sql data2.centos.dz:/tmp/
    Warning: Permanently added 'data2.centos.dz,192.168.56.51' (ECDSA) to the list of known hosts.
    root@data2.centos.dz's password: 
    mysql_dump.sql                                            100%  755KB 755.3KB/s   00:00
    Kembali lagi pada server slave untuk mengimpor dump data yang tadi dari server master.
    [root@dz-mysql-slave ~]# mysql -u root -p </tmp/mysql_dump.sql Enter password: 
    Masuk pada terminal mysql, dan ubah beberapa informasi master server. 
    [root@dz-mysql-slave ~]# mysql -u root -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 4
    Server version: 5.7.16-log MySQL Community Server (GPL)

    Copyright (c) 2000, 2016, 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> change master to
        -> master_host='192.168.56.50',
        -> master_user='tiruan',
        -> master_password='Password123!',
        -> master_log_file='mysql-bin.000001',
        -> master_log_pos=877;
    Query OK, 0 rows affected, 2 warnings (0,01 sec)
    Kita start replication. 
    mysql> start slave;
    Query OK, 0 rows affected (0,00 sec)
    Untuk melihat status slave. 
    mysql> 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: 1017
                   Relay_Log_File: dz-mysql-slave-relay-bin.000002
                    Relay_Log_Pos: 460
            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: 1017
                  Relay_Log_Space: 676
                  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_UUID: 2f03e180-39ff-11e7-93c0-080027518a74
                 Master_Info_File: /var/opt/rh/rh-mysql57/lib/mysql/master.info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
               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
             Replicate_Rewrite_DB: 
                     Channel_Name: 
               Master_TLS_Version: 
    1 row in set (0,00 sec)
    Pastikan tulisannya yes, yes Untuk mengujinya kita buat sebuah database pada server master. 
    [root@dz-mysql ~]# mysql -u root -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 4
    Server version: 5.7.16-log MySQL Community Server (GPL)

    Copyright (c) 2000, 2016, 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> create database munculgakyadisebelah;
    Query OK, 1 row affected (0,00 sec)

    Lalu pada server slave kita lihat isi databasenya apakah sudah singkron dengan master.
    [root@dz-mysql-slave ~]# mysql -u root -pEnter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.7.16-log MySQL Community Server (GPL) Copyright (c) 2000, 2016, 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 | | munculgakyadisebelah | | mysql | | performance_schema | | sys | | testdb | +----------------------+ 6 rows in set (0,00 sec)


Demikianlah Artikel [Lab 9.9] Database server replication MySQL

Sekianlah artikel [Lab 9.9] Database server replication MySQL kali ini, mudah-mudahan bisa memberi manfaat untuk anda semua. baiklah, sampai jumpa di postingan artikel lainnya.

Anda sekarang membaca artikel [Lab 9.9] Database server replication MySQL dengan alamat link https://anothers-stuff.blogspot.com/2017/05/lab-99-database-server-replication-mysql.html
close
==Close==