RDBMS Replication Master to Slave Mariadb

Published: November 04, 2014 by Galuh D Wijatmiko

Categories:
Databases 13

Master : 192.168.56.1 (Slackware) Mariadb
Slave : 192.168.56.3 (Centos) Mysql

Master and Slave make sure it is connected.

ON MASTER
Create Log For System Master
# mkdir -p /var/log/mysql
# touch /var/log/mysql/mysql-wjt.log

Enable Logbin
#mysql -u root -p -e ‘SET sql_log_bin = 1’

Copy full parameter configuration if my.cnf is blank configure
#cp /usr/share/mysql/my-large.cnf /etc/my.cnf

Configure MASTER /etc/my.cnf
#skip-networking
# bind-address = 127.0.0.1
log-bin = /var/log/mysql/mysql-wjt.log
binlog_format=row
binlog-do-db=gratika
server-id = 1

Restart mysql service
#/etc/rc.d/rc.mysqld start

create a new user MySQL database that will be entitled to replication
#mysql -u root -p -e ‘GRANT REPLICATION SLAVE ON *.* TO ‘replikasi’@’%’ IDENTIFIED BY '228844'’’;

#mysql -u root -p -e 'FLUSH PRIVILEGES’;

Database will by replication
#mysql -u root -p -e 'USE gratika’;
#mysql -u root -p -e ‘FLUSH TABLES WITH READ LOCK’;

Unlcok tables
#mysql -u root -p -e ‘UNLOCK TABLES’’
#mysql -u root -p -e 'FLUSH PRIVILEGES’;

Export database gratika for use on slave, purpose database have same between Master and Slave.
#mysqldump -u root -p gratika > ~/gratika_slave.wjt

Copy database gratika to server slave
#scp gratika_slave.wjt replikasi@192.168.56.3:/home/replikasi/

ON SLAVE
Create database
#mysql -u root -p -e 'create database gratika’

Import database
mysql -u root -p gratika < ~/gratika_slave.wjt

If mariadb or mysql version under 5.3, setting can configure on /etc/my.cnf

server-id=2
master-host=192.168.56.1
master-user=replikasi
master-password=228844
master-connect-retry=60
replicate-do-db=gratika

Else using mariadb or mysql above 5.5 using
#mysql -u root -p -e 'STOP SLAVE’;
#mysql -u root -p -e 'CHANGE MASTER TO MASTER_HOST='192.168.56.1’, MASTER_USER='replikasi’, MASTER_PASSWORD='228844’, MASTER_PORT=3306, MASTER_CONNECT_RETRY=60, MASTER_LOG_FILE='mysql-wjt.000001’, MASTER_LOG_POS=320’;
#mysql -u root -p -e 'START SLAVE’;

If there many error example :
ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MariaDB error log

Solving :
#mysql -u root -p -e 'RESET SLAVE’

and next flush privileges

#mysql -u root -p -e ’FLUSH PRIVILEGES’
On two method check server_id on mysqsql, it must be same with /etc/my.cnf

#grep server-id /etc/my.cnf
server-id=2

mysql> SHOW VARIABLES LIKE 'server_id’;
Value must be 2;

Check status :
#mysql -u root -p -e 'show slave statusG

Enter password:
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.56.1
Master_User: replikasi
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-wjt.000001
Read_Master_Log_Pos: 320
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 529
Relay_Master_Log_File: mysql-wjt.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 320
Relay_Log_Space: 827
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1