您好,登錄后才能下訂單哦!
這篇文章主要介紹“MySQL異步復(fù)制和半同步復(fù)制怎么實現(xiàn)”,在日常操作中,相信很多人在MySQL異步復(fù)制和半同步復(fù)制怎么實現(xiàn)問題上存在疑惑,小編查閱了各式資料,整理出簡單好用的操作方法,希望對大家解答”MySQL異步復(fù)制和半同步復(fù)制怎么實現(xiàn)”的疑惑有所幫助!接下來,請跟著小編一起來學(xué)習(xí)吧!
在異步復(fù)制(async replication)中,Master不用關(guān)心Slave是否接收到二進(jìn)制日志,所以Master與Slave沒有任何的依賴關(guān)系。你可以認(rèn)為Master和Slave是分別獨自工作的兩臺服務(wù)器,數(shù)據(jù)最終會通過二進(jìn)制日志達(dá)到一致。
異步復(fù)制的性能最好,因為它對數(shù)據(jù)庫本身幾乎沒有任何開銷,除非主從延遲非常大,Dump Thread需要讀取大量二進(jìn)制日志文件。
如果業(yè)務(wù)對于數(shù)據(jù)一致性要求不高,當(dāng)發(fā)生故障時,能容忍數(shù)據(jù)的丟失,甚至大量的丟失,推薦用異步復(fù)制,這樣性能最好(比如像微博這樣的業(yè)務(wù),雖然它對性能的要求極高,但對于數(shù)據(jù)丟失,通??梢匀萑蹋5诵臉I(yè)務(wù)系統(tǒng)最關(guān)心的就是數(shù)據(jù)安全,比如監(jiān)控業(yè)務(wù)、告警系統(tǒng)。
異步復(fù)制環(huán)境的規(guī)劃:
master(docker),端口3310
slave(docker),端口3311
配置文件my.cnf
$ sudo cat /home/mysql/docker-data/3311/conf/my.cnf # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html [mysqld] # # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M #datadir=/home/mysql/docker-data/3307/data #socket=/home/mysql/docker-data/3307/mysql.sock character_set_server=utf8 init_connect='SET NAMES utf8' # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 #log-error=/home/mysql/docker-data/3307/logs/mysqld.log #pid-file=/home/mysql/docker-data/3307/mysqld.pid lower_case_table_names=1 # 表名是否小寫 server-id=1403311 log-bin=mysql-bin # 開啟binlog binlog-format=ROW # binlog的格式 auto_increment_increment=1 # 自增的步長,適用于主主復(fù)制,為了避免id沖突,步長設(shè)置為master的個數(shù) auto_increment_offset=1 # 自增的偏移,主主復(fù)制每個master的偏移需要不一致 # binlog-do-db=mstest # 要同步的數(shù)據(jù)庫 # binlog-ignore-db=mysql # 要忽略的數(shù)據(jù)庫 #rpl_semi_sync_master_enabled=1 #rpl_semi_sync_master_timeout=10000
啟動mysql:
$ docker run --name mysql3310 -p 3310:3306 --privileged=true -ti -e MYSQL_ROOT_PASSWORD=root -e MYSQL_DATABASE=order -e MYSQL_USER=user -e MYSQL_PASSWORD=pass -v /home/mysql/docker-data/3310/conf:/etc/mysql/conf.d -v /home/mysql/docker-data/3310/data/:/var/lib/mysql -v /home/mysql/docker-data/3310/logs/:/var/log/mysql -d mysql:5.7
創(chuàng)建用于同步的用戶:
mysql> GRANT REPLICATION SLAVE,FILE,REPLICATION CLIENT ON *.* TO 'repluser'@'%' IDENTIFIED BY '123456'; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.01 sec)
在master上查看master的二進(jìn)制日志:
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000003 | 1147 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
查看master上的進(jìn)程列表:
mysql> show processlist; +----+----------+------------------+-------+-------------+------+---------------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+----------+------------------+-------+-------------+------+---------------------------------------------------------------+------------------+ | 2 | root | localhost | order | Query | 0 | starting | show processlist | | 6 | repluser | 172.17.0.1:48450 | NULL | Binlog Dump | 448 | Master has sent all binlog to slave; waiting for more updates | NULL | +----+----------+------------------+-------+-------------+------+---------------------------------------------------------------+------------------+ 2 rows in set (0.00 sec)
配置文件my.cnf與master的配置一致,除了server-id字段需唯一。
啟動mysql:
$ docker run --name mysql3311 -p 3311:3306 --privileged=true -ti -e MYSQL_ROOT_PASSWORD=root -e MYSQL_DATABASE=order -e MYSQL_USER=user -e MYSQL_PASSWORD=pass -v /home/mysql/docker-data/3311/conf:/etc/mysql/conf.d -v /home/mysql/docker-data/3311/data/:/var/lib/mysql -v /home/mysql/docker-data/3311/logs/:/var/log/mysql -d mysql:5.7
在slave中設(shè)置master的信息:
# master_log_file和master_log_pos取上面show master status顯示的值mysql> change master to master_host='172.23.252.98',master_port=3310,master_user='repluser',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=1147;
開啟slave,啟動SQL和IO線程:
mysql> start slave;Query OK, 0 rows affected (0.00 sec)
查看slave的狀態(tài):
mysql> show slave status\G;*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.54.214 Master_User: repluser Master_Port: 3310 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 1147 Relay_Log_File: 2da789531bf3-relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000003 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: 1147 Relay_Log_Space: 534 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: 0Master_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: 1403311 Master_UUID: cd2eaa0a-7a59-11ec-b3b4-0242ac110002 Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave 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:1 row in set (0.00 sec)
只有新增的數(shù)據(jù)才會進(jìn)行復(fù)制,存量的數(shù)據(jù)需要手動使用工具(如mysqldump)進(jìn)行同步。
查看slave上的進(jìn)程列表:
mysql> show processlist; +----+-------------+-----------+-------+---------+------+--------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+-------+---------+------+--------------------------------------------------------+------------------+ | 4 | root | localhost | order | Query | 0 | starting | show processlist | | 7 | system user | | NULL | Connect | 533 | Waiting for master to send event | NULL | | 8 | system user | | NULL | Connect | 127 | Slave has read all relay log; waiting for more updates | NULL | +----+-------------+-----------+-------+---------+------+--------------------------------------------------------+------------------+ 3 rows in set (0.00 sec)
半同步復(fù)制是MySQL 5.7版本前的半同步復(fù)制機(jī)制。
半同步復(fù)制要求Master事務(wù)提交過程中,至少有N個Slave接收到二進(jìn)制日志,這樣就能保證當(dāng)Master發(fā)生宕機(jī),至少有N 臺Slave服務(wù)器中的數(shù)據(jù)是完整的。
半同步復(fù)制并不是MySQL內(nèi)置的功能,而是要安裝半同步插件,并啟用半同步復(fù)制功能,設(shè)置N個Slave接受二進(jìn)制日志成功。
缺點:假設(shè)user1在主庫插入了一條數(shù)據(jù),正在等待數(shù)據(jù)返回,user2這時就能查詢到這條數(shù)據(jù)了,如果此時master掛了,user2又查不到這條數(shù)據(jù)了,產(chǎn)生了類似幻讀的現(xiàn)象。
增強(qiáng)半同步復(fù)制解決了半同步復(fù)制的缺點,WAIT和ACK發(fā)生在事務(wù)提交之前,這樣即便Slave沒有收到二進(jìn)制日志,但是 Master宕機(jī)了,由于最后一個事務(wù)還沒有提交,所以本身這個數(shù)據(jù)對外也不可見,不存在丟失的問題。
所以,對于任何有數(shù)據(jù)一致性要求的業(yè)務(wù),如電商的核心訂單業(yè)務(wù)、銀行、保險、證券等與資金密切相關(guān)的業(yè)務(wù),務(wù)必使用增強(qiáng)半同步復(fù)制。這樣數(shù)據(jù)才是安全的、有保障的、即使發(fā)生宕機(jī),從機(jī)也有一份完整的數(shù)據(jù)。
增強(qiáng)半同步復(fù)制環(huán)境是基于異步復(fù)制的基礎(chǔ)上進(jìn)行的。
安裝插件,建議master和slave都安裝,因為會有主從切換的情景:
# master mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so'; # slave mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
確保插件安裝成功:
mysql> show plugins; ... ... | rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL | | rpl_semi_sync_slave | ACTIVE | REPLICATION | semisync_slave.so | GPL | +----------------------------+----------+--------------------+--------------------+---------+ 46 rows in set (0.00 sec)
先啟動slave上的半同步:
mysql> set global rpl_semi_sync_slave_enabled = {0|1}; # 1:啟用,0:禁止
再啟動master上的半同步:
mysql> set global rpl_semi_sync_master_enabled = {0|1}; # 1:啟用,0:禁止 # mysql> set global rpl_semi_sync_master_timeout = 10000; # 單位為ms,默認(rèn)為10s
從庫重啟io_thread:
mysql> stop slave io_thread; mysql> start slave io_thread;
參數(shù)寫入配置文件的話,會使實例啟動后立即進(jìn)入半同步模式,如果發(fā)生長時間斷連的實例重新運(yùn)行啟動,有可能導(dǎo)致主庫被拖垮。
長時間斷開的從庫,重新連接后,要等待追完全部事務(wù)后,手動開啟半同步模式,而不是啟動后直接切換,防止沖擊主庫。
mysql> show global status like "%semi%"; +--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | Rpl_semi_sync_master_clients | 0 | | Rpl_semi_sync_master_net_avg_wait_time | 0 | | Rpl_semi_sync_master_net_wait_time | 0 | | Rpl_semi_sync_master_net_waits | 0 | | Rpl_semi_sync_master_no_times | 0 | | Rpl_semi_sync_master_no_tx | 0 | | Rpl_semi_sync_master_status | ON | | Rpl_semi_sync_master_timefunc_failures | 0 | | Rpl_semi_sync_master_tx_avg_wait_time | 0 | | Rpl_semi_sync_master_tx_wait_time | 0 | | Rpl_semi_sync_master_tx_waits | 0 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | | Rpl_semi_sync_master_wait_sessions | 0 | | Rpl_semi_sync_master_yes_tx | 0 | | Rpl_semi_sync_slave_status | ON | +--------------------------------------------+-------+ 15 rows in set (0.00 sec)
重要參數(shù)說明:
Rpl_semi_sync_master_clients:支持和注冊半同步復(fù)制的已連Slave數(shù)
Rpl_semi_sync_master_no_times:master關(guān)閉半同步復(fù)制的次數(shù)
Rpl_semi_sync_master_no_tx:master沒有收到slave的回復(fù)而提交的次數(shù),可以理解為master等待超時的次數(shù),即半同步模式不成功提交數(shù)量
Rpl_semi_sync_master_status:ON是活動狀態(tài)(半同步),OFF是非活動狀態(tài)(異步),用于表示主服務(wù)器使用的是異步復(fù)制模式,還是半同步復(fù)制模式
Rpl_semi_sync_master_tx_avg_wait_time:master花在每個事務(wù)上的平均等待時間
Rpl_semi_sync_master_tx_waits:master等待成功的次數(shù),即master沒有等待超時的次數(shù),也就是成功提交的次數(shù)
Rpl_semi_sync_master_yes_tx:master成功接收到slave的回復(fù)的次數(shù),即半同步模式成功提交數(shù)量。
mysql> show global variables like '%sync%'; +-------------------------------------------+------------+ | Variable_name | Value | +-------------------------------------------+------------+ | binlog_group_commit_sync_delay | 0 | | binlog_group_commit_sync_no_delay_count | 0 | | innodb_flush_sync | ON | | innodb_sync_array_size | 1 | | innodb_sync_spin_loops | 30 | | rpl_semi_sync_master_enabled | ON | | rpl_semi_sync_master_timeout | 10000 | | rpl_semi_sync_master_trace_level | 32 | | rpl_semi_sync_master_wait_for_slave_count | 1 | | rpl_semi_sync_master_wait_no_slave | ON | | rpl_semi_sync_master_wait_point | AFTER_SYNC | | rpl_semi_sync_slave_enabled | ON | | rpl_semi_sync_slave_trace_level | 32 | | sync_binlog | 1 | | sync_frm | ON | | sync_master_info | 10000 | | sync_relay_log | 10000 | | sync_relay_log_info | 10000 | +-------------------------------------------+------------+ 18 rows in set (0.01 sec)
重要參數(shù)說明:
rpl_semi_sync_master_enabled:(主庫)是否啟動半同步
rpl_semi_sync_master_timeout:等待多時毫秒后變成異步復(fù)制,默認(rèn)是10000ms
rpl_semi_sync_master_wait_point:5.7默認(rèn)AFTER_SYNC(增強(qiáng)版半同步復(fù)制,無損復(fù)制模式),在得到slave的應(yīng)答后再commit,可選值A(chǔ)FTER_COMMIT,在master提交后同步數(shù)據(jù)給slave,然后master等待slave應(yīng)答,應(yīng)答成功返回客戶端。
可以在slave端執(zhí)行stop slave
,測試master端會發(fā)生什么情況?
在master上執(zhí)行下面的sql:
mysql> insert into t_order values(3,"C");Query OK, 1 row affected (10.05 sec)
會發(fā)現(xiàn)這條語句會阻塞10s(對應(yīng)上面的參數(shù)rpl_semi_sync_master_timeout),然后執(zhí)行成功,最后看看看master的日志描述:
2022-01-25T02:31:57.016430Z 4 [Note] Start binlog_dump to master_thread_id(4) slave_server(1403312), pos(mysql-bin.000005, 154) 2022-01-25T02:31:57.016515Z 4 [Note] Start asynchronous binlog_dump to slave (server_id: 1403312), pos(mysql-bin.000005, 154) 2022-01-25T02:33:32.183819Z 2 [Note] Semi-sync replication initialized for transactions. 2022-01-25T02:33:32.183865Z 2 [Note] Semi-sync replication enabled on the master. 2022-01-25T02:33:32.184004Z 0 [Note] Starting ack receiver thread 2022-01-25T02:33:59.644444Z 5 [Note] While initializing dump thread for slave with UUID <aba2eb12-7cbc-11ec-9c1d-0242ac110003>, found a zombie dump thread with the same UUID. Master is killing the zombie dump thread(4). 2022-01-25T02:33:59.644612Z 5 [Note] Start binlog_dump to master_thread_id(5) slave_server(1403312), pos(mysql-bin.000005, 154) 2022-01-25T02:33:59.644632Z 4 [Note] Stop asynchronous binlog_dump to slave (server_id: 1403312) 2022-01-25T02:33:59.644727Z 5 [Note] Start semi-sync binlog_dump to slave (server_id: 1403312), pos(mysql-bin.000005, 154) 2022-01-25T02:38:02.847879Z 0 [ERROR] mysqld: Got an error reading communication packets 2022-01-25T02:38:27.228952Z 2 [Warning] Timeout waiting for reply of binlog (file: mysql-bin.000005, pos: 684), semi-sync up to file mysql-bin.000005, position 419. 2022-01-25T02:38:27.229063Z 2 [Note] Semi-sync replication switched OFF. 2022-01-25T02:39:47.230189Z 5 [Note] Stop semi-sync binlog_dump to slave (server_id: 1403312)
可以發(fā)現(xiàn)半同步關(guān)閉了,變成異步模式。
到此,關(guān)于“MySQL異步復(fù)制和半同步復(fù)制怎么實現(xiàn)”的學(xué)習(xí)就結(jié)束了,希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學(xué)習(xí),快去試試吧!若想繼續(xù)學(xué)習(xí)更多相關(guān)知識,請繼續(xù)關(guān)注億速云網(wǎng)站,小編會繼續(xù)努力為大家?guī)砀鄬嵱玫奈恼拢?/p>
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報,并提供相關(guān)證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權(quán)內(nèi)容。