mysql复制原理与机制一
复制原理:复制需要二进制日志记录数据库上的改变
slave的IO线程复制把master上的Binary log读取到本地的relay log里
SQL线程负责把relay log恢复到数据库数据里
show slave statusG; Slave_IO_State: Waiting for master to send event Master_Host: 10.105.7.108 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 630 Relay_Log_File: mysqld-relay-bin.000006 Relay_Log_Pos: 793 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes 关闭slave的io线程 stop slave io_thread; mysql> show slave statusG; *************************** 1. row *************************** Slave_IO_State: Master_Host: 10.105.7.108 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 630 Relay_Log_File: mysqld-relay-bin.000006 Relay_Log_Pos: 793 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: No Slave_SQL_Running: Yes 到master mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 143 | | mysql-bin.000002 | 367 | | mysql-bin.000003 | 120 | | mysql-bin.000004 | 630 | +------------------+-----------+ 4 rows in set (0.00 sec) 日志轮换 mysql> flush logs; Query OK, 0 rows affected (0.02 sec) mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 143 | | mysql-bin.000002 | 367 | | mysql-bin.000003 | 120 | | mysql-bin.000004 | 677 | | mysql-bin.000005 | 120 | +------------------+-----------+ mysql> use hk; Database changed mysql> show tables; +--------------+ | Tables_in_hk | +--------------+ | test | +--------------+ 1 row in set (0.01 sec) mysql> select * from test; +----+---------------------+ | id | time | +----+---------------------+ | 1 | 2017-02-04 10:04:12 | | 2 | 2017-02-04 13:53:35 | | 3 | 2017-02-08 19:46:49 | +----+---------------------+ 3 rows in set (0.00 sec) mysql> insert into test values(4,now()); Query OK, 1 row affected (0.05 sec) mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 143 | | mysql-bin.000002 | 367 | | mysql-bin.000003 | 120 | | mysql-bin.000004 | 677 | | mysql-bin.000005 | 344 | +------------------+-----------+ 5 rows in set (0.00 sec) mysql> show binlog events in 'mysql-bin.000005'; +------------------+-----+-------------+-----------+-------------+--------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+-------------+-----------+-------------+--------------------------------------------+ | mysql-bin.000005 | 4 | Format_desc | 108 | 120 | Server ver: 5.6.35-log, Binlog ver: 4 | | mysql-bin.000005 | 120 | Query | 108 | 203 | BEGIN | | mysql-bin.000005 | 203 | Query | 108 | 313 | use `hk`; insert into test values(4,now()) | | mysql-bin.000005 | 313 | Xid | 108 | 344 | COMMIT /* xid=110 */ | +------------------+-----+-------------+-----------+-------------+--------------------------------------------+ 可以看到一件记录到二进制日志里 mysql> select * from test where id=4; +----+---------------------+ | id | time | +----+---------------------+ | 4 | 2017-02-08 20:34:49 | +----+---------------------+ 到从库 Database changed mysql> select * from test; +----+---------------------+ | id | time | +----+---------------------+ | 1 | 2017-02-04 10:04:12 | | 2 | 2017-02-04 13:53:35 | | 3 | 2017-02-08 19:46:49 | +----+---------------------+ 查看此时 Relay_Log_File: mysqld-relay-bin.000006 mysql> show slave statusG *************************** 1. row *************************** Slave_IO_State: Master_Host: 10.105.7.108 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 630 Relay_Log_File: mysqld-relay-bin.000006 Relay_Log_Pos: 793 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: No Slave_SQL_Running: Yes 开启io线程 mysql> start slave io_thread; Query OK, 0 rows affected (0.01 sec) mysql> show slave statusG *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.105.7.108 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000005 Read_Master_Log_Pos: 344 Relay_Log_File: mysqld-relay-bin.000009 Relay_Log_Pos: 507 Relay_Master_Log_File: mysql-bin.000005 Slave_IO_Running: Yes Slave_SQL_Running: Yes 发现中继日志发生变化 Relay_Log_File: mysqld-relay-bin.000009 mysql> select * from test where id=4; +----+---------------------+ | id | time | +----+---------------------+ | 4 | 2017-02-08 20:34:49 | +----+---------------------+ 1 row in set (0.00 sec) 此时sql进程 从中继日志 读取数据 写入数据库
在mysql外执行
mysqlbinlog mysqld-relay-bin.000009
[root@bogon mysql]# mysqlbinlog mysqld-relay-bin.000009 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #170208 20:39:46 server id 163 end_log_pos 120 CRC32 0xbdb725c4 Start: binlog v 4, server v 5.6.35 created 170208 20:39:46 BINLOG ' khGbWA+jAAAAdAAAAHgAAABAAAQANS42LjM1AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAcQl t70= '/*!*/; # at 120 #170208 20:39:46 server id 108 end_log_pos 0 CRC32 0x0649f72c Rotate to mysql-bin.000005 pos: 4 # at 167 #170208 20:33:47 server id 108 end_log_pos 120 CRC32 0x045c4ff1 Start: binlog v 4, server v 5.6.35-log created 170208 20:33:47 BINLOG ' KxCbWA9sAAAAdAAAAHgAAAAAAAQANS42LjM1LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAfFP XAQ= '/*!*/; # at 283 #170208 20:34:49 server id 108 end_log_pos 203 CRC32 0xb10230cc Query thread_id=9 exec_time=0 error_code=0 SET TIMESTAMP=1486557289/*!*/; SET @@session.pseudo_thread_id=9/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1075838976/*!*/; 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.time_zone='SYSTEM'/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; # at 366 #170208 20:34:49 server id 108 end_log_pos 313 CRC32 0x59ee4c4e Query thread_id=9 exec_time=0 error_code=0 use `hk`/*!*/; SET TIMESTAMP=1486557289/*!*/; insert into test values(4,now()) /*!*/; # at 476 #170208 20:34:49 server id 108 end_log_pos 344 CRC32 0x1735772f Xid = 110 COMMIT/*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
复制特点
1.sql更新,没写binary log 不可能复制
2.正在执行的sql更新,也不可能被复制
3.只有执行完成而且成功的语句才会被复制