Replikasi Database Mysql Master to Master

By Steam | 2020-11-26

Example blog post alt

Banyak tutorial untuk melakukan replikasi database mysql, kita coba buat langkah-langkah lebih akurat supaya tidak gagal, oya tutorial ini khusus untuk yang sudah banyak mencoba server linux, disini kita akan simulasi di server ubuntu, oke berikut tahapannya :

mysql  Ver 8.0.22-0ubuntu0.20.04.2 for Linux on x86_64 ((Ubuntu))
Server 1 : 10.10.0.4 Server 2 : 10.10.0.5


1. Patikan telah di setting :
bind-address            = 0.0.0.0
server-id               = 4
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_expire_logs_seconds      = 2592000
max_binlog_size   = 100M
skip-name-resolve

2. Buat User untuk Replikasi :

mysql> create user 'user_replicator'@'%' IDENTIFIED WITH mysql_native_password BY 'Replik4*DB';
mysql> grant replication slave on *.* to 'user_replicator'@'%';
mysql> flush privileges;

3. Restart Service Mysql
4. mysql> flush tables with read lock;
5. Silahakan mysqldump -u root -p --all-databases --events > db-master.sql
6. Sesudah di backup kirim ke server 2
7. mysql> show master status; (di Server 1) catat : MASTER_LOG_FILE='mysql-bin.xxxxx',MASTER_LOG_POS=xxx
8. pada point 5, restore di server 2
9. mysql> flush privileges;    
10. mysql> show master status; (di Server 2) catat : MASTER_LOG_FILE='mysql-bin.xxxxx',MASTER_LOG_POS=xxx
11. kembali Server 1
mysql> CHANGE MASTER TO master_host='10.10.0.4', master_port=3306, master_user='user_replicator', master_password='xxxxxx', master_log_file='xxxxx', master_log_pos=156;
CHANGE MASTER TO ;

12. Server 2
mysql> CHANGE MASTER TO master_host='10.10.0.5', master_port=3306, master_user='user_replicator', master_password='xxxxxx', master_log_file='xxxxx', master_log_pos=156;

13. lakukan ini di server 1/2
mysql> start slave;
mysql> show slave status\G
14. silakan monitoring dengan : tail -f /var/log/mysql/error.log
15. silahkan coba buat db dan hapus db, cek di server 1 dan server 2 apakah sudah oke ?

untuk mencoba langkah diatas, pastikan anda coba di server tester, jangan langsung ke production, kita tidak bertanggung jawab atas kesalahan anda. Jika ada pertanyaan silahkan hubungi kami.

terimakasih semoga jadi referensi.


xxx... itu sesuai kondisi anda .


Jika terjadi Error :

[Warning] [MY-013360] [Server] Plugin sha256_password reported: ''sha256_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
mysql> INSTALL PLUGIN CONNECTION_CONTROL SONAME 'connection_control.so';
Query OK, 0 rows affected (0.15 sec)
mysql> INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS SONAME 'connection_control.so';
Query OK, 0 rows affected (0.04 sec)


Bind Address: if your bind address are 127.0.0.1 in MySQL configuration, you must change to 0.0.0.0 in [mysqld] on /etc/mysql/my.cnf
Tambaha ini di : 0.0.0.0 mysql config pada bindadress

[Warning] [MY-010055] [Server] IP address '10.10.0.4' could not be resolved: Name or service not known
Tambaha ini di : skip-name-resolve mysql config


0 comments

Leave comment