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
