Mysql Replication

February 27, 2009

Biar ga lupa (sebagai catatan pribadi) dan buat sharing juga, berikut yang saya lakukan untuk mereplikasi data mysql.

Sumber:
http://dev.mysql.com/doc/refman/5.0/en/replication.html

Note:
Master IP: 192.168.0.1
Slave IP: 192.168.0.2
Passwd Mysql : s3cr3t
Database : cat_db
Data dir : /cs2008/cat_db/

Master Configuration:

# cp /etc/my.cnf /etc/my.cnf.master

# vi /etc/my.cnf
[mysqld]
log-bin=mysql-bin
binlog-do-db=cat_db
server-id=1

# service mysql restart
# cd /cs2008/cat_db/
# scp -r cat_db root@192.168.0.2:/cs2008/cat_db
# mysql -u root -h 127.0.0.1 -P 3306 -p

mysql> GRANT REPLICATION SLAVE ON *.* TO 'root'@'%' IDENTIFIED BY 's3cr3t';
mysql> SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000007 | 639 | cat_db | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Slave Configuration:

# cp /etc/my.cnf /etc/my.cnf.slave

# vi /etc/my.cnf
[mysqld]
server-id=2
master-host=192.168.0.1
master-user=root
master-password=s3cr3t
master-connect-retry=60
replicate-do-db=cat_db

# chown -R mysql.mysql /cs2008/cat_db
# service mysql restart
# mysql -u root -h 127.0.0.1 -P 3306 -p

mysql> CHANGE MASTER TO
-> master_host='192.168.0.1',
-> master_user='root',
-> master_password='s3cr3t',
-> master_log_file='',
-> master_log_pos=4;

mysql> start slave;

Test the replicate:

on master:
mysql> SHOW DATABASES;
mysql> USE cat_db;
mysql> CREATE TABLE test_rep
-> (NoID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-> Nama VARCHAR(50) NOT NULL);

mysql> SHOW TABLES;
mysql> INSERT INTO test_rep
-> (Nama) VALUES ("Ahmad"), ("Budi"), ("Rudi");

mysql> SELECT * FROM test_rep;
+------+-------+
| NoID | Nama |
+------+-------+
| 1 | Ahmad |
| 2 | Budi |
| 3 | Rudi |
+------+-------+
3 rows in set (0.01 sec)

* tambah datanya lagi dan lihat pada slave apakah bertambah juga

mysql> INSERT INTO test_rep
-> (Nama) VALUES ("Jhoni");

mysql> SELECT * FROM test_rep;
+------+-------+
| NoID | Nama |
+------+-------+
| 1 | Ahmad |
| 2 | Budi |
| 3 | Rudi |
| 4 | Jhoni |
+------+-------+
4 rows in set (0.00 sec)

on slave:
mysql> USE cat_db;
mysql> SHOW TABLES;

* lihat apakah ada table test_rep

mysql> SELECT * FROM test_rep;

* lihat apakah datanya bertambah

That’s all

Advertisements

One Response to “Mysql Replication”

  1. siswaplus Says:

    ditunggu nih wejengan bw optimasi index & query database SQLnya, kapan mas assasment ke BJM lg nich 😀


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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

%d bloggers like this: