search by tags

for the user

adventures into the land of the command line

how to set up replication for mysql

replication enables data from one MySQL database server (the master) to be replicated to one or more MySQL database servers (the slaves) it’s useful for a recovery or failover situation, where all your precious data is backed up in real time to another computer, so if something happens to the master, you haven’t lost everything it’s a little bit better than taking nightly backups, as the amount of data lost would be much less due to the backups happening constantly in real time, as opposed to once a day you need to have at least two computers that are able to communicate with one another and the following items need to be performed to enable replication of the master database
1 enable binary logging
2 set a unique server id
3 disable skip-networking
4 define relay-log
5 configure databases to replicate and databases to ignore
6 synchronize the bin-log
7 create a user for replication
8 obtain the master binary log coordinates
9 obtain a database dump
the following items need to be performed to enable replication for the slave database
1 set a unique server id
2 import the master database dump
3 configure replication coordinates

setting up the master’s configuration

login to the master host and edit the configuration file as follows in /etc/my.cnf

enable binary logging

log-bin=mysql-bin

set the unique server id

server-id = 1

as a precaution, ensure that the skip-networking setting is commented out

# skip-networking

define the relay-log, replace hostname with the current hosts name

relay-log=masterhostname-relay-bin

configure databases to replicate and ignore

binlog_do_db=somedb
binlog_do_db=someotherdb
binlog_ignore_db=information_schema
binlog_ignore_db=mysql
binlog_ignore_db=test

synchronize the binlog

sync_binlog=1

the my.cnf entries should look as follows

[mysqld]
log-bin=mysql-bin
server-id=1
relay-log=masterhostname-relay-bin
binlog_do_db=somedb
binlog_do_db=someotherdb
binlog_ignore_db=information_schema
binlog_ignore_db=mysql
binlog_ignore_db=test

sync_binlog=1

restart mysql

$ /etc/init.d/mysql restart

creating a user for replication

each slave that connects to the master requires a username and password with the right privileges to perform replication calls.

login to the master mysql environment as root

$ mysql –u root -p

create the user repl, providing the hostname/ip of the slave host that will be connecting to the master host

mysql> CREATE USER 'repl'@'10.1.2.3' IDENTIFIED BY 'somepassword'
mysql> CREATE USER 'repl'@'hostname' IDENTIFIED BY 'somepassword'

grant the user replication privileges

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.1.2.3';
mysql> GRANT REPLICATION SLAVE ON *.* TO ‘repl'@'hostname'
mysql> FLUSH PRIVILEGES;

obtaining the master binary log coordinates

whenever something happens in a database on the master that has binary logging enabled, it will record it in the binary log, which essentially is a loooong list of things that have happened. replication essentially makes the slave read this binary log and then copy what has happened line for line in its own database. so each slave that connects to the master needs to point to the ‘coordinates’ (or line number in the binary log) of the master database

login to the master mysql database as root.

$ mysql –u root -p

flush all tables and block write statements from executing, leave this session open and continue on from a new mysql connection

mysql> FLUSH TABLES WITH READ LOCK;

from a new session execute the show master status statement to determine the current binary log file and coordinates.

mysql> SHOW MASTER STATUS\G;

something similar to the following will be displayed.

mysql> SHOW MASTER STATUS\G;