體驗 Amazon RDS 與 MySQL Replica 運作模式，有幾項心得：
- Amazon RDS 當 Slave 時，必須改用 Amazon 自定 func 來啟動，如 mysql.rds_set_external_master、mysql.rds_reset_external_master、mysql.rds_start_replication (START SLAVE)、mysql.rds_stop_replication (STOP SLAVE) 和 mysql.rds_skip_repl_error 等
- Amazon RDS 使用 UTC 時區，依照 MySQL Replication and Time Zones 文件所述，Master 跟 Slave 的時區須一致，因此，也調整 Master 情況，對於 Timestamp 的用法就要避免用 MySQL CURRENT_TIMESTAMP 等，建議避開透過 MySQL 管理時區，直接用程式設定 timestamp 數值。但這樣變動還滿大的。
- 定期用 mysql> SHOW SLAVE STATUS \G 觀看，儘量不要用 SELECT count(*) FROM table 來觀看，因為會需要關注的情況通常是資料量已經很大了，而 AWS RDS $0.10 per 1 million I/O requests，一直用 count(*) 在資料量大的情況也會很可觀的。Seconds_Behind_Master 數值，可能短期不會降下來，用0.5~1天的時間間隔去觀察是否有下降即可
- 機器要 reboot 前，記得要先用 mysql.rds_stop_replication (等同 STOP Slave) 指令，若 Seconds_Behind_Master 數值很大，通常要跑很久
mysql> CALL mysql.rds_stop_replication; +---------------------------+ | Message | +---------------------------+ | Slave is down or disabled | +---------------------------+ 1 row in set (5 min 28.69 sec)
- 假設 RDS 專門當 MySQL Replica Slave 的話，記得 Master 可以透過 binlog-ignore-db 濾掉 mysql 等 databases，不然當 Master 更動 mysql 時（例如 apt-get upgrade 有 mysql 時也會有機會碰到）也會把這些 log 記錄起來，到時後 RDS 收到就會噴錯誤訊息，而 RDS 的解法就是不斷地用 mysql.rds_skip_repl_error 略過這些更動（可以搭配 SHOW SLAVE STATUS \G 查看 Last_SQL_Error 資訊，例如：
mysql> SHOW SLAVE STATUS \G ... Last_SQL_Error: Error 'Access denied for user ''@'' to database 'mysql'' on query. Default database: 'mysql'. Query: 'ALTER TABLE db MODIFY Host char(60) NOT NULL default '', MODIFY Db char(64) NOT NULL default '', MODIFY User char(16) NOT NULL default '', ENGINE=MyISAM, CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin' mysql> CALL mysql.rds_skip_repl_error ; +-------------------------------------+ | Message | +-------------------------------------+ | Statement in error has been skipped | +-------------------------------------+ 1 row in set (0.05 sec) +-----------------------------------------------------------------------------------+ | Message | +-----------------------------------------------------------------------------------+ | Slave has encountered a new error. Please use SHOW SLAVE STATUS to see the error. | +-----------------------------------------------------------------------------------+ 1 row in set (2.05 sec) Query OK, 0 rows affected (2.05 sec) ... mysql> CALL mysql.rds_skip_repl_error ; +---------------------------------------------------------+ | Message | +---------------------------------------------------------+ | Slave is running normally. No errors detected to skip. | +---------------------------------------------------------+ 1 row in set (0.03 sec)
此外，對於 RDS 操作心得：
- 開一個單位出來至少 15 分鐘起跳，從 create 到 backup 約 15 分鐘，其中 backup 是開台例行流程，以 18GB的 storage size 來說，約 3 分鐘，從 backup 換到 modifying 還要 2分鐘。
- 從 snapshot restore 亦是如此，過程還必須重 load my.cnf 設定(因為預設不給挑 my.cnf)，過程像是要再 reboot 一下，從 applying 到 pending-reboot 約 4 分鐘，當按下手動 reboot 時，兩分鐘內狀態從 pending-reboot 變成 in-sync，但 DB Instance Status 還是在 rebooting，從 rebooting 轉成 available 約 2 分鐘
- 試過 db.t1.micro 跟 db.m1.small 做 MySQL Replica Slave 的角色，發現 DB server 資料增加量追不上？改用 db.m1.medium 了($0.115 per RDS Medium Instance hour )，穩穩地有看到 Seconds_Behind_Master 下降，但貴的不是開台的價錢，還可能是 I/O Requests 的價碼，資料要一直追，現況 12 小時約 0.5 million I/O requests ($0.10 per 1 million I/O requests)
所以從 snapshot 開一檯機器都可以用耗時約 27 分鐘，比我想像中慢了不少，測試起來真痛苦。
其他心得：
其他心得：
- 別忘了確認 Master 跟 Slave 的 character，此例我是設定為 utf8，RDS 預設也是 latin1
mysql> show variables like '%char%'; +--------------------------+-------------------------------------------+ | Variable_name | Value | +--------------------------+-------------------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /rdsdbbin/mysql-5.5.33.R1/share/charsets/ | +--------------------------+-------------------------------------------+
- 如果未來打算把 RDS 當 Master 的話，可以留意 auto_increment_increment 跟 auto_increment_offset 的設定，可提供平順的轉換機制（可支援從 Read 變 Write）
mysql> show variables like 'auto_inc%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | auto_increment_increment | 10 | | auto_increment_offset | 2 | +--------------------------+-------+
- 建一台 RDS 個人常做設定 Parameter Groups：
max_allowed_packet = 67108864 sync_binlog = 1 innodb_flush_log_at_trx_commit = 1 auto_increment_offset = m auto_increment_increment = n collation_connection = utf8_unicode_ci character_set_results = utf8 character_set_server = utf8 character_set_connection = utf8 character_set_database = utf8 character_set_client = utf8 character_set_filesystem = binary
- 一般 MySQL Server Master my.cnf：
[mysqld] # ... character-set-server=utf8 collation-server=utf8_general_ci # ... default-time-zone = '+00:00' server-id = x auto-increment-increment = y auto-increment-offset = z log_bin = /var/log/mysql/mysql-bin.log binlog-ignore-db = mysql binlog-ignore-db = performance_schema binlog-ignore-db = information_schema binlog-ignore-db = test innodb_flush_log_at_trx_commit = 1 sync_binlog = 1
- 簡易 Slave 設定對應表:
- 一般 MySQL Slave:
mysql> CHANGE MASTER TO MASTER_HOST='YourDBServerIP', MASTER_USER='repl_account', MASTER_PASSWORD='repl_password', MASTER_LOG_FILE='mysql-bin.######', MASTER_LOG_POS=#######; mysql> START SLAVE; mysql> STOP SLAVE; mysql> RESET SLAVE;
- RDS Slave:
mysql> CALL mysql.rds_set_external_master ( 'YourDBServerIP' , 3306 , 'repl_account' , 'repl_password' , 'mysql-bin.######' , ####### , 0 ); mysql> CALL mysql.rds_start_replication; mysql> CALL mysql.rds_stop_replication; mysql> CALL mysql.rds_reset_external_master;
- 一般 MySQL Slave:
- 如果MySQL Replica Master 是 5.5.x 版，那依照 MySQL 文件在 RDS 開一台 5.6.x 當 MySQL Replica Slave 應該也行，文件出處：MySQL 5.6 Reference Manual :: 16 Replication :: 16.4 Replication Notes and Tips :: 16.4.3 Upgrading a Replication Setup
When you upgrade servers that participate in a replication setup, the procedure for upgrading depends on the current server versions and the version to which you are upgrading.
This section applies to upgrading replication from older versions of MySQL to MySQL 5.6. A 4.0 server should be 4.0.3 or newer.
When you upgrade a master to 5.6 from an earlier MySQL release series, you should first ensure that all the slaves of this master are using the same 5.6.x release. If this is not the case, you should first upgrade the slaves. To upgrade each slave, shut it down, upgrade it to the appropriate 5.6.x version, restart it, and restart replication. Relay logs created by the slave after the upgrade are in 5.6 format.
...
沒有留言:
張貼留言