Step by Step MySQL Replication

Replikasi merupakan konfigurasi sistem dimana database MySQL server dalam hal ini dinamakan master, yang menyimpan data maupun menangani request pengguna dan database MySQL server yang lain dinamakan slave server, semua SQL statement yang dilakukan oleh master secara otomatis akan dilakukan juga oleh slave.

untuk meng-implementasikan backup database dengan replikasi yang perlu dilakukan hanya men-Set-Up file konfigurasi my.cnf atau my.ini, berikut STEP by STEP nya yang gw lakuin di dua Linux centos 2.6.18-53.el5 #1 GNU/Linux yang running di VMware Server Console Latest Version: 1.0.6

okay bro… kita langsung aja install mysql nya,
Instalasi database MySQL server di mesin centos1:

[root@centos1 ~]# cd /home/david/src
[root@centos1 src]# ls
MySQL-client-community-5.0.51a-0.rhel5.i386.rpm
MySQL-server-community-5.0.51a-0.rhel5.i386.rpm
MySQL-shared-community-5.0.51a-0.rhel5.i386.rpm
MySQL-test-community-5.0.51a-0.rhel5.i386.rpm

[root@centos1 src]# rpm -i MySQL-server-community-5.0.51a-0.rhel5.i386.rpm MySQL-client-community-5.0.51a-0.rhel5.i386.rpm MySQL-shared-community-5.0.51a-0.rhel5.i386.rpm

[root@centos1 src]# mysqladmin -u root password ‘password’
[root@centos1 src]# mysqladmin -u root -p -h centos1 password ‘password’
Enter password:

[root@centos1 src]# 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.0.51a-community MySQL Community Edition (GPL)
Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the buffer.

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| func                      |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| proc                      |
| procs_priv                |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
17 rows in set (0.01 sec)

mysql> q
Bye

Lakukan langkah instalasi database MySQL server seperti diatas pada mesin centos2 …
setelah selesai, kembali ke mesin centos1 dan masuk ke direktori /tmp untuk meng-ekstrak file world.sql

[root@centos1 src]# cd /tmp
[root@centos1 tmp]# unzip /home/david/world.sql.zip

[root@centos1 tmp]# 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.0.51a-community MySQL Community Edition (GPL)
Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the buffer.

mysql> create database world;
mysql> use world;
mysql> source world.sql;

mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| City            |
| Country         |
| CountryLanguage |
+-----------------+
3 rows in set (0.00 sec)

mysql> desc City;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   |     |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

mysql> select * from City order by ID desc limit 5;
+------+------------+-------------+------------+------------+
| ID   | Name       | CountryCode | District   | Population |
+------+------------+-------------+------------+------------+
| 4079 | Rafah      | PSE         | Rafah      |      92020 |
| 4078 | Nablus     | PSE         | Nablus     |     100231 |
| 4077 | Jabaliya   | PSE         | North Gaza |     113901 |
| 4076 | Hebron     | PSE         | Hebron     |     119401 |
| 4075 | Khan Yunis | PSE         | Khan Yunis |     123175 |
+------+------------+-------------+------------+------------+
5 rows in set (0.00 sec)

mysql> q
Bye

Shutdown database MySQL server dan buat file my.cnf atau edit file bila sudah ada pada direktori /etc
buat konfigurasi master untuk log-bin=mysql-bin dan set server-id=1

[root@centos1 tmp]# service mysql stop
Shutting down MySQL SUCCESS!

[root@centos1 tmp]# vi /etc/my.cnf
[root@centos1 tmp]# cat /etc/my.cnf
[mysqld]
log-bin=mysql-bin
server-id=1

setelah file my.cnf sudah dibuat, kopikan file database world pada direktori /var/lib/mysql ke centos2
pastikan database MySQL server pada mesin centos2 sudah di shutdown

[root@centos1 tmp]# cd /var/lib/mysql
[root@centos1 mysql]# tar -cf - world | ssh root@192.168.216.11 "cd /var/lib/mysql/; tar -xvf -"

The authenticity of host ‘192.168.216.11 (192.168.216.11)’ can’t be established.
RSA key fingerprint is 93:89:08:4f:08:57:6b:b9:5e:09:9b:2e:a6:0d:34:57.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added ‘192.168.216.11’ (RSA) to the list of known hosts.
root@192.168.216.11’s password:
world/
world/Country.frm
world/db.opt
world/CountryLanguage.frm
world/CountryLanguage.MYI
world/Country.MYI
world/City.MYI
world/CountryLanguage.MYD
world/City.frm
world/Country.MYD
world/City.MYD

kemudian jalankan database MySQL server kembali dengan service mysql start

[root@centos1 mysql]# service mysql start
Starting MySQL. SUCCESS!
[root@centos1 mysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 1
Server version: 5.0.51a-community-log MySQL Community Edition (GPL)

Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the buffer.

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |       98 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)


lalu buat user pada master, user ini akan digunakan oleh slave untuk replikasi

mysql> grant replication slave on *.* to ‘repl_user’@’192.168.216.11’ identified by ‘password’;
Query OK, 0 rows affected (0.00 sec)

mysql> q

Kemudian kembali ke mesin centos2 dan pastikan database world sudah ada di direktori /var/lib/mysql

[root@centos2 ~]# cd /var/lib/mysql/
[root@centos2 mysql]# ls
centos2.err ibdata1 ib_logfile0 ib_logfile1 mysql test world

masih di mesin centos2 dan buat konfigurasi slave pada file my.cnf set server-id=2, relay-log=aank-relay-bin dan relay-log-index=aank-relay-bin.index

[root@centos2 mysql]# vi /etc/my.cnf
[root@centos2 mysql]# cat /etc/my.cnf
[mysqld]
server-id=2
relay-log=aank-relay-bin
relay-log-index=aank-relay-bin.index
read-only

kemudian jalankan database MySQL server pada centos2

[root@centos2 mysql]# service mysql start --skip-slave
Starting MySQL SUCCESS!

mysql> change master to master_host=’192.168.216.10′, master_user=’repl_user’, master_password=’password’, master_log_file=”, master_log_pos=4;
Query OK, 0 rows affected (0.05 sec)

mysql> start slave;
Query OK, 0 rows affected (0.02 sec)

mysql> q

Cek file error log /var/lib/mysql/centos2.err Pastikan slave dapat terkoneksi dengan master

[root@centos2 mysql]# cat /var/lib/mysql/centos2.err

lihat baris paling bawah seperti berikut:

080723 1:32:42 [Note] Slave SQL thread initialized, starting replication in log
‘FIRST’ at position 0, relay log ‘./aank-relay-bin.000001’ position: 4
080723 1:32:50 [Note] Slave I/O thread: connected to master ‘repl_user@192.168.
216.10:3306’, replication started in log ‘FIRST’ at position 4

Kembali ke mesin centos1 dan lakukan test insert satu baris

[root@centos1 mysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 17
Server version: 5.0.51a-community-log MySQL Community Edition (GPL)

Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the buffer.

mysql> use world
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> INSERT INTO City (Name, CountryCode, District, Population) VALUES (‘Solusi247 City’, ‘021’, ‘Segitiga Emas Business Park’, 247);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from City order by ID desc limit 5;
+------+----------------+-------------+----------------------+------------+
| ID   | Name           | CountryCode | District             | Population |
+------+----------------+-------------+----------------------+------------+
| 4080 | Solusi247 City | 021         | Segitiga Emas Busine |        247 |
| 4079 | Rafah          | PSE         | Rafah                |      92020 |
| 4078 | Nablus         | PSE         | Nablus               |     100231 |
| 4077 | Jabaliya       | PSE         | North Gaza           |     113901 |
| 4076 | Hebron         | PSE         | Hebron               |     119401 |
+------+----------------+-------------+----------------------+------------+
5 rows in set (0.01 sec)

Kembali ke mesin centos2 untuk mengecek apakah yang di insert pada mesin centos1 juga ter-insert di mesin centos2

[root@centos2 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.0.51a-community MySQL Community Edition (GPL)

Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the buffer.

mysql> use world
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select * from City order by ID desc limit 5;
+------+----------------+-------------+----------------------+------------+
| ID   | Name           | CountryCode | District             | Population |
+------+----------------+-------------+----------------------+------------+
| 4080 | Solusi247 City | 021         | Segitiga Emas Busine |        247 |
| 4079 | Rafah          | PSE         | Rafah                |      92020 |
| 4078 | Nablus         | PSE         | Nablus               |     100231 |
| 4077 | Jabaliya       | PSE         | North Gaza           |     113901 |
| 4076 | Hebron         | PSE         | Hebron               |     119401 |
+------+----------------+-------------+----------------------+------------+
5 rows in set (0.00 sec)

yapppsss…. replikasi berhasil!
testing ini dengan centos yang running di VMware khusus buat posting di blog, sebelumnya gw pernah juga coba replikasi dari linux ke wendow, SunOS 5.10, FreeBSD 6.0 ke HP-UX B.11.11 pada intinya sama aja cuma beda performance ajah.
sebagai catatan:
Replikasi dapat berjalan jika versi mysql sama atau master nya tidak boleh lebih kecil dari slave server

mudah bukan? Semoga bermanfaat dan menjadi referensi buat anda yang ingin mencoba replikasi MySQL …

[root@centos1 ~]# echo "set dah pagi! mo bo2 dulu ah.. capekjugs"; date
set dah pagi! mo bo2 dulu ah.. capekjugs
Wed Jul 23 02:57:20 SGT 2008

Referensi:
http://dev.mysql.com/doc/refman/5.1/en/ … howto.html
http://www.onlamp.com/pub/a/onlamp/2006 … ation.html

Advertisements

About davidand

Hi, welcome to my blogs. My name is David Andriansyah. I am currently working as an independent Cognos Business Intelligence consultant, and I also as Freelance Web Developer. View all posts by davidand

4 responses to “Step by Step MySQL Replication

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: