Advanced MySQL Replication Techniques and High performance mysql(1)

Advanced MySQL Replication Techniques
如果要同時使用兩台db, 而且又需要互相replication 那麼就需要這兩個 auto_increment_increment, auto_increment_offset 參數, 這是為了讓兩邊自動增加的 primary key 像是id 不要一致, 這樣相互repliaction 的時候才不會有相同的id 出現

---

如果出現問題的時候, 就先看error log, 找不到log 時, 就直接設參數
log-error = /var/log/mysql/error.log

以我的例子來說090415 9:24:47 [ERROR] Slave I/O thread: error connecting to master 'yhd@192.168.11.103:3306': Error: 'Can't connect to MySQL server on '192.168.11.103' (101)' errno: 2003 retry-time: 60 retries: 86400

連不上, 為啥會連不上勒, telnet 192.168.11.103 3306 一下, 真的連不上 = =+
到103 查了一下 才發現上面的mysql server port 開在4040 囧
改好之後就連上了 = =+
090415 12:49:32 [Note] Slave I/O thread: connected to master 'yhd@192.168.11.103:4040', replication started in log 'mysql-bin.000002' at position 1525<br />

還有有問題的時候可以在用show slave status 看一下, 像上面有問題的情況就會看到
Slave_IO_Running 是NO
Slave_IO_State 是Connecting to master
表示連到master 那邊有問題

如果正常的話
Slave_IO_Running 是yes
Slave_IO_State 是Waiting for master to send event

---

據High performance Mysql 書上的建議
別再把slave 連master 的 master_host, master_port 的設定放在my.cnf 裡, 可能會有問題而且沒有啥優點
請直接用CHANGE MASTER TO 指令去改
<br />mysql > CHANGE MASTER TO MASTER_HOST='192.168.11.103',<br /> MASTER_USER='repl',<br /> MASTER_PASSWORD='p4ssword',<br /> MASTER_LOG_FILE='mysql-bin.000001'<br /> MASTER_LOG_POS=0;<br />
MASTER_LOG_POS=0 , 0不是指真正的log position 而是指at the start of log file

---

Waiting for master to send event 意思是 has fetched all of the master's binary logs

sync_binlog=1 , This makers MySQL synchronize the binary log's contents to disk each time it commits a transaction, so you dont lose log events if there's a crash

innodb_flush_logs_at_trx_commit=1 #Flush every log write
innodb_support_xa=1 #MySQL 5.0 and newer only
innodb_safe_binlog #MySQL 4.1 only

skip_slave_start will prevent the slave from starting automatically after a crash, which can give you a chance to repair a server if it has problems.

Even if you have enabled all the options we suggested, a slave can easily break after a crash, because the relay logs and master.info file are not crash-safe. They are not even flushed to disk, and there is no configuration option to control that behavior