Açıklaması şöyle. Yani veri kaybedebilir.
MySQL replication by default is asynchronous. The source writes events to its binary log and replicas request them when they are ready. The source does not know whether or when a replica has retrieved and processed the transactions, and there is no guarantee that any event ever reaches any replica. With asynchronous replication, if the source crashes, transactions that it has committed might not have been transmitted to any replica. Failover from source to replica in this case might result in failover to a server that is missing transactions relative to the source.
Şeklen şöyle
Resmin açıklaması şöyle. Burada 400 tane dump thread yaratılıyor diyor ama doğrumu bilmiyorum. Replica tarafında IO thread aldığı veriyi Relay log denilen dosyaya yazar. Replica tarafında SQL thread relay log dosyasını okur ve işler.
First, let's look into the traditional style of replication. This replication process involves 4 major steps, 2 on the source end and 2 on the replica's side.- Whenever the master's database is modified, the change is written to a file. This file is called the binary log or popularly binlog. Binlog is written by the same client thread, which executed the query. The binary log serves as a written record of all events that modify database structure (DDL) or content (data) (DML) from the moment the server was started.- The master has a thread, called the dump thread, that continuously reads the master's binlog and sends it to the replicas. Dump threads are created for each replica listening to the changes. For example, if in 1 second 100 writes happen on master and there are 4 replicas connected, then 400 dump threads will be created. Each replica that connects to the source requests a copy of the binary log. Note that replicas pull the data from the source, rather than the source pushing the data to the replica.- On the replica side, it has a thread called IO thread that receives the binlog changes that are sent by the master's dump thread and writes it to a file: called the relay log.- The replica has another thread, called the SQL thread, that continuously reads the relay log and applies the changes to the replica server.
slave_master_info Tablosu
Açıklaması şöyle
The mysql.slave_master_info table contains the metadata information of the connection created between the replica and the source database. Remember, we mentioned, that we should use a different user for replication because the password is saved as plain text, we can see that in slave_master_info table.
Replica üzerinde çalıştırılır Çıktısı şöyle
> select * from slave_master_info\G"
Number_of_lines: 33
Master_log_name: mysql-bin-1.000005
Master_log_pos: 156
Host: master
User_name: replicationUser
User_password: replicationPass
Port: 3306
Connect_retry: 60
Enabled_ssl: 0
Ssl_ca:
Ssl_capath:
Ssl_cert:
Ssl_cipher:
Ssl_key:
Ssl_verify_server_cert: 0
Heartbeat: 30
Bind:
Ignored_server_ids: 0
Uuid: f329dfab-6a5d-11ec-8e93-0242ac110002
Retry_count: 86400
Ssl_crl:
Ssl_crlpath:
Enabled_auto_position: 0
Channel_name:
Tls_version:
Public_key_path:
Get_public_key: 0
Network_namespace:
Master_compression_algorithm: uncompressed
Master_zstd_compression_level: 3
Tls_ciphersuites: NULL
Source_connection_auto_failover: 0
Gtid_only: 0
slave_relay_log_info Tablosu
Açıklaması şöyle
mysql.slave_relay_log_info table contains the metadata information about the SQL thread which reads the relay log and applies those transactions on the replica.
Replica üzerinde çalıştırılır Çıktısı şöyle
> select * from slave_relay_log_info\G
Number_of_lines: 14
Relay_log_name: ./replica1-relay-bin.000002
Relay_log_pos: 1222
Master_log_name: mysql-bin-1.000005
Master_log_pos: 1003
Sql_delay: 0
Number_of_workers: 4
Id: 1
Channel_name:
Privilege_checks_username: NULL
Privilege_checks_hostname: NULL
Require_row_format: 0
Require_table_primary_key_check: STREAM
Assign_gtids_to_anonymous_transactions_type: OFF
Assign_gtids_to_anonymous_transactions_value:
GTID
Açıklaması şöyle. Eski yöntemde dosyadaki konum önemli. Yeni yöntemdeki dosyadaki GTID numarası kullanılıyor. Yani artık konum önemli değil.
The traditional method is based on replicating events from the source's binary log, and requires the log files and positions in them to be synchronized between source and replica. The newer method based on global transaction identifiers (GTIDs) is transactional and therefore does not require working with log files or positions within these files, which greatly simplifies many common replication tasks. Replication using GTIDs guarantees consistency between source and replica as long as all transactions committed on the source have also been applied on the replica.
Replication Lag
Açıklaması şöyle
Replication lag is caused when either the I/O Thread or SQL Thread cannot keep up with the ingestion from the source database.
It is rarely the case, that the IO thread is causing the issue. If the I/O Thread is suffering, this means that the network connection between the master and slave is slow. We should consider enabling the slave_compressed_protocol to compress network traffic from source to replica.
If the SQL thread is lagging, then there could be broadly 2 issues, we first need to categorize the issue and we need to find out if the replication is more CPU-bound or I/O-bound. Generally speaking, if we increase resources on the replica, these issues should be mitigated, however, we should first check the nature of the problem before proceeding with any solution.
Now, the obvious question would be: how would we know, whether it's an I/O thread issue or SQL thread issue? Well, MySQL has got us covered, with only show master status and show slave status, we can calculate which thread is becoming the bottleneck. The calculation is explained pretty well in this blog, so I am not going to show the calculation, please read this blog for the calculation. https://blogs.oracle.com/mysql/post/what-causes-replication-lag
Örnek
Master'da şöyle yaparız.
# On Master CREATE USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'slavepass'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'; SHOW MASTER STATUS;
Burada Master şöyle çalıştırılıyor
... --enforce-gtid-consistency='ON' \ --log-slave-updates='ON' \ --gtid-mode='ON' \ --log-bin='mysql-bin-1.log'
Çıktısı şöyle
mysql: [Warning] Using a password on the command line interface can be insecure. +--------------------+----------+--------------+------------------+------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +--------------------+----------+--------------+------------------+------------------------------------------+ | mysql-bin-1.000003 | 660 | | | 50c17f4e-d61f-11ed-bedc-0242ac14000b:1-2 | +--------------------+----------+--------------+------------------+------------------------------------------+
Slave'de şöyle yaparız
# On Slave CHANGE MASTER TO MASTER_HOST='172.20.0.11', MASTER_PORT=3306, MASTER_USER='repl', MASTER_PASSWORD='slavepass', MASTER_AUTO_POSITION = 1;" START SLAVE;
Örnek
Master'da şöyle yaparız
CREATE USER 'replicationUser'@'%' IDENTIFIED WITH \ mysql_native_password BY 'replicationPass'; GRANT REPLICATION SLAVE ON *.* TO 'replicationUser'@'%';
Açıklaması şöyle. Burada yeni bir kullanıcı yaratılıyor ve sadece ama sadece REPLICATION SLAVE hakkı veriliyor.
We have created a new user on master and given it the privilege of REPLICATION SLAVE, please note that any account can be used for this operation, providing it has been granted the REPLICATION SLAVE privilege. Creating a new user for replication is recommended as the user having replication slave privilege's password is saved as plain text in mysql.slave_master_info table.
Daha sonra şöyle yaparız
> SHOW MASTER STATUS; mysql: [Warning] Using a password on the command line interface can be insecure. +--------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +--------------------+----------+--------------+------------------+-------------------+ | mysql-bin-1.000005 | 708 | | | | +--------------------+----------+--------------+------------------+-------------------+
Slave'de şöyle yaparız
CHANGE MASTER TO MASTER_HOST='master', MASTER_USER='replicationUser', \ MASTER_PASSWORD='replicationPass', MASTER_LOG_FILE='mysql-bin-1.000005';
Slave'de şöyle yaparız
SHOW SLAVE STATUS\G;
Çıktısı şöyle
Slave_IO_State: Waiting for source to send event
Master_Host: master
Master_User: replicationUser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin-1.000005
Read_Master_Log_Pos: 1003
Relay_Log_File: replica1-relay-bin.000002
Relay_Log_Pos: 1222
Relay_Master_Log_File: mysql-bin-1.000005
Slave_IO_Running: Yes
Slave_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_Master_Log_Pos: 1003
Relay_Log_Space: 1434
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: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: f329dfab-6a5d-11ec-8e93-0242ac110002
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:Örnek
SQL komut satırından şöyle yaparız
> SHOW PROCESSLIST;ID USER HOST DB COMMAND TIME STATE INFO43' 'vt_repl', '172.17.0.10:46490', NULL, 'Binlog Dump GTID', '3183', 'Master has sent all binlog to slave; waiting for more updates', NULL
SQL komut satırından şöyle yaparız
> SHOW MASTER STATUS File Position Binlog_Do_DB Binlog_Ignore_DB Executed_Gtid_Set vt-1330809953-bin.000001 10909 '' '' cb1656e8-dc03-11ec-a423-0242ac110009:1-35
Hiç yorum yok:
Yorum Gönder