MySQL replication note

- The Binary Log
- http://dev.mysql.com/doc/refman/5.0/en/binary-log.html

- PURGE BINARY LOGS

- RESET MASTER

- Flush BINARY Logs

- my.ini
- log-bin

- expire_logs_days

- max_binlog_size
- If a write to the binary log causes the current log file size to exceed the value of this variable, the server rotates the binary logs (closes the current file and opens the next one). You cannot set this variable to more than 1GB or to less than 4096 bytes. The default value is 1GB

- binlog-ignore-db

- ERROR 1062 (23000) at line 2: Duplicate entry '2' for key 'PRIMARY'
- use INSERT IGNORE statement.
- use REPLACE statement.

- MySQL data sync tool - Maatkit
http://www.maatkit.org/

- MySQL Replication with system snapshot
http://www.xaprb.com/blog/2007/01/20/how-to-make-mysql-replication-reliable/
http://www.mysqlperformanceblog.com/2009/03/04/making-replication-a-bit-more-reliable/

- mylvmbackup
www.lenzg.net/mylvmbackup
http://www.howtoforge.com/how-to-back-up-mysql-databases-with-mylvmbackup-on-ubuntu-8.10

- FalconStor Software
http://www.falconstor.com.tw/


- MySQL Proxy
http://forge.mysql.com/wiki/MySQL_Proxy

- MySQL Trigger
http://dev.mysql.com/doc/refman/5.0/en/triggers.html

- The binary log should be protected because logged statements might contain passwords.
See Section 5.5.6.1, “Administrator Guidelines for Password Security”.

http://dev.mysql.com/doc/refman/5.0/en/binary-log.html
http://dev.mysql.com/doc/refman/5.0/en/password-security-admin.html

- Master / Master configuration leads me to firmly believe it's a bad idea.

My experiences with configuring MySQL in a Master / Master configuration leads me to firmly believe it's a bad idea. The problem is that MySQL replication is best effort and offers no data delivery guarantees of any sort, so any data loss or corruption (ie from a network hiccup, or file system error, or a system administration error, or ...) is quietly ignored. If it doesn't lead to an ID conflict, nobody complains, yet you're now running with two "Masters" that contain different versions of the data. And errors often tend to cascade. There's a reason why MySQL calls the Master / Master configuration "unsupported".

Certainly there are hashing tricks that can be used to detect such data loss or corruption, but then comes the question of what is to be done when it's detected. Which Master is the correct one? Depending on the form of data loss or corruption, the answer may be "neither".

Finally, a Master / Master configuration would reintroduce the latency issues that Dries seems to be trying to solve by being able to select the Master or the Slave. In Master / Master mode, you no longer can guarantee a connection to a database with the absolute latest data.

BTW: core support for sharding would make an interesting (and imho much better) alternative to a Master-Master configuration.
August 2, 2007 - 18:28

http://buytaert.net/scaling-with-mysql-replication

- Apache Reverse proxy
http://en.wikipedia.org/wiki/Reverse_proxy

- --skip-write-binlog
Also, if you have a problem on your master, but your slaves are fine, and you need to restore the master, you can use --skip-write-binlog to avoid the point-in-time recovery to replicate to the slaves, which would cause more problems.
http://www.pythian.com/news/1174/

binlog-ignore-db=dblog
Be selective. Dont put into Binlog things which dont need replicating. NOTE: These will only ignore where "USE dbname" is set or if the statement is a CREATE dbname.tbname...
http://perplexed.co.uk/542_recreate_replication_with_mysqlbinlog.htm

Dont use wildcharacters to select multiple Binary logs. Instead use the modification timestamp of the file to determine whether its contains stuff required for the big push.
http://perplexed.co.uk/542_recreate_replication_with_mysqlbinlog.htm

MySQL Connector/ODBC 5.1
http://dev.mysql.com/downloads/connector/odbc/5.1.html

- Keywords
replication, redundancy, synchronization, failover, cluster, clustering