ããã¯ããªã«ãããããŠæžãããã®ïŒ
äžã®äžã"master"ã"slave"ãšããåèªãéãè¡šçŸã«çœ®ãæããããŠãã£ãŠããŸãããããããã°MySQLã®
ã¬ããªã±ãŒã·ã§ã³ã§ããã®ãããã®åèªã䜿ã£ãŠããŸãããã©ããªã£ãã®ããªïŒãšæã£ãŠèŠãŠã¿ããã
ã¡ãã£ãšãã€å€ãã£ãŠãã£ãŠããã¿ãããªã®ã§ã眮ãæããããåèªã§ã¬ããªã±ãŒã·ã§ã³ãæ§æããŠã¿ãããšã«
ããŸããã
ããšãã¬ããªã±ãŒã·ã§ã³ã§ã®éä¿¡ãéSSLïŒTLSã§è¡ãå Žåã®èªèšŒã§ãã¡ãã£ãšããã£ãã®ã§ããã¡ããã¡ã¢ãšããŠã
åèªã®å€å
MySQL 8.0.21ã§ããã¥ã¡ã³ããã8.0.22以éã§å®éã«äœ¿ãã·ã³ã¿ãã¯ã¹ãå€åããŠãã£ãŠããã¿ããã§ãã
æ¹éçã«ã¯ã"master"ã"source"ãžã"slave"ã"replica"ãžã
ããšã¯"whitelist"ã"allowlist"ãžã"blacklist"ã"blocklist"ãžããšããæãã¿ããã§ããã
In the documentation for MySQL 8.0.21, we have started changing the term âmasterâ to âsourceâ, the term âslaveâ to âreplicaâ, the term âwhitelistâ to âallowlistâ, and the term âblacklistâ to âblocklistâ. There are currently no changes to the product's syntax, so these terms are still present in the documentation where the current code requires their use. See the blog post MySQL Terminology Updates for more information.
MySQL :: MySQL 8.0 Release Notes :: Changes in MySQL 8.0.21 (2020-07-13, General Availability)
From MySQL 8.0.22, the statements START SLAVE, STOP SLAVE, SHOW SLAVE STATUS, SHOW SLAVE HOSTS and RESET SLAVE are deprecated. The following aliases should be used instead:
MySQL :: MySQL 8.0 Release Notes :: Changes in MySQL 8.0.22 (2020-10-19, General Availability)
From MySQL 8.0.23, the statement CHANGE MASTER TO is deprecated. The alias CHANGE REPLICATION SOURCE TO should be used instead. The parameters for the statement also have aliases that replace the term MASTER with the term SOURCE.
MySQL :: MySQL 8.0 Release Notes :: Changes in MySQL 8.0.23 (2021-01-18, General Availability)
Incompatible Change: From MySQL 8.0.26, new aliases or replacement names are provided for most remaining identifiers that contain the terms âmasterâ, which is changed to âsourceâ;
MySQL :: MySQL 8.0 Release Notes :: Changes in MySQL 8.0.26 (2021-07-20, General Availability)
ã¬ããªã±ãŒã·ã§ã³ã«é¢ããããã¥ã¡ã³ã
ã¬ããªã±ãŒã·ã§ã³ã«é¢ããããã¥ã¡ã³ãã¯ããã®ããããåç §ããŸãã
MySQL :: MySQL 8.0 リファレンスマニュアル :: 17.1.3.4 GTID を使用したレプリケーションのセットアップ
MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.4.1 ソースサーバーを制御する SQL ステートメント
MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.4.2 レプリケーションサーバーを制御するための SQL ステートメント
GTIDã䜿ã£ãã¬ããªã±ãŒã·ã§ã³ã§çµãããšã«ããŸãã
ç°å¢
ä»åã®ç°å¢ã¯ããã¡ãã§ãã
MySQLã
mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.27 | +-----------+ 1 row in set (0.00 sec)
MySQLãµãŒããŒã¯2å°çšæãã以äžã®åœ¹å²ãšããŸãã
- Source ⊠172.17.0.2
- Replica ⊠172.17.0.3
Sourceã¯ä»¥åã§èšãMasterãReplicaã¯ä»¥åã§èšãSlaveã§ãã
Sourceã®ã»ããã¢ãã
ã§ã¯ããŸãã¯SourceåŽã®ã»ããã¢ããããã
ã¬ããªã±ãŒã·ã§ã³ãŸããã§ãå¿ èŠãªèšå®ã¯ãã®ãããã
## gtid gtid_mode = on enforce_gtid_consistency = on ## binary log log-bin = mysql-bin sync_binlog = 1 binlog_expire_logs_seconds = 864000 binlog_checksum=NONE ## replication server_id = 1
ã¬ããªã±ãŒã·ã§ã³çšã®ãŠãŒã¶ãŒãäœæã
mysql> create user repl@'%' identified by 'password'; Query OK, 0 rows affected (0.04 sec) mysql> grant replication slave on *.* to 'repl'@'%'; Query OK, 0 rows affected (0.02 sec)
grantæã«é¢ããŠã¯ã"slave"ãšããåèªããŸã æ®ã£ãŠããããã§ãïŒ"replica"ã«ã¯çœ®ãæããããŸããã§ããïŒã
GRANT ã¹ããŒãã¡ã³ã / MySQL ã«ãã£ãŠãµããŒããããæš©é
ããã§ãSourceã®ã»ããã¢ããã¯çµããã§ãã
ãŸãããããŸã§ã¯åèªã®å€åã¯èŠããŸãããã
ã¡ãªã¿ã«ãshow master status
ã«ã€ããŠãåèªã¯å€ãã£ãŠããªãã¿ããã§ãã
MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.7.7.23 SHOW MASTER STATUS ステートメント
mysql> show master status\G *************************** 1. row *************************** File: mysql-bin.000002 Position: 660 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: c39bd721-35b2-11ec-9fc4-0242ac110002:1-2 1 row in set (0.00 sec)
Replicaã®ã»ããã¢ãã
ç¶ããŠãReplicaãèšå®ããŸãã
MySQLã®èšå®ã¯ããã¡ããSourceãšã¯server_id
ãç°ãªããããããread_only
ãon
ã«ãªã£ãŠããŸãã
## gtid gtid_mode = on enforce_gtid_consistency = on ## binary log log-bin = mysql-bin sync_binlog = 1 binlog_expire_logs_seconds = 864000 binlog_checksum=NONE ## replication server_id = 2 read_only = on
ã¬ããªã±ãŒã·ã§ã³ã§ã®éä¿¡ã«SSLïŒTLSã䜿ãå Žåãchange replication source to
æã䜿ã£ãŠSourceã®æ
å ±ãèšå®ããéã«ã
source_ssl
ã1
ã«ããŸãã
mysql> change replication source to source_host = '172.17.0.2', source_port = 3306, source_ssl = 1, source_auto_position = 1; Query OK, 0 rows affected (0.23 sec)
ã¬ããªã±ãŒã·ã§ã³ã§ã®éä¿¡ãSSLïŒTLSåããªãå Žåã¯ãSourceã«å¯ŸããŠå
ã»ã©äœæããã¬ããªã±ãŒã·ã§ã³çšã®ãŠãŒã¶ãŒã§
æ¥ç¶ãã--get-server-public-key
ãªãã·ã§ã³ã䜿ã£ãŠRSAå
¬ééµãååŸããŸãã
â»ãããã¯ãã¬ããªã±ãŒã·ã§ã³ã®éä¿¡ãSSLïŒTLSã«ããŸã
$ mysql --ssl-mode=DISABLED -urepl -p -h172.17.0.2 --get-server-public-key Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.27 MySQL Community Server - GPL Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
æ¥ç¶ããããç¹ã«ãªã«ãè¡ãå¿ èŠã¯ãªãã®ã§åæããŸãã
mysql> exit Bye
ãããŠãchange replication source to
æã§Sourceã®æ
å ±ãèšå®ããŸãã
mysql> change replication source to source_host = '172.17.0.2', source_port = 3306, source_auto_position = 1; Query OK, 0 rows affected (0.13 sec)
SSLïŒTLSãæå¹ãç¡å¹ã®ããããã®æ¹æ³ã§change replication source to
æãå®è¡ããããstart replica
æã§
ã¬ããªã±ãŒã·ã§ã³ãéå§ããŸãã
mysql> start replica user = 'repl' password = 'password'; Query OK, 0 rows affected (0.04 sec)
ã¬ããªã±ãŒã·ã§ã³ã®ã¹ããŒã¿ã¹ç¢ºèªã
mysql> show replica status\G *************************** 1. row *************************** Replica_IO_State: Waiting for source to send event Source_Host: 172.17.0.2 Source_User: repl Source_Port: 3306 Connect_Retry: 60 Source_Log_File: mysql-bin.000002 Read_Source_Log_Pos: 660 Relay_Log_File: 6502f56a3e70-relay-bin.000002 Relay_Log_Pos: 867 Relay_Source_Log_File: mysql-bin.000002 Replica_IO_Running: Yes Replica_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Source_Log_Pos: 660 Relay_Log_Space: 1075 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Source_SSL_Allowed: Yes Source_SSL_CA_File: Source_SSL_CA_Path: Source_SSL_Cert: Source_SSL_Cipher: Source_SSL_Key: Seconds_Behind_Source: 0 Source_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Source_Server_Id: 1 Source_UUID: c39bd721-35b2-11ec-9fc4-0242ac110002 Source_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates Source_Retry_Count: 86400 Source_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Source_SSL_Crl: Source_SSL_Crlpath: Retrieved_Gtid_Set: c39bd721-35b2-11ec-9fc4-0242ac110002:1-2 Executed_Gtid_Set: c39bd721-35b2-11ec-9fc4-0242ac110002:1-2 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Source_TLS_Version: Source_public_key_path: Get_Source_public_key: 0 Network_Namespace: 1 row in set (0.01 sec)
確èª
æåŸã«ãã¬ããªã±ãŒã·ã§ã³ãåäœããŠããããšã確èªããŸãã
Sourceã«æ¥ç¶ããŠãããŒã¿ããŒã¹ãšãŠãŒã¶ãŒãäœæã
mysql> create database example; Query OK, 1 row affected (0.03 sec) mysql> create user kazuhira@localhost identified by 'password'; Query OK, 0 rows affected (0.03 sec) mysql> create user kazuhira@'%' identified by 'password'; Query OK, 0 rows affected (0.02 sec) mysql> grant all privileges on example.* to 'kazuhira'@localhost; Query OK, 0 rows affected, 1 warning (0.02 sec) Warning (Code 1285): MySQL is started in --skip-name-resolve mode; you must restart it without this switch for this grant to work mysql> grant all privileges on example.* to 'kazuhira'@'%'; Query OK, 0 rows affected (0.02 sec) mysql> flush privileges; Query OK, 0 rows affected (0.01 sec)
äœæãããŠãŒã¶ãŒã§ãSourceã«ãã°ã€ã³ããŠããŒãã«äœæã
$ mysql -ukazuhira -p example Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 Server version: 8.0.27 MySQL Community Server - GPL Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create table t(c int); Query OK, 0 rows affected (0.11 sec)
Replicaã§ç¢ºèªããŠã¿ãŸãã
$ mysql -ukazuhira -p example Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 15 Server version: 8.0.27 MySQL Community Server - GPL Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show tables; +-------------------+ | Tables_in_example | +-------------------+ | t | +-------------------+ 1 row in set (0.00 sec)
OKã§ããã
ReplicaåŽã¯ãRead Onlyã§ãã
mysql> create table t2(c2 int); ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
ããã§ã確èªã§ããŸããããšã
RSAå ¬ééµã®ååŸã«ã€ããŠ
Replicaã®æ§ç¯ããŠããäžã§ãRSAå ¬ééµã®ååŸããããŸããã
ãããé£ã°ããšã以äžã®ãããªãšã©ãŒãèŠãããšã«ãªããŸããéSSLïŒTLSæ¥ç¶ã§ã®æã«çºçããã¿ããã§ãã
2021-10-25T14:10:38.866725Z 15 [ERROR] [MY-010584] [Repl] Slave I/O for channel '': error connecting to master 'repl@172.17.0.2:3306' - retry-time: 60 retries: 1 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. Error_code: MY-002061
ãããåé¿ããã«ã¯ãRSAå
¬ééµããµãŒããŒããååŸãããã1床ãµãŒããŒã«ãã°ã€ã³ããŠãã£ãã·ã¥ãäœæãã
å¿
èŠãããã¿ããã§ãã
MySQL :: MySQL 8.0 リファレンスマニュアル :: 6.4.1.2 SHA-2 プラガブル認証のキャッシュ
日々の覚書: MySQL 8.0のcaching_sha2_password + 非SSL接続が転ける
8.0ã®æåãããããªæãã§ããã£ãâŠïŒ
ä»åãRSAå
¬ééµã®ååŸãããªãå Žåã«source_ssl
ã1
ã«ããæ¹æ³ãæžããŸããããããã¯éä¿¡ã®æå·åã®ã¿ã
æå³ããŸãã
ã¬ããªã±ãŒã·ã§ã³æ¥ç¶çšã® SOURCE_SSL=1 | MASTER_SSL=1 ãèšå®ãããã以äžã® SOURCE_SSL_xxx | MASTER_SSL_xxx ãªãã·ã§ã³ãèšå®ããªãããšã¯ãæå·åæ¥ç¶ã®ã³ãã³ããªãã·ã§ã³ ã§èª¬æãããŠããããã«ãã¯ã©ã€ã¢ã³ãçšã® --ssl-mode=REQUIRED ã®èšå®ã«å¯Ÿå¿ããŸãã
MySQL :: MySQL 8.0 リファレンスマニュアル :: 17.3.1 暗号化接続を使用するためのレプリケーションの設定
--ssl-mode=VERIFY_CA
ã--ssl-mode=VERIFY_IDENTITY
çžåœãè¡ãã«ã¯ããå°ãèšå®ãå¿
èŠã¿ããã§ããã
ãã¡ãã¯ä»åã¯ãã¹ã§ãâŠã
MySQL :: MySQL 8.0 リファレンスマニュアル :: 6.3.1 暗号化接続を使用するための MySQL の構成