Setting master slave relationship between MySQL DBs

1.creating a user for replication.
Each slave must connect to the master using a standard MySQL user name and password, so there must be a user account on the master that the slave can use to connect. privilege REPLICATION SLAVE must be granted to the selected replication user.

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';

2. configure a master
2.1 must enable binary logging on master.
log-bin=c:\xxx\log file name
server-id=x
#specify the db replication is for
binlog-do-db=exampledb

3. configure a slave
server-id=2
master-host=192.168.0.100
master-connect-retry=60
replicate-do-db=exampledb
#these two parameters can be defined in script
master-user=slave_user
master-password=secret

you do not need to enable binary log on a slave.

or you can do by mysql command:
CHANGE MASTER TO MASTER_HOST='master_host_name',MASTER_USER='replication_user_name',MASTER_PASSWORD='replication_password',MASTER_LOG_FILE='recorded_log_file_name',MASTER_LOG_POS=recorded_log_position;

usually, it's not necessary to specify the log file and log position information. as a good practice, the following is a suggested sequence on setting slave:
- set slave server-id and master-host in mysql configure file
- stop slave
- reset slave
- change master to xxxxx
- start slave

4. to load data from master if the source db is myisam type and there's no db created on slave yet. in order to run that command successfully, RELOAD,SUPER and SELECt privileges on the tables you want to load have to be granted to the replication user.

load data from master;

5. replicate to another database
eg. db1 to db2 on another server
replicate-rewrite-db=db2->db2
replicate-do-db=db2

Misc:
A better method to ensure exact replication would go like the below:
1) start master
2) grant replication to slave
3) run "show master status" and record the index file and it's offset position
4) start the slave
5) run "stop slave"
6) run "change master to master_host='[MASTER HOST IP]', master_user='[USER]', master_password='[PASSWORD]', master_log_file='[NAME]', master_log_pos=[POSITION]" (from #3)
7) run "start slave"


misc:
1. get master replication information
-flush tables with read lock;
-show master status;
-unlock tables;
2. get slave replication information
-show slave status;
3.enforce log switch
flush logs;
4.retrieve log information
-show master logs
-show slave logs

Backup and Restore MySQL Database

1. Configure mysql to generate binlog.
example:
in my.ini, define these two lines
log-bin=c:\mysql_blog\mysqlblog
max_binlog_size=10m

2.make scheduled task that runs script to make full backup specified database.
the command used can be mysqldump

3. make scheduled task that runs script to make back up of binlog files. an easy way to back up only the newly created bin log files is to remove the binlog files have been backed up.

the backup scripts can rotate after a certain period.

upon the time to restore the database from disaster such as hard disk failure, the following steps can be followed.

1. restore the database from recent full database backup
mysql -u -p db 2. restore all the bin logs since the recent full database backup
mysqlbinlog binlogfiles binlogfile2 ... >onebig file
mysql -u -p db <onebig file

IO redirection can also be used here