REPLICATION: MYSQL..

Replication: Mysql
MYSQL Replication

Points:
1) Binary logs records all changes.
2) One dump thread occurs per slave which is started on request from slave. It read event from binary logs and send to slave.
3) I/O thread send dump request(creation) to master and copies events to relay logs. Relay logs is a disk based buffer for events.
4) SQL thread read events from relay logs. It decodes and applies events to database.

=====================================================================
Define things to replicate:
Master: bin-do-db, binlog-ignore-db
Slave: replicate-do-db(or table), replicate-ignore-db(or table)
=====================================================================

Scenario:
Master1: 192.168.1.1
Master2: 192.168.1.2
Slave1: 192.168.1.3
=====================================================================
##############################################################

Master1:-

# vi /etc/my.cnf
server-id = 1
log-bin = /var/log/mysql/var/bin.log
log-slave-updates
log-bin-index = /var/log/mysql/var/log-bin.index # index file for binary logs
bin-error = /var/log/mysql/var/error.log

relay-log = /var/log/mysql/var/relay.log
relay-log-info-file = /var/log/mysql/var/relay-log.info
relay-log-index = /var/log/mysql/var/relay-log.index

auto_increment_increment = 10
auto_increment_offset = 1
master-host = 192.168.1.2
master-user = user
master-password = pass

replicate-do-db = database1
replicate-do-db = database2

————————————————————————————————

Master2:- (For this server, use the same settings as above except below one)

# vi /etc/my.cnf
auto_increment_offset = 2
server-id = 2
master-host = 192.168.1.1
master-user = user
master-password = pass

———————————————————————————————

Slave1:- read only database (Do not use ‘auto_increment_offset’ or ‘auto_increment_increment’)

# vi /etc/my.cnf
server-id = 3
master-host = 192.168.1.1
master-user = user
master-password = pass

#################################################################
——————————————————————————————–

Note:
1) The master sets the “auto_increment” value in its binary log which the slave reads via replication
2) Set “auto_increment_increment” to 10, which will allow for 10 different servers, all of which could read-write masters if I wanted them to be
3) “auto_increment_offset” determines the starting point for “AUTO_INCREMENT” column values.
4) “auto_increment_increment” controls the increment between successive AUTO_INCREMENT values.

————————————————————————————————
Case 1:- You have just added a new slave:-

Step1 :-
Master1:-

# mysqldump -u root -p database1 > db.sql
and record bin log file name and position

mysql> show master status;
————–output——————
File Position Binlog_do_db ignoredb
bin.0002 1105
—————————————

Step2:-
Slave:-

(copy the above sql file from master over this server)
# mysql -u root -p database1 < db.sql Now do the following:-
mysql> stop slave;
mysql> stop slave status;
mysql> change master to MASTER_HOST=’master1.com’, Master_USER=’replication’, MASTER_PASSWORD=’replicationpass’, MASTER_LOG_FILE=’bin.0002′, MASTER_LOG_POS=1105;

mysql> start slave;
mysql> show slave status;

===========================================================================

Definition:-
1) Binary Logs:- It contains ‘events’ that describe database changes e.g table creation operation.
2) Slave Relay Logs:- During replication, a slave server creates several files that hold the binary log events relayed from master to slave, and to record information about the current status & location within the relay logs.
3) Index files:- are for indexing binary/relay log file names.

===================================================================

Questions:-
1: I want to replicate a new slave server from first position.
A: # mysqldump -u root -p –master-data-1 dbname > db.sql
Execute the below on slave server:
mysql> stop slave;
# mysql -u root -p dbname < db.sql mysql> start slave;

Tags:

Leave a Reply

Your email address will not be published. Required fields are marked *

nineteen − 13 =

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>