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
