MYSQL REPLICATION

MYSQL REPLICATION CHEAT SHEET

USER REPLICANT CREATION:

Log into Master server as root and issue this sql statement


GRANT REPLICATION SLAVE, REPLICATION CLIENT
ON *.*
TO 'replicant'@'slave_host'
IDENTIFIED BY 'my_pwd';




Log into Slave server as root and issue this sql statement

GRANT REPLICATION SLAVE, REPLICATION CLIENT
ON *.*
TO 'replicant'@'master_host'
IDENTIFIED BY 'my_pwd';


Configuring the Servers

MASTER:


The only changes need to make in my.cnf are

server-id = 1
log-bin = /var/log/mysql/bin.log


SLAVE:


The only changes need to make in my.cnf are

server-id = 2

master-host = mastersite.com
master-port = 3306
master-user = replicant
master-password = my_pwd

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

Copying Databases and Starting Replication

Master Server:
mysqldump --user=root --password=my_pwd \
--extended-insert --all-databases \
--master-data > /tmp/backup.sql

*Copy the backup.sql file to slave server and follow the
Below commands.

Slave Server:

mysql --user=root --password=my_pwd < /tmp/backup.sql START SLAVE;


Automating Backups


A shell script to take automated backup of Database dump at slave server.


#!/bin/sh date = `date +%Y%m%d`
mysqladmin --user=root --password=my_pwd stop-slave
mysqldump --user=root --password=my_pwd --lock-all-tables --all-databases > /backups/mysql/backup-${date}.sql
mysqladmin --user=root --password=my_pwd start-slave