2. Java
  3. mysql master slave 复制原理与实践

mysql master slave 复制原理与实践

xiaoxiao2022-06-11  57

接上一篇：https://blog.csdn.net/qq_26437925/article/details/83213961

master mysql> show master status -> ; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000003 | 154 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> slave mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.16.227.243 Master_User: root Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 154 Relay_Log_File: v2-relay-bin.000010 Relay_Log_Pos: 367 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: 154 Relay_Log_Space: 618 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: 5866ae02-d443-11e8-8f74-000c2959a0f2 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) ERROR: No query specified mysql>

从配置文件/etc/mysql/mysql.conf.d/mysqld.cnf可以找到各个log文件位置

root@v1:/var/lib/mysql# ll total 122936 drwx------ 6 mysql mysql 4096 Oct 22 08:41 ./ drwxr-xr-x 70 root root 4096 Oct 20 01:37 ../ -rw-r----- 1 mysql mysql 56 Oct 20 01:37 auto.cnf -rw-r--r-- 1 root root 0 Oct 20 01:37 debian-5.7.flag -rw-r----- 1 mysql mysql 284 Oct 20 23:01 ib_buffer_pool -rw-r----- 1 mysql mysql 12582912 Oct 21 06:36 ibdata1 -rw-r----- 1 mysql mysql 50331648 Oct 21 06:36 ib_logfile0 -rw-r----- 1 mysql mysql 50331648 Oct 20 01:37 ib_logfile1 -rw-r----- 1 mysql mysql 12582912 Oct 21 06:41 ibtmp1 drwxr-x--- 2 mysql mysql 4096 Oct 20 01:37 mysql/ -rw-r----- 1 mysql mysql 1513 Oct 21 08:16 mysql-bin.000001 -rw-r----- 1 mysql mysql 201 Oct 22 08:41 mysql-bin.000002 -rw-r----- 1 mysql mysql 154 Oct 22 08:41 mysql-bin.000003 -rw-r----- 1 mysql mysql 57 Oct 22 08:41 mysql-bin.index drwxr-x--- 2 mysql mysql 4096 Oct 20 01:37 performance_schema/ drwxr-x--- 2 mysql mysql 12288 Oct 20 01:37 sys/ drwxr-x--- 2 mysql mysql 4096 Oct 20 23:37 test/

查看binlog

mysql> show variables like 'log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | ON | +---------------+-------+ 1 row in set (0.03 sec) mysql> show global variables like "binlog%"; +--------------------------------------------+--------------+ | Variable_name | Value | +--------------------------------------------+--------------+ | binlog_cache_size | 32768 | | binlog_checksum | CRC32 | | binlog_direct_non_transactional_updates | OFF | | binlog_error_action | ABORT_SERVER | | binlog_format | ROW | | binlog_group_commit_sync_delay | 0 | | binlog_group_commit_sync_no_delay_count | 0 | | binlog_gtid_simple_recovery | ON | | binlog_max_flush_queue_time | 0 | | binlog_order_commits | ON | | binlog_row_image | FULL | | binlog_rows_query_log_events | OFF | | binlog_stmt_cache_size | 32768 | | binlog_transaction_dependency_history_size | 25000 | | binlog_transaction_dependency_tracking | COMMIT_ORDER | +--------------------------------------------+--------------+ 15 rows in set (0.00 sec) mysql>

使用如下命令能查看bin文件

#mysqlbinlog --base64-output="decode-rows" -v mysql-bin.000001

mater的binlog线程会记录下所有改变了数据库数据的语句，放进master上的binlog中；

show slave status可以看到有IO线程和SQL执行线程 2.1 io线程——在使用start slave 之后，负责从master上拉取 binlog 内容，放进 自己的relay log中； 2.2 从：sql执行线程——执行relay log中的语句；

slave机器上可以查看relay log

root@v2:/var/lib/mysql# ll total 122960 drwx------ 6 mysql mysql 4096 Oct 22 08:41 ./ drwxr-xr-x 69 root root 4096 Oct 20 02:21 ../ -rw-r----- 1 mysql mysql 56 Oct 20 02:21 auto.cnf -rw-r--r-- 1 root root 0 Oct 20 02:21 debian-5.7.flag -rw-r----- 1 mysql mysql 288 Oct 20 23:21 ib_buffer_pool -rw-r----- 1 mysql mysql 12582912 Oct 21 06:36 ibdata1 -rw-r----- 1 mysql mysql 50331648 Oct 21 06:36 ib_logfile0 -rw-r----- 1 mysql mysql 50331648 Oct 20 02:21 ib_logfile1 -rw-r----- 1 mysql mysql 12582912 Oct 20 23:21 ibtmp1 -rw-r----- 1 mysql mysql 128 Oct 23 08:24 master.info drwxr-x--- 2 mysql mysql 4096 Oct 20 02:21 mysql/ -rw-r----- 1 mysql mysql 177 Oct 20 23:21 mysql-bin.000001 -rw-r----- 1 mysql mysql 201 Oct 21 08:16 mysql-bin.000002 -rw-r----- 1 mysql mysql 201 Oct 22 08:41 mysql-bin.000003 -rw-r----- 1 mysql mysql 154 Oct 22 08:41 mysql-bin.000004 -rw-r----- 1 mysql mysql 76 Oct 22 08:41 mysql-bin.index drwxr-x--- 2 mysql mysql 4096 Oct 20 02:21 performance_schema/ -rw-r----- 1 mysql mysql 58 Oct 22 08:41 relay-log.info drwxr-x--- 2 mysql mysql 12288 Oct 20 02:21 sys/ drwxr-x--- 2 mysql mysql 4096 Oct 20 23:37 test/ -rw-r----- 1 mysql mysql 251 Oct 22 08:41 v2-relay-bin.000009 -rw-r----- 1 mysql mysql 367 Oct 22 08:41 v2-relay-bin.000010 -rw-r----- 1 mysql mysql 44 Oct 22 08:41 v2-relay-bin.index root@v2:/var/lib/mysql#

实践：

mysql> insert into t_user(username, email, password, status) values("test2", "test@test.com", "test", 0); Query OK, 1 row affected (0.07 sec) root@v1:/var/lib/mysql# mysqlbinlog --base64-output="decode-rows" -v mysql-bin.000003 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #181022 8:41:28 server id 1 end_log_pos 123 CRC32 0x782da571 Start: binlog v 4, server v 5.7.23-0ubuntu0.16.04.1-log created 181022 8:41:28 # Warning: this binlog is either in use or was not closed properly. # at 123 #181022 8:41:28 server id 1 end_log_pos 154 CRC32 0xb39e91e0 Previous-GTIDs # [empty] # at 154 #181023 18:54:24 server id 1 end_log_pos 219 CRC32 0xf066b818 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=yes /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 219 #181023 18:54:24 server id 1 end_log_pos 291 CRC32 0x1d9a044e Query thread_id=23 exec_time=0 error_code=0 SET TIMESTAMP=1540346064/*!*/; SET @@session.pseudo_thread_id=23/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1436549152/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; # at 291 #181023 18:54:24 server id 1 end_log_pos 350 CRC32 0xa2de7377 Table_map: `test`.`t_user` mapped to number 108 # at 350 #181023 18:54:24 server id 1 end_log_pos 420 CRC32 0xa9fad3e8 Write_rows: table id 108 flags: STMT_END_F ### INSERT INTO `test`.`t_user` ### SET ### @1=2 ### @2='test2' ### @3='test@test.com' ### @4='test' ### @5=0 # at 420 #181023 18:54:24 server id 1 end_log_pos 451 CRC32 0xde0c021e Xid = 182 COMMIT/*!*/; SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; root@v1:/var/lib/mysql# root@v2:/var/lib/mysql# mysqlbinlog --base64-output="decode-rows" -v v2-relay-bin.000010 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #181020 23:21:56 server id 2 end_log_pos 123 CRC32 0xb6ee0939 Start: binlog v 4, server v 5.7.23-0ubuntu0.16.04.1-log created 181020 23:21:56 # This Format_description_event appears in a relay log and was generated by the slave thread. # at 123 #181020 23:21:56 server id 2 end_log_pos 154 CRC32 0xa9c7c233 Previous-GTIDs # [empty] # at 154 #181020 23:21:56 server id 1 end_log_pos 0 CRC32 0x9337abef Rotate to mysql-bin.000003 pos: 4 # at 201 #181022 8:41:28 server id 1 end_log_pos 123 CRC32 0x782da571 Start: binlog v 4, server v 5.7.23-0ubuntu0.16.04.1-log created 181022 8:41:28 # at 320 #181020 23:21:56 server id 0 end_log_pos 367 CRC32 0xd6c2e255 Rotate to mysql-bin.000003 pos: 154 # at 367 #181023 18:54:24 server id 1 end_log_pos 219 CRC32 0xf066b818 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=yes /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 432 #181023 18:54:24 server id 1 end_log_pos 291 CRC32 0x1d9a044e Query thread_id=23 exec_time=0 error_code=0 SET TIMESTAMP=1540346064/*!*/; SET @@session.pseudo_thread_id=23/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1436549152/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; # at 504 #181023 18:54:24 server id 1 end_log_pos 350 CRC32 0xa2de7377 Table_map: `test`.`t_user` mapped to number 108 # at 563 #181023 18:54:24 server id 1 end_log_pos 420 CRC32 0xa9fad3e8 Write_rows: table id 108 flags: STMT_END_F ### INSERT INTO `test`.`t_user` ### SET ### @1=2 ### @2='test2' ### @3='test@test.com' ### @4='test' ### @5=0 # at 633 #181023 18:54:24 server id 1 end_log_pos 451 CRC32 0xde0c021e Xid = 182 COMMIT/*!*/; SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; root@v2:/var/lib/mysql#

参考：

https://www.cnblogs.com/panwenbin-logs/p/8366940.html

https://blog.csdn.net/JackLiu16/article/details/79189831

转载请注明原文地址: https://www.6miu.com/read-4931493.html

Java

最新回复(0)