Mysql transaction log restoration

almost all the documents you can find on the Net are telling you to use mysqlbinlog to read the binary logs and pipe the output to mysql client, but this method simply will fail on windows system if the database contains binary data such as blob and binvarchar, due to escaping and piping problem in mysqlbinlog utility. the mysql utility simply can not read the file or output stream created by mysqlbinlog.

here's a workaround though, we can tell mysql to use --execute "source xx" option to parse the log files from mysqlbinlog.

E.g.
C:\> mysqlbinlog binary_log_file --result-file=/tmp/bin.sql
C:\> mysql --user=root --execute "source /tmp/bin.sql"

ref 1: http://bugs.mysql.com/bug.php?id=33048
ref 2: http://www.linuxtopia.org/online_books/database_guides/mysql_5.1_database_reference_guide/windows-vs-unix.html

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