REPLIKASI MySQL MASTER - SLAVE

REPLIKASI MySQL MASTER - SLAVE

versi mysql :
 	- mysql-server-5.0.77-4.el5_5.4
	- mysql-5.0.77-4.el5_5.4

IP Server Master : 192.168.1.100
IP Server SLAVE  : 192.168.1.200

 ***. Pastikan versi MySQL slave dan master sama

Master :
1. Edit file /etc/my.cnf
   [mysqld]
     datadir=/var/lib/mysql
     socket=/var/lib/mysql/mysql.sock
     user=mysql
     log-bin=/var/log/mysql/mysql-bin.log
     binlog-do-db=dbku

   - buat file lognya
   # mkdir /var/log/mysql
   # touch /var/log/mysql/mysql-bin.log
   # chown mysql.mysql -Rf /var/log/mysql

   - restart server
   # service mysqld restart

2. Buat database : contoh database dbku dan satu table untuk contoh
   $ mysql -u root -p
   mysql> create database dbku;
   Query OK, 1 row affected (0.00 sec)
   mysql> use dbku;
   Database changed
   mysql> create table siswa (
       -> id integer auto_increment primary key,
       -> nama varchar(30)
       -> );
   Query OK, 0 rows affected (0.00 sec)

   mysql> INSERT INTO siswa values ('','Budiman');
   Query OK, 1 row affected, 1 warning (0.00 sec)
   mysql>quit;

3. Login kembali ke database dan buat user :
   $ mysql -u root -p
   mysql> GRANT REPLICATION SLAVE ON *.* TO [email protected] IDENTIFIED BY 'slave';
   mysql> FLUSH PRIVILEGES;
   mysql> use dbku;
   mysql> FLUSH TABLES WITH READ LOCK ;
   mysql> show master status;
   +------------------+----------+--------------+------------------+
   | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
   +------------------+----------+--------------+------------------+
   | mysql-bin.000003 |     449  | dbku         |                  |
   +------------------+----------+--------------+------------------+
   mysql> quit;

4. Backup database
   $mysqldump -u root -p dbku > dbku.sql

5. login kembali ke mysql
   $ mysql -u root -p
   mysql> unlock tables;
   mysql> quit

6. kirim file backup ke slave :
  $scp dbku.sql [email protected]:

--------------- SLAVE

1.edit file /etc/my.cnf
server-id=2
master-host=192.168.1.163
master-user=slave
master-password=slave
master-connect-retry=60
replicate-do-db=dbku


   - restart server
   # service mysqld restart

2. buat database dan restore file backup dari master
3. mysql -u root -p
   mysql> slave stop;
   mysql>change master to master_host='192.168.1.163',
                       master_user='slave',
                       master_password='slave',
                       master_log_file='mysql-bin.000002',
                       master_log_pos=702;
   mysql> start slave;