GRANT REPLICATION SLAVE ON *.* TO repl@'%' IDENTIFIED BY '【パスワード】';
[mysqld] log-bin server-id=1 binlog-ignore-db=mysql auto_increment_increment=2 auto_increment_offset=1 sync_binlog=1 master-host=【レプリケーション先(DB 2台目)】 master-user=【アカウント】 master-password=【パスワード】
[mysqld] log-bin server-id=2 binlog-ignore-db=mysql auto_increment_increment=2 auto_increment_offset=2 sync_binlog=1 master-host=【レプリケーション先(DB 1台目)】 master-user=【アカウント】 master-password=【パスワード】
090613 14:14:50 [Note] Slave I/O thread: connected to master 'repl@[HOST NAME]:3306',replication started in log '[HOST NAME]-bin.000002' at position 194
090613 14:22:47 [ERROR] Slave I/O: error reconnecting to master 'repl@[HOST NAME]:3306' - retry-time: 60 retries: 86400, Error_code: 2013
mysql> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: localhost Master_User: root Master_Port: 3306 Connect_Retry: 3 Master_Log_File: gbichot-bin.005 Read_Master_Log_Pos: 79 Relay_Log_File: gbichot-relay-bin.005 Relay_Log_Pos: 548 Relay_Master_Log_File: gbichot-bin.005 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 79 Relay_Log_Space: 552 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 8
以下の操作は不具合が出るので保留します
1台目
mysql1> show master status; +------------------+----------+ | File | Position | +------------------+----------+ | mysql-bin.000014 | 941 | +------------------+----------+ 1 row in set (0.00 sec)
2台目
mysql2> show master status; +------------------+----------+ | File | Position | +------------------+----------+ | mysql-bin.000022 | 1164 | +------------------+----------+ 1 row in set (0.00 sec)
1台目
mysql1> CHANGE MASTER TO MASTER_HOST='【DB 2台目】', MASTER_USER='【アカウント】', MASTER_PASSWORD='【パスワード】', MASTER_LOG_FILE='mysql2-bin.000014'; mysql1> start slave;
2台目
mysql2> CHANGE MASTER TO MASTER_HOST='【DB 1台目】', MASTER_USER='【アカウント】', MASTER_PASSWORD='【パスワード】', MASTER_LOG_FILE='mysql1-bin.000012'; mysql2> start slave;
http://dev.mysql.com/doc/refman/5.1/ja/replication.html
http://www.irori.org/doc/mysql-rep.html
http://d.hatena.ne.jp/end0tknr/20090202/1233557966
http://www.open-source-network.info/archives/3
http://www.usamimi.info/~sakura/cgi-bin/blog/article.php?id=96