Replication enables data from one MySQL database server (the master) to be replicated to one or more MySQL database servers (the slaves). Replication is asynchronous – slaves need not be connected permanently to receive updates from the master. This means that updates can occur over long-distance connections and even over temporary or intermittent connections such as a dial-up service. Depending on the configuration, you can replicate all databases, selected databases, or even selected tables within a database.

mysql replication

 

How To Set Up Database Replication In MySQL:

Configure master 192.168.100.10 (/etc/mysql/my.cnf):

log-bin = /var/log/mysql/mysql-bin.log
server-id=10
auto-increment-increment = 2
auto-increment-offset = 1
binlog-do-db=testdatabase
replicate-ignore-db=test
replicate-ignore-db=mysql
max-binlog-size=1024M

This settings will be available after a mysql restart.

Log into the MySQL shell and create a user with replication privileges:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.100.11' 
IDENTIFIED BY 'replication'

This wil create a user with replication privileges, with access from slave database, 192.168.100.11

In this moment we need a database dump (a copy of databasetest). This can be achived by mysqldump, mysqlhotcopy, load data from master (on slave) or just tar.gz the database files. Each method will be later discussed.

Configure slave 192.168.100.11 (/etc/mysql/my.cnf):

server-id = 11
auto-increment-increment = 2
auto-increment-offset = 2
master-host=192.168.100.10
master-user=slave
master-password=replication
master-port=3306
replicate-ignore-db=mysql
replicate-ignore-db=test
replicate-do-db=databasetest
master-info-file=/var/lib/mysql/master.info
master-connect-retry=60

Stop mysql server:

/etc/init.d/mysqld stop

Copy database to slave machine.

Start mysql server:

/etc/init.d/mysqld start

Check slave status:

mysql> show slave status \G

Look for this fields:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0

this mean that the replication is working!

 

Note:

“auto-increment-increment” and “auto-increment-offset” will be later explained.

 

 

MySQL replication – master slave schema
Tagged on:         

Leave a Reply

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