Wednesday 12 October 2011

MySql Replication - Master Master - From Scratch

Installing MySQL Replication
sql1.example.com (ubuntu), sql2.example.com (ubuntu)
aptitude install mysql-server mysql-client

Configuring MySQL
sql1.example.com: my.conf
server-id                       = 1
log-slave-updates
replicate-same-server-id        = 0
auto_increment_increment        = 5
auto_increment_offset           = 1
report-host                     = sql2.example.com
master-host                     = sql2.example.com
master-user                     = username
master-password                 = password
innodb_flush_log_at_trx_commit  = 1
sync_binlog                     = 1
skip-slave-start                = true
binlog-format                   = ROW


log-bin = /var/log/mysql/bin.log
log-bin-index = /var/log/mysql/log-bin.index
log-error = /var/log/mysql/error.log

relay-log = /var/log/mysql/relay.log
relay-log-info-file = /var/log/mysql/relay-log.info
relay-log-index = /var/log/mysql/relay-log.index
 
#comment out
#bind-address 127.0.0.1

sql2.example.com: my.conf
server-id                       = 2
log-slave-updates
replicate-same-server-id        = 0
auto_increment_increment        = 5
auto_increment_offset           = 2
report-host                     = sql1.example.com
master-host                     = sql1.example.com
master-user                     = username
master-password                 = password
innodb_flush_log_at_trx_commit  = 1
sync_binlog                     = 1
skip-slave-start                = true
binlog-format                   = ROW


log-bin = /var/log/mysql/bin.log
log-bin-index = /var/log/mysql/log-bin.index
log-error = /var/log/mysql/error.log

relay-log = /var/log/mysql/relay.log
relay-log-info-file = /var/log/mysql/relay-log.info
relay-log-index = /var/log/mysql/relay-log.index
 
#comment out
#bind-address 127.0.0.1

Restarting MySQL
For newer ubuntu 9.04++, use the following it faster
sql1.example.com , sql2.example.com
restart mysql

On older machine use the following

sql1.example.com , sql2.example.com
/etc/init.d/mysql restart

Creating the replication account
sql1.example.com
mysql -u root -p
CREATE USER 'username'@'sql2.example.com' 
IDENTIFIED BY 'password';
   GRANT ALL PRIVILEGES ON *.* 
TO 'username'@'sql2.example.com' 
WITH GRAND OPTION;
FLUSH PRIVILEGES;

sql2.example.com
CREATE USER 'username'@'sql1.example.com' 
IDENTIFIED BY 'password';
   GRANT ALL PRIVILEGES ON *.* 
TO 'username'@'sql1.example.com' 
WITH GRAND OPTION;
FLUSH PRIVILEGES;

Starting Replication
sql1.example.com, sql2.example.com
mysql -u username -ppassword -e "start slave"

Finish

No comments:

Post a Comment